{"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)\nimport matplotlib.pyplot as plt\n%matplotlib inline\n\nfrom keras.models import Model\nfrom keras.layers import *\nfrom keras.callbacks import *\nfrom keras.regularizers import l2\nfrom keras.optimizers import *\nfrom keras.utils import to_categorical\nimport datetime\nfrom sklearn.preprocessing import LabelEncoder\nimport gc\nfrom sklearn.model_selection import train_test_split\nimport lightgbm as lgb\nfrom sklearn.metrics import mean_squared_error\nfrom sklearn.model_selection import KFold,StratifiedKFold\nfrom keras import backend as K\nfrom sklearn.model_selection import KFold\n\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":{"_uuid":"86cae6aa917478a27cd397d7dcc949447e696e5e"},"cell_type":"markdown","source":"# Define global functions"},{"metadata":{"trusted":true,"_uuid":"4f4a12768f717d1dbf1671bdae5021816b60dd1e"},"cell_type":"code","source":"# define function to reduce memory usage\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":{"_uuid":"d9f7ce14f675a7fb562e70187ca409b14db27d3e"},"cell_type":"markdown","source":"# Read data"},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true,"scrolled":false},"cell_type":"code","source":"train_set = pd.read_csv(\"../input/train.csv\", parse_dates=[\"first_active_month\"])\ntest_set = pd.read_csv(\"../input/test.csv\", parse_dates=[\"first_active_month\"])\nhistory_trx = pd.read_csv(\"../input/historical_transactions.csv\", parse_dates=['purchase_date'])\nnew_trx = pd.read_csv(\"../input/new_merchant_transactions.csv\", parse_dates=['purchase_date'])\nmerchants_set = pd.read_csv(\"../input/merchants.csv\")\n\nprint(\"shape of train : \",train_set.shape)\nprint(\"shape of test : \",test_set.shape)\nprint(\"shape of history_trx : \",history_trx.shape)\nprint(\"shape of new_trx : \",new_trx.shape)\nprint(\"shape of merchants : \",merchants_set.shape)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"37ff431c771106e9f91cb028d1bb26f798f919b8"},"cell_type":"code","source":"merchants_set.drop_duplicates(subset=['merchant_id'], keep='first', inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4bdb9744aa760f20ff42eef26f9a8e037e4ee955"},"cell_type":"markdown","source":"# Feature extrection - collect information per card to form card_id profile"},{"metadata":{"trusted":true,"_uuid":"e1ae4cd8e747a22cd1cc955179713c2a199a1a80"},"cell_type":"code","source":"# add 'year', 'month', and 'elepsed_time' features to the dataframe\nfor df in [train_set, test_set]:\n    df['year'] = df['first_active_month'].dt.year\n    df['month'] = df['first_active_month'].dt.month\n    df['elapsed_time'] = (datetime.date(2018, 2, 1) - df['first_active_month'].dt.date).dt.days # 1/2/2018 is the max date in train set\n    \n# create set of columns name that is numeric\nnumeric_col = ['elapsed_time']\n\n# split the train set to features and target\ntarget = train_set['target']\ndel train_set['target']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5859f4a10f1d863b68f384becbbd863e6946960c"},"cell_type":"code","source":"def get_top_merchants(trx_data):\n    num_trx_per_mer = trx_data.groupby(['card_id', 'merchant_id'])['authorized_flag'].agg(['count'])\n    num_trx_per_mer.reset_index(inplace=True)\n    num_trx_per_mer = num_trx_per_mer.sort_values('count', ascending=False).drop_duplicates(['card_id'], keep='first')\n    num_trx_per_mer = num_trx_per_mer[['card_id', 'merchant_id']]\n    return num_trx_per_mer\n\nhistory_top_merchants = get_top_merchants(history_trx)\nnew_top_merchants = get_top_merchants(new_trx)\n\nhistory_top_merchants.rename(index=str, columns={\"merchant_id\": \"history_top_merchants\"}, inplace=True)\nnew_top_merchants.rename(index=str, columns={\"merchant_id\": \"new_top_merchants\"}, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4a9e29acf21594afc5f582033f3300f9bfd879ab"},"cell_type":"code","source":"train_set = pd.merge(train_set, history_top_merchants, on='card_id', how='left')\ntest_set = pd.merge(test_set, history_top_merchants, on='card_id', how='left')\n\ntrain_set = pd.merge(train_set, new_top_merchants, on='card_id', how='left')\ntest_set = pd.merge(test_set, new_top_merchants, on='card_id', how='left')\n\ntrain_set = pd.merge(train_set, merchants_set, left_on='history_top_merchants',right_on='merchant_id', how='left')\ntest_set = pd.merge(test_set, merchants_set, left_on='history_top_merchants',right_on='merchant_id', how='left')\n\ntrain_set.drop(['merchant_id','history_top_merchants', 'new_top_merchants'], axis=1, inplace=True)\ntest_set.drop(['merchant_id','history_top_merchants', 'new_top_merchants'], axis=1, inplace=True)\n\ndel merchants_set\ngc.collect()\n\ntrain_set = reduce_mem_usage(train_set)\ntest_set = reduce_mem_usage(test_set)\n\ndel history_top_merchants\ndel new_top_merchants\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"531ab3c6752bd71966845bc98cb46c8c194ef322"},"cell_type":"code","source":"# define aggregation function on card_id that collect information from data features to crate profile to each card_id\ndef agg_data_trx(trx_data, col_name):\n    \n    trx_data['authorized_flag'] = trx_data['authorized_flag'].map({'Y':1, 'N':0})\n    \n    trx_data['purchase_month'] = trx_data['purchase_date'].dt.month\n    \n    trx_data['month_diff'] = ((datetime.datetime.today() - trx_data['purchase_date']).dt.days)//30\n    trx_data['month_diff'] += trx_data['month_lag']\n    \n    trx_data = reduce_mem_usage(trx_data)\n    \n    trx_data.loc[:, 'purchase_date'] = pd.DatetimeIndex(trx_data['purchase_date']).astype(np.int64) * 1e-9\n    \n    agg_func = {\n        'merchant_id': ['nunique'],\n        'merchant_category_id': ['nunique'],\n        'state_id': ['nunique'],\n        'city_id': ['nunique'],\n        'subsector_id': ['nunique'],\n        'purchase_amount': ['sum', 'mean', 'max', 'min', 'std'],\n        'installments': ['sum', 'mean', 'max', 'min', 'std'],\n        'purchase_date': [np.ptp, 'min', 'max'],\n        'month_lag': ['mean', 'max', 'min', 'std'],\n        'month_diff': ['mean']\n    }\n    \n    agg_data = trx_data.groupby(['card_id']).agg(agg_func)\n    agg_data.columns = [col_name + '_' + '_'.join(col).strip() for col in agg_data.columns.values]\n    agg_data.reset_index(inplace=True)\n    \n    df = (trx_data.groupby('card_id').size().reset_index(name=col_name + '_trx_count'))\n    \n    agg_data = pd.merge(df, agg_data, on='card_id', how='left')\n    \n    agg_numeric_col = [col for col in agg_data.columns if col not in ['card_id']]\n    numeric_col.extend(agg_numeric_col)\n    \n    return agg_data\n\nauthorized_trx = history_trx[history_trx['authorized_flag'] == 'Y']\nhistory_trx = history_trx[history_trx['authorized_flag'] == 'N']\n\nhistory_trx_per_card = agg_data_trx(history_trx, 'history')\nauthorized_trx_per_card = agg_data_trx(authorized_trx, 'auto')\nnew_trx_per_card = agg_data_trx(new_trx, 'new')\n\n# merge the new features for each card_id with the 3 basic features in train set and test set\ntrain_set = pd.merge(train_set, history_trx_per_card, on='card_id', how='left')\ntest_set = pd.merge(test_set, history_trx_per_card, on='card_id', how='left')\n\ntrain_set = pd.merge(train_set, authorized_trx_per_card, on='card_id', how='left')\ntest_set = pd.merge(test_set, authorized_trx_per_card, on='card_id', how='left')\n\ntrain_set = pd.merge(train_set, new_trx_per_card, on='card_id', how='left')\ntest_set = pd.merge(test_set, new_trx_per_card, on='card_id', how='left')\n\n# delete unnecessary dataframes to reduce memory usage\ndel history_trx_per_card\ndel new_trx_per_card\ndel authorized_trx_per_card\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"1cabaeb46414e1114690beb015aa98275a37d027"},"cell_type":"code","source":"# define aggregation function on card_id and month_lag that collect information from data features to crate profile to each card_id\n\ndef aggregate_per_month(history):\n    grouped = history.groupby(['card_id', 'month_lag'])\n\n    agg_func = {\n            'purchase_amount': ['count', 'sum', 'mean', 'min', 'max', 'std'],\n            'installments': ['count', 'sum', 'mean', 'min', 'max', 'std'],\n            }\n\n    intermediate_group = grouped.agg(agg_func)\n    intermediate_group.columns = ['_'.join(col).strip() for col in intermediate_group.columns.values]\n    intermediate_group.reset_index(inplace=True)\n\n    final_group = intermediate_group.groupby('card_id').agg(['mean', 'std'])\n    final_group.columns = ['_'.join(col).strip() for col in final_group.columns.values]\n    final_group.reset_index(inplace=True)\n    \n    return final_group\n\nfinal_group =  aggregate_per_month(authorized_trx)\n\ntrain_set = pd.merge(train_set, final_group, on='card_id', how='left')\ntest_set = pd.merge(test_set, final_group, on='card_id', how='left')\n\ndel final_group\ndel authorized_trx\ngc.collect()\n\ntrain_set = reduce_mem_usage(train_set)\ntest_set = reduce_mem_usage(test_set)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"8b3a96c7e33b8d67d19600e0020a8718b5cb1f0d"},"cell_type":"markdown","source":"# define successive aggregation function on diffrent variables that collect information from data features to find corelations to \n# purchase_amount and installments\n\ndef successive_aggregates(df, field1, field2):\n    t = df.groupby(['card_id', field1])[field2].mean()\n    u = pd.DataFrame(t).reset_index().groupby('card_id')[field2].agg(['mean', 'min', 'max', 'std'])\n    u.columns = [field1 + '_' + field2 + '_' + col for col in u.columns.values]\n    u.reset_index(inplace=True)\n    return u\n\n\nnew_trx['category_1'] = new_trx['category_1'].map({'Y':1, 'N':0})\nadditional_fields = successive_aggregates(new_trx, 'category_1', 'purchase_amount')\nadditional_fields = additional_fields.merge(successive_aggregates(new_trx, 'installments', 'purchase_amount'),on = 'card_id', how='left')\nadditional_fields = additional_fields.merge(successive_aggregates(new_trx, 'city_id', 'purchase_amount'),on = 'card_id', how='left')\nadditional_fields = additional_fields.merge(successive_aggregates(new_trx, 'category_1', 'installments'),on = 'card_id', how='left')\n\ntrain_set = pd.merge(train_set, additional_fields, on='card_id', how='left')\ntest_set = pd.merge(test_set, additional_fields, on='card_id', how='left')\n\ndel additional_fields\ngc.collect()\n\ntrain_set = reduce_mem_usage(train_set)\ntest_set = reduce_mem_usage(test_set)"},{"metadata":{"_uuid":"281b90ed6602a940fac40094a310abbb6ed28b5c"},"cell_type":"markdown","source":"# Write train and test sets for future use"},{"metadata":{"trusted":true,"_uuid":"6b5c30df1f3fc70b60de8261c2b13fa58ac0b0e0"},"cell_type":"code","source":"train_set.to_csv('train_set.csv', index=False)\ntest_set.to_csv('test_set.csv', index=False)\ntarget.to_csv('target.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}