<jupyter_start><jupyter_text>Data Preprocessing* Generate These Tables:  * Merchant Order table    * Detailed orders for each merchant  * Merchant Product table    * Asile, deparment for each product of each merchant  * Time Series purchase, client table<jupyter_code>import pandas as pd
from datetime import date, timedelta
import random
import numpy as np<jupyter_output><empty_output><jupyter_text>Understand Data* Raw data is downloaded from  * https://www.kaggle.com/c/instacart-market-basket-analysis/data  * https://en.wikipedia.org/wiki/List_of_supermarket_chains_in_Canada<jupyter_code>products_df = pd.read_csv('products.csv')
print(products_df.shape)

products_df.head()
departments_df = pd.read_csv('departments.csv')
print(departments_df.shape)

departments_df.head()
aisles_df = pd.read_csv('aisles.csv')
print(aisles_df.shape)

aisles_df.head()
# order_dow is the day of the week
orders_df = pd.read_csv('orders.csv')
print(orders_df.shape)

orders_df.head()
order_prior_df = pd.read_csv('order_products__prior.csv')
print(order_prior_df.shape)

order_prior_df.head()
order_train_df = pd.read_csv('order_products__train.csv')
print(order_train_df.shape)

order_train_df.head()<jupyter_output>(1384617, 4)<jupyter_text>Utils<jupyter_code>def get_monday_lst(year):
    d = date(year, 2, 17)    
    d += timedelta(days = 0 - d.weekday())  # First Monday
    while d.year == year and d.month < 5:
        yield d
        d += timedelta(days = 7)

        
def uniform_random_select(lst, const):
    rand = random.randint(0, const%len(lst))
    return lst[rand]


#  Assign a purchase date to each order
monday_seeds = list(get_monday_lst(2019))
user_startingdate_dct = {}
def assign_date(r):
    user_id = r[1]
    days_since_prior_order = r[2]
    order_dow = r[3]
    
    if np.isnan(days_since_prior_order):
        row_date = uniform_random_select(monday_seeds, user_id) + timedelta(days = order_dow)
        user_startingdate_dct[user_id] = row_date
    else:
        row_date = user_startingdate_dct[user_id] + timedelta(days = days_since_prior_order)
        
    r = r.append(row_date)<jupyter_output><empty_output><jupyter_text>Merchant Order Table<jupyter_code># unit both prior and train orders
order_prior_train_df = order_prior_df.append(order_train_df)
print(order_prior_train_df.shape)
order_prod_df = orders_df.merge(order_prior_train_df, on='order_id')
order_prod_df = order_prod_df.sort_values(by=['user_id', 'order_number'])
print(order_prod_df.shape)

order_prod_df.head()<jupyter_output>(33819106, 10)<jupyter_text>Add purchase_date* For each order in each user, assign a random date but in time order listed in "days_since_prior_order".* Random number is uniform distribution.<jupyter_code>user_group_df = order_prod_df[['order_id', 'user_id', 'days_since_prior_order', 'order_dow', 'order_number']].drop_duplicates()
user_group_df = user_group_df.sort_values(by=['user_id', 'order_number'])

print(user_group_df.shape)

user_group_df.head()
# Using a list is faster
user_group_records = user_group_df.values.tolist()

for r in user_group_records:
    assign_date(r)
    
print (user_group_records[0:4])
user_group_df = pd.DataFrame(user_group_records)
user_group_df.columns = ['order_id', 'user_id', 'days_since_prior_order', 'order_dow', 'order_number', 'purchase_date']
user_group_df.head()
order_prod_df = order_prod_df.merge(user_group_df[['order_id', 'purchase_date']], on='order_id')
print(order_prod_df.shape)

order_prod_df.head()<jupyter_output>(33819106, 11)<jupyter_text>Add merchant* Randomly assign merchant for each order_id.* Random number is in normal distribution.<jupyter_code>merchant_lst = ["49th Parallel Grocery", "Asian Food Centre", "Askew's Foods", "Avril (Health Supermarket)", "Coleman's",
                "Co-op Atlantic", "Valu Foods", "Village Food Stores", "Coppa's Fresh Market", "Country Grocer",
                "Fairway Markets", "Family Foods", "Calgary Co-op", "North Central Co-op", "Red River Co-op",
                "Saskatoon Co-op", "Foodex", "FoodFare", "Fresh City Market", "Freson Bros.", "Galleria Supermarkets",
                "Goodness me", "H Mart", "Highland Farms", "Kim Phat", "Le Jardin Mobile", "L&M Markets (Hometown Grocers Co-op)",
                "Lalumière Bonanza", "Longo's", "Marché Frais", "Le Marché Esposito", "Les Arpents Verts", "Lococo's",
                "Lucky Supermarket", "Mike Dean's Super Food Stores", "Nations Fresh Food", "Nature's Emporium",
                "Northern", "NorthMart", "Panchvati Supermarket", "P.A.T. Mart", "Planet Organic", "Pomme Natural Market",
                "Rabba Fine Foods", "Subzi Mandi Cash & Carry", "Quality Foods", "Sunterra Market", "TaiKo Supermarket",
                "Vince's Market"]
