<jupyter_start><jupyter_text>Data extracted 27 May 2021<jupyter_code># import libraries
import pandas as pd
import requests
#import bs4 #not needed this time
import matplotlib.pyplot as plt
import seaborn as sns
# e-Commerce site: ntuc
# url = https://www.fairprice.com.sg/category/rice-noodles-cooking-ingredients

# get from inspect network
test_url = 'https://website-api.omni.fairprice.com.sg/api/product/v2?category=rice-noodles-cooking-ingredients&experiments=recHome-Z%2CbsHome-Z%2CpastPurchaseCategory-B%2CsearchVariant-B%2CUnappliedPromoVariant-B%2CtrendH-Z%2CtimerVariant-Z%2CsubstitutionVariant-B%2CsubstitutionVariant-B%2CsubstitutionVariant-B%2CsubstitutionVariant-B&includeTagDetails=true&orderType=DELIVERY&page=2&pageType=category&slug=rice-noodles-cooking-ingredients&storeId=165&url=rice-noodles-cooking-ingredients'
# make test request
# no need for headers
test_res = requests.get(test_url)
test_res
# get the JSON from response; dynamic site
test_json = test_res.json()
test_json
test_json.keys()
test_json['data'].keys()
# Note that the total number of pages is dynamic! 
# Must be factored into compilation to avoid potential run-time errors.  
test_json['data']['pagination']
test_product = test_json['data']['product']
#use pd.json_normalize to turn the dict into a DataFrame
test_df = pd.json_normalize(test_product)
test_df['storeSpecificData']<jupyter_output><empty_output><jupyter_text>Putting everything together<jupyter_code># base is the start of url
base_url = "https://website-api.omni.fairprice.com.sg/api/product/v2?category=fruits-vegetables&experiments=recHome-Z%2CbsHome-Z%2CpastPurchaseCategory-B%2CsearchVariant-B%2CUnappliedPromoVariant-B%2CtrendH-Z%2CtimerVariant-Z%2CsubstitutionVariant-B%2CsubstitutionVariant-B%2CsubstitutionVariant-B%2CsubstitutionVariant-B&includeTagDetails=true&page="


# end is the end of url
end_url = "&pageType=category&slug=rice-noodles-cooking-ingredients&storeId=165&url=rice-noodles-cooking-ingredients"
# declare an empty list to contain all pages' worth of DataFrames
df_list = []

# loop 1 to last page
print('Processing commences...')
for page_num in range(1,test_json['data']['pagination']['total_pages']+1):
    
    #if (abs(page_num))%100 == 0:
     #   print(f'processing page {page_num} of 458...')
    #if page_num%457 == 0:
     #   print(f'processing page {page_num} of 457.')
    # construct the temp URL
    temp_url = base_url + str(page_num) +end_url
    
    # make a GET request
    temp_res = requests.get(temp_url)
    
    # get json from response object
    temp_json = temp_res.json()
    
    # get the dataframe
    temp_df = pd.json_normalize(temp_json['data']['product'])
    
    #append df into list of dfs
    df_list.append(temp_df)

# combine the df in the list of dfs, into a huge df    
final_df = pd.concat(df_list)
# reset index
final_df.reset_index(drop=True,inplace=True)

# 
final_df
# get the entire DataFrame's "storeSpecificData" column
store_data = final_df['storeSpecificData']

# create an empty list to store the rows of data
store_list =[]

# use a for loop to loop through each row to unpack the single list
for store in store_data:
    # normalize the first item in the list
    store_temp = pd.json_normalize(store[0])
    
    # append the DataFrame into the list
    store_list.append(store_temp)

# concat everything in the list into a single DataFrame
final_store_df = pd.concat(store_list)

# reset the DataFrame's index
final_store_df.reset_index(drop=True,inplace=True)

# 
final_store_df
# combine the 458-page DataFrame with the unpacked "storeSpecificData"
final_combined_df = pd.concat([final_df, final_store_df], axis = 1)
# 
final_combined_df
# export the DataFrame into a CSV
final_combined_df.to_csv('final_combined_hw.csv',index=None)<jupyter_output><empty_output><jupyter_text>Data processing<jupyter_code>df_read = pd.read_csv('final_combined_hw.csv')
df_read.shape
df_read.columns
# manually select columns of interest
csi = ['bulkOrderThreshold','handlingDays','name','brand.name',
       'metaData.Country of Origin','metaData.Dietary Attributes','metaData.Supplier Name',
       'primaryCategory.parentCategory.name','inStoreStock','mrp']
