{"id":17006,"date":"2020-04-19T06:40:15","date_gmt":"2020-04-19T10:40:15","guid":{"rendered":"https:\/\/bangla.salearningschool.com\/recent-posts\/python-ecommerce-part-5-calculate-your-sell-prices-for-ebay-and-amazon\/"},"modified":"2020-04-19T15:11:39","modified_gmt":"2020-04-19T19:11:39","slug":"python-ecommerce-part-5-calculate-your-sell-prices-for-ebay-and-amazon","status":"publish","type":"post","link":"http:\/\/bangla.sitestree.com\/?p=17006","title":{"rendered":"Python: Ecommerce: Part \u2014 5: Calculate Your Sell Prices for Ebay and Amazon"},"content":{"rendered":"
\n
\n
\n
\n
\n
\n

Please read the other articles for this series.<\/span><\/h1>\n<\/div>\n<\/div>\n

Code will be given cell by cell from Jupyter Notebook. Then will also give all code in one block. You might need to adjust the indenting a bit.<\/p>\n

Read the code comments as well. Comments explained the sections.<\/p>\n

Calculate sell prices (and related) for ebay<\/h1>\n

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

ebay_profit_tax =<\/strong> U2 *<\/strong> income_tax<\/span>AB2 =<\/strong> price_calculation_df['ebay_cost'] =<\/strong> round(L2 +<\/strong> N2 +<\/strong> O2 +<\/strong> P2 +<\/strong> S2 +<\/strong> ebay_profit_tax, 2)<\/span>W2[:5], AB2[:5]<\/span>L2[:1], M2[:1], N2[:1], P2[:1], S2[:1], V2[:1]<\/span><\/pre>\n
Out[46]:<\/p>\n
(0    548.29\n dtype: float64, 0    54.829\n Name: Total with ship, dtype: float64, 0    16.7487\n Name: Total with ship, dtype: float64, 0    5.4829\n Name: Total with ship, dtype: float64, 0    16.4487\n Name: Total with ship, dtype: float64, 0    27.4145\n Name: shopforsoul_profit, dtype: float64)<\/span><\/pre>\n
In [47]:<\/p>\n
AC2 =<\/strong> price_calculation_df['ebay_list_price'] =<\/strong> round(AB2 +<\/strong> U2 -<\/strong>F2, 2) # - F2, 2)<\/em><\/span>AC2[:5]<\/span><\/pre>\n
Out[47]:<\/p>\n
0    262.43\n1    263.43\n2    258.56\n3    258.43\n4    259.65\ndtype: float64<\/span><\/pre>\n
In [48]:<\/p>\n
AD2 =<\/strong> price_calculation_df['Buyer pay from Mall'] =<\/strong> round(Y2+<\/strong>R2,2)<\/span><\/pre>\n
In [49]:<\/p>\n
AE2 =<\/strong> price_calculation_df['Ebay Buyers Pay'] =<\/strong> round(AC2+<\/strong>R2+<\/strong>F2,2)<\/span>AF2 =<\/strong> price_calculation_df['Ebay vs Mall'] =<\/strong> round(AE2-<\/strong>AD2, 2)<\/span><\/pre>\n
account for amazon selling cost and calculate your sell price for amazon<\/h1>\n
#amazon has sales commission to pay<\/p>\n
#you might want to account for monthly subscription fee (based on how much you sell in a month and how much subscription fee you pay)<\/p>\n
In [50]:<\/p>\n
# possible income tax<\/em><\/span>AG2 =<\/strong> price_calculation_df['NA-1'] =<\/strong> round(W2-<\/strong>R2+<\/strong>L2*<\/strong>0.2,2)<\/span>AI2 =<\/strong> price_calculation_df['amazon_profit'] =<\/strong> round(L2*<\/strong>amazon_profit, 2)<\/span>AJ2 =<\/strong> price_calculation_df['amazon_our_cost'] =<\/strong> round(L2+<\/strong>S2+<\/strong>P2+<\/strong>R2+<\/strong>AI2*<\/strong>income_tax,2)<\/span>AH2 =<\/strong> price_calculation_df['Amazon Final Value Fee'] =<\/strong> round( (AI2 +<\/strong> AJ2) *<\/strong> amazon_final_fee_rate, 2)<\/span>AK2 =<\/strong> price_calculation_df['amazon_list_price'] =<\/strong> round(AJ2+<\/strong>AI2+<\/strong>AH2, 2)<\/span>AL2 =<\/strong> price_calculation_df['amazon_special_price'] =<\/strong> round(L2*<\/strong>amazon_special_profit+<\/strong>AJ2+<\/strong>(L2*<\/strong>amazon_special_profit+<\/strong>AJ2)*<\/strong>income_tax+<\/strong>L2*<\/strong>amazon_special_profit*<\/strong>income_tax-<\/strong>L2*<\/strong>amazon_profit*<\/strong>income_tax, 2)<\/span>AM2 =<\/strong> price_calculation_df['Amazon Final Value Fee USD'] =<\/strong> round(AH2*<\/strong>cad_to_usd, 2)<\/span>AN2 =<\/strong> price_calculation_df['amazon_profit_usd'] =<\/strong> round(AI2*<\/strong>cad_to_usd, 2)<\/span>AO2 =<\/strong> price_calculation_df['amazon_our_cost_usd'] =<\/strong> round(AJ2*<\/strong>cad_to_usd, 2)<\/span>AP2 =<\/strong> price_calculation_df['amazon_list_price_usd'] =<\/strong> round(AK2*<\/strong>cad_to_usd, 2)<\/span>AQ2 =<\/strong> price_calculation_df['amazon_special_price_usd'] =<\/strong> round(AL2*<\/strong>cad_to_usd, 2)<\/span><\/pre>\n
In [51]:<\/p>\n
AE2[:2], AF2[:2] , AG2[:2] , AH2[:2] , AI2[:2] , AJ2[:2] , AK2[:2] ,<\/span>AL2[:2] , AM2[:2] , AN2[:2] , AO2[:2] , AP2[:2] , AQ2[:2]<\/span><\/pre>\n
Out[51]:<\/p>\n
(0    935.52\n 1    936.76\n dtype: float64, 0    96.42\n 1    96.55\n dtype: float64, 0    115.82\n 1    115.97\n dtype: float64, 0    526.97\n 1    527.67\n dtype: float64, 0    739.21\n 1    740.19\n dtype: float64, 0    658.71\n 1    659.59\n dtype: float64)<\/span><\/pre>\n
In [52]:<\/p>\n
AR2 =<\/strong> price_calculation_df['amazon_list_price_gbp'] =<\/strong> round(AK2*<\/strong>cad_to_gbp, 2)<\/span>AS2 =<\/strong> price_calculation_df['amazon_special_price_gbp'] =<\/strong> round(AL2*<\/strong>cad_to_gbp, 2)<\/span>AT2 =<\/strong> price_calculation_df['amazon_list_price_eu'] =<\/strong> round(AK2*<\/strong>cad_to_euro,2)<\/span>AU2 =<\/strong> price_calculation_df['amazon_special_price_eu'] =<\/strong> round(AL2*<\/strong>cad_to_euro, 2)<\/span>AV2 =<\/strong> price_calculation_df['amazon_list_price_mxn'] =<\/strong> round(AK2*<\/strong>cad_to_mxn, 2)<\/span>AW2 =<\/strong> price_calculation_df['amazon_special_price_mxn'] =<\/strong> round(AL2*<\/strong>cad_to_mxn, 2)<\/span>AR2[:2], AS2[:2], AT2[:2], AU2[:2], AV2[:2], AW2[:2]<\/span><\/pre>\n
Out[52]:<\/p>\n
(0    602.25\n 1    603.04\n dtype: float64, 0    536.66\n 1    537.37\n dtype: float64, 0    684.14\n 1    685.05\n dtype: float64, 0    609.64\n 1    610.45\n dtype: float64, 0    18468.44\n 1    18492.89\n dtype: float64, 0    16457.20\n 1    16479.01\n dtype: float64)<\/span><\/pre>\n
In [53]:<\/p>\n
#D2 = 1<\/em><\/span>BD2 =<\/strong> raw_df['Weight Kg']<\/span>AX2 =<\/strong> price_calculation_df['ama_safe_refund_amount'] =<\/strong> round(AL2-<\/strong>H2-<\/strong>I2-<\/strong>K2-<\/strong>S2-<\/strong>P2-<\/strong>R2-<\/strong>AH2-<\/strong>D2-<\/strong>18, 2)<\/span>AY2 =<\/strong> price_calculation_df['Weight'] =<\/strong> round(BD2*<\/strong>2.2, 2)<\/span>BJ2 =<\/strong> price_calculation_df['original in stock'] =<\/strong> raw_df['Stock status']<\/span>AZ2 =<\/strong> price_calculation_df['Stock status'] =<\/strong> (BJ2==<\/strong>\"In Stock\").astype(int)<\/span>#AZ2 = AZ2.astype(int)<\/em><\/span>AX2[:2], AY2[:2], AZ2[:2], BJ2[:2], BD2[:2]<\/span><\/pre>\n
Out[53]:<\/p>\n
(0    573.29\n 1    573.41\n dtype: float64, 0    0.09\n 1    0.06\n Name: Weight Kg, dtype: float64, 0    0\n 1    0\n Name: Stock status, dtype: int64, 0    Closed\n 1       NaN\n Name: Stock status, dtype: object, 0    0.043\n 1    0.025\n Name: Weight Kg, dtype: float64)<\/span><\/pre>\n
In [55]:<\/p>\n
AY2[:5]<\/span><\/pre>\n
Out[55]:<\/p>\n
0    0.09\n1    0.06\n2    0.09\n3    0.09\n4    0.09\nName: Weight Kg, dtype: float64<\/span><\/pre>\n
In [56]:<\/p>\n
min_raw_price_to_show_on_amazon =<\/strong> 10<\/span>BA2 =<\/strong> price_calculation_df['categories'] =<\/strong> raw_df['Category Name'] +<\/strong> '\/' +<\/strong> raw_df['Subcategory Name']<\/span>BB2 =<\/strong> price_calculation_df['_root_category'] =<\/strong> 'All'<\/span>BC2 =<\/strong> price_calculation_df['visibility'] =<\/strong> 4<\/span>vis =<\/strong> []<\/span>for<\/strong> x in<\/strong> D2:<\/span>if<\/strong> x <<\/strong> min_raw_price_to_show_on_amazon:<\/span>#vis.append(1)<\/em><\/span># temporary<\/em><\/span>vis.append('Catalog, Search')<\/span>else<\/strong>:<\/span>vis.append('Catalog, Search')<\/span>BC2 =<\/strong> price_calculation_df['visibility'] =<\/strong> vis<\/span># temporary<\/em><\/span># BC2 = price_calculation_df['visibility'] = 4<\/em><\/span><\/pre>\n
In [57]:<\/p>\n
BC2[:5]<\/span><\/pre>\n
Out[57]:<\/p>\n
['Catalog, Search',\n 'Catalog, Search',\n 'Catalog, Search',\n 'Catalog, Search',\n 'Catalog, Search']<\/span><\/pre>\n
In [61]:<\/p>\n
# take width height from supplier to push to Amazon<\/em><\/span><\/pre>\n
In [62]:<\/p>\n
BE2 =<\/strong> price_calculation_df['Height mm'] =<\/strong> raw_df['Height mm']<\/span>BF2 =<\/strong> price_calculation_df['Width mm'] =<\/strong> raw_df['Width mm']<\/span>BG2 =<\/strong> price_calculation_df['Depth mm'] =<\/strong> raw_df['Depth mm']<\/span><\/pre>\n
In [64]:<\/p>\n
# We do not want to sell products that has weight > 40 lbs<\/em><\/p>\n
# status = 2 = disabled on Amazon<\/em><\/span># based on 'weight right' products will be disabled or enabled<\/em><\/span><\/pre>\n
In [65]:<\/p>\n
# default value<\/em><\/span>BH2 =<\/strong> price_calculation_df['status'] =<\/strong>  2<\/span>BN2 =<\/strong> price_calculation_df['weight right'] =<\/strong> 0<\/span>vis =<\/strong> []<\/span>for<\/strong> x in<\/strong> AY2:<\/span>if<\/strong> x <=<\/strong> 20:<\/span>vis.append(1)<\/span>else<\/strong>:<\/span>vis.append(0)<\/span>BN2 =<\/strong> price_calculation_df['weight right'] =<\/strong> vis<\/span>BN2[:5]<\/span><\/pre>\n
Out[65]:<\/p>\n
[1, 1, 1, 1, 1]<\/span><\/pre>\n
In [66]:<\/p>\n
# based on shipping cost, you might not want to sell products.<\/em><\/p>\n
# we will not sell if the shipping cost is more than $40<\/em><\/p>\n
In [67]:<\/p>\n
vis =<\/strong> []<\/span>for<\/strong> x in<\/strong> F2.astype(float):<\/span>if<\/strong> x >=<\/strong> 40:<\/span>vis.append(0)<\/span>else<\/strong>:<\/span>vis.append(1)<\/span>BO2 =<\/strong> price_calculation_df['Ship Right'] =<\/strong> vis<\/span>#if (F2 >= 40) and (D2\/F2 <= 2):<\/em><\/span>#BO2 = price_calculation_df['Ship Right'] = 0<\/em><\/span>BO2[:5]<\/span><\/pre>\n
Out[67]:<\/p>\n
[0, 0, 0, 0, 0]<\/span><\/pre>\n
In [60]:<\/p>\n
# F2[2531]<\/em><\/span><\/pre>\n
In [68]:<\/p>\n
# if the product will be discontinued soon, we are not selling.<\/em><\/p>\n
# will set status = 2. initially will set Is Normal = No = 0<\/em><\/p>\n
In [69]:<\/p>\n
BM2 =<\/strong> price_calculation_df['Soon Discontinued'] =<\/strong> raw_df['Soon Discontinued']<\/span>BP2 =<\/strong> price_calculation_df['Is Normal'] =<\/strong> 0<\/span>vis =<\/strong> []<\/span>for<\/strong> x in<\/strong> BM2:<\/span>if<\/strong> x ==<\/strong> 'Soon Discontinued':<\/span>vis.append(0)<\/span>else<\/strong>:<\/span>vis.append(1)<\/span>BP2 =<\/strong> price_calculation_df['Is Normal'] =<\/strong> vis<\/span>BP2[10603:10610]<\/span><\/pre>\n
Out[69]:<\/p>\n
[0, 1, 1, 1, 1, 0, 0]<\/span><\/pre>\n
In [70]:<\/p>\n
'''<\/span>BH2 = [1] * len(BN2)<\/span>for index in range(0, len(BN2) ):<\/span>if (BN2[index]==1):<\/span>if (BO2[index]==1):<\/span>if (BP2[index]==1):<\/span>if (AZ2[index]==1):<\/span>BH2[index] =  1<\/span>price_calculation_df['status'][index] = 1<\/span>'''<\/span><\/pre>\n
Out[70]:<\/p>\n
\"\\nBH2 = [1] * len(BN2)\\nfor index in range(0, len(BN2) ):\\n    if (BN2[index]==1):\\n        if (BO2[index]==1):\\n            if (BP2[index]==1):\\n                if (AZ2[index]==1):\\n                    BH2[index] =  1\\n                    price_calculation_df['status'][index] = 1\\n\\n\"<\/span><\/pre>\n
In [71]:<\/p>\n
# set status for some or until now. Status = 2 = disabled<\/em><\/p>\n
# status = enabled if all conditions are right i.e. BN2 and BO2 and BP2 and AZ2<\/em><\/span># i.e. price right, ship right, weight right, not discontinued<\/em><\/span>BH2 =<\/strong> BN2 and<\/strong> BO2 and<\/strong> BP2 and<\/strong> AZ2<\/span>BH2.replace(0, 2, inplace=True<\/strong>)<\/span>#sorted(BH2, reverse=True)<\/em><\/span>price_calculation_df['status'] =<\/strong> BH2<\/span><\/pre>\n
In [72]:<\/p>\n
# keep track of supplier retail price (recommended retail) and retail+shipping price<\/em><\/span>BI2 =<\/strong> price_calculation_df['Retail Price'] =<\/strong> raw_df['Retail Price']<\/span><\/pre>\n
In [73]:<\/p>\n
BH2[1]  , raw_df[1:2]['Model Code']<\/span><\/pre>\n
Out[73]:<\/p>\n
(2, 1    A01AL3303110\n Name: Model Code, dtype: object)<\/span><\/pre>\n
In [74]:<\/p>\n
price_calculation_df['Retail Price'] =<\/strong> price_calculation_df['Retail Price'].astype(str)<\/span><\/pre>\n
In [75]:<\/p>\n
#list(price_calculation_df['Retail Price'].unique()[0:len(price_calculation_df['Retail Price'])+1])<\/em><\/span>BI2 =<\/strong> price_calculation_df['Retail Price'] =<\/strong> price_calculation_df['Retail Price'].str.replace(',','')<\/span>#max(sorted(price_calculation_df['Retail Price'].astype(str)))<\/em><\/span>BK2 =<\/strong> price_calculation_df['R+shipp'] =<\/strong> BI2.astype(float) +<\/strong> F2.astype(float) #price_calculation_df['Retail Price'] + price_calculation_df['cheapest_courier_shipping']<\/em><\/span><\/pre>\n
In [76]:<\/p>\n
BK2[:5]<\/span><\/pre>\n
Out[76]:<\/p>\n
0    516.77\n1    517.48\n2    513.00\n3    512.92\n4    513.97\ndtype: float64<\/span><\/pre>\n
In [77]:<\/p>\n
#F2<\/em><\/span># compare our retail shop = magento price with supplier recommended retail price<\/em><\/span>BL2 =<\/strong> price_calculation_df['Shop vs retail'] =<\/strong> Y2 -<\/strong> BK2 #round(W2+L2*0.15,22) #- price_calculation_df['Retail Price'] #- price_calculation_df['cheapest_courier_shipping']<\/em><\/span><\/pre>\n
Out[77]:<\/p>\n
(0    234.68\n 1    234.97\n 2    234.65\n 3    234.60\n 4    234.74\n dtype: float64, 0    691.10\n 1    692.04\n 2    687.49\n 3    687.38\n 4    688.51\n dtype: float64, 0    486.61\n 1    487.28\n 2    484.07\n 3    483.99\n 4    484.79\n dtype: float64)<\/span><\/pre>\n
In [78]:<\/p>\n
# calculate prices for Amazon<\/em><\/span><\/pre>\n
In [79]:<\/p>\n
BQ2 =<\/strong> price_calculation_df['amazon_ca_minimum_price'] =<\/strong> round(AJ2-<\/strong>H2-<\/strong>K2-<\/strong>I2-<\/strong>P2-<\/strong>S2,2)<\/span>usd_rate =<\/strong> 0.761242<\/span>BR2 =<\/strong> price_calculation_df['amazon_usd_minimum_price'] =<\/strong> round(BQ2 *<\/strong> cad_to_usd, 2)<\/span>BL2[:5], BQ2[:5], BR2[:5]<\/span><\/pre>\n
Out[79]:<\/p>\n
(0    234.68\n 1    234.97\n 2    234.65\n 3    234.60\n 4    234.74\n dtype: float64, 0    691.10\n 1    692.04\n 2    687.49\n 3    687.38\n 4    688.51\n dtype: float64, 0    486.61\n 1    487.28\n 2    484.07\n 3    483.99\n 4    484.79\n dtype: float64)<\/span><\/pre>\n
In [80]:<\/p>\n
BS2 =<\/strong> price_calculation_df['amazon_gbp_minimum_price'] =<\/strong> round(BQ2 *<\/strong> cad_to_gbp, 2)<\/span>BT2 =<\/strong> price_calculation_df['amazon_eu_minimum_price'] =<\/strong> round(BQ2 *<\/strong> cad_to_euro, 2)<\/span>BU2 =<\/strong> price_calculation_df['amazon_mxn_minimum'] =<\/strong> round(BQ2 *<\/strong> cad_to_mxn, 2)<\/span>\"\"\"<\/span>E2 = [500] * len(raw_df)<\/span>min_e2_f2 = E2<\/span>if F2.any() < min_e2_f2.any():<\/span>min_e2_f2 = F2<\/span>\"\"\"<\/span>#BV2 = price_calculation_df['amazon_ca_abs_minimum'] = round(BQ2-R2+Q2-F2+ F2 )<\/em><\/span>BW2 =<\/strong> price_calculation_df['expedited_cost'] =<\/strong> 45<\/span>BU2[1]#[:5], BT2[:5], BU2[:5], BW2[:5]<\/em><\/span><\/pre>\n
Out[80]:<\/p>\n
12174.02<\/span><\/pre>\n
In [81]:<\/p>\n
BV2 =<\/strong> price_calculation_df['amazon_ca_abs_minimum'] =<\/strong> round ( BQ2 -<\/strong> R2 +<\/strong> Q2, 2)<\/span>BV2[:5]<\/span><\/pre>\n
Out[81]:<\/p>\n
0    625.31\n1    626.16\n2    622.03\n3    621.93\n4    622.96\ndtype: float64<\/span><\/pre>\n
In [ ]:<\/p>\n
# output Price Calculation<\/em><\/span><\/pre>\n
In [82]:<\/p>\n
output_folder =<\/strong> '.\/data-supplier-2020-04-14\/output\/'<\/span><\/pre>\n
In [83]:<\/p>\n
price_calculation_df.to_csv( output_folder +<\/strong> 'calculated_price_' +<\/strong> input_file_name +<\/strong> today +<\/strong> '.csv')<\/span><\/pre>\n
In [84]:<\/p>\n
df_calculated =<\/strong> pd.read_csv(output_folder +<\/strong> 'calculated_price_' +<\/strong> input_file_name +<\/strong> today +<\/strong> '.csv')<\/span>df_calculated.head(2)<\/span><\/pre>\n
Out[84]:<\/p>\n
Unnamed: 0product_idskuNameraw_pricePostal Shipping Pricecheapest_courier_shippingproduct_eanShip RiskPrice Risk\u2026Retail PriceR+shippShop vs retailamazon_ca_minimum_priceamazon_usd_minimum_priceamazon_gbp_minimum_priceamazon_eu_minimum_priceamazon_mxn_minimumexpedited_costamazon_ca_abs_minimum0030399shopforsoul-A01AL3301111Black 3x3x3 MoYu AoLong V2 Puzzle-A01AL330111112.90500.0500.06.941378e+1215.00.3870\u202616.77516.77234.68691.10486.61396.45450.3612157.4945625.311138649shopforsoul-A01AL3303110Qiyun AoLong V2 3x3x3 Speed Cube Enhanced Edit\u202613.61500.0500.06.941378e+1215.00.4083\u202617.48517.48234.97692.04487.28396.99450.9712174.0245626.16<\/p>\n
2 rows \u00d7 75 columns<\/p>\n
In [85]:<\/p>\n
price_calculation_df[['product_id', 'sku', 'Name']][:5]<\/span><\/pre>\n
Out[85]:<\/p>\n
product_idskuName030399shopforsoul-A01AL3301111Black 3x3x3 MoYu AoLong V2 Puzzle-A01AL3301111138649shopforsoul-A01AL3303110Qiyun AoLong V2 3x3x3 Speed Cube Enhanced Edit\u2026239296shopforsoul-A01CL3301111New , 5.46cm Smaller Sized Fangshi (Funs) Shua\u2026337397shopforsoul-A01CL3302113Fangshi Shuanren 3 x 3 x 3 with Sticker 54.6 m\u2026438354shopforsoul-A01CL3302120FangShi 3x3x3 Speed Cube Puzzle White Fully As\u2026<\/p>\n
# # Calculate sell prices (and related) for ebay<\/span># In[46]:<\/span>ebay_profit_tax = U2 * income_tax\nAB2 = price_calculation_df[\u2018ebay_cost\u2019] = round(L2 + N2 + O2 + P2 + S2 + ebay_profit_tax, 2)\nW2[:5], AB2[:5]\nL2[:1], M2[:1], N2[:1], P2[:1], S2[:1], V2[:1]<\/span># In[47]:<\/span>AC2 = price_calculation_df[\u2018ebay_list_price\u2019] = round(AB2 + U2 -F2, 2) # \u2014 F2, 2)\nAC2[:5]<\/span># In[48]:<\/span>AD2 = price_calculation_df[\u2018Buyer pay from Mall\u2019] = round(Y2+R2,2)<\/span># In[49]:<\/span>AE2 = price_calculation_df[\u2018Ebay Buyers Pay\u2019] = round(AC2+R2+F2,2)\nAF2 = price_calculation_df[\u2018Ebay vs Mall\u2019] = round(AE2-AD2, 2)<\/span># # account for amazon selling cost and calculate your sell price for amazon\n# #amazon has sales commission to pay\n# #you might want to account for monthly subscription fee (based on how much you sell in a month and how much subscription fee you pay)<\/span># In[50]:<\/span># possible income tax\nAG2 = price_calculation_df[\u2018NA-1\u2019] = round(W2-R2+L2*0.2,2)<\/span>AI2 = price_calculation_df[\u2018amazon_profit\u2019] = round(L2*amazon_profit, 2)\nAJ2 = price_calculation_df[\u2018amazon_our_cost\u2019] = round(L2+S2+P2+R2+AI2*income_tax,2)\nAH2 = price_calculation_df[\u2018Amazon Final Value Fee\u2019] = round( (AI2 + AJ2) * amazon_final_fee_rate, 2)\nAK2 = price_calculation_df[\u2018amazon_list_price\u2019] = round(AJ2+AI2+AH2, 2)\nAL2 = price_calculation_df[\u2018amazon_special_price\u2019] = round(L2*amazon_special_profit+AJ2+(L2*amazon_special_profit+AJ2)*income_tax+L2*amazon_special_profit*income_tax-L2*amazon_profit*income_tax, 2)\nAM2 = price_calculation_df[\u2018Amazon Final Value Fee USD\u2019] = round(AH2*cad_to_usd, 2)\nAN2 = price_calculation_df[\u2018amazon_profit_usd\u2019] = round(AI2*cad_to_usd, 2)\nAO2 = price_calculation_df[\u2018amazon_our_cost_usd\u2019] = round(AJ2*cad_to_usd, 2)\nAP2 = price_calculation_df[\u2018amazon_list_price_usd\u2019] = round(AK2*cad_to_usd, 2)\nAQ2 = price_calculation_df[\u2018amazon_special_price_usd\u2019] = round(AL2*cad_to_usd, 2)<\/span># In[51]:<\/span>AE2[:2], AF2[:2] , AG2[:2] , AH2[:2] , AI2[:2] , AJ2[:2] , AK2[:2] , \nAL2[:2] , AM2[:2] , AN2[:2] , AO2[:2] , AP2[:2] , AQ2[:2]<\/span># In[52]:<\/span>AR2 = price_calculation_df[\u2018amazon_list_price_gbp\u2019] = round(AK2*cad_to_gbp, 2)\nAS2 = price_calculation_df[\u2018amazon_special_price_gbp\u2019] = round(AL2*cad_to_gbp, 2)\nAT2 = price_calculation_df[\u2018amazon_list_price_eu\u2019] = round(AK2*cad_to_euro,2)\nAU2 = price_calculation_df[\u2018amazon_special_price_eu\u2019] = round(AL2*cad_to_euro, 2)\nAV2 = price_calculation_df[\u2018amazon_list_price_mxn\u2019] = round(AK2*cad_to_mxn, 2)\nAW2 = price_calculation_df[\u2018amazon_special_price_mxn\u2019] = round(AL2*cad_to_mxn, 2)<\/span>AR2[:2], AS2[:2], AT2[:2], AU2[:2], AV2[:2], AW2[:2]<\/span># In[53]:<\/span>#D2 = 1\nBD2 = raw_df[\u2018Weight Kg\u2019] \nAX2 = price_calculation_df[\u2018ama_safe_refund_amount\u2019] = round(AL2-H2-I2-K2-S2-P2-R2-AH2-D2\u201318, 2)\nAY2 = price_calculation_df[\u2018Weight\u2019] = round(BD2*2.2, 2)\nBJ2 = price_calculation_df[\u2018original in stock\u2019] = raw_df[\u2018Stock status\u2019]\nAZ2 = price_calculation_df[\u2018Stock status\u2019] = (BJ2==\u201dIn Stock\u201d).astype(int)\n#AZ2 = AZ2.astype(int)\nAX2[:2], AY2[:2], AZ2[:2], BJ2[:2], BD2[:2]<\/span># In[55]:<\/span>AY2[:5]<\/span># In[56]:<\/span>min_raw_price_to_show_on_amazon = 10\nBA2 = price_calculation_df[\u2018categories\u2019] = raw_df[\u2018Category Name\u2019] + \u2018\/\u2019 + raw_df[\u2018Subcategory Name\u2019]\nBB2 = price_calculation_df[\u2018_root_category\u2019] = \u2018All\u2019\nBC2 = price_calculation_df[\u2018visibility\u2019] = 4<\/span>vis = []\nfor x in D2:\n if x < min_raw_price_to_show_on_amazon:\n #vis.append(1)\n # temporary \n vis.append(\u2018Catalog, Search\u2019)\n else:\n vis.append(\u2018Catalog, Search\u2019) \nBC2 = price_calculation_df[\u2018visibility\u2019] = vis\n# temporary\n# BC2 = price_calculation_df[\u2018visibility\u2019] = 4<\/span># In[57]:<\/span>BC2[:5]<\/span># In[61]:<\/span># take width height from supplier to push to Amazon<\/span># In[62]:<\/span>BE2 = price_calculation_df[\u2018Height mm\u2019] = raw_df[\u2018Height mm\u2019]\nBF2 = price_calculation_df[\u2018Width mm\u2019] = raw_df[\u2018Width mm\u2019]\nBG2 = price_calculation_df[\u2018Depth mm\u2019] = raw_df[\u2018Depth mm\u2019]<\/span># In[64]:<\/span># We do not want to sell products that has weight > 40 lbs\n# status = 2 = disabled on Amazon\n# based on \u2018weight right\u2019 products will be disabled or enabled<\/span># In[65]:<\/span># default value\nBH2 = price_calculation_df[\u2018status\u2019] = 2\nBN2 = price_calculation_df[\u2018weight right\u2019] = 0<\/span>vis = []\nfor x in AY2:\n if x <= 20:\n vis.append(1)\n else:\n vis.append(0)<\/span>BN2 = price_calculation_df[\u2018weight right\u2019] = vis\nBN2[:5]<\/span># In[66]:<\/span># based on shipping cost, you might not want to sell products. \n# we will not sell if the shipping cost is more than $40<\/span># In[67]:<\/span>vis = []\nfor x in F2.astype(float):\n if x >= 40:\n vis.append(0)\n else:\n vis.append(1)\n \nBO2 = price_calculation_df[\u2018Ship Right\u2019] = vis\n#if (F2 >= 40) and (D2\/F2 <= 2):\n #BO2 = price_calculation_df[\u2018Ship Right\u2019] = 0\n \nBO2[:5]<\/span># In[60]:<\/span># F2[2531]<\/span># In[68]:<\/span># if the product will be discontinued soon, we are not selling.\n# will set status = 2. initially will set Is Normal = No = 0<\/span># In[69]:<\/span>BM2 = price_calculation_df[\u2018Soon Discontinued\u2019] = raw_df[\u2018Soon Discontinued\u2019] \nBP2 = price_calculation_df[\u2018Is Normal\u2019] = 0<\/span>vis = []\nfor x in BM2:\n if x == \u2018Soon Discontinued\u2019:\n vis.append(0)\n else:\n vis.append(1)\n \nBP2 = price_calculation_df[\u2018Is Normal\u2019] = vis \nBP2[10603:10610]<\/span># In[70]:<\/span>\u2018\u2019\u2019\nBH2 = [1] * len(BN2)\nfor index in range(0, len(BN2) ):\n if (BN2[index]==1):\n if (BO2[index]==1):\n if (BP2[index]==1):\n if (AZ2[index]==1):\n BH2[index] = 1\n price_calculation_df[\u2018status\u2019][index] = 1<\/span>\u2018\u2019\u2019<\/span># In[71]:<\/span># set status for some or until now\n# status = enabled if all conditions are right i.e. BN2 and BO2 and BP2 and AZ2\n# i.e. price right, ship right, weight right, not discontinued\nBH2 = BN2 and BO2 and BP2 and AZ2\nBH2.replace(0, 2, inplace=True)\n#sorted(BH2, reverse=True)\nprice_calculation_df[\u2018status\u2019] = BH2<\/span># In[72]:<\/span># keep track of supplier retail price (recommended retail) and retail+shipping price\nBI2 = price_calculation_df[\u2018Retail Price\u2019] = raw_df[\u2018Retail Price\u2019]<\/span># In[73]:<\/span>BH2[1] , raw_df[1:2][\u2018Model Code\u2019]<\/span># In[74]:<\/span>price_calculation_df[\u2018Retail Price\u2019] = price_calculation_df[\u2018Retail Price\u2019].astype(str)<\/span># In[75]:<\/span>#list(price_calculation_df[\u2018Retail Price\u2019].unique()[0:len(price_calculation_df[\u2018Retail Price\u2019])+1])\nBI2 = price_calculation_df[\u2018Retail Price\u2019] = price_calculation_df[\u2018Retail Price\u2019].str.replace(\u2018,\u2019,\u2019\u2019)<\/span>#max(sorted(price_calculation_df[\u2018Retail Price\u2019].astype(str)))\nBK2 = price_calculation_df[\u2018R+shipp\u2019] = BI2.astype(float) + F2.astype(float) #price_calculation_df[\u2018Retail Price\u2019] + price_calculation_df[\u2018cheapest_courier_shipping\u2019]<\/span># In[76]:<\/span>BK2[:5]<\/span># In[77]:<\/span>#F2\n# compare our retail shop = magento price with supplier recommended retail price\nBL2 = price_calculation_df[\u2018Shop vs retail\u2019] = Y2 \u2014 BK2 #round(W2+L2*0.15,22) #- price_calculation_df[\u2018Retail Price\u2019] #- price_calculation_df[\u2018cheapest_courier_shipping\u2019]<\/span># In[78]:<\/span># calculate prices for Amazon<\/span># In[79]:<\/span>BQ2 = price_calculation_df[\u2018amazon_ca_minimum_price\u2019] = round(AJ2-H2-K2-I2-P2-S2,2)\nusd_rate = 0.761242\nBR2 = price_calculation_df[\u2018amazon_usd_minimum_price\u2019] = round(BQ2 * cad_to_usd, 2)\nBL2[:5], BQ2[:5], BR2[:5]<\/span># In[80]:<\/span>BS2 = price_calculation_df[\u2018amazon_gbp_minimum_price\u2019] = round(BQ2 * cad_to_gbp, 2)\nBT2 = price_calculation_df[\u2018amazon_eu_minimum_price\u2019] = round(BQ2 * cad_to_euro, 2)\nBU2 = price_calculation_df[\u2018amazon_mxn_minimum\u2019] = round(BQ2 * cad_to_mxn, 2)<\/span>\u201c\u201d\u201d\nE2 = [500] * len(raw_df)\nmin_e2_f2 = E2\nif F2.any() < min_e2_f2.any():\n min_e2_f2 = F2\n\u201c\u201d\u201d<\/span>#BV2 = price_calculation_df[\u2018amazon_ca_abs_minimum\u2019] = round(BQ2-R2+Q2-F2+ F2 )\nBW2 = price_calculation_df[\u2018expedited_cost\u2019] = 45\nBU2[1]#[:5], BT2[:5], BU2[:5], BW2[:5]<\/span># In[81]:<\/span>BV2 = price_calculation_df[\u2018amazon_ca_abs_minimum\u2019] = round ( BQ2 \u2014 R2 + Q2, 2) \nBV2[:5]<\/span># In[ ]:<\/span># output Price Calculation<\/span># In[82]:<\/span>output_folder = \u2018.\/data-supplier-2020\u201304\u201314\/output\/\u2019<\/span># In[83]:<\/span>price_calculation_df.to_csv( output_folder + \u2018calculated_price_\u2019 + input_file_name + today + \u2018.csv\u2019)<\/span># In[84]:<\/span>df_calculated = pd.read_csv(output_folder + \u2018calculated_price_\u2019 + input_file_name + today + \u2018.csv\u2019)\ndf_calculated.head(2)<\/span># In[85]:<\/span>price_calculation_df[[\u2018product_id\u2019, \u2018sku\u2019, \u2018Name\u2019]][:5]<\/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<\/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