{"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.10.12","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":10445,"databundleVersionId":200747,"sourceType":"competition"}],"dockerImageVersionId":30918,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"markdown","source":"
Importing Libraries","metadata":{}},{"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nimport gc\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport plotly.express as px\nfrom datetime import datetime\nimport statistics\nfrom sklearn.preprocessing import OneHotEncoder","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-07T16:41:22.811943Z","iopub.execute_input":"2025-05-07T16:41:22.812437Z","iopub.status.idle":"2025-05-07T16:41:26.036368Z","shell.execute_reply.started":"2025-05-07T16:41:22.812389Z","shell.execute_reply":"2025-05-07T16:41:26.034938Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"
Defining Helper Functions","metadata":{}},{"cell_type":"code","source":"def IQROutlierCheck(df, col):\n \n Q1 = df[col].quantile(0.25)\n Q3 = df[col].quantile(0.75)\n IQR = Q3 - Q1\n lower = Q1 - 1.5 * IQR\n upper = Q1 + 1.5 * IQR\n critic_score_outliers = df[(df[col] < lower) | (df[col] > upper)]\n \n return critic_score_outliers","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-07T16:41:26.037874Z","iopub.execute_input":"2025-05-07T16:41:26.038600Z","iopub.status.idle":"2025-05-07T16:41:26.046579Z","shell.execute_reply.started":"2025-05-07T16:41:26.038554Z","shell.execute_reply":"2025-05-07T16:41:26.044576Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"def OutliersInfo(df, cols):\n\n outlier_dict=dict()\n for col in cols: \n print(f\"\\n{col}\")\n print(\"-\"*35)\n critic_outliers = IQROutlierCheck(df, col)\n outlier_dict[col] = critic_outliers.index\n print(f\"Number of outlier samples produced by IQR is {critic_outliers[col].shape[0]}\")\n for i in range(0, 2):\n\n print(\"{}% percentile value is {:3.3f}\".format(i, np.percentile(df[col], i)))\n for i in range(98, 101):\n print(\"{}% percentile value is {:3.3f}\".format(i, np.percentile(df[col], i)))\n\n percent = np.percentile(df[col], 1)\n count = len(df[df[col]percent]) \n print(f\"\\t- Number of values greater than {percent} is {count}\")","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-07T16:41:26.048582Z","iopub.execute_input":"2025-05-07T16:41:26.048985Z","iopub.status.idle":"2025-05-07T16:41:26.071535Z","shell.execute_reply.started":"2025-05-07T16:41:26.048941Z","shell.execute_reply":"2025-05-07T16:41:26.069702Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"
Reading and Combining Transactions Datasets","metadata":{}},{"cell_type":"code","source":"# Define data types for each column to reduce memory usage\ndtypes = {\n 'card_id': 'category',\n 'merchant_id': 'category',\n 'month_lag': 'int8',\n 'authorized_flag': 'category',\n 'category_1': 'category',\n 'installments': 'int16',\n 'purchase_amount': 'float32',\n 'city_id': 'int16',\n 'state_id': 'int8',\n 'subsector_id': 'int8',\n 'merchant_category_id': 'int16',\n 'category_2': 'float32', \n 'category_3': 'category'\n}\n","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-07T16:41:26.072764Z","iopub.execute_input":"2025-05-07T16:41:26.073170Z","iopub.status.idle":"2025-05-07T16:41:26.091240Z","shell.execute_reply.started":"2025-05-07T16:41:26.073124Z","shell.execute_reply":"2025-05-07T16:41:26.089733Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Define chunk size\nchunk_size = 500000\n\n# Initialize lists to store chunks\nhistory_chunks = []\nnew_merchant_chunks = []\n\n# Read historical transactions in chunks\nfor chunk in pd.read_csv('/kaggle/input/elo-merchant-category-recommendation/historical_transactions.csv', chunksize=chunk_size, dtype=dtypes):\n history_chunks.append(chunk)\n\n# Read new merchant transactions in chunks\nfor chunk in pd.read_csv('/kaggle/input/elo-merchant-category-recommendation/new_merchant_transactions.csv', chunksize=chunk_size, dtype=dtypes):\n new_merchant_chunks.append(chunk)\n\n# Combine all chunks from both datasets\ncombined_transactions = pd.concat(history_chunks + new_merchant_chunks, axis=0)\n\n# Clean up memory\ndel history_chunks, new_merchant_chunks\ngc.collect()\n\nprint(combined_transactions.shape)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-07T16:41:26.092678Z","iopub.execute_input":"2025-05-07T16:41:26.093071Z","iopub.status.idle":"2025-05-07T16:44:02.365893Z","shell.execute_reply.started":"2025-05-07T16:41:26.093032Z","shell.execute_reply":"2025-05-07T16:44:02.364713Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"The combination of the two transaction datasets, 'historical_transactions' and 'new_merchant_transactions', likely happened to create a comprehensive view of all transactions for analysis, This unified dataset would allow for a more complete understanding of user behavior and spending patterns.","metadata":{}},{"cell_type":"code","source":"# Displaying the first 5 rows of the combined transactions\n\ncombined_transactions.head()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-07T16:44:02.367406Z","iopub.execute_input":"2025-05-07T16:44:02.367773Z","iopub.status.idle":"2025-05-07T16:44:02.399570Z","shell.execute_reply.started":"2025-05-07T16:44:02.367733Z","shell.execute_reply":"2025-05-07T16:44:02.398229Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Summary of combined transactions DataFrame including data types, non-null counts, and memory usage\n\ncombined_transactions.info()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-07T16:45:53.851359Z","iopub.execute_input":"2025-05-07T16:45:53.851752Z","iopub.status.idle":"2025-05-07T16:45:53.863838Z","shell.execute_reply.started":"2025-05-07T16:45:53.851725Z","shell.execute_reply":"2025-05-07T16:45:53.862686Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"
Data Cleaning","metadata":{}},{"cell_type":"markdown","source":"
Handling Missing Values","metadata":{}},{"cell_type":"code","source":"# Check for missing values in the combined transactions\n\ncombined_transactions.isnull().sum()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-07T16:45:59.440036Z","iopub.execute_input":"2025-05-07T16:45:59.440444Z","iopub.status.idle":"2025-05-07T16:46:07.169342Z","shell.execute_reply.started":"2025-05-07T16:45:59.440404Z","shell.execute_reply":"2025-05-07T16:46:07.167841Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"'merchant_id', 'category_3' and, 'category_2' are Categorical Features, So i will impute the missing values in these three columns using the Most Frequent Value for each of them.","metadata":{}},{"cell_type":"code","source":"# Calculating and Printing the Mode for 'Category_2', 'Category_3', and 'Merchant_id' columns\n\ncategory_2_mode = combined_transactions['category_2'].mode()[0]\ncategory_3_mode = combined_transactions['category_3'].mode()[0]\nmerchant_id_mode = combined_transactions['merchant_id'].mode()[0]\n\nprint(f\"Most Common in category_2 : {category_2_mode}\")\nprint(f\"Most Common in category_3 : {category_3_mode}\")\nprint(f\"Most Common in merchant_id : {merchant_id_mode}\")","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:06:45.638515Z","iopub.execute_input":"2025-05-06T18:06:45.638891Z","iopub.status.idle":"2025-05-06T18:06:55.385890Z","shell.execute_reply.started":"2025-05-06T18:06:45.638860Z","shell.execute_reply":"2025-05-06T18:06:55.384166Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Fill the missing values in 'mercahnt_id', 'category_2', and 'category_3' columns\n\ncombined_transactions['merchant_id'].fillna(merchant_id_mode, inplace=True)\ncombined_transactions['category_3'].fillna(category_3_mode, inplace=True)\ncombined_transactions['category_2'].fillna(category_2_mode, inplace=True)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:06:55.387153Z","iopub.execute_input":"2025-05-06T18:06:55.387510Z","iopub.status.idle":"2025-05-06T18:06:59.481874Z","shell.execute_reply.started":"2025-05-06T18:06:55.387471Z","shell.execute_reply":"2025-05-06T18:06:59.480095Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Check for missing values in combined transactions after filling them\n\ncombined_transactions.isnull().sum()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:06:59.483528Z","iopub.execute_input":"2025-05-06T18:06:59.484062Z","iopub.status.idle":"2025-05-06T18:07:07.350763Z","shell.execute_reply.started":"2025-05-06T18:06:59.483992Z","shell.execute_reply":"2025-05-06T18:07:07.349454Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"
Handling Duplicated Values","metadata":{}},{"cell_type":"code","source":"# Check for duplicate rows in combined transactions\n\ncombined_transactions.duplicated().sum()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:07:07.352164Z","iopub.execute_input":"2025-05-06T18:07:07.352643Z","iopub.status.idle":"2025-05-06T18:09:00.498121Z","shell.execute_reply.started":"2025-05-06T18:07:07.352600Z","shell.execute_reply":"2025-05-06T18:09:00.492317Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"
Handling Outliers","metadata":{}},{"cell_type":"code","source":"# Generate descriptive statistics for numerical columns in combined transactions\n\ncombined_transactions.describe().T","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:09:00.502606Z","iopub.execute_input":"2025-05-06T18:09:00.503870Z","iopub.status.idle":"2025-05-06T18:09:13.125912Z","shell.execute_reply.started":"2025-05-06T18:09:00.503744Z","shell.execute_reply":"2025-05-06T18:09:13.123883Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Display information about outliers\n\ncol = ['installments', 'month_lag', 'purchase_amount','category_2']\nOutliersInfo(combined_transactions, col)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:09:13.128736Z","iopub.execute_input":"2025-05-06T18:09:13.130225Z","iopub.status.idle":"2025-05-06T18:09:32.177849Z","shell.execute_reply.started":"2025-05-06T18:09:13.130138Z","shell.execute_reply":"2025-05-06T18:09:32.176073Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Count unique value in 'installments' column\n\ncombined_transactions['installments'].value_counts()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:09:32.179492Z","iopub.execute_input":"2025-05-06T18:09:32.180075Z","iopub.status.idle":"2025-05-06T18:09:32.374790Z","shell.execute_reply.started":"2025-05-06T18:09:32.179980Z","shell.execute_reply":"2025-05-06T18:09:32.372705Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"'Installments' should be between 0–12 months, however we can see month value of -1 and 999 which may be some error or wrong entries","metadata":{}},{"cell_type":"code","source":"# Count the number of rows where 'purchase_amount' is greater than 2 \n\ncombined_transactions[combined_transactions['purchase_amount'] > 2].count()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:09:32.376097Z","iopub.execute_input":"2025-05-06T18:09:32.376504Z","iopub.status.idle":"2025-05-06T18:09:32.713590Z","shell.execute_reply.started":"2025-05-06T18:09:32.376474Z","shell.execute_reply":"2025-05-06T18:09:32.711629Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Drop the rows where 'purchase_amount' is greater than 2 \n\ncombined_transactions = combined_transactions.drop(combined_transactions[combined_transactions['purchase_amount'] > 2].index)\ncombined_transactions.shape","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:09:32.714747Z","iopub.execute_input":"2025-05-06T18:09:32.715429Z","iopub.status.idle":"2025-05-06T18:10:05.350857Z","shell.execute_reply.started":"2025-05-06T18:09:32.715380Z","shell.execute_reply":"2025-05-06T18:10:05.349352Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Drop rows where 'installments' is -1 or 999\n\nRemove_Values = [-1,999]\ncombined_transactions = combined_transactions[~combined_transactions['installments'].isin(Remove_Values)]\ncombined_transactions.shape","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:10:05.352094Z","iopub.execute_input":"2025-05-06T18:10:05.352465Z","iopub.status.idle":"2025-05-06T18:10:11.588381Z","shell.execute_reply.started":"2025-05-06T18:10:05.352434Z","shell.execute_reply":"2025-05-06T18:10:11.586906Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"
Handling Data Formatting","metadata":{}},{"cell_type":"code","source":"# Convert the 'Purchase_date' column to datatime Type\n\ncombined_transactions['purchase_date'] = pd.to_datetime(combined_transactions['purchase_date'], format='%Y-%m-%d %H:%M:%S')","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:10:11.597441Z","iopub.execute_input":"2025-05-06T18:10:11.598086Z","iopub.status.idle":"2025-05-06T18:10:21.624284Z","shell.execute_reply.started":"2025-05-06T18:10:11.598000Z","shell.execute_reply":"2025-05-06T18:10:21.622979Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"
Data Visualization","metadata":{}},{"cell_type":"markdown","source":"
Create function for categorical features","metadata":{}},{"cell_type":"code","source":"colors = sns.cubehelix_palette(20,reverse = True, light= 0.01,dark = 0.5, gamma= 0.7)\npalette_color = sns.color_palette(\"RdBu\",10)\nsns.set_theme(style=\"whitegrid\", palette=palette_color)\n\ndef bar_plot(counts, column, ax, orient='v'):\n\n if orient=='h':\n x_col = 'count'\n y_col = column\n else:\n x_col = column\n y_col = 'count'\n\n sns.barplot(data = counts, x=x_col, y=y_col, ax=ax, orient=orient)\n\n\n if orient == 'v':\n # Annotate each bar with its height (number of occurrences)\n for p in ax.patches:\n x_coor = p.get_x() + 0.5 * p.get_width()\n y_coor = p.get_height()\n hight = int(p.get_height())\n\n ax.annotate(hight, # Text to be displayed (converted to int for formatting)\n (x_coor, y_coor), # Coordinates of the annotation (x, y)\n ha='center', # Horizontal alignment of the text ('center' aligns it at the center of the x-coordinate)\n va='bottom', # Vertical alignment of the text ('bottom' aligns it at the bottom of the bar)\n color='black' # Color of the text\n )\n ax.set_xlabel(column, weight = \"bold\", fontsize = 14, labelpad = 20)\n ax.set_ylabel('Number of Occurrences', weight = \"bold\", fontsize = 14, labelpad = 20)\n\n ax.tick_params(axis = 'both', labelsize = 12)\n\n return ax","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:10:21.627685Z","iopub.execute_input":"2025-05-06T18:10:21.628138Z","iopub.status.idle":"2025-05-06T18:10:21.647851Z","shell.execute_reply.started":"2025-05-06T18:10:21.628099Z","shell.execute_reply":"2025-05-06T18:10:21.646300Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"def plot_categorical_feature(counts, figsize=(12,5)):\n column = counts.columns[0]\n \n # Create a figure with two subplots\n fig, (ax1, ax2) = plt.subplots(1, 2, figsize=figsize)\n\n # Bar chart\n bar_plot(counts, column, ax1)\n\n # Pie chart\n ax2.pie(counts['count'], labels=counts[column], autopct = '%1.1f%%')\n ax2.axis('equal') # Equal aspect ratio ensures that pie is drawn as a circle.\n ax2.legend(counts[column], loc=\"best\") # Add legned with labels\n\n # Adjust layout\n plt.suptitle(f'Distribution of {column}', weight = \"bold\", fontsize = 16)\n plt.tight_layout()\n plt.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:10:21.649238Z","iopub.execute_input":"2025-05-06T18:10:21.649628Z","iopub.status.idle":"2025-05-06T18:10:21.675670Z","shell.execute_reply.started":"2025-05-06T18:10:21.649597Z","shell.execute_reply":"2025-05-06T18:10:21.674187Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"
Distribution of Authorized Flag","metadata":{}},{"cell_type":"code","source":"authorized_flag_counts = combined_transactions['authorized_flag'].value_counts().reset_index()\nauthorized_flag_counts","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:10:21.677153Z","iopub.execute_input":"2025-05-06T18:10:21.677506Z","iopub.status.idle":"2025-05-06T18:10:23.242898Z","shell.execute_reply.started":"2025-05-06T18:10:21.677479Z","shell.execute_reply":"2025-05-06T18:10:23.241546Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"plot_categorical_feature(authorized_flag_counts)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:10:23.244386Z","iopub.execute_input":"2025-05-06T18:10:23.244902Z","iopub.status.idle":"2025-05-06T18:10:23.708083Z","shell.execute_reply.started":"2025-05-06T18:10:23.244856Z","shell.execute_reply":"2025-05-06T18:10:23.706688Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"The data shows that most transactions (92%) are authorized which mean that the purchase or transaction went through without any issues, while only (8%) are unauthorized which indicates that the transaction was declined or not approved for some reason.","metadata":{}},{"cell_type":"markdown","source":"
Top 25 Card ID","metadata":{}},{"cell_type":"code","source":"Card_ID_Counts = combined_transactions['card_id'].value_counts().head(25).sort_values().reset_index()\nCard_ID_Counts","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:10:23.709437Z","iopub.execute_input":"2025-05-06T18:10:23.709923Z","iopub.status.idle":"2025-05-06T18:10:25.880909Z","shell.execute_reply.started":"2025-05-06T18:10:23.709882Z","shell.execute_reply":"2025-05-06T18:10:25.879393Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"plt.figure(figsize=(10,8))\nsns.barplot(data=Card_ID_Counts,x='count',y='card_id' ,orient='h')\nplt.title('Top 25 Card')\nplt.xlabel('Count')\nplt.ylabel('Card ID')\nplt.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:10:25.882426Z","iopub.execute_input":"2025-05-06T18:10:25.882854Z","iopub.status.idle":"2025-05-06T18:10:26.449276Z","shell.execute_reply.started":"2025-05-06T18:10:25.882820Z","shell.execute_reply":"2025-05-06T18:10:26.447669Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"# The data indicates that a few cards dominate transaction activity:\n\n1- One card (C_ID_3d3dfdc692) has 5,526 transactions—almost double next highest card.\n\n2- The rest of the top 25 cards range between 1,380 and 2,897 transactions, gradually decreasing.","metadata":{}},{"cell_type":"markdown","source":"
Top 25 Merchant ID","metadata":{}},{"cell_type":"code","source":"Merchant_ID_Counts = combined_transactions['merchant_id'].value_counts().head(25).sort_values().reset_index()\nMerchant_ID_Counts","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:10:26.450901Z","iopub.execute_input":"2025-05-06T18:10:26.451408Z","iopub.status.idle":"2025-05-06T18:10:35.919865Z","shell.execute_reply.started":"2025-05-06T18:10:26.451361Z","shell.execute_reply":"2025-05-06T18:10:35.918302Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"plt.figure(figsize=(10,8))\nsns.barplot(data=Merchant_ID_Counts,x='count',y='merchant_id' ,orient='h')\nplt.title('Top 25 Merchant')\nplt.xlabel('Count')\nplt.ylabel('Merchant ID')\nplt.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:10:35.921267Z","iopub.execute_input":"2025-05-06T18:10:35.921658Z","iopub.status.idle":"2025-05-06T18:10:36.471923Z","shell.execute_reply.started":"2025-05-06T18:10:35.921626Z","shell.execute_reply":"2025-05-06T18:10:36.470464Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"# The data shows a few big merchants handle most transactions:\n\n1- One Merchants likely account for over a million transactions.\n\n2- Other merchants in the top 25 handle between 200,000-450,000 transactions\n\n3- The #1 merchant (M_ID_00a6ca8a8a) appears to be the most popular.\n\n# What This Means:\n\n1- Customers mostly shop at frequently-used stores, These could be supermarkets, department stores, or popular service providers\n\n2- The pattern suggests people tend to stick with certain merchants regularly\n\n3- Loyalty scores may depend heavily on these frequent merchant relationships","metadata":{}},{"cell_type":"markdown","source":"
Distribution of Category 1","metadata":{}},{"cell_type":"code","source":"Category_1_Counts = combined_transactions['category_1'].value_counts().reset_index()\nCategory_1_Counts","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:10:36.473474Z","iopub.execute_input":"2025-05-06T18:10:36.473957Z","iopub.status.idle":"2025-05-06T18:10:36.718563Z","shell.execute_reply.started":"2025-05-06T18:10:36.473912Z","shell.execute_reply":"2025-05-06T18:10:36.717254Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"plot_categorical_feature(Category_1_Counts)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:10:36.719689Z","iopub.execute_input":"2025-05-06T18:10:36.720054Z","iopub.status.idle":"2025-05-06T18:10:37.107818Z","shell.execute_reply.started":"2025-05-06T18:10:36.719989Z","shell.execute_reply":"2025-05-06T18:10:37.106395Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"The distribution of 'Category 1' is notably imbalanced, with 93.1% of occurrences labeled as 'N' and only 6.9% labeled as 'Y', highlighting a substantial class imbalance that may affect model performance.","metadata":{}},{"cell_type":"markdown","source":"
Distribution of Installments","metadata":{}},{"cell_type":"code","source":"Installments_Counts = combined_transactions['installments'].value_counts().reset_index()\nInstallments_Counts","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:10:37.109268Z","iopub.execute_input":"2025-05-06T18:10:37.109768Z","iopub.status.idle":"2025-05-06T18:10:37.309397Z","shell.execute_reply.started":"2025-05-06T18:10:37.109719Z","shell.execute_reply":"2025-05-06T18:10:37.308065Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"plt.figure(figsize=(10,8))\nsns.barplot(data=Installments_Counts,x='count',y='installments' ,orient='h')\nplt.title('Distribution of Installments')\nplt.xlabel('Count')\nplt.ylabel('Months')\nplt.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:10:37.310757Z","iopub.execute_input":"2025-05-06T18:10:37.311146Z","iopub.status.idle":"2025-05-06T18:10:37.758366Z","shell.execute_reply.started":"2025-05-06T18:10:37.311110Z","shell.execute_reply":"2025-05-06T18:10:37.756734Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"Most transactions occur with 0 or 1 installment, with a sharp decrease in frequency as the number of installments increases, suggesting that customers generally prefer to pay in full or with a single installment.","metadata":{}},{"cell_type":"markdown","source":"
Distribution of Category 3","metadata":{}},{"cell_type":"code","source":"Category_3_Counts = combined_transactions['category_3'].value_counts().reset_index()\nCategory_3_Counts","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:10:37.759756Z","iopub.execute_input":"2025-05-06T18:10:37.760167Z","iopub.status.idle":"2025-05-06T18:10:38.007326Z","shell.execute_reply.started":"2025-05-06T18:10:37.760135Z","shell.execute_reply":"2025-05-06T18:10:38.004924Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"plot_categorical_feature(Category_3_Counts)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:10:38.008761Z","iopub.execute_input":"2025-05-06T18:10:38.009189Z","iopub.status.idle":"2025-05-06T18:10:38.448414Z","shell.execute_reply.started":"2025-05-06T18:10:38.009154Z","shell.execute_reply":"2025-05-06T18:10:38.446697Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"Category 'A' (53.2%) and 'B' (40.7%) dominate, with 'C' being much less common (6.2%), indicating a strong preference towards categories 'A' and 'B'.","metadata":{}},{"cell_type":"markdown","source":"
Distribution of Month Lag","metadata":{}},{"cell_type":"code","source":"Month_Lag_Counts = combined_transactions['month_lag'].value_counts().reset_index()\nMonth_Lag_Counts","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:10:38.450048Z","iopub.execute_input":"2025-05-06T18:10:38.450619Z","iopub.status.idle":"2025-05-06T18:10:38.622220Z","shell.execute_reply.started":"2025-05-06T18:10:38.450491Z","shell.execute_reply":"2025-05-06T18:10:38.620873Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"plt.figure(figsize=(10,8))\nsns.barplot(data=Month_Lag_Counts,x='count',y='month_lag' ,orient='h')\nplt.title('Distribution of Month Lag')\nplt.xlabel('Count')\nplt.ylabel('Months')\nplt.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:10:38.623714Z","iopub.execute_input":"2025-05-06T18:10:38.624167Z","iopub.status.idle":"2025-05-06T18:10:39.070592Z","shell.execute_reply.started":"2025-05-06T18:10:38.624126Z","shell.execute_reply":"2025-05-06T18:10:39.069101Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"Most activity (+2.5 M transactions each) happened in the 4 months before the reference date or the same purchase date (lag months = 0), while older transactions (months -13 to -5) and future ones (months 1-2) appear less frequently, Which mean that the data focuses heavily on recent purchases.","metadata":{}},{"cell_type":"markdown","source":"
Distribution of Category 2","metadata":{}},{"cell_type":"code","source":"Category_2_Counts = combined_transactions['category_2'].value_counts().reset_index()\nCategory_2_Counts","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:10:39.072383Z","iopub.execute_input":"2025-05-06T18:10:39.072929Z","iopub.status.idle":"2025-05-06T18:10:39.399846Z","shell.execute_reply.started":"2025-05-06T18:10:39.072873Z","shell.execute_reply":"2025-05-06T18:10:39.398089Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"plot_categorical_feature(Category_2_Counts)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:10:39.401387Z","iopub.execute_input":"2025-05-06T18:10:39.401879Z","iopub.status.idle":"2025-05-06T18:10:39.909038Z","shell.execute_reply.started":"2025-05-06T18:10:39.401823Z","shell.execute_reply":"2025-05-06T18:10:39.907402Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"Category 2 is highly imbalanced, with the majority value being 1.0 (61.2%), while other categories are much less frequent.","metadata":{}},{"cell_type":"markdown","source":"
Distribution of Top 25 Card Purchased","metadata":{}},{"cell_type":"code","source":"grouped = combined_transactions.groupby('card_id')['purchase_amount'].sum().reset_index().sort_values(by='purchase_amount',ascending=False).head(25)\ngrouped","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:10:39.910716Z","iopub.execute_input":"2025-05-06T18:10:39.911254Z","iopub.status.idle":"2025-05-06T18:10:44.836132Z","shell.execute_reply.started":"2025-05-06T18:10:39.911204Z","shell.execute_reply":"2025-05-06T18:10:44.834837Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"top_25_card_Purchased = grouped['card_id'].tolist()\nfiltered_trans = combined_transactions[combined_transactions['card_id'].isin(top_25_card_Purchased)]\nfiltered_trans","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:10:44.837223Z","iopub.execute_input":"2025-05-06T18:10:44.837541Z","iopub.status.idle":"2025-05-06T18:10:47.899082Z","shell.execute_reply.started":"2025-05-06T18:10:44.837516Z","shell.execute_reply":"2025-05-06T18:10:47.897455Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"fig = px.box(filtered_trans,x='card_id',y='purchase_amount')\n\nfont_config = {\n 'family': 'Arial', # Font family\n 'size': 24, # Font size\n 'color': 'Black' # Font color\n }\n\ntitle_config = {\n 'text': \"Purchase Amount For Top 25 Card\", # The title text\n 'x': 0.5, # x-position of the title (0 to 1, where 0.5 is centered)\n 'xanchor': 'center', # Anchor point for the title's x position\n 'y': 0.95, # y-position of the title (0 to 1)\n 'yanchor': 'top', # Anchor point for the title's y position\n 'font': font_config\n }\n\nxaxis_config={\n 'title': 'Card ID', # Axis title text\n 'titlefont': {'size': 16, 'color': 'DarkBlue'}, # Font properties for the axis title\n 'tickangle': -45, # Angle of tick labels (degrees)\n 'gridcolor': 'LightGray', # Grid line color\n }\n\nyaxis_config={\n 'title': 'Purchase Amount',\n 'titlefont': {'size': 16, 'color': 'DarkBlue'},\n }\n\nfig.update_layout(title= title_config,xaxis=xaxis_config, yaxis=yaxis_config)\nfig.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:10:47.900952Z","iopub.execute_input":"2025-05-06T18:10:47.901581Z","iopub.status.idle":"2025-05-06T18:10:49.667525Z","shell.execute_reply.started":"2025-05-06T18:10:47.901482Z","shell.execute_reply":"2025-05-06T18:10:49.666204Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"
Most Purchased Month","metadata":{}},{"cell_type":"code","source":"Most_Purchased_Month = combined_transactions.groupby(combined_transactions['purchase_date'].dt.month)['card_id'].count().reset_index()\nMost_Purchased_Month","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:10:49.669245Z","iopub.execute_input":"2025-05-06T18:10:49.669749Z","iopub.status.idle":"2025-05-06T18:10:52.988439Z","shell.execute_reply.started":"2025-05-06T18:10:49.669706Z","shell.execute_reply":"2025-05-06T18:10:52.987154Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"sns.lineplot(data=Most_Purchased_Month,x='purchase_date',y='card_id')\nplt.title('Transactions Over Months')\nplt.xlabel('Month')\nplt.ylabel('Count of Transactions')\nplt.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:10:52.989492Z","iopub.execute_input":"2025-05-06T18:10:52.989868Z","iopub.status.idle":"2025-05-06T18:10:53.331042Z","shell.execute_reply.started":"2025-05-06T18:10:52.989833Z","shell.execute_reply":"2025-05-06T18:10:53.329495Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"There is a notable seasonal trend: transactions are highest at the beginning and end of the year and reach their lowest point around month 5, followed by a steady increase towards the end of the year.","metadata":{}},{"cell_type":"markdown","source":"
Distribution of Purchased Amount","metadata":{}},{"cell_type":"code","source":"sns.histplot(data=combined_transactions,x='purchase_amount',kde=True,bins=20)\nplt.title('Distribution of Purchased Amount')\nplt.xlabel('Purchased Amount')\nplt.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:10:53.332581Z","iopub.execute_input":"2025-05-06T18:10:53.333069Z","iopub.status.idle":"2025-05-06T18:12:55.515375Z","shell.execute_reply.started":"2025-05-06T18:10:53.332997Z","shell.execute_reply":"2025-05-06T18:12:55.513682Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"# The purchase amounts show some clear trends:\n\n1- Most purchases are small - The vast majority (20M+ transactions) are tiny amounts near zero\n\n2- Big purchases are rare\n\n3- Some negative values exist - These likely represent refunds or returns\n\n# Why This Matters for Loyalty:\n\n1- Customers mainly make small, frequent purchases\n\n2- The occasional big spenders might be High-value customers or People making special purchases\n\n3- Negative amounts (refunds) could indicate unhappy customers","metadata":{}},{"cell_type":"markdown","source":"
KDE plot for Purchase Date","metadata":{}},{"cell_type":"code","source":"sns.displot(combined_transactions['purchase_date'].dt.year,kind='kde')\nplt.title('Kde plot for purchase date')\nplt.show()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:12:55.517094Z","iopub.execute_input":"2025-05-06T18:12:55.517537Z","iopub.status.idle":"2025-05-06T18:15:20.488995Z","shell.execute_reply.started":"2025-05-06T18:12:55.517469Z","shell.execute_reply":"2025-05-06T18:15:20.487183Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"The data shows two distinct shopping times Major Spike - Jan 2017 (very high), Smaller Spike - Jan 2018 (moderate), Dead Zone - Almost no activity between these peaks, and Customer behavior might have changed between 2017-2018, this could affect model accuracy","metadata":{}},{"cell_type":"markdown","source":"
Data Preprocessing","metadata":{}},{"cell_type":"markdown","source":"
Feature Encoding","metadata":{}},{"cell_type":"code","source":"# Encode categorical features to numerical \n\ncombined_transactions['authorized_flag'] = combined_transactions['authorized_flag'].map({'Y': 1 , 'N': 0})\ncombined_transactions['category_1'] = combined_transactions['category_1'].map({'Y': 1 , 'N': 0})\ncombined_transactions['category_3'] = combined_transactions['category_3'].map({'A': 1 , 'B': 2 , 'C': 3})","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:15:20.490574Z","iopub.execute_input":"2025-05-06T18:15:20.490951Z","iopub.status.idle":"2025-05-06T18:15:21.916205Z","shell.execute_reply.started":"2025-05-06T18:15:20.490920Z","shell.execute_reply":"2025-05-06T18:15:21.914586Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"
Feature Engineering on Purchase Date and Month Lag","metadata":{}},{"cell_type":"markdown","source":"I will add five new columns in the transactions_df dataframe: purchase_year, purchase_month, purchase_day, purchase_dow, and purchase_hour which contain the year, month, day, day of the week, and hour for each transaction, respectively. The dayofweek attribute returns the day of the week as an integer, where Monday is 0 and Sunday is 6. Note that we have also converted the year and month columns to categorical data type, as they are categorical variables. Steps:\n
    \n
  1. Converting the purchase_date column to a datetime object.
    2. \n
  2. Creating new columns for weekday, weekend indicator, month, day of year, week of year, hour, and holiday indicator.
    3. \n
  3. Calculating the number of months since each transaction using month_diff.
    4. \n
","metadata":{}},{"cell_type":"code","source":"# Extract the year from 'purchase_date' column and create 'purchase_year' column\n\ncombined_transactions['purchase_year'] = combined_transactions['purchase_date'].dt.year","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:15:21.917691Z","iopub.execute_input":"2025-05-06T18:15:21.918336Z","iopub.status.idle":"2025-05-06T18:15:23.373734Z","shell.execute_reply.started":"2025-05-06T18:15:21.918275Z","shell.execute_reply":"2025-05-06T18:15:23.372170Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Extract the month from 'purchase_date' column and create 'purchase_month' column\n\n\ncombined_transactions['purchase_month'] = combined_transactions['purchase_date'].dt.month","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:15:23.375589Z","iopub.execute_input":"2025-05-06T18:15:23.376076Z","iopub.status.idle":"2025-05-06T18:15:24.839241Z","shell.execute_reply.started":"2025-05-06T18:15:23.376033Z","shell.execute_reply":"2025-05-06T18:15:24.837764Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Extract the day from 'purchase_date' column and create 'purchase_day' column\n\n\ncombined_transactions['purchase_day'] = combined_transactions['purchase_date'].dt.day","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:15:24.840597Z","iopub.execute_input":"2025-05-06T18:15:24.841123Z","iopub.status.idle":"2025-05-06T18:15:26.320978Z","shell.execute_reply.started":"2025-05-06T18:15:24.841070Z","shell.execute_reply":"2025-05-06T18:15:26.319535Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Extract the day of week from 'purchase_date' column and create 'purchase_dow' column\n\n\ncombined_transactions['purchase_dow'] = combined_transactions['purchase_date'].dt.day_of_week","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:15:26.322591Z","iopub.execute_input":"2025-05-06T18:15:26.323136Z","iopub.status.idle":"2025-05-06T18:15:28.018961Z","shell.execute_reply.started":"2025-05-06T18:15:26.323076Z","shell.execute_reply":"2025-05-06T18:15:28.017582Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Extract the hour from 'purchase_date' column and create 'purchase_hour' column\n\n\ncombined_transactions['purchase_hour'] = combined_transactions['purchase_date'].dt.hour","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:15:28.020115Z","iopub.execute_input":"2025-05-06T18:15:28.020615Z","iopub.status.idle":"2025-05-06T18:15:29.480165Z","shell.execute_reply.started":"2025-05-06T18:15:28.020566Z","shell.execute_reply":"2025-05-06T18:15:29.478670Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Create 'is_weekend' column: 1 if 'purchase_dow' is Saturday (5) or Sunday (6), 0 otherwise\n\ncombined_transactions['is_weekend'] = np.where(combined_transactions['purchase_dow'].isin([5, 6]), 1, 0)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:15:29.481640Z","iopub.execute_input":"2025-05-06T18:15:29.482211Z","iopub.status.idle":"2025-05-06T18:15:30.414411Z","shell.execute_reply.started":"2025-05-06T18:15:29.482152Z","shell.execute_reply":"2025-05-06T18:15:30.413080Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Define time intervals based on the 'purchase_hour' column to Create 'purchase_at' column\n\ntime_day = [\n (combined_transactions['purchase_hour'] >= 6) & (combined_transactions['purchase_hour'] < 12),\n (combined_transactions['purchase_hour'] >= 12) & (combined_transactions['purchase_hour'] < 15),\n (combined_transactions['purchase_hour'] >= 15) & (combined_transactions['purchase_hour'] < 18),\n (combined_transactions['purchase_hour'] >= 18) & (combined_transactions['purchase_hour'] < 24)\n]","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:15:30.415788Z","iopub.execute_input":"2025-05-06T18:15:30.416330Z","iopub.status.idle":"2025-05-06T18:15:30.791424Z","shell.execute_reply.started":"2025-05-06T18:15:30.416289Z","shell.execute_reply":"2025-05-06T18:15:30.790180Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Define labels for different time periods of the day\n\npurchase_at = ['Morning', 'Noon', 'Afternoon', 'Evening']\n\n# Create 'purchase_at' column based on 'time_day' conditions and 'purchase_at' labels\n\ncombined_transactions['purchase_at'] = np.select(time_day, purchase_at, default='Night')","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:15:30.792785Z","iopub.execute_input":"2025-05-06T18:15:30.793291Z","iopub.status.idle":"2025-05-06T18:15:41.647314Z","shell.execute_reply.started":"2025-05-06T18:15:30.793241Z","shell.execute_reply":"2025-05-06T18:15:41.645905Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Get today's date\n\ntoday = np.datetime64(datetime.today())","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:15:41.648497Z","iopub.execute_input":"2025-05-06T18:15:41.648966Z","iopub.status.idle":"2025-05-06T18:15:41.654512Z","shell.execute_reply.started":"2025-05-06T18:15:41.648922Z","shell.execute_reply":"2025-05-06T18:15:41.652846Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Calculate the difference in days between today and 'purchase_date'\n\ndays_difference = (today - combined_transactions['purchase_date'].values).astype('timedelta64[D]').astype(int)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:15:41.656039Z","iopub.execute_input":"2025-05-06T18:15:41.656548Z","iopub.status.idle":"2025-05-06T18:15:42.425468Z","shell.execute_reply.started":"2025-05-06T18:15:41.656513Z","shell.execute_reply":"2025-05-06T18:15:42.424199Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Calculate the months difference, assuming 30 days per month\n\ncombined_transactions['month_difference'] = (days_difference // 30).astype('int16')","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:15:42.426971Z","iopub.execute_input":"2025-05-06T18:15:42.427367Z","iopub.status.idle":"2025-05-06T18:15:42.683775Z","shell.execute_reply.started":"2025-05-06T18:15:42.427333Z","shell.execute_reply":"2025-05-06T18:15:42.682554Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Adjust 'month_difference' by substracting 'month_lag'\n\ncombined_transactions['month_difference'] = combined_transactions['month_difference'] - combined_transactions['month_lag']","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:15:42.685137Z","iopub.execute_input":"2025-05-06T18:15:42.685528Z","iopub.status.idle":"2025-05-06T18:15:42.783808Z","shell.execute_reply.started":"2025-05-06T18:15:42.685500Z","shell.execute_reply":"2025-05-06T18:15:42.782571Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Convert 'purchase_date' column to datetime objects\n\ncombined_transactions['purchase_date'] = pd.to_datetime(combined_transactions['purchase_date'])","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:15:42.785034Z","iopub.execute_input":"2025-05-06T18:15:42.785414Z","iopub.status.idle":"2025-05-06T18:15:42.955714Z","shell.execute_reply.started":"2025-05-06T18:15:42.785378Z","shell.execute_reply":"2025-05-06T18:15:42.954172Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Display the first rows of the Data\n\npd.set_option('display.max_columns', None)\ncombined_transactions.head()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:15:42.957149Z","iopub.execute_input":"2025-05-06T18:15:42.957681Z","iopub.status.idle":"2025-05-06T18:15:42.984366Z","shell.execute_reply.started":"2025-05-06T18:15:42.957635Z","shell.execute_reply":"2025-05-06T18:15:42.982472Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"
First Features Aggregation","metadata":{}},{"cell_type":"code","source":"# Create Aggregation dictionary to define how to aggregate columns\n\naggregate_tbl = {\n 'purchase_date':['min','max'],\n 'purchase_year': [statistics.mode],\n 'purchase_month': ['mean',statistics.mode],\n 'purchase_day': [statistics.mode],\n 'purchase_dow': [statistics.mode],\n 'purchase_hour': ['min','max',statistics.mode],\n 'is_weekend': ['mean','sum',statistics.mode],\n 'month_difference': ['sum','mean','min','max'] \n }\n","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:15:42.986053Z","iopub.execute_input":"2025-05-06T18:15:42.986477Z","iopub.status.idle":"2025-05-06T18:15:43.005082Z","shell.execute_reply.started":"2025-05-06T18:15:42.986440Z","shell.execute_reply":"2025-05-06T18:15:43.003410Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Group the 'combined_transactions' by 'card_id' and apply the aggregations \n\naggregate_purchase_date = combined_transactions.groupby('card_id').agg(aggregate_tbl).reset_index()\naggregate_purchase_date.head()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:15:43.017313Z","iopub.execute_input":"2025-05-06T18:15:43.017794Z","iopub.status.idle":"2025-05-06T18:17:07.661457Z","shell.execute_reply.started":"2025-05-06T18:15:43.017759Z","shell.execute_reply":"2025-05-06T18:17:07.660192Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Rename the columns of the aggregated Dataframe with 'trans_' prefix and concatenated column names\n\naggregate_purchase_date.columns = ['trans_'+'_'.join(col).strip() for col in aggregate_purchase_date.columns.values]\naggregate_purchase_date = aggregate_purchase_date.rename(columns={'trans_card_id_':'card_id'})\naggregate_purchase_date.head()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:17:07.663250Z","iopub.execute_input":"2025-05-06T18:17:07.663591Z","iopub.status.idle":"2025-05-06T18:17:07.711248Z","shell.execute_reply.started":"2025-05-06T18:17:07.663555Z","shell.execute_reply":"2025-05-06T18:17:07.709795Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Calculate the number of days since the first and last transactions for each card_id \n\naggregate_purchase_date['first_transaction'] = (datetime.today() - aggregate_purchase_date['trans_purchase_date_min']).dt.days\naggregate_purchase_date['last_transaction'] = (datetime.today() - aggregate_purchase_date['trans_purchase_date_max']).dt.days","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:17:07.712436Z","iopub.execute_input":"2025-05-06T18:17:07.712782Z","iopub.status.idle":"2025-05-06T18:17:07.752534Z","shell.execute_reply.started":"2025-05-06T18:17:07.712754Z","shell.execute_reply":"2025-05-06T18:17:07.750881Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Intialize OneHotEncoder to convert categorical 'purchase_at' to numerical feature\n\nencoder = OneHotEncoder()\none_hot = encoder.fit_transform(combined_transactions[['purchase_at']])","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:17:07.754264Z","iopub.execute_input":"2025-05-06T18:17:07.754836Z","iopub.status.idle":"2025-05-06T18:17:18.852923Z","shell.execute_reply.started":"2025-05-06T18:17:07.754780Z","shell.execute_reply":"2025-05-06T18:17:18.851421Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Create Dataframe from the one-hot encoded array with feature names\n\ndf = pd.DataFrame(one_hot.toarray(),columns=encoder.get_feature_names_out())\ndf.head()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:17:18.854621Z","iopub.execute_input":"2025-05-06T18:17:18.855182Z","iopub.status.idle":"2025-05-06T18:17:21.205707Z","shell.execute_reply.started":"2025-05-06T18:17:18.855131Z","shell.execute_reply":"2025-05-06T18:17:21.204463Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Reset the index of combined_transactions\ncombined_transactions = combined_transactions.reset_index(drop=True)\n\n#Reset index of df\ndf = df.reset_index(drop=True)\n\n# Concatenate the 'combined_transactions' DataFrame with the one-hot encoded DataFrame\ncombined_transactions = pd.concat([combined_transactions, df], axis=1)\ncombined_transactions.head()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:17:21.207054Z","iopub.execute_input":"2025-05-06T18:17:21.207564Z","iopub.status.idle":"2025-05-06T18:17:42.064824Z","shell.execute_reply.started":"2025-05-06T18:17:21.207509Z","shell.execute_reply":"2025-05-06T18:17:42.063227Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"
Feature Engineering and Aggregation on Purchase Amount","metadata":{}},{"cell_type":"markdown","source":"Computes several aggregate statistics ontransactions the purchase_amount column for each group. The computed statistics are the minimum, maximum, mean, median, standard deviation, sum, and count.This aggregated data gives an overview of each customer's spending behavior:\n\n
    \n
  • High maximum or average amounts indicate big ticket purchases
    • \n
  • A wide spread (high standard deviation) suggests irregular spending
    • \n
  • A high total spend sum purchase_amount captures overall expenditures
    • \n
  • The count of transactions reveals purchase frequency
    • \n
","metadata":{}},{"cell_type":"code","source":"# Group the 'combined_transactions' DataFrame by 'card_id' and aggregate 'purchase_amount'\n\naggregate_purchase_amount = combined_transactions.groupby('card_id')['purchase_amount'].agg(['sum','max','min','mean','median']).reset_index()\naggregate_purchase_amount.head()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:17:42.066402Z","iopub.execute_input":"2025-05-06T18:17:42.066813Z","iopub.status.idle":"2025-05-06T18:17:49.500832Z","shell.execute_reply.started":"2025-05-06T18:17:42.066780Z","shell.execute_reply":"2025-05-06T18:17:49.499051Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Rename the columns of the 'aggregate_purchase_amount' DataFrame\n\naggregate_purchase_amount.columns = ['trans_purchase_amount_'+''.join(col).strip() for col in aggregate_purchase_amount.columns.values]\naggregate_purchase_amount = aggregate_purchase_amount.rename(columns={'trans_purchase_amount_card_id':'card_id'})\naggregate_purchase_amount.head()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:17:49.502094Z","iopub.execute_input":"2025-05-06T18:17:49.502537Z","iopub.status.idle":"2025-05-06T18:17:49.540272Z","shell.execute_reply.started":"2025-05-06T18:17:49.502498Z","shell.execute_reply":"2025-05-06T18:17:49.538736Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"
Third Features Aggregation","metadata":{}},{"cell_type":"code","source":"# Define an aggregation dictionary\n\naggregate_tbl2 = {\n 'authorized_flag': ['sum','mean'],\n 'city_id': [statistics.mode,'nunique'],\n 'category_1': ['sum','mean',statistics.mode],\n 'category_2': ['sum','mean',statistics.mode],\n 'category_3': ['sum','mean',statistics.mode],\n 'installments': ['mean','sum','min','max',statistics.mode],\n 'merchant_category_id': [statistics.mode,'nunique'],\n 'merchant_id': [statistics.mode,'nunique'],\n 'state_id': [statistics.mode,'nunique'],\n 'subsector_id': ['nunique',statistics.mode],\n 'purchase_at_Afternoon': [statistics.mode,'sum','mean'],\n 'purchase_at_Evening': [statistics.mode,'sum','mean'],\n 'purchase_at_Morning': [statistics.mode,'sum','mean'],\n 'purchase_at_Night': [statistics.mode,'sum','mean'],\n 'purchase_at_Noon': [statistics.mode,'sum','mean']\n}","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:17:49.541731Z","iopub.execute_input":"2025-05-06T18:17:49.542187Z","iopub.status.idle":"2025-05-06T18:17:49.549923Z","shell.execute_reply.started":"2025-05-06T18:17:49.542144Z","shell.execute_reply":"2025-05-06T18:17:49.548342Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Dropping 'purchase_at' column\n\ncombined_transactions = combined_transactions.drop(columns='purchase_at',axis=1)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:17:49.551532Z","iopub.execute_input":"2025-05-06T18:17:49.551897Z","iopub.status.idle":"2025-05-06T18:17:52.806865Z","shell.execute_reply.started":"2025-05-06T18:17:49.551865Z","shell.execute_reply":"2025-05-06T18:17:52.805541Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Convert category columns to object\n\ncombined_transactions['category_1'] = combined_transactions['category_1'].astype('object')\ncombined_transactions['category_3'] = combined_transactions['category_3'].astype('object')","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:17:52.808268Z","iopub.execute_input":"2025-05-06T18:17:52.808656Z","iopub.status.idle":"2025-05-06T18:17:54.784783Z","shell.execute_reply.started":"2025-05-06T18:17:52.808624Z","shell.execute_reply":"2025-05-06T18:17:54.783546Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Group 'combined_transactions' by 'card_id' and apply aggregations defined \n\naggregate_features = combined_transactions.groupby('card_id').agg(aggregate_tbl2).reset_index()\naggregate_features.head()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:17:54.785920Z","iopub.execute_input":"2025-05-06T18:17:54.786326Z","iopub.status.idle":"2025-05-06T18:23:00.611760Z","shell.execute_reply.started":"2025-05-06T18:17:54.786293Z","shell.execute_reply":"2025-05-06T18:23:00.610198Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Rename the columns of the 'aggregate_features' DataFrame\n\naggregate_features.columns = [str(uppers)+'_'+str(lowers) for uppers, lowers in aggregate_features.columns.values]\naggregate_features.head()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:23:00.613487Z","iopub.execute_input":"2025-05-06T18:23:00.613906Z","iopub.status.idle":"2025-05-06T18:23:00.663936Z","shell.execute_reply.started":"2025-05-06T18:23:00.613865Z","shell.execute_reply":"2025-05-06T18:23:00.662415Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Concatenate 'aggregate_purchase_date', 'aggregate_purchase_amount' and 'aggregate_features'\n\naggregate_transactions = pd.concat([aggregate_purchase_date,aggregate_purchase_amount,aggregate_features],axis=1)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:23:00.665335Z","iopub.execute_input":"2025-05-06T18:23:00.665742Z","iopub.status.idle":"2025-05-06T18:23:01.086302Z","shell.execute_reply.started":"2025-05-06T18:23:00.665701Z","shell.execute_reply":"2025-05-06T18:23:01.084753Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Display the number of rows and columns of 'aggregate_transactions'\n\naggregate_transactions.shape","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:23:01.087579Z","iopub.execute_input":"2025-05-06T18:23:01.087946Z","iopub.status.idle":"2025-05-06T18:23:01.095489Z","shell.execute_reply.started":"2025-05-06T18:23:01.087915Z","shell.execute_reply":"2025-05-06T18:23:01.093934Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Checking the number of null values in the 'aggregate_transactions' DataFrame\n\naggregate_transactions.isnull().sum().sum()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:23:01.096952Z","iopub.execute_input":"2025-05-06T18:23:01.097409Z","iopub.status.idle":"2025-05-06T18:23:01.573792Z","shell.execute_reply.started":"2025-05-06T18:23:01.097372Z","shell.execute_reply":"2025-05-06T18:23:01.572305Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Perform garbage collection to free up memory \n\ngc.collect()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:23:01.575078Z","iopub.execute_input":"2025-05-06T18:23:01.575477Z","iopub.status.idle":"2025-05-06T18:23:01.778927Z","shell.execute_reply.started":"2025-05-06T18:23:01.575435Z","shell.execute_reply":"2025-05-06T18:23:01.777382Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Save the 'aggregate_transactions' DataFrame to CSV file without the Index\naggregate_transactions.to_csv('/kaggle/working/Aggregate_Transactions.csv', index=False)\n\nprint(\"aggregate_transactions saved to /kaggle/working/aggregate_transactions.csv\")","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-05-06T18:23:01.780635Z","iopub.execute_input":"2025-05-06T18:23:01.781243Z","iopub.status.idle":"2025-05-06T18:23:20.754827Z","shell.execute_reply.started":"2025-05-06T18:23:01.781184Z","shell.execute_reply":"2025-05-06T18:23:20.753061Z"}},"outputs":[],"execution_count":null}]}