<jupyter_start><jupyter_text>AutoAggregator for Merging External TablesIn many situations your data may be stored in several tables. Let's see how `AutoConverter` can take care of it. After you perform conversion you can proceed with `AutoLearn` as usual.Sometimes not all the data is stored in one table. For example you can have some user information in one table and some additional data in other tables, such as transactions, user activity logs etc. If you are trying to predict something about one user you can have hundreds of transactions for each user. And you want to use this data too.If you have multiple data tables you want to use for your task you can useAutoConverter for linking these tables and grouping values. Let's load the libraries that we are going to use and have a closer look on the datasets:<jupyter_code>import os
import sys
sys.path.append('../')
import pandas as pd
import warnings
warnings.filterwarnings('ignore')
from learnit import AutoConverter, AutoLearn, Evaluate
warnings.filterwarnings('ignore')

dirpath = "data/kaggle-kkbox-churn-prediction-challenge-1k/"
df_main = pd.read_csv(os.path.join(dirpath, 'members_train.csv'), parse_dates=['registration_init_time', 'expiration_date'])
df_sub = pd.read_csv(os.path.join(dirpath, 'transactions.csv'), parse_dates=['membership_expire_date', 'transaction_date'])
df_sub2 = pd.read_csv(os.path.join(dirpath, 'user_logs.csv'), parse_dates=['date'])<jupyter_output>Using TensorFlow backend.<jupyter_text>You need to predict churn of the users and you have twotables:1) users table. This table consists of users, some information about them and, most importantly the target values (labels for prediction). The latter suggests that this should be the main table for the purpose of `AutoConverter`. This table also contains ID column that we are going to use to link it to the second table.<jupyter_code>df_main.head()<jupyter_output><empty_output><jupyter_text>2) user_transactions table: this table consists of separate transaction records. Each user, represented by User_ID, can have multiple transactions. In order to get a single feature matrix for our learning algorithm, we will need to group this information by User_ID before we merge user table with user_transactions table. But don't worry! `AutoConverter` will take care of this!<jupyter_code>df_sub.head()
df_sub2.head()<jupyter_output><empty_output><jupyter_text>Let's take care about both of the tables. For that we will create `AutoConverter` object as usual. We are also creating `subtables` dictionary that tells the system how to link the secondary tables to the main one. For each secondary table this dictionary contains its name, the variable that contains the table (`df_sub` in our case), the column in the main table that is used for linking (`link_key`) and the column of the secondary table used for linking and grouping items (`group_key`). You can use as many secondary tables as you want.<jupyter_code>ac = AutoConverter(target='is_churn')
"""subtables = {'second_table': {'table': df_sub,
                           'link_key': 'msno',
                           'group_key': 'msno'}}"""
subtables = {'transactions': {'table': df_sub,
                           'link_key': 'msno',
                           'group_key': 'msno'},
                'user_logs': {'table': df_sub2,
                           'link_key': 'msno',
                           'group_key': 'msno'}}
X, y = ac.fit_transform(df_main, subtables)

print(X.shape)
ac.feature_names<jupyter_output>(898, 302)<jupyter_text>As we can see now we have features from the main table and the ones from the secondary table. Printing out feature names also gives us understanding about how well the system has extracted the features.  Now we can train the model using `AutoLearn` the same way as we did before.<jupyter_code>print(X.shape)
al = AutoLearn(level=2)
al.learn(X, y)
info, metrics = al.display(tab=False)
info.head()
metrics.head()
#Visualize train/test performance with error bars (standard deviation)
metrics.plot(kind="bar", rot=0)<jupyter_output><empty_output><jupyter_text>It is usual for the predictive models to achieve better results on *training set* than on *test_set*. This happens because the model can see the data in the *training set* and take it into account and can not see the data from the *test set*. This difference, however should not be too big. For example if the model achieves 99% results on training data and 60% on test data that would indicate that the model is overfitting the data and fails to generalize to the unseen data. It would be a good idea to increase the dataset in this case.  Understanding Predictive Models via Feature Importance CalculationLet's have a closer look at the model that AutoLearn trained for us. Here's how we can get the full list of metrics for the given model.Users usually want to understand what stands behind the model's decisions. One way to do so is to calculate the feature importance. This metric shows which data column is giving the highest contribution to the decision has made. This is a good way to perform a sanity check of the model. To activate the function we use pre-fit `AutoConverter` and pre-trained `AutoLearn` instance.<jupyter_code># Feature Importance calculation with Evaluate
e = Evaluate(ac=ac, alearn=al)
col_imp_df = e.calculate_column_importance()
pd.DataFrame(col_imp_df["roc_auc"])
# Will be implemented as a function. Don't worry!
col_imp_df["roc_auc"].sort_values().plot(kind="barh")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0744085244717356, 'fraction_numerical': 0.0032508578652700017, 'mean_word_length': 4.674180327868853, 'pattern_counts': {'":': 0, '<': 23, '<?xml version=': 0, '>': 23, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '6', 'dir': 'jupyter-structured-clean-dedup', 'id': '666966', 'license': 'Apache-2.0', 'n_tokens_mistral': 1523, 'n_tokens_neox': 1466, 'n_words': 699, 'path': 'notebooks/03_auto_aggregation.ipynb', 'repo_name': 'suhara/learnit', 'size': '5537'}