{"cells":[{"metadata":{"trusted":true,"_uuid":"332fa118684da9a9278d2caf565620686fd73a68"},"cell_type":"markdown","source":"<h1><center> Elo - Data Engineering </center></h1> \n\n### Competition Description\nImagine being hungry in an unfamiliar part of town and getting restaurant recommendations served up, based on your personal preferences, at just the right moment. The recommendation comes with an attached discount from your credit card provider for a local place around the corner!\n\nRight now, Elo, one of the largest payment brands in Brazil, has built partnerships with merchants in order to offer promotions or discounts to cardholders. But do these promotions work for either the consumer or the merchant? Do customers enjoy their experience? Do merchants see repeat business? Personalization is key.\n\nElo has built machine learning models to understand the most important aspects and preferences in their customers’ lifecycle, from food to shopping. But so far none of them is specifically tailored for an individual or profile. This is where you come in.\n\nIn this competition, Kagglers will develop algorithms to identify and serve the most relevant opportunities to individuals, by uncovering signal in customer loyalty. Your input will improve customers’ lives and help Elo reduce unwanted campaigns, to create the right experience for customers.\n\n### What files do I need?\nYou will need, at a minimum, the train.csv and test.csv files. These contain the card_ids that we'll be using for training and prediction.\n\nThe historical_transactions.csv and new_merchant_transactions.csv files contain information about each card's transactions. \n- historical_transactions.csv contains up to 3 months' worth of transactions for every card at any of the provided merchant_ids. \n- new_merchant_transactions.csv contains the transactions at new merchants (merchant_ids that this particular card_id has not yet visited) over a period of two months.\n- merchants.csv contains aggregate information for each merchant_id represented in the data set.\n\n### What should I expect the data format to be?\nThe data is formatted as follows:\n\ntrain.csv and test.csv contain card_ids and information about the card itself - the first month the card was active, etc. train.csv also contains the target.\n\nhistorical_transactions.csv and new_merchant_transactions.csv are designed to be joined with train.csv, test.csv, and merchants.csv. They contain information about transactions for each card, as described above.\n\nmerchants can be joined with the transaction sets to provide additional merchant-level information.\n\n### What am I predicting?\nYou are predicting a loyalty score for each card_id represented in test.csv and sample_submission.csv."},{"metadata":{"_uuid":"130cf461caa791c01bb33397d9cbb25b8f38907f"},"cell_type":"markdown","source":"<hr>"},{"metadata":{"_uuid":"21b94ef1a7e14f25c3e411ef3cefe77c9dce51d3"},"cell_type":"markdown","source":"### Goals of this notebook:\n1. Load all necessary data\n1. Optimize memory and adjust datasets\n1. Feature engineer variables and correlate to target\n1. Export ready to use data for machine learning model"},{"metadata":{"_uuid":"9d8c310c9533f929532906070e6f37f49b0ddf65"},"cell_type":"markdown","source":"<hr>"},{"metadata":{"_uuid":"596e814b0499cc20fb891f0d7f1f21e623556f5a"},"cell_type":"markdown","source":"<h1><center> Import Libraries </center></h1>"},{"metadata":{"_uuid":"4c833909bc687583b9909f855a71ecbabbfd2be7"},"cell_type":"markdown","source":"#### Import all necessary python libraries "},{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"# data engineering libraries\nimport numpy as np \nimport pandas as pd \n\n# plotting libraries\nimport matplotlib.pyplot as plt\nimport seaborn as sns\n\n# support python libraries\nimport warnings\nimport time\nimport sys\nimport datetime\n\n# machine learning libraries\nfrom sklearn.model_selection import KFold\nfrom sklearn.metrics import mean_squared_error","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"24c868c0ccc734e08c7574efb006465365b8d077"},"cell_type":"markdown","source":"#### Change notebook options "},{"metadata":{"trusted":true,"_uuid":"777c955f451ff7f4f037c5eed3ceb5470838fdfb"},"cell_type":"code","source":"warnings.simplefilter(action='ignore', category=FutureWarning)\npd.set_option('display.max_columns', 500)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"6c6757cf0ec844553330da46dc5bb558df2be7b6"},"cell_type":"markdown","source":"<hr>"},{"metadata":{"_uuid":"6a059a8dcf9d93a650f1ccaa8e2bfa3e087219f3"},"cell_type":"markdown","source":"<a id=\"1\"></a> <br>\n<h1><center> Definitions </center></h1>"},{"metadata":{"_uuid":"f9038a808568c6f103024922560ab2c3b526f4ff"},"cell_type":"markdown","source":"#### Define functions "},{"metadata":{"trusted":true,"_uuid":"1dff300a2a00ad51078dd292cf6288bf241934a7"},"cell_type":"code","source":"def reduce_mem_usage(df, verbose=True):\n    \"\"\"\n    Reduce dataset memory usage by changing the dtypes within each dataset column\n    :param df: (pd.DataFrame) dataset to be changed\n    :param verbose: (bool) Flag indicating if we should verbose actions\n    \n    :return: (pd.DataFrame) optimized dataset\n    \"\"\"\n    # list numeric datatypes\n    numerics = ['int8', 'int16', 'int32', 'int64', 'float16', 'float32', 'float64']\n    \n    # set up the initial memory usage\n    start_mem = df.memory_usage().sum() / 1024**2   \n    \n    # for each column in the dataset\n    for col in df.columns:\n        # get the column type\n        col_type = df[col].dtypes\n        \n        # if the column type is within the numeric datatypes\n        if col_type in numerics:\n            # calculate the column value range\n            c_min = df[col].min()\n            c_max = df[col].max()\n            \n            # Change the column type based on its range\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.uint8).min and c_max <= np.iinfo(np.uint8).max:\n                    df[col] = df[col].astype(np.uint8)\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.uint16).min and c_max < np.iinfo(np.uint16).max:\n                    df[col] = df[col].astype(np.uint16)\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.uint32).min and c_max < np.iinfo(np.uint32).max:\n                    df[col] = df[col].astype(np.uint32)\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                elif c_min > np.iinfo(np.uint64).min and c_max < np.iinfo(np.uint64).max:\n                    df[col] = df[col].astype(np.uint64) \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        # if the column is not of numeric type\n        elif col_type == np.object:\n            # change it to categorical\n            df[col] = df[col].astype('category')\n                \n    end_mem = df.memory_usage().sum() / 1024**2\n    if verbose: \n        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":"b4ec73ad52e222246e2fb1fd4c9e00bc51ae4b1c"},"cell_type":"code","source":"def dataset_overview(df):\n    \"\"\"\n    \"\"\"\n    dataset_info = list()\n    for c in df.columns:\n        # create a list of column information to add\n        column_info = list()\n        \n        # add the column name\n        column_info.append(c)\n        \n        # add the column type\n        column_info.append(df[c].dtypes)\n        \n        # add the column memory usage\n        column_info.append('%.2f' % (df[c].memory_usage(index=False, deep=True)/(1024*1024)))\n        \n        # add the percentage of fill\n        column_info.append('%.1f%%' % (100*df[c].count()/df.shape[0]))\n        \n        # add the number of unique values\n        column_info.append(df[c].nunique())\n        \n        # add the example of values\n        column_info.append(df[c].unique())\n        \n        # add the column info to the dataset info\n        dataset_info.append(column_info)\n        \n    # return a dataframe with all information\n    return pd.DataFrame(data=dataset_info, columns=['Column Name', 'Column Type', 'Memory Usage (MB)', '% of Fill', 'Unique Values', 'Examples'])\n        ","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f1c3c5974e56930d586261855640c3ee41c8c3f0"},"cell_type":"markdown","source":"<hr>"},{"metadata":{"_uuid":"6a059a8dcf9d93a650f1ccaa8e2bfa3e087219f3"},"cell_type":"markdown","source":"<a id=\"1\"></a> <br>\n<h1><center>Database Loading</center></h1>"},{"metadata":{"_uuid":"77685cfee475dbaec8b2584dee464534dcdaab14"},"cell_type":"markdown","source":"#### New transactions dataset "},{"metadata":{"trusted":true,"_uuid":"b6b5a505bfecabd70c6dac0ee30386d074b03953"},"cell_type":"code","source":"rNewTransc = pd.read_csv('../input/new_merchant_transactions.csv', parse_dates=['purchase_date'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"025164dff91c12cd15f44ea70eae530354f3e048"},"cell_type":"code","source":"dataset_overview(rNewTransc)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"280b02e008135d8f478535d8f69a873d624871de"},"cell_type":"markdown","source":"#### Historical transactions dataset "},{"metadata":{"trusted":true,"_uuid":"f3701fcfbecee292d1d8c335257ba98ce7a5d333"},"cell_type":"code","source":"rHistTransc = pd.read_csv('../input/historical_transactions.csv', parse_dates=['purchase_date'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d56a4df232aa1c10344f1d5a7433582a08a0e2db"},"cell_type":"code","source":"dataset_overview(rHistTransc)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"695b2771c88c602cb776e826928c4298c61feb53"},"cell_type":"markdown","source":"#### Merchant data "},{"metadata":{"trusted":true,"_uuid":"12d056482d3de7570f8e2ac8bc519fb0e2388df7"},"cell_type":"code","source":"rMerchant = pd.read_csv('../input/merchants.csv', parse_dates=['first_active_month'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"e63bff27037eddf994406aed96e40d232fb931a0"},"cell_type":"code","source":"dataset_overview(rMerchant)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f582b62109d949bd684be8afdd186503b6bdcefe"},"cell_type":"markdown","source":"#### Train set "},{"metadata":{"trusted":true,"_uuid":"16700bf38544ebc0ff20b1e5c2a8411e0ddcd97c"},"cell_type":"code","source":"rTrain = pd.read_csv('../input/train.csv', parse_dates=['first_active_month'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"da432a776159a98c447bd3c6164b1db16b0843fb"},"cell_type":"code","source":"dataset_overview(rTrain)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"d57c14aafce0a10cbdeb87af22f45e5d63fc2d7d"},"cell_type":"markdown","source":"#### Test set "},{"metadata":{"trusted":true,"_uuid":"cb880f9b0e361a03d6da6440794f5ef6908cd1ab"},"cell_type":"code","source":"rTest = pd.read_csv('../input/test.csv', parse_dates=['first_active_month'])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"d5cebd16bbf66399c3f29f4162b9978756a859c0"},"cell_type":"code","source":"dataset_overview(rTest)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"2a9963061942c3338127b20cf2fa11cbef33afad"},"cell_type":"markdown","source":"<hr>"},{"metadata":{"_uuid":"6a059a8dcf9d93a650f1ccaa8e2bfa3e087219f3"},"cell_type":"markdown","source":"<a id=\"2\"></a> <br>\n<h1><center>Data Manipulation</center></h1>"},{"metadata":{"_uuid":"a1ebef657df67011c9e067657ddddb5ed6c8e513"},"cell_type":"markdown","source":"#### Binarize data"},{"metadata":{"trusted":true,"_uuid":"2836407b6439bfefed1951422b5eb8e64a7d540f"},"cell_type":"code","source":"rHistTransc['authorized_flag'] = rHistTransc['authorized_flag'].replace({'Y':1, 'N':0})\nrHistTransc['category_1'] = rHistTransc['category_1'].replace({'Y':1, 'N':0})\nrNewTransc['authorized_flag'] = rNewTransc['authorized_flag'].replace({'Y':1, 'N':0})\nrNewTransc['category_1'] = rNewTransc['category_1'].replace({'Y':1, 'N':0})","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"f6bfe4637c7de521f607773e1c47649e647d5da1"},"cell_type":"markdown","source":"#### Add the train data to historical and new transactions "},{"metadata":{"trusted":true,"_uuid":"0ae3d50c65968a2b93577748118cb52eb4db0a65"},"cell_type":"code","source":"tHistTrain = rHistTransc.merge(rTrain, on='card_id', how='inner')\ntNewTrain = rNewTransc.merge(rTrain, on='card_id', how='inner')\n\ntHistTest = rHistTransc.merge(rTest, on='card_id', how='inner')\ntNewTest = rNewTransc.merge(rTest, on='card_id', how='inner')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"85e47b4210612320142883f192fadc7dccddde5d"},"cell_type":"code","source":"rTrain.columns","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"82228067c71c472fbb2d2ac5f88b9231f68b9c62"},"cell_type":"code","source":"(tHistTrain['card_id'].nunique())/rTrain['card_id'].nunique()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"b8003f3dfe4b6da1f07e5023716c199a4965fd8c"},"cell_type":"code","source":"(tNewTrain['card_id'].nunique())/rTrain['card_id'].nunique()","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"ca30ff06eb00207cfca364539eb8ce6ffd8ff8b2"},"cell_type":"markdown","source":"#### Optimize memory"},{"metadata":{"trusted":true,"_uuid":"7b80681012678554b2d024c068c2c252158d75d1"},"cell_type":"code","source":"tHistTrain = reduce_mem_usage(tHistTrain)\ntHistTest = reduce_mem_usage(tHistTest)\ntNewTrain = reduce_mem_usage(tNewTrain)\ntNewTest = reduce_mem_usage(tNewTest)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b1189629f5bc212e9830ebe9aa611335ee652f73"},"cell_type":"raw","source":"cols = [\n    'card_id', 'first_active_month', 'authorized_flag', 'purchase_date', 'month_lag', 'purchase_amount', 'installments',\n    'city_id', 'subsector_id', 'merchant_category_id', 'merchant_id', \n    'state_id', 'category_1', 'category_2', 'category_3', 'feature_1', 'feature_2', 'feature_3', 'target'\n]\ndataset_overview(tNewTest[cols])"},{"metadata":{"_uuid":"017409a54decaea0b8d9ac9ea9fab0392e58c34b"},"cell_type":"markdown","source":"#### Merge the merchant data "},{"metadata":{"trusted":true,"_uuid":"9841b7d6727e126e451050774b614b85a616f363"},"cell_type":"code","source":"tNewTest[cols].columns","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_uuid":"dcb33da2b23db21c46f24928072448472acaf7d9"},"cell_type":"code","source":"tNewTest.merge(rMerchant.drop(['merchant_category_id', 'subsector_id', 'category_1', 'c'], axis=1), on=['merchant_id'], how='left')","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"b3c2a5853f29c314dca418e1ad8420358fbf74fe"},"cell_type":"markdown","source":"<a id=\"3\"></a> <br>\n<h1><center>Feature Engineering</center></h1>"},{"metadata":{"_uuid":"f0312fca90a060e58d415b7ef8879c1e180ce378"},"cell_type":"markdown","source":"#### Create dummy variables"},{"metadata":{"trusted":true,"_uuid":"ff5191c7eec21ec77909bb4f4b234b6ddfded6ba"},"cell_type":"code","source":"","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1404ca45172ffdfd1914e8fb882cd5407d0173dd"},"cell_type":"markdown","source":"#### Add the customer lifetime up to the data generation "},{"metadata":{"trusted":true,"_uuid":"596129103ceaa4889790858b238889e1c4ecb18a"},"cell_type":"code","source":"tNewTrain['elapsed_time'] = (tNewTrain['first_active_month'].max() - tNewTrain['first_active_month'].dt.date).dt.days\ntHistTrain['elapsed_time'] = (tHistTrain['first_active_month'].max() - tHistTrain['first_active_month'].dt.date).dt.days","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"1f210906accc70acc206cb036a7fc85e9021476e"},"cell_type":"markdown","source":"#### Normalize difference to current date and time"},{"metadata":{"trusted":true,"_uuid":"f787e3f81121b236528b59d0e0a723bcef510e4b"},"cell_type":"raw","source":"historical_transactions['month_diff'] = ((datetime.datetime.today() - historical_transactions['purchase_date']).dt.days)//30\nhistorical_transactions['month_diff'] += historical_transactions['month_lag']\n\nnew_transactions['month_diff'] = ((datetime.datetime.today() - new_transactions['purchase_date']).dt.days)//30\nnew_transactions['month_diff'] += new_transactions['month_lag']"},{"metadata":{"trusted":true,"_uuid":"9db35828b8cb62fbe95a9d30e63b30b49966a304"},"cell_type":"code","source":"dataset_overview(rHistTransc)","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e7ec4354fb7cb3bccb5a7a8ba9301017c71f119c"},"cell_type":"markdown","source":"#### Get dummies of categorical data "},{"metadata":{"trusted":true,"_uuid":"b1da58e20da9fbb6d200119b242c168ae8fc5843"},"cell_type":"raw","source":"historical_transactions = pd.get_dummies(historical_transactions, columns=['category_2', 'category_3'])\nnew_transactions = pd.get_dummies(new_transactions, columns=['category_2', 'category_3'])"},{"metadata":{"trusted":true,"_uuid":"e4272e50efd89811c00918127f3ff678e067831c"},"cell_type":"code","source":"historical_transactions = reduce_mem_usage(historical_transactions)\nnew_transactions = reduce_mem_usage(new_transactions)\n\nagg_fun = {'authorized_flag': ['mean']}\nauth_mean = historical_transactions.groupby(['card_id']).agg(agg_fun)\nauth_mean.columns = ['_'.join(col).strip() for col in auth_mean.columns.values]\nauth_mean.reset_index(inplace=True)\n\nauthorized_transactions = historical_transactions[historical_transactions['authorized_flag'] == 1]\nhistorical_transactions = historical_transactions[historical_transactions['authorized_flag'] == 0]","execution_count":null,"outputs":[]},{"metadata":{"_uuid":"e7bcc89ba28d9521aeeed850b5929669019f07e2"},"cell_type":"markdown","source":"#### Add purchase month"},{"metadata":{"trusted":true,"_uuid":"76c41ff3af21c7bfa9194f873f6f47c5849eb4d8"},"cell_type":"raw","source":"historical_transactions['purchase_month'] = historical_transactions['purchase_date'].dt.month\nauthorized_transactions['purchase_month'] = authorized_transactions['purchase_date'].dt.month\nnew_transactions['purchase_month'] = new_transactions['purchase_date'].dt.month"},{"metadata":{"trusted":true,"_uuid":"82c25c7cd0d075195fb7bd63211c66f0dac9304b"},"cell_type":"raw","source":"def aggregate_transactions(history):\n    \n    history.loc[:, 'purchase_date'] = pd.DatetimeIndex(history['purchase_date']).\\\n                                      astype(np.int64) * 1e-9\n    \n    agg_func = {\n    'category_1': ['sum', 'mean'],\n    'category_2_1.0': ['mean'],\n    'category_2_2.0': ['mean'],\n    'category_2_3.0': ['mean'],\n    'category_2_4.0': ['mean'],\n    'category_2_5.0': ['mean'],\n    'category_3_A': ['mean'],\n    'category_3_B': ['mean'],\n    'category_3_C': ['mean'],\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_month': ['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_history = history.groupby(['card_id']).agg(agg_func)\n    agg_history.columns = ['_'.join(col).strip() for col in agg_history.columns.values]\n    agg_history.reset_index(inplace=True)\n    \n    df = (history.groupby('card_id')\n          .size()\n          .reset_index(name='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":"44ea0524d0af0001c5ea57a6593be35e2402d0b6"},"cell_type":"raw","source":"history = aggregate_transactions(historical_transactions)\nhistory.columns = ['hist_' + c if c != 'card_id' else c for c in history.columns]\nhistory[:5]"},{"metadata":{"trusted":true,"_uuid":"be09e1a7d4f307beb59c18bf61261239877da4e3"},"cell_type":"raw","source":"authorized = aggregate_transactions(authorized_transactions)\nauthorized.columns = ['auth_' + c if c != 'card_id' else c for c in authorized.columns]\nauthorized[:5]"},{"metadata":{"trusted":true,"_uuid":"78b87a7863b62fa3758ef3167504d3ad8c279f3d","scrolled":true},"cell_type":"raw","source":"new = aggregate_transactions(new_transactions)\nnew.columns = ['new_' + c if c != 'card_id' else c for c in new.columns]\nnew[:5]"},{"metadata":{"_uuid":"843a929ddea5e317f882c58c3b69f5e5a4476a38"},"cell_type":"markdown","source":"The second function first aggregates on the two variables `card_id` and `month_lag`. Then a second grouping is performed to aggregate over time:"},{"metadata":{"trusted":true,"scrolled":false,"_uuid":"f210acd074326ea74c1b9316eec3136f4ab73855"},"cell_type":"raw","source":"def 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_transactions) \nfinal_group[:10]"},{"metadata":{"trusted":true,"_uuid":"95bfafc86d846204d7defa8015d9d2b936a72844"},"cell_type":"raw","source":"def 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"},{"metadata":{"trusted":true,"_uuid":"5a85689aef5f58b6af8d8617b0d8b97ee5e85a43"},"cell_type":"raw","source":"additional_fields = successive_aggregates(new_transactions, 'category_1', 'purchase_amount')\nadditional_fields = additional_fields.merge(successive_aggregates(new_transactions, 'installments', 'purchase_amount'),\n                                            on = 'card_id', how='left')\nadditional_fields = additional_fields.merge(successive_aggregates(new_transactions, 'city_id', 'purchase_amount'),\n                                            on = 'card_id', how='left')\nadditional_fields = additional_fields.merge(successive_aggregates(new_transactions, 'category_1', 'installments'),\n                                            on = 'card_id', how='left')"},{"metadata":{"trusted":true,"_uuid":"e155324a5fd4a7e07b06a5e00b7fb4a02afc38d9"},"cell_type":"code","source":"","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}