{"cells":[{"metadata":{},"cell_type":"markdown","source":"## Intro"},{"metadata":{},"cell_type":"markdown","source":"### TL;DR\nYou can use the sql database or two column dataframe produced in this book to select training and validation sets that are distributed similarly to the test set. [Dataset here](https://www.kaggle.com/calebeverett/riiid-folds).\n\n\n### Motivation\nIn general, we want to train on data that is distributed as similarly as possible to the test data with respect to both the variable we are predicting and the features we are using in the model. We also want a validation set that is simiar to the test set, but doesn't have any leakage from the training set.\n\nFor this dataset, I thought is was important to ensure that the train and validation sets had distributions similar to the test set for the following variables:\n* Percent of interactions answered correctly\n* Number of interactions per user\n* Where in users' histories interactions begin\n\nThe test set has been described as being comprised of mostly records that continue sequentially from records in the training data. If that is the case, then the training and validation sets should be constructed similarly, so that the included sequences of users' interactions start later in users' histories, in fact, towards the end of their histories based on the description of the test set. This seems especially important given the nature of users' learning and differences in the likely changes in the characteristics of the questions delivered to users' over time. For example, on the one hand, we would expect users to correctly answer a greater percentage of questions as their knowledge increases through the use of the system over time. On the other hand, though, users are delivered more difficult questions over time to advance their learning, which may otherwise may make it more difficult for them to answer questions correctly. Below, I compared users' performance on questions later in their histories with their earlier histories and found that there was a significant increase in performance over time. Regardless of the magnitude of these two illustative effects, the important point is that the characteristics of the later interactions are liklely different from the early ones and that since we are going to be tesing on the later ones, we should construct training and validation sets that are comprised mostly of later records.\n\nI had initially thought that it was beneficial to train on records of users that would be seen in test set, but have since come to the conclusion that for decision tree based models, where there is no explicit relationship between records, and user_id isn't used as a feature, that the benefit is minimal. As long as we are able to construct the features our models use to make predictions from an accurate representation of each users' state at the beginning of the predicton period, it won't make much of a difference whether we are predicting on interactions of users that were in our training set. (Is this right?) As it is currently, if you train a model on a subset of the training data, it is likely that you are predicting on a test set that includes records primarily related to users you didn't train on. Perhaps there is something inherent in users' interactions that carries across the sequence of their interactions that is valuable, but it seems like as long as you have the state data for each user as of the start of the sequences in the test set, it is much more imporant to make sure that the time frames of the sequences are similar. \n\n### Methodology\nThe rest of this notebook constructs folds that can be used interchangeably for training or validation purposes. It divides the training data into 40 folds with approximately 1.2 million records in each that are distributed in accordance with the description provided by the competition organizers of the test set and mutually exclusive with respect to users. The end product is an sqlite database that can be easily read into pandas dataframes that includes the training data with a fold column appended. I've also created a two column (row_id, fold) pandas dataframe that can be joined with your data to select data sets.\n\n* Users divided randomly into 40 folds\n* Within each fold:\n * 10% of users have all of their records included (~20% of records)\n * 60% of users have only their records starting in the later half of their complete histories included (~30% of records) \n * 20% of users have only their records starting in the in the first half of their complete histories included (~50% of records)\n \n### Results\nThe objective of having good data sets is obviously to have better model results. I was looking for an improvement in model performance and for my validation scores to be closer to my leader board scores. While not an exhaustive analysis of the impact, I did train two models with the same features and hyperparameters on a similar number of records using my old split methodology and using this new methodology for the purpose of comparison. There was less of a difference here than I was expecting, but my validation score is now closer to my leaderboard score. It is difficult to draw any definitive conclusions from just two models, but I believe this metodology will ultimately lead to better results throughout the competition.\n\n\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n
Train DatasetValid DatasetTrain AUCValid AUCPublic
Leaderboard
\n
    \n
  • 24.5 million records
    • \n
  • 85k users
    • \n
  • 100% of users (100% of records) from first interaction
    • \n
\n 		\n
    \n
  • 4.9 million records
    • \n
  • 17k users
    • \n
  • 10% of users (20% of records) from first interaction
    • \n
  • 90% of users (80% of records) from beginning on average 50%\n of the way through sequence of interactions
    • \n
\n 		0.7710.7640.772
\n
    \n
  • 23.4 million records
    • \n
  • 172k users
    • \n
  • 10% of users (~20% of records) from first interaction
    • \n
  • 60% of users (~30% of records) from an average of 75% of the way through sequence of interactions
    • \n
  • 30% of users (~50% of records) from an average of 25% of the way through sequence of interactions
    • \n
\n 		\n
    \n
  • 2.6 million records
    • \n
  • 19k users
    • \n
  • 10% of users (~20% of records) from first interaction
    • \n
  • 60% of users (~30% of records) from an average of 75% of the way through sequence of interactions
    • \n
  • 30% of users (~50% of records) from an average of 25% of the way through sequence of interactions
    • \n
\n 		0.7730.7690.773
"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"import pandas as pd\nfrom pathlib import Path\nfrom scipy import stats\nimport subprocess\nimport sqlite3\nfrom tqdm.notebook import tqdm, trange\n\nimport plotly\nimport plotly.express as px\npd.options.plotting.backend = 'plotly'","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"PATH = Path('/kaggle/input/riiid-test-answer-prediction')\nDB = 'riiid.db'\n\ndtypes_orig = {\n 'lectures': {\n 'lecture_id': 'uint16',\n 'tag': 'uint8',\n 'part': 'uint8',\n 'type_of': 'str',\n },\n 'questions': {\n 'question_id': 'uint16',\n 'bundle_id': 'uint16',\n 'correct_answer': 'uint8',\n 'part': 'uint8',\n 'tags': 'str',\n \n },\n 'train': {\n 'row_id': 'int64',\n 'timestamp': 'int64',\n 'user_id': 'int32',\n 'content_id': 'int16',\n 'content_type_id': 'int8',\n 'task_container_id': 'int16',\n 'user_answer': 'int8',\n 'answered_correctly': 'int8',\n 'prior_question_elapsed_time': 'float32', \n 'prior_question_had_explanation': 'bool'\n }\n}\n\ntype_map = {\n 'int64': 'INTEGER',\n 'int32': 'INTEGER',\n 'int16': 'INTEGER',\n 'int8': 'INTEGER',\n 'uint8': 'INTEGER',\n 'uint16': 'INTEGER',\n 'str': 'TEXT',\n 'bool': 'INTEGER',\n 'float32': 'REAL'\n}","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"conn = sqlite3.connect(DB)\nc = conn.cursor()","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"def sql_create_table(table_id):\n primary_keys = {'lectures': 'lecture_id', 'questions': 'question_id', 'train': 'row_id'}\n primary_key = primary_keys[table_id]\n\n columns = [f'{primary_key} INTEGER PRIMARY KEY']\n \n columns = columns + [f'{c} {type_map[t]}' for c,t in dtypes_orig[table_id].items()\n if c != primary_keys[table_id]]\n \n columns = (', ').join(columns)\n \n return f\"\"\"\n DROP TABLE IF EXISTS {table_id};\n \n CREATE TABLE {table_id}\n ({columns});\n \"\"\"\n\ndef sql_update_fold_seq_start(fold, beg_pct=0.1, late_pct=0.6):\n return f\"\"\"\n UPDATE folds\n SET seq_start = 'beg',\n task_container_id_min = 0\n WHERE fold = {fold} AND ABS(RANDOM() % 100) < {int(beg_pct * 100)};\n \n UPDATE folds\n SET seq_start = 'late',\n task_container_id_min = task_container_id_max * 0.5 + (task_container_id_max * 0.5) * ABS(RANDOM() % 100) / 100\n WHERE fold = {fold}\n AND task_container_id_min is NULL\n AND ABS(RANDOM() % 100) < {int(100 * (late_pct / (1 - beg_pct)))};\n \n UPDATE folds\n SET seq_start = 'early',\n task_container_id_min = task_container_id_max * 0.5 * ABS(RANDOM() % 100) / 100\n WHERE fold = {fold}\n AND task_container_id_min is NULL;\n \"\"\"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Load Tables"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"for table_id in dtypes_orig:\n c.executescript(sql_create_table(table_id))\n conn.commit()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"%%time\nfor t in tqdm(dtypes_orig):\n result = subprocess.run(['sqlite3', DB, '.mode csv',\n f'.import {(PATH/t).absolute()}.csv {t}'],\n capture_output=True, encoding='utf-8')\n \n n_records = c.execute(f'select count(*) from {t}').fetchone()[0]\n print(f'Loaded {n_records:0,d} to {t} table.')\n \n# datatype mismatch is the header column failing\n# --skip n not implemented in sqlite3 until version 3.32","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Clean Up a Few Columns"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"c.execute(\"\"\"\n UPDATE train\n set prior_question_elapsed_time = \"0\"\n WHERE prior_question_elapsed_time = \"\";\n\"\"\").fetchone()","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"c.executescript(\"\"\"\n ALTER TABLE train\n RENAME prior_question_had_explanation TO prior_question_had_explanation_string;\n \n ALTER TABLE train\n ADD COLUMN prior_question_had_explanation INTEGER;\n\"\"\").fetchone()","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true,"_kg_hide-output":true},"cell_type":"code","source":"%%time\nc.execute(\"\"\"\n UPDATE train\n SET prior_question_had_explanation = (\n SELECT t.prior_question_had_explanation_string = \"True\"\n FROM train t\n WHERE\n train.row_id = t.row_id\n )\n\"\"\").fetchone()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"pd.read_sql('select * from train limit 10', conn)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"%%time\nc.execute(\"\"\"\n CREATE INDEX train_user_id_task_container_id_index ON train (user_id, task_container_id);\n \"\"\").fetchone()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"db_size = pd.read_sql('SELECT page_count * page_size as size FROM pragma_page_count(), pragma_page_size()', conn)['size'][0]\nprint(f'Total size of database is: {db_size/1e9:0.3f} GB')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Difference in Early and Late Performance"},{"metadata":{},"cell_type":"markdown","source":"This illustrates nicely the difference in peformance between users' early and late performance. This takes the difference in the percentage of questions answered correctly in the first ten task containers and compares it to the percentage of questions answered correctly in the last ten task containers. The differences are then sorted starting with those that had the biggest decrease in performance and ending with those that had the biggest increase. If the performance was the same, there would be the same amount of purple on the left as there is red on the right, but you can clearly see that there are a lot more users with increases in performance.\n\nI ran this for 100,000 users and the difference was as a staggering ten points, from an average of 51% in the first ten task containers, to an average of 61% in the last ten. The p-value on the t-statistic is basically zero. As an aside, it is interesting to note that the average over the last 10 task containers is actually lower than the overall average of approximately 65%, perhaps supporting the hypothesis that the later questions are more difficult than the earlier ones."},{"metadata":{"_kg_hide-output":true,"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"%%time\ndf_users = pd.read_sql(\"\"\"\n SELECT t.user_id,\n MAX(c2.task_container_id_total) task_container_id_total,\n MAX(c2.row_id_count_total) row_id_count_total,\n MAX(c2.answered_correctly_total) answered_correctly_total,\n MAX(c2.task_container_id_early) task_container_id_early,\n MAX(c2.row_id_count_early) row_id_count_early,\n MAX(c2.ansered_correctly_early) answered_correctly_early,\n MIN(t.task_container_id) task_container_id_late,\n COUNT(t.row_id) row_id_count_late,\n SUM(t.answered_correctly) answered_correctly_late,\n ROUND(MAX(c2.ansered_correctly_early) * 100 / MAX(c2.row_id_count_early), 2) answered_correctly_early_pct,\n ROUND(SUM(t.answered_correctly) * 100 / COUNT(t.row_id), 2) answered_correctly_late_pct,\n ROUND(MAX(c2.answered_correctly_total) * 100 / MAX(c2.row_id_count_total), 2) answered_correctly_total_pct,\n ROUND(MAX(c2.answered_correctly_total) * 100 / MAX(c2.row_id_count_total), 2) - ROUND(MAX(c2.ansered_correctly_early) * 100 / MAX(c2.row_id_count_early), 2) late_early_delta\n FROM (\n SELECT c.user_id,\n MAX(t.task_container_id) task_container_id_early,\n MAX(c.task_container_id) task_container_id_total,\n SUM(t.answered_correctly) ansered_correctly_early,\n MAX(c.answered_correctly) answered_correctly_total,\n MAX(c.row_id_count) row_id_count_total,\n COUNT(t.row_id) row_id_count_early\n FROM (\n SELECT user_id, MAX(task_container_id) task_container_id, SUM(answered_correctly) answered_correctly, COUNT(row_id) row_id_count\n FROM train\n WHERE content_type_id = 0\n GROUP BY user_id\n LIMIT 100000\n ) c\n JOIN train t\n ON t.user_id = c.user_id AND t.content_type_id = 0 AND t.task_container_id < 10 /* c.task_container_id * 0.1 */\n GROUP BY c.user_id\n ORDER BY c.user_id\n ) c2\n JOIN train t\n ON t.user_id = c2.user_id AND t.content_type_id = 0 AND t.task_container_id > c2.task_container_id_total - 10 /* * 0.1 */\n WHERE c2.task_container_id_total > 30\n GROUP BY c2.user_id\n ORDER BY c2.user_id\n\n \"\"\", conn)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"df_users['positive_delta'] = df_users.late_early_delta > 0\nfig = (df_users[['user_id', 'late_early_delta', 'positive_delta']]\n .iloc[600:800].sort_values('late_early_delta').set_index('user_id')\n .plot(kind='bar', y='late_early_delta', color='positive_delta',\n title='Difference in Percent Answered Correctly of - First 10 Task Container Ids vs. Last 10'))\nfig.update_xaxes(type='category')\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"stats.ttest_ind(df_users.answered_correctly_early_pct, df_users.answered_correctly_late_pct)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Construct Folds"},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"c.executescript(\"\"\"\n DROP TABLE IF EXISTS folds;\n\n CREATE TABLE folds (\n user_id INTEGER PRIMARY KEY,\n fold INTEGER,\n seq_start TEXT,\n task_container_id_min INTEGER,\n task_container_id_max INTEGER);\n\"\"\").fetchone()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"%%time\nn_folds = 40\nc.executescript(f\"\"\"\n INSERT INTO folds (user_id, task_container_id_max)\n SELECT user_id, MAX(task_container_id) task_container_id_max\n FROM train\n GROUP BY user_id\n ORDER BY user_id;\n \n UPDATE folds\n SET fold = ABS(RANDOM() % {n_folds});\n\"\"\").fetchone()","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"%%time\nc.execute(\"\"\"\n CREATE INDEX folds_user_id_index ON folds (user_id);\n \"\"\").fetchone()","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"def get_df_folds():\n\n return pd.read_sql(\"\"\"\n SELECT fold, seq_start, COUNT(user_id) user_count,\n AVG(task_container_id_min) task_container_id_min_avg,\n AVG(task_container_id_max) task_container_id_max_avg\n FROM folds\n GROUP BY fold, seq_start;\n \"\"\", conn)\n\ndf_folds = get_df_folds()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Review Folds\n\nThe charts below show that folds have basically the same number of users and records in them."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"df_folds.groupby('fold').sum().plot(kind='bar', y='user_count',\n title='User Count by Fold')","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"df_folds.groupby('fold').sum().plot(kind='bar', y='task_container_id_max_avg',\n title='Average Task Containers by Fold')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Set Minimum Task Container Id to Include\n\nHere we set the minimum task container id that will be included for users in each of the three groups. Note that the mix of records shifts towards the earlier groups since by design we are including smaller percentages of the later groups.\n\n\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n \n
GroupUsersRecordsDescription
Beginning10%~20%\n
    \n
  • Includes sequences starting with users' initial interaction
    • \n
  • Minimum task container id = 0
    • \n
\n
Late60%~30%\n
    \n
  • Includes sequences starting in the later half of users' task containers
    • \n
  • Minimum included task container id determined by selecting randomly from second half of users' task containers
    • \n
  • Effectively averages to including 25% of users' records
    • \n
\n
Early30%~50%\n
    \n
  • Includes sequences starting in the first half of users' task containers
    • \n
  • Minimum included task container id determined by selecting randomly from first half of users' task containers
    • \n
  • Effectively averages to including 75% of users' records
    • \n
\n
"},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"%%time\nc.execute(\"\"\"\n UPDATE folds\n SET task_container_id_min = NULL,\n seq_start = NULL;\n\"\"\").fetchone()\n\nfor f in trange(n_folds):\n c.executescript(sql_update_fold_seq_start(f)).fetchone()","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"df_folds_seq = (get_df_folds().groupby(['fold', 'seq_start']).max()\n .unstack('seq_start').reset_index().set_index('fold'))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"This chart shows that the desired mix of beginning, early and late **users** achieved."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"(df_folds_seq.user_count\n .plot(kind='bar', title='Composition of Folds by Sequence Start - Users'))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"This chart shows that each of the three sets of user sequences were basically distributed similarly with respect to task containers before determining where in those sequences to start including them."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"(df_folds_seq.task_container_id_max_avg\n .plot(title='Average Max Task Container Id by Fold'))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"And this chart shows where in the users' sequences of task containers they started getting included in the folds. The beginning group obviously starts at zero, the late group starts at an average of approximately 100 and and the early group starts at an average of approximately 50. The late group starts at approximately 75% of the way through the sequences and the early group starts at approximately 25% of the way through the sequences."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"(df_folds_seq.task_container_id_min_avg\n .plot(title='Average Starting Task Container Id by Fold'))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Add Fold Column to Train Table"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"c.executescript(\"\"\"\n ALTER TABLE train\n ADD COLUMN fold INTEGER;\n\"\"\").fetchone()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"if False:\n c.execute(\"\"\"\n UPDATE train\n SET fold = NULL;\n \"\"\").fetchone()","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"%%time\nc.executescript(\"\"\"\n UPDATE train\n SET fold = (\n SELECT folds.fold\n FROM folds\n WHERE\n train.user_id = folds.user_id\n AND train.task_container_id >= folds.task_container_id_min\n );\n\"\"\").fetchone()","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"%%time\nc.execute(\"\"\"\n CREATE INDEX train_fold_index ON train (fold);\n \"\"\").fetchone()\nconn.commit()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Here is a two columned dataframe that can be joined with your data to select folds."},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"%%time\n(pd.read_sql(\"\"\"\n SELECT row_id, fold\n FROM train\n WHERE fold is not NULL\n ORDER BY row_id\n \"\"\", conn)\n .astype({'row_id': 'int64', 'fold': 'int8'})\n .to_pickle('df_folds.pkl'))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Create Dataframe of Selected Folds\n\nThis is how you can query the database to select folds for training and validation."},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"%%time\n\nfolds_valid = [0]\nfolds_train = list(range(1,5))\nfolds = folds_valid + folds_train\n\ndf_train = pd.read_sql(f\"\"\"\n SELECT t.user_id, t.fold, f.seq_start, t.task_container_id,\n t.row_id, t.answered_correctly\n FROM train t\n JOIN folds f\n ON t.user_id = f.user_id\n WHERE t.fold in ({(', ').join([str(f) for f in folds])})\n AND content_type_id = 0\n \"\"\", conn)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The charts above were based on users and task container ids. The charts below are run based on the included records and confirm that they are distrbuted similarly."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"title = 'Record County by Fold'\ndf_train.fold.value_counts().plot(kind='bar', title=title)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Here we can see that the composition of records has shifted towards the earlier groups since by design we are including fewer of the records from the users in the later groups."},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"df_tfg = df_train.groupby(['fold', 'seq_start']).count()['row_id'].unstack('seq_start')\ndf_ts = df_tfg.sum(axis=1)\n(df_tfg.divide(df_ts / 100, axis=0)\n .plot(kind='bar', barmode='stack', title='Composition of Folds by Sequence Start - Records'))","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"(df_train.groupby(['fold','user_id'])[['fold','user_id']].head(1)\n .reset_index().groupby('fold').count().user_id\n .plot(kind='bar', title='Count of Users by Fold'))","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"df_train_g_fold = df_train[['user_id', 'fold', 'answered_correctly']].groupby('fold').agg({'user_id': 'count', 'answered_correctly': 'sum'})\ndf_train_g_fold['answered_correctly_avg'] = (df_train_g_fold.answered_correctly * 100000 / df_train_g_fold.user_id) // 100 / 10\nfig = df_train_g_fold.reset_index().plot(kind='bar', y='answered_correctly_avg', title='Average Percent Answered Correctly by Fold')\nfig.show()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"df_train['valid'] = df_train.fold.isin(folds_valid)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"def show_plot(agg, title, labels):\n g_user_ct = (df_train[['valid', 'row_id', 'seq_start', 'user_id']]\n .groupby(['valid', 'seq_start', 'user_id']).count().unstack('seq_start').fillna(0))\n\n bins = [0,10,20,50,100,250,500,1000,2500,5000,20000]\n g_user_ct['bin'] = pd.cut(g_user_ct.row_id.sum(axis=1), bins=bins, duplicates='drop')\n g_counts = g_user_ct.sort_index().groupby(['valid', 'bin']).agg(agg).reset_index()\n g_counts.columns = ['valid', 'bin', 'beg', 'early', 'late']\n g_counts.bin = g_counts.bin.astype(str)\n\n fig = g_counts.plot(kind='bar', x='bin', y=['beg', 'early', 'late'], facet_col='valid',\n title=title,\n labels=labels)\n \n fig.show()\n\nshow_plot('count',\n title='Count of Users by Records per User by Sequence Group',\n labels={'bin': 'Records per User', 'value': 'Count of Users'}\n )","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"show_plot('sum',\n title='Count of Records by Records per User by Sequence Group',\n labels={'bin': 'Records per User', 'value': 'Count of Records'}\n )","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"That's all folks - if you made it here, you are dedicated - thanks for reading!"}],"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"pygments_lexer":"ipython3","nbconvert_exporter":"python","version":"3.6.4","file_extension":".py","codemirror_mode":{"name":"ipython","version":3},"name":"python","mimetype":"text/x-python"}},"nbformat":4,"nbformat_minor":4}