{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"import datatable as dt\nimport pandas as pd\nimport numpy as np\nimport time\nimport gc\nfrom collections import Counter\n\n# Visualization\nimport seaborn as sns\nimport matplotlib.pyplot as plt\npd.set_option('display.max_columns', 200)\npd.set_option('display.max_colwidth', 400)\n\nimport warnings\nwarnings.filterwarnings('ignore')\n\n%matplotlib inline\n%config InlineBackend.figure_format = 'retina'","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Functions","metadata":{}},{"cell_type":"code","source":"def frame_corr(dt_frame):\n    numcols = [col for col in dt_frame]\n    result = dt.rbind([dt_frame[:, [dt.corr(col1, col2) for col2 in numcols]] for col1 in numcols])\n    result.names = dt_frame[:,numcols].names\n    corr_result = result.to_pandas()\n\n    return corr_result.set_index([pd.Index(corr_result.columns)])","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Load csv to datatable dataframe ","metadata":{}},{"cell_type":"code","source":"FPATH = \"../input/amex-default-prediction/\"\n\ntrain_df = dt.fread(FPATH+\"train_data.csv\")\n\n","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_labels_df = dt.fread(FPATH+\"train_labels.csv\")","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df.view()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_labels_df.view()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_labels_df.key = \"customer_ID\"\n","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"train_df = train_df[:, :, dt.join(train_labels_df)]","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"del train_labels_df","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc.collect()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Column Descriptions :\n\ncustomer_ID = Unique Customer ID\n- D_* = Delinquency variables\n- S_* = Spend variables\n- P_* = Payment variables\n- B_* = Balance variables\n- R_* = Risk variables","metadata":{}},{"cell_type":"code","source":"print(\"Train shape (nrows, ncols): \",train_df.shape)   # (nrows, ncols)\n","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"Counter(list(train_df.stypes))","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Customer_ID\n\nThere are 458913 unique customer ID in train dataset and from the two graphs below we can see that there are one Customer register in each date","metadata":{}},{"cell_type":"code","source":"print(\"Customer_ID (qrows, unique):\",train_df[:, dt.count(dt.f.customer_ID)].to_pandas().values[0][0], dt.unique(train_df[\"customer_ID\"]).to_pandas().shape[0])","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"count_df = train_df[:, dt.count(dt.f.customer_ID), dt.by(dt.time.ymd(dt.time.year(dt.f.S_2), dt.time.month(dt.f.S_2), 1))].to_pandas()\ncount_df.columns = ['date', 'qcustomerid']\nplt.figure(figsize=(12,8))\nax = sns.barplot(x=\"date\", y=\"qcustomerid\", data=count_df, ci = 0)\nax.tick_params(axis='x', rotation=90)\nax.set(title='Number row per date transaction')\nplt.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"\ndf_tmp = train_df[:,{'customer_ID': dt.f[0], 'date': dt.time.ymd(dt.time.year(dt.f.S_2), dt.time.month(dt.f.S_2), 1)}].to_pandas()\ncount_df = df_tmp.groupby('date').nunique().reset_index()\ncount_df.columns = ['date', 'quniq_customerid']\nplt.figure(figsize=(12,8))\nax = sns.barplot(x=\"date\", y=\"quniq_customerid\", data=count_df, ci = 0)\nax.tick_params(axis='x', rotation=90)\nax.set(title='Number unique customerId per date transaction')\nplt.show()\n","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"gc.collect()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Null values","metadata":{}},{"cell_type":"code","source":"train_null = train_df.countna().to_pandas().T.reset_index()\ntrain_null.columns = ['variable', 'qnull']\ntrain_null = train_null[train_null['qnull']>0]\ntrain_null = train_null.sort_values(by = 'qnull',ascending = True)\nplt.figure(figsize=(28,8))\nax = sns.barplot(x=\"variable\", y=\"qnull\", data=train_null, ci = 0)\nax.tick_params(axis='x', rotation=90)\nax.set(title='Variable null distribution')\nplt.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Describe","metadata":{}},{"cell_type":"code","source":"qunique_df = train_df.nunique().to_pandas().T\nmax_df = train_df.max().to_pandas().T\nmin_df = train_df.min().to_pandas().T\nmean_df = train_df.mean().to_pandas().T\nstddev_df = train_df.sd().to_pandas().T\nmode_df = train_df.mode().to_pandas().T\nsummary_df = pd.concat([qunique_df, max_df, min_df, mean_df, stddev_df, mode_df], axis=1)\nsummary_df.columns = ['nunique', 'max', 'min', 'mean', 'steddev', 'mode']\nsummary_df","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Categorical vars and target","metadata":{}},{"cell_type":"code","source":"fig, ax =plt.subplots(4,3, figsize=(20,20))\n\n\ncat_var = \"B_30\"\nagg_df = train_df[:, dt.count(dt.f.customer_ID), dt.by(cat_var)].to_pandas()\nsns.barplot(x=cat_var, y=\"customer_ID\", data=agg_df, ci = 0, ax=ax[0,0])\n\ncat_var = \"B_38\"\nagg_df = train_df[:, dt.count(dt.f.customer_ID), dt.by(cat_var)].to_pandas()\nsns.barplot(x=cat_var, y=\"customer_ID\", data=agg_df, ci = 0, ax=ax[0,1])\n\ncat_var = \"D_114\"\nagg_df = train_df[:, dt.count(dt.f.customer_ID), dt.by(cat_var)].to_pandas()\nsns.barplot(x=cat_var, y=\"customer_ID\", data=agg_df, ci = 0, ax=ax[0,2])\n\ncat_var = \"D_116\"\nagg_df = train_df[:, dt.count(dt.f.customer_ID), dt.by(cat_var)].to_pandas()\nsns.barplot(x=cat_var, y=\"customer_ID\", data=agg_df, ci = 0, ax=ax[1,0])\n\ncat_var = \"D_117\"\nagg_df = train_df[:, dt.count(dt.f.customer_ID), dt.by(cat_var)].to_pandas()\nsns.barplot(x=cat_var, y=\"customer_ID\", data=agg_df, ci = 0, ax=ax[1,1])\n\ncat_var = \"D_120\"\nagg_df = train_df[:, dt.count(dt.f.customer_ID), dt.by(cat_var)].to_pandas()\nsns.barplot(x=cat_var, y=\"customer_ID\", data=agg_df, ci = 0, ax=ax[1,2])\n\ncat_var = \"D_126\"\nagg_df = train_df[:, dt.count(dt.f.customer_ID), dt.by(cat_var)].to_pandas()\nsns.barplot(x=cat_var, y=\"customer_ID\", data=agg_df, ci = 0, ax=ax[2,0])\n\ncat_var = \"D_63\"\nagg_df = train_df[:, dt.count(dt.f.customer_ID), dt.by(cat_var)].to_pandas()\nsns.barplot(x=cat_var, y=\"customer_ID\", data=agg_df, ci = 0, ax=ax[2,1])\n\ncat_var = \"D_64\"\nagg_df = train_df[:, dt.count(dt.f.customer_ID), dt.by(cat_var)].to_pandas()\nsns.barplot(x=cat_var, y=\"customer_ID\", data=agg_df, ci = 0, ax=ax[2,2])\n\ncat_var = \"D_66\"\nagg_df = train_df[:, dt.count(dt.f.customer_ID), dt.by(cat_var)].to_pandas()\nsns.barplot(x=cat_var, y=\"customer_ID\", data=agg_df, ci = 0, ax=ax[3,0])\n\ncat_var = \"D_68\"\nagg_df = train_df[:, dt.count(dt.f.customer_ID), dt.by(cat_var)].to_pandas()\nsns.barplot(x=cat_var, y=\"customer_ID\", data=agg_df, ci = 0, ax=ax[3,1])\n\ncat_var = \"target\"\nagg_df = train_df[:, dt.count(dt.f.customer_ID), dt.by(cat_var)].to_pandas()\nsns.barplot(x=cat_var, y=\"customer_ID\", data=agg_df, ci = 0, ax=ax[3,2])\n\nfig.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Target","metadata":{}},{"cell_type":"code","source":"agg_df = train_df[:,['customer_ID', 'target']].to_pandas().groupby('target').nunique().reset_index()\nax = sns.barplot(x='target', y=\"customer_ID\", data=agg_df, ci = 0)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"import random\n \n# using random.sample()\n# to generate random number list\nsample = random.sample(range(0, 5531451), int(5531451/10))\n\ncat_vars = ['B_30','B_31', 'B_38', 'D_114', 'D_116', 'D_117', 'D_120', 'D_126', 'D_63', 'D_64', 'D_66', 'D_68','S_2']","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Delinquency variables","metadata":{}},{"cell_type":"code","source":"delinquency_vars = [s for s in train_df.names if \"D_\" in s if s not in cat_vars]\n\nfig, ax =plt.subplots(13,7, figsize=(28,28))\n\nfor i in range(0,13):\n    for j in range(0,7):\n        \n        if delinquency_vars:\n            num_var = delinquency_vars.pop()\n        \n            if num_var is not None:\n                agg_df = train_df[sample, [num_var,'target']].to_pandas()\n                sns.boxplot(data=agg_df, y=num_var, x='target',ax=ax[i,j])\n\ngc.collect()\n\nfig.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"delinquency_vars = [s for s in train_df.names if \"D_\" in s if s not in cat_vars if s not in ['customer_ID', 'S_2']]\n\ncorr = frame_corr(train_df[:, delinquency_vars])\n\n\nf, ax = plt.subplots(figsize=(34, 34))\nmask = np.triu(np.ones_like(corr, dtype=bool))\ncmap = sns.diverging_palette(230, 20, as_cmap=True)\nsns.heatmap(corr, annot=True, mask = mask, cmap=cmap)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"NBINS = 1000\n\ndelinquency_vars = [s for s in train_df.names if \"D_\" in s if s not in cat_vars]\n\nfig, ax =plt.subplots(13,7, figsize=(34,28))\n\nfor i in range(0,13):\n    for j in range(0,7):\n        \n        if delinquency_vars:\n            num_var = delinquency_vars.pop()\n        \n            if num_var is not None:\n                agg_df = train_df[:, dt.cut(dt.f[num_var], nbins = NBINS)].to_pandas()\n                sns.distplot(agg_df[num_var], kde = False, color ='red', bins = NBINS, ax=ax[i,j])\n\n\ngc.collect()\n\nfig.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Spend variables","metadata":{}},{"cell_type":"code","source":"spend_vars = [s for s in train_df.names if \"S_\" in s if s not in cat_vars if s not in ['customer_ID', 'S_2']]\n\nfig, ax =plt.subplots(3,7, figsize=(24,12))\n\nfor i in range(0,3):\n    for j in range(0,7):\n        num_var = spend_vars.pop()\n        \n        agg_df = train_df[sample, [num_var,'target']].to_pandas()\n        sns.boxplot(data=agg_df, y=num_var, x='target',ax=ax[i,j])\n\ngc.collect()\n\nfig.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"NBINS = 1000\n\n\nspend_vars = [s for s in train_df.names if \"S_\" in s if s not in cat_vars if s not in ['customer_ID', 'S_2']]\n\nfig, ax =plt.subplots(3,7, figsize=(24,12))\n\nfor i in range(0,3):\n    for j in range(0,7):\n        num_var = spend_vars.pop()\n        \n\n        agg_df = train_df[:, dt.cut(dt.f[num_var], nbins = NBINS)].to_pandas()\n        sns.distplot(agg_df[num_var], kde = False, color ='red', bins = NBINS,ax=ax[i,j])\n\ngc.collect()\n\nfig.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"spend_vars = [s for s in train_df.names if \"S_\" in s if s not in cat_vars if s not in ['customer_ID', 'S_2']]\n\ncorr = frame_corr(train_df[:, spend_vars])\n\n\nf, ax = plt.subplots(figsize=(28, 28))\nmask = np.triu(np.ones_like(corr, dtype=bool))\ncmap = sns.diverging_palette(230, 20, as_cmap=True)\nsns.heatmap(corr, annot=True, mask = mask, cmap=cmap)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Payment variables","metadata":{}},{"cell_type":"code","source":"payment_vars = [s for s in train_df.names if \"P_\" in s if s not in cat_vars]\n\nfig, ax =plt.subplots(3, figsize=(6,12))\n\nfor i in range(0,3):\n    num_var = payment_vars.pop()\n\n    agg_df = train_df[sample, [num_var,'target']].to_pandas()\n    sns.boxplot(data=agg_df, y=num_var, x='target',ax=ax[i])\n\ngc.collect()\n\nfig.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"NBINS = 1000\n\n\npayment_vars = [s for s in train_df.names if \"P_\" in s if s not in cat_vars ]\n\nfig, ax =plt.subplots(3, figsize=(10,12))\n\nfor i in range(0,3):\n    num_var = payment_vars.pop()\n\n\n    \n    agg_df = train_df[:, dt.cut(dt.f[num_var], nbins = NBINS)].to_pandas()\n    sns.distplot(agg_df[num_var], kde = False, color ='red', bins = NBINS,ax=ax[i])\n\ngc.collect()\n\nfig.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"payment_vars = [s for s in train_df.names if \"P_\" in s if s not in cat_vars if s not in ['customer_ID', 'S_2']]\n\ncorr = frame_corr(train_df[:, payment_vars])\n\n\nf, ax = plt.subplots(figsize=(6, 6))\nmask = np.triu(np.ones_like(corr, dtype=bool))\ncmap = sns.diverging_palette(230, 20, as_cmap=True)\nsns.heatmap(corr, annot=True, mask = mask, cmap=cmap)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Balance variables","metadata":{}},{"cell_type":"code","source":"balance_vars = [s for s in train_df.names if \"B_\" in s if s not in cat_vars]\n\nfig, ax =plt.subplots(7,6, figsize=(26,13))\n\nfor i in range(0,7):\n    for j in range(0,6):\n        \n        if balance_vars:\n            num_var = balance_vars.pop()\n        \n            if num_var is not None:\n                agg_df = train_df[sample, [num_var,'target']].to_pandas()\n                sns.boxplot(data=agg_df, y=num_var, x='target',ax=ax[i,j])\n\ngc.collect()\n\nfig.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"NBINS = 1000\n\nbalance_vars = [s for s in train_df.names if \"B_\" in s if s not in cat_vars]\n\nfig, ax =plt.subplots(7,6, figsize=(24,28))\n\nfor i in range(0,7):\n    for j in range(0,6):\n        \n        if balance_vars:\n            num_var = balance_vars.pop()\n        \n            if num_var is not None:\n                \n                agg_df = train_df[:, dt.cut(dt.f[num_var], nbins = NBINS)].to_pandas()\n                sns.distplot(agg_df[num_var], kde = False, color ='red', bins = NBINS, ax=ax[i,j])\n\ngc.collect()\n\nfig.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"balance_vars = [s for s in train_df.names if \"B_\" in s if s not in cat_vars]\n\ncorr = frame_corr(train_df[:, balance_vars])\n\n\nf, ax = plt.subplots(figsize=(28, 28))\nmask = np.triu(np.ones_like(corr, dtype=bool))\ncmap = sns.diverging_palette(230, 20, as_cmap=True)\nsns.heatmap(corr, annot=True, mask = mask, cmap=cmap)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Risk variables","metadata":{}},{"cell_type":"code","source":"risk_vars = [s for s in train_df.names if \"R_\" in s if s not in cat_vars]\n\nfig, ax =plt.subplots(7,4, figsize=(20,16))\n\nfor i in range(0,7):\n    for j in range(0,4):\n        \n        if risk_vars:\n            num_var = risk_vars.pop()\n        \n            if num_var is not None:\n                agg_df = train_df[sample, [num_var,'target']].to_pandas()\n                sns.boxplot(data=agg_df, y=num_var, x='target',ax=ax[i,j])\n\ngc.collect()\n\nfig.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"NBINS = 1000\n\nrisk_vars = [s for s in train_df.names if \"R_\" in s if s not in cat_vars]\n\nfig, ax =plt.subplots(7,4, figsize=(28,28))\n\nfor i in range(0,7):\n    for j in range(0,4):\n        \n        if risk_vars:\n            num_var = risk_vars.pop()\n        \n            if num_var is not None:\n                agg_df = train_df[:, dt.cut(dt.f[num_var], nbins = NBINS)].to_pandas()\n                sns.distplot(agg_df[num_var], kde = False, color ='red', bins = NBINS, ax=ax[i,j])\n\ngc.collect()\n\nfig.show()","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"risk_vars = [s for s in train_df.names if \"R_\" in s if s not in cat_vars]\n\ncorr = frame_corr(train_df[:, risk_vars])\n\n\nf, ax = plt.subplots(figsize=(24, 24))\nmask = np.triu(np.ones_like(corr, dtype=bool))\ncmap = sns.diverging_palette(230, 20, as_cmap=True)\nsns.heatmap(corr, annot=True, mask = mask, cmap=cmap)","metadata":{"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### To be continued..","metadata":{}},{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]}]}