Read the code comments as well. Comments explained the sections.

In [86]:

# calculate/adjust data so that we can create magento 2 upload files

# csv file to upload to Magento 2

In [88]:

# load the template : i.e. columns required for magento 2 upload

magento_2_upload_template_file = template_folder  + 'magento2_catalog_product.csv' #'all-to-upload-without-description.csv'magento_2_upload_template_file = pd.read_csv(magento_2_upload_template_file)

In [89]:

magento_2_upload_template_file.columns

Out[89]:

Index(['sku', 'store_view_code', 'attribute_set_code', 'product_type',
       'categories', 'product_websites', 'name', 'description',
       'short_description', 'weight', 'product_online', 'tax_class_name',
       'visibility', 'price', 'special_price', 'special_price_from_date',
       'special_price_to_date', 'url_key', 'meta_title', 'meta_keywords',
       'meta_description', 'created_at', 'updated_at', 'new_from_date',
       'new_to_date', 'display_product_options_in', 'map_price', 'msrp_price',
       'map_enabled', 'gift_message_available', 'custom_design',
       'custom_design_from', 'custom_design_to', 'custom_layout_update',
       'page_layout', 'product_options_container',
       'msrp_display_actual_price_type', 'country_of_manufacture',
       'additional_attributes', 'qty', 'out_of_stock_qty',
       'use_config_min_qty', 'is_qty_decimal', 'allow_backorders',
       'use_config_backorders', 'min_cart_qty', 'use_config_min_sale_qty',
       'max_cart_qty', 'use_config_max_sale_qty', 'is_in_stock',
       'notify_on_stock_below', 'use_config_notify_stock_qty', 'manage_stock',
       'use_config_manage_stock', 'use_config_qty_increments',
       'qty_increments', 'use_config_enable_qty_inc', 'enable_qty_increments',
       'is_decimal_divided', 'website_id', 'deferred_stock_update',
       'use_config_deferred_stock_update', 'related_skus', 'crosssell_skus',
       'upsell_skus', 'hide_from_product_page', 'custom_options',
       'bundle_price_type', 'bundle_sku_type', 'bundle_price_view',
       'bundle_weight_type', 'bundle_values', 'associated_skus'],
      dtype='object')

In [90]:

price_calculation_df.columns

Out[90]:

Index(['product_id', 'sku', 'Name', 'raw_price', 'Postal Shipping Price',
       '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_profit', 'amazon_our_cost', 'Amazon Final Value Fee',
       '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',
       'original in stock', 'Stock status', 'categories', '_root_category',
       'visibility', 'Height mm', 'Width mm', 'Depth mm', 'status',
       'weight right', 'Ship Right', 'Soon Discontinued', 'Is Normal',
       'Retail Price', 'R+shipp', 'Shop vs retail', 'amazon_ca_minimum_price',
       'amazon_usd_minimum_price', 'amazon_gbp_minimum_price',
       'amazon_eu_minimum_price', 'amazon_mxn_minimum', 'expedited_cost',
       'amazon_ca_abs_minimum'],
      dtype='object')

In [91]:

price_calculation_df.rename(columns={'Height mm': 'height'}, inplace=True)price_calculation_df.rename(columns={'Depth mm': 'length', 'Width mm': 'width'}, inplace=True)price_calculation_df.rename(columns={'Special List Price':'special_price', 'MLP':'price', 'Stock status':'is_in_stock'}, inplace=True)#amazon_our_cost_usdprice_calculation_df.rename(columns={'Name':'name', 'Weight':'weight'}, inplace=True)  #, 'use_config_min_qty', 'visibility'

In [92]:

# set some default values that you want to upload to Magento 2

# default values are in list format so that it can be aligned with other data

In [93]:

type = ['simple'] * price_calculation_df.shape[0]_attribute_set = ['Default'] * price_calculation_df.shape[0]qty = [10] * price_calculation_df.shape[0]min_qty = [1] * price_calculation_df.shape[0]use_config_min_qty = [1] * price_calculation_df.shape[0]is_qty_decimal = [0] * price_calculation_df.shape[0]backorders = [0] * price_calculation_df.shape[0]use_config_backorders = [1] * price_calculation_df.shape[0]min_sale_qty = [1] * price_calculation_df.shape[0]use_config_min_sale_qty = [1] * price_calculation_df.shape[0]max_sale_qty = [2] * price_calculation_df.shape[0]use_config_max_sale_qty = [1] * price_calculation_df.shape[0]notify_stock_qty = [0] * price_calculation_df.shape[0]use_config_notify_stock_qty = [1] * price_calculation_df.shape[0]manage_stock = [0] * price_calculation_df.shape[0]use_config_manage_stock = [1] * price_calculation_df.shape[0]stock_status_changed_auto = [0] * price_calculation_df.shape[0]use_config_qty_increments = [1] * price_calculation_df.shape[0]qty_increments = [0] * price_calculation_df.shape[0]use_config_enable_qty_inc = [1] * price_calculation_df.shape[0]enable_qty_increments = [0] * price_calculation_df.shape[0]is_decimal_divided = [0] * price_calculation_df.shape[0]_media_is_disabled = [0] * price_calculation_df.shape[0]tax_class_id = [2] * price_calculation_df.shape[0]

In [94]:

# creating dataframe to store data to upload to Magento 2

# this dataframe will be saved to a csv file

In [95]:

magento_2_upload_file_with_data_no_desc = price_calculation_df[['sku',#'store_view_code',#'attribute_set_code',#'product_type',#'categories',#'product_websites', 'name',#'description',#'short_description','weight',#'product_online',#'tax_class_name',#'visibility','price','special_price',#'special_price_from_date',#'special_price_to_date',#'url_key',#'meta_title',#'meta_keywords',#'meta_description',#'created_at',#'updated_at',#'new_from_date',#'new_to_date',#'display_product_options_in',#'map_price', 'msrp_price',#'map_enabled',#'gift_message_available',#'custom_design',#'custom_design_from',#'custom_design_to',#'custom_layout_update',#'page_layout',#'product_options_container',#'msrp_display_actual_price_type',#'country_of_manufacture',#'additional_attributes',#'qty',#'out_of_stock_qty',#'use_config_min_qty',#'is_qty_decimal',#'allow_backorders',#'use_config_backorders',#'min_cart_qty',#'use_config_min_sale_qty',#'max_cart_qty',#'use_config_max_sale_qty','is_in_stock',#'notify_on_stock_below',#'use_config_notify_stock_qty',#'manage_stock',#'use_config_manage_stock',#'use_config_qty_increments',#'qty_increments',#'use_config_enable_qty_inc',#'enable_qty_increments',#'is_decimal_divided',#'website_id',#'deferred_stock_update',#'use_config_deferred_stock_update',#'related_skus',#'crosssell_skus',#'upsell_skus',#'hide_from_product_page',#'custom_options',#'bundle_price_type', 'bundle_sku_type', 'bundle_price_view',#'bundle_weight_type', 'bundle_values', 'associated_skus']]magento_2_upload_file_with_data_no_desc = price_calculation_df[['product_id', 'sku', 'name', 'raw_price',#'dummy','cheapest_courier_shipping', 'product_ean', 'shopforsoul_profit','ebay_profit', 'shopforsoul_cost', 'price', 'special_price','special_from_date', 'special_to_date', 'ebay_cost', 'ebay_list_price','amazon_profit','amazon_our_cost', 'amazon_list_price','amazon_special_price', 'amazon_list_price_usd','amazon_special_price_usd', 'amazon_list_price_gbp','amazon_special_price_gbp', 'amazon_list_price_eu','amazon_special_price_eu', 'weight', 'is_in_stock', 'categories',#'_root_category','visibility','height', 'width','length','status',#'_type', '_attribute_set', 'qty', 'min_qty',#'use_config_min_qty',#'is_qty_decimal', 'backorders',#'use_config_backorders', 'min_sale_qty', 'use_config_min_sale_qty',#'max_sale_qty', 'use_config_max_sale_qty', 'notify_stock_qty',#'use_config_notify_stock_qty', 'manage_stock',#'use_config_manage_stock', 'stock_status_changed_auto',#'use_config_qty_increments', 'qty_increments',#'use_config_enable_qty_inc', 'enable_qty_increments',#'is_decimal_divided', '_media_is_disabled',#'tax_class_id']]

In [ ]:

magento_2_upload_file_with_data_no_desc

In [ ]:

#df_new_template['categories']

In [96]:

# set some more default values for magento 2

In [97]:

magento_2_upload_file_with_data_no_desc['attribute_set_code'] = 'Default'#magento_2_upload_file_with_data_no_desc['categories'] =#magento_2_upload_file_with_data_no_desc['height'] =#magento_2_upload_file_with_data_no_desc['length'] =magento_2_upload_file_with_data_no_desc['product_online'] = 1magento_2_upload_file_with_data_no_desc['product_type'] = 'simple'magento_2_upload_file_with_data_no_desc['product_websites'] = 'base'magento_2_upload_file_with_data_no_desc['root_category'] = 'All'magento_2_upload_file_with_data_no_desc['store_view_code'] = ''magento_2_upload_file_with_data_no_desc['tax_class_name'] = 'Taxable Goods'#magento_2_upload_file_with_data_no_desc['width'] =

take the default values set before in the section marked as

Check couple of steps up “

#set some default values that you want to upload to Magento 2

#default values are in list format so that it can be aligned with other data “

In [99]:

magento_2_upload_file_with_data_no_desc#magento_2_upload_file_with_data_no_desc['_type'] = typemagento_2_upload_file_with_data_no_desc['_attribute_set'] = _attribute_setmagento_2_upload_file_with_data_no_desc['qty'] = qtymagento_2_upload_file_with_data_no_desc['min_qty'] = min_qtymagento_2_upload_file_with_data_no_desc['use_config_min_qty'] = use_config_min_qtymagento_2_upload_file_with_data_no_desc['is_qty_decimal'] = is_qty_decimalmagento_2_upload_file_with_data_no_desc['backorders'] = backordersmagento_2_upload_file_with_data_no_desc['use_config_backorders'] = use_config_backordersmagento_2_upload_file_with_data_no_desc['min_sale_qty'] = min_sale_qtymagento_2_upload_file_with_data_no_desc['use_config_min_sale_qty'] = use_config_min_sale_qtymagento_2_upload_file_with_data_no_desc['max_sale_qty'] = max_sale_qtymagento_2_upload_file_with_data_no_desc['use_config_max_sale_qty'] = use_config_max_sale_qtymagento_2_upload_file_with_data_no_desc['notify_stock_qty'] = notify_stock_qtymagento_2_upload_file_with_data_no_desc['use_config_notify_stock_qty'] = use_config_notify_stock_qtymagento_2_upload_file_with_data_no_desc['manage_stock'] = manage_stockmagento_2_upload_file_with_data_no_desc['use_config_manage_stock'] = use_config_manage_stockmagento_2_upload_file_with_data_no_desc['stock_status_changed_auto'] = stock_status_changed_automagento_2_upload_file_with_data_no_desc['use_config_qty_increments'] = use_config_qty_incrementsmagento_2_upload_file_with_data_no_desc['qty_increments'] = qty_incrementsmagento_2_upload_file_with_data_no_desc['use_config_enable_qty_inc'] = use_config_enable_qty_incmagento_2_upload_file_with_data_no_desc['enable_qty_increments'] = enable_qty_incrementsmagento_2_upload_file_with_data_no_desc['is_decimal_divided'] = is_decimal_dividedmagento_2_upload_file_with_data_no_desc['_media_is_disabled'] = _media_is_disabledmagento_2_upload_file_with_data_no_desc['tax_class_id'] = tax_class_id

In [100]:

#magento_2_upload_file_with_data_no_desc['categories'] = magento_1_upload_file_with_data_no_desc['_category']

In [101]:

magento_2_upload_file_with_data_no_desc['visibility'] = BC2

In [102]:

#magento_1_upload_file_with_data_no_desc.drop( columns = "", axis=1)magento_2_upload_file_with_data_no_desc.columns

Out[102]:

Index(['product_id', 'sku', 'name', 'raw_price', 'cheapest_courier_shipping',
       'product_ean', 'shopforsoul_profit', 'ebay_profit', 'shopforsoul_cost',
       'price', 'special_price', 'special_from_date', 'special_to_date',
       'ebay_cost', 'ebay_list_price', 'amazon_profit', 'amazon_our_cost',
       'amazon_list_price', 'amazon_special_price', 'amazon_list_price_usd',
       'amazon_special_price_usd', 'amazon_list_price_gbp',
       'amazon_special_price_gbp', 'amazon_list_price_eu',
       'amazon_special_price_eu', 'weight', 'is_in_stock', 'categories',
       'visibility', 'height', 'width', 'length', 'status',
       'attribute_set_code', 'product_online', 'product_type',
       'product_websites', 'root_category', 'store_view_code',
       'tax_class_name', '_attribute_set', 'qty', 'min_qty',
       'use_config_min_qty', 'is_qty_decimal', 'backorders',
       'use_config_backorders', 'min_sale_qty', 'use_config_min_sale_qty',
       'max_sale_qty', 'use_config_max_sale_qty', 'notify_stock_qty',
       'use_config_notify_stock_qty', 'manage_stock',
       'use_config_manage_stock', 'stock_status_changed_auto',
       'use_config_qty_increments', 'qty_increments',
       'use_config_enable_qty_inc', 'enable_qty_increments',
       'is_decimal_divided', '_media_is_disabled', 'tax_class_id'],
      dtype='object')

In [103]:

# drop products with the same sku

In [104]:

#magento_1_upload_file_with_data_no_desc.drop(['categories'], inplace=True, axis=1)# sorting by first name#magento_1_upload_file_with_data_no_desc.sort_values("sku", inplace = True)# dropping ALL duplicte valuesmagento_2_upload_file_with_data_no_desc.drop_duplicates(subset ="sku", keep = False, inplace = True)

In [ ]:

# save the output file i.e. to load to magento 2

# no image data/url as part of the upload file

In [105]:

#output_folder = './data-supplier-20219-04-14/output/';magento_2_upload_file_with_data_no_desc.to_csv( output_folder + 'no-image-all_to_upload_without_description_' + input_file_name + '_magento_2_' + today + '.csv',index=False, encoding='utf8')

In [ ]:

# divide the output into multiple files

All code in one block. Still indenting might be required

# In[86]:# calculate/adjust data so that we can create magento 2 upload files
# csv file to upload to Magento 2# In[88]:# load the template : i.e. columns required for magento 2 upload
magento_2_upload_template_file = template_folder + ‘magento2_catalog_product.csv’ #’all-to-upload-without-description.csv’
magento_2_upload_template_file = pd.read_csv(magento_2_upload_template_file)# In[89]:magento_2_upload_template_file.columns# In[90]:price_calculation_df.columns# In[91]:price_calculation_df.rename(columns={‘Height mm’: ‘height’}, inplace=True)
price_calculation_df.rename(columns={‘Depth mm’: ‘length’, ‘Width mm’: ‘width’}, inplace=True)
price_calculation_df.rename(columns={‘Special List Price’:’special_price’, ‘MLP’:’price’, ‘Stock status’:’is_in_stock’}, inplace=True)
#amazon_our_cost_usd
price_calculation_df.rename(columns={‘Name’:’name’, ‘Weight’:’weight’}, inplace=True) #, ‘use_config_min_qty’, ‘visibility’# In[92]:# set some default values that you want to upload to Magento 2
# default values are in list format so that it can be aligned with other data# In[93]:type = [‘simple’] * price_calculation_df.shape[0]
_attribute_set = [‘Default’] * price_calculation_df.shape[0]
qty = [10] * price_calculation_df.shape[0]
min_qty = [1] * price_calculation_df.shape[0]
use_config_min_qty = [1] * price_calculation_df.shape[0]
is_qty_decimal = [0] * price_calculation_df.shape[0]
backorders = [0] * price_calculation_df.shape[0]
use_config_backorders = [1] * price_calculation_df.shape[0]
min_sale_qty = [1] * price_calculation_df.shape[0]
use_config_min_sale_qty = [1] * price_calculation_df.shape[0]
max_sale_qty = [2] * price_calculation_df.shape[0]
use_config_max_sale_qty = [1] * price_calculation_df.shape[0]
notify_stock_qty = [0] * price_calculation_df.shape[0]
use_config_notify_stock_qty = [1] * price_calculation_df.shape[0]
manage_stock = [0] * price_calculation_df.shape[0]
use_config_manage_stock = [1] * price_calculation_df.shape[0]
stock_status_changed_auto = [0] * price_calculation_df.shape[0]
use_config_qty_increments = [1] * price_calculation_df.shape[0]
qty_increments = [0] * price_calculation_df.shape[0]
use_config_enable_qty_inc = [1] * price_calculation_df.shape[0]
enable_qty_increments = [0] * price_calculation_df.shape[0]
is_decimal_divided = [0] * price_calculation_df.shape[0]
_media_is_disabled = [0] * price_calculation_df.shape[0]
tax_class_id = [2] * price_calculation_df.shape[0]# In[94]:# creatinf dataframe to store data to upload to Magento 2
# this dataframe will be saved to a csv file# In[95]:magento_2_upload_file_with_data_no_desc = price_calculation_df[[
 ‘sku’, 
 #’store_view_code’, 
 #’attribute_set_code’, 
 #’product_type’,
 #’categories’, 
 #’product_websites’, ‘name’, 
 #’description’,
 #’short_description’, 
 ‘weight’, 
 #’product_online’, 
 #’tax_class_name’,
 #’visibility’, 
 ‘price’, 
 ‘special_price’, 
 #’special_price_from_date’,
 #’special_price_to_date’, 
 #’url_key’, 
 #’meta_title’, 
 #’meta_keywords’,
 #’meta_description’, 
 #’created_at’, 
 #’updated_at’, 
 #’new_from_date’,
 #’new_to_date’, 
 #’display_product_options_in’, 
 #’map_price’, ‘msrp_price’,
 #’map_enabled’, 
 #’gift_message_available’, 
 #’custom_design’,
 #’custom_design_from’, 
 #’custom_design_to’, 
 #’custom_layout_update’,
 #’page_layout’, 
 #’product_options_container’,
 #’msrp_display_actual_price_type’, 
 #’country_of_manufacture’,
 #’additional_attributes’, 
 #’qty’, 
 #’out_of_stock_qty’,
 #’use_config_min_qty’, 
 #’is_qty_decimal’, 
 #’allow_backorders’,
 #’use_config_backorders’, 
 #’min_cart_qty’, 
 #’use_config_min_sale_qty’,
 #’max_cart_qty’, 
 #’use_config_max_sale_qty’, 
 ‘is_in_stock’,
 #’notify_on_stock_below’, 
 #’use_config_notify_stock_qty’, 
 #’manage_stock’,
 #’use_config_manage_stock’, 
 #’use_config_qty_increments’,
 #’qty_increments’, 
 #’use_config_enable_qty_inc’, 
 
 #’enable_qty_increments’,
 #’is_decimal_divided’, 
 #’website_id’, 
 #’deferred_stock_update’,
 #’use_config_deferred_stock_update’, 
 #’related_skus’, 
 #’crosssell_skus’,
 #’upsell_skus’, 
 #’hide_from_product_page’, 
 #’custom_options’,
 
 #’bundle_price_type’, ‘bundle_sku_type’, ‘bundle_price_view’,
 #’bundle_weight_type’, ‘bundle_values’, ‘associated_skus’
]]magento_2_upload_file_with_data_no_desc = price_calculation_df[[‘product_id’, ‘sku’, ‘name’, ‘raw_price’, 
 #’dummy’,
 ‘cheapest_courier_shipping’, ‘product_ean’, ‘shopforsoul_profit’,
 ‘ebay_profit’, ‘shopforsoul_cost’, ‘price’, ‘special_price’,
 ‘special_from_date’, ‘special_to_date’, ‘ebay_cost’, ‘ebay_list_price’,
 ‘amazon_profit’, 
 ‘amazon_our_cost’, ‘amazon_list_price’,
 ‘amazon_special_price’, ‘amazon_list_price_usd’,
 ‘amazon_special_price_usd’, ‘amazon_list_price_gbp’,
 ‘amazon_special_price_gbp’, ‘amazon_list_price_eu’,
 ‘amazon_special_price_eu’, ‘weight’, ‘is_in_stock’, ‘categories’,
 #’_root_category’, 
 ‘visibility’, 
 ‘height’, ‘width’,
 ‘length’, 
 ‘status’, 
 #’_type’, ‘_attribute_set’, ‘qty’, ‘min_qty’,
 #’use_config_min_qty’, 
 #’is_qty_decimal’, ‘backorders’,
 #’use_config_backorders’, ‘min_sale_qty’, ‘use_config_min_sale_qty’,
 #’max_sale_qty’, ‘use_config_max_sale_qty’, ‘notify_stock_qty’,
 #’use_config_notify_stock_qty’, ‘manage_stock’,
 #’use_config_manage_stock’, ‘stock_status_changed_auto’,
 #’use_config_qty_increments’, ‘qty_increments’,
 #’use_config_enable_qty_inc’, ‘enable_qty_increments’,
 #’is_decimal_divided’, ‘_media_is_disabled’, 
 #’tax_class_id’
]]# In[ ]:magento_2_upload_file_with_data_no_desc# In[ ]:#df_new_template[‘categories’]# In[96]:# set some more default values for magento 2# In[97]:magento_2_upload_file_with_data_no_desc[‘attribute_set_code’] = ‘Default’
#magento_2_upload_file_with_data_no_desc[‘categories’] = 
#magento_2_upload_file_with_data_no_desc[‘height’] = 
#magento_2_upload_file_with_data_no_desc[‘length’] = 
magento_2_upload_file_with_data_no_desc[‘product_online’] = 1
magento_2_upload_file_with_data_no_desc[‘product_type’] = ‘simple’
magento_2_upload_file_with_data_no_desc[‘product_websites’] = ‘base’
magento_2_upload_file_with_data_no_desc[‘root_category’] = ‘All’
magento_2_upload_file_with_data_no_desc[‘store_view_code’] = ‘’
magento_2_upload_file_with_data_no_desc[‘tax_class_name’] = ‘Taxable Goods’
#magento_2_upload_file_with_data_no_desc[‘width’] =# # take the default values set before in the section marked as 
# Check couple of steps up
# “
# #set some default values that you want to upload to Magento 2
# #default values are in list format so that it can be aligned with other data
# “# In[99]:magento_2_upload_file_with_data_no_desc
#magento_2_upload_file_with_data_no_desc[‘_type’] = type
magento_2_upload_file_with_data_no_desc[‘_attribute_set’] = _attribute_set
magento_2_upload_file_with_data_no_desc[‘qty’] = qty
magento_2_upload_file_with_data_no_desc[‘min_qty’] = min_qty
magento_2_upload_file_with_data_no_desc[‘use_config_min_qty’] = use_config_min_qty
magento_2_upload_file_with_data_no_desc[‘is_qty_decimal’] = is_qty_decimal
magento_2_upload_file_with_data_no_desc[‘backorders’] = backorders
magento_2_upload_file_with_data_no_desc[‘use_config_backorders’] = use_config_backorders
magento_2_upload_file_with_data_no_desc[‘min_sale_qty’] = min_sale_qty
magento_2_upload_file_with_data_no_desc[‘use_config_min_sale_qty’] = use_config_min_sale_qty
magento_2_upload_file_with_data_no_desc[‘max_sale_qty’] = max_sale_qty
magento_2_upload_file_with_data_no_desc[‘use_config_max_sale_qty’] = use_config_max_sale_qty
magento_2_upload_file_with_data_no_desc[‘notify_stock_qty’] = notify_stock_qty
magento_2_upload_file_with_data_no_desc[‘use_config_notify_stock_qty’] = use_config_notify_stock_qty
magento_2_upload_file_with_data_no_desc[‘manage_stock’] = manage_stock
magento_2_upload_file_with_data_no_desc[‘use_config_manage_stock’] = use_config_manage_stock
magento_2_upload_file_with_data_no_desc[‘stock_status_changed_auto’] = stock_status_changed_auto
magento_2_upload_file_with_data_no_desc[‘use_config_qty_increments’] = use_config_qty_increments
magento_2_upload_file_with_data_no_desc[‘qty_increments’] = qty_increments
magento_2_upload_file_with_data_no_desc[‘use_config_enable_qty_inc’] = use_config_enable_qty_inc
magento_2_upload_file_with_data_no_desc[‘enable_qty_increments’] = enable_qty_increments
magento_2_upload_file_with_data_no_desc[‘is_decimal_divided’] = is_decimal_divided
magento_2_upload_file_with_data_no_desc[‘_media_is_disabled’] = _media_is_disabled
magento_2_upload_file_with_data_no_desc[‘tax_class_id’] = tax_class_id# In[100]:#magento_2_upload_file_with_data_no_desc[‘categories’] = magento_1_upload_file_with_data_no_desc[‘_category’]# In[101]:magento_2_upload_file_with_data_no_desc[‘visibility’] = BC2# In[102]:#magento_1_upload_file_with_data_no_desc.drop( columns = “”, axis=1)
magento_2_upload_file_with_data_no_desc.columns# In[103]:# drop products with the same sku# In[104]:#magento_1_upload_file_with_data_no_desc.drop([‘categories’], inplace=True, axis=1)
# sorting by first name 
#magento_1_upload_file_with_data_no_desc.sort_values(“sku”, inplace = True) 
 
