{"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":"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":"a6277d67d36069f62e85e559eb3cc988c6e51656"},"cell_type":"code","source":"train_df['first_active_month'] = pd.to_datetime(train_df['first_active_month'])","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":"6220260e04eb144823980d87de1cd6f131d98623"},"cell_type":"code","source":"test_df['first_active_month'] = pd.to_datetime(test_df['first_active_month'])","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":"68c99df9e2947c535773b931e4588981f366bbec"},"cell_type":"code","source":"test_df_merged = pd.merge(trans_df, test_df, how = 'inner', left_on = ['card_id'], right_on=['card_id'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e45d49c69ab4ae49b90c0d78050fc29c70b6f1fd"},"cell_type":"code","source":"merchant_df = pd.read_csv('../input/elo-merchant-category-recommendation/merchants.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d58ebf9b03af5418e2087daa48b8d9bacab1c93d"},"cell_type":"code","source":"merchant_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"06338eb691a285b0cb1b8fff87b7fe6da7b9f542"},"cell_type":"code","source":"train_df_merch = pd.merge(train_df_merged, merchant_df, how = 'inner', left_on = ['merchant_id'], right_on=['merchant_id'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"40b784b664dcf33e224251e01acc7c34ad32e883"},"cell_type":"code","source":"test_df_merch = pd.merge(test_df_merged, merchant_df, how = 'inner', left_on = ['merchant_id'], right_on=['merchant_id'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e2c3904437235ff4401ca1f83cf87ad40e437878"},"cell_type":"code","source":"train_df_merch.fillna(0,inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cd577ed7c06c57848d6d8a719199ebcd05b0a8e8"},"cell_type":"code","source":"train_df_merged['purchase_date']=pd.to_datetime(train_df_merged['purchase_date'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"57a57e66674d30b3c8405f479e24ee40ab2e0c0b"},"cell_type":"code","source":"test_df_merged['purchase_date']=pd.to_datetime(test_df_merged['purchase_date'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"75e064e47a6faf4f759ecef3e75f8fd2981e4acb"},"cell_type":"code","source":"def gen_features(df,inp_df):\n    month_lag_bycard = df.groupby(['card_id'])['month_lag'].sum().reset_index()\n    purch_amt_bycard = df.groupby(['card_id'])['purchase_amount'].sum().reset_index()\n    max_purch_dt = df.groupby(['card_id'])['purchase_date'].max().reset_index()\n    insta_by_card = df.groupby(['card_id'])['installments'].sum().reset_index()\n    #auth_ct_bycard=df.groupby('card_id')['authorized_flag'].apply(lambda x: (x=='Y').sum()).reset_index(name='Auth_count')\n    df_comb = inp_df.merge(month_lag_bycard, how='left', on = ['card_id'])\n    df_comb = df_comb.merge(purch_amt_bycard, how='left', on = ['card_id'])\n    df_comb = df_comb.merge(max_purch_dt, how='left', on = ['card_id'])\n    df_comb['age_of_card'] = (df_comb['purchase_date'] - df_comb['first_active_month']).dt.days\n    df_comb = df_comb.merge(insta_by_card, how='left', on = ['card_id'])\n    #train_df_comb = train_df_comb.merge(auth_ct_bycard, how='left', on = ['card_id'])\n    df_comb.fillna(0,inplace=True)\n    return df_comb","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"36fdf4ad0cd798e72c4473c531a04d2dd95a0ff1"},"cell_type":"code","source":"def gen_merch_features(df, inp_df):\n    sales_lag3_card = df.groupby(['card_id'])['avg_sales_lag3'].mean().reset_index()\n    purch_lag3_card = df.groupby(['card_id'])['avg_purchases_lag3'].mean().reset_index()\n    act_mth3_card = df.groupby(['card_id'])['active_months_lag3'].mean().reset_index()\n    sales_lag6_card = df.groupby(['card_id'])['avg_sales_lag6'].mean().reset_index()\n    purch_lag6_card = df.groupby(['card_id'])['avg_purchases_lag6'].mean().reset_index()\n    act_mth6_card = df.groupby(['card_id'])['active_months_lag6'].mean().reset_index()\n    sales_lag12_card = df.groupby(['card_id'])['avg_sales_lag12'].mean().reset_index()\n    purch_lag12_card = df.groupby(['card_id'])['avg_purchases_lag12'].mean().reset_index()\n    act_mth12_card = df.groupby(['card_id'])['active_months_lag12'].mean().reset_index()\n    df_comb = inp_df.merge(sales_lag3_card, how='left', on = ['card_id'])\n    df_comb = df_comb.merge(purch_lag3_card, how='left', on = ['card_id'])\n    df_comb = df_comb.merge(act_mth3_card, how='left', on = ['card_id'])\n    df_comb = df_comb.merge(sales_lag6_card, how='left', on = ['card_id'])\n    df_comb = df_comb.merge(purch_lag6_card, how='left', on = ['card_id'])\n    df_comb = df_comb.merge(act_mth6_card, how='left', on = ['card_id'])\n    df_comb = df_comb.merge(sales_lag12_card, how='left', on = ['card_id'])\n    df_comb = df_comb.merge(purch_lag12_card, how='left', on = ['card_id'])\n    df_comb = df_comb.merge(act_mth12_card, how='left', on = ['card_id'])\n    df_comb.fillna(0,inplace=True)\n    return df_comb","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f361e7f7c06358b9b084703c46c7fd568facc6dc"},"cell_type":"code","source":"train_df_comb = gen_features(train_df_merged,train_df)\ntrain_df_comb = gen_merch_features(train_df_merch,train_df_comb)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"dcf0ce9236531e3382f3f8191a5578b012786857"},"cell_type":"code","source":"len(train_df_comb)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8033b9e42d6df57a3081ce0a4a56d7214ef5039a"},"cell_type":"code","source":"train_df_comb.to_csv('train_df_comb.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9657290318f80d1236aee67c10ac982e321fedbf"},"cell_type":"code","source":"test_df_comb = gen_features(test_df_merged,test_df)\ntest_df_comb = gen_merch_features(test_df_merch,test_df_comb)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0b6eae229e3bf42aefe9e1fc0bf1815452d6e199"},"cell_type":"code","source":"len(test_df_comb)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"98e175ae32c8f46ecea71b87a526f95d2c34c1f1"},"cell_type":"code","source":"test_df_comb.loc[~(test_df_comb.purchase_amount==0)]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"47dca973fa37e1f41f05a91aa67af98332cc3125"},"cell_type":"code","source":"test_df_comb.to_csv('test_df_comb.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}