{"cells":[{"metadata":{"_uuid":"84b175b056dabbaea0e377dab355351dca5b03f4"},"cell_type":"markdown","source":"The general workflow is:\n\n1. Fill NaN with most common value for that column \n2. Take the historical / new files, aggregate each column using min, mean, max, standard deviation, count. (including categorical variables which proved to be important)\n3. A good chunk of the train dataset doesn't have rows for the new files but lightgbm can handle this.  I use a StratifiedKFold of 5 folds to help smooth out predictions for test set.\n4. Important note for CV vs LB which I learned in the Kaggle forums: drop the columns that don't have a similar distribution of values between the train and test files.  This will cause your predictions on test to be well-prepared by the train data set.\n4. The features that stand out the most are the ones that represent how much they paid and when they committed those transactions.  I've tried layering on more features as this competition has gone on and some of them have added value to my score. \n5.  There's a LOT of crazy outliers (10x standard deviations -> -31 value) that obviously blow out the end RMSE.  If I train just on the non-outliers, my score is in the mid 1's (1.4 ish), but since we have to predict these outliers too, it gets blown out to 3.735.  Whoever can best predict outliers will win this competition, plain and simple.  I tried out doing a classifier on whether we could predict if an outlier exists, but it didn't help my end RMSE (even though the AUC was ~.8).  Curious if anyone had success adding a classifier on the -31 outliers.\n\nIn addition a challenge for this dataset was the size of the data using 16GB of RAM on Kaggle environment.  Just importing the data takes >1 minute.  I used some tactics like pandas .sample() to more efficiently move the data through the pipeline."},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":true},"cell_type":"code","source":"import numpy as np\nimport pandas as pd\npd.options.display.max_rows = 999\n\nimport datetime\nimport gc","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"bfd1d829c986eac6b8874f0374855dbb2a6907f2"},"cell_type":"code","source":"#credit to Ashish Gupta for sharing this function: https://www.kaggle.com/roydatascience/elo-stack-interactions-on-categorical-variables\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":{"trusted":true,"_uuid":"c08160cea8aa2f2e6b3eeb9c4618b8178755bee5"},"cell_type":"code","source":"historical_transactions = reduce_mem_usage(pd.read_csv('../input/historical_transactions.csv'))\ntrain = reduce_mem_usage(pd.read_csv('../input/train.csv'))\ntest = reduce_mem_usage(pd.read_csv('../input/test.csv'))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"de5c7ad9aef713b5026e4b072e287ce9efadfe19"},"cell_type":"markdown","source":"# historical_transactions = 29mm rows / 325K card_ids\n# new_merchant_transactions = 1.96mm rows\n# train = 201K card_ids\n# test = 123K card_ids\n\n# 573  / 2,207 outliers don't have row in new_merchant_transactions\n# 21,931 / 201K card_ids in train don't have row in new_merchant_transactions\n"},{"metadata":{"trusted":true,"_uuid":"9e27283d2d7b7ae4a1421d2039d7a9db5130cd68"},"cell_type":"code","source":"transx = pd.merge(\n    historical_transactions,\n    train['card_id'].to_frame(),\n    on = 'card_id',\n    how = 'inner'\n)\n\ntest_transx = pd.merge(\n    test['card_id'].to_frame(),\n    historical_transactions,\n    on = 'card_id',\n    how = 'inner'\n)\n\ndel historical_transactions\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"823b3e480b568d6a4c435238f50bca2ffb2b5964"},"cell_type":"markdown","source":"# historical_transactions clean up"},{"metadata":{"trusted":true,"_uuid":"407b7f6df026e174dab58b4a957737c7c31c8d78"},"cell_type":"code","source":"transx['nan_merchant_id'] = 0\ntransx.loc[transx.merchant_id.isnull(), 'nan_merchant_id'] = 1\ntransx.loc[transx.merchant_id.isnull(), 'merchant_id'] = 'M_ID_00a6ca8a8a'\n\ntransx['nan_category_3'] = 0\ntransx.loc[transx.category_3.isnull(), 'nan_category_3'] = 1\ntransx.loc[transx.category_3.isnull(), 'category_3'] = 'A'\n\ntransx['nan_category_2'] = 0\ntransx.loc[transx.category_2.isnull(), 'nan_category_2'] = 1\ntransx.loc[transx.category_2.isnull(), 'category_2'] = 1.0\n\ntransx['category_1'] = transx['category_1'].map({'Y': 1, 'N': 0})\ntransx['authorized_flag'] = transx['authorized_flag'].map({'Y': 1, 'N': 0})\ntransx['category_3'] = transx['category_3'].map({'A': 0, 'B': 1, 'C': 2})\n\ntransx['exec_date'] = pd.to_datetime(transx['purchase_date'], format = '%Y%m%d %H:%M:%S')\ntransx['month'] = pd.DatetimeIndex(transx['exec_date']).month\ntransx['year'] = pd.DatetimeIndex(transx['exec_date']).year\ntransx['day'] = pd.DatetimeIndex(transx['exec_date']).day\ntransx['day_of_year'] = pd.DatetimeIndex(transx['exec_date']).dayofyear\ntransx['day_of_week'] = pd.DatetimeIndex(transx['exec_date']).dayofweek\ntransx['is_month_start'] = (pd.DatetimeIndex(transx['exec_date']).is_month_start).astype(int)\ntransx['is_month_end'] = (pd.DatetimeIndex(transx['exec_date']).is_month_end).astype(int)\ntransx['is_weekend'] = (pd.DatetimeIndex(transx['exec_date']).dayofweek >= 5).astype(int)\ntransx['is_weekday'] = (pd.DatetimeIndex(transx['exec_date']).dayofweek < 5).astype(int)\ntransx['weekday'] = pd.DatetimeIndex(transx['exec_date']).weekday\ntransx['week_of_year'] = pd.DatetimeIndex(transx['exec_date']).weekofyear\ntransx['days_since_purchase'] = (datetime.datetime.today() - transx['exec_date']).dt.days\ntransx['quarter'] = pd.DatetimeIndex(transx['exec_date']).quarter\ntransx['hour'] = pd.DatetimeIndex(transx['exec_date']).hour\ntransx['months_since_purchase'] = (((datetime.datetime.today() - transx['exec_date']).dt.days) / 30) + transx['month_lag']\ntransx['duration'] = transx['purchase_amount'] * transx['months_since_purchase']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"7f1c713809ca8eb14718ac9c361cf824a5250ff1"},"cell_type":"code","source":"test_transx['nan_merchant_id'] = 0\ntest_transx.loc[test_transx.merchant_id.isnull(), 'nan_merchant_id'] = 1\ntest_transx.loc[test_transx.merchant_id.isnull(), 'merchant_id'] = 'M_ID_00a6ca8a8a'\n\ntest_transx['nan_category_3'] = 0\ntest_transx.loc[test_transx.category_3.isnull(), 'nan_category_3'] = 1\ntest_transx.loc[test_transx.category_3.isnull(), 'category_3'] = 'A'\n\ntest_transx['nan_category_2'] = 0\ntest_transx.loc[test_transx.category_2.isnull(), 'nan_category_2'] = 1\ntest_transx.loc[test_transx.category_2.isnull(), 'category_2'] = 1.0\n\ntest_transx['category_1'] = test_transx['category_1'].map({'Y': 1, 'N': 0})\ntest_transx['authorized_flag'] = test_transx['authorized_flag'].map({'Y': 1, 'N': 0})\ntest_transx['category_3'] = test_transx['category_3'].map({'A': 0, 'B': 1, 'C': 2})\n\ntest_transx['exec_date'] = pd.to_datetime(test_transx['purchase_date'], format = '%Y%m%d %H:%M:%S')\ntest_transx['month'] = pd.DatetimeIndex(test_transx['exec_date']).month\ntest_transx['year'] = pd.DatetimeIndex(test_transx['exec_date']).year\ntest_transx['day'] = pd.DatetimeIndex(test_transx['exec_date']).day\ntest_transx['day_of_year'] = pd.DatetimeIndex(test_transx['exec_date']).dayofyear\ntest_transx['day_of_week'] = pd.DatetimeIndex(test_transx['exec_date']).dayofweek\ntest_transx['is_month_start'] = (pd.DatetimeIndex(test_transx['exec_date']).is_month_start).astype(int)\ntest_transx['is_month_end'] = (pd.DatetimeIndex(test_transx['exec_date']).is_month_end).astype(int)\ntest_transx['is_weekend'] = (pd.DatetimeIndex(test_transx['exec_date']).dayofweek >= 5).astype(int)\ntest_transx['is_weekday'] = (pd.DatetimeIndex(test_transx['exec_date']).dayofweek < 5).astype(int)\ntest_transx['weekday'] = pd.DatetimeIndex(test_transx['exec_date']).weekday\ntest_transx['week_of_year'] = pd.DatetimeIndex(test_transx['exec_date']).weekofyear\ntest_transx['days_since_purchase'] = (datetime.datetime.today() - test_transx['exec_date']).dt.days\ntest_transx['quarter'] = pd.DatetimeIndex(test_transx['exec_date']).quarter\ntest_transx['hour'] = pd.DatetimeIndex(test_transx['exec_date']).hour\ntest_transx['months_since_purchase'] = (((datetime.datetime.today() - test_transx['exec_date']).dt.days) / 30) + test_transx['month_lag']\ntest_transx['duration'] = test_transx['purchase_amount'] * test_transx['months_since_purchase']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b18c07573c0bb99ea0e82cce98d64c7551a1d5b4"},"cell_type":"code","source":"agg_inputs = {\n    'card_id' : ['nunique', 'size'],\n    'exec_date' : ['min', 'max'],\n    'city_id' : ['nunique'], \n    'installments' : ['mean', 'max', 'min', 'var', 'std', 'sum'],\n    'merchant_category_id' : ['nunique'], \n    'month_lag' : ['mean', 'max', 'min', 'var', 'std', 'sum'],\n    'purchase_amount' : ['mean', 'max', 'min', 'var', 'std', 'sum'], \n    'category_2': ['nunique', 'mean'],\n    'state_id' : ['nunique'],\n    'subsector_id' : ['nunique'], \n    'nan_merchant_id' : ['nunique', 'mean', 'sum'], \n    'nan_category_3': ['nunique', 'mean', 'sum'], \n    'nan_category_2': ['nunique', 'mean', 'sum'],\n    'authorized_flag': ['sum', 'mean'],\n    'category_1' : ['nunique', 'mean', 'sum'],\n    'category_3': ['nunique', 'mean', 'sum'],\n    'month': ['mean', 'max', 'min', 'var', 'std', 'nunique'], \n    'year': ['mean', 'max', 'min', 'var', 'std', 'nunique'], \n    'day': ['mean', 'max', 'min', 'var', 'std', 'nunique'], \n    'weekday': ['mean', 'max', 'min', 'var', 'std', 'nunique'], \n    'week_of_year': ['mean', 'max', 'min', 'var', 'std', 'nunique'],\n    'day_of_year': ['mean', 'max', 'min', 'var', 'std', 'nunique'],\n    'day_of_week': ['mean', 'max', 'min', 'var', 'std', 'nunique'],\n    'months_since_purchase' : ['mean', 'max', 'min', 'var', 'std', 'sum'],\n    'quarter' : ['mean', 'max', 'min', 'var', 'std', 'nunique'],\n    'hour' : ['mean', 'max', 'min', 'var', 'std', 'nunique'],\n    'is_month_start': ['mean', 'sum'],\n    'is_month_end': ['mean', 'sum'],\n    'is_weekend': ['mean', 'sum'],\n    'is_weekday': ['mean', 'sum'],\n    'duration' : ['mean', 'max', 'min', 'var', 'std', 'sum']\n}","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c82befab43a8e57b87185666d25a8469d82de6e9"},"cell_type":"code","source":"transx_staging = transx[\n    [\n        'card_id', \n        'exec_date',\n        'city_id', \n        'installments',\n        'merchant_category_id', \n        'month_lag',\n        'purchase_amount', \n        'category_2', \n        'state_id',\n        'subsector_id', \n        'nan_merchant_id', \n        'nan_category_3', \n        'nan_category_2',\n        'authorized_flag',\n        'category_1',\n        'category_3', \n        'month', \n        'year', \n        'day', \n        'weekday', \n        'week_of_year',\n        'day_of_year',\n        'day_of_week',\n        'days_since_purchase',\n        'is_month_start',\n        'is_month_end',\n        'is_weekend',\n        'is_weekday',\n        'quarter',\n        'hour',\n        'months_since_purchase',\n        'duration'\n    ]\n]\n\ndel transx\ngc.collect()\n\ntransx_staging = transx_staging \\\n    .groupby('card_id') \\\n    .agg(agg_inputs) \\\n    .reset_index()\n\ntransx_staging.columns = [\n    'h_t_' + '_'.join(col).strip() \n        for col in transx_staging.columns.values\n]\n\ntransx_staging = transx_staging.rename(columns={transx_staging.columns[0] : 'card_id'})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b8dca2e7354414d8f35ed14c1fc20590efb97208"},"cell_type":"code","source":"test_transx_staging = test_transx[\n    [\n        'card_id',\n        'exec_date',\n        'city_id',\n        'installments',\n        'merchant_category_id', \n        'month_lag',\n        'purchase_amount', \n        'category_2', \n        'state_id',\n        'subsector_id', \n        'nan_merchant_id', \n        'nan_category_3', \n        'nan_category_2',\n        'authorized_flag',\n        'category_1',\n        'category_3', \n        'month', \n        'year', \n        'day', \n        'weekday', \n        'week_of_year',\n        'day_of_year',\n        'day_of_week',\n        'days_since_purchase',\n        'is_month_start',\n        'is_month_end',\n        'is_weekend',\n        'is_weekday',\n        'quarter',\n        'hour',\n        'months_since_purchase',\n        'duration'\n    ]\n]\n\ndel test_transx\ngc.collect()\n\ntest_transx_staging = test_transx_staging \\\n    .groupby('card_id') \\\n    .agg(agg_inputs) \\\n    .reset_index()\n\ntest_transx_staging.columns = [\n    'h_t_' + '_'.join(col).strip() \n        for col in test_transx_staging.columns.values\n]\n\ntest_transx_staging = test_transx_staging.rename(columns={test_transx_staging.columns[0] : 'card_id'})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6998602af8f07eb9a43224b02fe014946add35c5"},"cell_type":"code","source":"train = pd.merge(\n    train,\n    transx_staging,\n    on = 'card_id',\n    how = 'left'\n)\n\ntrain['first_purch'] = pd.to_datetime(train['first_active_month'], format = '%Y%m%d %H:%M:%S')\ntrain['first_month'] = pd.DatetimeIndex(train['first_purch']).month\ntrain['first_year'] = pd.DatetimeIndex(train['first_purch']).year\ntrain['days'] = pd.DatetimeIndex(train['first_purch']).day\ntrain['first_quarter'] = pd.DatetimeIndex(train['first_purch']).quarter\ntrain['first_week'] = pd.DatetimeIndex(train['first_purch']).weekofyear\ntrain['first_day_of_week'] = pd.DatetimeIndex(train['first_purch']).dayofweek\n\ntrain['days_feature1'] = train['days'] * train['feature_1']\ntrain['days_feature2'] = train['days'] * train['feature_2']\ntrain['days_feature3'] = train['days'] * train['feature_3']\n\ntest = pd.merge(\n    test,\n    test_transx_staging,\n    on = 'card_id',\n    how = 'left'\n)\n\ntest['first_purch'] = pd.to_datetime(test['first_active_month'], format = '%Y%m%d %H:%M:%S')\ntest['first_month'] = pd.DatetimeIndex(test['first_purch']).month\ntest['first_year'] = pd.DatetimeIndex(test['first_purch']).year\ntest['days'] = pd.DatetimeIndex(test['first_purch']).day\ntest['first_quarter'] = pd.DatetimeIndex(test['first_purch']).quarter\ntest['first_week'] = pd.DatetimeIndex(test['first_purch']).weekofyear\ntest['first_day_of_week'] = pd.DatetimeIndex(test['first_purch']).dayofweek\n\ntest['days_feature1'] = test['days'] * train['feature_1']\ntest['days_feature2'] = test['days'] * train['feature_2']\ntest['days_feature3'] = test['days'] * train['feature_3']\n\ndel [\n    test_transx_staging,\n    transx_staging\n]\n\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b9b65545cb1fe00edbe3ce65f8a3f8aed419ab13"},"cell_type":"code","source":"new_merchant_transactions = reduce_mem_usage(pd.read_csv('../input/new_merchant_transactions.csv'))\n\nnew_transx = pd.merge(\n    new_merchant_transactions,\n    train['card_id'].to_frame(),\n    on = 'card_id',\n    how = 'inner'\n)\n\ntest_new_transx = pd.merge(\n    test['card_id'].to_frame(),\n    new_merchant_transactions,\n    on = 'card_id',\n    how = 'inner'\n)\n\ndel new_merchant_transactions\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"4e64b2ff605b1153fdf64b5c1fb8f956f5560748"},"cell_type":"markdown","source":"# new_merchant_transactions clean up"},{"metadata":{"trusted":true,"_uuid":"3fb8e39a6f5bec3d13d45a944821b25c3678c588"},"cell_type":"code","source":"new_transx['nan_merchant_id'] = 0\nnew_transx.loc[new_transx.merchant_id.isnull(), 'nan_merchant_id'] = 1\nnew_transx.loc[new_transx.merchant_id.isnull(), 'merchant_id'] = 'M_ID_00a6ca8a8a'\n\nnew_transx['nan_category_3'] = 0\nnew_transx.loc[new_transx.category_3.isnull(), 'nan_category_3'] = 1\nnew_transx.loc[new_transx.category_3.isnull(), 'category_3'] = 'A'\n\nnew_transx['nan_category_2'] = 0\nnew_transx.loc[new_transx.category_2.isnull(), 'nan_category_2'] = 1\nnew_transx.loc[new_transx.category_2.isnull(), 'category_2'] = 1.0\n\nnew_transx['category_1'] = new_transx['category_1'].map({'Y': 1, 'N': 0})\nnew_transx['authorized_flag'] = new_transx['authorized_flag'].map({'Y': 1, 'N': 0})\nnew_transx['category_3'] = new_transx['category_3'].map({'A': 0, 'B': 1, 'C': 2})\n\nnew_transx['exec_date'] = pd.to_datetime(new_transx['purchase_date'], format = '%Y%m%d %H:%M:%S')\nnew_transx['month'] = pd.DatetimeIndex(new_transx['exec_date']).month\nnew_transx['year'] = pd.DatetimeIndex(new_transx['exec_date']).year\nnew_transx['day'] = pd.DatetimeIndex(new_transx['exec_date']).day\nnew_transx['day_of_year'] = pd.DatetimeIndex(new_transx['exec_date']).dayofyear\nnew_transx['day_of_week'] = pd.DatetimeIndex(new_transx['exec_date']).dayofweek\nnew_transx['is_month_start'] = (pd.DatetimeIndex(new_transx['exec_date']).is_month_start).astype(int)\nnew_transx['is_month_end'] = (pd.DatetimeIndex(new_transx['exec_date']).is_month_end).astype(int)\nnew_transx['is_weekend'] = (pd.DatetimeIndex(new_transx['exec_date']).dayofweek >= 5).astype(int)\nnew_transx['is_weekday'] = (pd.DatetimeIndex(new_transx['exec_date']).dayofweek < 5).astype(int)\nnew_transx['weekday'] = pd.DatetimeIndex(new_transx['exec_date']).weekday\nnew_transx['week_of_year'] = pd.DatetimeIndex(new_transx['exec_date']).weekofyear\nnew_transx['quarter'] = pd.DatetimeIndex(new_transx['exec_date']).quarter\nnew_transx['hour'] = pd.DatetimeIndex(new_transx['exec_date']).hour\nnew_transx['days_since_purchase'] = (datetime.datetime.today() - new_transx['exec_date']).dt.days\nnew_transx['months_since_purchase'] = (((datetime.datetime.today() - new_transx['exec_date']).dt.days) / 30) + new_transx['month_lag']\nnew_transx['duration'] = new_transx['purchase_amount'] * new_transx['months_since_purchase']","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"6044471f5383274c390745ef362479132f32f70c"},"cell_type":"code","source":"test_new_transx['nan_merchant_id'] = 0\ntest_new_transx.loc[test_new_transx.merchant_id.isnull(), 'nan_merchant_id'] = 1\ntest_new_transx.loc[test_new_transx.merchant_id.isnull(), 'merchant_id'] = 'M_ID_00a6ca8a8a'\n\ntest_new_transx['nan_category_3'] = 0\ntest_new_transx.loc[test_new_transx.category_3.isnull(), 'nan_category_3'] = 1\ntest_new_transx.loc[test_new_transx.category_3.isnull(), 'category_3'] = 'A'\n\ntest_new_transx['nan_category_2'] = 0\ntest_new_transx.loc[test_new_transx.category_2.isnull(), 'nan_category_2'] = 1\ntest_new_transx.loc[test_new_transx.category_2.isnull(), 'category_2'] = 1.0\n\ntest_new_transx['exec_date'] = pd.to_datetime(test_new_transx['purchase_date'], format = '%Y%m%d %H:%M:%S')\ntest_new_transx['month'] = pd.DatetimeIndex(test_new_transx['exec_date']).month\ntest_new_transx['year'] = pd.DatetimeIndex(test_new_transx['exec_date']).year\ntest_new_transx['day'] = pd.DatetimeIndex(test_new_transx['exec_date']).day\ntest_new_transx['day_of_year'] = pd.DatetimeIndex(test_new_transx['exec_date']).dayofyear\ntest_new_transx['day_of_week'] = pd.DatetimeIndex(test_new_transx['exec_date']).dayofweek\ntest_new_transx['is_month_start'] = (pd.DatetimeIndex(test_new_transx['exec_date']).is_month_start).astype(int)\ntest_new_transx['is_month_end'] = (pd.DatetimeIndex(test_new_transx['exec_date']).is_month_end).astype(int)\ntest_new_transx['is_weekend'] = (pd.DatetimeIndex(test_new_transx['exec_date']).dayofweek >= 5).astype(int)\ntest_new_transx['is_weekday'] = (pd.DatetimeIndex(test_new_transx['exec_date']).dayofweek < 5).astype(int)\ntest_new_transx['weekday'] = pd.DatetimeIndex(test_new_transx['exec_date']).weekday\ntest_new_transx['week_of_year'] = pd.DatetimeIndex(test_new_transx['exec_date']).weekofyear\ntest_new_transx['quarter'] = pd.DatetimeIndex(test_new_transx['exec_date']).quarter\ntest_new_transx['hour'] = pd.DatetimeIndex(test_new_transx['exec_date']).hour\ntest_new_transx['days_since_purchase'] = (datetime.datetime.today() - test_new_transx['exec_date']).dt.days\ntest_new_transx['category_1'] = test_new_transx['category_1'].map({'Y': 1, 'N': 0})\ntest_new_transx['authorized_flag'] = test_new_transx['authorized_flag'].map({'Y': 1, 'N': 0})\ntest_new_transx['category_3'] = test_new_transx['category_3'].map({'A': 0, 'B': 1, 'C': 2})\ntest_new_transx['months_since_purchase'] = (((datetime.datetime.today() - test_new_transx['exec_date']).dt.days) / 30) + test_new_transx['month_lag']\ntest_new_transx['duration'] = test_new_transx['purchase_amount'] * test_new_transx['months_since_purchase']","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8154697d9ef1ac32dfe585c64c6fc3ce8fcddfb5"},"cell_type":"markdown","source":"# Aggregate historical / new files"},{"metadata":{"trusted":true,"_uuid":"c6eedb620ddeda62eee740eded8e8ce3d981528d"},"cell_type":"code","source":"new_transx_staging = new_transx[\n    [\n        'card_id', \n        'exec_date',\n        'city_id', \n        'installments',\n        'merchant_category_id', \n        'month_lag',\n        'purchase_amount', \n        'category_2', \n        'state_id',\n        'subsector_id', \n        'nan_merchant_id', \n        'nan_category_3', \n        'nan_category_2',\n        'authorized_flag',\n        'category_1',\n        'category_3', \n        'month', \n        'year', \n        'day', \n        'weekday', \n        'week_of_year',\n        'day_of_year',\n        'day_of_week',\n        'days_since_purchase',\n        'is_month_start',\n        'is_month_end',\n        'is_weekend',\n        'is_weekday',\n        'quarter',\n        'hour',\n        'months_since_purchase',\n        'duration'\n    ]\n].reset_index(drop = True)\n\ndel new_transx\ngc.collect()\n\nnew_transx_staging = new_transx_staging \\\n    .groupby('card_id') \\\n    .agg(agg_inputs) \\\n    .reset_index()\n\nnew_transx_staging.columns = [\n    'new_' + '_'.join(col).strip() \n        for col in new_transx_staging.columns.values\n]\n\nnew_transx_staging = new_transx_staging.rename(columns={new_transx_staging.columns[0] : 'card_id'})","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"3af4f24bd24323198f93e85c9650d7fd60b27c02"},"cell_type":"code","source":"test_new_transx_staging = test_new_transx[\n    [\n        'card_id', \n        'exec_date',\n        'city_id', \n        'installments',\n        'merchant_category_id', \n        'month_lag',\n        'purchase_amount', \n        'category_2', \n        'state_id',\n        'subsector_id', \n        'nan_merchant_id', \n        'nan_category_3', \n        'nan_category_2',\n        'authorized_flag',\n        'category_1',\n        'category_3', \n        'month', \n        'year', \n        'day', \n        'weekday', \n        'week_of_year',\n        'day_of_year',\n        'day_of_week',\n        'days_since_purchase',\n        'is_month_start',\n        'is_month_end',\n        'is_weekend',\n        'is_weekday',\n        'quarter',\n        'hour',\n        'months_since_purchase',\n        'duration'\n    ]\n].reset_index(drop = True)\n\ndel test_new_transx\ngc.collect()\n\ntest_new_transx_staging = test_new_transx_staging \\\n    .groupby('card_id') \\\n    .agg(agg_inputs) \\\n    .reset_index()\n\ntest_new_transx_staging.columns = [\n    'new_' + '_'.join(col).strip() \n        for col in test_new_transx_staging.columns.values\n]\n\ntest_new_transx_staging = test_new_transx_staging.rename(columns={test_new_transx_staging.columns[0] : 'card_id'})","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"67a844f4bfdc889795fd1852018dcc0593b62c9b"},"cell_type":"markdown","source":"# Bring it all together"},{"metadata":{"trusted":true,"_uuid":"d9ac70e8d927e89a34a6363da35b09c70a37af82"},"cell_type":"code","source":"train = pd.merge(\n    train,\n    new_transx_staging,\n    on = 'card_id',\n    how = 'left'\n)\n\ntest = pd.merge(\n    test,\n    test_new_transx_staging,\n    on = 'card_id',\n    how = 'left'\n)\n\ndel [\n    test_new_transx_staging,\n    new_transx_staging\n]\n\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"616cda35477c1a6d8b58eebd235ec88ec3bbcbc8"},"cell_type":"markdown","source":"# Domain features\n## DONE Sum historical and new purchase amounts\n## DONE Average day of purchase\n## DONE Min / Max day of year\n## DONE How dense are purchases?\n## DONE Time of day\n## DONE How consistent are they with amounts? Standard dev of amounts\n## Store level score averages\n## DONE Pct authorized flag = N\n## DONE First month / year -> get more granular\n## Month lag?"},{"metadata":{"trusted":true,"_uuid":"5531acea3cf9e052c9a09410fd11f90c6342c9f2"},"cell_type":"code","source":"import datetime\n\ntrain['new_hist_purch_amt_max'] = train['h_t_purchase_amount_max'] + train['new_purchase_amount_max']\ntest['new_hist_purch_amt_max'] = test['h_t_purchase_amount_max'] + test['new_purchase_amount_max']\n\ntrain['new_time_elapsed'] = (train['new_exec_date_max'] - train['new_exec_date_min']).dt.days\ntest['new_time_elapsed'] = (test['new_exec_date_max'] - test['new_exec_date_min']).dt.days\ntrain['h_t_time_elapsed'] = (train['h_t_exec_date_max'] - train['h_t_exec_date_min']).dt.days\ntest['h_t_time_elapsed'] = (test['h_t_exec_date_max'] - test['h_t_exec_date_min']).dt.days\n\ntrain['days_since_first_purch'] = (datetime.datetime.today() - train['first_purch']).dt.days\ntest['days_since_first_purch'] = (datetime.datetime.today() - test['first_purch']).dt.days\n\ntrain['new_days_since_first_exec'] = (datetime.datetime.today() - train['new_exec_date_min']).dt.days\ntest['new_days_since_first_exec'] = (datetime.datetime.today() - test['new_exec_date_min']).dt.days\ntrain['h_t_days_since_first_exec'] = (datetime.datetime.today() - train['h_t_exec_date_min']).dt.days\ntest['h_t_days_since_first_exec'] = (datetime.datetime.today() - test['h_t_exec_date_min']).dt.days\n\ntrain['new_days_since_last_exec'] = (datetime.datetime.today() - train['new_exec_date_max']).dt.days\ntest['new_days_since_last_exec'] = (datetime.datetime.today() - test['new_exec_date_max']).dt.days\ntrain['h_t_days_since_last_exec'] = (datetime.datetime.today() - train['h_t_exec_date_max']).dt.days\ntest['h_t_days_since_last_exec'] = (datetime.datetime.today() - test['h_t_exec_date_max']).dt.days\n\ntrain['h_t_avg_purch_per_day'] = train['h_t_time_elapsed'] / train['h_t_card_id_size']\ntest['h_t_avg_purch_per_day'] = test['h_t_time_elapsed'] / test['h_t_card_id_size']\ntrain['new_avg_purch_per_day'] = train['new_time_elapsed'] / train['new_card_id_size']\ntest['new_avg_purch_per_day'] = test['new_time_elapsed'] / test['new_card_id_size']\n\ntrain['h_t_days_between_first_purchases'] = (train['h_t_exec_date_min'] - train['first_purch']).dt.days\ntest['h_t_days_between_first_purchases'] = (test['h_t_exec_date_min'] - test['first_purch']).dt.days\ntrain['new_days_between_first_purchases'] = (train['new_exec_date_min'] - train['first_purch']).dt.days\ntest['new_days_between_first_purchases'] = (test['new_exec_date_min'] - test['first_purch']).dt.days","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ba861b48a7479635b42386e779538a0ce369416c"},"cell_type":"markdown","source":"# Run predictions, submit csv"},{"metadata":{"trusted":true,"_uuid":"aa37989a6c53dc54a5cdf560e1bb417844250593"},"cell_type":"code","source":"blah","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"412f7f7790bc188dc96eaadf04db01d7ba907f84"},"cell_type":"code","source":"from sklearn.model_selection import StratifiedKFold, RepeatedKFold\nimport lightgbm as lgb\nfrom sklearn.metrics import mean_squared_error\nfrom sklearn.model_selection import train_test_split\nfrom math import sqrt\n\nx = train.copy().drop(\n    [\n        #'target',\n        'first_active_month',\n        'first_purch',\n        'card_id',\n        'new_exec_date_min',\n        'new_exec_date_max',\n        'h_t_exec_date_min',\n        'h_t_exec_date_max'\n    ],\n    axis = 1\n)\n\ny = train['target']\n\nx_submit = test.copy().drop(\n    [\n        'first_active_month',\n        'first_purch',\n        'card_id',\n        'new_exec_date_min',\n        'new_exec_date_max',\n        'h_t_exec_date_min',\n        'h_t_exec_date_max'\n    ],\n    axis = 1\n)\n\nparam = {'num_leaves': 31,\n         'min_data_in_leaf': 27, \n         'objective':'regression',\n         'max_depth': -1,\n         'learning_rate': 0.015,\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\": 4950}\n\nfolds = StratifiedKFold(n_splits = 5, shuffle = True)\ntrain_predictions = np.zeros(len(train))\ntest_predictions = np.zeros(len(test))\n\nfor train_index, test_index in folds.split(x, x['feature_1']):\n    y = x['target']\n    x0 = x.drop('target', axis = 1)\n    \n    trn_data = lgb.Dataset(x0.iloc[train_index], label=y.iloc[train_index])\n    val_data = lgb.Dataset(x0.iloc[test_index], label=y.iloc[test_index])\n    \n    #x_train, x_test = x0.iloc[train_index], x0.iloc[test_index]\n    #y_train, y_test = y.iloc[train_index], y.iloc[test_index]\n    \n    num_round = 10000\n    clf = lgb.train(param, trn_data, num_round, valid_sets = [trn_data, val_data], verbose_eval=-1, early_stopping_rounds = 200)\n    train_predictions[test_index] = clf.predict(x0.iloc[test_index], num_iteration=clf.best_iteration)\n    \n    #train_predictions[test_index] = lgb_model.predict(x_test)\n\n    test_predictions += clf.predict(x_submit, num_iteration=clf.best_iteration) / folds.n_splits\n\nnp.sqrt(mean_squared_error(train_predictions, y))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e96f471ddfa1c00a1ccf223cdc7d754def2462c7"},"cell_type":"code","source":"predictions = pd.DataFrame(\n    data = {\n        'card_id' : test['card_id'],\n        'target' : test_predictions\n    }\n).to_csv('submit.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}