# dropping ALL duplicte values 
magento_2_upload_file_with_data_no_desc.drop_duplicates(subset =”sku”, 
 keep = False, inplace = True)# In[ ]:# save the output file i.e. to load to magento 2
# no image data/url as part of the upload file# In[105]:#output_folder = ‘data-china-2020–03–29/’
#output_folder = ‘./data-supplier-2020–04–14/output/’;
magento_2_upload_file_with_data_no_desc.to_csv( output_folder + ‘no-image-all_to_upload_without_description_’ + input_file_name + ‘_magento_2_’ + today + ‘.csv’,index=False, encoding=’utf8')# In[ ]:# divide the output into multiple files

Medium: https://medium.com/@SayedAhmedCanada

*** . *** *** . *** . *** . ***

Sayed Ahmed

BSc. Eng. in Comp. Sc. & Eng. (BUET)

MSc. in Comp. Sc. (U of Manitoba, Canada)

MSc. in Data Science and Analytics (Ryerson University, Canada)

Linkedin: https://ca.linkedin.com/in/sayedjustetc

Blog: http://Bangla.SaLearningSchool.com, http://SitesTree.com

Training Courses: http://Training.SitesTree.com

8112223 Canada Inc/Justetc: http://JustEtc.net

Facebook Groups/Forums to discuss (Q & A):

https://www.facebook.com/banglasalearningschool

https://www.facebook.com/justetcsocial

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. http://sitestree.com/training/

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.

Read the code comments as well. Comments explained the sections.

Calculate sell prices (and related) for ebay

In [46]:

ebay_profit_tax = U2 * income_taxAB2 = price_calculation_df['ebay_cost'] = round(L2 + N2 + O2 + P2 + S2 + ebay_profit_tax, 2)W2[:5], AB2[:5]L2[:1], M2[:1], N2[:1], P2[:1], S2[:1], V2[:1]

Out[46]:

(0    548.29
 dtype: float64, 0    54.829
 Name: Total with ship, dtype: float64, 0    16.7487
 Name: Total with ship, dtype: float64, 0    5.4829
 Name: Total with ship, dtype: float64, 0    16.4487
 Name: Total with ship, dtype: float64, 0    27.4145
 Name: shopforsoul_profit, dtype: float64)

In [47]:

AC2 = price_calculation_df['ebay_list_price'] = round(AB2 + U2 -F2, 2) # - F2, 2)AC2[:5]

Out[47]:

0    262.43
1    263.43
2    258.56
3    258.43
4    259.65
dtype: float64

In [48]:

AD2 = price_calculation_df['Buyer pay from Mall'] = round(Y2+R2,2)

In [49]:

AE2 = price_calculation_df['Ebay Buyers Pay'] = round(AC2+R2+F2,2)AF2 = price_calculation_df['Ebay vs Mall'] = round(AE2-AD2, 2)

account for amazon selling cost and calculate your sell price for amazon

#amazon has sales commission to pay

#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)

In [50]:

# possible income taxAG2 = price_calculation_df['NA-1'] = round(W2-R2+L2*0.2,2)AI2 = price_calculation_df['amazon_profit'] = round(L2*amazon_profit, 2)AJ2 = price_calculation_df['amazon_our_cost'] = round(L2+S2+P2+R2+AI2*income_tax,2)AH2 = price_calculation_df['Amazon Final Value Fee'] = round( (AI2 + AJ2) * amazon_final_fee_rate, 2)AK2 = price_calculation_df['amazon_list_price'] = round(AJ2+AI2+AH2, 2)AL2 = price_calculation_df['amazon_special_price'] = 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)AM2 = price_calculation_df['Amazon Final Value Fee USD'] = round(AH2*cad_to_usd, 2)AN2 = price_calculation_df['amazon_profit_usd'] = round(AI2*cad_to_usd, 2)AO2 = price_calculation_df['amazon_our_cost_usd'] = round(AJ2*cad_to_usd, 2)AP2 = price_calculation_df['amazon_list_price_usd'] = round(AK2*cad_to_usd, 2)AQ2 = price_calculation_df['amazon_special_price_usd'] = round(AL2*cad_to_usd, 2)

In [51]:

AE2[:2], AF2[:2] , AG2[:2] , AH2[:2] , AI2[:2] , AJ2[:2] , AK2[:2] ,AL2[:2] , AM2[:2] , AN2[:2] , AO2[:2] , AP2[:2] , AQ2[:2]

Out[51]:

(0    935.52
 1    936.76
 dtype: float64, 0    96.42
 1    96.55
 dtype: float64, 0    115.82
 1    115.97
 dtype: float64, 0    526.97
 1    527.67
 dtype: float64, 0    739.21
 1    740.19
 dtype: float64, 0    658.71
 1    659.59
 dtype: float64)

In [52]:

AR2 = price_calculation_df['amazon_list_price_gbp'] = round(AK2*cad_to_gbp, 2)AS2 = price_calculation_df['amazon_special_price_gbp'] = round(AL2*cad_to_gbp, 2)AT2 = price_calculation_df['amazon_list_price_eu'] = round(AK2*cad_to_euro,2)AU2 = price_calculation_df['amazon_special_price_eu'] = round(AL2*cad_to_euro, 2)AV2 = price_calculation_df['amazon_list_price_mxn'] = round(AK2*cad_to_mxn, 2)AW2 = price_calculation_df['amazon_special_price_mxn'] = round(AL2*cad_to_mxn, 2)AR2[:2], AS2[:2], AT2[:2], AU2[:2], AV2[:2], AW2[:2]

Out[52]:

(0    602.25
 1    603.04
 dtype: float64, 0    536.66
 1    537.37
 dtype: float64, 0    684.14
 1    685.05
 dtype: float64, 0    609.64
 1    610.45
 dtype: float64, 0    18468.44
 1    18492.89
 dtype: float64, 0    16457.20
 1    16479.01
 dtype: float64)

In [53]:

#D2 = 1BD2 = raw_df['Weight Kg']AX2 = price_calculation_df['ama_safe_refund_amount'] = round(AL2-H2-I2-K2-S2-P2-R2-AH2-D2-18, 2)AY2 = price_calculation_df['Weight'] = round(BD2*2.2, 2)BJ2 = price_calculation_df['original in stock'] = raw_df['Stock status']AZ2 = price_calculation_df['Stock status'] = (BJ2=="In Stock").astype(int)#AZ2 = AZ2.astype(int)AX2[:2], AY2[:2], AZ2[:2], BJ2[:2], BD2[:2]

Out[53]:

(0    573.29
 1    573.41
 dtype: float64, 0    0.09
 1    0.06
 Name: Weight Kg, dtype: float64, 0    0
 1    0
 Name: Stock status, dtype: int64, 0    Closed
 1       NaN
 Name: Stock status, dtype: object, 0    0.043
 1    0.025
 Name: Weight Kg, dtype: float64)

In [55]:

AY2[:5]

Out[55]:

0    0.09
1    0.06
2    0.09
3    0.09
4    0.09
Name: Weight Kg, dtype: float64

In [56]:

min_raw_price_to_show_on_amazon = 10BA2 = price_calculation_df['categories'] = raw_df['Category Name'] + '/' + raw_df['Subcategory Name']BB2 = price_calculation_df['_root_category'] = 'All'BC2 = price_calculation_df['visibility'] = 4vis = []for x in D2:if x < min_raw_price_to_show_on_amazon:#vis.append(1)# temporaryvis.append('Catalog, Search')else:vis.append('Catalog, Search')BC2 = price_calculation_df['visibility'] = vis# temporary# BC2 = price_calculation_df['visibility'] = 4

In [57]:

BC2[:5]

Out[57]:

['Catalog, Search',
 'Catalog, Search',
 'Catalog, Search',
 'Catalog, Search',
 'Catalog, Search']

In [61]:

# take width height from supplier to push to Amazon

In [62]:

BE2 = price_calculation_df['Height mm'] = raw_df['Height mm']BF2 = price_calculation_df['Width mm'] = raw_df['Width mm']BG2 = price_calculation_df['Depth mm'] = raw_df['Depth mm']

In [64]:

# We do not want to sell products that has weight > 40 lbs

# status = 2 = disabled on Amazon# based on 'weight right' products will be disabled or enabled

In [65]:

# default valueBH2 = price_calculation_df['status'] =  2BN2 = price_calculation_df['weight right'] = 0vis = []for x in AY2:if x <= 20:vis.append(1)else:vis.append(0)BN2 = price_calculation_df['weight right'] = visBN2[:5]

Out[65]:

[1, 1, 1, 1, 1]

In [66]:

# based on shipping cost, you might not want to sell products.

# we will not sell if the shipping cost is more than $40

In [67]:

vis = []for x in F2.astype(float):if x >= 40:vis.append(0)else:vis.append(1)BO2 = price_calculation_df['Ship Right'] = vis#if (F2 >= 40) and (D2/F2 <= 2):#BO2 = price_calculation_df['Ship Right'] = 0BO2[:5]

Out[67]:

[0, 0, 0, 0, 0]

In [60]:

# F2[2531]

In [68]:

# if the product will be discontinued soon, we are not selling.

# will set status = 2. initially will set Is Normal = No = 0

In [69]:

BM2 = price_calculation_df['Soon Discontinued'] = raw_df['Soon Discontinued']BP2 = price_calculation_df['Is Normal'] = 0vis = []for x in BM2:if x == 'Soon Discontinued':vis.append(0)else:vis.append(1)BP2 = price_calculation_df['Is Normal'] = visBP2[10603:10610]

Out[69]:

[0, 1, 1, 1, 1, 0, 0]

In [70]:

'''BH2 = [1] * len(BN2)for index in range(0, len(BN2) ):if (BN2[index]==1):if (BO2[index]==1):if (BP2[index]==1):if (AZ2[index]==1):BH2[index] =  1price_calculation_df['status'][index] = 1'''

Out[70]:

"\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"

In [71]:

# set status for some or until now. Status = 2 = disabled

# status = enabled if all conditions are right i.e. BN2 and BO2 and BP2 and AZ2# i.e. price right, ship right, weight right, not discontinuedBH2 = BN2 and BO2 and BP2 and AZ2BH2.replace(0, 2, inplace=True)#sorted(BH2, reverse=True)price_calculation_df['status'] = BH2

In [72]:

# keep track of supplier retail price (recommended retail) and retail+shipping priceBI2 = price_calculation_df['Retail Price'] = raw_df['Retail Price']

In [73]:

BH2[1]  , raw_df[1:2]['Model Code']

Out[73]:

(2, 1    A01AL3303110
 Name: Model Code, dtype: object)

In [74]:

price_calculation_df['Retail Price'] = price_calculation_df['Retail Price'].astype(str)

In [75]:

#list(price_calculation_df['Retail Price'].unique()[0:len(price_calculation_df['Retail Price'])+1])BI2 = price_calculation_df['Retail Price'] = price_calculation_df['Retail Price'].str.replace(',','')#max(sorted(price_calculation_df['Retail Price'].astype(str)))BK2 = price_calculation_df['R+shipp'] = BI2.astype(float) + F2.astype(float) #price_calculation_df['Retail Price'] + price_calculation_df['cheapest_courier_shipping']

In [76]:

BK2[:5]

Out[76]:

0    516.77
1    517.48
2    513.00
3    512.92
4    513.97
dtype: float64

In [77]:

#F2# compare our retail shop = magento price with supplier recommended retail priceBL2 = price_calculation_df['Shop vs retail'] = Y2 - BK2 #round(W2+L2*0.15,22) #- price_calculation_df['Retail Price'] #- price_calculation_df['cheapest_courier_shipping']

Out[77]:

(0    234.68
 1    234.97
 2    234.65
 3    234.60
 4    234.74
 dtype: float64, 0    691.10
 1    692.04
 2    687.49
 3    687.38
 4    688.51
 dtype: float64, 0    486.61
 1    487.28
 2    484.07
 3    483.99
 4    484.79
 dtype: float64)

In [78]:

# calculate prices for Amazon

In [79]:

BQ2 = price_calculation_df['amazon_ca_minimum_price'] = round(AJ2-H2-K2-I2-P2-S2,2)usd_rate = 0.761242BR2 = price_calculation_df['amazon_usd_minimum_price'] = round(BQ2 * cad_to_usd, 2)BL2[:5], BQ2[:5], BR2[:5]

Out[79]:

(0    234.68
 1    234.97
 2    234.65
 3    234.60
 4    234.74
 dtype: float64, 0    691.10
 1    692.04
 2    687.49
 3    687.38
 4    688.51
 dtype: float64, 0    486.61
 1    487.28
 2    484.07
 3    483.99
 4    484.79
 dtype: float64)

In [80]:

BS2 = price_calculation_df['amazon_gbp_minimum_price'] = round(BQ2 * cad_to_gbp, 2)BT2 = price_calculation_df['amazon_eu_minimum_price'] = round(BQ2 * cad_to_euro, 2)BU2 = price_calculation_df['amazon_mxn_minimum'] = round(BQ2 * cad_to_mxn, 2)"""E2 = [500] * len(raw_df)min_e2_f2 = E2if F2.any() < min_e2_f2.any():min_e2_f2 = F2"""#BV2 = price_calculation_df['amazon_ca_abs_minimum'] = round(BQ2-R2+Q2-F2+ F2 )BW2 = price_calculation_df['expedited_cost'] = 45BU2[1]#[:5], BT2[:5], BU2[:5], BW2[:5]

Out[80]:

12174.02

In [81]:

BV2 = price_calculation_df['amazon_ca_abs_minimum'] = round ( BQ2 - R2 + Q2, 2)BV2[:5]

Out[81]:

0    625.31
1    626.16
2    622.03
3    621.93
4    622.96
dtype: float64

In [ ]:

# output Price Calculation

In [82]:

output_folder = './data-supplier-2020-04-14/output/'

In [83]:

price_calculation_df.to_csv( output_folder + 'calculated_price_' + input_file_name + today + '.csv')

In [84]:

df_calculated = pd.read_csv(output_folder + 'calculated_price_' + input_file_name + today + '.csv')df_calculated.head(2)

Out[84]:

Unnamed: 0product_idskuNameraw_pricePostal Shipping Pricecheapest_courier_shippingproduct_eanShip RiskPrice Risk…Retail 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…16.77516.77234.68691.10486.61396.45450.3612157.4945625.311138649shopforsoul-A01AL3303110Qiyun AoLong V2 3x3x3 Speed Cube Enhanced Edit…13.61500.0500.06.941378e+1215.00.4083…17.48517.48234.97692.04487.28396.99450.9712174.0245626.16

2 rows × 75 columns

In [85]:

price_calculation_df[['product_id', 'sku', 'Name']][:5]

Out[85]:

product_idskuName030399shopforsoul-A01AL3301111Black 3x3x3 MoYu AoLong V2 Puzzle-A01AL3301111138649shopforsoul-A01AL3303110Qiyun AoLong V2 3x3x3 Speed Cube Enhanced Edit…239296shopforsoul-A01CL3301111New , 5.46cm Smaller Sized Fangshi (Funs) Shua…337397shopforsoul-A01CL3302113Fangshi Shuanren 3 x 3 x 3 with Sticker 54.6 m…438354shopforsoul-A01CL3302120FangShi 3x3x3 Speed Cube Puzzle White Fully As…

# # Calculate sell prices (and related) for ebay# In[46]:ebay_profit_tax = U2 * income_tax
AB2 = price_calculation_df[‘ebay_cost’] = round(L2 + N2 + O2 + P2 + S2 + ebay_profit_tax, 2)
W2[:5], AB2[:5]
L2[:1], M2[:1], N2[:1], P2[:1], S2[:1], V2[:1]# In[47]:AC2 = price_calculation_df[‘ebay_list_price’] = round(AB2 + U2 -F2, 2) # — F2, 2)
AC2[:5]# In[48]:AD2 = price_calculation_df[‘Buyer pay from Mall’] = round(Y2+R2,2)# In[49]:AE2 = price_calculation_df[‘Ebay Buyers Pay’] = round(AC2+R2+F2,2)
AF2 = price_calculation_df[‘Ebay vs Mall’] = round(AE2-AD2, 2)# # account for amazon selling cost and calculate your sell price for amazon
# #amazon has sales commission to pay
# #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)# In[50]:# possible income tax
AG2 = price_calculation_df[‘NA-1’] = round(W2-R2+L2*0.2,2)AI2 = price_calculation_df[‘amazon_profit’] = round(L2*amazon_profit, 2)
AJ2 = price_calculation_df[‘amazon_our_cost’] = round(L2+S2+P2+R2+AI2*income_tax,2)
AH2 = price_calculation_df[‘Amazon Final Value Fee’] = round( (AI2 + AJ2) * amazon_final_fee_rate, 2)
AK2 = price_calculation_df[‘amazon_list_price’] = round(AJ2+AI2+AH2, 2)
AL2 = price_calculation_df[‘amazon_special_price’] = 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)
AM2 = price_calculation_df[‘Amazon Final Value Fee USD’] = round(AH2*cad_to_usd, 2)
AN2 = price_calculation_df[‘amazon_profit_usd’] = round(AI2*cad_to_usd, 2)
AO2 = price_calculation_df[‘amazon_our_cost_usd’] = round(AJ2*cad_to_usd, 2)
AP2 = price_calculation_df[‘amazon_list_price_usd’] = round(AK2*cad_to_usd, 2)
AQ2 = price_calculation_df[‘amazon_special_price_usd’] = round(AL2*cad_to_usd, 2)# In[51]:AE2[:2], AF2[:2] , AG2[:2] , AH2[:2] , AI2[:2] , AJ2[:2] , AK2[:2] , 
AL2[:2] , AM2[:2] , AN2[:2] , AO2[:2] , AP2[:2] , AQ2[:2]# In[52]:AR2 = price_calculation_df[‘amazon_list_price_gbp’] = round(AK2*cad_to_gbp, 2)
AS2 = price_calculation_df[‘amazon_special_price_gbp’] = round(AL2*cad_to_gbp, 2)
AT2 = price_calculation_df[‘amazon_list_price_eu’] = round(AK2*cad_to_euro,2)
AU2 = price_calculation_df[‘amazon_special_price_eu’] = round(AL2*cad_to_euro, 2)
AV2 = price_calculation_df[‘amazon_list_price_mxn’] = round(AK2*cad_to_mxn, 2)
AW2 = price_calculation_df[‘amazon_special_price_mxn’] = round(AL2*cad_to_mxn, 2)AR2[:2], AS2[:2], AT2[:2], AU2[:2], AV2[:2], AW2[:2]# In[53]:#D2 = 1
BD2 = raw_df[‘Weight Kg’] 
AX2 = price_calculation_df[‘ama_safe_refund_amount’] = round(AL2-H2-I2-K2-S2-P2-R2-AH2-D2–18, 2)
AY2 = price_calculation_df[‘Weight’] = round(BD2*2.2, 2)
BJ2 = price_calculation_df[‘original in stock’] = raw_df[‘Stock status’]
AZ2 = price_calculation_df[‘Stock status’] = (BJ2==”In Stock”).astype(int)
#AZ2 = AZ2.astype(int)
AX2[:2], AY2[:2], AZ2[:2], BJ2[:2], BD2[:2]# In[55]:AY2[:5]# In[56]:min_raw_price_to_show_on_amazon = 10
BA2 = price_calculation_df[‘categories’] = raw_df[‘Category Name’] + ‘/’ + raw_df[‘Subcategory Name’]
BB2 = price_calculation_df[‘_root_category’] = ‘All’
BC2 = price_calculation_df[‘visibility’] = 4vis = []
for x in D2:
 if x < min_raw_price_to_show_on_amazon:
 #vis.append(1)
 # temporary 
 vis.append(‘Catalog, Search’)
 else:
 vis.append(‘Catalog, Search’) 