len(merchant_lst)
import scipy.stats as ss
import numpy as np
import matplotlib.pyplot as plt

x = np.arange(0.5, len(merchant_lst))
xU, xL = x + 0.5, x - 0.5 
prob = ss.norm.cdf(xU, scale = 20) - ss.norm.cdf(xL, scale = 20)
prob = prob / prob.sum() #normalize the probabilities so their sum is 1
nums = np.random.choice(x, size = order_prod_df['order_id'].nunique(), p = prob)
plt.hist(nums, bins = len(x), color='g')
plt.title('Normal Distributed Random Values')
plt.show()
print(int(min(nums)), int(max(nums)))
# order_id list
orderid_lst = order_prod_df['order_id'].unique()

order_merchant_records = []
for i in range(len(orderid_lst)):
    orderid = orderid_lst[i]
    merchant = merchant_lst[int(nums[i])]
    order_merchant_records.append([orderid, merchant])
    
order_merchant_df = pd.DataFrame(order_merchant_records)
order_merchant_df.columns = ['order_id', 'merchant']
order_merchant_df.head()
order_prod_df = order_prod_df.merge(order_merchant_df, on='order_id')
print(order_prod_df.shape)
order_prod_df.head()
order_prod_df.drop(['eval_set', 'order_dow', 'days_since_prior_order', 'reordered', 'add_to_cart_order'], 
                   inplace=True, axis=1)
order_prod_df.head()
order_prod_df.to_pickle('merchant_order.pkl')<jupyter_output><empty_output><jupyter_text>Merchant Product Table<jupyter_code>merchant_prod = order_prod_df[['merchant', 'product_id']].drop_duplicates()
print(merchant_prod.shape)
merchant_prod.head()
merchant_prod_df = merchant_prod.merge(products_df, on='product_id')
print(merchant_prod_df.shape)
merchant_prod_df.head()
merchant_prod_df = merchant_prod_df.merge(aisles_df, on='aisle_id')
print(merchant_prod_df.shape)
merchant_prod_df.head()
merchant_prod_df = merchant_prod_df.merge(departments_df, on='department_id')
print(merchant_prod_df.shape)
merchant_prod_df.head()
# Check product_id : aisle_id
df_count = merchant_prod_df[['product_id', 'aisle_id']].astype('str').drop_duplicates()\
          .groupby(['product_id'], as_index=False)['aisle_id']\
          .agg(['count']).reset_index()\
          .sort_values(['count'], ascending=False)
print(df_count.head(n=2))
print()

# Check product_id : department_id
df_count = merchant_prod_df[['product_id', 'department_id']].astype('str').drop_duplicates()\
          .groupby(['product_id'], as_index=False)['department_id']\
          .agg(['count']).reset_index()\
          .sort_values(['count'], ascending=False)
print(df_count.head(n=2))
print()

# Check aisle_id : department_id
df_count = merchant_prod_df[['aisle_id', 'department_id']].astype('str').drop_duplicates()\
          .groupby(['aisle_id'], as_index=False)['department_id']\
          .agg(['count']).reset_index()\
          .sort_values(['count'], ascending=False)
print(df_count.head(n=2))
print()

# Check department_id : aisle_id
df_count = merchant_prod_df[['aisle_id', 'department_id']].astype('str').drop_duplicates()\
          .groupby(['department_id'], as_index=False)['aisle_id']\
          .agg(['count']).reset_index()\
          .sort_values(['count'], ascending=False)
print(df_count.head(n=2))<jupyter_output>product_id  count
0              1      1
33140      39826      1

      product_id  count
0              1      1
33140      39826      1

   aisle_id  count
0         1      1
84       54      1

  department_id  count