# get the subset dataframe
df_i = df_read[csi].copy()
df_i.shape
df_i.tail(2)
# rename columns
df_i.rename(columns={
    'bulkOrderThreshold':'maxordQty',
    'brand.name':'Brand',
    'metaData.Country of Origin':'Origin',
    'metaData.Dietary Attributes':'DietaryAttr',
    'metaData.Supplier Name':'Supplier',
    'primaryCategory.parentCategory.name':'Cat',
    'inStoreStock':'storeStock',
    'mrp':'retailPrice'
}, inplace=True)
df_i.describe()
# Data wrangling challenge
df_test = df_i[['DietaryAttr']].copy()
df_test['DietaryAttr'].unique()
# Convert to string values for data cleaning
df_test["DietaryAttr"] = df_test["DietaryAttr"].astype(str)
df_test["DietaryAttr"] = [x.replace("[","").replace("'","").replace("]","") for x in df_test["DietaryAttr"]]
df_test["DietaryAttr"].shape
### Putting this together for data wrangling
# Interest on Healthier choices, trans-fat free and lactose free
hc_lst = []
tff_lst = []
lf_lst = []

for attr in df_test['DietaryAttr']:
        
    # Healthier Choice   
    if 'Healthier Choice' in attr:
        hc = 'Yes'
    else:
        hc = 'No'
    hc_lst.append(hc)
        
    # Trans-fat indicator
    if 'Trans-Fat Free' in attr:
        tff = 'Yes'
    else:
        tff = 'No'
    tff_lst.append(tff)
        
    # Lactose-free indicator    
    if 'Lactose-Free' in attr:
        lf = 'Yes'
    else:
        lf = 'No'
    lf_lst.append(lf)
df_hc = pd.DataFrame(hc_lst,columns=['LowerSugar'])
df_tff = pd.DataFrame(tff_lst,columns=['TransFat'])
df_lf = pd.DataFrame(lf_lst,columns=['LactoseFree'])

frames = [df_hc,df_tff,df_lf]
result = pd.concat(frames, axis=1)

df_inew = pd.concat([df_i,result],axis=1)
df_inew.shape
df_inew.tail(1)
df_inew2 = df_inew.drop(['DietaryAttr'],axis=1).copy()
df_inew2.info()
# convert data types
df_inew2.convert_dtypes().dtypes
# Any null values?
df_inew2.isnull().any()
null_columns=df_inew2.columns[df_inew2.isnull().any()]
df_inew2[null_columns].isnull().sum()
df_inew2[df_inew2['Brand'].isnull()]
# replace the values for Brand
df_inew2.loc[279,'Brand'] = 'Heinz'
df_inew2.loc[1421,'Brand'] = 'Chuan Heng Bee'
df_inew2.loc[1545,'Brand'] = 'Lee K<NAME>'
df_inew2.loc[1830,'Brand'] = 'ABC'
df_inew2.loc[2248,'Brand'] = 'ABC'
df_inew2.loc[2501,'Brand'] = '<NAME>'
df_inew2.loc[6611,'Brand'] = 'Colavita'
df_inew2.loc[8166,'Brand'] = 'Barilla'
df_inew2.loc[8261,'Brand'] = 'Tiger'
# check
df_inew2.loc[df_inew2.index==8261]['Brand']
# replace NaN Supplier and Cat with 'no info'
df_inew2['Supplier'].fillna('no info',inplace=True)
df_inew2['Cat'].fillna('no info',inplace=True)
# export the DataFrame into a CSV
df_inew2.to_csv('df_inew.csv',index=None)<jupyter_output><empty_output><jupyter_text>Visualize Data<jupyter_code>df_viz = pd.read_csv('df_inew.csv')
df_viz.tail(1)
# Style
plt.style.use('ggplot')
# fontsize
# title
tfs = 16
# suptitle
stfs = 14
# axes
afs = 14
# axesticks
ats = 14

# Plot horizontal bar chart function
def hb(df,col,title,figname,colorname):
    plt.subplots(figsize=(14,12))
    plt.barh(df[col].value_counts().sort_values().index,
             df[col].value_counts().sort_values(),color=colorname,alpha=0.7)

    # Remove the upper & right frameline
    sns.despine()

    # Title
    plt.title(title,fontsize=tfs, loc='left')

    # Axes
    plt.yticks(fontsize=ats)
    plt.xlabel('Item count',fontsize=afs)
    plt.xticks(fontsize=ats)

    plt.tight_layout()
    plt.savefig(figname)
    plt.show()<jupyter_output><empty_output><jupyter_text>* We want to see the Country of origin for rice, noodles and cooking ingredients<jupyter_code># data (Origin)
hb(df_viz,'Origin','Rice, Noodles, Cooking Ingredients by Country of Origin','Country of Origin.png','blue')<jupyter_output><empty_output><jupyter_text>* Items by Category<jupyter_code># Data (Cat)
# Plot
hb(df_viz,'Cat','Items by Category','Items(Cat).png','blue')<jupyter_output><empty_output><jupyter_text>* Investigate the distribution of item price<jupyter_code># data (retailPrice, Origin)