BC2 = price_calculation_df[‘visibility’] = vis
# temporary
# BC2 = price_calculation_df[‘visibility’] = 4# In[57]:BC2[:5]# In[61]:# take width height from supplier to push to Amazon# In[62]:BE2 = price_calculation_df[‘Height mm’] = raw_df[‘Height mm’]
BF2 = price_calculation_df[‘Width mm’] = raw_df[‘Width mm’]
BG2 = price_calculation_df[‘Depth mm’] = raw_df[‘Depth mm’]# In[64]:# We do not want to sell products that has weight > 40 lbs
# status = 2 = disabled on Amazon
# based on ‘weight right’ products will be disabled or enabled# In[65]:# default value
BH2 = price_calculation_df[‘status’] = 2
BN2 = price_calculation_df[‘weight right’] = 0vis = []
for x in AY2:
 if x <= 20:
 vis.append(1)
 else:
 vis.append(0)BN2 = price_calculation_df[‘weight right’] = vis
BN2[:5]# In[66]:# based on shipping cost, you might not want to sell products. 
# we will not sell if the shipping cost is more than $40# In[67]:vis = []
for x in F2.astype(float):
 if x >= 40:
 vis.append(0)
 else:
 vis.append(1)
 
BO2 = price_calculation_df[‘Ship Right’] = vis
#if (F2 >= 40) and (D2/F2 <= 2):
 #BO2 = price_calculation_df[‘Ship Right’] = 0
 
