{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":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)\n\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\n\nimport os\nfrom numpy import percentile\nprint(os.listdir(\"../input\"))\n\n# Any results you write to the current directory are saved as output.","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"hist_trans_df1 = pd.read_csv('../input/elo-cards-basic-eda-exposes-data-skewness/hist_DecFeb18.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d41f49e439a57b5b050b6b26bb7230d79faa22e1"},"cell_type":"code","source":"#hist_trans_df2 = pd.read_csv('../input/elo-cards-basic-eda-exposes-data-skewness/hist_SepNov.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"adce346fc86cc5b6c9927f2380bc175ce7d04342"},"cell_type":"code","source":"new_trans_df=pd.read_csv('../input/elo-merchant-category-recommendation/new_merchant_transactions.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b18ad3bff42177b8f32f17eeafabda8c6828c186"},"cell_type":"code","source":"trans_df = pd.concat([hist_trans_df1,new_trans_df])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f76daaea69d0049013e85db5f12c3b22c5fb4dbe"},"cell_type":"code","source":"train_df = pd.read_csv('../input/elo-merchant-category-recommendation/train.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"19282c661e478a72430808d50ea35f18f681dd02"},"cell_type":"code","source":"test_df = pd.read_csv('../input/elo-merchant-category-recommendation/test.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0821b39206f06552708180579840abaf11448dd8"},"cell_type":"code","source":"merchant_df = pd.read_csv('../input/elo-merchant-category-recommendation/merchants.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fdd7b623d713489fe27c8ec2e39299e14b3c6e14"},"cell_type":"code","source":"merch_trans_df = pd.merge(merchant_df, trans_df, how = 'inner', left_on = ['merchant_id'], right_on=['merchant_id'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a56e106c038baf09514fbc3e9359a03661b0686d"},"cell_type":"code","source":"train_df_merged = pd.merge(merch_trans_df[['card_id','merchant_group_id','purchase_amount']], train_df, how = 'inner', left_on = ['card_id'], right_on=['card_id'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ba8a52fa23292f8b30723802eee580d37eb34d82"},"cell_type":"code","source":"train_df_lt2 = train_df_merged.loc[(train_df_merged.target >= -15) & (train_df_merged.target <= -1)]\ntrain_df_gt2 = train_df_merged.loc[(train_df_merged.target >= 1)]\ntrain_df_0_2 = train_df_merged.loc[(train_df_merged.target > 0) & (train_df_merged.target < 1)]\ntrain_df_2_0 = train_df_merged.loc[(train_df_merged.target > -1) & (train_df_merged.target <= 0)]\ntrain_df_lt15 = train_df_merged.loc[(train_df_merged.target < -15)]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5160c46fb8c57f11d72209299cf771cff95c19c8"},"cell_type":"code","source":"train_df_lt2.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3ed155febeef5f463af4e1d93a2611f63473a035"},"cell_type":"code","source":"def prepare_stratifiedByMerchCat(train_df_red,train_df):\n    Trans_by_MerchantCat = train_df_red.groupby(['merchant_group_id']).size().reset_index()\n    Trans_by_MerchantCat = Trans_by_MerchantCat.rename(columns={0: 'TransCtbyMerchCat'})\n    print(\"Max of Count of Trans by Merch Grp ID:\"+str(Trans_by_MerchantCat['TransCtbyMerchCat'].max().round(2)))\n    print(\"Median of Count of Trans by Merch Grp ID:\"+str(round(Trans_by_MerchantCat['TransCtbyMerchCat'].median(),2)))\n    print(\"Mean of Count of Trans by Merch Grp ID:\"+str(round(Trans_by_MerchantCat['TransCtbyMerchCat'].mean(),2)))\n    print(\"Min of Count of Trans by Merch Grp ID:\"+str(round(Trans_by_MerchantCat['TransCtbyMerchCat'].min(),2)))\n    data = Trans_by_MerchantCat['TransCtbyMerchCat']\n    percentiles = percentile(data,[50,75,90,95,99])\n    print(percentiles.round(2))\n    list_MerchCat_P98 = Trans_by_MerchantCat['merchant_group_id'].loc[(Trans_by_MerchantCat.TransCtbyMerchCat>percentiles[4])]\n    list_MerchCat_P95 = Trans_by_MerchantCat['merchant_group_id'].loc[(Trans_by_MerchantCat.TransCtbyMerchCat>percentiles[3]) & (Trans_by_MerchantCat.TransCtbyMerchCat<=percentiles[4])]\n    list_MerchCat_P90 = Trans_by_MerchantCat['merchant_group_id'].loc[(Trans_by_MerchantCat.TransCtbyMerchCat>percentiles[2]) & (Trans_by_MerchantCat.TransCtbyMerchCat<=percentiles[3])]\n    list_MerchCat_P75 = Trans_by_MerchantCat['merchant_group_id'].loc[(Trans_by_MerchantCat.TransCtbyMerchCat>percentiles[1]) & (Trans_by_MerchantCat.TransCtbyMerchCat<=percentiles[2])]\n    list_MerchCat_P50 = Trans_by_MerchantCat['merchant_group_id'].loc[(Trans_by_MerchantCat.TransCtbyMerchCat>percentiles[0]) & (Trans_by_MerchantCat.TransCtbyMerchCat<=percentiles[1])]\n    list_MerchCat_Plt50 = Trans_by_MerchantCat['merchant_group_id'].loc[(Trans_by_MerchantCat.TransCtbyMerchCat<=percentiles[0])]\n    print(\"P98 Merch Grp:\")\n    print(len(list_MerchCat_P98))\n    trans_by_merch_cat=train_df_red.groupby(['card_id','merchant_group_id']).size().reset_index()\n    amt_by_merch_cat=train_df_red.groupby(['card_id','merchant_group_id'])['purchase_amount'].sum().reset_index()\n    \n    trans_by_merch_cat = trans_by_merch_cat.rename(columns={0: 'TransCtbyMerchGrp'})\n    amt_by_merch_cat = amt_by_merch_cat.rename(columns={'purchase_amount': 'AmtbyMerchGrp'})\n    \n    trans_by_merch_cat_P98 = trans_by_merch_cat.loc[(trans_by_merch_cat.merchant_group_id.isin(list_MerchCat_P98))]\n    trans_by_merch_cat_P95 = trans_by_merch_cat.loc[(trans_by_merch_cat.merchant_group_id.isin(list_MerchCat_P95))]\n    trans_by_merch_cat_P90 = trans_by_merch_cat.loc[(trans_by_merch_cat.merchant_group_id.isin(list_MerchCat_P90))]\n    trans_by_merch_cat_P75 = trans_by_merch_cat.loc[(trans_by_merch_cat.merchant_group_id.isin(list_MerchCat_P75))]\n    trans_by_merch_cat_P50 = trans_by_merch_cat.loc[(trans_by_merch_cat.merchant_group_id.isin(list_MerchCat_P50))]\n    trans_by_merch_cat_Plt50 = trans_by_merch_cat.loc[(trans_by_merch_cat.merchant_group_id.isin(list_MerchCat_Plt50))]\n    \n    amt_by_merch_cat_P98 = amt_by_merch_cat.loc[(amt_by_merch_cat.merchant_group_id.isin(list_MerchCat_P98))]\n    amt_by_merch_cat_P95 = amt_by_merch_cat.loc[(amt_by_merch_cat.merchant_group_id.isin(list_MerchCat_P95))]\n    amt_by_merch_cat_P90 = amt_by_merch_cat.loc[(amt_by_merch_cat.merchant_group_id.isin(list_MerchCat_P90))]\n    amt_by_merch_cat_P75 = amt_by_merch_cat.loc[(amt_by_merch_cat.merchant_group_id.isin(list_MerchCat_P75))]\n    amt_by_merch_cat_P50 = amt_by_merch_cat.loc[(amt_by_merch_cat.merchant_group_id.isin(list_MerchCat_P50))]\n    amt_by_merch_cat_Plt50 = amt_by_merch_cat.loc[(amt_by_merch_cat.merchant_group_id.isin(list_MerchCat_Plt50))]\n    \n    trans_by_merch_cat_P98_card = trans_by_merch_cat_P98.groupby(['card_id'])['TransCtbyMerchGrp'].sum().reset_index()\n    trans_by_merch_cat_P95_card = trans_by_merch_cat_P95.groupby(['card_id'])['TransCtbyMerchGrp'].sum().reset_index()\n    trans_by_merch_cat_P90_card = trans_by_merch_cat_P90.groupby(['card_id'])['TransCtbyMerchGrp'].sum().reset_index()\n    trans_by_merch_cat_P75_card = trans_by_merch_cat_P75.groupby(['card_id'])['TransCtbyMerchGrp'].sum().reset_index()\n    trans_by_merch_cat_P50_card = trans_by_merch_cat_P50.groupby(['card_id'])['TransCtbyMerchGrp'].sum().reset_index()\n    trans_by_merch_cat_Plt50_card = trans_by_merch_cat_Plt50.groupby(['card_id'])['TransCtbyMerchGrp'].sum().reset_index()\n    \n    amt_by_merch_cat_P98_card = amt_by_merch_cat_P98.groupby(['card_id'])['AmtbyMerchGrp'].sum().reset_index()\n    amt_by_merch_cat_P95_card = amt_by_merch_cat_P95.groupby(['card_id'])['AmtbyMerchGrp'].sum().reset_index()\n    amt_by_merch_cat_P90_card = amt_by_merch_cat_P90.groupby(['card_id'])['AmtbyMerchGrp'].sum().reset_index()\n    amt_by_merch_cat_P75_card = amt_by_merch_cat_P75.groupby(['card_id'])['AmtbyMerchGrp'].sum().reset_index()\n    amt_by_merch_cat_P50_card = amt_by_merch_cat_P50.groupby(['card_id'])['AmtbyMerchGrp'].sum().reset_index()\n    amt_by_merch_cat_Plt50_card = amt_by_merch_cat_Plt50.groupby(['card_id'])['AmtbyMerchGrp'].sum().reset_index()\n    \n    trans_by_merch_cat_P98_card = trans_by_merch_cat_P98_card.rename(columns={'TransCtbyMerchGrp': 'TransCtbyMerchGrpP98'})\n    trans_by_merch_cat_P95_card = trans_by_merch_cat_P95_card.rename(columns={'TransCtbyMerchGrp': 'TransCtbyMerchGrpP95'})\n    trans_by_merch_cat_P90_card = trans_by_merch_cat_P90_card.rename(columns={'TransCtbyMerchGrp': 'TransCtbyMerchGrpP90'})\n    trans_by_merch_cat_P75_card = trans_by_merch_cat_P75_card.rename(columns={'TransCtbyMerchGrp': 'TransCtbyMerchGrpP75'})\n    trans_by_merch_cat_P50_card = trans_by_merch_cat_P50_card.rename(columns={'TransCtbyMerchGrp': 'TransCtbyMerchGrpP50'})\n    trans_by_merch_cat_Plt50_card = trans_by_merch_cat_Plt50_card.rename(columns={'TransCtbyMerchGrp': 'TransCtbyMerchGrpPlt50'})\n    \n    amt_by_merch_cat_P98_card = amt_by_merch_cat_P98_card.rename(columns={'AmtbyMerchGrp': 'AmtbyMerchGrpP98'})\n    amt_by_merch_cat_P95_card = amt_by_merch_cat_P95_card.rename(columns={'AmtbyMerchGrp': 'AmtbyMerchGrpP95'})\n    amt_by_merch_cat_P90_card = amt_by_merch_cat_P90_card.rename(columns={'AmtbyMerchGrp': 'AmtbyMerchGrpP90'})\n    amt_by_merch_cat_P75_card = amt_by_merch_cat_P75_card.rename(columns={'AmtbyMerchGrp': 'AmtbyMerchGrpP75'})\n    amt_by_merch_cat_P50_card = amt_by_merch_cat_P50_card.rename(columns={'AmtbyMerchGrp': 'AmtbyMerchGrpP50'})\n    amt_by_merch_cat_Plt50_card = amt_by_merch_cat_Plt50_card.rename(columns={'AmtbyMerchGrp': 'AmtbyMerchGrpPlt50'})\n    print(len(amt_by_merch_cat_P98_card))\n  \n    train_df_merged = train_df.merge(amt_by_merch_cat_P98_card, how='left', on = ['card_id'])\n    train_df_merged = train_df_merged.merge(amt_by_merch_cat_P95_card, how='left', on = ['card_id'])\n    train_df_merged = train_df_merged.merge(amt_by_merch_cat_P90_card, how='left', on = ['card_id'])\n    train_df_merged = train_df_merged.merge(amt_by_merch_cat_P75_card, how='left', on = ['card_id'])\n    train_df_merged = train_df_merged.merge(amt_by_merch_cat_P50_card, how='left', on = ['card_id'])\n    train_df_merged = train_df_merged.merge(amt_by_merch_cat_Plt50_card, how='left', on = ['card_id'])\n    \n    train_df_merged = train_df_merged.merge(trans_by_merch_cat_P98_card, how='left', on = ['card_id'])\n    train_df_merged = train_df_merged.merge(trans_by_merch_cat_P95_card, how='left', on = ['card_id'])\n    train_df_merged = train_df_merged.merge(trans_by_merch_cat_P90_card, how='left', on = ['card_id'])\n    train_df_merged = train_df_merged.merge(trans_by_merch_cat_P75_card, how='left', on = ['card_id'])\n    train_df_merged = train_df_merged.merge(trans_by_merch_cat_P50_card, how='left', on = ['card_id'])\n    train_df_merged = train_df_merged.merge(trans_by_merch_cat_Plt50_card, how='left', on = ['card_id'])\n    train_df_merged.fillna(0, inplace=True)\n    print(\"df columns:\")\n    print(train_df_merged.columns)\n    #print(\"Train df target range\")\n    #print(train_df_red['target'].max())\n    #print(train_df_red['target'].min())\n    print(\"df length\")\n    print(len(train_df_merged))\n  \n    return train_df_merged","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"233e4e42d08af8aa670e90fa9e368f901fe4efa8"},"cell_type":"code","source":"train_df_merged = prepare_stratifiedByMerchCat(train_df_lt2,train_df)\ntrain_df_merged.to_csv('train_df_strat_MerchGrp_lt2.csv')\ntrain_df_merged = prepare_stratifiedByMerchCat(train_df_gt2,train_df)\ntrain_df_merged.to_csv('train_df_strat_MerchGrp_gt2.csv')\ntrain_df_merged = prepare_stratifiedByMerchCat(train_df_0_2,train_df)\ntrain_df_merged.to_csv('train_df_strat_MerchGrp_0_2.csv')\ntrain_df_merged = prepare_stratifiedByMerchCat(train_df_2_0,train_df)\ntrain_df_merged.to_csv('train_df_strat_MerchGrp_2_0.csv')\ntrain_df_merged = prepare_stratifiedByMerchCat(train_df_lt15,train_df)\ntrain_df_merged.to_csv('train_df_strat_MerchGrp_lt15.csv')\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9eeb85e1b25fdac6da54d51f1191ef0b3948078c"},"cell_type":"code","source":"test_df_merged = prepare_stratifiedByMerchCat(merch_trans_df,test_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8033b9e42d6df57a3081ce0a4a56d7214ef5039a"},"cell_type":"code","source":"test_df_merged.to_csv('test_df_strat_MerchGrp.csv')","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}