{"cells":[{"metadata":{"_uuid":"afe3f95d82c6f688b90a8c7d38dd3ed84fc2c175"},"cell_type":"markdown","source":"# ELO data exploration\n\n[TOC]\n\nthis is my late EDA for this competition. I made it for myself, but perhaps other people will find it useful. I am still playing with it so please refresh from time to time"},{"metadata":{"_uuid":"a83c3e0b71671c373f74ce66cd587bd612c6ef86"},"cell_type":"markdown","source":"### imports and listing files"},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load in \n\nimport numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport seaborn as sns\nimport matplotlib.pyplot as plt\nimport matplotlib\nfrom matplotlib_venn import venn2, venn3\nimport squarify\n# Input data files are available in the \"../input/\" directory.\n# For example, running this (by clicking run or pressing Shift+Enter) will list the files in the input directory\nimport warnings\nwarnings.simplefilter(action='ignore', category=FutureWarning)\nimport os\nprint(os.listdir(\"../input\"))\n\n# Any results you write to the current directory are saved as output.","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"55cfe41a29e7a26e2a2ef53aa3e3418609cf62bf"},"cell_type":"markdown","source":"## Train fields description"},{"metadata":{"trusted":true,"_uuid":"8f7ff7b684a8887ccf054a8fe560f2afe9d73c9e"},"cell_type":"code","source":"pd.read_excel(\"../input/Data_Dictionary.xlsx\", sheet = 0, header = 2)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b807b1ce8a38ab3e03b3083b1e6f37ce05123230"},"cell_type":"markdown","source":"## peek at train and train shape"},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true,"scrolled":true},"cell_type":"code","source":"train  = pd.read_csv(\"../input/train.csv\")\nprint(train.shape)\n\ntrain.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"eb9c78f2d2d1545cbdfe61656403584effab3048"},"cell_type":"markdown","source":"Target distribution"},{"metadata":{"trusted":true,"_uuid":"721df4b9f31eef01679a9fe73af4150503913587","scrolled":false},"cell_type":"code","source":"sns.distplot(train.target)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"779c561464243443cfe19893a8149e1644734820"},"cell_type":"markdown","source":"peek at test"},{"metadata":{"trusted":true,"_uuid":"2ccc58f567f005796a3d6485b8f5fd08e91b7d1e"},"cell_type":"code","source":"test = pd.read_csv(\"../input/test.csv\")\nprint(test.shape)\ntest.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"9b5e321a172189ee704f9940e3ab927c958f4a66"},"cell_type":"markdown","source":"## Let's check if any card_ids overlap between train and test"},{"metadata":{"trusted":true,"_uuid":"35037f967bbea588a53f5c4a93a229b11e11f4a6"},"cell_type":"code","source":"venn2([set(train.card_id), set(test.card_id)])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"bcb464413bc8b48ddeab0598ea3e1e30bccd2159"},"cell_type":"markdown","source":"conclusion: they don't"},{"metadata":{"_uuid":"4a1e31dda49033684a5c938736922a123b9d0b66"},"cell_type":"markdown","source":"## Just in case, let's check for any dependency of the target on data order or card_id"},{"metadata":{"trusted":true,"_uuid":"b0aadc1334e115762d01cfd9993339a17081414a"},"cell_type":"code","source":"sns.jointplot(train.index.values, train.target)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ccbb5694c509b28ca716389f42b6aa1cfb4e99da"},"cell_type":"code","source":"from sklearn.preprocessing import LabelEncoder","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"70644f95b743ff55ec9f69b05b5654a0aeb53296"},"cell_type":"code","source":"sns.jointplot(LabelEncoder().fit_transform(train.card_id), train.target)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"058d5b7aca0a4ae178877191c2e5b486e224d5b1"},"cell_type":"markdown","source":"conclusion: no dependency"},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"166bbf0301aed48a16c83ab7be29afaf721f51b1"},"cell_type":"code","source":"train[\"is_test\"] = np.int8(0)\ntest[\"is_test\"] = np.int8(1)\ntest[\"target\"] = np.NaN\ntrain_test = pd.concat([train,test], ignore_index=True, sort=True, axis = 0)\ntrain_test[\"first_active_month\"] = pd.to_datetime(train_test.first_active_month)\ntrain_test[\"mon\"] = train_test.first_active_month.dt.month\ntrain_test[\"year\"] = train_test.first_active_month.dt.year\n\nprint(train_test.shape)\ntrain_test.head()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"67171a56c66b8179a4e2ce38816dcdca9b0c9409"},"cell_type":"code","source":"del train\ndel test","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"68b27abe2932af82f214351e19be218366cc8e84"},"cell_type":"markdown","source":"## Let's have a look at the \"feature_N\" categoricals. Is the proportion of different categories different for train and test?"},{"metadata":{"trusted":true,"_uuid":"9794416dcb7f821bd2545b246dcc94c78c4324c6"},"cell_type":"code","source":" pd.merge(train_test.groupby([\"is_test\", \"feature_1\"])[\"card_id\"].count().reset_index(),\n                      train_test.groupby([\"is_test\"]).count().reset_index(), on = \"is_test\" )","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"09bd148cf268e5ad0de5151718159fbbbb3548f3"},"cell_type":"code","source":"def compare_cat_pct_counts(df, col_name, compare_col_name = \"is_test\", ind_col =\"card_id\"):\n    cnt_df = pd.merge(df.groupby([compare_col_name, col_name])[ind_col].count().reset_index(),\n                      df.groupby([compare_col_name])[ind_col].count().reset_index(), on = \"is_test\" )\n    cnt_df[\"cnt\"] = cnt_df[ind_col + \"_x\"] / cnt_df[ind_col + \"_y\"] \n    sns.barplot(col_name, \"cnt\", hue = compare_col_name, data = cnt_df)\n     ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2404b5d45651bb30135ae1f91960cfe7ec41bda6"},"cell_type":"code","source":"compare_cat_pct_counts(train_test, \"feature_1\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5817780e881aca8e8ba4f926f4b97816776373ce"},"cell_type":"code","source":"compare_cat_pct_counts(train_test, \"feature_2\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a643060788f0f70734cf4f70061c5eac098db9fa"},"cell_type":"code","source":"compare_cat_pct_counts(train_test, \"feature_3\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"36b4bc911dbaff1a50277eff83d4ab06e34b0778"},"cell_type":"markdown","source":"conclusion: same proportion of categories for train and test"},{"metadata":{"_uuid":"77b102ef4463bac0ad850ebeaf7f900322004ba2"},"cell_type":"markdown","source":"## Comparing target stats for different categories"},{"metadata":{"trusted":true,"_uuid":"af5e365986b02d3485b139c93acfe00120778d7a"},"cell_type":"code","source":"plt.subplots(figsize=(15,6))\nplt.subplot(131)\nsns.boxplot(train_test[\"feature_1\"], train_test.target)\nplt.subplot(132)\nsns.boxplot(train_test[\"feature_2\"], train_test.target)\nplt.subplot(133)\nsns.boxplot(train_test[\"feature_3\"], train_test.target)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8ced01b07adaffa8cf6efc3e53647c7be232e2b6"},"cell_type":"markdown","source":"conclusion: these features look useless. What do they mean?"},{"metadata":{"_uuid":"815f4eafa1769622f968f54cc86e8fcfe267b61d"},"cell_type":"markdown","source":"## Let's look at the first_active_month"},{"metadata":{"trusted":true,"_uuid":"6a13984c8efe026539dd7778cb60a4df317f76ea"},"cell_type":"code","source":"compare_cat_pct_counts(train_test, \"mon\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b0ffbf7c4eb04033e401261efdc8b564a2f1e463"},"cell_type":"code","source":"compare_cat_pct_counts(train_test, \"year\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"86efc20a3e77f75a22397b52f971880c972418d5"},"cell_type":"code","source":"plt.subplots(figsize=(15,6))\nplt.subplot(121)\nsns.boxplot(train_test.mon, train_test.target)\nplt.subplot(122)\nsns.boxplot(train_test.year, train_test.target)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a9fd69ca8a3a8d54f87753ac545760c2be33aa08","scrolled":true},"cell_type":"code","source":"pd.read_excel(\"../input/Data_Dictionary.xlsx\", 1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5babdad118ed143b087d66becb6aecb605505658"},"cell_type":"code","source":"hist_trans = pd.read_csv(\"../input/historical_transactions.csv\")\nprint(hist_trans.shape)\nhist_trans.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"361459883e91afd109a1d86118dae027f003e367"},"cell_type":"code","source":"plt.figure(figsize=(20,10))\nprint(hist_trans.card_id.nunique())\nvenn3([set(hist_trans.card_id.unique()), set(train_test.query(\"is_test == 0\").card_id), \n                                             set(train_test.query(\"is_test == 1\").card_id)])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e1799ba397a8cc690b9a20816f461eac5a697a0d"},"cell_type":"markdown","source":"So, all card_ids present in transaction history, are either from train or from test"},{"metadata":{"_uuid":"232ce66b48dd13dd0ae491f0708c0c461a6cf890"},"cell_type":"markdown","source":"## Number of transactions per card"},{"metadata":{"trusted":true,"_uuid":"a63c3d7c74160266a39f553458faa035c3a82818"},"cell_type":"code","source":"hist_trans.card_id.value_counts().head(10)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"65c8e361282adacc82d82f566e10f40f9d7928a8"},"cell_type":"markdown","source":"Looks like some cards have a lot of transactions"},{"metadata":{"trusted":true,"_uuid":"6e644c91564d7ad2a3784c8cb6fad148d001f584"},"cell_type":"code","source":"tmp_df = pd.merge(hist_trans.card_id.value_counts().reset_index(), train_test, left_on=\"index\", right_on = \"card_id\")\nplt.subplots(figsize = (14, 6))\nplt.subplot(131)\nsns.boxplot(\"is_test\", \"card_id_x\", data=tmp_df )\nplt.subplot(132)\nsns.distplot(tmp_df.query(\"is_test == 0\")[\"card_id_x\"],  color = \"blue\" )\nsns.distplot(tmp_df.query(\"is_test == 1\")[\"card_id_x\"],  color = \"green\")\nplt.subplot(133)\nsns.distplot(np.log(tmp_df.query(\"is_test == 0\")[\"card_id_x\"]),  color = \"blue\" )\nsns.distplot(np.log(tmp_df.query(\"is_test == 1\")[\"card_id_x\"]),  color = \"green\")\ndel tmp_df","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"82a6e03744cd358feca263603bc02d3b4957e821"},"cell_type":"markdown","source":"Count of transactions has lognormal distribution, with some cards have an extreme number of transactions. However test set happens to have some cards that have even bigger number of transactions (5k vs 3k highest in train)\n\nBy now, I am convinced that train and test split is absolutely random and are selected from the same population and there is no need to compare the two sets further."},{"metadata":{"trusted":true,"_uuid":"b87e83a2e64c38227dbe886f2dcc8b9584a19e32"},"cell_type":"code","source":"# a helper function\ndef plot_cat_treemap(df, col_name, title = None):\n    cnts = df[col_name].value_counts()\n    cmap = matplotlib.cm.Spectral\n    mini=min(cnts)\n    maxi=max(cnts)\n    norm = matplotlib.colors.Normalize(vmin=mini, vmax=maxi)\n    colors = [cmap(norm(value)) for value in cnts]\n\n    \n    squarify.plot(sizes = cnts, label = cnts.index.values, value = cnts, color = colors)\n    plt.axis(\"off\")\n    plt.title(title)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f194835d5084784bd5b1ab6db1cd9dd5e3782f3b"},"cell_type":"markdown","source":"## authorized_flag"},{"metadata":{"_uuid":"911ac27db54c060f8be7e87bb5204437367ffd01"},"cell_type":"markdown","source":"### number of authorized / non authorized and target"},{"metadata":{"trusted":true,"_uuid":"b123c94c3f40cffe7a2cfa91da42421852685716"},"cell_type":"code","source":"card_auth_agg = pd.merge(train_test, \n                         hist_trans.groupby([\"card_id\", \"authorized_flag\"])[\"city_id\"].count().unstack(level=-1),\n                         on = \"card_id\").rename(columns = {\"N\":\"cnt_unauthorized\", \"Y\":\"cnt_authorized\"}).fillna(0)\n\ncard_auth_agg[\"cnt_trans\"] = card_auth_agg[\"cnt_unauthorized\"] + card_auth_agg[\"cnt_authorized\"] \n#plt.subplot(311)\nsns.jointplot(card_auth_agg.target, card_auth_agg.cnt_trans)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ea6bde43fd3b8c19f14577f8d9c35f84bccfcde7"},"cell_type":"code","source":"sns.jointplot(card_auth_agg.target, card_auth_agg.cnt_unauthorized)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"914dbf2ff1014d80acb40bf318a91acf204b885b"},"cell_type":"code","source":"sns.jointplot(card_auth_agg.target, card_auth_agg.cnt_authorized)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c973b9c35eba911892b97b1bc9e4ef37cf643642"},"cell_type":"code","source":"del card_auth_agg","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"71f99ea3bb25cddc1588383adc2879907c4b9866"},"cell_type":"markdown","source":"conclusion: seems like for a very high number of transactions (independent on whether they were unathorized or not, the loyalty score is 0). For lower number of transactions, the loyalty score is all over"},{"metadata":{"_uuid":"af943ed7260f47abbfb5d175adbc7442cde91c8d"},"cell_type":"markdown","source":"## city_id"},{"metadata":{"trusted":true,"_uuid":"fef22f9a22f302c35172bd10c85d0f3a02aa8c44"},"cell_type":"markdown","source":"### number of historical transactions per city"},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"40cdaf9c6fdde95242fa6565eec14867b40af62d"},"cell_type":"code","source":"plt.figure(figsize=(20,14))\nplot_cat_treemap(hist_trans, \"city_id\", \"City ID\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"aa4e31da2ab85dff641de94248f0d5059301bb57"},"cell_type":"markdown","source":"### Purchase amt by city"},{"metadata":{"trusted":true,"_uuid":"9b75595b1ffbf5433c83a96f629072bbf955bde3"},"cell_type":"code","source":"city_agg = hist_trans.groupby(\"city_id\")[\"purchase_amount\"].agg([\"mean\", \"min\", \"max\", \"median\"])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"95335b30b8fead1e783d2ce6afe68a7a9ef91633"},"cell_type":"code","source":"city_agg.sort_values(\"mean\", ascending=False).head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"2c746eb9f0e1a701aaf24b16ff6c1a8dd94968e3"},"cell_type":"code","source":"city_agg.sort_values(\"max\", ascending=False).head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"10e5f92942d76aa852569f6dc16a15768686e7fe"},"cell_type":"markdown","source":"Some purchase amounts are huge, especially knowing they are already scaled"},{"metadata":{"trusted":true,"_uuid":"5f0de539ef1b5ddec52ddcc36eb82521ca92a08a"},"cell_type":"code","source":"del city_agg","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"54dfdede1e9510f6178ecc7d031be30b9ebb6618"},"cell_type":"markdown","source":"### Number of citites per persona"},{"metadata":{"trusted":true,"_uuid":"b661d37ed7e61dcb7bd3b0ada3fc55299f613436"},"cell_type":"code","source":"city_card_agg  = pd.merge(hist_trans.groupby(\"card_id\")[\"city_id\"].agg([\"nunique\"]).reset_index(), train_test, on=\"card_id\").rename(columns={\"nunique\":\"city_count\"})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"acae54c3dada55f6e39cb506b8bcc1696b12411e"},"cell_type":"code","source":"plt.subplot(111)\nsns.distplot(city_card_agg.loc[~city_card_agg.target.isnull(), \"city_count\"], color = \"blue\")\nsns.distplot(city_card_agg.loc[city_card_agg.target.isnull(), \"city_count\"], color = \"green\")","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"0f30a852b9796266a4e9036ad2b7d1b20ada3a7e"},"cell_type":"markdown","source":"Conclusion: most of the cards conduct transactions from a few cities. there are some, that have 70-100 different cities in their transaction history."},{"metadata":{"trusted":true,"_uuid":"c4bcd10c8dcefb7c9a5813c1e62ea1464ce6eb5a"},"cell_type":"code","source":"del city_card_agg","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"be9b82213f484b9c8869038f6461afb3cbfa5499"},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"67bf448f8e360720ca6dd123120c4b922347e267"},"cell_type":"markdown","source":"### Target by the most frequent city"},{"metadata":{"_uuid":"891d87bff8c2879df2fa7ab1f9226afcdf4b05b9"},"cell_type":"markdown","source":"## state_id"},{"metadata":{"trusted":true,"_uuid":"a2c82820eb86237320fc31a8e7f2ec712a13c07d"},"cell_type":"code","source":"plt.figure(figsize=(20,14))\nplot_cat_treemap(hist_trans, \"state_id\", \"State ID\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2676e65cad691d4846a6a6488248765132493944"},"cell_type":"code","source":"stat_city_df = hist_trans.groupby([\"state_id\", \"city_id\"])[\"card_id\"].count().reset_index().sort_values(\"card_id\", ascending=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f6a7552d7772c47aea972889852a997b08e41679"},"cell_type":"code","source":"stat_city_df.query(\"state_id == -1\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e44fded005e6128abaff185054a94c1cdc3132f9"},"cell_type":"code","source":"stat_city_df.query(\"city_id == -1\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8b11447bc3ef5e480afdda912b6b77071db6f788"},"cell_type":"code","source":"stat_city_df.query(\"city_id == 179\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0da06b281620785cad9f586152d4e5e202503930"},"cell_type":"code","source":"stat_city_df.query(\"city_id == 75\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b8d7703c31e122c0056ca42d1d28354f299352df"},"cell_type":"code","source":"del stat_city_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1e1cc175ac8f20648a8004256f9fe8e7c019912c"},"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}