BO2[:5]# In[60]:# F2[2531]# In[68]:# if the product will be discontinued soon, we are not selling.
# will set status = 2. initially will set Is Normal = No = 0# In[69]:BM2 = price_calculation_df[‘Soon Discontinued’] = raw_df[‘Soon Discontinued’] 
BP2 = price_calculation_df[‘Is Normal’] = 0vis = []
for x in BM2:
 if x == ‘Soon Discontinued’:
 vis.append(0)
 else:
 vis.append(1)
 
BP2 = price_calculation_df[‘Is Normal’] = vis 
BP2[10603:10610]# In[70]:‘’’
BH2 = [1] * len(BN2)
for index in range(0, len(BN2) ):
 if (BN2[index]==1):
 if (BO2[index]==1):
 if (BP2[index]==1):
 if (AZ2[index]==1):
 BH2[index] = 1
 price_calculation_df[‘status’][index] = 1‘’’# In[71]:# set status for some or until now
# status = enabled if all conditions are right i.e. BN2 and BO2 and BP2 and AZ2
# i.e. price right, ship right, weight right, not discontinued
BH2 = BN2 and BO2 and BP2 and AZ2
BH2.replace(0, 2, inplace=True)
#sorted(BH2, reverse=True)
price_calculation_df[‘status’] = BH2# In[72]:# keep track of supplier retail price (recommended retail) and retail+shipping price
BI2 = price_calculation_df[‘Retail Price’] = raw_df[‘Retail Price’]# In[73]:BH2[1] , raw_df[1:2][‘Model Code’]# In[74]:price_calculation_df[‘Retail Price’] = price_calculation_df[‘Retail Price’].astype(str)# In[75]:#list(price_calculation_df[‘Retail Price’].unique()[0:len(price_calculation_df[‘Retail Price’])+1])
BI2 = price_calculation_df[‘Retail Price’] = price_calculation_df[‘Retail Price’].str.replace(‘,’,’’)#max(sorted(price_calculation_df[‘Retail Price’].astype(str)))
BK2 = price_calculation_df[‘R+shipp’] = BI2.astype(float) + F2.astype(float) #price_calculation_df[‘Retail Price’] + price_calculation_df[‘cheapest_courier_shipping’]# In[76]:BK2[:5]# In[77]:#F2
# compare our retail shop = magento price with supplier recommended retail price
BL2 = price_calculation_df[‘Shop vs retail’] = Y2 — BK2 #round(W2+L2*0.15,22) #- price_calculation_df[‘Retail Price’] #- price_calculation_df[‘cheapest_courier_shipping’]# In[78]:# calculate prices for Amazon# In[79]:BQ2 = price_calculation_df[‘amazon_ca_minimum_price’] = round(AJ2-H2-K2-I2-P2-S2,2)
usd_rate = 0.761242
BR2 = price_calculation_df[‘amazon_usd_minimum_price’] = round(BQ2 * cad_to_usd, 2)
BL2[:5], BQ2[:5], BR2[:5]# In[80]:BS2 = price_calculation_df[‘amazon_gbp_minimum_price’] = round(BQ2 * cad_to_gbp, 2)
BT2 = price_calculation_df[‘amazon_eu_minimum_price’] = round(BQ2 * cad_to_euro, 2)
BU2 = price_calculation_df[‘amazon_mxn_minimum’] = round(BQ2 * cad_to_mxn, 2)“””
E2 = [500] * len(raw_df)
min_e2_f2 = E2
if F2.any() < min_e2_f2.any():
 min_e2_f2 = F2
