{"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":"407b94547d947df9935d1f91bea1be9ec4795230"},"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,hist_trans_df2,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":"a56e106c038baf09514fbc3e9359a03661b0686d"},"cell_type":"code","source":"train_df_merged = pd.merge(trans_df, train_df, how = 'inner', left_on = ['card_id'], right_on=['card_id'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"438f6b5898c1d22d2646dd56f94221882f07ebca"},"cell_type":"code","source":"train_df_merged['target'].hist(bins=50)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a8e81d2d211d8233e0fcf3b52e948cd81a7a3aa6"},"cell_type":"code","source":"print(train_df_merged['target'].max())\nprint(train_df_merged['target'].min())\nprint(train_df_merged['target'].median())","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":"47c3aa14f78962f9eb7aabbd6f13f4fab58e1d7f"},"cell_type":"code","source":"print(len(set(train_df_merged['card_id'].unique()) & set(hist_trans_df1['card_id'].unique())))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ef659a559637bb48a589a09fce086d84f4abba8f"},"cell_type":"code","source":"print(len(set(hist_trans_df1['card_id'].unique()) & set(test_df['card_id'].unique())))","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_category_id']).size().reset_index()\n    Trans_by_MerchantCat = Trans_by_MerchantCat.rename(columns={0: 'TransCtbyMerchCat'})\n    print(\"Max of Count of Trans by Merch Cat ID:\"+str(Trans_by_MerchantCat['TransCtbyMerchCat'].max().round(2)))\n    print(\"Median of Count of Trans by Merch Cat ID:\"+str(round(Trans_by_MerchantCat['TransCtbyMerchCat'].median(),2)))\n    print(\"Mean of Count of Trans by Merch Cat ID:\"+str(round(Trans_by_MerchantCat['TransCtbyMerchCat'].mean(),2)))\n    print(\"Min of Count of Trans by Merch Cat ID:\"+str(round(Trans_by_MerchantCat['TransCtbyMerchCat'].min(),2)))\n    data = Trans_by_MerchantCat['TransCtbyMerchCat']\n    percentiles = percentile(data,[50,75,90,95,98,99,99.5])\n    print(percentiles.round(2))\n    list_MerchCat_P995 = Trans_by_MerchantCat['merchant_category_id'].loc[(Trans_by_MerchantCat.TransCtbyMerchCat>percentiles[6])]\n    list_MerchCat_P99 = Trans_by_MerchantCat['merchant_category_id'].loc[(Trans_by_MerchantCat.TransCtbyMerchCat>percentiles[5]) & (Trans_by_MerchantCat.TransCtbyMerchCat<=percentiles[6])]\n    list_MerchCat_P98 = Trans_by_MerchantCat['merchant_category_id'].loc[(Trans_by_MerchantCat.TransCtbyMerchCat>percentiles[4]) & (Trans_by_MerchantCat.TransCtbyMerchCat<=percentiles[5])]\n    list_MerchCat_P95 = Trans_by_MerchantCat['merchant_category_id'].loc[(Trans_by_MerchantCat.TransCtbyMerchCat>percentiles[3]) & (Trans_by_MerchantCat.TransCtbyMerchCat<=percentiles[4])]\n    list_MerchCat_P90 = Trans_by_MerchantCat['merchant_category_id'].loc[(Trans_by_MerchantCat.TransCtbyMerchCat>percentiles[2]) & (Trans_by_MerchantCat.TransCtbyMerchCat<=percentiles[3])]\n    list_MerchCat_P75 = Trans_by_MerchantCat['merchant_category_id'].loc[(Trans_by_MerchantCat.TransCtbyMerchCat>percentiles[1]) & (Trans_by_MerchantCat.TransCtbyMerchCat<=percentiles[2])]\n    list_MerchCat_P50 = Trans_by_MerchantCat['merchant_category_id'].loc[(Trans_by_MerchantCat.TransCtbyMerchCat>percentiles[0]) & (Trans_by_MerchantCat.TransCtbyMerchCat<=percentiles[1])]\n    list_MerchCat_Plt50 = Trans_by_MerchantCat['merchant_category_id'].loc[(Trans_by_MerchantCat.TransCtbyMerchCat<=percentiles[0])]\n    print(\"P99.5 Merch Cat:\")\n    print(len(list_MerchCat_P995))\n    trans_by_merch_cat=train_df_red.groupby(['card_id','merchant_category_id']).size().reset_index()\n    amt_by_merch_cat=train_df_red.groupby(['card_id','merchant_category_id'])['purchase_amount'].sum().reset_index()\n    \n    trans_by_merch_cat = trans_by_merch_cat.rename(columns={0: 'TransCtbyMerchCat'})\n    amt_by_merch_cat = amt_by_merch_cat.rename(columns={'purchase_amount': 'AmtbyMerchCat'})\n    \n    trans_by_merch_cat_P995 = trans_by_merch_cat.loc[(trans_by_merch_cat.merchant_category_id.isin(list_MerchCat_P995))]\n    trans_by_merch_cat_P99 = trans_by_merch_cat.loc[(trans_by_merch_cat.merchant_category_id.isin(list_MerchCat_P99))]\n    trans_by_merch_cat_P98 = trans_by_merch_cat.loc[(trans_by_merch_cat.merchant_category_id.isin(list_MerchCat_P98))]\n    trans_by_merch_cat_P95 = trans_by_merch_cat.loc[(trans_by_merch_cat.merchant_category_id.isin(list_MerchCat_P95))]\n    trans_by_merch_cat_P90 = trans_by_merch_cat.loc[(trans_by_merch_cat.merchant_category_id.isin(list_MerchCat_P90))]\n    trans_by_merch_cat_P75 = trans_by_merch_cat.loc[(trans_by_merch_cat.merchant_category_id.isin(list_MerchCat_P75))]\n    trans_by_merch_cat_P50 = trans_by_merch_cat.loc[(trans_by_merch_cat.merchant_category_id.isin(list_MerchCat_P50))]\n    trans_by_merch_cat_Plt50 = trans_by_merch_cat.loc[(trans_by_merch_cat.merchant_category_id.isin(list_MerchCat_Plt50))]\n    \n    amt_by_merch_cat_P995 = amt_by_merch_cat.loc[(amt_by_merch_cat.merchant_category_id.isin(list_MerchCat_P995))]\n    amt_by_merch_cat_P99 = amt_by_merch_cat.loc[(amt_by_merch_cat.merchant_category_id.isin(list_MerchCat_P99))]\n    amt_by_merch_cat_P98 = amt_by_merch_cat.loc[(amt_by_merch_cat.merchant_category_id.isin(list_MerchCat_P98))]\n    amt_by_merch_cat_P95 = amt_by_merch_cat.loc[(amt_by_merch_cat.merchant_category_id.isin(list_MerchCat_P95))]\n    amt_by_merch_cat_P90 = amt_by_merch_cat.loc[(amt_by_merch_cat.merchant_category_id.isin(list_MerchCat_P90))]\n    amt_by_merch_cat_P75 = amt_by_merch_cat.loc[(amt_by_merch_cat.merchant_category_id.isin(list_MerchCat_P75))]\n    amt_by_merch_cat_P50 = amt_by_merch_cat.loc[(amt_by_merch_cat.merchant_category_id.isin(list_MerchCat_P50))]\n    amt_by_merch_cat_Plt50 = amt_by_merch_cat.loc[(amt_by_merch_cat.merchant_category_id.isin(list_MerchCat_Plt50))]\n    \n    trans_by_merch_cat_P995_card = trans_by_merch_cat_P995.groupby(['card_id'])['TransCtbyMerchCat'].sum().reset_index()\n    trans_by_merch_cat_P99_card = trans_by_merch_cat_P99.groupby(['card_id'])['TransCtbyMerchCat'].sum().reset_index()\n    trans_by_merch_cat_P98_card = trans_by_merch_cat_P98.groupby(['card_id'])['TransCtbyMerchCat'].sum().reset_index()\n    trans_by_merch_cat_P95_card = trans_by_merch_cat_P95.groupby(['card_id'])['TransCtbyMerchCat'].sum().reset_index()\n    trans_by_merch_cat_P90_card = trans_by_merch_cat_P90.groupby(['card_id'])['TransCtbyMerchCat'].sum().reset_index()\n    trans_by_merch_cat_P75_card = trans_by_merch_cat_P75.groupby(['card_id'])['TransCtbyMerchCat'].sum().reset_index()\n    trans_by_merch_cat_P50_card = trans_by_merch_cat_P50.groupby(['card_id'])['TransCtbyMerchCat'].sum().reset_index()\n    trans_by_merch_cat_Plt50_card = trans_by_merch_cat_Plt50.groupby(['card_id'])['TransCtbyMerchCat'].sum().reset_index()\n    \n    amt_by_merch_cat_P995_card = amt_by_merch_cat_P995.groupby(['card_id'])['AmtbyMerchCat'].sum().reset_index()\n    amt_by_merch_cat_P99_card = amt_by_merch_cat_P99.groupby(['card_id'])['AmtbyMerchCat'].sum().reset_index()\n    amt_by_merch_cat_P98_card = amt_by_merch_cat_P98.groupby(['card_id'])['AmtbyMerchCat'].sum().reset_index()\n    amt_by_merch_cat_P95_card = amt_by_merch_cat_P95.groupby(['card_id'])['AmtbyMerchCat'].sum().reset_index()\n    amt_by_merch_cat_P90_card = amt_by_merch_cat_P90.groupby(['card_id'])['AmtbyMerchCat'].sum().reset_index()\n    amt_by_merch_cat_P75_card = amt_by_merch_cat_P75.groupby(['card_id'])['AmtbyMerchCat'].sum().reset_index()\n    amt_by_merch_cat_P50_card = amt_by_merch_cat_P50.groupby(['card_id'])['AmtbyMerchCat'].sum().reset_index()\n    amt_by_merch_cat_Plt50_card = amt_by_merch_cat_Plt50.groupby(['card_id'])['AmtbyMerchCat'].sum().reset_index()\n    \n    trans_by_merch_cat_P995_card = trans_by_merch_cat_P995_card.rename(columns={'TransCtbyMerchCat': 'TransCtbyMerchCatP99.5'})\n    trans_by_merch_cat_P99_card = trans_by_merch_cat_P99_card.rename(columns={'TransCtbyMerchCat': 'TransCtbyMerchCatP99'})\n    trans_by_merch_cat_P98_card = trans_by_merch_cat_P98_card.rename(columns={'TransCtbyMerchCat': 'TransCtbyMerchCatP98'})\n    trans_by_merch_cat_P95_card = trans_by_merch_cat_P95_card.rename(columns={'TransCtbyMerchCat': 'TransCtbyMerchCatP95'})\n    trans_by_merch_cat_P90_card = trans_by_merch_cat_P90_card.rename(columns={'TransCtbyMerchCat': 'TransCtbyMerchCatP90'})\n    trans_by_merch_cat_P75_card = trans_by_merch_cat_P75_card.rename(columns={'TransCtbyMerchCat': 'TransCtbyMerchCatP75'})\n    trans_by_merch_cat_P50_card = trans_by_merch_cat_P50_card.rename(columns={'TransCtbyMerchCat': 'TransCtbyMerchCatP50'})\n    trans_by_merch_cat_Plt50_card = trans_by_merch_cat_Plt50_card.rename(columns={'TransCtbyMerchCat': 'TransCtbyMerchCatPlt50'})\n    \n    amt_by_merch_cat_P995_card = amt_by_merch_cat_P995_card.rename(columns={'AmtbyMerchCat': 'AmtbyMerchCatP99.5'})\n    amt_by_merch_cat_P99_card = amt_by_merch_cat_P99_card.rename(columns={'AmtbyMerchCat': 'AmtbyMerchCatP99'})\n    amt_by_merch_cat_P98_card = amt_by_merch_cat_P98_card.rename(columns={'AmtbyMerchCat': 'AmtbyMerchCatP98'})\n    amt_by_merch_cat_P95_card = amt_by_merch_cat_P95_card.rename(columns={'AmtbyMerchCat': 'AmtbyMerchCatP95'})\n    amt_by_merch_cat_P90_card = amt_by_merch_cat_P90_card.rename(columns={'AmtbyMerchCat': 'AmtbyMerchCatP90'})\n    amt_by_merch_cat_P75_card = amt_by_merch_cat_P75_card.rename(columns={'AmtbyMerchCat': 'AmtbyMerchCatP75'})\n    amt_by_merch_cat_P50_card = amt_by_merch_cat_P50_card.rename(columns={'AmtbyMerchCat': 'AmtbyMerchCatP50'})\n    amt_by_merch_cat_Plt50_card = amt_by_merch_cat_Plt50_card.rename(columns={'AmtbyMerchCat': 'AmtbyMerchCatPlt50'})\n    print(len(trans_by_merch_cat_P99_card))\n    print(len(trans_by_merch_cat_P995_card))\n    train_df_merged = train_df.merge(trans_by_merch_cat_P995_card, how='left', on = ['card_id'])\n    train_df_merged = train_df_merged.merge(trans_by_merch_cat_P99_card, how='left', on = ['card_id'])\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    \n    train_df_merged = train_df_merged.merge(amt_by_merch_cat_P995_card, how='left', on = ['card_id'])\n    train_df_merged = train_df_merged.merge(amt_by_merch_cat_P99_card, how='left', on = ['card_id'])\n    train_df_merged = train_df_merged.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    train_df_merged.fillna(0, inplace=True)\n    print(\" df columns:\")\n    print(train_df_merged.columns)\n    #print(\"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_MerchCat_lt2.csv')\ntrain_df_merged = prepare_stratifiedByMerchCat(train_df_gt2,train_df)\ntrain_df_merged.to_csv('train_df_strat_MerchCat_gt2.csv')\ntrain_df_merged = prepare_stratifiedByMerchCat(train_df_0_2,train_df)\ntrain_df_merged.to_csv('train_df_strat_MerchCat_0_2.csv')\ntrain_df_merged = prepare_stratifiedByMerchCat(train_df_2_0,train_df)\ntrain_df_merged.to_csv('train_df_strat_MerchCat_2_0.csv')\ntrain_df_merged = prepare_stratifiedByMerchCat(train_df_lt15,train_df)\ntrain_df_merged.to_csv('train_df_strat_MerchCat_lt15.csv')\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9eeb85e1b25fdac6da54d51f1191ef0b3948078c"},"cell_type":"code","source":"test_df_merged = prepare_stratifiedByMerchCat(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_MerchCat.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}