# Plot
fig,ax = plt.subplots(figsize=(14,14))
ax = sns.boxplot(y='Origin',x='retailPrice',data=df_viz,palette = 'viridis')
# Rectangle
ax.axvspan(xmin=50,xmax=100, facecolor='r',alpha=0.2)

# Title
ax.set_title('Distribution of Item Retail Price by Country',fontsize=tfs, loc='left')
# Axes
ax.set_ylabel('')
plt.yticks(fontsize=ats)
ax.set_xlabel('SGD',fontsize=afs)
plt.xticks(fontsize=ats)


plt.tight_layout()
plt.savefig('RetailPrice by Country.png')
plt.show()
df_ex = df_viz.loc[df_viz['retailPrice']>50].copy()
# data (retailPrice, Origin, name)

# Plot
ax = sns.catplot(x='retailPrice',y='name',hue='Origin',data=df_ex, kind='swarm',s=8,
                 palette = 'viridis', legend_out=False,
                 height=5, aspect=2)

# Axes
ax.set_xlabels('SGD', fontsize=afs)
ax.set_ylabels('')
ax.set_xticklabels(fontsize=ats)
ax.set_yticklabels(fontsize=ats)

# Legend
ax._legend.set_title('Country')
plt.tight_layout()
plt.savefig('Items above 100.png')
plt.show()
# data (retailPrice)

# Plot
fig,ax = plt.subplots(figsize=(8,4))
ax= sns.kdeplot(data=df_viz['retailPrice'], cut=0,cumulative=True,color='g')

# Horizontal line @90%
ax.axhline(0.9, 0, 450, color = 'b', linestyle='-.')
# Title
ax.set_title('Cumulative distr. of Item Price', fontsize=tfs)

# Axes
plt.yticks(fontsize=ats)
plt.xticks(fontsize=ats)
plt.savefig('Price cum_distr.png')
plt.show()<jupyter_output><empty_output><jupyter_text>* Look at items below 100sgd<jupyter_code>df_lwrprice = df_viz.loc[df_viz['retailPrice']<100].copy()
df_lwrprice.shape
df_lwrprice.loc[(df_lwrprice['LactoseFree']=='Yes')]['Cat'].value_counts()<jupyter_output><empty_output><jupyter_text>* 10 most expensive Cooking Paste & Sauces items that are lactose free (under 100sgd).<jupyter_code>df_sauces = df_lwrprice.loc[(df_lwrprice['LactoseFree']=='Yes') & 
                (df_lwrprice['Cat'] == 'Cooking Paste & Sauces')].sort_values('retailPrice').reset_index(drop=True)
# 5 Priceiest sauces (lactose-free) under 100sgd
df_sauces.iloc[-5:]<jupyter_output><empty_output><jupyter_text>* How about healthier choices (i.e. lower sugar)?<jupyter_code>df_lwrprice.loc[(df_lwrprice['LowerSugar']=='Yes')]['Cat'].value_counts()
# Look at Sugar & Sweeteners
df_lwrprice.loc[(df_lwrprice['LowerSugar']=='No') & 
                        (df_lwrprice['Cat'] == 'Sugar & Sweeteners')]
df_hc = df_lwrprice.loc[(df_lwrprice['LowerSugar']=='Yes') & 
                        (df_lwrprice['Cat'] == 'Sugar & Sweeteners')].sort_values('retailPrice').reset_index(drop=True)
df_hc.iloc[-5:]<jupyter_output><empty_output><jupyter_text>* Handling days on average<jupyter_code>plt.hist(df_viz['handlingDays'],color='grey');<jupyter_output>C:\Users\65943\anaconda3\lib\site-packages\numpy\lib\histograms.py:839: RuntimeWarning: invalid value encountered in greater_equal
  keep = (tmp_a >= first_edge)
C:\Users\65943\anaconda3\lib\site-packages\numpy\lib\histograms.py:840: RuntimeWarning: invalid value encountered in less_equal
  keep &= (tmp_a <= last_edge)
{'fraction_non_alphanumeric': 0.14352266618885504, 'fraction_numerical': 0.018365884250134384, 'mean_word_length': 5.656529516994634, 'pattern_counts': {'":': 0, '<': 48, '<?xml version=': 0, '>': 48, 'https://': 3, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '458674', 'license': 'MIT', 'n_tokens_mistral': 4374, 'n_tokens_neox': 4137, 'n_words': 745, 'path': 'web_scrape/e-commerce_fairprice/FairPriceNoodles,Rice,CookingIngredients Scrape_Viz.ipynb', 'repo_name': 'AngShengJun/petproj', 'size': '11170'}