{"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\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":"fdc62e971dfedaca1ea8b8d3cece3d4b0438b99c"},"cell_type":"code","source":"#Reduce the memory usage - Inspired by Panchajanya Banerjee\ndef reduce_mem_usage(df, verbose=True):\n    numerics = ['int16', 'int32', 'int64', 'float16', 'float32', 'float64']\n    start_mem = df.memory_usage().sum() / 1024**2    \n    for col in df.columns:\n        col_type = df[col].dtypes\n        if col_type in numerics:\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    end_mem = df.memory_usage().sum() / 1024**2\n    if verbose: print('Mem. usage decreased to {:5.2f} Mb ({:.1f}% reduction)'.format(end_mem, 100 * (start_mem - end_mem) / start_mem))\n    return df","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"train = reduce_mem_usage(pd.read_csv(\"../input/train.csv\", parse_dates=[\"first_active_month\"]))\nmerch = reduce_mem_usage(pd.read_csv(\"../input/merchants.csv\"))\ntest = reduce_mem_usage(pd.read_csv(\"../input/test.csv\", parse_dates= [\"first_active_month\"]))\nhistTrans = reduce_mem_usage(pd.read_csv(\"../input/historical_transactions.csv\"))\nnewMerchTrans = reduce_mem_usage(pd.read_csv(\"../input/new_merchant_transactions.csv\"))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e6d3506e3ce46e37a74100205b4ed92792928225"},"cell_type":"code","source":"train.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bdf60fb51c894ad0d5ac6eb037057c68b4ae4115"},"cell_type":"code","source":"train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e05badd31e71043e8843c91dcff5e26e2f5757fb"},"cell_type":"code","source":"import gc\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9503ad8657d88410bf530fea3db5e506bfdbe0d3"},"cell_type":"code","source":"merch.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d4cad0b05eeacb9bf4d63877f4962b7088a2bed7"},"cell_type":"code","source":"test.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5ae5f69c553974713d04ed7d9e2924efe1434aca"},"cell_type":"code","source":"histTrans.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ce41f65fba475859062cce77c0860dbf11176edb"},"cell_type":"code","source":"newMerchTrans.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"797e231aecd9eaa6ac60c0ad45105424bf9e314b"},"cell_type":"code","source":"train.target.describe()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a7fe7affacad3684d24926e700cc2262e54d044c"},"cell_type":"code","source":"import matplotlib.pyplot as plt\nimport seaborn as sns\n%matplotlib inline\nplt.figure(figsize=(12,5))\nplt.hist(train.target.values, bins = 300)\nplt.title('Histogram of target Variable')\nplt.xlabel('target')\nplt.ylabel('counts')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"be6ca5877e14c4c36782a3f36b7d55bfd9c95440"},"cell_type":"code","source":"histTrans.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"a643a525910a0183c154b0e7bb90ec28b7681795"},"cell_type":"code","source":"histTrans['merchant_category_id'].value_counts(dropna = False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"9ce25ad08f20ff3d6e95ca54e60f01e06e2bffcc"},"cell_type":"code","source":"histTrans['subsector_id'].value_counts(dropna = False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"8f4d2992f1a6db706f51a0f9b9a450be82d3130c"},"cell_type":"code","source":"histTrans['state_id'].value_counts(dropna = False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"1437baa1df411d23c27b03f06f8931b67082c081"},"cell_type":"code","source":"histTrans['city_id'].value_counts(dropna = False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f001b2448987a578b203a74912cf04badb7854f5"},"cell_type":"code","source":"newMerchTrans.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"92ffd6cdf599655b17fff2ef647bda5a531ee1ba"},"cell_type":"code","source":"newMerchTrans.installments.value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d69e5f6877aa05cf6151b73ae9ea58bc652c1ae0"},"cell_type":"code","source":"train.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"384c666aa0488b7daadfbdddcb3449c4789e5867"},"cell_type":"code","source":"#train_new = train.merge(histTrans, on= 'card_id' ,how = 'left')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e250c7b501a4e490e6085f19dad925cb7450f4ec"},"cell_type":"code","source":"newMerchTrans.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a24783f7ac0d54299587ec88210ad3a8c506c6fe"},"cell_type":"code","source":"histTrans.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2c464b85dc9b714a99f818035b2a72ed14119e3c"},"cell_type":"code","source":"merch.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"724bc99657748605d28feb0743a6e09b41fa16f8"},"cell_type":"code","source":"plt.figure(figsize=(12,5))\nplt.hist(train.feature_1.values,100)\nplt.title('Histogram of feature_1 Variable')\nplt.xlabel('feature_1')\nplt.ylabel('counts')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b949e45b32649ab124aab7959adac0138d06725c"},"cell_type":"code","source":"train.feature_1.value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"796fe29bcd59133062e2744df60c3498b8371cbc"},"cell_type":"code","source":"plt.figure(figsize=(12,6))\nplt.hist(train.feature_2.values,bins = 100)\nplt.title(\"Histogram of feature_2 variable\")\nplt.xlabel(\"feature_2\")\nplt.ylabel('counts')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"798e501adee46a9d6ba1555e5886008085ab05f4"},"cell_type":"code","source":"train.feature_2.value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fb94492a6ec7aea7b5f149684d4b57e62a233679"},"cell_type":"code","source":"plt.figure(figsize=(12,6))\nplt.hist(train.feature_3, bins = 100)\nplt.title('histogram of feature_3')\nplt.xlabel('feature_3')\nplt.ylabel('counts')\nplt.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3b23324074fa7da667ea6264423dd6b4a3abc5d7"},"cell_type":"code","source":"train.feature_3.value_counts()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"96909e193e19fb8e5f518a586e280e65ec7ab3fa"},"cell_type":"markdown","source":"So far we have seen that all the feature variables of train data are categorical.\nThe Target variable is normalised with some anomalies on the far left."},{"metadata":{"trusted":true,"_uuid":"8b2feccfeb07178b555ba4008694f08a6fc39e69"},"cell_type":"code","source":"histTrans.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a9065c4d14f359d986ee31fb471f7bc6ad241388"},"cell_type":"code","source":"train.first_active_month.value_counts().sort_index().tail()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0fabdc9590e9c642c5364ab2af2d9888dbc2d8b4"},"cell_type":"code","source":"train.first_active_month.value_counts().sort_index().head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"cafb32c0193ed996c31d318f8ad2ba55f7fa7b32"},"cell_type":"markdown","source":"**<h2>Feature Engineering</h2>**"},{"metadata":{"trusted":true,"_uuid":"8dae22bda72d262cb77d201e340fa4b0baef805d"},"cell_type":"code","source":"import datetime\n#train['first_active_month'] = pd.to_datetime(train['first_active_month'], format = '%Y-%m' )\n\ntrain['month'] = train[\"first_active_month\"].dt.month\ntrain['year'] = train[\"first_active_month\"].dt.year\n\ntest['month'] = test[\"first_active_month\"].dt.month\ntest['year'] = test[\"first_active_month\"].dt.year\n\ntrain['elapsed_time'] = (datetime.date(2018, 2, 1) - train['first_active_month'].dt.date).dt.days # the last date entry is 2018-02\ntest['elapsed_time'] = (datetime.date(2018, 1, 1) - test['first_active_month'].dt.date).dt.days # the last date entry is 2018-01","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9038b227b5277092d436400bf223e7d8bf9497f4"},"cell_type":"code","source":"train.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ac6324d3bbf3c37618101e3110d4203a57df86e6"},"cell_type":"code","source":"test.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fdc93fbe2d9f68e3727fc3f725260bfba8ba2760"},"cell_type":"code","source":"train.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8f22ceeb5d7cdbed62d88597ba76ced9c7f05cd5"},"cell_type":"code","source":"#train = pd.get_dummies(train, columns = ['feature_1','feature_2'])\n#test = pd.get_dummies(test, columns = ['feature_1','feature_2'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"968ff1eb1ec3a65149d5d7669d6987e4b85ba2cc"},"cell_type":"code","source":"train.columns","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b2a8b432c545042ef918d456c18aea93f4b828a0"},"cell_type":"code","source":"train.info()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d400e800a6fab43bbf97f44a42065217e78eb955"},"cell_type":"code","source":"histTrans.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8278dddf5c13197918f64059ccfdd3d9c3787e47"},"cell_type":"code","source":"null_df = pd.DataFrame(columns = ['columns','Count'])\nnull_df['Count'] = [histTrans[x].isnull().sum() for x in histTrans.columns ]\nnull_df['columns'] =histTrans.columns\nnull_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ad2554579b46fe020f6fa34b0cdb6b93bebe3be1"},"cell_type":"code","source":"histTrans['category_1'].value_counts(dropna = False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8ea1b2dc49137c7ffe98d25bd91eb1b455c8637b"},"cell_type":"code","source":"histTrans.columns","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f82f313082be6842a72dff50e30dbb461348442c"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"40036b86c768aa3317fc1e1f1f4d4898afa3f533"},"cell_type":"code","source":"histTrans['category_1'] = pd.factorize(histTrans['category_1'])[0]\n#histTrans = pd.get_dummies(histTrans, columns = ['category_3', 'category_2'])\nhistTrans['authorized_flag'] = pd.factorize(histTrans['authorized_flag'])[0]\nhistTrans['category_3'] = histTrans['category_3'].map({'A':0, 'B':1, 'C':2})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"27d6c5d7bc874451ac52112b1f85e0cb9e14af09"},"cell_type":"code","source":"histTrans['category_2'].fillna(histTrans['category_2'].mode()[0], inplace = True)\nhistTrans['category_3'].fillna(histTrans['category_3'].mode()[0], inplace = True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a7361973ec883c33432c6aac9b1a54d05e23f655"},"cell_type":"code","source":"#histTrans = pd.get_dummies(histTrans, columns = ['category_3'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c3a668e0efd6373df02492979b9cba870cad6b04"},"cell_type":"code","source":"histTrans.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b072d589f6c86c5664ad77306b8a5c9e9121ac5b"},"cell_type":"code","source":"sum(histTrans['purchase_amount']<0)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"994dc341ad61ef0e878c50b8d240d0355f28da3f"},"cell_type":"code","source":"histTrans.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2dd1496bc5f7393594d968be716a8da09439710c"},"cell_type":"code","source":"#histTrans['month_lag'] = abs(histTrans['month_lag'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"890e0c4b4c284a195d29eb41d4885634de289fcc"},"cell_type":"code","source":"#histTrans['city_id'] = abs(histTrans['city_id'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"64a53d59d08dbdecd13497351aafe4e1b7f82d8a"},"cell_type":"code","source":"train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"15f86fe35c2da0f809057f8eadc654b1330b8eb7"},"cell_type":"code","source":"import gc","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"fc984089b2bfa70e5265d46f20e094fdce775f16"},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"eef89c98b7535ce43ce3799d28492373bd3efe14"},"cell_type":"code","source":"#Feature Engineering - Adding new features inspired by Chau's first kernel\nhistTrans['purchase_date'] = pd.to_datetime(histTrans['purchase_date'])\nhistTrans['year'] = histTrans['purchase_date'].dt.year\nhistTrans['weekofyear'] = histTrans['purchase_date'].dt.weekofyear\nhistTrans['month'] = histTrans['purchase_date'].dt.month\nhistTrans['dayofweek'] = histTrans['purchase_date'].dt.dayofweek\nhistTrans['weekend'] = (histTrans.purchase_date.dt.weekday >=5).astype(int)\nhistTrans['hour'] = histTrans['purchase_date'].dt.hour \nhistTrans['quarter'] = histTrans['purchase_date'].dt.quarter\nhistTrans['is_month_start'] = histTrans['purchase_date'].dt.is_month_start\nhistTrans['month_diff'] = ((datetime.datetime.today() - histTrans['purchase_date']).dt.days)//30\nhistTrans['month_diff'] += histTrans['month_lag']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ad910eef1999cb55796c93c2eaa6d71a9dda5e09"},"cell_type":"code","source":"histTrans['is_month_start'] = pd.factorize(histTrans['is_month_start'])[0]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6e40386ae96f3e018423658f8a081b0685d555ed"},"cell_type":"code","source":"histTrans.head(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"22e74eefb03b715be64db533da60e38e754d0761"},"cell_type":"code","source":"#histTrans.head(8)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9a295cc7dc56d88dc1679db982560980fa95a282"},"cell_type":"code","source":"#histTrans['category_2_mean'].value_counts(dropna = False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d03e76c9aac7b7342d11b73a328e024e45a41bc5"},"cell_type":"code","source":"# additional features\nhistTrans['price'] = histTrans['purchase_amount'] / histTrans['installments']\n\n#Christmas : December 25 2017\nhistTrans['Christmas_Day_2017']=(pd.to_datetime('2017-12-25')-histTrans['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n#Mothers Day: May 14 2017\nhistTrans['Mothers_Day_2017']=(pd.to_datetime('2017-06-04')-histTrans['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n#fathers day: August 13 2017\nhistTrans['fathers_day_2017']=(pd.to_datetime('2017-08-13')-histTrans['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n#Childrens day: October 12 2017\nhistTrans['Children_day_2017']=(pd.to_datetime('2017-10-12')-histTrans['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n#Valentine's Day : 12th June, 2017\nhistTrans['Valentine_Day_2017']=(pd.to_datetime('2017-06-12')-histTrans['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n#Black Friday : 24th November 2017\nhistTrans['Black_Friday_2017']=(pd.to_datetime('2017-11-24') - histTrans['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n\n#2018\n#Mothers Day: May 13 2018\nhistTrans['Mothers_Day_2018']=(pd.to_datetime('2018-05-13')-histTrans['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"802e70d29fc34c388a5283f76a0cdb676d625e4d"},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a9eee06fc6b2b573fe217dbb8e86555e01a0e628"},"cell_type":"code","source":"histTrans = reduce_mem_usage(histTrans)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e461b6bcf27906abfd1c54f4929ecef20b5bb879"},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a518dde571a315435822b1c4fc243e2e654a2dbe"},"cell_type":"code","source":"train = reduce_mem_usage(train)\ntest = reduce_mem_usage(test)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ce2a045be344f909ee2dbfd50bb093acd4ec3cba"},"cell_type":"code","source":"# Taking Reference from Other Kernels\ndef aggregate_transaction_hist(trans, prefix):  \n        \n    agg_func = {\n        'purchase_date' : ['max','min'],\n        'month_diff' : ['mean', 'min', 'max', 'var'],\n        'weekend' : ['sum', 'mean'],\n        'authorized_flag': ['sum', 'mean'],\n        'category_1': ['sum','mean', 'max','min'],\n        'purchase_amount': ['sum', 'mean', 'max', 'min', 'std'],\n        'installments': ['sum', 'mean', 'max', 'min', 'std'],  \n        'month_lag': ['max','min','mean','var'],\n        'card_id' : ['size'],\n        'month': ['nunique'],\n        'hour': ['nunique'],\n        'weekofyear': ['nunique'],\n        'dayofweek': ['nunique'],\n        'year': ['nunique'],\n        'subsector_id': ['nunique'],\n        'merchant_category_id' : ['nunique'],\n        'Christmas_Day_2017' : ['mean'],\n        'Mothers_Day_2017' : ['mean'],\n        'fathers_day_2017' : ['mean'],\n        'Children_day_2017' : ['mean'],\n        'Valentine_Day_2017' : ['mean'],\n        'Black_Friday_2017' : ['mean'],\n        'Mothers_Day_2018' : ['mean'],\n        'category_2': ['sum','mean', 'max','min'],\n        'category_3': ['sum','mean', 'max','min']\n    }\n    \n    agg_trans = trans.groupby(['card_id']).agg(agg_func)\n    agg_trans.columns = [prefix + '_'.join(col).strip() \n                           for col in agg_trans.columns.values]\n    agg_trans.reset_index(inplace=True)\n    \n    df = (trans.groupby('card_id')\n          .size()\n          .reset_index(name='{}transactions_count'.format(prefix)))\n    \n    agg_trans = pd.merge(df, agg_trans, on='card_id', how='left')\n    \n    return agg_trans","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9f14cfe9849dd57fb2a918aa395a5cad0164d50e"},"cell_type":"code","source":"merge_trans = aggregate_transaction_hist(histTrans, prefix='hist_')\nmerge_trans.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0f95206affab19ca2dd00d8d1df3687dac95498f"},"cell_type":"code","source":"del histTrans\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"21e7625a5b34f7d33f4a475110a7eedaa2b0413a"},"cell_type":"code","source":"#merge_trans = aggregate_transaction_hist(histTrans, prefix='hist_')\ntrain = pd.merge(train, merge_trans, on='card_id',how='left')\ntest = pd.merge(test, merge_trans, on='card_id',how='left')\ndel merge_trans\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"76ddd523f209352ddd5f9d1b6e568a913930a20e"},"cell_type":"code","source":"train.head(10)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"597666993d8550f7130a93ec53cffac0f785a54d"},"cell_type":"code","source":"train.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"71230d5f894bbccba06ee34d00984a09f64753a0"},"cell_type":"code","source":"test.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3abf410341a2bec20e6687cb3de11446c41ae8dc"},"cell_type":"code","source":"#Feature Engineering - Adding new features inspired by Chau's first kernel\ntrain['hist_purchase_date_max'] = pd.to_datetime(train['hist_purchase_date_max'])\ntrain['hist_purchase_date_min'] = pd.to_datetime(train['hist_purchase_date_min'])\ntrain['hist_purchase_date_diff'] = (train['hist_purchase_date_max'] - train['hist_purchase_date_min']).dt.days\ntrain['hist_purchase_date_average'] = train['hist_purchase_date_diff']/train['hist_card_id_size']\ntrain['hist_purchase_date_uptonow'] = (datetime.datetime.today() - train['hist_purchase_date_max']).dt.days\ntrain['hist_purchase_date_uptomin'] = (datetime.datetime.today() - train['hist_purchase_date_min']).dt.days\ntrain['hist_first_buy'] = (train['hist_purchase_date_min'] - train['first_active_month']).dt.days\n\nfor feature in ['hist_purchase_date_max','hist_purchase_date_min']:\n    train[feature] = train[feature].astype(np.int64) * 1e-9\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"882b617efec4b16ddc3b5d0f97a0442f71b8c8af"},"cell_type":"code","source":"#Feature Engineering - Adding new features inspired by Chau's first kernel\ntest['hist_purchase_date_max'] = pd.to_datetime(test['hist_purchase_date_max'])\ntest['hist_purchase_date_min'] = pd.to_datetime(test['hist_purchase_date_min'])\ntest['hist_purchase_date_diff'] = (test['hist_purchase_date_max'] - test['hist_purchase_date_min']).dt.days\ntest['hist_purchase_date_average'] = test['hist_purchase_date_diff']/test['hist_card_id_size']\ntest['hist_purchase_date_uptonow'] = (datetime.datetime.today() - test['hist_purchase_date_max']).dt.days\ntest['hist_purchase_date_uptomin'] = (datetime.datetime.today() - test['hist_purchase_date_min']).dt.days\n\ntest['hist_first_buy'] = (test['hist_purchase_date_min'] - test['first_active_month']).dt.days\n\nfor feature in ['hist_purchase_date_max','hist_purchase_date_min']:\n    test[feature] = test[feature].astype(np.int64) * 1e-9\n\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cbe2aeb694cd53e8842f939b1a2ff90cf2e0c40e"},"cell_type":"code","source":"# Taking Reference from Other Kernels\ndef aggregate_transaction_new(trans, prefix):  \n        \n    agg_func = {\n        'purchase_date' : ['max','min'],\n        'month_diff' : ['mean', 'min', 'max', 'var'],\n        'weekend' : ['sum', 'mean'],\n        'authorized_flag': ['sum', 'mean'],\n        'category_1': ['sum','mean', 'max','min'],\n        'purchase_amount': ['sum', 'mean', 'max', 'min', 'std'],\n        'installments': ['sum', 'mean', 'max', 'min', 'std'],  \n        'month_lag': ['max','min','mean','var'],\n        'card_id' : ['size'],\n        'month': ['nunique'],\n        'hour': ['nunique'],\n        'weekofyear': ['nunique'],\n        'dayofweek': ['nunique'],\n        'year': ['nunique'],\n        'subsector_id': ['nunique'],\n        'merchant_category_id' : ['nunique']\n    }\n    \n    agg_trans = trans.groupby(['card_id']).agg(agg_func)\n    agg_trans.columns = [prefix + '_'.join(col).strip() \n                           for col in agg_trans.columns.values]\n    agg_trans.reset_index(inplace=True)\n    \n    df = (trans.groupby('card_id')\n          .size()\n          .reset_index(name='{}transactions_count'.format(prefix)))\n    \n    agg_trans = pd.merge(df, agg_trans, on='card_id', how='left')\n    \n    return agg_trans","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a3b6cdca44ffff9d87b1e8e6a11f8e9573e5de28"},"cell_type":"code","source":"newMerchTrans['authorized_flag'] = newMerchTrans['authorized_flag'].map({'Y': 1, 'N': 0})\nnewMerchTrans['category_1'] = newMerchTrans['category_1'].map({'Y': 1, 'N': 0})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f55cf1d252353977ba210274b16b4d93d3b81418"},"cell_type":"code","source":"#Feature Engineering - Adding new features inspired by Chau's first kernel\nnewMerchTrans['purchase_date'] = pd.to_datetime(newMerchTrans['purchase_date'])\nnewMerchTrans['year'] = newMerchTrans['purchase_date'].dt.year\nnewMerchTrans['weekofyear'] = newMerchTrans['purchase_date'].dt.weekofyear\nnewMerchTrans['month'] = newMerchTrans['purchase_date'].dt.month\nnewMerchTrans['dayofweek'] = newMerchTrans['purchase_date'].dt.dayofweek\nnewMerchTrans['weekend'] = (newMerchTrans.purchase_date.dt.weekday >=5).astype(int)\nnewMerchTrans['hour'] = newMerchTrans['purchase_date'].dt.hour \nnewMerchTrans['quarter'] = newMerchTrans['purchase_date'].dt.quarter\nnewMerchTrans['is_month_start'] = newMerchTrans['purchase_date'].dt.is_month_start\nnewMerchTrans['month_diff'] = ((datetime.datetime.today() - newMerchTrans['purchase_date']).dt.days)//30\nnewMerchTrans['month_diff'] += newMerchTrans['month_lag']\n\ngc.collect()\n\n#impute missing values\n#newMerchTrans['category_2'] = newMerchTrans['category_2'].fillna(1.0,inplace=True)\n#newMerchTrans['category_3'] = newMerchTrans['category_3'].fillna('A',inplace=True)\n#newMerchTrans['merchant_id'] = newMerchTrans['merchant_id'].fillna('M_ID_00a6ca8a8a',inplace=True)\n\nnewMerchTrans['category_3'] = newMerchTrans['category_3'].map({'A':0, 'B':1, 'C':2}) \n\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0c72de93734558266d95dd0062f08d4ffc85784c"},"cell_type":"code","source":"merge_new = aggregate_transaction_new(newMerchTrans, prefix='new_')\ndel newMerchTrans\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a6de29459e7b9361531e7ff9b42523f5c4b41b35"},"cell_type":"code","source":"\ntrain = pd.merge(train, merge_new, on='card_id',how='left')\ntest = pd.merge(test, merge_new, on='card_id',how='left')\ndel merge_new\n\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9d004b1314e1f52441648b393155d3d70d8775aa"},"cell_type":"code","source":"#Feature Engineering - Adding new features inspired by Chau's first kernel\ntrain['new_purchase_date_max'] = pd.to_datetime(train['new_purchase_date_max'])\ntrain['new_purchase_date_min'] = pd.to_datetime(train['new_purchase_date_min'])\ntrain['new_purchase_date_diff'] = (train['new_purchase_date_max'] - train['new_purchase_date_min']).dt.days\ntrain['new_purchase_date_average'] = train['new_purchase_date_diff']/train['new_card_id_size']\ntrain['new_purchase_date_uptonow'] = (datetime.datetime.today() - train['new_purchase_date_max']).dt.days\ntrain['new_purchase_date_uptomin'] = (datetime.datetime.today() - train['new_purchase_date_min']).dt.days\ntrain['new_first_buy'] = (train['new_purchase_date_min'] - train['first_active_month']).dt.days\nfor feature in ['new_purchase_date_max','new_purchase_date_min']:\n    train[feature] = train[feature].astype(np.int64) * 1e-9\n\n#Feature Engineering - Adding new features inspired by Chau's first kernel\ntest['new_purchase_date_max'] = pd.to_datetime(test['new_purchase_date_max'])\ntest['new_purchase_date_min'] = pd.to_datetime(test['new_purchase_date_min'])\ntest['new_purchase_date_diff'] = (test['new_purchase_date_max'] - test['new_purchase_date_min']).dt.days\ntest['new_purchase_date_average'] = test['new_purchase_date_diff']/test['new_card_id_size']\ntest['new_purchase_date_uptonow'] = (datetime.datetime.today() - test['new_purchase_date_max']).dt.days\ntest['new_purchase_date_uptomin'] = (datetime.datetime.today() - test['new_purchase_date_min']).dt.days\ntest['new_first_buy'] = (test['new_purchase_date_min'] - test['first_active_month']).dt.days\nfor feature in ['new_purchase_date_max','new_purchase_date_min']:\n    test[feature] = test[feature].astype(np.int64) * 1e-9\n    \n#added new feature - Interactive\ntrain['card_id_total'] = train['new_card_id_size'] + train['hist_card_id_size']\ntrain['purchase_amount_total'] = train['new_purchase_amount_sum'] + train['hist_purchase_amount_sum']\n\ntest['card_id_total'] = test['new_card_id_size'] + test['hist_card_id_size']\ntest['purchase_amount_total'] = test['new_purchase_amount_sum'] + test['hist_purchase_amount_sum']\n\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f3af1c1d678504a3bc47086b534e883ad13fa130"},"cell_type":"code","source":"#Check for Missing Values after Concatination\n\nobs = train.isnull().sum().sort_values(ascending = False)\npercent = round(train.isnull().sum().sort_values(ascending = False)/len(train)*100, 2)\npd.concat([obs, percent], axis = 1,keys= ['Number of Observations', 'Percent'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"efc5cd49bd76cb506963252dcfe50e6b0ded18fa"},"cell_type":"code","source":"del train['first_active_month']\ndel test['first_active_month']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c3984e91daebcbcb25fad4b8b7fa938df45e1837"},"cell_type":"code","source":"train_id = train['card_id']\ntest_id = test['card_id']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2b0386190773c35dbc6ddadf77a5a39c9defb498"},"cell_type":"code","source":"del train['card_id']\ndel test['card_id']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8686ac9f6631eb4934b226de2ad49fecec59ad35"},"cell_type":"code","source":"train = train.apply(lambda x: x.fillna(x.median()))\ntest = test.apply(lambda x: x.fillna(x.median()))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4c92ba22cd59519370c2c07c901bc549cfb82b91"},"cell_type":"code","source":"train['card_id'] = train_id\ntest['card_id'] = test_id ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e5cbbffc6e63d46db77fdf034a026426f53b3204"},"cell_type":"code","source":"# Remove the Outliers if any \ntrain['outliers'] = 0\ntrain.loc[train['target'] < -30, 'outliers'] = 1\ntrain['outliers'].value_counts()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1c9663340c38c43f5d8af1b26f3369247df619e8"},"cell_type":"code","source":"train.shape, test.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2f1038a24ce9e75d74582e267aed339c1eadcaa4"},"cell_type":"code","source":"train.to_csv(\"train_processed2.csv\",index = False)\ntest.to_csv(\"test_processed2.csv\",index = False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"cb3e1a16a3be2854b2b36027c3b7f6f37e7aac0e"},"cell_type":"code","source":"#train = train.drop(['card_id', 'first_active_month'], axis = 1)\n#test = test.drop(['card_id', 'first_active_month'], axis = 1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8739ee68221009118aea9f5d76fd5c630324f5ac"},"cell_type":"code","source":"#train.category_1","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"dcc7bdf4e1f8fa47e4420bdec1880e2468ccacf3"},"cell_type":"code","source":"# Get the X and Y\n'''df_train_columns = [c for c in train.columns if c not in ['target','outliers']] \ntarget = train['target']\ndel train['target']'''","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bfdaaa55815b3f4c4b48dee203a78ec9e894cf11"},"cell_type":"code","source":"#len(df_train_columns)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d8dc99dc8c36e195c4d4ae66ace7e131479ef9fe"},"cell_type":"code","source":"test.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"f1a532f733c44c1d777e77dea366b725277bc244"},"cell_type":"code","source":"from sklearn.ensemble import RandomForestRegressor","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"a6556075b6fbd3cd94dd4b55b993cc12bf160d2c"},"cell_type":"code","source":"#model = RandomForestRegressor(n_estimators=1000, criterion = 'mse', random_state=1, n_jobs = -1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b83f5751e06f5bdbaf05127d4505859e83732c52"},"cell_type":"code","source":"#model.fit(train,target)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ee6b013e13bf91822e85443874fe6988fb0a9db6"},"cell_type":"code","source":"#prediction = model.predict(test)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"96728ec994f4ffbbde99a6c9be5680cb73818d34"},"cell_type":"code","source":"'''sample_submission = pd.read_csv('../input/sample_submission.csv')\nsample_submission['target'] = prediction\nsample_submission.to_csv('simple_RF.csv', index=False)'''","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}