{"id":17005,"date":"2020-04-19T06:39:03","date_gmt":"2020-04-19T10:39:03","guid":{"rendered":"http:\/\/bangla.salearningschool.com\/recent-posts\/python-ecommerce-part-4-calculate-your-sell-prices-for-your-retail-website-and-then-calculate-for-amazon-and-ebay\/"},"modified":"2021-04-06T18:28:39","modified_gmt":"2021-04-06T22:28:39","slug":"python-ecommerce-part-4-calculate-your-sell-prices-for-your-retail-website-and-then-calculate-for-amazon-and-ebay","status":"publish","type":"post","link":"http:\/\/bangla.sitestree.com\/?p=17005","title":{"rendered":"Python: Ecommerce: Part \u2014 4: Calculate Your Sell Prices for your Retail Website, and then calculate for Amazon and Ebay"},"content":{"rendered":"
\n
\n
\n
\n
\n

The requirements for each of the sales channel can be different such as for Ebay you might want to calculate Paypal fees; for Amazon you have to check for Amazon fees. For your own retail site, shipping cost might need different calculation as well as based on your target audience tax\/hst will affect the price.<\/p>\n

Use these code as a guidance and adjust as per your need.<\/p>\n

Read the comments in the code. The comments explain what the code is doing. Check the previous articles in this series to make this article make more sense.<\/p>\n

I will put the cell by cell code from Jupyter Notebook first; then will paste all the code in one block. If Medium does not maintain the indent, do it yourself when using the code.<\/p>\n

This article will focus on Price calculation for Magento Based Retail Website. Calculation is not really platform dependent. At one point this data will be saved for Magento 2 platform to be uploaded.<\/p>\n

Section Data Upload for Magento 2.<\/h1>\n

In [1]:<\/p>\n

import<\/strong> pandas as<\/strong> pd<\/span><\/pre>\n
# output file will be dated<\/em><\/p>\n
from<\/strong> datetime import<\/strong> date;<\/span>today =<\/strong> date.today()<\/span>today =<\/strong> today.strftime(\"%Y-%m-%d\")<\/span>today<\/span><\/pre>\n
Out[2]:<\/p>\n
'2020-04-17'<\/span><\/pre>\n
In [3]:<\/p>\n
data_folder =<\/strong> 'data-supplier-2020-04-14\/';<\/span>template_folder =<\/strong> '.\/templates\/'<\/span><\/pre>\n
In [4]:<\/p>\n
input_file_name =<\/strong> 'all_supplier_data_unique_sorted_and_filtered.csv';<\/span>raw_file =<\/strong> data_folder +<\/strong> input_file_name;<\/span>raw_file<\/span><\/pre>\n
Out[4]:<\/p>\n
'data-supplier-2020-04-14\/all_supplier_data_unique_sorted_and_filtered.csv'<\/span><\/pre>\n
In [6]:<\/p>\n
# read data from the combined sorted filtered file<\/em><\/p>\n
raw_df =<\/strong> pd.read_csv(raw_file)<\/span>raw_df.head(2)<\/span><\/pre>\n
Out[6]:<\/p>\n
Unnamed: 0Product IDModel CodeFull Product NameShort Product NameProduct URLCategory NameCategory URLSubcategory NameSubcategory URL\u2026Related ProductsRelated AccessoriesWeight KgHeight mmWidth mmDepth mmVideo linkRetail PriceStock statusDate Back0899230399A01AL3301111Black 3x3x3 MoYu AoLong V2 PuzzleBlack 3x3x3 MoYu AoLong V2 Puzzle<\/p>\n
2 rows \u00d7 41 columns<\/p>\n
In [7]:<\/p>\n
# check product price and columns<\/em><\/p>\n
raw_df[:1]['1pc Price'], raw_df.shape, raw_df.columns<\/span><\/pre>\n
Out[7]:<\/p>\n
(0    12.9\n Name: 1pc Price, dtype: float64,\n (44911, 41),\n Index(['Unnamed: 0', 'Product ID', 'Model Code', 'Full Product Name',\n        'Short Product Name', 'Product URL', 'Category Name', 'Category URL',\n        'Subcategory Name', 'Subcategory URL', 'Date Product Was Launched',\n        'Main Product Picture', 'Currency', '1pc Price',\n        '1pc Cheapest Postal Shipping Price',\n        '1pc Cheapest Express Courier Shipping Price', 'EAN',\n        'Soon Discontinued', 'Full Product Description Part 1',\n        'Full Product Description Part 2', 'Short Description',\n        'Additional Product Picture 1', 'Additional Product Picture 2',\n        'Additional Product Picture 3', 'Additional Product Picture 4',\n        'Additional Product Picture 5', 'Additional Product Picture 6',\n        'Additional Product Picture 7', 'Additional Product Picture 8',\n        'Additional Product Picture 9', 'Additional Product Picture 10',\n        'Related Products', 'Related Accessories', 'Weight Kg', 'Height mm',\n        'Width mm', 'Depth mm', 'Video link', 'Retail Price', 'Stock status',\n        'Date Back'],\n       dtype='object'))<\/span><\/pre>\n
In [10]:<\/p>\n
# Now, sell prices and related will be calculated including sell prices for amazon and ebay<\/em><\/p>\n
# The output column names are kept in a csv file.<\/em><\/span># Reading the column names from that template file<\/em><\/span>price_calculation_template_file =<\/strong> template_folder  +<\/strong> 'price_calculations_template.csv'<\/span>price_calculation_template =<\/strong> pd.read_csv(price_calculation_template_file)<\/span>price_calculation_template.head(), price_calculation_template.columns<\/span><\/pre>\n
Out[10]:<\/p>\n
(Empty DataFrame\n Columns: [product_id, sku, Name, raw_price, dummy, cheapest_courier_shipping, product_ean, Ship Risk, Price Risk, Total, Risk Return , Total with ship, Referral Fee, Paypal Fees, Final Value Fee, Insertion Fee , HST, EU Vat, Currency Risk, shopforsoul_profit, ebay_profit, Income Tax, shopforsoul_cost, MLP, Special List Price, special_from_date, special_to_date, ebay_cost, ebay_list_price, Buyer pay from Mall, Ebay Buyers Pay, Ebay vs Mall, NA-1, Amazon Final Value Fee, amazon_profit, amazon_our_cost, amazon_list_price, amazon_special_price, Amazon Final Value Fee USD, amazon_profit_usd, amazon_our_cost_usd, amazon_list_price_usd, amazon_special_price_usd, amazon_list_price_gbp, amazon_special_price_gbp, amazon_list_price_eu, amazon_special_price_eu, amazon_list_price_mxn, amazon_special_price_mxn, ama_safe_refund_amount, Weight, Stock status, _category, _root_category, Visibility, Original Weight, Height mm, Width mm, Depth mm, status, Retail Price, original in stock, R+shipp, Shop vs retail, Soon Discontinued, weight right, Ship Right, Is Normal, amazon_ca_minimum_price, amazon_usd_minimum_price, amazon_gbp_minimum_price, amazon_eu_minimum_price, amazon_mxn_minimum, amazon_ca_abs_minimum, expedited_cost]\n Index: []\n \n [0 rows x 75 columns],\n Index(['product_id', 'sku', 'Name', 'raw_price', 'dummy',\n        'cheapest_courier_shipping', 'product_ean', 'Ship Risk', 'Price Risk',\n        'Total', 'Risk Return ', 'Total with ship', 'Referral Fee',\n        'Paypal Fees', 'Final Value Fee', 'Insertion Fee ', 'HST', 'EU Vat',\n        'Currency Risk', 'shopforsoul_profit', 'ebay_profit', 'Income Tax',\n        'shopforsoul_cost', 'MLP', 'Special List Price', 'special_from_date',\n        'special_to_date', 'ebay_cost', 'ebay_list_price',\n        'Buyer pay from Mall', 'Ebay Buyers Pay', 'Ebay vs Mall', 'NA-1',\n        'Amazon Final Value Fee', 'amazon_profit', 'amazon_our_cost',\n        'amazon_list_price', 'amazon_special_price',\n        'Amazon Final Value Fee USD', 'amazon_profit_usd',\n        'amazon_our_cost_usd', 'amazon_list_price_usd',\n        'amazon_special_price_usd', 'amazon_list_price_gbp',\n        'amazon_special_price_gbp', 'amazon_list_price_eu',\n        'amazon_special_price_eu', 'amazon_list_price_mxn',\n        'amazon_special_price_mxn', 'ama_safe_refund_amount', 'Weight',\n        'Stock status', '_category', '_root_category', 'Visibility',\n        'Original Weight', 'Height mm', 'Width mm', 'Depth mm', 'status',\n        'Retail Price', 'original in stock', 'R+shipp', 'Shop vs retail',\n        'Soon Discontinued', 'weight right', 'Ship Right', 'Is Normal',\n        'amazon_ca_minimum_price', 'amazon_usd_minimum_price',\n        'amazon_gbp_minimum_price', 'amazon_eu_minimum_price',\n        'amazon_mxn_minimum', 'amazon_ca_abs_minimum', 'expedited_cost'],\n       dtype='object'))<\/span>#price_calculation_template.columns<\/em><\/span>#raw_df.columns, price_calculation_template.columns<\/em><\/span><\/pre>\n
In [11]:<\/p>\n
# create a dataframe to store the prices<\/em><\/p>\n
price_calculation_df =<\/strong> pd.DataFrame()<\/span><\/pre>\n
In [12]:<\/p>\n
# take some column value as is : This block is not used any more<\/em><\/p>\n
\"\"\"<\/span>price_calculation_df[<\/span>[<\/span>'product_id',<\/span>'sku',<\/span>'Name',<\/span>'raw_price',<\/span>'Postal Shipping Price',<\/span>'cheapest_courier_shipping',<\/span>'product_ean'<\/span>]<\/span>] = raw_df[<\/span>[<\/span>'Product ID',<\/span>'Model Code',<\/span>'Full Product Name',<\/span>'1pc Price',<\/span>'1pc Cheapest Postal Shipping Price',<\/span>'1pc Cheapest Express Courier Shipping Price',<\/span>'EAN'<\/span>]<\/span>]<\/span>\"\"\"<\/span><\/pre>\n
Out[12]:<\/p>\n
\"\\nprice_calculation_df[\\n        [\\n        'product_id', \\n        'sku', \\n        'Name',\\n        'raw_price', \\n        'Postal Shipping Price',\\n        'cheapest_courier_shipping', \\n        'product_ean'\\n        ]\\n    ] = raw_df[\\n            [\\n                'Product ID', \\n                'Model Code', \\n                'Full Product Name',                \\n                '1pc Price', \\n                '1pc Cheapest Postal Shipping Price',\\n                '1pc Cheapest Express Courier Shipping Price', \\n                'EAN'                \\n            ]\\n        ]\\n\\n\"<\/span><\/pre>\n
In [ ]:<\/p>\n
# take some columns from the raw input dataframe to the output price calculation data frame<\/em><\/span># The terms A2, B2, C2 were used for a reason. Previously the calulations were being done in Excel and later converted to<\/em><\/span># Python code. To make the conversion easy, the cell names are kept intact. You can modify i.e. replace A2, B2, C2 with names you like,<\/em><\/span># you can even remove them and adjust the code with dataframe columns<\/em><\/span><\/pre>\n
In [13]:<\/p>\n
# keep product id as is<\/em><\/span>A2 =<\/strong> price_calculation_df['product_id'] =<\/strong> raw_df['Product ID']<\/span># modify product sku<\/em><\/span>B2 =<\/strong> price_calculation_df['sku'] =<\/strong> 'shopforsoul-' +<\/strong> raw_df['Model Code']<\/span># create product name. added code to the name. this might for verification purpose such as to refer to the supplier<\/em><\/span>C2 =<\/strong> price_calculation_df['Name'] =<\/strong> raw_df['Full Product Name'] +<\/strong> '-' +<\/strong> raw_df['Model Code']<\/span>D2 =<\/strong> price_calculation_df['raw_price'] =<\/strong> raw_df['1pc Price']<\/span>E2 =<\/strong> price_calculation_df['Postal Shipping Price'] =<\/strong> raw_df['1pc Cheapest Postal Shipping Price']<\/span>F2 =<\/strong> price_calculation_df['cheapest_courier_shipping'] =<\/strong> raw_df['1pc Cheapest Express Courier Shipping Price']<\/span>G2 =<\/strong> price_calculation_df['product_ean'] =<\/strong> raw_df['EAN']<\/span>#price_calculation_df['product_ean'] [:-3]<\/em><\/span><\/pre>\n
In [14]:<\/p>\n
list(price_calculation_df['Name'][:1])<\/span><\/pre>\n
Out[14]:<\/p>\n
['Black 3x3x3 MoYu AoLong V2 Puzzle-A01AL3301111']<\/span><\/pre>\n
In [16]:<\/p>\n
raw_df['1pc Cheapest Postal Shipping Price'][:2]<\/span><\/pre>\n
Out[16]:<\/p>\n
0    No Postal Shipping Available\n1    No Postal Shipping Available\nName: 1pc Cheapest Postal Shipping Price, dtype: object<\/span><\/pre>\n
In [21]:<\/p>\n
# adjust cheapest_courier_shipping as supplied by supplier<\/em><\/span><\/pre>\n
In [22]:<\/p>\n
# bring all F2 processing : cheapest_courier_shipping : replace no with $500 i.e. dificult and do not want to sell<\/em><\/p>\n
F2 =<\/strong> F2.replace('NaN','500')<\/span>F2 =<\/strong> F2.replace('nan','500')<\/span><\/pre>\n
In [23]:<\/p>\n
#F2.unique()<\/em><\/span>#No Courier Shipping Available, replace with 500<\/em><\/span>#F2 = price_calculation_df['cheapest_courier_shipping'] = F2.replace('No Courier Shipping Available','500')#.astype(float)<\/em><\/span>F2 =<\/strong> price_calculation_df['cheapest_courier_shipping'] =<\/strong> F2.replace('No Postal Shipping Available','500')#.astype(float)<\/em><\/span>F2 =<\/strong> price_calculation_df['cheapest_courier_shipping'] =<\/strong> F2.astype(float)<\/span>F2 =<\/strong> price_calculation_df['cheapest_courier_shipping'] =<\/strong> price_calculation_df['cheapest_courier_shipping'].replace(',','')<\/span><\/pre>\n
In [24]:<\/p>\n
price_calculation_df['cheapest_courier_shipping'][:5]<\/span><\/pre>\n
Out[24]:<\/p>\n
0    500.0\n1    500.0\n2    500.0\n3    500.0\n4    500.0\nName: cheapest_courier_shipping, dtype: float64<\/span><\/pre>\n
In [25]:<\/p>\n
# adjust cheapest_courier_shipping as provided by the supplier<\/em><\/span><\/pre>\n
In [26]:<\/p>\n
E2 =<\/strong> price_calculation_df['Postal Shipping Price'] =<\/strong> E2.replace('No Postal Shipping Available','500')<\/span>E2 =<\/strong> price_calculation_df['Postal Shipping Price'] =<\/strong> E2.astype(float)<\/span>E2 =<\/strong> price_calculation_df['Postal Shipping Price'] =<\/strong> price_calculation_df['Postal Shipping Price'].replace(',','')<\/span><\/pre>\n
In [28]:<\/p>\n
sorted(F2, reverse=True<\/strong>)[:5]<\/span><\/pre>\n
Out[28]:<\/p>\n
[500.0, 500.0, 500.0, 500.0, 500.0]<\/span><\/pre>\n
In [30]:<\/p>\n
# check the output file so far<\/em><\/span>price_calculation_df.head(2)<\/span><\/pre>\n
Out[30]:<\/p>\n
product_idskuNameraw_pricePostal Shipping Pricecheapest_courier_shippingproduct_ean030399shopforsoul-A01AL3301111Black 3x3x3 MoYu AoLong V2 Puzzle-A01AL330111112.90500.0500.06.941378e+12138649shopforsoul-A01AL3303110Qiyun AoLong V2 3x3x3 Speed Cube Enhanced Edit\u202613.61500.0500.06.941378e+12<\/p>\n
In [31]:<\/p>\n
# adjust product EAN. Product EAN are matched with Amazon to find the Amazon Product ID : ASIN<\/em><\/span>G2 =<\/strong> price_calculation_df['product_ean'] =<\/strong> price_calculation_df['product_ean'].astype(str)<\/span>price_calculation_df['product_ean'][:4]<\/span><\/pre>\n
Out[31]:<\/p>\n
0    6941377886603.0\n1    6941377965988.0\n2    6941377972443.0\n3    6941377953466.0\nName: product_ean, dtype: object<\/span><\/pre>\n
In [32]:<\/p>\n
type(F2)<\/span><\/pre>\n
Out[32]:<\/p>\n
pandas.core.series.Series<\/span><\/pre>\n
In [33]:<\/p>\n
# define some constants to calculate your target price such as currency rates, shipping risk, hst, your tax burden. Some below are in percentages<\/em><\/span><\/pre>\n
In [34]:<\/p>\n
ship_risk_percent =<\/strong> 0.03<\/span>price_risk_percent =<\/strong> 0.03<\/span>return_risk_percent =<\/strong> 0.05 #or max $XX<\/em><\/span>max_return_amount =<\/strong> 5<\/span>referral_rate =<\/strong> 0.001<\/span>paypal_fee_rate =<\/strong> 0.001<\/span>final_value_fee_rate =<\/strong> 0.001<\/span>insertion_fee_rate =<\/strong> 0.001<\/span>hst_rate =<\/strong> 0.001<\/span>eu_vat_rate =<\/strong> 0.001<\/span>currency_risk =<\/strong> 0.001<\/span>sfs_profit =<\/strong> 0.001<\/span>ebay_profit =<\/strong> 0.001<\/span>income_tax =<\/strong> 0.001<\/span>sfs_special_profit =<\/strong> 0.001<\/span>special_from_date =<\/strong> '2020-04-03'<\/span>special_to_date =<\/strong> '2020-06-30'<\/span>amazon_final_fee_rate =<\/strong> 0.001<\/span>amazon_profit =<\/strong> 0.001<\/span>amazon_special_profit =<\/strong> 0.001<\/span>cad_to_usd =<\/strong> 0.704114<\/span>cad_to_gbp =<\/strong> 0.573651<\/span>cad_to_euro =<\/strong> 0.651656<\/span>cad_to_mxn =<\/strong> 17.5915<\/span>min_raw_price_to_show_on_amazon =<\/strong> 10<\/span><\/pre>\n
In [ ]:<\/p>\n
# adjust for the case that shipping rate\/cost may change<\/em><\/span><\/pre>\n
In [36]:<\/p>\n
# D2 = price_calculation_df['raw_price'] = raw_df['']<\/em><\/span># price_calculation_df['cheapest_courier_shipping'].where<\/em><\/span>H2 =<\/strong> price_calculation_df['Ship Risk'] =<\/strong> F2 *<\/strong> ship_risk_percent #price_calculation_df['raw_price']<\/em><\/span>price_calculation_df['Ship Risk'][:5]<\/span><\/pre>\n
Out[36]:<\/p>\n
0    15.0\n1    15.0\n2    15.0\n3    15.0\n4    15.0\nName: Ship Risk, dtype: float64<\/span><\/pre>\n
In [ ]:<\/p>\n
H2 =<\/strong> price_calculation_df['Ship Risk'] =<\/strong> F2 *<\/strong> ship_risk_percent #price_calculation_df['raw_price']<\/em><\/span><\/pre>\n
In [37]:<\/p>\n
# adjust for the case that supplier price  may change<\/em><\/span>I2 =<\/strong> price_calculation_df['Price Risk'] =<\/strong> price_calculation_df['raw_price'] *<\/strong> price_risk_percent<\/span><\/pre>\n
In [38]:<\/p>\n
# calculate total product price so far<\/em><\/span>J2 =<\/strong> price_calculation_df['Total'] =<\/strong> round(D2 +<\/strong> F2 +<\/strong> H2 +<\/strong> I2,2) #price_calculation_df['raw_price'] + price_calculation_df['Ship Risk'] + price_calculation_df['Price Risk']<\/em><\/span><\/pre>\n
Out[38]:<\/p>\n
(0    12.90\n 1    13.61\n 2    10.20\n 3    10.12\n 4    10.96\n Name: raw_price, dtype: float64, 0    500.0\n 1    500.0\n 2    500.0\n 3    500.0\n 4    500.0\n Name: cheapest_courier_shipping, dtype: float64, 0    500.0\n 1    500.0\n 2    500.0\n 3    500.0\n 4    500.0\n Name: cheapest_courier_shipping, dtype: float64, 0    15.0\n 1    15.0\n 2    15.0\n 3    15.0\n 4    15.0\n Name: cheapest_courier_shipping, dtype: float64, 0    0.3870\n 1    0.4083\n 2    0.3060\n 3    0.3036\n 4    0.3288\n Name: raw_price, dtype: float64, 0    528.29\n 1    529.02\n 2    525.51\n 3    525.42\n 4    526.29\n dtype: float64)<\/span><\/pre>\n
In [39]:<\/p>\n
\n
# verify the total price so far<\/em><\/p>\n<\/blockquote>\n
price_calculation_df['raw_price'][:5], F2[:5], price_calculation_df['cheapest_courier_shipping'][:5], H2[:5], I2[:5], J2[:5]<\/span><\/pre>\n
Out[39]:<\/p>\n
(0    12.90\n 1    13.61\n 2    10.20\n 3    10.12\n 4    10.96\n Name: raw_price, dtype: float64, 0    500.0\n 1    500.0\n 2    500.0\n 3    500.0\n 4    500.0\n Name: cheapest_courier_shipping, dtype: float64, 0    500.0\n 1    500.0\n 2    500.0\n 3    500.0\n 4    500.0\n Name: cheapest_courier_shipping, dtype: float64, 0    15.0\n 1    15.0\n 2    15.0\n 3    15.0\n 4    15.0\n Name: cheapest_courier_shipping, dtype: float64, 0    0.3870\n 1    0.4083\n 2    0.3060\n 3    0.3036\n 4    0.3288\n Name: raw_price, dtype: float64, 0    528.29\n 1    529.02\n 2    525.51\n 3    525.42\n 4    526.29\n dtype: float64)<\/span><\/pre>\n
In [40]:<\/p>\n
# adjust for the case that some products will be returned back. Account for related expenses<\/em><\/span><\/pre>\n
In [41]:<\/p>\n
#price_calculation_df['Risk Return'] = []<\/em><\/span>risk_return =<\/strong> []<\/span>for<\/strong> aPrice in<\/strong> F2:<\/span>#print(float(aPrice) * return_risk_percent)<\/em><\/span>try<\/strong>:<\/span>risk_amount =<\/strong> float(aPrice) *<\/strong> return_risk_percent<\/span>risk_amount =<\/strong> round(risk_amount, 2)<\/span>except<\/strong>:<\/span>risk_amount =<\/strong> max_return_amount<\/span>if<\/strong> risk_amount  <<\/strong> max_return_amount:<\/span>risk_return.append(  risk_amount )<\/span>else<\/strong>:<\/span>risk_return.append(max_return_amount)<\/span>price_calculation_df['Risk Return'] =<\/strong> risk_return<\/span>K2 =<\/strong> price_calculation_df['Risk Return'] #= 10 #sorted([ int(price_calculation_df['cheapest_courier_shipping']), 10 ] )[1]<\/em><\/span>K2 =<\/strong> risk_return<\/span>K2[:5]<\/span><\/pre>\n
Out[41]:<\/p>\n
[20, 20, 20, 20, 20]<\/span><\/pre>\n
In [42]:<\/p>\n
# calculate your total cost (also how it affects your selling price) considering various factors<\/em><\/span><\/pre>\n
In [43]:<\/p>\n
L2 =<\/strong> price_calculation_df['Total with ship'] =<\/strong> round(J2 +<\/strong> K2, 2) #price_calculation_df['Total'] +  price_calculation_df['Risk Return']<\/em><\/span>L2[:5]<\/span>M2 =<\/strong> price_calculation_df['Referral Fee'] =<\/strong> price_calculation_df['Total with ship'] *<\/strong> referral_rate<\/span>N2 =<\/strong> price_calculation_df['Paypal Fees'] =<\/strong> 0.30 +<\/strong> price_calculation_df['Total with ship'] *<\/strong> paypal_fee_rate<\/span>O2 =<\/strong> price_calculation_df['Final Value Fee'] =<\/strong> price_calculation_df['Total with ship'] *<\/strong>  final_value_fee_rate<\/span>P2 =<\/strong> price_calculation_df['Insertion Fee'] =<\/strong> price_calculation_df['Total with ship'] *<\/strong> insertion_fee_rate<\/span>Q2 =<\/strong> price_calculation_df['HST'] =<\/strong> price_calculation_df['Total with ship'] *<\/strong> hst_rate<\/span>R2 =<\/strong> price_calculation_df['EU Vat'] =<\/strong> price_calculation_df['Total with ship'] *<\/strong> eu_vat_rate<\/span>S2 =<\/strong> price_calculation_df['Currency Risk'] =<\/strong> price_calculation_df['Total with ship'] *<\/strong> currency_risk<\/span>T2 =<\/strong> price_calculation_df['shopforsoul_profit'] =<\/strong> price_calculation_df['Total with ship'] *<\/strong> sfs_profit<\/span>#ebay_profit = 0.20<\/em><\/span>U2 =<\/strong> price_calculation_df['ebay_profit'] =<\/strong> price_calculation_df['Total with ship'] *<\/strong> ebay_profit<\/span>#income_tax = 0.20<\/em><\/span>V2 =<\/strong> price_calculation_df['Income Tax'] =<\/strong> price_calculation_df['shopforsoul_profit'] *<\/strong> income_tax<\/span>M2[:5], V2[:5], U2[:5]<\/span><\/pre>\n
Out[43]:<\/p>\n
(0    54.829\n 1    54.902\n 2    54.551\n 3    54.542\n 4    54.629\n Name: Total with ship, dtype: float64, 0    27.4145\n 1    27.4510\n 2    27.2755\n 3    27.2710\n 4    27.3145\n Name: shopforsoul_profit, dtype: float64, 0    109.658\n 1    109.804\n 2    109.102\n 3    109.084\n 4    109.258\n Name: Total with ship, dtype: float64)<\/span><\/pre>\n
In [44]:<\/p>\n
# Output price (and related) for your magento or similar based retail shop<\/em><\/span># sale\/special prices and dates<\/em><\/span><\/pre>\n
In [45]:<\/p>\n
W2 =<\/strong> price_calculation_df['shopforsoul_cost'] =<\/strong> round(L2+<\/strong>M2+<\/strong>N2+<\/strong>P2+<\/strong>S2+<\/strong>V2,2)<\/span>#W2 = price_calculation_df['shopforsoul_cost']<\/em><\/span>X2 =<\/strong> price_calculation_df['MLP'] =<\/strong> round(W2+<\/strong>T2,2)<\/span>Y2 =<\/strong> price_calculation_df['Special List Price'] =<\/strong> round(W2+<\/strong>L2*<\/strong>sfs_special_profit,2)<\/span>Z2 =<\/strong> price_calculation_df['special_from_date'] =<\/strong> special_from_date<\/span>special_to_date =<\/strong> '2020-12-31'<\/span>AA2 =<\/strong> price_calculation_df['special_to_date'] =<\/strong> special_to_date<\/span><\/pre>\n
All Code in One Block. You might still need to adjust the indent<\/p>\n
# # Section Data Upload for Magento 2<\/p>\n
# In[1]:<\/span>import pandas as pd<\/span># In[2]:<\/span># output file will be dated\nfrom datetime import date;\ntoday = date.today()\ntoday = today.strftime(\u201c%Y-%m-%d\u201d)\ntoday<\/span># In[3]:<\/span>data_folder = \u2018data-supplier-2020\u201304\u201314\/\u2019;\ntemplate_folder = \u2018.\/templates\/\u2019<\/span># In[4]:<\/span>input_file_name = \u2018all_supplier_data_unique_sorted_and_filtered.csv\u2019;\nraw_file = data_folder + input_file_name;\nraw_file<\/span># In[6]:<\/span># read data from the combined sorted filtered file\nraw_df = pd.read_csv(raw_file)\nraw_df.head(2)<\/span># In[7]:<\/span># check product price and columns\nraw_df[:1][\u20181pc Price\u2019], raw_df.shape, raw_df.columns<\/span># In[10]:<\/span># Now, sell prices and related will be calculated including sell prices for amazon and ebay \n# The output column names are kept in a csv file.\n# Reading the column names from that template file\nprice_calculation_template_file = template_folder + \u2018price_calculations_template.csv\u2019\nprice_calculation_template = pd.read_csv(price_calculation_template_file)\nprice_calculation_template.head(), price_calculation_template.columns<\/span># In[ ]:<\/span>#price_calculation_template.columns<\/span># In[ ]:<\/span>#raw_df.columns, price_calculation_template.columns<\/span># In[11]:<\/span># create a dataframe to store the prices\nprice_calculation_df = pd.DataFrame()<\/span># In[12]:<\/span># take some column value as is : This block is not used any more\n\u201c\u201d\u201d\nprice_calculation_df[\n [\n \u2018product_id\u2019, \n \u2018sku\u2019, \n \u2018Name\u2019,\n \u2018raw_price\u2019, \n \u2018Postal Shipping Price\u2019,\n \u2018cheapest_courier_shipping\u2019, \n \u2018product_ean\u2019\n ]\n ] = raw_df[\n [\n \u2018Product ID\u2019, \n \u2018Model Code\u2019, \n \u2018Full Product Name\u2019, \n \u20181pc Price\u2019, \n \u20181pc Cheapest Postal Shipping Price\u2019,\n \u20181pc Cheapest Express Courier Shipping Price\u2019, \n \u2018EAN\u2019 \n ]\n ]<\/span>\u201c\u201d\u201d<\/span># In[ ]:<\/span># take some columns from the raw input dataframe to the output price calculation data frame\n# The terms A2, B2, C2 were used for a reason. Previously the calulations were being done in Excel and later converted to\n# Python code. To make the conversion easy, the cell names are kept intact. You can modify i.e. replace A2, B2, C2 with names you like, \n# you can even remove them and adjust the code with dataframe columns<\/span># In[13]:<\/span># keep product id as is\nA2 = price_calculation_df[\u2018product_id\u2019] = raw_df[\u2018Product ID\u2019]<\/span># modify product sku\nB2 = price_calculation_df[\u2018sku\u2019] = \u2018shopforsoul-\u2019 + raw_df[\u2018Model Code\u2019]<\/span># create product name. added code to the name. this might for verification purpose such as to refer to the supplier\nC2 = price_calculation_df[\u2018Name\u2019] = raw_df[\u2018Full Product Name\u2019] + \u2018-\u2019 + raw_df[\u2018Model Code\u2019]\nD2 = price_calculation_df[\u2018raw_price\u2019] = raw_df[\u20181pc Price\u2019]\nE2 = price_calculation_df[\u2018Postal Shipping Price\u2019] = raw_df[\u20181pc Cheapest Postal Shipping Price\u2019]\nF2 = price_calculation_df[\u2018cheapest_courier_shipping\u2019] = raw_df[\u20181pc Cheapest Express Courier Shipping Price\u2019]\nG2 = price_calculation_df[\u2018product_ean\u2019] = raw_df[\u2018EAN\u2019]\n#price_calculation_df[\u2018product_ean\u2019] [:-3]<\/span># In[14]:<\/span>list(price_calculation_df[\u2018Name\u2019][:1])<\/span># In[16]:<\/span>raw_df[\u20181pc Cheapest Postal Shipping Price\u2019][:2]<\/span># In[21]:<\/span># adjust cheapest_courier_shipping as supplied by supplier<\/span># In[22]:<\/span># bring all F2 processing : cheapest_courier_shipping : replace no with $500 i.e. dificult and do not want to sell\nF2 = F2.replace(\u2018NaN\u2019,\u2019500')\nF2 = F2.replace(\u2018nan\u2019,\u2019500')<\/span># In[23]:<\/span>#F2.unique()\n#No Courier Shipping Available, replace with 500\n#F2 = price_calculation_df[\u2018cheapest_courier_shipping\u2019] = F2.replace(\u2018No Courier Shipping Available\u2019,\u2019500')#.astype(float)\nF2 = price_calculation_df[\u2018cheapest_courier_shipping\u2019] = F2.replace(\u2018No Postal Shipping Available\u2019,\u2019500')#.astype(float)\nF2 = price_calculation_df[\u2018cheapest_courier_shipping\u2019] = F2.astype(float)\nF2 = price_calculation_df[\u2018cheapest_courier_shipping\u2019] = price_calculation_df[\u2018cheapest_courier_shipping\u2019].replace(\u2018,\u2019,\u2019\u2019)<\/span># In[24]:<\/span>price_calculation_df[\u2018cheapest_courier_shipping\u2019][:5]<\/span># In[25]:<\/span># adjust cheapest_courier_shipping as provided by the supplier<\/span># In[26]:<\/span>E2 = price_calculation_df[\u2018Postal Shipping Price\u2019] = E2.replace(\u2018No Postal Shipping Available\u2019,\u2019500')\nE2 = price_calculation_df[\u2018Postal Shipping Price\u2019] = E2.astype(float)\nE2 = price_calculation_df[\u2018Postal Shipping Price\u2019] = price_calculation_df[\u2018Postal Shipping Price\u2019].replace(\u2018,\u2019,\u2019\u2019)<\/span># In[28]:<\/span>sorted(F2, reverse=True)[:5]<\/span># In[30]:<\/span># check the output file so far\nprice_calculation_df.head(2)<\/span># In[31]:<\/span># adjust product EAN. Product EAN are matched with Amazon to find the Amazon Product ID : ASIN\nG2 = price_calculation_df[\u2018product_ean\u2019] = price_calculation_df[\u2018product_ean\u2019].astype(str)\nprice_calculation_df[\u2018product_ean\u2019][:4]<\/span># In[32]:<\/span>type(F2)<\/span># In[33]:<\/span># define some constants to calculate your target price such as currency rates, shipping risk, hst, your tax burden<\/span># In[34]:<\/span>ship_risk_percent = 0.001\nprice_risk_percent = 0.001\nreturn_risk_percent = 0.001\nmax_return_amount = 0.001\nreferral_rate = 0.001\npaypal_fee_rate = 0.001\nfinal_value_fee_rate = 0.001\ninsertion_fee_rate = 0.001\nhst_rate = 0.001\neu_vat_rate = 0.001\ncurrency_risk = 0.001\nsfs_profit = 0.001\nebay_profit = 0.001\nincome_tax = 0.001\nsfs_special_profit = 0.001\nspecial_from_date = \u20182020\u201304\u201303\u2019\nspecial_to_date = \u20182020\u201306\u201330\u2019\namazon_final_fee_rate = 0.001\namazon_profit = 0.001\namazon_special_profit = 0.001\ncad_to_usd = 0.704114\ncad_to_gbp = 0.573651\ncad_to_euro = 0.651656\ncad_to_mxn = 17.5915\nmin_raw_price_to_show_on_amazon = 0.001<\/span># In[ ]:<\/span># adjust for the case that shipping rate\/cost may change<\/span># In[36]:<\/span># D2 = price_calculation_df[\u2018raw_price\u2019] = raw_df[\u2018\u2019]\n# price_calculation_df[\u2018cheapest_courier_shipping\u2019].where\nH2 = price_calculation_df[\u2018Ship Risk\u2019] = F2 * ship_risk_percent #price_calculation_df[\u2018raw_price\u2019]\nprice_calculation_df[\u2018Ship Risk\u2019][:5]<\/span># In[ ]:<\/span>H2 = price_calculation_df[\u2018Ship Risk\u2019] = F2 * ship_risk_percent #price_calculation_df[\u2018raw_price\u2019]<\/span># In[37]:<\/span># adjust for the case that supplier price may change\nI2 = price_calculation_df[\u2018Price Risk\u2019] = price_calculation_df[\u2018raw_price\u2019] * price_risk_percent<\/span># In[38]:<\/span># calculate total product price so far\nJ2 = price_calculation_df[\u2018Total\u2019] = round(D2 + F2 + H2 + I2,2) #price_calculation_df[\u2018raw_price\u2019] + price_calculation_df[\u2018Ship Risk\u2019] + price_calculation_df[\u2018Price Risk\u2019]<\/span># In[39]:<\/span># verify the total price so far\nprice_calculation_df[\u2018raw_price\u2019][:5], F2[:5], price_calculation_df[\u2018cheapest_courier_shipping\u2019][:5], H2[:5], I2[:5], J2[:5]<\/span># In[40]:<\/span># adjust for the case that some products will be returned back. Account for related expenses<\/span># In[41]:<\/span>#price_calculation_df[\u2018Risk Return\u2019] = []\nrisk_return = []\nfor aPrice in F2:\n #print(float(aPrice) * return_risk_percent)\n try:\n risk_amount = float(aPrice) * return_risk_percent\n risk_amount = round(risk_amount, 2)\n except:\n risk_amount = max_return_amount\n \n \n if risk_amount < max_return_amount:\n risk_return.append( risk_amount )\n else:\n risk_return.append(max_return_amount)\n \nprice_calculation_df[\u2018Risk Return\u2019] = risk_return\nK2 = price_calculation_df[\u2018Risk Return\u2019] #= 10 #sorted([ int(price_calculation_df[\u2018cheapest_courier_shipping\u2019]), 10 ] )[1]\nK2 = risk_return\nK2[:5]<\/span># In[42]:<\/span># calculate your total cost (also how it affects your selling price) considering various factors<\/span># In[43]:<\/span>L2 = price_calculation_df[\u2018Total with ship\u2019] = round(J2 + K2, 2) #price_calculation_df[\u2018Total\u2019] + price_calculation_df[\u2018Risk Return\u2019]\nL2[:5]\nM2 = price_calculation_df[\u2018Referral Fee\u2019] = price_calculation_df[\u2018Total with ship\u2019] * referral_rate\nN2 = price_calculation_df[\u2018Paypal Fees\u2019] = 0.30 + price_calculation_df[\u2018Total with ship\u2019] * paypal_fee_rate<\/span>O2 = price_calculation_df[\u2018Final Value Fee\u2019] = price_calculation_df[\u2018Total with ship\u2019] * final_value_fee_rate\nP2 = price_calculation_df[\u2018Insertion Fee\u2019] = price_calculation_df[\u2018Total with ship\u2019] * insertion_fee_rate\nQ2 = price_calculation_df[\u2018HST\u2019] = price_calculation_df[\u2018Total with ship\u2019] * hst_rate \nR2 = price_calculation_df[\u2018EU Vat\u2019] = price_calculation_df[\u2018Total with ship\u2019] * eu_vat_rate\nS2 = price_calculation_df[\u2018Currency Risk\u2019] = price_calculation_df[\u2018Total with ship\u2019] * currency_risk \nT2 = price_calculation_df[\u2018shopforsoul_profit\u2019] = price_calculation_df[\u2018Total with ship\u2019] * sfs_profit\n#ebay_profit = 0.20\nU2 = price_calculation_df[\u2018ebay_profit\u2019] = price_calculation_df[\u2018Total with ship\u2019] * ebay_profit\n#income_tax = 0.20\nV2 = price_calculation_df[\u2018Income Tax\u2019] = price_calculation_df[\u2018shopforsoul_profit\u2019] * income_tax\nM2[:5], V2[:5], U2[:5]<\/span># In[44]:<\/span># Output price (and related) for your magento or similar based retail shop\n# sale\/special prices and dates<\/span># In[45]:<\/span>W2 = price_calculation_df[\u2018shopforsoul_cost\u2019] = round(L2+M2+N2+P2+S2+V2,2)\n#W2 = price_calculation_df[\u2018shopforsoul_cost\u2019]\nX2 = price_calculation_df[\u2018MLP\u2019] = round(W2+T2,2)\nY2 = price_calculation_df[\u2018Special List Price\u2019] = round(W2+L2*sfs_special_profit,2)\nZ2 = price_calculation_df[\u2018special_from_date\u2019] = special_from_date\nspecial_to_date = \u20182020\u201312\u201331\u2019\nAA2 = price_calculation_df[\u2018special_to_date\u2019] = special_to_date<\/span><\/pre>\n
Medium:\u00a0<\/em><\/strong>https:\/\/medium.com\/@SayedAhmedCanada<\/a><\/p>\n
*** . *** *** . *** . *** . ***<\/p>\n
Sayed Ahmed<\/em><\/strong><\/p>\n
BSc. Eng. in Comp. Sc. & Eng. (BUET)<\/em><\/strong><\/p>\n
MSc. in Comp. Sc. (U of Manitoba, Canada)<\/em><\/strong><\/p>\n
MSc. in Data Science and Analytics (Ryerson University, Canada)<\/em><\/strong><\/p>\n
Linkedin<\/em><\/strong>:\u00a0<\/em>https:\/\/ca.linkedin.com\/in\/sayedjustetc<\/em><\/a><\/p>\n
Blog<\/em><\/strong>:\u00a0<\/em>http:\/\/Bangla.SaLearningSchool.com<\/em><\/a>,\u00a0<\/em>http:\/\/SitesTree.com<\/em><\/a><\/p>\n
Training Courses:\u00a0<\/em><\/strong>http:\/\/Training.SitesTree.com<\/em><\/a><\/p>\n
8112223 Canada Inc\/Justetc<\/em><\/strong>:\u00a0<\/em>http:\/\/JustEtc.net<\/em><\/a><\/p>\n
Facebook Groups\/Forums to discuss (Q & A):<\/em><\/strong><\/p>\n
https:\/\/www.facebook.com\/banglasalearningschool<\/a><\/p>\n
https:\/\/www.facebook.com\/justetcsocial<\/a><\/p>\n
Get access to courses on Big Data, Data Science, AI, Cloud, Linux, System Admin, Web Development and Misc. related. Also, create your own course to sell to others.\u00a0<\/em>http:\/\/sitestree.com\/training\/<\/a><\/p>\n<\/div>\n<\/div>\n<\/section>\n<\/div>\n<\/article>\n
\n
\n
\n
\n
\n
\n
Build Ecommerce Software and Systems<\/h2>\n
\n
Build Ecommerce Software and Systems<\/h4>\n<\/div>\n