{"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":"<div style=\"padding:10px;\n            color:#E55604;\n            margin:10px;\n            font-size:130%;\n            display:fill;\n            border-radius:5px;\n            border-style: solid;\n            border-color: #FF9B50;\n            background-color:#000000;\n            overflow:hidden;\n            font-weight:400\"><b>Importing Libraries</b>","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-03-26T23:46:33.769088Z","iopub.execute_input":"2025-03-26T23:46:33.769367Z","iopub.status.idle":"2025-03-26T23:46:35.410917Z","shell.execute_reply.started":"2025-03-26T23:46:33.769342Z","shell.execute_reply":"2025-03-26T23:46:35.410133Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"<div style=\"padding:10px;\n            color:#E55604;\n            margin:10px;\n            font-size:130%;\n            display:fill;\n            border-radius:5px;\n            border-style: solid;\n            border-color: #FF9B50;\n            background-color:#000000;\n            overflow:hidden;\n            font-weight:400\"><b>Defining Functions</b>","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-03-26T23:46:35.411982Z","iopub.execute_input":"2025-03-26T23:46:35.412431Z","iopub.status.idle":"2025-03-26T23:46:35.416637Z","shell.execute_reply.started":"2025-03-26T23:46:35.412406Z","shell.execute_reply":"2025-03-26T23:46:35.415814Z"}},"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\"\\n\\t- Number of values less than {percent} is {count}\")\n        percent = np.percentile(df[col], 99)\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-03-26T23:46:35.418443Z","iopub.execute_input":"2025-03-26T23:46:35.418695Z","iopub.status.idle":"2025-03-26T23:46:35.435695Z","shell.execute_reply.started":"2025-03-26T23:46:35.418675Z","shell.execute_reply":"2025-03-26T23:46:35.434916Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"<div style=\"padding:10px;\n            color:#E55604;\n            margin:10px;\n            font-size:130%;\n            display:fill;\n            border-radius:5px;\n            border-style: solid;\n            border-color: #FF9B50;\n            background-color:#000000;\n            overflow:hidden;\n            font-weight:400\"><b>Reading and Combining Transactions</b>","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-03-26T23:46:35.436569Z","iopub.execute_input":"2025-03-26T23:46:35.43681Z","iopub.status.idle":"2025-03-26T23:46:35.450459Z","shell.execute_reply.started":"2025-03-26T23:46:35.436792Z","shell.execute_reply":"2025-03-26T23:46:35.449723Z"}},"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)\ncombined_transactions.head()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-03-26T23:46:35.451216Z","iopub.execute_input":"2025-03-26T23:46:35.451437Z","iopub.status.idle":"2025-03-26T23:47:59.107453Z","shell.execute_reply.started":"2025-03-26T23:46:35.451421Z","shell.execute_reply":"2025-03-26T23:47:59.106654Z"}},"outputs":[],"execution_count":null},{"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-03-26T23:47:59.108281Z","iopub.execute_input":"2025-03-26T23:47:59.108509Z","iopub.status.idle":"2025-03-26T23:47:59.119694Z","shell.execute_reply.started":"2025-03-26T23:47:59.108491Z","shell.execute_reply":"2025-03-26T23:47:59.118918Z"}},"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-03-26T23:47:59.120485Z","iopub.execute_input":"2025-03-26T23:47:59.120715Z","iopub.status.idle":"2025-03-26T23:47:59.146202Z","shell.execute_reply.started":"2025-03-26T23:47:59.120691Z","shell.execute_reply":"2025-03-26T23:47:59.145371Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"<div style=\"padding:10px;\n            color:#E55604;\n            margin:10px;\n            font-size:130%;\n            display:fill;\n            border-radius:5px;\n            border-style: solid;\n            border-color: #FF9B50;\n            background-color:#000000;\n            overflow:hidden;\n            font-weight:400\"><b>Handling Missing Values</b>","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-03-26T23:47:59.146904Z","iopub.execute_input":"2025-03-26T23:47:59.147143Z","iopub.status.idle":"2025-03-26T23:48:03.993371Z","shell.execute_reply.started":"2025-03-26T23:47:59.147124Z","shell.execute_reply":"2025-03-26T23:48:03.992662Z"}},"outputs":[],"execution_count":null},{"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-03-26T23:48:03.995701Z","iopub.execute_input":"2025-03-26T23:48:03.995948Z","iopub.status.idle":"2025-03-26T23:48:08.800479Z","shell.execute_reply.started":"2025-03-26T23:48:03.995927Z","shell.execute_reply":"2025-03-26T23:48:08.799682Z"}},"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-03-26T23:48:08.801623Z","iopub.execute_input":"2025-03-26T23:48:08.801777Z","iopub.status.idle":"2025-03-26T23:48:11.096233Z","shell.execute_reply.started":"2025-03-26T23:48:08.801763Z","shell.execute_reply":"2025-03-26T23:48:11.095151Z"}},"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-03-26T23:48:11.097109Z","iopub.execute_input":"2025-03-26T23:48:11.097417Z","iopub.status.idle":"2025-03-26T23:48:15.704572Z","shell.execute_reply.started":"2025-03-26T23:48:11.097389Z","shell.execute_reply":"2025-03-26T23:48:15.703791Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"<div style=\"padding:10px;\n            color:#E55604;\n            margin:10px;\n            font-size:130%;\n            display:fill;\n            border-radius:5px;\n            border-style: solid;\n            border-color: #FF9B50;\n            background-color:#000000;\n            overflow:hidden;\n            font-weight:400\"><b>Handling Duplicated Values</b>","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-03-26T23:48:15.705247Z","iopub.execute_input":"2025-03-26T23:48:15.705433Z","iopub.status.idle":"2025-03-26T23:49:00.091581Z","shell.execute_reply.started":"2025-03-26T23:48:15.705417Z","shell.execute_reply":"2025-03-26T23:49:00.090901Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"<div style=\"padding:10px;\n            color:#E55604;\n            margin:10px;\n            font-size:130%;\n            display:fill;\n            border-radius:5px;\n            border-style: solid;\n            border-color: #FF9B50;\n            background-color:#000000;\n            overflow:hidden;\n            font-weight:400\"><b>Handling Outliers</b>","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-03-26T23:49:00.092213Z","iopub.execute_input":"2025-03-26T23:49:00.092389Z","iopub.status.idle":"2025-03-26T23:49:04.301982Z","shell.execute_reply.started":"2025-03-26T23:49:00.092373Z","shell.execute_reply":"2025-03-26T23:49:04.301135Z"}},"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-03-26T23:49:04.302755Z","iopub.execute_input":"2025-03-26T23:49:04.302981Z","iopub.status.idle":"2025-03-26T23:49:16.74702Z","shell.execute_reply.started":"2025-03-26T23:49:04.302963Z","shell.execute_reply":"2025-03-26T23:49:16.745707Z"}},"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-03-26T23:49:16.748265Z","iopub.execute_input":"2025-03-26T23:49:16.748507Z","iopub.status.idle":"2025-03-26T23:49:16.861885Z","shell.execute_reply.started":"2025-03-26T23:49:16.748485Z","shell.execute_reply":"2025-03-26T23:49:16.860757Z"}},"outputs":[],"execution_count":null},{"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-03-26T23:49:16.862862Z","iopub.execute_input":"2025-03-26T23:49:16.86314Z","iopub.status.idle":"2025-03-26T23:49:17.0468Z","shell.execute_reply.started":"2025-03-26T23:49:16.86312Z","shell.execute_reply":"2025-03-26T23:49:17.046043Z"}},"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-03-26T23:49:17.047677Z","iopub.execute_input":"2025-03-26T23:49:17.047906Z","iopub.status.idle":"2025-03-26T23:49:29.809278Z","shell.execute_reply.started":"2025-03-26T23:49:17.047888Z","shell.execute_reply":"2025-03-26T23:49:29.808511Z"}},"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-03-26T23:49:29.809982Z","iopub.execute_input":"2025-03-26T23:49:29.810207Z","iopub.status.idle":"2025-03-26T23:49:33.255497Z","shell.execute_reply.started":"2025-03-26T23:49:29.810184Z","shell.execute_reply":"2025-03-26T23:49:33.254227Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"<div style=\"padding:10px;\n            color:#E55604;\n            margin:10px;\n            font-size:130%;\n            display:fill;\n            border-radius:5px;\n            border-style: solid;\n            border-color: #FF9B50;\n            background-color:#000000;\n            overflow:hidden;\n            font-weight:400\"><b>Handling Data Formatting</b>","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-03-26T23:49:33.256517Z","iopub.execute_input":"2025-03-26T23:49:33.256767Z","iopub.status.idle":"2025-03-26T23:49:38.542864Z","shell.execute_reply.started":"2025-03-26T23:49:33.256746Z","shell.execute_reply":"2025-03-26T23:49:38.542143Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"<div style=\"padding:10px;\n            color:#E55604;\n            margin:10px;\n            font-size:160%;\n            display:fill;\n            border-radius:5px;\n            border-style: solid;\n            border-color: #FF9B50;\n            background-color:#000000;\n            overflow:hidden;\n            font-weight:400;\n            text-align:\n            center;\"><b>Visualization</b>","metadata":{}},{"cell_type":"markdown","source":"<div style=\"padding:10px;\n            color:#E55604;\n            margin:10px;\n            font-size:130%;\n            display:fill;\n            border-radius:5px;\n            border-style: solid;\n            border-color: #FF9B50;\n            background-color:#000000;\n            overflow:hidden;\n            font-weight:400\"><b>Create function for categorical features</b>","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-03-26T23:49:38.543655Z","iopub.execute_input":"2025-03-26T23:49:38.543872Z","iopub.status.idle":"2025-03-26T23:49:38.556857Z","shell.execute_reply.started":"2025-03-26T23:49:38.543853Z","shell.execute_reply":"2025-03-26T23:49:38.555916Z"}},"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-03-26T23:49:38.557678Z","iopub.execute_input":"2025-03-26T23:49:38.557972Z","iopub.status.idle":"2025-03-26T23:49:38.573919Z","shell.execute_reply.started":"2025-03-26T23:49:38.557952Z","shell.execute_reply":"2025-03-26T23:49:38.573129Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"<div style=\"padding:10px;\n            color:#E55604;\n            margin:10px;\n            font-size:130%;\n            display:fill;\n            border-radius:5px;\n            border-style: solid;\n            border-color: #FF9B50;\n            background-color:#000000;\n            overflow:hidden;\n            font-weight:400\"><b>Distribution of Authorized Flag</b>","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-03-26T23:49:38.574701Z","iopub.execute_input":"2025-03-26T23:49:38.574985Z","iopub.status.idle":"2025-03-26T23:49:39.68557Z","shell.execute_reply.started":"2025-03-26T23:49:38.574961Z","shell.execute_reply":"2025-03-26T23:49:39.684648Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"plot_categorical_feature(authorized_flag_counts)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-03-26T23:49:39.68648Z","iopub.execute_input":"2025-03-26T23:49:39.68674Z","iopub.status.idle":"2025-03-26T23:49:40.005169Z","shell.execute_reply.started":"2025-03-26T23:49:39.686713Z","shell.execute_reply":"2025-03-26T23:49:40.004374Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"<div style=\"padding:10px;\n            color:#E55604;\n            margin:10px;\n            font-size:130%;\n            display:fill;\n            border-radius:5px;\n            border-style: solid;\n            border-color: #FF9B50;\n            background-color:#000000;\n            overflow:hidden;\n            font-weight:400\"><b>Top 25 Card ID</b>","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-03-26T23:49:40.005969Z","iopub.execute_input":"2025-03-26T23:49:40.006191Z","iopub.status.idle":"2025-03-26T23:49:41.386593Z","shell.execute_reply.started":"2025-03-26T23:49:40.006172Z","shell.execute_reply":"2025-03-26T23:49:41.385868Z"}},"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-03-26T23:49:41.387253Z","iopub.execute_input":"2025-03-26T23:49:41.387438Z","iopub.status.idle":"2025-03-26T23:49:41.739277Z","shell.execute_reply.started":"2025-03-26T23:49:41.387422Z","shell.execute_reply":"2025-03-26T23:49:41.738161Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"<div style=\"padding:10px;\n            color:#E55604;\n            margin:10px;\n            font-size:130%;\n            display:fill;\n            border-radius:5px;\n            border-style: solid;\n            border-color: #FF9B50;\n            background-color:#000000;\n            overflow:hidden;\n            font-weight:400\"><b>Top 25 Merchant ID</b>","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-03-26T23:49:41.74031Z","iopub.execute_input":"2025-03-26T23:49:41.740603Z","iopub.status.idle":"2025-03-26T23:49:46.073424Z","shell.execute_reply.started":"2025-03-26T23:49:41.740577Z","shell.execute_reply":"2025-03-26T23:49:46.072655Z"}},"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-03-26T23:49:46.077975Z","iopub.execute_input":"2025-03-26T23:49:46.078201Z","iopub.status.idle":"2025-03-26T23:49:46.418972Z","shell.execute_reply.started":"2025-03-26T23:49:46.078182Z","shell.execute_reply":"2025-03-26T23:49:46.41794Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"<div style=\"padding:10px;\n            color:#E55604;\n            margin:10px;\n            font-size:130%;\n            display:fill;\n            border-radius:5px;\n            border-style: solid;\n            border-color: #FF9B50;\n            background-color:#000000;\n            overflow:hidden;\n            font-weight:400\"><b>Distribution of Category 1</b>","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-03-26T23:49:46.420956Z","iopub.execute_input":"2025-03-26T23:49:46.421185Z","iopub.status.idle":"2025-03-26T23:49:46.514923Z","shell.execute_reply.started":"2025-03-26T23:49:46.421165Z","shell.execute_reply":"2025-03-26T23:49:46.514133Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"plot_categorical_feature(Category_1_Counts)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-03-26T23:49:46.51568Z","iopub.execute_input":"2025-03-26T23:49:46.515876Z","iopub.status.idle":"2025-03-26T23:49:46.751652Z","shell.execute_reply.started":"2025-03-26T23:49:46.515859Z","shell.execute_reply":"2025-03-26T23:49:46.750878Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"<div style=\"padding:10px;\n            color:#E55604;\n            margin:10px;\n            font-size:130%;\n            display:fill;\n            border-radius:5px;\n            border-style: solid;\n            border-color: #FF9B50;\n            background-color:#000000;\n            overflow:hidden;\n            font-weight:400\"><b>Distribution of Installments</b>","metadata":{}},{"cell_type":"code","source":"Installments_Counts = combined_transactions['installments'].value_counts().reset_index()\nInstallments_Counts","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-03-26T23:49:46.752617Z","iopub.execute_input":"2025-03-26T23:49:46.752918Z","iopub.status.idle":"2025-03-26T23:49:46.863758Z","shell.execute_reply.started":"2025-03-26T23:49:46.752892Z","shell.execute_reply":"2025-03-26T23:49:46.863026Z"}},"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-03-26T23:49:46.864681Z","iopub.execute_input":"2025-03-26T23:49:46.864936Z","iopub.status.idle":"2025-03-26T23:49:47.121816Z","shell.execute_reply.started":"2025-03-26T23:49:46.864918Z","shell.execute_reply":"2025-03-26T23:49:47.120952Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"<div style=\"padding:10px;\n            color:#E55604;\n            margin:10px;\n            font-size:130%;\n            display:fill;\n            border-radius:5px;\n            border-style: solid;\n            border-color: #FF9B50;\n            background-color:#000000;\n            overflow:hidden;\n            font-weight:400\"><b>Distribution of Category 3</b>","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-03-26T23:49:47.122704Z","iopub.execute_input":"2025-03-26T23:49:47.123025Z","iopub.status.idle":"2025-03-26T23:49:47.223892Z","shell.execute_reply.started":"2025-03-26T23:49:47.122997Z","shell.execute_reply":"2025-03-26T23:49:47.222993Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"plot_categorical_feature(Category_3_Counts)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-03-26T23:49:47.224669Z","iopub.execute_input":"2025-03-26T23:49:47.224933Z","iopub.status.idle":"2025-03-26T23:49:47.484407Z","shell.execute_reply.started":"2025-03-26T23:49:47.224913Z","shell.execute_reply":"2025-03-26T23:49:47.483297Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"<div style=\"padding:10px;\n            color:#E55604;\n            margin:10px;\n            font-size:130%;\n            display:fill;\n            border-radius:5px;\n            border-style: solid;\n            border-color: #FF9B50;\n            background-color:#000000;\n            overflow:hidden;\n            font-weight:400\"><b>Distribution of Month Lag</b>","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-03-26T23:49:47.485197Z","iopub.execute_input":"2025-03-26T23:49:47.485425Z","iopub.status.idle":"2025-03-26T23:49:47.598176Z","shell.execute_reply.started":"2025-03-26T23:49:47.485407Z","shell.execute_reply":"2025-03-26T23:49:47.59712Z"}},"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-03-26T23:49:47.598975Z","iopub.execute_input":"2025-03-26T23:49:47.599222Z","iopub.status.idle":"2025-03-26T23:49:47.867234Z","shell.execute_reply.started":"2025-03-26T23:49:47.599204Z","shell.execute_reply":"2025-03-26T23:49:47.866333Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"<div style=\"padding:10px;\n            color:#E55604;\n            margin:10px;\n            font-size:130%;\n            display:fill;\n            border-radius:5px;\n            border-style: solid;\n            border-color: #FF9B50;\n            background-color:#000000;\n            overflow:hidden;\n            font-weight:400\"><b>Distribution of Category 2</b>","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-03-26T23:49:47.867921Z","iopub.execute_input":"2025-03-26T23:49:47.868152Z","iopub.status.idle":"2025-03-26T23:49:48.069405Z","shell.execute_reply.started":"2025-03-26T23:49:47.868134Z","shell.execute_reply":"2025-03-26T23:49:48.068099Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"plot_categorical_feature(Category_2_Counts)","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-03-26T23:49:48.070167Z","iopub.execute_input":"2025-03-26T23:49:48.070396Z","iopub.status.idle":"2025-03-26T23:49:48.37041Z","shell.execute_reply.started":"2025-03-26T23:49:48.070377Z","shell.execute_reply":"2025-03-26T23:49:48.369661Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"<div style=\"padding:10px;\n            color:#E55604;\n            margin:10px;\n            font-size:130%;\n            display:fill;\n            border-radius:5px;\n            border-style: solid;\n            border-color: #FF9B50;\n            background-color:#000000;\n            overflow:hidden;\n            font-weight:400\"><b>Distribution of Top 25 Card Purchased</b>","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-03-26T23:49:48.371098Z","iopub.execute_input":"2025-03-26T23:49:48.371306Z","iopub.status.idle":"2025-03-26T23:49:51.179851Z","shell.execute_reply.started":"2025-03-26T23:49:48.371286Z","shell.execute_reply":"2025-03-26T23:49:51.179001Z"}},"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-03-26T23:49:51.180633Z","iopub.execute_input":"2025-03-26T23:49:51.180902Z","iopub.status.idle":"2025-03-26T23:49:52.817949Z","shell.execute_reply.started":"2025-03-26T23:49:51.180881Z","shell.execute_reply":"2025-03-26T23:49:52.817212Z"}},"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-03-26T23:49:52.818667Z","iopub.execute_input":"2025-03-26T23:49:52.818945Z","iopub.status.idle":"2025-03-26T23:49:54.230958Z","shell.execute_reply.started":"2025-03-26T23:49:52.818924Z","shell.execute_reply":"2025-03-26T23:49:54.22967Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"<div style=\"padding:10px;\n            color:#E55604;\n            margin:10px;\n            font-size:130%;\n            display:fill;\n            border-radius:5px;\n            border-style: solid;\n            border-color: #FF9B50;\n            background-color:#000000;\n            overflow:hidden;\n            font-weight:400\"><b>Most Purchased Month</b>","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-03-26T23:49:54.231922Z","iopub.execute_input":"2025-03-26T23:49:54.232191Z","iopub.status.idle":"2025-03-26T23:49:56.464522Z","shell.execute_reply.started":"2025-03-26T23:49:54.232166Z","shell.execute_reply":"2025-03-26T23:49:56.463735Z"}},"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-03-26T23:49:56.465445Z","iopub.execute_input":"2025-03-26T23:49:56.465627Z","iopub.status.idle":"2025-03-26T23:49:56.822808Z","shell.execute_reply.started":"2025-03-26T23:49:56.465611Z","shell.execute_reply":"2025-03-26T23:49:56.821845Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"<div style=\"padding:10px;\n            color:#E55604;\n            margin:10px;\n            font-size:130%;\n            display:fill;\n            border-radius:5px;\n            border-style: solid;\n            border-color: #FF9B50;\n            background-color:#000000;\n            overflow:hidden;\n            font-weight:400\"><b>Distribution of Purchased Amount</b>","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-03-26T23:49:56.823607Z","iopub.execute_input":"2025-03-26T23:49:56.823808Z","iopub.status.idle":"2025-03-26T23:51:54.729616Z","shell.execute_reply.started":"2025-03-26T23:49:56.823789Z","shell.execute_reply":"2025-03-26T23:51:54.728795Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"<div style=\"padding:10px;\n            color:#E55604;\n            margin:10px;\n            font-size:130%;\n            display:fill;\n            border-radius:5px;\n            border-style: solid;\n            border-color: #FF9B50;\n            background-color:#000000;\n            overflow:hidden;\n            font-weight:400\"><b>Feature Encoding</b>","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-03-26T23:51:54.730452Z","iopub.execute_input":"2025-03-26T23:51:54.730624Z","iopub.status.idle":"2025-03-26T23:51:55.542421Z","shell.execute_reply.started":"2025-03-26T23:51:54.730607Z","shell.execute_reply":"2025-03-26T23:51:55.541658Z"}},"outputs":[],"execution_count":null},{"cell_type":"markdown","source":"<div style=\"padding:10px;\n            color:#E55604;\n            margin:10px;\n            font-size:130%;\n            display:fill;\n            border-radius:5px;\n            border-style: solid;\n            border-color: #FF9B50;\n            background-color:#000000;\n            overflow:hidden;\n            font-weight:400\"><b>Feature Engineering</b>","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-03-26T23:51:55.543394Z","iopub.execute_input":"2025-03-26T23:51:55.543668Z","iopub.status.idle":"2025-03-26T23:51:56.468539Z","shell.execute_reply.started":"2025-03-26T23:51:55.543643Z","shell.execute_reply":"2025-03-26T23:51:56.467774Z"}},"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-03-26T23:51:56.469251Z","iopub.execute_input":"2025-03-26T23:51:56.469414Z","iopub.status.idle":"2025-03-26T23:51:57.386998Z","shell.execute_reply.started":"2025-03-26T23:51:56.469398Z","shell.execute_reply":"2025-03-26T23:51:57.386274Z"}},"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-03-26T23:51:57.387706Z","iopub.execute_input":"2025-03-26T23:51:57.387968Z","iopub.status.idle":"2025-03-26T23:51:58.310649Z","shell.execute_reply.started":"2025-03-26T23:51:57.387925Z","shell.execute_reply":"2025-03-26T23:51:58.309878Z"}},"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-03-26T23:51:58.311545Z","iopub.execute_input":"2025-03-26T23:51:58.311762Z","iopub.status.idle":"2025-03-26T23:51:59.452747Z","shell.execute_reply.started":"2025-03-26T23:51:58.31174Z","shell.execute_reply":"2025-03-26T23:51:59.452068Z"}},"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-03-26T23:51:59.45348Z","iopub.execute_input":"2025-03-26T23:51:59.453639Z","iopub.status.idle":"2025-03-26T23:52:00.393222Z","shell.execute_reply.started":"2025-03-26T23:51:59.453623Z","shell.execute_reply":"2025-03-26T23:52:00.392405Z"}},"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-03-26T23:52:00.394004Z","iopub.execute_input":"2025-03-26T23:52:00.394171Z","iopub.status.idle":"2025-03-26T23:52:00.830697Z","shell.execute_reply.started":"2025-03-26T23:52:00.394156Z","shell.execute_reply":"2025-03-26T23:52:00.830085Z"}},"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-03-26T23:52:00.831377Z","iopub.execute_input":"2025-03-26T23:52:00.831534Z","iopub.status.idle":"2025-03-26T23:52:01.001029Z","shell.execute_reply.started":"2025-03-26T23:52:00.831519Z","shell.execute_reply":"2025-03-26T23:52:01.000192Z"}},"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-03-26T23:52:01.001816Z","iopub.execute_input":"2025-03-26T23:52:01.002051Z","iopub.status.idle":"2025-03-26T23:52:05.848446Z","shell.execute_reply.started":"2025-03-26T23:52:01.002033Z","shell.execute_reply":"2025-03-26T23:52:05.847559Z"}},"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-03-26T23:52:05.849219Z","iopub.execute_input":"2025-03-26T23:52:05.849436Z","iopub.status.idle":"2025-03-26T23:52:05.852731Z","shell.execute_reply.started":"2025-03-26T23:52:05.849416Z","shell.execute_reply":"2025-03-26T23:52:05.851948Z"}},"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-03-26T23:52:05.853771Z","iopub.execute_input":"2025-03-26T23:52:05.854165Z","iopub.status.idle":"2025-03-26T23:52:06.030388Z","shell.execute_reply.started":"2025-03-26T23:52:05.854132Z","shell.execute_reply":"2025-03-26T23:52:06.029285Z"}},"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-03-26T23:52:06.031217Z","iopub.execute_input":"2025-03-26T23:52:06.031422Z","iopub.status.idle":"2025-03-26T23:52:06.104846Z","shell.execute_reply.started":"2025-03-26T23:52:06.031405Z","shell.execute_reply":"2025-03-26T23:52:06.104123Z"}},"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-03-26T23:52:06.105601Z","iopub.execute_input":"2025-03-26T23:52:06.10581Z","iopub.status.idle":"2025-03-26T23:52:06.127766Z","shell.execute_reply.started":"2025-03-26T23:52:06.10579Z","shell.execute_reply":"2025-03-26T23:52:06.127049Z"}},"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-03-26T23:52:06.128582Z","iopub.execute_input":"2025-03-26T23:52:06.128746Z","iopub.status.idle":"2025-03-26T23:52:06.172112Z","shell.execute_reply.started":"2025-03-26T23:52:06.128731Z","shell.execute_reply":"2025-03-26T23:52:06.171363Z"}},"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-03-26T23:52:06.172912Z","iopub.execute_input":"2025-03-26T23:52:06.173077Z","iopub.status.idle":"2025-03-26T23:52:06.185625Z","shell.execute_reply.started":"2025-03-26T23:52:06.173061Z","shell.execute_reply":"2025-03-26T23:52:06.184886Z"}},"outputs":[],"execution_count":null},{"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-03-26T23:52:06.186343Z","iopub.execute_input":"2025-03-26T23:52:06.18653Z","iopub.status.idle":"2025-03-26T23:52:06.199585Z","shell.execute_reply.started":"2025-03-26T23:52:06.186511Z","shell.execute_reply":"2025-03-26T23:52:06.198891Z"}},"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-03-26T23:52:06.20018Z","iopub.execute_input":"2025-03-26T23:52:06.200351Z","iopub.status.idle":"2025-03-26T23:52:55.296853Z","shell.execute_reply.started":"2025-03-26T23:52:06.200331Z","shell.execute_reply":"2025-03-26T23:52:55.296085Z"}},"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.head()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-03-26T23:52:55.297505Z","iopub.execute_input":"2025-03-26T23:52:55.297706Z","iopub.status.idle":"2025-03-26T23:52:55.309232Z","shell.execute_reply.started":"2025-03-26T23:52:55.297686Z","shell.execute_reply":"2025-03-26T23:52:55.308434Z"}},"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-03-26T23:52:55.309996Z","iopub.execute_input":"2025-03-26T23:52:55.310259Z","iopub.status.idle":"2025-03-26T23:52:55.341765Z","shell.execute_reply.started":"2025-03-26T23:52:55.310234Z","shell.execute_reply":"2025-03-26T23:52:55.341118Z"}},"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-03-26T23:52:55.342513Z","iopub.execute_input":"2025-03-26T23:52:55.34267Z","iopub.status.idle":"2025-03-26T23:53:01.004581Z","shell.execute_reply.started":"2025-03-26T23:52:55.342654Z","shell.execute_reply":"2025-03-26T23:53:01.003531Z"}},"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-03-26T23:53:01.005524Z","iopub.execute_input":"2025-03-26T23:53:01.005877Z","iopub.status.idle":"2025-03-26T23:53:02.074286Z","shell.execute_reply.started":"2025-03-26T23:53:01.005843Z","shell.execute_reply":"2025-03-26T23:53:02.073185Z"}},"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-03-26T23:54:59.987849Z","iopub.execute_input":"2025-03-26T23:54:59.988266Z","iopub.status.idle":"2025-03-26T23:55:06.369624Z","shell.execute_reply.started":"2025-03-26T23:54:59.988237Z","shell.execute_reply":"2025-03-26T23:55:06.36865Z"}},"outputs":[],"execution_count":null},{"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-03-26T23:55:10.918755Z","iopub.execute_input":"2025-03-26T23:55:10.91908Z","iopub.status.idle":"2025-03-26T23:55:15.142508Z","shell.execute_reply.started":"2025-03-26T23:55:10.919058Z","shell.execute_reply":"2025-03-26T23:55:15.141679Z"}},"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-03-26T23:55:15.143514Z","iopub.execute_input":"2025-03-26T23:55:15.143788Z","iopub.status.idle":"2025-03-26T23:55:15.158445Z","shell.execute_reply.started":"2025-03-26T23:55:15.143761Z","shell.execute_reply":"2025-03-26T23:55:15.157539Z"}},"outputs":[],"execution_count":null},{"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-03-26T23:55:19.511655Z","iopub.execute_input":"2025-03-26T23:55:19.511994Z","iopub.status.idle":"2025-03-26T23:55:19.517368Z","shell.execute_reply.started":"2025-03-26T23:55:19.511968Z","shell.execute_reply":"2025-03-26T23:55:19.516433Z"}},"outputs":[],"execution_count":null},{"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\n\n\n# Convert category columns to integer before aggregation\ncombined_transactions['category_1'] = combined_transactions['category_1'].astype('int8')\ncombined_transactions['authorized_flag'] = combined_transactions['authorized_flag'].astype('int8')\n\n# Verify data types\nprint(\"Data types after conversion:\")\nprint(combined_transactions[['category_1', 'authorized_flag']].dtypes)\n\n# Check for NaN values\nprint(\"\\nNaN values:\")\nprint(combined_transactions[['category_1', 'authorized_flag']].isnull().sum())\n\n# Group 'combined_transactions' by 'card_id' and apply aggregations defined with numeric_only=True\naggregate_features = combined_transactions.groupby('card_id').agg(aggregate_tbl2, numeric_only=True).reset_index()\naggregate_features.head()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-03-26T23:58:25.918812Z","iopub.execute_input":"2025-03-26T23:58:25.91922Z","iopub.status.idle":"2025-03-26T23:58:53.667034Z","shell.execute_reply.started":"2025-03-26T23:58:25.919188Z","shell.execute_reply":"2025-03-26T23:58:53.665709Z"}},"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-03-26T23:58:53.667523Z","iopub.status.idle":"2025-03-26T23:58:53.667777Z","shell.execute_reply":"2025-03-26T23:58:53.667668Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"aggregate_features = combined_transactions.groupby('card_id').agg(aggregate_tbl2, numeric_only=True).reset_index()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-03-27T00:00:07.107526Z","iopub.execute_input":"2025-03-27T00:00:07.107901Z","iopub.status.idle":"2025-03-27T00:00:35.475986Z","shell.execute_reply.started":"2025-03-27T00:00:07.107874Z","shell.execute_reply":"2025-03-27T00:00:35.474518Z"}},"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-03-26T23:59:13.307401Z","iopub.execute_input":"2025-03-26T23:59:13.307712Z","iopub.status.idle":"2025-03-26T23:59:13.320009Z","shell.execute_reply.started":"2025-03-26T23:59:13.307689Z","shell.execute_reply":"2025-03-26T23:59:13.318699Z"}},"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-03-26T23:55:32.114986Z","iopub.status.idle":"2025-03-26T23:55:32.115304Z","shell.execute_reply":"2025-03-26T23:55:32.115158Z"}},"outputs":[],"execution_count":null},{"cell_type":"code","source":"# Perform garbage collection to free up memory \n\n#gc.collect()","metadata":{"trusted":true,"execution":{"iopub.status.busy":"2025-03-26T23:53:45.039443Z","iopub.status.idle":"2025-03-26T23:53:45.039667Z","shell.execute_reply":"2025-03-26T23:53:45.039572Z"}},"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-03-26T23:53:45.040119Z","iopub.status.idle":"2025-03-26T23:53:45.04034Z","shell.execute_reply":"2025-03-26T23:53:45.040245Z"}},"outputs":[],"execution_count":null}]}