“””#BV2 = price_calculation_df[‘amazon_ca_abs_minimum’] = round(BQ2-R2+Q2-F2+ F2 )
BW2 = price_calculation_df[‘expedited_cost’] = 45
BU2[1]#[:5], BT2[:5], BU2[:5], BW2[:5]# In[81]:BV2 = price_calculation_df[‘amazon_ca_abs_minimum’] = round ( BQ2 — R2 + Q2, 2) 
BV2[:5]# In[ ]:# output Price Calculation# In[82]:output_folder = ‘./data-supplier-2020–04–14/output/’# In[83]:price_calculation_df.to_csv( output_folder + ‘calculated_price_’ + input_file_name + today + ‘.csv’)# In[84]:df_calculated = pd.read_csv(output_folder + ‘calculated_price_’ + input_file_name + today + ‘.csv’)
df_calculated.head(2)# In[85]:price_calculation_df[[‘product_id’, ‘sku’, ‘Name’]][:5]

Medium: https://medium.com/@SayedAhmedCanada

*** . *** *** . *** . *** . ***

Sayed Ahmed

BSc. Eng. in Comp. Sc. & Eng. (BUET)

MSc. in Comp. Sc. (U of Manitoba, Canada)

MSc. in Data Science and Analytics (Ryerson University, Canada)

