{"cells":[{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","collapsed":true,"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","trusted":false},"cell_type":"markdown","source":"**Hello, guys. This is my first Kernel at Kaggle. I hope you find it useful. **\n\nHere are steps of problem solving process:\n\n1.  Understand the problem set; \n    - if you don't have any domain knowledge, you should do more research online or ask your friends for help\n2.  Analyze each data set before using them;\n    - Check Relationships of data\n    - Drop any redundant data\n    - Explore New Features from current features\n3.  Merge data sets by primary keys; \n    - Understand the relations among datasets\n4.  Select a suitable model;\n    - Prepare data for the model\n    - Set up the model\n    - Tune parameters\n "},{"metadata":{"trusted":true,"_uuid":"9dc4274a9e6633fa4f40db17a049949f55aba091"},"cell_type":"markdown","source":"As we know, a data scientist spends much time analyzing data. What's more important, he or she needs to uderstand the problem first. Then he/she needs to think about what could be got from current given data, how to use these data, what kinds of useful features could be created from these given data sets, and what kinds of models that we can try to solve the problem. \n\n#### Reference:  \n[Elo world by FabienDaniel](https://www.kaggle.com/fabiendaniel/elo-world)"},{"metadata":{"trusted":true,"_uuid":"3d2c13c9fee2a3739fdb0bf5b0dee40547aa32c3"},"cell_type":"markdown","source":"## **Analyze each data set before using them**"},{"metadata":{"trusted":true,"_uuid":"6a809f200c3a5ed8ad0e44856694dcf4057b6b14"},"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nimport matplotlib.pyplot as plt\nimport seaborn as sns\nimport lightgbm as lgb\nfrom sklearn.model_selection import KFold\nfrom sklearn.metrics import mean_squared_error\nimport datetime\nfrom datetime import date\nimport calendar\nimport dateutil\nimport os","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ea2f8cbfa3b6c2786636a703dc7b1cef8a90e795"},"cell_type":"code","source":"## Check files\nprint(os.listdir(\"../input/elo-merchant-category-recommendation\"))","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"201a8f2837fe51fed41f93d4ed64c1a51754ca26"},"cell_type":"markdown","source":"From the list above, there are a total of 7 files. In additon, we can uderstand the meaning of each current given data by Data_Dictionary.xlsx. "},{"metadata":{"_uuid":"ac22175a53098385bfc97148eccbf0b46a3ce0c3"},"cell_type":"markdown","source":"***Train.csv***"},{"metadata":{"trusted":true,"_uuid":"c7aa6b1dd69a8fbda5ce50af054b213cb023bf22"},"cell_type":"code","source":"file_path = '../input/elo-merchant-category-recommendation/train.csv'\ntrain = pd.read_csv(file_path)\ntrain.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"5802122e10c6e24da38ed8629c24988688cbc675"},"cell_type":"code","source":"## check the correlation of each feature and target\ntrain.corr()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"22fb7064bad656294ee3fc4d5e3d6c19b89fe066"},"cell_type":"markdown","source":"From the table above, it shows that feature_1 has a stronger correlation with feature_3, while all features have a weak correlation with target. Which means we need more other features so that we can predict target accurately. "},{"metadata":{"trusted":true,"_uuid":"0f0fc495f5d841274a1104eafeef81701c1bb868"},"cell_type":"code","source":"train.target.describe()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6b27131b308d231d8da37010a0f814ba2c1b6847"},"cell_type":"markdown","source":"The mean of the target is about 0,  and 25% and 75% are closed to 0, but the min and max values are much higher than that. We should pay attention to these since these might be wrong labels. "},{"metadata":{"_uuid":"13506f528cbc109b6a6fdb2067811e298b4d8204"},"cell_type":"markdown","source":"***Test.csv***"},{"metadata":{"trusted":true,"_uuid":"6f37795ad4ec0fda137a4a4ba6c473244f57d237"},"cell_type":"code","source":"file_path = '../input/elo-merchant-category-recommendation/test.csv'\ntest = pd.read_csv(file_path)\ntest.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b58ae906c1eb6f6d5b6204c8f101d12d23037f22"},"cell_type":"markdown","source":"From train and test data sets,  except the target column, they have the same number of columns with the same column names. So we can know, card_id is something related to other provided files. "},{"metadata":{"_uuid":"f4c704f6da06c69207e50fa1ac20a02377917dc0"},"cell_type":"markdown","source":"***Historical_transactions.csv***"},{"metadata":{"trusted":true,"scrolled":true,"_uuid":"1a89ecf31f6f9104fcb742f09e904b5bf1efc3f2"},"cell_type":"code","source":"history = pd.read_csv('../input/elo-merchant-category-recommendation/historical_transactions.csv')\nhistory.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"445d93ad5750b891d8bf0bd4e69ac2ac8d5ca266"},"cell_type":"markdown","source":"From the historical_transactions file, we know authorized_flag, category_1 and category_3 are categorical features except card_id and merchant_id, they should be tranformed to numerical data or dummies later. \nOur job is to help understand customer loyalty. At first glance, purchase_amount, month_lag, purchase_date, city_id, and state_id are important in this task. Let's check their relations. "},{"metadata":{"trusted":true,"_uuid":"fbb3b95b427a176b108dd60182c36de440fbe5f4"},"cell_type":"code","source":"## Month_lag\nfig = plt.figure(figsize=(14,6))\nax = sns.distplot(history.month_lag)\nax.set_xlabel('Month_lag', size=12)\nax.set_ylabel('Frequency', size=12)\nax.set_title('Month_lag', size=15)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e7cfcf12a3a81da296d7b5b333b70512b820f7d4"},"cell_type":"code","source":"history['month_lag'].describe()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"05f8a512f838d8e652b3efa4e5097f599fc59c04"},"cell_type":"markdown","source":"The graph and the table show that the 75% of month_lag are less than -2."},{"metadata":{"trusted":true,"_uuid":"0143cd64e9816766030493e1ea7d10cd3f134a10"},"cell_type":"code","source":"fig, (ax1, ax2) = plt.subplots(nrows=1, ncols=2, figsize=(16,8))\ntemp1 = history.groupby('city_id')['purchase_amount'].mean()\ntemp2 = history.groupby('state_id')['purchase_amount'].mean()\n\nax = [ax1, ax2]\ntemp = [temp1, temp2]\nx_labels = ['city_id', 'state_id']\ntitles = ['Average City purchase amount', 'Average State purchase amount']\nscale = [100, 10000]\n\nfor i in range(2):\n    ax[i].scatter(x=temp[i].index, y=temp[i].values, c=temp[i].values, s=temp[i].values*scale[i], alpha=0.8)\n    ax[i].set_xlabel(x_labels[i], size=12)\n    ax[i].set_ylabel('Purchase amount', size=12)\n    ax[i].set_title(titles[i], size=15)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1d0de8ffa5899ed0f71fdbc10908ced7004e9cf0"},"cell_type":"markdown","source":"From these two graphs, it is obvious to see that some cities or states contributed more to the Elo. So, I bet city_id and state_id are quite important to the project. "},{"metadata":{"trusted":true,"_uuid":"acc4503bc6d8de25ce259c5f919843f3598a4cc8"},"cell_type":"code","source":"## parse purchase_date and divide it into day, week, time session and see how important they will be\nhistory['purchase_weekday'] = pd.to_datetime(history['purchase_date']).dt.day_name()\nhistory['purchase_month'] = pd.to_datetime(history['purchase_date']).dt.month_name()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4c71ce8ce732735bffc36145fbc34e54a904884e"},"cell_type":"code","source":"## Define a day session\n## Morning: 5am to 12pm (05:00 to 11:59)\n## Afternoon: 12pm to 5pm (12:00 to 16:59)\n## Evening: 5pm to 9pm (17:00 to 20:59)\n## Night: 9pm to 5am (21:00 to 04:59)\n\ndef time_session(time):\n    \n    if time >= 5 and time < 12:\n        return 'Morning'\n    elif time >=12 and time < 17:\n        return 'Afternoon'\n    elif time >=17 and time < 21:\n        return 'Evening'\n    else:\n        return 'Night'","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"2ee2db221c12ddcc17e397b137b64abe4f922e5f"},"cell_type":"code","source":"history['temp'] = pd.to_datetime(history['purchase_date']).dt.hour\nhistory['purchase_time_session'] = history['temp'].apply(lambda x : time_session(x))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9c51045db6b0a2cf5b0e03a00ee38a34d001adbb"},"cell_type":"code","source":"## Make categorical data has specific order\nweekday_labels = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday']\nhistory['purchase_weekday'] = pd.Categorical(history['purchase_weekday'], categories=weekday_labels, ordered=True)\n\nmonth_labels = ['January', 'February', 'March', 'April', 'May', 'June', 'July', 'August',\n                'September', 'October', 'November', 'December']\nhistory['purchase_month'] = pd.Categorical(history['purchase_month'], categories=month_labels, ordered=True)\n\nsession_labels = ['Morning', 'Afternoon', 'Evening', 'Night']\nhistory['purchase_time_session'] = pd.Categorical(history['purchase_time_session'], categories=session_labels, ordered=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0ec5a893ce8349e7b64b1fb3adea0383f8bfefc7"},"cell_type":"code","source":"f, (ax1, ax2, ax3) = plt.subplots(nrows=3, ncols=1, figsize=(14,10))\ntemp1 = history.groupby('purchase_weekday')['purchase_amount'].mean()\ntemp2 = history.groupby('purchase_month')['purchase_amount'].mean()\ntemp3 = history.groupby('purchase_time_session')['purchase_amount'].mean()\n\na = sns.lineplot(x=temp1.index, y=temp1.values, data=history, ax=ax2)\nb = sns.lineplot(x=temp2.index, y=temp2.values, data=history, ax=ax1)\nc = sns.lineplot(x=temp3.index, y=temp3.values, data=history, ax=ax3)\n\nplt.xlabel('Purchase time', size=12)\nplt.ylabel('Purchase amount', size=12)\nf.suptitle('Time Series Analysis', size=15)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"4f6acd349fc843a41d83e525c6f6dcab426cb179"},"cell_type":"markdown","source":"From the graphs above, we can see that purchase date contains some useful information. It is better to think about a way to convert the purchase date into categorical data or a numerical number. "},{"metadata":{"trusted":true,"_uuid":"5ab31eb5988eabf2069516ccf0eb94ec248fecb2"},"cell_type":"markdown","source":"***Merchants.csv***"},{"metadata":{"trusted":true,"_uuid":"8f7c631cb58aa7a859cbfff9b8fd154e92d244ab"},"cell_type":"code","source":"mer = pd.read_csv('../input/elo-merchant-category-recommendation/merchants.csv')\nmer.head()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"8ef49756213aaf31746b44bb4976bda15f065c09"},"cell_type":"markdown","source":"From the contents of merchants.csv, we can know it contains some columns which could be found on history file. In addition, there are some new columns could be found on merchants file, like ave_sales_lag3. "},{"metadata":{"trusted":true,"_uuid":"2b83e715338ade0c156da15e014774f8053f6263"},"cell_type":"code","source":"f, (ax1, ax2, ax3) = plt.subplots(nrows=1, ncols=3, figsize=(16,8))\ntemp1 = mer.groupby('city_id')['avg_sales_lag3'].sum()\ntemp2 = mer.groupby('city_id')['avg_sales_lag6'].sum()\ntemp3 = mer.groupby('city_id')['avg_sales_lag12'].sum()\n\nax = [ax1, ax2, ax3]\ntemp = [temp1, temp2, temp3]\ny_labels = ['Total avg sales lag3', 'Total avg sales lag6', 'Total avg sales lag12']\n\nfor i in range(3):\n    ax[i].scatter(x=temp[i].index, y=temp[i].values, s=temp[i].values/100, c=temp[i].values, alpha=0.8)\n    ax[i].set_xlabel('City id', fontsize=12)\n    ax[i].set_ylabel(y_labels[i])\n    ax[i].set_title(y_labels[i] + ' in each city')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"0ae2968efefa385e452d3f0d065345549f7021f4"},"cell_type":"code","source":"f, (ax1, ax2, ax3) = plt.subplots(nrows=1, ncols=3, figsize=(16,8))\ntemp1 = mer.groupby('city_id')['avg_purchases_lag3'].sum()\ntemp2 = mer.groupby('city_id')['avg_purchases_lag6'].sum()\ntemp3 = mer.groupby('city_id')['avg_purchases_lag12'].sum()\n\nax = [ax1, ax2, ax3]\ntemp = [temp1, temp2, temp3]\ny_labels = ['Total avg purchases lag3', 'Total avg purchases lag6', 'Total avg purchases lag12']\n\nfor i in range(3):\n    ax[i].scatter(x=temp[i].index, y=temp[i].values, s=temp[i].values/100, c=temp[i].values, alpha=0.8)\n    ax[i].set_xlabel('City id', fontsize=12)\n    ax[i].set_ylabel(y_labels[i])\n    ax[i].set_title(y_labels[i] + ' in each city')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2564cc17caa76265fb7d82c4559ac0ec8bbba34e"},"cell_type":"markdown","source":"According to average sales graphs, we can know that some cities have higher total average sales lag than other cities. However, the majority of cities have lower total average purchases lag. "},{"metadata":{"_uuid":"0f6081b7a05d0ec0bd89c5d062dd3b95fb382ff5"},"cell_type":"markdown","source":"***new_merchant_transactions.csv***"},{"metadata":{"trusted":true,"_uuid":"b32821c6efea97b461d2f518afabb95ef126d214"},"cell_type":"code","source":"new_mer = pd.read_csv('../input/elo-merchant-category-recommendation/new_merchant_transactions.csv')\nnew_mer.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"ac3f6adc82d77b30c31aba83c7820ecfadb88acd"},"cell_type":"markdown","source":"From the table above, we can know that new merchant transactions contain some useful features, like purchase amount, and purchase date. \n\n#### Due to kaggle kernel died for many times while merging datasets, so I have to process these data on Jupyter Notebook, and save the final files and uploaded them to Kaggle.  \n\n#### Please check the appendix of this kernel for details. "},{"metadata":{"_uuid":"3b9b9914298f27edf640473d5cb64c9f89ecc51b"},"cell_type":"markdown","source":"## Select a suitable model\n\nIn this kernel, we are focusing on ***LightGBM***, which is a gradient boosting framework that uses tree based learning algorithms. To better understand this algorithm, you should do more research about it. What's more inmportant, you should try to understand the thoery behind it and play with it. \n\n#### Reference\n* [LightGBM’s documentation!](https://lightgbm.readthedocs.io/en/latest/)\n* [What is LightGBM, How to implement it? How to fine tune the parameters?](https://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.rename.html)\n* [LightGBM, Light Gradient Boosting Machine](https://github.com/Microsoft/LightGBM)\n\n**For Faster Speed:**\n* Use bagging by setting bagging_fraction and bagging_freq\n* Use feature sub-sampling by setting feature_fraction\n* Use small max_bin\n* Use save_binary to speed up data loading in future learning\n* Use parallel learning, refer to parallel learning guide.\n\n**For better accuracy:**\n* Use large max_bin (may be slower)\n* Use small learning_rate with large num_iterations\n* Use large num_leaves(may cause over-fitting)\n* Use bigger training data\n* Try dart\n* Try to use categorical feature directly\n\n**To deal with over-fitting:**\n* Use small max_bin\n* Use small num_leaves\n* Use min_data_in_leaf and min_sum_hessian_in_leaf\n* Use bagging by set bagging_fraction and bagging_freq\n* Use feature sub-sampling by set feature_fraction\n* Use bigger training data\n* Try lambda_l1, lambda_l2 and min_gain_to_split to regularization\n* Try max_depth to avoid growing deep tree"},{"metadata":{"_uuid":"c7858e9cc614b99ce34e2129fc0e8da5644fb3ae"},"cell_type":"markdown","source":"## Prepare features"},{"metadata":{"trusted":true,"_uuid":"db5780b4e6f41223486841ddabae29851f4b5bc1"},"cell_type":"code","source":"train = pd.read_csv('../input/elo-combined-data/X.csv')\ntrain.drop('Unnamed: 0', axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c14faccf5c8aefa313833211aeb50a0341c6b934"},"cell_type":"code","source":"test = pd.read_csv('../input/elo-combined-data/X_test.csv')\ntest.drop('Unnamed: 0', axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"9a564cab4fc241f1f433d4320df645eafeaa5334"},"cell_type":"code","source":"y = pd.read_csv('../input/elo-combined-data/y.csv', header=None)\ny.drop(0, axis=1, inplace=True)\ny.rename({1: 'target'}, axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"dfe616356cb16868ce23563b11265cf357812b96"},"cell_type":"code","source":"not_use_col = ['first_active_month', 'card_id']\nuse_cols = [col for col in train.columns if col not in not_use_col]\nX = train[use_cols]\nX_test = test[use_cols]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e1539e73d27e80c7b42bc478d963f90e9b8c89e4"},"cell_type":"code","source":"features = list(train[use_cols].columns)\ncategorical_feat = [col for col in features if 'feature_' in col]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"c4d7231141bf7fc0956dbfcea3d50963f81c4d37"},"cell_type":"code","source":"def model():\n    lgb_params = {\n              'objective': 'regression',\n              'metric': 'rmse',\n              'max_depth': 11,\n              'min_chil_samples': 20,\n              'min_data_in_leaf': 200,\n              'reg_alpha': 1,\n              'reg_lambda': 1,\n              'num_leaves': 140,\n              'learning_rate': 0.07,\n              'subsample': 0.8,\n              'colsample_bytress': 0.9,\n              'verbosity': -1}\n    \n    folds = KFold(n_splits=10, shuffle=True, random_state=1)\n    oof = np.zeros(len(X))\n    predictions = np.zeros(len(X_test))\n    \n    for fold_, (trn_idx, val_idx) in enumerate(folds.split(X.values, y.values)):\n        print(\"LGB\" + str(fold_) + '*' * 50)\n        trn_data = lgb.Dataset(X.iloc[trn_idx][use_cols], label=y.iloc[trn_idx], categorical_feature=categorical_feat)\n        val_data = lgb.Dataset(X.iloc[val_idx][use_cols], label=y.iloc[val_idx], categorical_feature=categorical_feat)\n\n        num_round=1000\n\n        clf = lgb.train(lgb_params, trn_data, valid_sets=[trn_data, val_data], verbose_eval=100, early_stopping_rounds=600)\n        oof[val_idx] = clf.predict(X.iloc[val_idx][use_cols], num_iteration = clf.best_iteration)\n        predictions += clf.predict(X_test[use_cols], num_iteration=clf.best_iteration) / folds.n_splits\n\n    print(\"CV score: {:<8.5f}\".format(mean_squared_error(oof, y)**0.5))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"006519046609ce016d26290d9cfe6cd90e7bf516"},"cell_type":"code","source":"model()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e79000101d48364803caa9765ed099744cad6c62"},"cell_type":"markdown","source":"## Appendix\n\n### Data preprocessing and cleaning:"},{"metadata":{"trusted":true,"_uuid":"533838820e2d55af0c46ed5c0fe38f1cef87441b"},"cell_type":"markdown","source":"### Convert categorical data into numerical data if there are less than two classes\nhistory['authorized_flag'] = history['authorized_flag'].map({'Y': 1, 'N': 0}) \n\nnew_mer['authorized_flag'] = new_mer['authorized_flag'].map({'Y': 1, 'N': 0})"},{"metadata":{"trusted":true,"_uuid":"a51d55bb99223a632f42078c4e7091747ca73ffe"},"cell_type":"markdown","source":"### Change type of purchase date, so that we can get extra useful information from them\nhistory['purchase_date'] = pd.DatetimeIndex(history['purchase_date'])\n\nnew_mer['purchase_date'] = pd.DatetimeIndex(new_mer['purchase_date'])"},{"metadata":{"_uuid":"1e2f7b33b2d08519ba0a334b92457e3929ca0f7f"},"cell_type":"markdown","source":"### **Group merchants dataset **"},{"metadata":{"trusted":true,"_uuid":"37558f74bb7a54664fc07db6e0bc02aa6bc76dfc"},"cell_type":"markdown","source":"#### def aggregate_merchants(data):\n    \n    agg_fun={\n        'avg_sales_lag3': ['mean'],\n        'avg_purchases_lag3': ['mean'],\n        'active_months_lag3': ['mean'],\n        'avg_sales_lag6': ['mean'],\n        'avg_purchases_lag6': ['mean'],\n        'active_months_lag6': ['mean'],\n        'avg_sales_lag12': ['mean'],\n        'avg_purchases_lag12': ['mean']\n    }\n    \n    agg_mer = data.groupby('merchant_id').agg(agg_fun)\n    agg_mer.columns = ['mer_' + '_'.join(col).strip() for col in agg_mer.columns.values]\n    \n    agg_mer.reset_index(inplace=True)\n    \n    return agg_mer"},{"metadata":{"trusted":true,"_uuid":"21935cde0de687a9e05bc6ad51a23ab17b09c0de"},"cell_type":"markdown","source":"mer_agg = aggregate_merchants(mer)"},{"metadata":{"trusted":true,"_uuid":"9b6273c87f75bcfe08a6ced642b60cd2b20752aa"},"cell_type":"markdown","source":"#### Merge history dateset with merchant_agg dataset by using 'merchant_id'\nhistory = pd.merge(history, mer_agg, on='merchant_id', how='left', suffixes=('_hist', '_mer'))"},{"metadata":{"trusted":true,"_uuid":"8d21c000925b0416bd2256eb98cc9e518037f232"},"cell_type":"markdown","source":"#### def aggregate_history_transactions(history):\n    \n    agg_fun = {\n        'authorized_flag': ['sum', 'mean'],\n        'city_id': ['nunique'],\n        'installments': ['sum', 'max', 'mean', 'std'],\n        'merchant_category_id': ['nunique'],\n        'month_lag': ['mean', 'min'],\n        'purchase_amount': ['sum', 'mean', 'max', 'min', 'std'],\n        'state_id': ['nunique'],\n        'subsector_id': ['mean', 'max', 'min'],\n        'purchase_date': ['max', 'min', lambda x: max(x) - min(x)],\n        'category_1': ['nunique'],\n        'category_3': ['nunique'],\n        'category_2': ['mean'],\n        'mer_avg_sales_lag3_mean_mer': ['sum', 'mean'],\n        'mer_avg_purchases_lag3_mean_mer': ['sum', 'mean'],\n        'mer_active_months_lag3_mean_mer': ['sum'],\n        'mer_avg_sales_lag6_mean_mer': ['sum', 'mean'], \n        'mer_avg_purchases_lag6_mean_mer': ['sum', 'mean'],\n        'mer_active_months_lag6_mean_mer': ['sum'], \n        'mer_avg_sales_lag12_mean_mer': ['sum', 'mean'],\n        'mer_avg_purchases_lag12_mean_mer': ['sum', 'mean']\n    }\n    \n    agg_history = history.groupby(['card_id']).agg(agg_fun)\n    agg_history.columns = ['hist_' + '_'.join(col).strip() for col in agg_history.columns.values]\n    agg_history.reset_index(inplace=True)\n    \n    df = (history.groupby('card_id').size().reset_index(name='hist_transactions_count'))\n    \n    agg_history = pd.merge(df, agg_history, on='card_id', how='left')\n    \n    return agg_history"},{"metadata":{"trusted":true,"_uuid":"36a15a99cfcb5989c00754f55f623b49c645b8e0"},"cell_type":"markdown","source":"history_agg = aggregate_history_transactions(history)"},{"metadata":{"trusted":true,"_uuid":"e0211bccbac961c15fdd2f384bfadd7c3c80256b"},"cell_type":"markdown","source":"#### Modified the time data so that they could be easily recognized by the machine. \nhistory_agg['hist_purchase_date_diff_day'] = pd.to_datetime(history_agg['hist_purchase_date_max']).dt.day - pd.to_datetime(history_agg['hist_purchase_date_min']).dt.day\n\nhistory_agg.drop(['hist_purchase_date_max', 'hist_purchase_date_min'], axis=1, inplace=True)\n\nhistory_agg = history_agg.rename(columns={\"hist_purchase_date_<lambda>\": \"hist_purchase_date_diff\"})\n\nhistory_agg['hist_purchase_date_diff'] = history_agg['hist_purchase_date_diff'].dt.total_seconds()"},{"metadata":{"_uuid":"3823d1628fe5723813f72bcd04e4bfb23da1bd54"},"cell_type":"markdown","source":"### **Group new merchant transactions dataset**"},{"metadata":{"trusted":true,"_uuid":"af42676abee9a5b6dadb7ebe96d340e3dc152013"},"cell_type":"markdown","source":"#### def aggregate_new_merchant_transaction(data):\n    \n    agg_fun={\n        'authorized_flag': ['sum'],\n        'merchant_id': ['nunique'],\n        'installments': ['sum', 'max', 'mean'],\n        'month_lag': ['sum', 'mean'],\n        'purchase_amount': ['sum', 'mean', 'max', 'min'],\n        'purchase_date': ['max', 'min', lambda x : max(x) - min(x)]\n    }\n    \n    agg_new_mer = data.groupby('card_id').agg(agg_fun)\n    agg_new_mer.columns = ['new_' + '_'.join(col).strip() for col in agg_new_mer.columns.values]\n    \n    agg_new_mer.reset_index(inplace=True)\n    \n    return agg_new_mer"},{"metadata":{"trusted":true,"_uuid":"9cdb92db9a7e848abac36b5805da68e75df62cb2"},"cell_type":"markdown","source":"new_trans_agg = aggregate_new_merchant_transaction(new_mer)"},{"metadata":{"trusted":true,"_uuid":"85117f89b7a21258093e68bf01c962329a5ea48e"},"cell_type":"markdown","source":"#### Modified the time data so that they could be easily recognized by the machine. \nnew_trans_agg['new_purchase_date_diff_days'] = pd.to_datetime(new_trans_agg['new_purchase_date_max']).dt.day - pd.to_datetime(new_trans_agg['new_purchase_date_min']).dt.day\n\nnew_trans_agg.drop(['new_purchase_date_max', 'new_purchase_date_min'], axis=1, inplace=True)\n\nnew_trans_agg = new_trans_agg.rename(columns={'new_purchase_date_<lambda>': 'new_purchase_date_diff'})\n\nnew_trans_agg['new_purchase_date_diff'] = new_trans_agg['new_purchase_date_diff'].dt.total_seconds()"},{"metadata":{"_uuid":"b373275f38a12176f8960468dbf9d3cf1c42db5e"},"cell_type":"markdown","source":"#### process the train and test data set\ndef read_data(input_file):\n    df = pd.read_csv(input_file)\n   \n   df['first_active_month'] = pd.to_datetime(df['first_active_month'])\n    \n   df['elapsed_time'] = (datetime.date(2018, 2, 1) - df['first_active_month'].dt.date).dt.days\n    \n   return df"},{"metadata":{"_uuid":"57890008436576bf49d7ac9e85cc437fcced2c02"},"cell_type":"markdown","source":"#### Seperate the target values from train dataset and delete the target values from it.\ny = train['target']\n\ndel train['target']"},{"metadata":{"_uuid":"acae6de77274a8c2668c43c5fd60140a90661de9"},"cell_type":"markdown","source":"#### Merge the all aggregate files with train and test dataset, so that they contain the same number of features.\ntrain = pd.merge(train, history_agg, on='card_id', how='left')\n\ntest = pd.merge(test, history_agg, on='card_id', how='left')\n\ntrain = pd.merge(train, new_trans_agg, on='card_id', how='left')\n\ntest = pd.merge(test, new_trans_agg, on='card_id', how='left')"}],"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}