{"cells":[{"metadata":{"_uuid":"ad4802441c905baaf9ce80497e0ae60ca412be9f"},"cell_type":"markdown","source":"**Elo**, one of the largest payment brands in Brazil, has built partnerships with merchants in order to offer promotions or discounts to cardholders. Elo want to understand the most important aspects and preferences in their customers’ lifecycle, from food to shopping. The aim of this project is to develop algorithms to identify and serve the most relevant opportunities to individuals, by uncovering signal in customer loyalty.\n\nThis data is found under the competetion \"Elo Merchant Category Recommendation\" (refrence: https://www.kaggle.com/c/elo-merchant-category-recommendation )\n\nData Description:\n*  The train.csv and test.csv files contain the card_ids and information about the card itself - the first month the card was active, etc.. that will be using for training and prediction and train.csv also contains the target.\n* historical_transactions.csv -contains up to 3 months' worth of historical transactions for each card_id\n* merchants.csv - additional information about all merchants / merchant_ids (aggregate information for each merchant_id) in the dataset.\n* new_merchant_transactions.csv - two months' worth of data for each card_id containing ALL purchases that card_id made at merchant_ids that were not visited in the historical data.\n\n\nResult:\n* I am predicting a loyalty score for each card_id represented in test.csv and sample_submission.csv."},{"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 datetime\nimport gc\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport lightgbm as lgb\nfrom sklearn.model_selection import StratifiedKFold\nfrom sklearn.metrics import mean_squared_error\nimport warnings\nwarnings.filterwarnings('ignore')\nnp.random.seed(49)\nimport os\nprint(os.listdir(\"../input\"))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"510588e2c556bfb1b30a4f7e61b98d2f0ada445e"},"cell_type":"code","source":"train_df = pd.read_csv(\"../input/train.csv\")\ntest_df = pd.read_csv(\"../input/test.csv\")\nmerchants_df = pd.read_csv(\"../input/merchants.csv\")","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"new_merchant_transactions_df = pd.read_csv(\"../input/new_merchant_transactions.csv\", )\nhistorical_transactions_df = pd.read_csv(\"../input/historical_transactions.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4b8545eed9f52716bcd3b810e7823526816e3482"},"cell_type":"code","source":"#ref https://www.kaggle.com/chauhuynh/my-first-kernel-3-699/ \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        else:\n            df[col] = df[col].astype('category')\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":"0345d41b74362c1cd93d47663677ccaae5f23023"},"cell_type":"code","source":"historical_transactions_df = reduce_mem_usage(historical_transactions_df)\nnew_merchant_transactions_df = reduce_mem_usage(new_merchant_transactions_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5d5e28b333eca84df31a960356f460fb4f5d66a3"},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b1a3498bd420423420cf949879e42a10092e63b2"},"cell_type":"code","source":"merchants_df = reduce_mem_usage(merchants_df)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"33904dd6a9ecba404c564e9278ff7cbdef7ea2ab"},"cell_type":"code","source":"train_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c73097975943ef96cb955f1902e8b38dd7856bef"},"cell_type":"code","source":"test_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"22dd43f3aba80de263b8f494fecd16e75a6381a5"},"cell_type":"code","source":"historical_transactions_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"85c06330c648652ee89de021ea771b239285364a"},"cell_type":"code","source":"merchants_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6584281b101b48d00d602b1156c3fdc5bd5ef64c"},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"93019e59543d12d5ae7f60197efc52bdded9c801"},"cell_type":"code","source":"#check for null values\nfor df in[historical_transactions_df, new_merchant_transactions_df]:\n    print(df.isna().sum())","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"61599a533c036d6936a856592ba62a90b33a0a81"},"cell_type":"code","source":"for df in [historical_transactions_df,new_merchant_transactions_df]:\n    df['category_2'].fillna(1.0,inplace=True)\n    df['category_3'].fillna('A',inplace=True)\n    df['merchant_id'].fillna('M_ID_00a6ca8a8a',inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d8d877c1a057c6626d591a6ce64e4073fd139774"},"cell_type":"code","source":"merchants_df.isna().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2511d5e2514fd4a1b1c4064214a49830f2222e74"},"cell_type":"code","source":"merchants_df.apply(lambda x : len(x.unique()))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"03dfc3e1dcfbd00e07954a4973164cdfe849f2e6"},"cell_type":"code","source":"merchants_df['avg_purchases_lag3'].value_counts().head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bfb8f62f98eb0a00d39bb2eea0db52c1bf0aa731"},"cell_type":"code","source":"merchants_df['avg_purchases_lag6'].value_counts().head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0d749dd6a6194d9c34a39a4fd86757d930a3c0f9"},"cell_type":"code","source":"merchants_df['avg_purchases_lag12'].value_counts().head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"72b305485d912f943aa0c643b1c29d29567d5fb2"},"cell_type":"code","source":"merchants_df['category_2'].value_counts().head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c95bb273b53ef33235d2df4e35c014cbc6b1adca"},"cell_type":"code","source":"merchants_df['avg_sales_lag3'].fillna(merchants_df['avg_sales_lag3'].mean(),inplace=True)\nmerchants_df['avg_sales_lag6'].fillna(merchants_df['avg_sales_lag6'].mean(),inplace=True)\nmerchants_df['avg_sales_lag12'].fillna(merchants_df['avg_sales_lag12'].mean(),inplace=True)\nmerchants_df['category_2'].fillna(1.0,inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1693f6706fc297c45bfd1506c8a39dfe169356ac"},"cell_type":"code","source":"merchants_df.isnull().sum()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"60e81aaa406b26744132a031855b3c0d853de2e9"},"cell_type":"code","source":"merchants_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"74817dcb8822d1d29ea49b7f8eaa712da03aa561"},"cell_type":"code","source":"merchants_df['avg_sales_lag6'].describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ea4e148991c6782d233c5e36f110a0e99dd03bfa"},"cell_type":"code","source":"#del merchants_df['avg_sales_lag3']\n#del merchants_df['avg_sales_lag6']\n#del merchants_df['avg_sales_lag12']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"68f8270a14c9255cf88670d92814b5a2b0740908"},"cell_type":"code","source":"merchants_df.columns = ['merch_' + str(col) for col in merchants_df.columns]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d8c53a65071d5cc5b70a259b83b60e026f1875d6"},"cell_type":"code","source":"merchants_df.columns","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bcaacfb80a81766338983ed427aa899d9782df69"},"cell_type":"code","source":"merchants_df.columns = ['merchant_id', 'merch_merchant_group_id',\n       'merch_merchant_category_id', 'merch_subsector_id', 'merch_numerical_1',\n       'merch_numerical_2', 'merch_category_1',\n       'merch_most_recent_sales_range', 'merch_most_recent_purchases_range',\n       'merch_avg_sales_lag3', 'merch_avg_purchases_lag3',\n       'merch_active_months_lag3', 'merch_avg_sales_lag6',\n       'merch_avg_purchases_lag6', 'merch_active_months_lag6',\n       'merch_avg_sales_lag12', 'merch_avg_purchases_lag12',\n       'merch_active_months_lag12', 'merch_category_4', 'merch_city_id',\n       'merch_state_id', 'merch_category_2']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a787c9b39e7a68e4adf704bddfa319da3e196b9a"},"cell_type":"code","source":"merchants_df['merch_most_recent_sales_range'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"816d52659a3eb24b9bcefdcfe86ca4289cc84c0f"},"cell_type":"code","source":"merchants_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"511aa8e95e33d677acba790cf9d6c01607fdf28b"},"cell_type":"code","source":"merchants_df['merch_category_1'] = merchants_df['merch_category_1'].map({'Y':1, 'N':0})\nmerchants_df['merch_most_recent_sales_range'] = merchants_df['merch_most_recent_sales_range'].astype('category').cat.codes\nmerchants_df['merch_most_recent_purchases_range'] = merchants_df['merch_most_recent_purchases_range'].astype('category').cat.codes","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1061091e05e5a79052b7506f7e03cf321ddec8ba"},"cell_type":"code","source":"merchants_df = merchants_df.drop_duplicates(subset='merchant_id', keep=\"last\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"19410005731de465788ed8067d45bd0a570496fd"},"cell_type":"code","source":"merchants_df.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ebef1b526e32674927a85d61763fcc51766862f1"},"cell_type":"code","source":"merchants_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"dac799363697af20078584e5d7c72c8954505571"},"cell_type":"code","source":"merchants_df['merch_category_4'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d6554ff557771f919d5553bd09a801db9a57e439"},"cell_type":"code","source":"merchants_df['merch_category_4'] = merchants_df['merch_category_4'].map({'Y':1, 'N':0})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b313cef238a5e29bada32866cc885a14f3e96b09"},"cell_type":"code","source":"merchants_df.dtypes","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f66bfbc911dc982edd1a78c04e5a7e9e49a630e0"},"cell_type":"code","source":"merchants_df['merch_avg_sales_lag3'] = merchants_df['merch_avg_sales_lag3'].astype('float16')\nmerchants_df['merch_avg_purchases_lag3'] = merchants_df['merch_avg_purchases_lag3'].astype('float16')\nmerchants_df['merch_avg_sales_lag6'] = merchants_df['merch_avg_sales_lag6'].astype('float16')\nmerchants_df['merch_avg_purchases_lag6'] = merchants_df['merch_avg_purchases_lag6'].astype('float16')\nmerchants_df['merch_avg_sales_lag12'] = merchants_df['merch_avg_sales_lag12'].astype('float16')\nmerchants_df['merch_avg_purchases_lag12'] = merchants_df['merch_avg_purchases_lag12'].astype('float16')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f9287607e08012bf403a9923893ffae633d8ad79"},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9267973549da40a4181ac6d5f7747e4c21108203"},"cell_type":"code","source":"historical_transactions_df = historical_transactions_df.merge(merchants_df,on='merchant_id', how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"33e02b71e542d93e9a8b9463dccc91c03f077818"},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ccf4f1825e1404ff0c486368d25d02c97b6b0afe"},"cell_type":"code","source":"historical_transactions_df.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"aa2430aa6a4c707915780a37539de3ec1fb11495"},"cell_type":"code","source":"historical_transactions_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6eee4d947cfab67be04b3b546bd3cbda6a56e1bb"},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6f96768c7b41a7f2f9cf833a5a01bc2ae3bdb286"},"cell_type":"code","source":"new_merchant_transactions_df = new_merchant_transactions_df.merge(merchants_df,on='merchant_id', how='left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5110afde2c7ab0f3ca23d71f548d3fdc748290a7"},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b5b0f6c34aa799c785155ce126effbb27166728b"},"cell_type":"code","source":"for df in[historical_transactions_df, new_merchant_transactions_df]:\n    del df['merch_city_id']\n    del df['merch_category_2']\n    del df['merch_state_id']\n    del df['merch_merchant_category_id']\n    del df['merch_subsector_id']\n    del df['merch_category_1']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"69b0407ff1b740cf8f21e004281d7ca7017fe42e"},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"80e6c0a048b54e61602697395089d88ffeb94e6a"},"cell_type":"code","source":"del merchants_df\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"869a36664fd33e43a88e0260e42636b0b2c8ced0"},"cell_type":"code","source":"new_merchant_transactions_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"563a54decbad397ce3015904933373c8fe93c453"},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"323d8f01017b579cc94e7dd65c8ece5fcb477b53"},"cell_type":"code","source":"def get_new_columns(name,aggs):\n    return [name + '_' + k + '_' + agg for k in aggs.keys() for agg in aggs[k]]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b659b1194fefb0903f6930283e1007e4144d5e10"},"cell_type":"code","source":"for df in [historical_transactions_df,new_merchant_transactions_df]:\n    df['purchase_date'] = pd.to_datetime(df['purchase_date'])\n    df['year'] = df['purchase_date'].dt.year\n    df['weekofyear'] = df['purchase_date'].dt.weekofyear\n    df['month'] = df['purchase_date'].dt.month\n    df['dayofweek'] = df['purchase_date'].dt.dayofweek\n    df['weekend'] = (df.purchase_date.dt.weekday >=5).astype(int)\n    df['hour'] = df['purchase_date'].dt.hour\n    df['authorized_flag'] = df['authorized_flag'].map({'Y':1, 'N':0})\n    df['category_1'] = df['category_1'].map({'Y':1, 'N':0}) \n    df['month_diff'] = ((datetime.datetime.today() - df['purchase_date']).dt.days)//30\n    df['month_diff'] += df['month_lag']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1ca662b7dec41f93dc8dd68cdf33f1155de4ec01"},"cell_type":"code","source":"historical_transactions_df.dtypes","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c58dc69b270ff8549726da791c97c84ee1b4cbbc"},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e310f44465661a4942c24c4fb67c7ff3c3b197f8"},"cell_type":"code","source":"for df in [historical_transactions_df,new_merchant_transactions_df]:\n    df['merch_category_4'] = df['merch_category_4'].astype('int8')\n    df['year'] = df['year'].astype('int8')\n    df['weekofyear'] = df['weekofyear'].astype('int8')\n    df['month'] = df['month'].astype('int8')\n    df['dayofweek'] = df['dayofweek'].astype('int8')\n    df['weekend'] = df['weekend'].astype('int8')\n    df['hour'] = df['hour'].astype('int8')\n    df['month_diff'] = df['month_diff'].astype('int8')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bd244e10404e355f5af6251120bc9999a9734c50"},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ade222b6be2579f0e58ac8b4c5f2e7dba16f6ff4"},"cell_type":"code","source":"historical_transactions_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ad24d78283d4e144b7e9d304bd2d6afc11e0beb2"},"cell_type":"code","source":"for df in[historical_transactions_df, new_merchant_transactions_df]:\n    df['category_3'] = df['category_3'].astype('category').cat.codes","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c8019498cb02ba983bae44c07ed957da0c46615f"},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"28611ee92b0b0dfea10000bae093668f749ecd19"},"cell_type":"code","source":"historical_transactions_df.dtypes","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"122ec33165aec6f9f204cd5a806090fc538866b4"},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8a797fe92720ccce84846a6fcfab4727185b79b9"},"cell_type":"code","source":"historical_transactions_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a61f82607118aa76f03645f6097d15060b06e82e"},"cell_type":"code","source":"aggs = {}\nfor col in ['month','hour','weekofyear','dayofweek','year','subsector_id','merchant_id','merchant_category_id']:\n    aggs[col] = ['nunique']\n    \n    \n    \naggs['purchase_amount'] = ['sum','max','min','mean','var']\naggs['installments'] = ['sum','max','min','mean','var']\naggs['purchase_date'] = ['max','min']\naggs['month_lag'] = ['max','min','mean','var']\naggs['month_diff'] = ['mean']\naggs['authorized_flag'] = ['sum', 'mean']\naggs['weekend'] = ['sum', 'mean']\naggs['category_1'] = ['sum', 'mean']\naggs['card_id'] = ['size']\n\naggs['merch_numerical_1'] = ['sum', 'mean']\naggs['merch_numerical_2'] = ['sum', 'mean']\naggs['merch_most_recent_sales_range'] = ['sum', 'mean']\naggs['merch_most_recent_purchases_range'] = ['sum', 'mean']\naggs['merch_avg_purchases_lag3'] = ['sum', 'mean']\naggs['merch_active_months_lag3'] = ['sum', 'mean']\naggs['merch_avg_purchases_lag6'] = ['sum', 'mean']\naggs['merch_active_months_lag6'] = ['sum', 'mean']\naggs['merch_avg_purchases_lag12'] = ['sum', 'mean']\naggs['merch_active_months_lag12'] = ['sum', 'mean']\naggs['merch_category_4'] = ['sum', 'mean']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fddb699fcd0877b14fcb1f573561c75fa2c206c4"},"cell_type":"code","source":"for col in ['category_1', 'category_2','category_3','installments', 'state_id', 'month', 'dayofweek', 'hour']:\n    historical_transactions_df[col+'_mean'] = historical_transactions_df.groupby([col])['purchase_amount'].transform('mean')\n    aggs[col+'_mean'] = ['mean']   \n    \nhistorical_transactions_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"95cd3ce88fdcf99a038d5f689e19474ad6275e2b"},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c1179109788db593b4ba6b07057f17f41bdb59b2"},"cell_type":"code","source":"new_columns = get_new_columns('hist',aggs)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6380731496d9fd7f5022202dc8d2f3dc0baeb29c"},"cell_type":"code","source":"df_hist_trans_group = historical_transactions_df.groupby('card_id').agg(aggs)\ndf_hist_trans_group.columns = new_columns\ndf_hist_trans_group.reset_index(drop=False,inplace=True)\ndf_hist_trans_group['hist_purchase_date_diff'] = (df_hist_trans_group['hist_purchase_date_max'] - df_hist_trans_group['hist_purchase_date_min']).dt.days\ndf_hist_trans_group['hist_purchase_date_average'] = df_hist_trans_group['hist_purchase_date_diff']/df_hist_trans_group['hist_card_id_size']\ndf_hist_trans_group['hist_purchase_date_uptonow'] = (datetime.datetime.today() - df_hist_trans_group['hist_purchase_date_max']).dt.days","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5aeeb1fe8b61301e07c708cc7e08395835a2ebfa"},"cell_type":"code","source":"del historical_transactions_df\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f7182c037cb1e06ba79394418a2807a1ebd3fea7"},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f5320715bc1fefa30031be2b23e0d12d1e22a4ba"},"cell_type":"code","source":"train_df = train_df.merge(df_hist_trans_group,on='card_id',how='left')\ntest_df = test_df.merge(df_hist_trans_group,on='card_id',how='left')\ndel df_hist_trans_group\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d144f079b87fddd56121b9d377a90b7add0a7b71"},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b57cc6932d9f62d88ddfb5723b31da381f2d90b4"},"cell_type":"code","source":"train_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a7c930af166e749add6469071f3f9b65de57fa93"},"cell_type":"code","source":"test_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f3c81eadc8b1c4c0dd49b929959ac9952bcaf595"},"cell_type":"code","source":"aggs = {}\nfor col in ['month','hour','weekofyear','dayofweek','year','subsector_id','merchant_id','merchant_category_id']:\n    aggs[col] = ['nunique']\naggs['purchase_amount'] = ['sum','max','min','mean','var']\naggs['installments'] = ['sum','max','min','mean','var']\naggs['purchase_date'] = ['max','min']\naggs['month_lag'] = ['max','min','mean','var']\naggs['month_diff'] = ['mean']\naggs['weekend'] = ['sum', 'mean']\naggs['category_1'] = ['sum', 'mean']\naggs['card_id'] = ['size']\n\nfor col in ['category_1', 'category_2','category_3', 'installments', 'state_id', 'month', 'dayofweek', 'hour']:\n    new_merchant_transactions_df[col+'_mean'] = new_merchant_transactions_df.groupby([col])['purchase_amount'].transform('mean')\n    aggs[col+'_mean'] = ['mean']\n    \nnew_columns = get_new_columns('new_hist',aggs)\ndf_hist_trans_group = new_merchant_transactions_df.groupby('card_id').agg(aggs)\ndf_hist_trans_group.columns = new_columns\ndf_hist_trans_group.reset_index(drop=False,inplace=True)\ndf_hist_trans_group['new_hist_purchase_date_diff'] = (df_hist_trans_group['new_hist_purchase_date_max'] - df_hist_trans_group['new_hist_purchase_date_min']).dt.days\ndf_hist_trans_group['new_hist_purchase_date_average'] = df_hist_trans_group['new_hist_purchase_date_diff']/df_hist_trans_group['new_hist_card_id_size']\ndf_hist_trans_group['new_hist_purchase_date_uptonow'] = (datetime.datetime.today() - df_hist_trans_group['new_hist_purchase_date_max']).dt.days\ntrain_df = train_df.merge(df_hist_trans_group,on='card_id',how='left')\ntest_df = test_df.merge(df_hist_trans_group,on='card_id',how='left')\ndel df_hist_trans_group\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"dc448e18da992db02d5ef16eb9f134012bdab9f2"},"cell_type":"code","source":"train_df.head(5)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fbba67b2e37beea09490402f9b08f388da35417b"},"cell_type":"code","source":"del new_merchant_transactions_df\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c3af37cca2dd7808c311ffacc14cc7819ea4df91"},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4384cb2309e6eb4d57a9c8d42e19e11e3842d3d0"},"cell_type":"code","source":"train_df['outliers'] = 0\ntrain_df.loc[train_df['target'] < -30, 'outliers'] = 1\ntrain_df['outliers'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5714796bd2eac4005f2b8d00b47380d350504bbe"},"cell_type":"code","source":"for df in [train_df,test_df]:\n    df['first_active_month'] = pd.to_datetime(df['first_active_month'])\n    df['dayofweek'] = df['first_active_month'].dt.dayofweek\n    df['weekofyear'] = df['first_active_month'].dt.weekofyear\n    df['month'] = df['first_active_month'].dt.month\n    df['elapsed_time'] = (datetime.datetime.today() - df['first_active_month']).dt.days\n    df['hist_first_buy'] = (df['hist_purchase_date_min'] - df['first_active_month']).dt.days\n    df['new_hist_first_buy'] = (df['new_hist_purchase_date_min'] - df['first_active_month']).dt.days\n    \n    for f in ['hist_purchase_date_max','hist_purchase_date_min','new_hist_purchase_date_max','new_hist_purchase_date_min']:\n        df[f] = df[f].astype(np.int64) * 1e-9\n    \n    df['card_id_total'] = df['new_hist_card_id_size']+df['hist_card_id_size']\n    df['purchase_amount_total'] = df['new_hist_purchase_amount_sum']+df['hist_purchase_amount_sum']\n\nfor f in ['feature_1','feature_2','feature_3']:\n    order_label = train_df.groupby([f])['outliers'].mean()\n    train_df[f] = train_df[f].map(order_label)\n    test_df[f] = test_df[f].map(order_label)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"28a4dc9f09002a18d918e62821c81245e471243d"},"cell_type":"code","source":"train_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5dac71c3bd4f6319cc10827b78abfdc4d1e175bd"},"cell_type":"code","source":"test_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9cbe585317650d9c0d42cd22538be75ac328ed6d"},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d742a281093e1ba4ff02aa88187634f3c5bcdf82"},"cell_type":"code","source":"df_train_columns = [c for c in train_df.columns if c not in ['card_id', 'first_active_month','target','outliers']]\ntarget = train_df['target']\ndel train_df['target']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"40b2098be4a86142a68d64140ae0bdfd68b7a38f"},"cell_type":"code","source":" param = {'num_leaves': 31,\n         'min_data_in_leaf': 30, \n         'objective':'regression',\n         'max_depth': -1,\n         'learning_rate': 0.01,\n         \"min_child_samples\": 20,\n         \"boosting\": \"gbdt\",\n         \"feature_fraction\": 0.9,\n         \"bagging_freq\": 1,\n         \"bagging_fraction\": 0.9 ,\n         \"bagging_seed\": 11,\n         \"metric\": 'rmse',\n         \"lambda_l1\": 0.1,\n         \"verbosity\": -1,\n         \"nthread\": 4,\n         \"random_state\": 49}\n    \nfolds = StratifiedKFold(n_splits=5, shuffle=True, random_state=49)\noof = np.zeros(len(train_df))\npredictions = np.zeros(len(test_df))\nfeature_importance_df = pd.DataFrame()\n\nfor fold_, (trn_idx, val_idx) in enumerate(folds.split(train_df,train_df['outliers'].values)):\n    print(\"fold {}\".format(fold_))\n    trn_data = lgb.Dataset(train_df.iloc[trn_idx][df_train_columns], label=target.iloc[trn_idx])#, categorical_feature=categorical_feats)\n    val_data = lgb.Dataset(train_df.iloc[val_idx][df_train_columns], label=target.iloc[val_idx])#, categorical_feature=categorical_feats)\n\n    num_round = 10000\n    clf = lgb.train(param, trn_data, num_round, valid_sets = [trn_data, val_data], verbose_eval=100, early_stopping_rounds = 100)\n    oof[val_idx] = clf.predict(train_df.iloc[val_idx][df_train_columns], num_iteration=clf.best_iteration)\n    \n    fold_importance_df = pd.DataFrame()\n    fold_importance_df[\"Feature\"] = df_train_columns\n    fold_importance_df[\"importance\"] = clf.feature_importance()\n    fold_importance_df[\"fold\"] = fold_ + 1\n    feature_importance_df = pd.concat([feature_importance_df, fold_importance_df], axis=0)\n    \n    predictions += clf.predict(test_df[df_train_columns], num_iteration=clf.best_iteration) / folds.n_splits\n\nnp.sqrt(mean_squared_error(oof, target))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"be100dc62404a80a487c666eee4d90b1cd814962"},"cell_type":"code","source":"cols = (feature_importance_df[[\"Feature\", \"importance\"]]\n        .groupby(\"Feature\")\n        .mean()\n        .sort_values(by=\"importance\", ascending=False)[:1000].index)\n\nbest_features = feature_importance_df.loc[feature_importance_df.Feature.isin(cols)]\n\nplt.figure(figsize=(14,25))\nsns.barplot(x=\"importance\",\n            y=\"Feature\",\n            data=best_features.sort_values(by=\"importance\",\n                                           ascending=False))\nplt.title('LightGBM Features (avg over folds)')\nplt.tight_layout()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"24b17f4094f0994d18c6a96ca00799dc4ba82aeb"},"cell_type":"code","source":"sub_df = pd.DataFrame({\"card_id\":test_df[\"card_id\"].values})\nsub_df[\"target\"] = predictions\nsub_df.to_csv(\"submission.csv\", index=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f121a187663fd2ba7ffc19f986f0496c241f5cb1"},"cell_type":"code","source":"sub_df.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4951cc93aeadf375818739e47f94202b674ab11f"},"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}