Linkedin: https://ca.linkedin.com/in/sayedjustetc

Blog: http://Bangla.SaLearningSchool.com, http://SitesTree.com

Training Courses: http://Training.SitesTree.com

8112223 Canada Inc/Justetc: http://JustEtc.net

Facebook Groups/Forums to discuss (Q & A):

https://www.facebook.com/banglasalearningschool

https://www.facebook.com/justetcsocial

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. http://sitestree.com/training/

You could as well remove products that are not allowed in a country or in a market place as well as products that you are not authorized to sell (some brands)

All Code in One Block. Please check the other parts of this series/publication

The code could be simplified/reduced. You could join multiple blocks into one just by keeping the words/category names in a list; and then filtering against that list. You could as well join conditions using and (&) or or-operations (|) to reduce the number of lines of code.

# # Section Remove products that have slang words# In[24]:unique_sorted_data[‘Category Name’].unique()# In[30]:# Remove products from a category that you do not want to sell# Apparelunique_sorted_data_filter_category = unique_sorted_data [
 ~( unique_sorted_data[‘Category Name’].str.contains(“Apparel”, case = False, na=False ) )
 ];# android TV Boxunique_sorted_data_filter_category = unique_sorted_data_filter_category [
 ~( unique_sorted_data_filter_category[‘Category Name’].str.contains(“TV Box”, case = False, na=False ) )
 ];# Laser Products
unique_sorted_data_filter_category = unique_sorted_data_filter_category [
 ~( unique_sorted_data_filter_category[‘Category Name’].str.contains(“Laser”, case = False, na=False ) )
 ];# Costume
unique_sorted_data_filter_category = unique_sorted_data_filter_category [
 ~( unique_sorted_data_filter_category[‘Category Name’].str.contains(“Costume”, case = False, na=False ) )
 ];# Showing the count after removal
unique_sorted_data_filter_category[‘Category Name’].unique(), unique_sorted_data_filter_category.shape# In[31]:################## section Remove products that have slang/bad words in the name -- you do not need this block - was for testing only. Another block will do this jobunique_sorted_data_filter_1 = unique_sorted_data[ 
 ( unique_sorted_data[‘Full Product Name’].str.contains(“Slang 1“, case = False, na=False) )
 
 | ( unique_sorted_data[‘Full Product Name’].str.contains(“Slang 2“, case = False, na=False) )
 
 | ( unique_sorted_data[‘Full Product Name’].str.contains(“Slang 3“, case = False, na=False) )
 
 | ( unique_sorted_data[‘Full Product Name’].str.contains(“Slang 4“, case = False, na=False) )] #[ {‘Full Product Name’, ‘Category Name’}];
unique_sorted_data_filter_1.shape #, unique_sorted_data_filter_1.head(1) #, “\n “, unique_sorted_data_filter_1.shape######################### In[38]:# Remove products that have slang words in the product nameunique_sorted_filtered_data = unique_sorted_data_filter_category [
 ~( unique_sorted_data_filter_category[‘Full Product Name’].str.contains(“Bad word 1“, case = False, na=False ) ) ];print(unique_sorted_filtered_data.shape);
unique_sorted_filtered_data = unique_sorted_filtered_data [
 ~( unique_sorted_filtered_data[‘Full Product Name’].str.contains(“Bad word 2“, case = False, na=False ) )
 ];print(unique_sorted_filtered_data.shape);
unique_sorted_filtered_data = unique_sorted_filtered_data [
 ~( unique_sorted_filtered_data[‘Full Product Name’].str.contains(“Bad word 3“, case = False, na=False ) )
 ];print(unique_sorted_filtered_data.shape);
unique_sorted_filtered_data = unique_sorted_filtered_data [
 ~( unique_sorted_filtered_data[‘Full Product Name’].str.contains(“Bad word 4”, case = False, na=False ) )
 ];print(unique_sorted_filtered_data.shape);
unique_sorted_filtered_data = unique_sorted_filtered_data [
 ~( unique_sorted_filtered_data[‘Full Product Name’].str.contains(“Bad word 5“, case = False, na=False ) )
 ];print(unique_sorted_filtered_data.shape);
unique_sorted_filtered_data = unique_sorted_filtered_data [
 ~( unique_sorted_filtered_data[‘Full Product Name’].str.contains(“Bad word 1“, case = False, na=False ) )
 ];print(unique_sorted_filtered_data.shape);
unique_sorted_filtered_data = unique_sorted_filtered_data [
 ~( unique_sorted_filtered_data[‘Full Product Name’].str.contains(“Tracker”, case = False, na=False ) )
 ];print(unique_sorted_filtered_data.shape);
unique_sorted_filtered_data = unique_sorted_filtered_data [
 ~( unique_sorted_filtered_data[‘Full Product Name’].str.contains(“Laser”, case = False, na=False ) )
 ];# brands that you do not want to sell
print(unique_sorted_filtered_data.shape);
unique_sorted_filtered_data = unique_sorted_filtered_data [
 ~( unique_sorted_filtered_data[‘Full Product Name’].str.contains(“VKworld”, case = False, na=False ) )
 ];
print(unique_sorted_filtered_data.shape);
unique_sorted_filtered_data = unique_sorted_filtered_data [
 ~( unique_sorted_filtered_data[‘Full Product Name’].str.contains(“Samsung”, case = False, na=False ) )
 ];# video streaming TV Box HDMI -- products are sensitive (intellectual rights)
print(unique_sorted_filtered_data.shape);
unique_sorted_filtered_data = unique_sorted_filtered_data [
 ~( unique_sorted_filtered_data[‘Full Product Name’].str.contains(“Car Video”, case = False, na=False ) )
 ];print(unique_sorted_filtered_data.shape);
unique_sorted_filtered_data = unique_sorted_filtered_data [
 ~( unique_sorted_filtered_data[‘Full Product Name’].str.contains(“Streaming”, case = False, na=False ) )
 ];print(unique_sorted_filtered_data.shape);
unique_sorted_filtered_data = unique_sorted_filtered_data [
 ~( unique_sorted_filtered_data[‘Full Product Name’].str.contains(“HDMI”, case = False, na=False ) )
 ];print(unique_sorted_filtered_data.shape);
unique_sorted_filtered_data.shape# In[39]:unique_sorted_filtered_data.to_csv(“../all_supplier_data_unique_sorted_and_filtered.csv”);

From Jupyter Notebook: Cell by Cell with Output

Section Remove products that have slang/bad words or similar. Or Brands/Products that you do not want to sell (not allowed to sell/resell/restricted products or similar): I know the grammar is not right. Sorry, not fixing it.

In [24]:

unique_sorted_data['Category Name'].unique()

Out[24]:

array(['Toys & Games', 'Drone & Quadcopter', 'Cool Gadgets',
       'Office supplies', 'Novelty Costumes & Accessories',
       "Women's Jewelry", 'External Parts', 'Vehicle Electronics Devices',
       'Replacement Parts', 'Internal Parts', 'Lamps and Accessories',
       'Video Games', 'Hair Care', 'Skin Care',
       'Makeup Tool & Accessories', 'Household Products',
       "Women's Accessories", "Women's Apparel", 'Home accessories',
       'Oral Respiratory Protection', "Men's Accessories",
       "Men's Apparel", "Girl's Apparel", 'Cell Phone Accessories',
       'Health Care', 'Electronic Accessories', 'Health tools',
       'Computer Peripherals', 'Audio & Video Gadgets', nan,
       'Headrest Monitors & DVD Players', 'Car DVR',
       'Camera Equipment / Accessories', 'Personal Care',
       'Laser Gadgets & Measuring Tools', 'Accessories',
       'Electronic Cigarettes', 'Sports Action Camera',
       'Android TV Box / Stick', 'Sports & Body Building',
       'Smart Watches', 'Security & Surveillance', 'Android Tablets',
       'Musical Instruments & Accessorie', 'LED', 'Outdoor Recrections',
       'Tools & Home Decor', 'Home, Kitchen & Garden', 'Home Electrical',
       'Bedding & Bath', 'Camping & Hiking', 'Drives & Storage',
       'Pet Supplies', 'Hunting & Fishing', 'Garden & Lawn',
       'Medical treatments', 'Android Smartphones', 'Car Video',
       'Cell Phones', 'Cycling', 'Solar Products', 'Doogee Phones',
       'Rugged Phones', 'Ulefone Phones', 'Xiaomi Phones', 'Huawei Phone',
       'Lenovo Phones', 'Refurbished iPhones', 'Samsung Phones',
       'Water Sport', 'Tools & Equipment', 'Repair Accessories',
       'Body protection', 'Disinfection and sterilization', "Men's Care",
       'Cleaning Supplies', 'Baby Girls Apparel', "Women's Bags",
       "Women's Shoes", "Men's Jewelry", 'Baby Boys Apparel',
       "Boy's Apparel", "Girl's Shoes", "Girl's Jewelry", "Boy's Shoes",
       'kN95/KF94 Mask', 'Flash Drives + Memory Cards',
       '6-7 Inch Android Phones', 'Apple Phones', 'Xiaomi Phone',
       'Laptops & Tablets', 'Apple iPad', 'Musical Instruments',
       'Computer Accessories', 'Ball Games', "Boy's Jewelry"],
      dtype=object)

In [30]:

# Remove products from a category that you do not want to sellunique_sorted_data_filter_category = unique_sorted_data [ ~( unique_sorted_data['Category Name'].str.contains("Apparel", case = False, na=False ) )];​unique_sorted_data_filter_category = unique_sorted_data_filter_category [ ~( unique_sorted_data_filter_category['Category Name'].str.contains("TV Box", case = False, na=False ) )];unique_sorted_data_filter_category = unique_sorted_data_filter_category [~( unique_sorted_data_filter_category['Category Name'].str.contains("Laser", case = False, na=False ) )];unique_sorted_data_filter_category = unique_sorted_data_filter_category [ ~( unique_sorted_data_filter_category['Category Name'].str.contains("Costume", case = False, na=False ) )];unique_sorted_data_filter_category['Category Name'].unique(), unique_sorted_data_filter_category.shape

Out[30]:

(array(['Toys & Games', 'Drone & Quadcopter', 'Cool Gadgets',
        'Office supplies', "Women's Jewelry", 'External Parts',
        'Vehicle Electronics Devices', 'Replacement Parts',
        'Internal Parts', 'Lamps and Accessories', 'Video Games',
        'Hair Care', 'Skin Care', 'Makeup Tool & Accessories',
        'Household Products', "Women's Accessories", 'Home accessories',
        'Oral Respiratory Protection', "Men's Accessories",
        'Cell Phone Accessories', 'Health Care', 'Electronic Accessories',
        'Health tools', 'Computer Peripherals', 'Audio & Video Gadgets',
        nan, 'Headrest Monitors & DVD Players', 'Car DVR',
        'Camera Equipment / Accessories', 'Personal Care', 'Accessories',
        'Electronic Cigarettes', 'Sports Action Camera',
        'Sports & Body Building', 'Smart Watches',
        'Security & Surveillance', 'Android Tablets',
        'Musical Instruments & Accessorie', 'LED', 'Outdoor Recrections',
        'Tools & Home Decor', 'Home, Kitchen & Garden', 'Home Electrical',
        'Bedding & Bath', 'Camping & Hiking', 'Drives & Storage',
        'Pet Supplies', 'Hunting & Fishing', 'Garden & Lawn',
        'Medical treatments', 'Android Smartphones', 'Car Video',
        'Cell Phones', 'Cycling', 'Solar Products', 'Doogee Phones',
        'Rugged Phones', 'Ulefone Phones', 'Xiaomi Phones', 'Huawei Phone',
        'Lenovo Phones', 'Refurbished iPhones', 'Samsung Phones',
        'Water Sport', 'Tools & Equipment', 'Repair Accessories',
        'Body protection', 'Disinfection and sterilization', "Men's Care",
        'Cleaning Supplies', "Women's Bags", "Women's Shoes",
        "Men's Jewelry", "Girl's Shoes", "Girl's Jewelry", "Boy's Shoes",
        'kN95/KF94 Mask', 'Flash Drives + Memory Cards',
        '6-7 Inch Android Phones', 'Apple Phones', 'Xiaomi Phone',
        'Laptops & Tablets', 'Apple iPad', 'Musical Instruments',
        'Computer Accessories', 'Ball Games', "Boy's Jewelry"],
       dtype=object), (47826, 40))

In [31]:

# sect

In [38]:

# Remove products that have slang words in the product nameunique_sorted_filtered_data = unique_sorted_data_filter_category [ ~( unique_sorted_data_filter_category['Full Product Name'].str.contains("Bad word 1", case = False, na=False ) )];print(unique_sorted_filtered_data.shape);unique_sorted_filtered_data = unique_sorted_filtered_data [~( unique_sorted_filtered_data['Full Product Name'].str.contains("Bad word 2", case = False, na=False ) )];print(unique_sorted_filtered_data.shape);
unique_sorted_filtered_data = unique_sorted_filtered_data [~( unique_sorted_filtered_data['Full Product Name'].str.contains("Bad word 3", case = False, na=False ) )];# product
print(unique_sorted_filtered_data.shape);
unique_sorted_filtered_data = unique_sorted_filtered_data [ ~( unique_sorted_filtered_data['Full Product Name'].str.contains("Tracker", case = False, na=False ) ) ];print(unique_sorted_filtered_data.shape);
unique_sorted_filtered_data = unique_sorted_filtered_data [ ~( unique_sorted_filtered_data['Full Product Name'].str.contains("Laser", case = False, na=False ) )];print(unique_sorted_filtered_data.shape);# remove brands
unique_sorted_filtered_data = unique_sorted_filtered_data [ ~( unique_sorted_filtered_data['Full Product Name'].str.contains("VKworld", case = False, na=False ) )];print(unique_sorted_filtered_data.shape);unique_sorted_filtered_data = unique_sorted_filtered_data [ ~( unique_sorted_filtered_data['Full Product Name'].str.contains("Samsung", case = False, na=False ) )];print(unique_sorted_filtered_data.shape);# video, streaming, HDMI
unique_sorted_filtered_data = unique_sorted_filtered_data [ ~( unique_sorted_filtered_data['Full Product Name'].str.contains("Car Video", case = False, na=False ) )];print(unique_sorted_filtered_data.shape);unique_sorted_filtered_data = unique_sorted_filtered_data [ ~( unique_sorted_filtered_data['Full Product Name'].str.contains("Streaming", case = False, na=False ) )];print(unique_sorted_filtered_data.shape);unique_sorted_filtered_data = unique_sorted_filtered_data [~( unique_sorted_filtered_data['Full Product Name'].str.contains("HDMI", case = False, na=False ) )];print(unique_sorted_filtered_data.shape);
unique_sorted_filtered_data.shape(47001, 40)
(46988, 40)
(46968, 40)
(46968, 40)
(46959, 40)
(46959, 40)
(46441, 40)
(46388, 40)
(46381, 40)
(45357, 40)
(45349, 40)
(45338, 40)
(44911, 40)

Out[38]:

(44911, 40)

In [39]:

# send the filtered data to a file
unique_sorted_filtered_data.to_csv("../all_supplier_data_unique_sorted_and_filtered.csv");

***. ***. ***

Note: Older short-notes from this site are posted on Medium: https://medium.com/@SayedAhmedCanada

*** . *** *** . *** . *** . ***

Sayed Ahmed

BSc. Eng. in Comp. Sc. & Eng. (BUET)

MSc. in Comp. Sc. (U of Manitoba, Canada)

MSc. in Data Science and Analytics (Ryerson University, Canada)

Linkedin: https://ca.linkedin.com/in/sayedjustetc

Blog: http://Bangla.SaLearningSchool.com, http://SitesTree.com

Training Courses: http://Training.SitesTree.com

8112223 Canada Inc/Justetc: http://JustEtc.net

Facebook Groups/Forums to discuss (Q & A):

https://www.facebook.com/banglasalearningschool

https://www.facebook.com/justetcsocial

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. http://sitestree.com/training/