{"cells":[{"metadata":{"_uuid":"8c8fa27a7f0bd58a5b4db4ac0f08271149a737db"},"cell_type":"markdown","source":"Many EDA notebook has probably shown that there is a substantial amount of target that is around -33. So I wanted to investigate a little bit into this."},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true},"cell_type":"code","source":"import numpy as np # linear algebra\nimport pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)\nimport gc\nimport matplotlib.pyplot as plt\nfrom tqdm import tqdm\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":{"trusted":true,"_uuid":"3bd48afc7f2a20873339d2774657b1793bc10488"},"cell_type":"code","source":"data_train = pd.read_csv('../input/elo-merchant-category-recommendation/train.csv')\ndata_test = pd.read_csv('../input/elo-merchant-category-recommendation/test.csv')","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"data_train.target.value_counts().sort_index()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b11ba1faf9cc28ec30449c267edbe6f31fdd47ba"},"cell_type":"code","source":"data_train.first_active_month.value_counts().sort_index().plot()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"337bde944fd4729ee81f989e9cae330fe54e0d62"},"cell_type":"code","source":"data_test.first_active_month.value_counts().sort_index().plot()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f5cebccab56f4a20a9bd93b762eb97e09f7c0560"},"cell_type":"markdown","source":"From the two plots above, we can see that the distribution is very similar, this would suggest that a bigger original data set was split into the train set and the test set. Hence, it would be alrigh to assume that there will be target that are around -33. This is also consistent with the high RMSE in the leaderboard. The culprit is most likely the existence of such outliers target.\n\nHence, assuming the same proportion of -33 target in the test set as the train set, I calculated how many -33 target are we expecting in the test set."},{"metadata":{"trusted":true,"_uuid":"65ad8fc1af8d76f6271c15a783fea4189e91f2a5"},"cell_type":"code","source":"percent_low = data_train.target.value_counts(normalize=True).sort_index().iloc[0]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d9eca465f6c4087c58299a2df4c22204337a9088"},"cell_type":"code","source":"print('We expect {:,.10} of -33.219 in the test set.'.format(percent_low * len(data_test.index)))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ce951f06ec9209a34809f9375de8152b1dcdb74e"},"cell_type":"markdown","source":"I split the train set into 2: data_normal for target > -30, data_less for target < -30."},{"metadata":{"trusted":true,"_uuid":"487cc8a99e52d9b52019ce4b636524c4f9cafcb8"},"cell_type":"code","source":"data_normal = data_train.loc[data_train.target>-30]\ndata_less = data_train.loc[data_train.target<-30]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a0fadcb35511f3cd7f11e340a19c425f281d3935"},"cell_type":"code","source":"pd.concat([data_normal.first_active_month.value_counts(normalize=True).sort_index(),\n           data_less.first_active_month.value_counts(normalize=True).sort_index()],axis=1).plot()\nplt.legend(['normal','-33'])\n","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8892e38cffc7a4f636ca574f2ded1a85ff116faa"},"cell_type":"markdown","source":"We see that there is some differences in the distribution of first_active_month."},{"metadata":{"trusted":true,"_uuid":"49aab9e10a9cb08a8928506f2bfd01d8928c11da"},"cell_type":"code","source":"pd.concat([data_normal.feature_1.value_counts(normalize=True).sort_index(),\n           data_less.feature_1.value_counts(normalize=True).sort_index()],axis=1).plot(kind='bar')\nplt.legend(['normal','-33'])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"fba599e91d95f963aee0eaf930a9a068166638a2"},"cell_type":"markdown","source":"There is a higher chance of feature_1 == 5, given that the target is -33."},{"metadata":{"trusted":true,"_uuid":"63a4a34ab7ccf6b9e9fbdb03548de72452586363"},"cell_type":"code","source":"pd.concat([data_normal.feature_2.value_counts(normalize=True).sort_index(),\n           data_less.feature_2.value_counts(normalize=True).sort_index()],axis=1).plot(kind='bar')\nplt.legend(['normal','-33'])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8315b0d31f6dd4d2709c2c856b33fb66669d6b54"},"cell_type":"markdown","source":"There is a higher chance of feature_2 == 3, given that the target is -33."},{"metadata":{"trusted":true,"_uuid":"349a9ba6411886e879ba3ee2760b6ab7e802f6a5"},"cell_type":"code","source":"pd.concat([data_normal.feature_3.value_counts(normalize=True).sort_index(),\n           data_less.feature_3.value_counts(normalize=True).sort_index()],axis=1).plot(kind='bar')\nplt.legend(['normal','-33'])","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6bcd85bcb8e41dd725aaf7a28aaa747b2382c719"},"cell_type":"markdown","source":"There is a higher chance of feature_3 == 1, given that the target is -33."},{"metadata":{"trusted":true,"_uuid":"0445dd4ee84de57f57efb158c1ac3dc20d23cad4"},"cell_type":"code","source":"card_id = list(data_train.card_id.unique()) + list(data_test.card_id.unique())\nmerchant_id = list(pd.read_csv('../input/elo-merchant-category-recommendation/merchants.csv',usecols=['merchant_id']).merchant_id.unique())\ncard_id_dict = {value: key for key, value in enumerate(card_id)}\nmerchant_id_dict = {value: key for key, value in enumerate(merchant_id)}","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"19245844609eac11af1c313555a7148f4cf71d1b"},"cell_type":"code","source":"data_normal['card_id_label'] = data_normal.card_id.map(lambda x: card_id_dict[x])\ndata_less['card_id_label'] = data_less.card_id.map(lambda x: card_id_dict[x])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3279a20f3a6401755a3d4f34b41cf5de58d96f30"},"cell_type":"code","source":"data_hist = pd.read_csv('../input/cleaning-historical-txns/historical_transactions.csv')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"3158cde4d65e91508cc4ea711eeefb34dda4a68e"},"cell_type":"code","source":"# Memory saving function credit to https://www.kaggle.com/gemartin/load-data-reduce-memory-usage\ndef reduce_mem_usage(df):\n    \"\"\" iterate through all the columns of a dataframe and modify the data type\n        to reduce memory usage.        \n    \"\"\"\n    #start_mem = df.memory_usage().sum() / 1024**2\n    #print('Memory usage of dataframe is {:.2f} MB'.format(start_mem))\n\n    for col in df.columns:\n        col_type = df[col].dtype\n\n        if col_type != object:\n            c_min = df[col].min()\n            c_max = df[col].max()\n            if str(col_type)[:3] == 'int':\n                if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max:\n                    df[col] = df[col].astype(np.int8)\n                elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:\n                    df[col] = df[col].astype(np.int16)\n                elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:\n                    df[col] = df[col].astype(np.int32)\n                elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max:\n                    df[col] = df[col].astype(np.int64)  \n            else:\n                if c_min > np.finfo(np.float16).min and c_max < np.finfo(np.float16).max:\n                    df[col] = df[col].astype(np.float16)\n                elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max:\n                    df[col] = df[col].astype(np.float32)\n                else:\n                    df[col] = df[col].astype(np.float64)\n\n    #end_mem = df.memory_usage().sum() / 1024**2\n    #print('Memory usage after optimization is: {:.2f} MB'.format(end_mem))\n    #print('Decreased by {:.1f}%'.format(100 * (start_mem - end_mem) / start_mem))\n\n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"aed7b96f4ce20adafaa9c5309843a4973ff141be"},"cell_type":"code","source":"data_hist.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4f66f80591f50809e91023f372e2a93454ca68ab"},"cell_type":"code","source":"data_hist.category_2 = data_hist.category_2.astype(int)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4a2afe315dc2ca3a521afddb05669e609efb3e9d"},"cell_type":"code","source":"data_hist = reduce_mem_usage(data_hist)\ndata_hist.purchase_amount = data_hist.purchase_amount.astype(float)\ngc.collect()\ndata_hist.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"763dd3beaca433dfa531090682cc649952f5a0d8"},"cell_type":"code","source":"data_normal_hist = data_hist.loc[data_hist.card_id_label.isin(data_normal.card_id_label)]\ndata_less_hist = data_hist.loc[data_hist.card_id_label.isin(data_less.card_id_label)]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bddb43eb1207266db832f626c8a140870ab73d35"},"cell_type":"code","source":"print('''There are {:,} transactions from the normal target card,\n{:,} transactions from the -33 target card,\nremaining {:,} transactions for the test set.'''.format(\nlen(data_normal_hist), len(data_less_hist), len(data_hist)-len(data_normal_hist)-len(data_less_hist)))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f67a0b3cee230c5179bc2f4b3d5f8dcf1b7f18e0"},"cell_type":"code","source":"from scipy.stats import percentileofscore","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"29eb9eb9b9ece490de4d3add1bf20680eb57e38b"},"cell_type":"code","source":"authorised_normal = data_normal_hist.groupby('card_id_label').authorized_flag.agg(['count','mean'])\nauthorised_normal.head(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"745ebf2159e928b720eda97f6bf86728a173e286"},"cell_type":"code","source":"authorised_normal['count_less_than100'] = authorised_normal['count'].map(lambda x: 1 if x<100 else 0)\nauthorised_normal['have_unauthorised'] = authorised_normal['mean'].map(lambda x: 1 if x<1 else 0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7ff6b6ffa14a9f9433868b878f95f1a344a0876f"},"cell_type":"code","source":"authorised_less = data_less_hist.groupby('card_id_label').authorized_flag.agg(['count','mean'])\nauthorised_less['count_less_than100'] = authorised_less['count'].map(lambda x: 1 if x<100 else 0)\nauthorised_less['have_unauthorised'] = authorised_less['mean'].map(lambda x: 1 if x<1 else 0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"dc167e540a9dd70c50e3e10ac5e482cf63c2c227"},"cell_type":"code","source":"pd.concat([authorised_normal.count_less_than100.value_counts(normalize=True).sort_index(),\n           authorised_less.count_less_than100.value_counts(normalize=True).sort_index()],axis=1).plot(kind='bar')\nplt.legend(['normal','-33'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5490e3f1a16fc87db5025c62f2ebbd1b9062d989"},"cell_type":"code","source":"pd.concat([authorised_normal.have_unauthorised.value_counts(normalize=True).sort_index(),\n           authorised_less.have_unauthorised.value_counts(normalize=True).sort_index()],axis=1).plot(kind='bar')\nplt.legend(['normal','-33'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"02bf4eb6cafdfb33bc09c32f3dcfd73efa4c8688"},"cell_type":"code","source":"data_normal_hist.groupby('card_id_label').authorized_flag.agg(['count']).describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ecda209fb83ace96edd3653ea59910accafc4adf"},"cell_type":"code","source":"data_less_hist.groupby('card_id_label').authorized_flag.agg(['count']).describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1f87104e5def450cd276f9801d6f254503d9d7d2","_kg_hide-output":true},"cell_type":"code","source":"data_normal_hist.groupby('card_id_label').authorized_flag.agg(['count'])['count'].value_counts(normalize=True).sort_index().plot()\ndata_less_hist.groupby('card_id_label').authorized_flag.agg(['count'])['count'].value_counts(normalize=True).sort_index().plot()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1602e1d889c362c169b3a4a9ee2e4446b42b6648"},"cell_type":"code","source":"data_normal_hist.groupby('card_id_label').authorized_flag.agg(['mean'])['mean'].describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b72ab8325d56688ed7eb2ac4b58f2dd89835723a"},"cell_type":"code","source":"data_less_hist.groupby('card_id_label').authorized_flag.agg(['mean'])['mean'].describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"68ce4b7085ef5bcc4f6720a97aef9f390a52d2d0","_kg_hide-output":true},"cell_type":"code","source":"data_normal_hist.groupby('card_id_label').authorized_flag.agg(['mean'])['mean'].value_counts(normalize=True).sort_index().plot()\ndata_less_hist.groupby('card_id_label').authorized_flag.agg(['mean'])['mean'].value_counts(normalize=True).sort_index().plot()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a2662791734d02ece0c93d8d91edbec911fcd648"},"cell_type":"code","source":"def get_agg_stats(columns,func):\n    normal = data_normal_hist.groupby('card_id_label')[columns].agg(func)\n    \n    less = data_less_hist.groupby('card_id_label')[columns].agg(func)\n    return normal, less","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"405e7606bbb69c710edb9cbb729c74a26996a4e3"},"cell_type":"code","source":"cat1_normal, cat1_less = get_agg_stats('category_1',['mean'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"765d47f4152c8922d1fa0d5fb77f8d4440586386"},"cell_type":"code","source":"def add_new_columns(normal, less, new_column_name, column_name, func):\n    normal[new_column_name] = normal[column_name].map(func)\n    less[new_column_name] = less[column_name].map(func)\n    return normal, less","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8bfd283f8176a250313308de9aaabc44dc46f37d"},"cell_type":"code","source":"cat1_normal, cat1_less = add_new_columns(cat1_normal, cat1_less, 'mixed', 'mean', lambda x: 1 if x>0 and x<1 else 0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d5ccc4ce51ee18cde94f8040aecd7789c402b065"},"cell_type":"code","source":"pd.concat([cat1_normal['mixed'].value_counts(normalize=True).sort_index(),\n           cat1_less['mixed'].value_counts(normalize=True).sort_index()],axis=1).plot(kind='bar')\nplt.legend(['normal','-33'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"193030dbb1b40cd6a0ffaffefeb1fac6cdae290b"},"cell_type":"code","source":"instal_normal, instal_less = get_agg_stats('installments',['mean'])\ninstal_normal, instal_less = add_new_columns(instal_normal, instal_less, 'use_instal', 'mean', lambda x: 1 if x>0 else 0)\npd.concat([instal_normal['use_instal'].value_counts(normalize=True).sort_index(),\n           instal_less['use_instal'].value_counts(normalize=True).sort_index()],axis=1).plot(kind='bar')\nplt.legend(['normal','-33'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5e7a15e451737e1ac989e72673264ba0907e137e"},"cell_type":"code","source":"cat2_normal, cat2_less = get_agg_stats('category_2',['nunique'])\ncat2_normal, cat2_less = add_new_columns(cat2_normal, cat2_less, 'more_than_1', 'nunique', lambda x: 1 if x>1 else 0)\npd.concat([cat2_normal['more_than_1'].value_counts(normalize=True).sort_index(),\n           cat2_less['more_than_1'].value_counts(normalize=True).sort_index()],axis=1).plot(kind='bar')\nplt.legend(['normal','-33'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"aa8f338d54cb10067ae8d4d847666ad3feebf832"},"cell_type":"code","source":"cat3_normal, cat3_less = get_agg_stats('category_3',['nunique'])\ncat3_normal, cat3_less = add_new_columns(cat3_normal, cat3_less, 'more_than_1', 'nunique', lambda x: 1 if x>1 else 0)\npd.concat([cat3_normal['more_than_1'].value_counts(normalize=True).sort_index(),\n           cat3_less['more_than_1'].value_counts(normalize=True).sort_index()],axis=1).plot(kind='bar')\nplt.legend(['normal','-33'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3e611b16c4f2bff2051c129badff69d2989273a2"},"cell_type":"code","source":"merc_cat_normal, merc_cat_less = get_agg_stats('merchant_category_id',['nunique'])\nmerc_cat_normal, merc_cat_less = add_new_columns(merc_cat_normal, merc_cat_less, 'more_than_25', 'nunique', lambda x: 1 if x>25 else 0)\npd.concat([merc_cat_normal['more_than_25'].value_counts(normalize=True).sort_index(),\n           merc_cat_less['more_than_25'].value_counts(normalize=True).sort_index()],axis=1).plot(kind='bar')\nplt.legend(['normal','-33'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"922e68161defe41eb1598a60398e35e54e5d65d4"},"cell_type":"code","source":"month_lag_normal, month_lag_less = get_agg_stats('month_lag',['median'])\nmonth_lag_normal, month_lag_less = add_new_columns(month_lag_normal, month_lag_less, 'more_than_-3', 'median', lambda x: 1 if x>-3 else 0)\npd.concat([month_lag_normal['more_than_-3'].value_counts(normalize=True).sort_index(),\n           month_lag_less['more_than_-3'].value_counts(normalize=True).sort_index()],axis=1).plot(kind='bar')\nplt.legend(['normal','-33'])","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true,"_uuid":"69f69d37a4beb738f384c7a260731fe19376014f"},"cell_type":"code","source":"data_less_hist.columns","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8bc5bbc294be04e420e88a2e341e275f0c9b36b4"},"cell_type":"code","source":"purchase_amount_normal = data_normal_hist.groupby('card_id_label').purchase_amount.agg(['sum','mean','median'])\npurchase_amount_normal['sum_positive'] = purchase_amount_normal['sum'].map(lambda x: 1 if x>0 else 0)\npurchase_amount_normal['mean_positive'] = purchase_amount_normal['mean'].map(lambda x: 1 if x>0 else 0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ea73667176dc231211f556113bd7f5d4d9e4093d"},"cell_type":"code","source":"purchase_amount_less = data_less_hist.groupby('card_id_label').purchase_amount.agg(['sum','mean','median'])\npurchase_amount_less['sum_positive'] = purchase_amount_less['sum'].map(lambda x: 1 if x>0 else 0)\npurchase_amount_less['mean_positive'] = purchase_amount_less['mean'].map(lambda x: 1 if x>0 else 0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"832003d5aa2f67d16d50c8a6c3b28881ee24b9ad"},"cell_type":"code","source":"pd.concat([purchase_amount_normal.sum_positive.value_counts(normalize=True).sort_index(),\n           purchase_amount_less.sum_positive.value_counts(normalize=True).sort_index()],axis=1).plot(kind='bar')\nplt.legend(['normal','-33'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"33e9538ec78f4623f581458e1cc84e68e3613ca0"},"cell_type":"code","source":"pd.concat([purchase_amount_normal.mean_positive.value_counts(normalize=True).sort_index(),\n           purchase_amount_less.mean_positive.value_counts(normalize=True).sort_index()],axis=1).plot(kind='bar')\nplt.legend(['normal','-33'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8132f1d9409d2dbea67382d3b1ed046f8f7050f6"},"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}