<jupyter_start><jupyter_text>Set-up<jupyter_code># gg drive
from google.colab import drive
drive.mount('/content/gdrive')
# Import packages
import os
import pandas as pd
import numpy as np
import datetime as dt
from sklearn.pipeline import Pipeline
from sklearn.linear_model import LinearRegression
from sklearn.metrics import mean_absolute_error, mean_squared_error
from sklearn.preprocessing import StandardScaler
from sklearn.model_selection import train_test_split
# path to data
path = '/content/gdrive/MyDrive/brazilian_ecommerce'
# read data
df_orders = pd.read_csv(os.path.join(path,'df_order_items_products_customers.csv'))
df_orders.head()<jupyter_output><empty_output><jupyter_text>Data<jupyter_code># create a dataframe contains necessary columns
data = df_orders[['order_id','customer_unique_id','product_id','order_purchase_timestamp',
                    'quantity','price'
                    ]].copy()
data.rename(columns={'order_purchase_timestamp':'purchased_date'},inplace=True)
data.head()
# overal info
data.info()
# change data type 
data['purchased_date'] = pd.to_datetime(data['purchased_date'])
# define purchase year month
data['PurchaseYearMonth'] = data['purchased_date'].dt.to_period('M')
# check timeframe
data['purchased_date'].min(), data['purchased_date'].max()
# first 5 rows of dataframe
data.head()<jupyter_output><empty_output><jupyter_text>Calculation and prediction<jupyter_code># calculate total sales
data['TotalPurchase'] = data['quantity'] * data['price']
# Remove customer ids, which have only records for the last month
# Split into 2 dataframes to check customer ids
data['PurchaseYearMonth'] = data['PurchaseYearMonth'].astype(str)
df_0818 = data[data['PurchaseYearMonth'] == '2018-08']  
df_others = data[data['PurchaseYearMonth'] != '2018-08'] # since we want to predict trans on 08/2018
# get lists of customer ids
cust_ids_0818 = df_0818['customer_unique_id'].unique().tolist()
cust_ids_others = df_others['customer_unique_id'].unique().tolist()
# define customer ids, who made a purchase during the last month only
new_cust_ids = list(set(cust_ids_0818) - set(cust_ids_others))
# remove the customer ids without previous purchasing activity
data = data[~data['customer_unique_id'].isin(new_cust_ids)]
# remove all transaction on 08-2018
data_X = data[data['PurchaseYearMonth'] != '2018-08']
# assume we're are at the 01/08/2018 and want to predict trans in this month
now = dt.datetime(2018, 8, 1)
# calculate recency, frequency, monetary
data_features = data_X.groupby('customer_unique_id').agg({'purchased_date': lambda date: (now - date.max()).days,
                                              'order_id':pd.Series.nunique,
                                              'TotalPurchase': 'sum',
                                              'quantity': ['mean', 'sum']
                                              }).reset_index()
# rename columns
data_features.columns = ['CustomerID', 'Recency', 'Frequency', 
                       'Monetary', 'QuantityAvg', 'QuantityTotal'
                      ]    
data_features.head()                                               
# get monthly transactions for customers
cust_month_trans = pd.pivot_table(data=data,
                                  index=['customer_unique_id'],
                                  values='order_id',
                                  columns=['PurchaseYearMonth'],
                                  aggfunc=pd.Series.nunique,
                                  fill_value=0).reset_index()
# rename index column
cust_month_trans = cust_month_trans.rename_axis('index',axis=1)                                 
cust_month_trans.head()                                 
# define y and X for further model training and prediction
cols = [col for col in data_features.columns if col !='CustomerID']
X = data_features[cols]
y = cust_month_trans[['2018-08']]
X
y
y.value_counts()
# split data
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size = .2, random_state = 42)
# Define the pipeline
pipeline = Pipeline([('scaler', StandardScaler()), 
                           ('model', LinearRegression())                           
                          ])    
# train model
pipeline.fit(X_train,y_train)
# make predictions
train_pred_y = pipeline.predict(X_train)
test_pred_y = pipeline.predict(X_test)
# calculate MAE using scikit-learn
print("MAE:",mean_absolute_error(y_train,train_pred_y))
print("MSE",mean_squared_error(y_train, train_pred_y))
# compute the RMSE of our predictions
print("RMSE:",np.sqrt(mean_squared_error(y_train, train_pred_y)))
# calculate MAE using scikit-learn
print("MAE:",mean_absolute_error(y_test,test_pred_y))
print("MSE",mean_squared_error(y_test, test_pred_y))
# compute the RMSE of our predictions
print("RMSE:",np.sqrt(mean_squared_error(y_test, test_pred_y)))
df_pred = pd.DataFrame(test_pred_y, columns=['prediction'])
df_pred.head()
df_pred.value_counts()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1121627064035441, 'fraction_numerical': 0.014699959726137737, 'mean_word_length': 3.4587073608617596, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 13, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 15, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '254568', 'license': 'MIT', 'n_tokens_mistral': 1553, 'n_tokens_neox': 1464, 'n_words': 329, 'path': '6_Predict_next_month_transaction.ipynb', 'repo_name': 'thaitruong018/e_commerce_analysis', 'size': '4966'}