2            11     17
4            13     12<jupyter_text>So the data collection here is correct.* Each productid only has 1 aisle_id and 1 department_id* Each aisle_id only has 1 department_id* Each department has multiple aisle<jupyter_code>print(merchant_prod_df['department'].unique())<jupyter_output>['beverages' 'dairy eggs' 'snacks' 'household' 'produce' 'breakfast'
 'pantry' 'deli' 'frozen' 'personal care' 'meat seafood' 'international'
 'bakery' 'canned goods' 'dry goods pasta' 'alcohol' 'pets' 'babies'
 'other' 'missing' 'bulk']<jupyter_text>Assign price for each product* To make it simple, same product has the same price among all the merchants.* Also assume each order has the same amount for the same product... Since we don't have the data indicating purchase amount here...<jupyter_code>depart_price_range_dct = {
    'beverages': [2, 10], 'dairy eggs': [2, 6], 'snacks': [3, 15], 'household': [4, 30], 'produce': [5, 30], 
    'breakfast': [4, 15], 'pantry': [3, 10], 'deli': [7, 25], 'frozen': [4, 40], 'personal care': [5, 12],  
    'meat seafood': [8, 60], 'international': [5, 30], 'bakery':[3, 17], 'canned goods': [1, 8], 
    'dry goods pasta': [1, 6], 'alcohol': [6, 50], 'pets': [5, 12], 'babies': [10, 40], 
    'other': [9, 28], 'missing': [4, 14], 'bulk': [8, 28]
}
prod_depart_df = merchant_prod_df[['product_id', 'department']].drop_duplicates()
print(prod_depart_df.shape)
prod_depart_df.head()
prod_depart_records = prod_depart_df.values.tolist()

for r in prod_depart_records:
    price_range = depart_price_range_dct[r[1]]
    rand_price = random.uniform(price_range[0], price_range[1])
    r.append(round(rand_price, 2))

print(prod_depart_records[4:10])
prod_depart_df = pd.DataFrame(prod_depart_records)
prod_depart_df.columns = ['product_id', 'department', 'price']
print(prod_depart_df.shape)

print(min(prod_depart_df['price']), max(prod_depart_df['price']))
prod_depart_df.head()
merchant_prod_df = merchant_prod_df.merge(prod_depart_df[['product_id', 'price']], on='product_id')
print(merchant_prod_df.shape)

merchant_prod_df.head()
merchant_prod_df.to_pickle('merchant_product.pkl')<jupyter_output><empty_output><jupyter_text>Time Series Purchase Table<jupyter_code>ts_order_prod_df = order_prod_df[['merchant', 'user_id', 'product_id', 'purchase_date']]
print(ts_order_prod_df.shape)

ts_order_prod_df.head()
prod_price_df = merchant_prod_df[['product_id', 'price']].drop_duplicates()
ts_order_prod_price_df = ts_order_prod_df.merge(prod_price_df, on='product_id')
print(ts_order_prod_price_df.shape)

ts_order_prod_price_df.head()
# Because user_id is to count distinct, while price is to sum all, so cannot calculate the aggregation together.
ts_daily_payment = ts_order_prod_price_df[['merchant', 'purchase_date', 'price']]\
          .groupby(['merchant', 'purchase_date'], as_index=False)['price']\
          .agg(['sum']).reset_index()
print(ts_daily_payment.shape)
ts_daily_payment.head()
ts_daily_clients = ts_order_prod_price_df[['merchant', 'purchase_date', 'user_id']].astype('str').drop_duplicates()\
          .groupby(['merchant', 'purchase_date'], as_index=False)['user_id']\
          .agg(['count']).reset_index()
print(ts_daily_clients.shape)
ts_daily_clients.head()
# When the data type is not string, merge doesn't work, I will just do appending
ts_daily_payment = ts_daily_payment.sort_values(by=['merchant', 'purchase_date'])
ts_daily_clients = ts_daily_clients.sort_values(by=['merchant', 'purchase_date'])
ts_purchase_df = ts_daily_payment.copy()
ts_purchase_df = ts_purchase_df.rename(index=str, columns={'sum': 'purchase_amount'})
ts_purchase_df['client_count'] = ts_daily_clients['count'].values

print(ts_purchase_df.shape)
ts_purchase_df.head()
ts_purchase_df.to_pickle('ts_purchase.pkl')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13932697984313064, 'fraction_numerical': 0.015096567428523236, 'mean_word_length': 5.462125340599455, 'pattern_counts': {'":': 0, '<': 37, '<?xml version=': 0, '>': 36, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '166932', 'license': 'MIT', 'n_tokens_mistral': 4505, 'n_tokens_neox': 4255, 'n_words': 835, 'path': 'Bank_Fantasy/Golden_Bridge/data_preprocessing.ipynb', 'repo_name': 'hanhanwu/Hanhan_Break_the_Limits', 'size': '11857'}