{"cells":[{"metadata":{"_uuid":"29bdc5641b85084bf4acb3459c8730692b6d1d6c"},"cell_type":"markdown","source":"Elo Merchant Category Recommendation\n=====\n\n# Objective\n\nDevelop algorithms to identify and serve the most relevant opportunities to individuals, by uncovering signal in customer loyalty. We have to predict a loyalty score for each ```card_id``` represented in ```test.csv```.\n\n# Evaluation Metric\n\nEvaluation metric is RMSE - $\\textrm{RMSE} = \\sqrt{\\frac{1}{n} \\sum_{i=1}^{n} (y_i - \\hat{y}_i)^2}$, \nwhere $\\hat{y}$ is the predicted loyalty score for each ```card_id```, and $y$ is the actual loyalty score assigned to a ```card_id```.\n\n# Data\n\n- **train.csv**: Training set having the information of cards. It also has the loyalty score column.\n- **test.csv**: Testing set having the information on cards\n- **historical_transactions.csv**: Up to 3 months' worth of historical transactions for each card_id\n- **merchants.csv**: Additional information about all merchants / ```merchant_id```s in the dataset.\n- **new_merchant_transactions.csv**: two months' worth of data for each ```card_id``` containing ALL purchases that ```card_id``` made at ```merchant_id```s that were not visited in the historical data.\n"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"scrolled":true},"cell_type":"code","source":"import os\nimport numpy as np\nimport pandas as pd\nimport matplotlib.pyplot as plt\n\nfrom pathlib import Path\n\nprint(os.listdir(\"../input\"))\n\n%matplotlib inline\n\n# display all the outputs\nfrom IPython.core.interactiveshell import InteractiveShell\nInteractiveShell.ast_node_interactivity = \"all\"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3f2202a0f3b280451c02e9c2a305967050482d5c"},"cell_type":"code","source":"# utility functions\ndef get_col_stats(df):\n    temp_nulls = df.isnull().sum()\n    temp = pd.DataFrame({\"nulls\": temp_nulls, \"null_percent\": temp_nulls*100/df.shape[0]})\n    \n    uniqs = []\n    for col in df.columns:\n        uniqs.append(df[col].unique().shape[0])\n    temp[\"uniqs\"] = uniqs\n    return temp\n\ndef get_categorical_stats(df, col):\n    counts = df[col].value_counts(dropna=False)\n    temp = pd.DataFrame({\n        \"counts\": counts,\n        \"count_per\": counts * 100 / df.shape[0],\n    })\n    return temp\n\ndef numeric_to_cat(df, cols):\n    for col in cols:\n        df[col] = df[col].astype(\"category\")\n    return df\n","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"DATA_ROOT = Path(\"../input/\")\n\ncard_f = DATA_ROOT / \"train.csv\"\nmerchant_f = DATA_ROOT / \"merchants.csv\"\nhist_f = DATA_ROOT / \"historical_transactions.csv\"\nnew_hist_f = DATA_ROOT / \"new_merchant_transactions.csv\"\n\ntest_f = DATA_ROOT / \"test.csv\"","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5aaf993292445105d6020646fa4cd44204d0d687"},"cell_type":"markdown","source":"# Card Data\n\n\n"},{"metadata":{"trusted":true,"_uuid":"dd9db6ae59c8ad2382efcb0074468274d2150ced"},"cell_type":"code","source":"card_df = pd.read_csv(card_f)\ncard_df.shape\ncard_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e65585a49def2026e0ee4ae70dbebba8454148c6"},"cell_type":"code","source":"test_df = pd.read_csv(test_f)\ntest_df.shape\ntest_df.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c5c01f7581dafc1dd76df8e6504ff9dee11f6369"},"cell_type":"markdown","source":"## Sanity Check\n\n- Unique ```card_id```'s in training data\n- Unique ```card_id```'s in testing data\n- Overlap of cards in training and testing data\n- Nulls"},{"metadata":{"trusted":true,"_uuid":"20ef27fc5610ecdce063354c04566c9f58f7c4ae"},"cell_type":"code","source":"card_df.card_id.unique().shape\ncard_df.card_id.unique().shape[0] == card_df.shape[0]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"005d4cf91c069fd7500d5e90fadb3e43b8acf0cf"},"cell_type":"markdown","source":"There are no duplicate ```card_id```'s in the train set."},{"metadata":{"trusted":true,"_uuid":"df19437083698483bfe34a82cbe0b347e8d75098"},"cell_type":"code","source":"test_df.card_id.unique().shape\ntest_df.card_id.unique().shape[0] == test_df.shape[0]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"dfdc673a21458752bdd30f5ce98b92c2f88f2b57"},"cell_type":"markdown","source":"There are no duplicate ```card_id```'s in the test set."},{"metadata":{"trusted":true,"_uuid":"085302c0a8e347e486454ecb407b009c42e20377"},"cell_type":"code","source":"len(set(test_df.card_id).intersection(set(card_df.card_id)))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"685623e2c9b9bfb4f2d4a17cd8a8883f98bb53e2"},"cell_type":"markdown","source":"There's no overlapping cards in the training and testing datasets."},{"metadata":{"trusted":true,"_uuid":"d09d8a24c2ac7cb8883e97ddde6e2b77f8f4741a"},"cell_type":"code","source":"print(\"Training data:\")\ncard_df.isna().sum()\nprint(\"Testing data:\")\ntest_df.isna().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"032c4cfb9b0ed24447bcf7e5e1295a411794b767"},"cell_type":"code","source":"test_df[test_df.first_active_month.isnull()]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e9df6ffe6884801837ed0d15d600bf19a350da73"},"cell_type":"markdown","source":"Training dataset has no nulls but testing dataset has one row where the ```first_active_month``` column is Null. Impute testing data specifically?  \nWe are done with our sanity check now. Let's explore the table.\n\n## Exploration\n\n### 1. Target Variable\n\nTarget column is a numerical loyalty score.\n\n- Negative means the card holder is not loyal?\n- And, positive means the opposite?"},{"metadata":{"trusted":true,"_uuid":"83dc5c76c8860bd8cdac6addf180fc21f99932ec"},"cell_type":"code","source":"ax = card_df.target.plot.hist(bins=20, figsize=(10, 5))\n_ = ax.set_title(\"target histogram\")\nplt.show()\n\nfig, axs = plt.subplots(1,2, figsize=(20, 5))\n_ = card_df.target[card_df.target > 10].plot.hist(ax=axs[0])\n_ = axs[0].set_title(\"target histogram for values greater than 10\")\n_ = card_df.target[card_df.target < -10].plot.hist(ax=axs[1])\n_ = axs[1].set_title(\"target histogram for values less than -10\")\nplt.show()\n\ncard_df.target.describe()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"33388fb2971da6ef2248e81bd4c02caad6ad8038"},"cell_type":"markdown","source":"Observations:\n\n- Values range from -33.2 to 17.9\n- -33 seems like an outlier as can be seen in the 3rd plot\n- other values less than -10 also seem like outliers due to very less in number\n- All values above 10 are also looking like outliers\n"},{"metadata":{"trusted":true,"_uuid":"eacd6a6d1e484e260464df86e1ea018812a7d24f"},"cell_type":"code","source":"card_df[\"target_sign\"] = card_df.target.apply(lambda x: 0 if x <= 0 else 1)\ncard_df.target_sign.value_counts()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1bc5220dd6d90820b2f4255f7cd0545a0b9c7716"},"cell_type":"markdown","source":"Observations:\n\n- Negative and positive target values are almost in the same proportion\n\n### 2. Anonymised Features\n\nfeature_1, feature_2, feature_3"},{"metadata":{"trusted":true,"_uuid":"d00a8e3a765c739e3b969f12a612883b9b0c79e6"},"cell_type":"code","source":"print(\"feature_1\")\npd.DataFrame({\"counts\": card_df.feature_1.value_counts(), \"counts_per\": card_df.feature_1.value_counts()*100/card_df.shape[0]})\nprint(\"feature_2\")\npd.DataFrame({\"counts\": card_df.feature_2.value_counts(), \"counts_per\": card_df.feature_2.value_counts()*100/card_df.shape[0]})\nprint(\"feature_3\")\npd.DataFrame({\"counts\": card_df.feature_3.value_counts(), \"counts_per\": card_df.feature_3.value_counts()*100/card_df.shape[0]})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9fc4a6d27196c4b8db601c12030419f99f5003b4"},"cell_type":"markdown","source":"Observations:\n\n- feature_1, feature_2, feature_3, all are categorical variables\n- feature_1 has 5 unique values\n- feature_2 has 3 unique values\n- feature_3 is a binary column\n\n### 3. first_active_month"},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"1cf20da40d62313c3b3743462f604086ca9d9b66"},"cell_type":"code","source":"temp = card_df.first_active_month.value_counts().sort_index()\nax = temp.plot(figsize=(10, 5))\n_ = ax.set_xticklabels(range(2010, 2019))\n_ = ax.set_title(\"Distribution across years\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8bd51c6d66ea6038d43b5711250f13e3a9ffa4b8"},"cell_type":"markdown","source":"Observations:\n\n- Most of the data lies in the years ranging from 2016 to 2018\n"},{"metadata":{"trusted":true,"_uuid":"d5fb6c334ca3ff7ec145c922dcd277f01f5ea4aa"},"cell_type":"code","source":"card_df[\"yr\"] = card_df.first_active_month.str.split(\"-\").str[0]\ncard_df[\"month\"] = card_df.first_active_month.str.split(\"-\").str[1]\ncard_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2968ae70d79f903bef4f6d1e46977de44af5ab23"},"cell_type":"code","source":"temp = get_categorical_stats(card_df, \"yr\")\ntemp\n\nax = temp.counts.sort_index().plot()\n_ = ax.set_xticklabels(range(2010, 2019))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"42cb967a9f1208f3e069f17ddf4e2185cf895564"},"cell_type":"markdown","source":"Observations:\n\n- Years range from 2011 to 2018\n- 64% data is from 2017, followed by 2016 (25%) and 2015 (7%)\n- Very less data from 2017 (may be, test data will have data from 2018?)"},{"metadata":{"trusted":true,"_uuid":"351547595816b460d5c5fc1e8f28bc45e7a52ee6"},"cell_type":"code","source":"temp = get_categorical_stats(card_df, \"month\")\ntemp\n\nax = temp.counts.sort_index().plot()\n_ = ax.set_xticklabels(range(-1, 13, 2))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"356002b4f0c1c8cfcd31241cb9ca3812409cc8cf"},"cell_type":"markdown","source":"Observations:\n\n- last 6 months (July to December) has relatively more data than first 6 months (January to June)\n\n### 4. card_id"},{"metadata":{"trusted":true,"_uuid":"7f50668cd5528ccf5058b9568e4b1b6b1dfd8951"},"cell_type":"code","source":"card_df[\"card_id_dec\"] = card_df.card_id.str.split(\"_\").str[2].apply(lambda x: int(x, 16))\n\ncard_df.card_id.str.split(\"_\").str[0].unique()\ncard_df.card_id.str.split(\"_\").str[1].unique()\ncard_df.card_id_dec.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7fe8f6416f191c6fe9c3c55ca97d8b3a622b5200","scrolled":true},"cell_type":"code","source":"card_df[[\"card_id_dec\", \"first_active_month\"]].sort_values(\"card_id_dec\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"68210fa6e0ced003c66b9fec11cc5d643ed6740a"},"cell_type":"markdown","source":"Observations:\n\n- tried to see if there was any pattern in the ids.\n- converted hex values to decimal, but when we see it according to the ```first_active_month``` then there is no apparent order in the ids\n\n\n### 5. Anonimised features vs target"},{"metadata":{"trusted":true,"_uuid":"43e03f8ebf3848f6609b6d9799f722cc1d67c4cd"},"cell_type":"code","source":"_ = card_df[[\"feature_1\", \"target\"]].plot.scatter(x=\"feature_1\", y=\"target\")\n_ = card_df[[\"feature_2\", \"target\"]].plot.scatter(x=\"feature_2\", y=\"target\")\n_ = card_df[[\"feature_3\", \"target\"]].plot.scatter(x=\"feature_3\", y=\"target\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"539fced7a836ca110473e2f062386e0fd90fb10b"},"cell_type":"markdown","source":"Observations:\n\n- all anonymised features are similar in value ranges for the target column\n- -33 target value is quite distinct across all the variables\n- Maybe, -33 is a default value of loyalty score.\n\n\n### 6. Year vs feature_1\n"},{"metadata":{"trusted":true,"_uuid":"4dec87bf3c0fc5294b7dbe095b05a99d9d152df7"},"cell_type":"code","source":"card_df.groupby([\"yr\", \"feature_1\"])[\"month\"].count()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"31546180ae38f9c6c065db8809927b353bee8fe7"},"cell_type":"markdown","source":"Observations:\n\n- Value 3 (most frequent category in the whole feature_1 column) is at all time high across all the years except 2018\n- Value 2 is another value which is 2nd highest after 2015\n\n### 7. Year vs feature_2"},{"metadata":{"trusted":true,"_uuid":"76a1af7245d4c94cf77fb5fece577d21c2d74ef2"},"cell_type":"code","source":"card_df.groupby([\"yr\", \"feature_2\"])[\"month\"].count()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0842891018280209772241e997b511c1803a6516"},"cell_type":"markdown","source":"Observations:\n\n- 1 and 3 are present for all the years\n- 2 started showing up with good numbers from the year 2015"},{"metadata":{"_uuid":"0dec234c965b3d2665c0bf374263babd99a13ce8"},"cell_type":"markdown","source":"# Merchant Data"},{"metadata":{"trusted":true,"_uuid":"3733cddf04e5e0f99820012dc2f4bd0b8833a5c5"},"cell_type":"code","source":"merc_df = pd.read_csv(merchant_f)\n\nminus_1_to_nan_cols = [\"city_id\", \"state_id\", \"merchant_group_id\",\n                      \"merchant_category_id\", \"subsector_id\"]\nfor col in minus_1_to_nan_cols:\n    merc_df[col] = merc_df[col].replace(-1, pd.np.nan)\n\nnum_to_cat_cols = [\"category_2\", \"city_id\", \"state_id\",\n                  \"merchant_group_id\", \"merchant_category_id\",\n                   \"subsector_id\"]\nmerc_df = numeric_to_cat(merc_df, num_to_cat_cols)\n\nmerc_df.shape\nmerc_df.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"66b7d27f393ca8d3a0a14fb6f132304769d0c700"},"cell_type":"markdown","source":"## Sanity Check\n\n- Unique ```merchant_id```s\n- Nulls"},{"metadata":{"trusted":true,"_uuid":"fdda58c13cddfa9bf1bdd14a8acb148fe4b8a8ce"},"cell_type":"code","source":"merc_df.merchant_id.unique().shape\nmerc_df.merchant_id.unique().shape[0] == merc_df.shape[0]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8cc9a29f4896d62b1af8e7a375e82110221e4059"},"cell_type":"markdown","source":"Few ```merchant_id```s have more than one rows. Lets investigate some of them."},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"4bf17508f6c4b32c7bcb807370d6c578991d4e79"},"cell_type":"code","source":"temp = merc_df.merchant_id.value_counts()\ntemp[temp > 1]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3a0a19e1e86d552a7a562e9d967827251e4f728f"},"cell_type":"code","source":"merc_df[merc_df.merchant_id == \"M_ID_d123532c72\"]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8667cc9752860d5f8647178e5c177a94f036a66e"},"cell_type":"markdown","source":"Most of the columns have same values, the ```_lag``` columns are different. We can try deduping the rows by taking an average of those values."},{"metadata":{"trusted":true,"_uuid":"40c9bb780c6fdfc8c96c74297d5ce80406ba2ac6"},"cell_type":"code","source":"temp_nulls = merc_df.isnull().sum()\ntemp = pd.DataFrame({\"nulls\": temp_nulls, \"null_percent\": temp_nulls*100/merc_df.shape[0]})\ntemp","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f32b6d02e57dd17161ff029359f5677f90098947"},"cell_type":"markdown","source":"Great!! No nulls in the id columns.\n\n## Exploration\n\n### 1. Anonymised measure\nnumerical_1, numerical_1"},{"metadata":{"trusted":true,"_uuid":"865b0243bf342a500c2d80f541b6eb53869b9f34"},"cell_type":"code","source":"merc_df[[\"numerical_1\", \"numerical_2\"]].describe()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6987fbf56aa854c387dc155dc1ad889c278842a6"},"cell_type":"markdown","source":"Observations:\n- No nulls in both the columns\n- ```numerical_1``` values ranges from -0.057 to 183.735\n- ```numerical_2``` values ranges from -0.008 to 182.097\n- 75th quantile is at -0.047 for both ```numerical_1``` and ```numerical_2```\n- max value for both is very close"},{"metadata":{"trusted":true,"_uuid":"0440000c3c6088bac094a7283c1cb94eaf3f5572"},"cell_type":"code","source":"fig, ax = plt.subplots(1, 2)\n_ = merc_df[[\"numerical_1\"]].boxplot(ax=ax[0])\n_ = merc_df[[\"numerical_2\"]].boxplot(ax=ax[1])\n\nfig, ax = plt.subplots(1, 2)\n_ = merc_df.loc[merc_df.numerical_1 < 0.1, [\"numerical_1\"]].boxplot(ax=ax[0])\n_ = merc_df.loc[merc_df.numerical_2 < 0.01, [\"numerical_2\"]].boxplot(ax=ax[1])\nplt.tight_layout()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1e1cf0830e33c4bd9132e8cec17ef1d822e6f3b7"},"cell_type":"markdown","source":"Observations:\n- Big tail after IQR (many outliers) for both the columns"},{"metadata":{"trusted":true,"_uuid":"9befe8cdf9b26a2a7952d4338ddfaaac4442a445"},"cell_type":"code","source":"_ = merc_df[[\"numerical_1\", \"numerical_2\"]].plot.scatter(x=\"numerical_1\", y=\"numerical_2\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"be0bd3bb3dd614cfc1fc5005517fdd24ca9e9ef3"},"cell_type":"markdown","source":"Observations:\n- A clean linear relationship b/w numerical_1 and numerical_2\n- will remove one column when being used in the model\n- also handle the outliers (what will be the threshold)"},{"metadata":{"trusted":true,"_uuid":"599837ac5b0cb800ead6d7478db82d4fc4ec5d9f"},"cell_type":"markdown","source":"### 2. Anon Categories\ncategory_1, category_2, category_4"},{"metadata":{"trusted":true,"_uuid":"f1309f88204118b976a6c27f7e22f7e572ff9ef7"},"cell_type":"code","source":"print(\"category_1\")\nget_categorical_stats(merc_df, \"category_1\")\nprint(\"category_2\")\nget_categorical_stats(merc_df, \"category_2\")\nprint(\"category_4\")\nget_categorical_stats(merc_df, \"category_4\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f56e4e5ecb231176d360807ba7db9595b0166979"},"cell_type":"markdown","source":"Observations:\n- category_1 is a binary column (N/Y)\n- category_4 is a binary column (N/Y)\n- category_1 and category_4 have very high number of N\n- category_2 has 5 categories and also nulls (11,887)\n- category_2 seems like a ratings column with 1 being the most frequent rating followed by 5 and 3."},{"metadata":{"trusted":true,"_uuid":"23e425c0dce2937ffc639019183ac5a307ddbb28"},"cell_type":"code","source":"merc_df.groupby([\"category_4\", \"category_2\"])[\"category_1\"].count()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b1746dc1d242569b54f9493983595e2d81f718c1"},"cell_type":"code","source":"print(\"Ratio of category_4 with value N to value Y based on category_2 freqeuncy counts\")\ntemp1 = merc_df.loc[merc_df.category_4 == \"N\", \"category_2\"].value_counts(dropna=False)\ntemp2 = merc_df.loc[merc_df.category_4 == \"Y\", \"category_2\"].value_counts(dropna=False)\ntemp1/temp2","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6c5e55dedbe3175dd06b5cfcb98fd46b0c7d0b5c"},"cell_type":"markdown","source":"Observations:\n- Ratio of merchants with N to Y is 3.4 for category_2 as 1\n- Ratio of merchants with N to Y is 4.2 for category_2 as 5\n- All others revolve around 1.5"},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"c5f8e8a4dac01c661d3070f1c87a0bba2f2f02fb"},"cell_type":"code","source":"merc_df.groupby([\"category_1\", \"category_2\"])[\"category_4\"].count()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0a6812018f96b507856966043621a57b4899c5ac"},"cell_type":"code","source":"merc_df.loc[merc_df.category_1 == \"Y\", \"category_2\"].value_counts(dropna=False)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"5cfbfd52e53b7543a94c62f951bb25e9a0ae5c43"},"cell_type":"markdown","source":"Observations:\n- All the merchants under category_1 as Y have all nulls in category_2\n- Remove category_1 column when using in training as it's not adding much information\n"},{"metadata":{"trusted":true,"_uuid":"63127b20b5f9c66a60adc0a4af74b1ae223616ed"},"cell_type":"code","source":"merc_df.groupby([\"category_1\", \"category_4\"])[\"category_2\"].count()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8f624757aad6d1cbd8fee0685f55ceba8fa97c82"},"cell_type":"markdown","source":"### 3. Location ID cols\n\ncity_id, state_id"},{"metadata":{"trusted":true,"_uuid":"17c8a283809b60d8d41978d7855fae77b62a95e8","scrolled":true},"cell_type":"code","source":"print(\"city_id\")\nget_categorical_stats(merc_df, \"city_id\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"92f1c87f298387b35efcfe83b5991cdc8789850c"},"cell_type":"markdown","source":"Observations:\n- 31.4% data in city_id is null\n- There are 216 distinct cities merchants belong to"},{"metadata":{"trusted":true,"_uuid":"496480afdccb3896ec20c8eced2d982b1096cf37","scrolled":true},"cell_type":"code","source":"print(\"state_id\")\nget_categorical_stats(merc_df, \"state_id\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1565f4648ad680bc0ae8cb8571355573e8d7e300"},"cell_type":"markdown","source":"Observations:\n- There are 24 unique states; not sure if this is US (US has 50 states)\n- 3.5% nulls\n\n```state_id``` and ```category_2``` has same number of nulls - 11,887. Let's see if they occur together"},{"metadata":{"trusted":true,"_uuid":"3ca3bfe380cee4bfa39d4319d9a812460502299d"},"cell_type":"code","source":"merc_df[merc_df.state_id.isna()].category_2.value_counts(dropna=False)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0c95afe398ffab677043e34781d0be88d6d8f9de"},"cell_type":"markdown","source":"Yup, nulls in both the cols - ```state_id``` and ```category_2``` - occur together. We'll investigate this slice after we are done with the remaining columns in the merchant table."},{"metadata":{"trusted":true,"_uuid":"6e1e08cfcea55ddcafe6b346ed2fe55434e440e3"},"cell_type":"markdown","source":"### 4. Merchant ID cols\n\nmerchant_id, merchant_group_id, merchant_category_id, subsector_id"},{"metadata":{"trusted":true,"_uuid":"e84a0098e307f01b187766c64472ad2c5e921bb3","scrolled":true},"cell_type":"code","source":"get_col_stats(merc_df[[\"merchant_id\", \"merchant_group_id\", \"merchant_category_id\", \"subsector_id\"]])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"550e7fd7aa1dc63ff937ef2b3c8ffff8eb5a7cd0"},"cell_type":"code","source":"(\n    merc_df.merchant_id.astype(str)\\\n    + merc_df.merchant_group_id.astype(str)\n).unique().shape\nmerc_df.shape","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"3b86f6244bf052a0fa3ea327fb76d6925640eec0"},"cell_type":"markdown","source":"Since ```merchant_id``` is not uniquely identifying each row in the table, I tried to find some compound key. Unfortunately, no such key is there. Data will need to be deduped on the ```merchant_id``` as we discussed during the sanity check part."},{"metadata":{"_uuid":"b12d16ed3367fb3d68037ac68a3b6835c976b1da"},"cell_type":"markdown","source":"### 5. Most recent sales and purchases\n\nmost_recent_sales_range, most_recent_purchases_range\n\nThses categorical variables have the following values: A > B > C > D > E\n\n\nmost_recent_sales_range: Range of revenue (monetary units) in last active month  \nmost_recent_purchases_range: Range of quantity of transactions in last active month"},{"metadata":{"trusted":true,"_uuid":"534591a58e5d5558e926dbac5d46cc68081a8c3f"},"cell_type":"code","source":"print(\"most_recent_sales_range\")\nget_categorical_stats(merc_df, \"most_recent_sales_range\")\nprint(\"most_recent_purchases_range\")\nget_categorical_stats(merc_df, \"most_recent_purchases_range\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8798cbf0d4c33113fee9d9f97d6677f1ada36ea2"},"cell_type":"code","source":"merc_df.groupby([\"most_recent_sales_range\", \"most_recent_purchases_range\"])\\\n    .merchant_id\\\n    .count()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b36137c7b08cd16c7785ef642a6eeb136706c148"},"cell_type":"code","source":"fig, axs = plt.subplots(2, 3, figsize=(10, 5))\n\ni = 0\nj = 0\nfor val in merc_df.most_recent_purchases_range.unique():\n    ax = axs[i, j]\n    _ = merc_df\\\n        .loc[merc_df.most_recent_purchases_range == val, \"most_recent_sales_range\"]\\\n        .value_counts()\\\n        .plot.bar(ax=ax)\n    _ = ax.set_title(f\"most_recent_purchases_range = {val}\")\n    if i==0 and j==2:\n        i = 1\n        j = 0\n    else:\n        j += 1\n\nplt.tight_layout()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0398a765bf619ee8cdbf8aa2cc899a3326b50106"},"cell_type":"markdown","source":"Observations:\n- Both the columns have 5 unique values\n- No nulls are present\n- most_recent_sales_range give the range of revenue (monetary units) in last active month  \n- most_recent_purchases_range gives the range of quantity of transactions in last active month\n- the above plots show for each transactions bucket, the distribution across the revenue bucket\n- The lowest transactions bucket (E), highest is the lowest revenue bucket (E) showing that most of the merchants are small in size and most of the transactions happening with them are low\n- similarly, A corresponds to A, B to B, C to C and D to D for sales and revenue in the highest values\n- Can I say these columns are correlated?"},{"metadata":{"_uuid":"6bbf4d17f9e737d5eebab38ba47f0759bb1674d2"},"cell_type":"markdown","source":"### 6. lag_3 columns\n\n- ```avg_sales_lag3```: Monthly average of revenue in last 3 months divided by revenue in last active month\n- ```avg_purchases_lag3```: Monthly average of transactions in last 3 months divided by transactions in last active month\n- ```active_months_lag3```: Quantity of active months within last 3 months\n"},{"metadata":{"trusted":true,"_uuid":"5e4ad1f2db82849f84b21401496eb070fb3a62d7"},"cell_type":"code","source":"merc_df[[\"avg_sales_lag3\", \"avg_purchases_lag3\", \"active_months_lag3\"]].describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"deaed9f52cf15887339c4ada0f8c899ef0d2067d"},"cell_type":"code","source":"get_categorical_stats(merc_df, \"active_months_lag3\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"996ed1896c47cbfedbfeff9342a657c231a6b617"},"cell_type":"markdown","source":"Observations:\n- Quantity of active months within last 3 months, is 3 in 99.5% of data meaning, they were active during all the 3 months"},{"metadata":{"trusted":true,"_uuid":"c481d58ccb5c54159f83c1fd424350bd04e7d49a"},"cell_type":"code","source":"fig, axs = plt.subplots(1, 2)\n_ = merc_df[[\"avg_sales_lag3\"]].boxplot(ax=axs[0])\n_ = merc_df[[\"avg_purchases_lag3\"]].boxplot(ax=axs[1])\nplt.tight_layout()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1210017a4887ca2da20fc51db9ba15c6762ea0de"},"cell_type":"code","source":"fig, axs = plt.subplots(1, 2)\n_ = merc_df.loc[merc_df.avg_sales_lag3 < 10, [\"avg_sales_lag3\"]].boxplot(ax=axs[0])\n_ = merc_df.loc[merc_df.avg_purchases_lag3 < 10, [\"avg_purchases_lag3\"]].boxplot(ax=axs[1])\nplt.tight_layout()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b242a110669310fc0d73629b721e08cf592eae64"},"cell_type":"markdown","source":"Observations:\n- both ```avg_purchases_lag3``` and ```avg_sales_lag3``` have a few outliers in the extremes "},{"metadata":{"trusted":true,"_uuid":"82a1aee97e9295ea949ff65c1cb8e08fefba1663"},"cell_type":"code","source":"temp = merc_df.loc[(merc_df.avg_sales_lag3 < 10) & (merc_df.avg_sales_lag3 > -10), [\"avg_sales_lag3\", \"avg_purchases_lag3\"]]\ntemp.plot.scatter(x=\"avg_sales_lag3\", y=\"avg_purchases_lag3\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6b3063c0d8ab8f6fa22caacc78c551bddf052a70"},"cell_type":"markdown","source":"Observations:\n- due to large values, the plot with the whole data was not proper, so i filtered the avg_sales_lag3 between -10 and 10\n- it seems to be between 0 and 10\n- Need to handle the outliers\n- there is not apparent pattern between the columns though"},{"metadata":{"_uuid":"029a000d9656dc609cd67ae377b36750ed7c6e99"},"cell_type":"markdown","source":"### 7. lag_6 columns\n\n- ```avg_sales_lag6```: Monthly average of revenue in last 6 months divided by revenue in last active month\n- ```avg_purchases_lag6```: Monthly average of transactions in last 6 months divided by transactions in last active month\n- ```active_months_lag6```: Quantity of active months within last 6 months\n"},{"metadata":{"trusted":true,"_uuid":"295f48c31a1caba4a3975bb62da3ff4deee139a6"},"cell_type":"code","source":"merc_df[[\"avg_sales_lag6\", \"avg_purchases_lag6\", \"active_months_lag6\"]].describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"deaed9f52cf15887339c4ada0f8c899ef0d2067d"},"cell_type":"code","source":"get_categorical_stats(merc_df, \"active_months_lag6\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"996ed1896c47cbfedbfeff9342a657c231a6b617"},"cell_type":"markdown","source":"Observations:\n- Quantity of active months within last 6 months, is 3 in 97.8% of data meaning, they were active during all the 6 months"},{"metadata":{"trusted":true,"_uuid":"c481d58ccb5c54159f83c1fd424350bd04e7d49a"},"cell_type":"code","source":"fig, axs = plt.subplots(1, 2)\n_ = merc_df[[\"avg_sales_lag6\"]].boxplot(ax=axs[0])\n_ = merc_df[[\"avg_purchases_lag6\"]].boxplot(ax=axs[1])\nplt.tight_layout()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1210017a4887ca2da20fc51db9ba15c6762ea0de"},"cell_type":"code","source":"fig, axs = plt.subplots(1, 2)\n_ = merc_df.loc[merc_df.avg_sales_lag6 < 10, [\"avg_sales_lag6\"]].boxplot(ax=axs[0])\n_ = merc_df.loc[merc_df.avg_purchases_lag6 < 10, [\"avg_purchases_lag6\"]].boxplot(ax=axs[1])\nplt.tight_layout()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"95e1523204f374d051ad840bedc1e494180d50ef"},"cell_type":"markdown","source":"Observations:\n- both ```avg_purchases_lag6``` and ```avg_sales_lag6``` have a few outliers in the extremes "},{"metadata":{"trusted":true,"_uuid":"f85abbeb8bfa13541c98acf16ebea05f941cf17a"},"cell_type":"code","source":"temp = merc_df.loc[(merc_df.avg_sales_lag6 < 10) & (merc_df.avg_sales_lag6 > -10), [\"avg_sales_lag6\", \"avg_purchases_lag6\"]]\ntemp.plot.scatter(x=\"avg_sales_lag6\", y=\"avg_purchases_lag6\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1e3bcbae77d18d689d7d80486a6374a7a97cd589"},"cell_type":"markdown","source":"Observations:\n- due to large values, the plot with the whole data was not proper, so i filtered the avg_sales_lag3 between -10 and 10\n- it seems to be between 0 and 10\n- Need to handle the outliers\n- there is not apparent pattern between the columns though"},{"metadata":{"_uuid":"59d864d800cfdbc127e4b21385d22f3f1dd1d45a"},"cell_type":"markdown","source":"### 8. lag_12 columns\n\n- ```avg_sales_lag12```: Monthly average of revenue in last 12 months divided by revenue in last active month\n- ```avg_purchases_lag12```: Monthly average of transactions in last 12 months divided by transactions in last active month\n- ```active_months_lag12```: Quantity of active months within last 12 months\n\nwork in progress"},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"12173b14d9eaa116723419d29527e39edc4da688"},"cell_type":"code","source":"merc_df[[\"avg_sales_lag12\", \"avg_purchases_lag12\", \"active_months_lag12\"]].describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"deaed9f52cf15887339c4ada0f8c899ef0d2067d"},"cell_type":"code","source":"get_categorical_stats(merc_df, \"active_months_lag12\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"996ed1896c47cbfedbfeff9342a657c231a6b617"},"cell_type":"markdown","source":"Observations:\n- Quantity of active months within last 12 months, is 3 in 91.1% of data meaning, they were active during all the 12 months"},{"metadata":{"trusted":true,"_uuid":"c481d58ccb5c54159f83c1fd424350bd04e7d49a"},"cell_type":"code","source":"fig, axs = plt.subplots(1, 2)\n_ = merc_df[[\"avg_sales_lag12\"]].boxplot(ax=axs[0])\n_ = merc_df[[\"avg_purchases_lag12\"]].boxplot(ax=axs[1])\nplt.tight_layout()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1210017a4887ca2da20fc51db9ba15c6762ea0de"},"cell_type":"code","source":"fig, axs = plt.subplots(1, 2)\n_ = merc_df.loc[merc_df.avg_sales_lag12 < 10, [\"avg_sales_lag12\"]].boxplot(ax=axs[0])\n_ = merc_df.loc[merc_df.avg_purchases_lag12 < 10, [\"avg_purchases_lag12\"]].boxplot(ax=axs[1])\nplt.tight_layout()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e8667bde70571f1cc6f88c34d4d10174e263d332"},"cell_type":"markdown","source":"Observations:\n- both ```avg_purchases_lag12``` and ```avg_sales_lag12``` have a few outliers in the extremes "},{"metadata":{"trusted":true,"_uuid":"ddcfd770604cabfbdc0afbe9228ee8dec865f3dc"},"cell_type":"code","source":"temp = merc_df.loc[(merc_df.avg_sales_lag12 < 10) & (merc_df.avg_sales_lag12 > -10), [\"avg_sales_lag12\", \"avg_purchases_lag12\"]]\ntemp.plot.scatter(x=\"avg_sales_lag12\", y=\"avg_purchases_lag12\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1e3bcbae77d18d689d7d80486a6374a7a97cd589"},"cell_type":"markdown","source":"Observations:\n- due to large values, the plot with the whole data was not proper, so i filtered the avg_sales_lag3 between -10 and 10\n- it seems to be between 0 and 10\n- Need to handle the outliers\n- there is not apparent pattern between the columns though"},{"metadata":{"_uuid":"b53a6ed7bbd07aad88e563acc1b0b63592599a86"},"cell_type":"markdown","source":"### 9. numerical_1 vs other columns"},{"metadata":{"trusted":true,"_uuid":"ad3985714feb46c45810634eff1f2bd96f8d6476"},"cell_type":"code","source":"fig, axs = plt.subplots(1, 3, figsize=(15, 5))\n\nfilt = (merc_df.avg_sales_lag3 < 10) & (merc_df.avg_sales_lag3 > -10)\ntemp = merc_df.loc[filt, [\"numerical_1\", \"avg_sales_lag3\"]]\n_ = temp.plot.scatter(x=\"numerical_1\", y=\"avg_sales_lag3\", ax=axs[0])\n\nfilt = (merc_df.avg_sales_lag6 < 10) & (merc_df.avg_sales_lag6 > -10)\ntemp = merc_df.loc[filt, [\"numerical_1\", \"avg_sales_lag6\"]]\n_ = temp.plot.scatter(x=\"numerical_1\", y=\"avg_sales_lag6\", ax=axs[1])\n\nfilt = (merc_df.avg_sales_lag12 < 10) & (merc_df.avg_sales_lag12 > -10)\ntemp = merc_df.loc[filt, [\"numerical_1\", \"avg_sales_lag12\"]]\n_ = temp.plot.scatter(x=\"numerical_1\", y=\"avg_sales_lag12\", ax=axs[2])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9c5b45e289b1f22f28e8c31e992c624a645f9563"},"cell_type":"markdown","source":"Observations:\n- ```avg_sales_lag``` columns show similar patterns according when plotted corresponding to ```numerical_1```.\n"},{"metadata":{"trusted":true,"_uuid":"7a66562820b246cd19d8f23997dbd36c1d2c14d6"},"cell_type":"code","source":"fig, axs = plt.subplots(1, 3, figsize=(15, 5))\n\nfilt = (merc_df.avg_purchases_lag3 < 10) & (merc_df.avg_purchases_lag3 > -10)\ntemp = merc_df.loc[filt, [\"numerical_1\", \"avg_purchases_lag3\"]]\n_ = temp.plot.scatter(x=\"numerical_1\", y=\"avg_purchases_lag3\", ax=axs[0])\n\nfilt = (merc_df.avg_purchases_lag6 < 10) & (merc_df.avg_purchases_lag6 > -10)\ntemp = merc_df.loc[filt, [\"numerical_1\", \"avg_purchases_lag6\"]]\n_ = temp.plot.scatter(x=\"numerical_1\", y=\"avg_purchases_lag6\", ax=axs[1])\n\nfilt = (merc_df.avg_purchases_lag12 < 10) & (merc_df.avg_purchases_lag12 > -10)\ntemp = merc_df.loc[filt, [\"numerical_1\", \"avg_purchases_lag12\"]]\n_ = temp.plot.scatter(x=\"numerical_1\", y=\"avg_purchases_lag12\", ax=axs[2])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"c6d2c477e13811a25b5b12781945ff36a8071e2f"},"cell_type":"markdown","source":"Observations:\n- ```avg_purchases_lag``` columns show similar patterns according when plotted corresponding to ```numerical_1```.\n"},{"metadata":{"trusted":true,"_uuid":"e32929045e08c4aad43fb4a71648a645f73f11a3"},"cell_type":"code","source":"merc_df.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"018d10f23966df0427eb2a82e40fc57c21d4cafa"},"cell_type":"markdown","source":"### 10. Correlation in lag columns\n\n"},{"metadata":{"trusted":true,"_uuid":"668d7b9120f00c9e0d13c1b3730d3a0b2575f474","scrolled":true},"cell_type":"code","source":"lag_cols = [\n    \"avg_sales_lag3\", \"avg_purchases_lag3\", \"active_months_lag3\",\n    \"avg_sales_lag6\", \"avg_purchases_lag6\", \"active_months_lag6\",\n    \"avg_sales_lag12\", \"avg_purchases_lag12\", \"active_months_lag12\"\n]\ncorr = merc_df[lag_cols].corr()\ncorr.style.background_gradient()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"24c5e8ff1f455cede648c56185689ebae62e6521"},"cell_type":"markdown","source":"### 11. looking at the slice of data where ```state_id``` and ```category_2``` are nulls\n"},{"metadata":{"trusted":true,"_uuid":"1a1f539044532592554f0589c1adba8a572d43a9"},"cell_type":"code","source":"filt = (merc_df.state_id.isna()) & (merc_df.category_2.isna())\ntemp = merc_df.loc[filt]\ntemp.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"21610dc58e1a7b59aee913c98a44561e4e617830"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6b9cd68940bc438a07d0c78e0c0c3c691f21151f"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5b410fac42fc37776cc70870b514e65f2b0359ef"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e2a80e6f26d3ee8b3f077ac2cad872e860cd5f5e"},"cell_type":"markdown","source":"### 12. Deduping merchant data based on ```merchant_id```\n\nwork in progress"},{"metadata":{"trusted":true,"_uuid":"144c28baacb5f0dac0fe02a1eadec3d4c616a95a"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"edfc81f35dff74da5c87e5309064f1a57b0d9480"},"cell_type":"markdown","source":"### 13. ```category_2``` vs lag columns"},{"metadata":{"trusted":true,"_uuid":"bd67c3bee7eca66da5b7e321b6a7181c1da81e7b"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2e9f20aff68c4020cc4c148c9675e868499e17a2"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"7052a6e98df857e32c110fc9a0be7d4a481a0fb6"},"cell_type":"markdown","source":"# Historical Data"},{"metadata":{"trusted":true,"_uuid":"13aef759cb13a5c6dd3e1807ce1fb58e7fe497bd"},"cell_type":"code","source":"hist_df = pd.read_csv(hist_f)\nhist_df.shape\nhist_df.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"02c02acee358b70b1bf424d50499182ed77cd57a"},"cell_type":"markdown","source":"## Sanity Check\n\n- All ```card_id```s in training and testing set\n- All ```merchant_id```s in merchant file\n- Nulls\n"},{"metadata":{"trusted":true,"_uuid":"17b99f9a896756be6805386c572f80c991442c22"},"cell_type":"code","source":"len(set(hist_df.card_id) - set(card_df.card_id) - set(test_df.card_id))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0420b33d6bdefd71539175a74e238351ef4e10dd"},"cell_type":"markdown","source":"There are no new ```card_id```s which are not there in training or test set."},{"metadata":{"trusted":true,"_uuid":"fb764bb4916600fc0242727ffc3a28e242687a4c"},"cell_type":"code","source":"set(hist_df.merchant_id) - set(merc_df.merchant_id)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"28ff04a1ad3a1f4eb00eeedc345e43f00098d670"},"cell_type":"markdown","source":"Except null values in ```merchant_id``` column from historical data, every ```merchant_id``` is present in merchant data. Lets explore nulls"},{"metadata":{"trusted":true,"_uuid":"2b72a4c6a7a01e22c75057ddb1c3b1c45f5e4872"},"cell_type":"code","source":"temp_nulls = hist_df.isnull().sum()\ntemp = pd.DataFrame({\"nulls\": temp_nulls, \"null_percent\": temp_nulls/hist_df.shape[0]})\ntemp","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9361a197ad622c5c1962110c922bc0f9e391d5ff"},"cell_type":"markdown","source":"Except the id cols, we'll not go into other columns. Among ids, only ```merchant_id``` column has 178,159 nulls, which is 0.0061% of the total data. We'll need to handle these when we are creating the final data"},{"metadata":{"_uuid":"ba75b95d166d3f9f7dec2126f729c6a0821a35d1"},"cell_type":"markdown","source":"# New Historical Data"},{"metadata":{"trusted":true,"_uuid":"0d0d8a22b1da134bc442ca978f91fac8a6db55ae"},"cell_type":"code","source":"new_hist_df = pd.read_csv(new_hist_f)\nnew_hist_df.shape\nnew_hist_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"44e9fa9da03cd30321c5c285c6eb881aa249bf87"},"cell_type":"markdown","source":"## Sanity Check\n\n- All ```card_id```s in training and testing set\n- All ```merchant_id```s in merchant file\n- Nulls\n"},{"metadata":{"trusted":true,"_uuid":"a3c31a0aa6d024ef03119f075e174092f9570492"},"cell_type":"code","source":"len(set(new_hist_df.card_id) - set(card_df.card_id) - set(test_df.card_id))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b54971e91af141b0826d69056e9f25fec900a518"},"cell_type":"markdown","source":"No extra ```card_id``` in the new merchants file."},{"metadata":{"trusted":true,"_uuid":"4e45a85c8beb8bde7d423714c5a57b4791beb874"},"cell_type":"code","source":"set(new_hist_df.merchant_id) - set(merc_df.merchant_id)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3f3b0ba04ddfd2669a2d45a15bbe2ec1b40eb5c3"},"cell_type":"markdown","source":"New merchants file also has nulls in the ```merchant_id```. Other than that every ```merchant_id``` is present in the merchants file."},{"metadata":{"trusted":true,"_uuid":"08e8d1a5b79400ccaab6473dd8fd5933c7f40047"},"cell_type":"code","source":"temp_nulls = new_hist_df.isnull().sum()\ntemp = pd.DataFrame({\"nulls\": temp_nulls, \"null_percent\": temp_nulls/new_hist_df.shape[0]})\ntemp","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d1c8285e49fe1e5f3b975ca1098fe25a20042820"},"cell_type":"markdown","source":"Following the same pattern as the historical data, we will only discuss id columns here. Col ```merchant_id``` has 26,216 nulls which is 0.013% of the data."},{"metadata":{"trusted":true,"_uuid":"9a38af464b1425c22de75df361ea4597b7becce7"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"14b0515fb8620dd639c231bfca776f523be68395"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fcd944a05cc6f7a762e3e8c5238600f53739ac5a"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"727e96c2c20a3d3e706b4ee5b879f8b75c896438"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8bba44b2477e4f938909980807dea396e273893a"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"db1c1b6275d5740028370f4c77db029079c35ed8"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a11aaf354bf61bb56a212c7bfdde5997aecd7226"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8caf2faa1cd7ecdf05a9fc614515699fa03fe45a"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"797ebd0475cfc58ce35254af1057f5e77646ceb8"},"cell_type":"code","source":"","execution_count":null,"outputs":[]}],"metadata":{"kernelspec":{"display_name":"Python 3","language":"python","name":"python3"},"language_info":{"name":"python","version":"3.6.6","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"}},"nbformat":4,"nbformat_minor":1}