{"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\n# Any results you write to the current directory are saved as output.","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":false,"trusted":true,"_uuid":"a556cd91cce6269062fea4d8203d15c51af6f58b"},"cell_type":"code","source":"def featurize_train_test(df):\n    # to datetime\n    df['first_active_month'] = pd.to_datetime(df['first_active_month'])\n\n    # datetime features\n    df['quarter'] = df['first_active_month'].dt.quarter\n    df['elapsed_time'] = (datetime.datetime.today() - df['first_active_month']).dt.days\n\n    df['days_feature1'] = df['elapsed_time'] * df['feature_1']\n    df['days_feature2'] = df['elapsed_time'] * df['feature_2']\n    df['days_feature3'] = df['elapsed_time'] * df['feature_3']\n\n    df['days_feature1_ratio'] = df['feature_1'] / df['elapsed_time']\n    df['days_feature2_ratio'] = df['feature_2'] / df['elapsed_time']\n    df['days_feature3_ratio'] = df['feature_3'] / df['elapsed_time']\n\n\n    df['feature_sum'] = df['feature_1'] + df['feature_2'] + df['feature_3']\n    df['feature_mean'] = df['feature_sum']/3\n    df['feature_max'] = df[['feature_1', 'feature_2', 'feature_3']].max(axis=1)\n    df['feature_min'] = df[['feature_1', 'feature_2', 'feature_3']].min(axis=1)\n    df['feature_var'] = df[['feature_1', 'feature_2', 'feature_3']].std(axis=1)\n\n    t1 = pd.get_dummies(df_train.feature_1, prefix = 'feature1')\n    t2 = pd.get_dummies(df_train.feature_2, prefix = 'feature2')\n    t3 = pd.get_dummies(df_train.feature_3, prefix = 'feature3')\n\n    df[t1.columns] = t1\n    df[t2.columns] = t2\n    df[t3.columns] = t3\n\n    del t1, t2, t3\n    gc.collect()\n\n    return df\n","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":false,"trusted":true,"_uuid":"c1ab86f10cb55f6809e903288bbb4c2752ba174b"},"cell_type":"code","source":"def Featurized(df1, prefix_string):\n    flag = 0\n    df1['authorized_flag'] = df1['authorized_flag'].map({'Y':1, \"N\":0})\n    df1['category_1'] = df1['category_1'].map({'Y':1, \"N\":0})\n\n    df1['category_2'].fillna(1.0,inplace=True)\n    df1['category_3'].fillna('A',inplace=True)\n    df1['merchant_id'].fillna('M_ID_00a6ca8a8a',inplace=True)\n    df1['installments'].replace(-1, np.nan,inplace=True)\n    df1['installments'].replace(999, np.nan,inplace=True)\n\n    gb = df1.groupby('card_id')\n\n    df_features = pd.DataFrame()\n\n    df_features['hist_count'] = gb['card_id'].count()\n\n    #authorized_flag\n    df_authorized_flag_count = df1.groupby('card_id')['authorized_flag'].value_counts().unstack()\n    df_authorized_flag_fraction = np.divide(df_authorized_flag_count, df_authorized_flag_count.sum(axis = 1).values.reshape(-1,1))\n    df_authorized_flag_count.columns = df_authorized_flag_count.columns.name +'__' +df_authorized_flag_count.columns.astype('str')+ '_count'\n    df_authorized_flag_fraction.columns = df_authorized_flag_fraction.columns.name +'__' +df_authorized_flag_fraction.columns.astype('str')+'_fraction'\n\n\n    # 'category_1'\n    df_category1_count = df1.groupby('card_id')['category_1'].value_counts().unstack()\n    df_category1_fraction = np.divide(df_category1_count, df_category1_count.sum(axis = 1).values.reshape(-1,1))\n    df_category1_count.columns = df_category1_count.columns.name +'__' +df_category1_count.columns.astype('str')+ '_count'\n    df_category1_fraction.columns = df_category1_fraction.columns.name +'__' +df_category1_fraction.columns.astype('str')+'_fraction'\n\n\n    # 'category_2'\n    df_category2_count = df1.groupby('card_id')['category_2'].value_counts().unstack()\n    df_category2_fraction = np.divide(df_category2_count, df_category2_count.sum(axis = 1).values.reshape(-1,1))\n    df_category2_count.columns = df_category2_count.columns.name +'__' +df_category2_count.columns.astype('str')+ '_count'\n    df_category2_fraction.columns = df_category2_fraction.columns.name +'__' +df_category2_fraction.columns.astype('str')+'_fraction'\n\n\n    # 'category_3'\n    df_category3_count = df1.groupby('card_id')['category_3'].value_counts().unstack()\n    df_category3_fraction = np.divide(df_category3_count, df_category3_count.sum(axis = 1).values.reshape(-1,1))\n    df_category3_count.columns = df_category3_count.columns.name +'__' +df_category3_count.columns.astype('str')+ '_count'\n    df_category3_fraction.columns =  df_category3_fraction.columns.name +'__' + df_category3_fraction.columns.astype('str')+'_fraction'\n\n\n    #'city_id'\n        #bin creation\n    city_id_count = df1.groupby('city_id')['city_id'].count()\n    np.log(city_id_count).hist()\n    bins = pd.qcut(np.log(city_id_count), 5, duplicates='drop')\n    df1['city_id_bins'] = df1['city_id'].map(bins)\n\n        #column creation (count and fraction)\n    df_city_id_count = df1.groupby('card_id')['city_id_bins'].value_counts().unstack()\n    df_city_id_fraction = np.divide(df_city_id_count, df_city_id_count.sum(axis = 1).values.reshape(-1,1))\n    df_city_id_count.columns = df_city_id_count.columns.name +'__' +df_city_id_count.columns.astype('str')+ '_count'\n    df_city_id_fraction.columns = df_city_id_fraction.columns.name +'__' + df_city_id_fraction.columns.astype('str')+'_fraction'\n\n\n    #'merchant_category_id'\n        #bin creation\n    merchant_category_id_count = df1.groupby('merchant_category_id')['merchant_category_id'].count()\n    np.log(merchant_category_id_count).hist()\n    bins = pd.qcut(np.log(merchant_category_id_count), 5,duplicates='drop')\n    df1['merchant_category_id_bins'] = df1['merchant_category_id'].map(bins)\n\n        #column creation (count and fraction)\n    df_merchant_category_id_count = df1.groupby('card_id')['merchant_category_id_bins'].value_counts().unstack()\n    df_merchant_category_id_fraction = np.divide(df_merchant_category_id_count, df_merchant_category_id_count.sum(axis = 1).values.reshape(-1,1))\n    df_merchant_category_id_count.columns = df_merchant_category_id_count.columns.name +'__' +df_merchant_category_id_count.columns.astype('str')+ '_count'\n    df_merchant_category_id_fraction.columns = df_merchant_category_id_fraction.columns.name +'__' + df_merchant_category_id_fraction.columns.astype('str')+'_fraction'\n\n\n    #'merchant_id'\n        #bin creation\n    merchant_id_count = df1.groupby('merchant_id')['merchant_id'].count()\n    np.log(merchant_id_count).hist()\n    bins = pd.qcut(np.log(merchant_id_count), 5,duplicates='drop')\n    df1['merchant_id_bins'] = df1['merchant_id'].map(bins)\n\n        #column creation (count and fraction)\n    df_merchant_id_count = df1.groupby('card_id')['merchant_id_bins'].value_counts().unstack()\n    df_merchant_id_fraction = np.divide(df_merchant_id_count, df_merchant_id_count.sum(axis = 1).values.reshape(-1,1))\n    df_merchant_id_count.columns = df_merchant_id_count.columns.name +'__' +df_merchant_id_count.columns.astype('str')+ '_count'\n    df_merchant_id_fraction.columns = df_merchant_id_fraction.columns.name +'__' + df_merchant_id_fraction.columns.astype('str')+'_fraction'\n\n\n    #'state_id'\n        #bin creation\n    state_id_count = df1.groupby('state_id')['state_id'].count()\n    np.log(state_id_count).hist()\n    bins = pd.qcut(np.log(state_id_count), 5,duplicates='drop')\n    df1['state_id_bins'] = df1['state_id'].map(bins)\n\n        #column creation (count and fraction)\n    df_state_id_count = df1.groupby('card_id')['state_id_bins'].value_counts().unstack()\n    df_state_id_fraction = np.divide(df_state_id_count, df_state_id_count.sum(axis = 1).values.reshape(-1,1))\n    df_state_id_count.columns = df_state_id_count.columns.name +'__' +df_state_id_count.columns.astype('str')+ '_count'\n    df_state_id_fraction.columns = df_state_id_fraction.columns.name +'__' + df_state_id_fraction.columns.astype('str')+'_fraction'\n\n\n    #'subsector_id'\n        #bin creation\n    subsector_id_count = df1.groupby('subsector_id')['subsector_id'].count()\n    np.log(subsector_id_count).hist()\n    bins = pd.qcut(np.log(subsector_id_count), 5, duplicates='drop')\n    df1['subsector_id_bins'] = df1['subsector_id'].map(bins)\n\n        #column creation (count and fraction)\n    df_subsector_id_count = df1.groupby('card_id')['subsector_id_bins'].value_counts().unstack()\n    df_subsector_id_fraction = np.divide(df_subsector_id_count, df_subsector_id_count.sum(axis = 1).values.reshape(-1,1))\n    df_subsector_id_count.columns = df_subsector_id_count.columns.name +'__' +df_subsector_id_count.columns.astype('str')+ '_count'\n    df_subsector_id_fraction.columns = df_subsector_id_fraction.columns.name +'__' + df_subsector_id_fraction.columns.astype('str')+'_fraction'\n\n\n    # 'installments', 'month_lag', 'purchase_amount'\n\n    Min = df1.groupby('card_id')[['installments', 'month_lag', 'purchase_amount']].min()\n    Min.columns = np.array(Min.columns)+'_mean'\n    Max = df1.groupby('card_id')[['installments', 'month_lag', 'purchase_amount']].max()\n    Max.columns = np.array(Max.columns)+'_max'\n    Median = df1.groupby('card_id')[['installments', 'month_lag', 'purchase_amount']].median()\n    Median.columns = np.array(Median.columns)+'_median'\n    Std = df1.groupby('card_id')[['installments', 'month_lag', 'purchase_amount']].std()\n    Std.columns = np.array(Std.columns)+'_std'\n    Skew = df1.groupby('card_id')[['installments', 'month_lag', 'purchase_amount']].skew()\n    Skew.columns = np.array(Skew.columns)+'_skew'\n    Mad =df1.groupby('card_id')[['installments', 'month_lag', 'purchase_amount']].mad()\n    Mad.columns = np.array(Mad.columns)+'_mad'\n    Sum =df1.groupby('card_id')[['installments', 'month_lag', 'purchase_amount']].sum()\n    Sum.columns = np.array(Sum.columns)+'_sum'\n\n\n    # 'purchase_date'\n\n    df_features[\"purchase_date_max\"] = df1.groupby('card_id')['purchase_date'].max()\n    df_features[\"purchase_date_min\"] = df1.groupby('card_id')['purchase_date'].min()\n\n    #df_features['first_buy'] = (df_features['purchase_date_min'] - df['first_active_month']).dt.days\n\n    df1['today_purchase_date'] =  pd.datetime.today() - pd.to_datetime(df1.purchase_date)\n    df1['purchase_date_month_diff'] = df1['today_purchase_date'].dt.total_seconds()/(3600*24*30) - df1.month_lag\n\n\n    gb = df1.groupby('card_id')['purchase_date_month_diff'].apply(sorted).apply(np.diff)\n\n    try:\n        mean = gb.apply(np.mean).rename(\"purchase_date_month_diff\"+'_mean')\n        median = gb.apply(np.median).rename(\"purchase_date_month_diff\"+'_median')\n        std = gb.apply(np.std).rename(\"purchase_date_month_diff\"+'_std')\n        max1 = gb.apply(np.max).rename(\"purchase_date_month_diff\"+'_max')\n        min1 = gb.apply(np.min).rename(\"purchase_date_month_diff\"+'_min')\n        sum1 = gb.apply(np.sum).rename(\"purchase_date_month_diff\"+'_sum')\n    except:\n        flag = 1\n\n    #=============================== Appending into One File ================\n\n    #pd.DataFrame(df_features['old_hist_count']).to_csv(\"appended.csv\", index=True)\n\n    List = [df_features['hist_count'], df_authorized_flag_count, df_authorized_flag_fraction, df_category1_count,df_category1_fraction,\n               df_category2_count, df_category2_fraction, df_category3_count, df_category3_fraction,\n               df_city_id_count, df_city_id_fraction, df_merchant_category_id_count, df_merchant_category_id_fraction,\n               df_merchant_id_count, df_merchant_id_fraction, df_state_id_count, df_state_id_fraction,\n               df_subsector_id_count, df_subsector_id_fraction, Min, Max, Median, Std, Skew, Mad, Sum]\n               \n    if(flag !=1):\n        List = List+[mean, median, std, max1, min1, sum1]    \n\n    df_concat = pd.concat(List, axis = 1, ignore_index=False)\n    df_concat.columns = prefix_string + np.array(df_concat.columns)\n\n    ##df_concat.to_csv(\"appended.csv\")\n\n    for i in range(1):\n        # datetime features\n        df1['purchase_date'] = pd.to_datetime(df1['purchase_date'])\n        df1['month'] = df1['purchase_date'].dt.month\n        df1['day'] = df1['purchase_date'].dt.day\n        df1['hour'] = df1['purchase_date'].dt.hour\n        df1['weekofyear'] = df1['purchase_date'].dt.weekofyear\n        df1['weekday'] = df1['purchase_date'].dt.weekday\n        df1['weekend'] = (df1['purchase_date'].dt.weekday >=5).astype(int)\n\n        # additional features\n        df1['price'] = df1['purchase_amount'] / df1['installments']\n\n        #Christmas : December 25 2017\n        df1['Christmas_Day_2017']=(pd.to_datetime('2017-12-25')-df1['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n        #Mothers Day: May 14 2017\n        df1['Mothers_Day_2017']=(pd.to_datetime('2017-06-04')-df1['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n        #fathers day: August 13 2017\n        df1['fathers_day_2017']=(pd.to_datetime('2017-08-13')-df1['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n        #Childrens day: October 12 2017\n        df1['Children_day_2017']=(pd.to_datetime('2017-10-12')-df1['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n        #Valentine's Day : 12th June, 2017\n        df1['Valentine_Day_2017']=(pd.to_datetime('2017-06-12')-df1['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n        #Black Friday : 24th November 2017\n        df1['Black_Friday_2017']=(pd.to_datetime('2017-11-24') - df1['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\n        df1['Mothers_Day_2018']=(pd.to_datetime('2018-05-13')-df1['purchase_date']).dt.days.apply(lambda x: x if x > 0 and x < 100 else 0)\n\n        df1['month_diff'] = ((datetime.datetime.today() - df1['purchase_date']).dt.days)//30\n        df1['month_diff'] += df1['month_lag']\n\n        # additional features\n        df1['duration'] = df1['purchase_amount']*df1['month_diff']\n        df1['amount_month_ratio'] = df1['purchase_amount']/df1['month_diff']\n\n    col_seas = ['month', 'hour', 'weekofyear', 'weekday', 'day']\n    aggs = {}\n    \n    for col in col_seas:\n        aggs[col] = ['nunique', 'mean', 'min', 'max']\n\n    for i in range(1):\n        aggs['purchase_date'] = ['max','min']\n        aggs['weekend'] = ['mean']\n        aggs['month'] = ['mean', 'min', 'max']\n        aggs['weekday'] = ['mean', 'min', 'max']\n        aggs['price'] = ['mean','max','min','var']\n        aggs['Christmas_Day_2017'] = ['mean']\n        aggs['Children_day_2017'] = ['mean']\n        aggs['Black_Friday_2017'] = ['mean']\n        aggs['Mothers_Day_2018'] = ['mean']\n        aggs['duration']=['mean','min','max','var','skew']\n        aggs['amount_month_ratio']=['mean','min','max','var','skew']\n\n\n    df_temp = df1.groupby('card_id').agg(aggs)\n\n    # change column name\n    df_temp.columns = pd.Index([e[0] + \"_\" + e[1] for e in df_temp.columns.tolist()])\n    df_temp.columns = [prefix_string+ c for c in df_temp.columns]\n\n    if(prefix_string =='old_'):\n        df_temp['old_purchase_date_diff'] = (df_temp['old_purchase_date_max']-df_temp['old_purchase_date_min']).dt.days\n        #df_temp['hist_purchase_date_average'] = df_temp['hist_purchase_date_diff']/df_temp['hist_card_id_size']\n        df_temp['old_purchase_date_uptonow'] = (datetime.datetime.today()-df_temp['old_purchase_date_max']).dt.days\n        df_temp['old_purchase_date_uptomin'] = (datetime.datetime.today()-df_temp['old_purchase_date_min']).dt.days\n\n    if(prefix_string =='new_'):\n        df_temp['new_purchase_date_diff'] = (df_temp['new_purchase_date_max']-df_temp['new_purchase_date_min']).dt.days\n        #df_temp['hist_purchase_date_average'] = df_temp['hist_purchase_date_diff']/df_temp['hist_card_id_size']\n        df_temp['new_purchase_date_uptonow'] = (datetime.datetime.today()-df_temp['new_purchase_date_max']).dt.days\n        df_temp['new_purchase_date_uptomin'] = (datetime.datetime.today()-df_temp['new_purchase_date_min']).dt.days\n\n    if(prefix_string =='old_new_'):\n        df_temp['old_new_purchase_date_diff'] = (df_temp['old_new_purchase_date_max']-df_temp['old_new_purchase_date_min']).dt.days\n        #df_temp['hist_purchase_date_average'] = df_temp['hist_purchase_date_diff']/df_temp['hist_card_id_size']\n        df_temp['old_new_purchase_date_uptonow'] = (datetime.datetime.today()-df_temp['old_new_purchase_date_max']).dt.days\n        df_temp['old_new_purchase_date_uptomin'] = (datetime.datetime.today()-df_temp['old_new_purchase_date_min']).dt.days\n\n\n    df_concat_new = pd.concat([df_concat,df_temp], axis = 1, ignore_index=False)\n\n    return df_concat_new\n\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"598f09f4b66a20c85679d8ba1e2821a64acaae84"},"cell_type":"code","source":"Path = \"../input/\"\nimport pandas as pd\nimport matplotlib.pyplot as plt\nimport numpy as np\nimport datetime\nfrom tqdm import tqdm\nimport gc\n\ndf1 = pd.read_csv(Path+\"historical_transactions.csv\")\ndf2 = pd.read_csv(Path+\"merchants.csv\")\ndf3 = pd.read_csv(Path+\"new_merchant_transactions.csv\")\ndf_train = pd.read_csv(Path+\"train.csv\")\ndf_test = pd.read_csv(Path+\"test.csv\")","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_uuid":"e340f730091910dc6050e59e28e49fa1c40f13ca"},"cell_type":"code","source":"#A = B\n\n#--- Feature Engineering ---------------\n\n##authorized_flag                2  (cat)    #done\n##card_id                   325540  (cat uid)   #done\n##city_id                      308   (cat)   #done\n##category_1                     2    (cat)   #done\n##installments                  15    (cat/numeric) #done\n##category_3                     3    (cat)     #done\n##merchant_category_id         327     (cat)    #done\n##merchant_id               326311      (cat)   #done\n##month_lag                     14      (cat/numeric)  #done\n##purchase_amount           215014     (numeric)      #done\n##purchase_date           16395300     (cat and numeric)\n##category_2                     5     (cat)      #done\n##state_id                      25      (cat)       #done\n##subsector_id                  41      (cat)       #done\n##dtype: int64\n\n\n\n##authorized_flag         object\n##card_id                 object\n##city_id                 int64\n##category_1              object\n##installments            int64\n##category_3              object\n##merchant_category_id    int64\n##merchant_id             object\n##month_lag               int64\n##purchase_amount         float64\n##purchase_date           object\n##category_2              float64\n##state_id                int64\n##subsector_id            int64\n","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","collapsed":true,"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":false},"cell_type":"code","source":"\n#df3.to_csv(\"old_and_new.csv\", mode='a', columns=False, index=False)\n\nColumns  = ['authorized_flag', 'card_id', 'city_id', 'category_1', 'installments',\n       'category_3', 'merchant_category_id', 'merchant_id', 'month_lag',\n       'purchase_amount', 'purchase_date', 'category_2', 'state_id',\n       'subsector_id']\n\nprint(\"This takes more than the kernal time limit Do run on your PC\")\nprint(\"Or may be try to optimize the Code\")\nprint(\"Output files are shared below\")\n# \nprint(\"https://www.kaggle.com/mks2192/elo-features-data-7-feb\")\n\nA = B #Exiting the code\n\ndf_old = Featurized(df1, 'old_')\ndf_new = Featurized(df3, 'new_')\n\ndel df1, df3\ngc.collect()\n\n# for i in range(1):\n#     temp = pd.read_csv(\"old_and_new.csv\")\n#     df_appended = Featurized(temp, 'old_new_')\n\ndf_old_new = pd.concat([df_old,df_new], axis = 1, ignore_index=False)\ndf_old_new = df_old_new.reset_index().rename(columns = {'index':'card_id'})\n\n#================ Featurized train and test =================\n\ntrain = featurize_train_test(df_train)\ntest = featurize_train_test(df_test)\n\n#================================== train and test ==========================\n\ntrain_df = pd.merge(df_old_new, train , on ='card_id')\ntest_df = pd.merge(df_old_new, test , on ='card_id')\n\n#============================================================================\ntrain_df.to_csv(\"train.csv\")\ntest_df.to_csv(\"test.csv\")\n","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}