<jupyter_start><jupyter_text><jupyter_code>def show_version_history():
    from IPython.display import HTML
    style_header = 'mui--align-bottom mui--bg-primary mui--text-light mui--text-center'
    style_cell = 'mui--align-top mui--text-center'

    def make_li(feat):
        return f'<li><code>{feat}</code></li>'
    # print(('\n').join(list(map(make_li, sorted(dtypes.keys())))))

    html_str = f"""
    <link href="//cdn.muicss.com/mui-0.10.3/css/mui.min.css" rel="stylesheet" type="text/css" />
    <div class="mui-container-fluid">
        <h2>Version History</h2>
        <div style="max-width:1016px" class="mui-row">
            <div class="mui-col-8">
                <table class="mui-table mui-table--bordered">
                    <tr>
                        <th width="12%" class="{style_header}">Version</th>
                        <th width="12%" class="{style_header}">Date</th>
                        <th width="12%" class="{style_header}">Local CV</th>
                        <th width="12%" class="{style_header}">Public<br>Leaderboard</th>
                        <th class="{style_header} mui--align-bottom mui--bg-primary
                            mui--text-dark mui--text-left">Notes</th>                    
                    </tr>
                    <tr>
                        <td class="{style_cell}">40</td>
                        <td class="{style_cell}">2020-11-05</td>
                        <td class="{style_cell}">0.761</td>
                        <td class="{style_cell}">--</td>
                        <td>
                            <ul>
                                <li>Features added:
                                    <ul>
                                        <li><code>answered_correctly_content_id_cumsum</code></li>
                                        <li><code>answered_correctly_content_id_cumsum_pct</code></li>
                                        <li><code>answered_correctly_cumsum10</code></li>
                                        <li><code>answered_correctly_cumsum_pct</code></li>
                                        <li><code>answered_correctly_roll10sum_pct</code></li>
                                        <li><code>answered_incorrectly_content_id_cumsum</code></li>
                                        <li><code>lectures_cumcount</code></li>
                                        <li><code>prior_question_elapsed_time_roll10avg</code></li>
                                    </ul>
                                </li>
                                <li>Single model, single fold</li>
                                <li>No public leaderboard - efficient inference in progress</li>
                                <li>Refactored code to move queries and helper functions into
                                    separate modules</li>
                                <li>Completed set up to commit code to Github from Colab and</li>
                                <li>Completed set up to push kernels to Kaggle from Colab</li>
                            </ul>
                        </td>
                    </tr>
                    <tr>
                        <td class="{style_cell}">37</td>
                        <td class="{style_cell}">2020-11-04</td>
                        <td class="{style_cell}">0.751</td>
                        <td class="{style_cell}">0.748</td>
                        <td>
                            <ul>
                                <li>Features added:
                                    <ul>
                                        <li><code>answered_correctly_cumsum</code></li>
                                        <li><code>answered_correctly_roll10sum</code></li>
                                        <li><code>answered_incorrectly_cumsum</code></li>
                                        <li><code>answered_incorrectly_roll10sum</code></li>
                                        <li><code>part</code></li>
                                        <li><code>part_correct_pct</code></li>
                                        <li><code>question_id_correct_pct</code></li>
                                        <li><code>tag__0</code></li>
                                        <li><code>tag__0_correct_pct</code></li>
                                    </ul>
                                </li>
                                <li>Single model, single fold</li>
                                <li>Model for public leaderboard didn't include
                                    rolling features - still working out how to
                                    efficiently calculate for inference</li>
                            </ul>
                        </td>
                    </tr>
                </table>
            </div>
        </div>
        <p>
            <a href="https://colab.research.google.com/github/CalebEverett/riiid-2020/blob/master/riiid-2020.ipynb" target="_blank" rel="nofollow">
            <img src="https://colab.research.google.com/assets/colab-badge.svg" alt="Open In Colab"></a>
        </p>
    </div>
    """

    html = HTML(html_str)
    display(html)
show_version_history()
# <hide-input><jupyter_output><empty_output><jupyter_text>Intro This kernel is an end to end pipeline that uses BigQuery to store data and perform feature engineering, and trains a model using XGBoost. I was resorting to breaking up tables and still waiting a long time to see the results of my analysis and to process my engineered features, so I decided to learn about BigQuery. This kernel is the current state of my setup, which is working very well. It is much faster than my previous local setup, even with having to download files. It also is making it easier to keep the structure of the data and and code clean, which in turn makes it easier to stay focused on thinking about and executing ideas without getting bogged down waiting for things to finish or wading through extraneous processing code.I've attempted to put  this book together in such a way that somebody else can fork it, update a few environment variables, run it and then be in the game engineering features and improving the model. The only requirements are a GCP project and storage bucket. Other than that, it is turn key, starting with creating a BigQuery dataset and ending with a saved model and two feature tables that get uploaded to a Kaggle dataset where they are used in a separate kernel to make predictions and submit to the competition api.A couple of cool features:* Uses the gcs version of the competition datset to create a dataset and upload to BigQuery in around a minute* Transformations get run on the entire train table at once and run in under 10 minutes* Feature engineering gets done on a sample of the train table, taking advantage of BigQuery' graphical query editing interface that includes tab completion, syntax checking and the ability to run queries and inspect results* Stores queries as methods on a dedicated class, where they can be easily reused* Dtypes for local dataframes, schema for BigQuery tables and all tranformations are maintained locally so that the transformed tables can be recreated from the original competition dataset files automatically at any time (see description of workflow below to continue with this practice)* Exports to gcs using temporary tables created by BigQuery avoiding unnecessary storage and wasted time rerunning and exporting duplicate queriesI've engineered a few features as a starting point to demonstrate how additional features can be efficiently developed and processed, including:* Cumulative and rolling sums of questions answered correctly and incorrectly by user    * I'm still working on efficiently updating the rolling sums during prediction, so they aren't included for training purposes* Percent of questions answered correctly by question id, part and the first question tagThe model is also just a starting point, with a first pass at a train/validation split and no hyperparameter tuning. I have included some basic diagnostics on both the train/validtion split and model performance as a starting place for further development. Trained on a small subset of the overall training data with a small number of engineered features, it is producing a local validation AUC score of around 0.75 and slighly less than that on the public leaderboard.  Resources* [BigQuery Console](https://console.cloud.google.com/bigquery?project=riiid-caleb) (Update project query string for your project.)* [Python Client for Google BigQuery](https://googleapis.dev/python/bigquery/latest/index.html)* [BigQuery Query syntax in Standard SQL](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax)* [XGBoost Documentation](https://xgboost.readthedocs.io/en/latest/index.html)* [Storge Client](https://googleapis.dev/python/storage/latest/client.html)* [pandas documentation](https://pandas.pydata.org/docs/)* [Plotly Python Open Source Graphing Library](https://plotly.com/python/)* [PEP 8 -- Style Guide for Python Code](https://www.python.org/dev/peps/pep-0008/)  Imports<jupyter_code>%load_ext autoreload
%autoreload 2
# <hide-input><hide-input>
from datetime import datetime
import gc
import json
import os
from pathlib import Path
import subprocess
import sys
import time

from google.cloud import storage, bigquery
from google.cloud.bigquery import SchemaField
import numpy as np
import pandas as pd
from tqdm.notebook import tqdm

NOT_KAGGLE = not os.getenv('KAGGLE_URL_BASE')
if NOT_KAGGLE:
    from google.colab import auth
    auth.authenticate_user()
    print('Authenticated with the Googs!')
BUCKET = 'riiid-caleb'
DATASET = 'data'
LOCATION = 'us-west4'
KAGGLE_SUBMIT_DATASET = 'riiid-submission'
PROJECT = 'riiid-caleb'

bucket = storage.Client(project=PROJECT).get_bucket(BUCKET)
dataset = bigquery.Dataset(f'{PROJECT}.{DATASET}')
bq_client = bigquery.Client(project=PROJECT, location=LOCATION)

CONFIG = json.loads(bucket.get_blob('config.json').download_as_string())
os.environ = {**os.environ, **CONFIG}

# this is a little jank loading utilities.py from storage instead of
# the repo, but I need them to be able to clone the repo
bucket.blob('utilities.py').download_to_filename('utilities.py')
from utilities import check_packages, Git

packages = {
    'comet-ml': '3.2.5',
    'gcsfs': '0.7.1',
    'kaggle': '1.5.9',
    'plotly': '4.12.0'
}

check_packages(packages)

from comet_ml import Experiment
from kaggle.api.kaggle_api_extended import KaggleApi
import plotly
import plotly.express as px
from sklearn.metrics import roc_auc_score
from sklearn.preprocessing import MultiLabelBinarizer
import xgboost as xgb

kaggle_api = KaggleApi()
kaggle_api.authenticate()
pd.options.plotting.backend = 'plotly'
# <hide-output>
git = Git('riiid_modules', CONFIG.get('GIT_USERNAME'),
          CONFIG.get('GIT_PASSWORD'), CONFIG.get('EMAIL'))
latest = os.getenv('KAGGLE_URL_BASE') is not None
git.clone(latest=latest)
from riiid_modules.queries import Queries
from riiid_modules.bqhelpers import BQHelper

Q = Queries(DATASET)
bqh = BQHelper(bucket, DATASET, bq_client)<jupyter_output><empty_output><jupyter_text>Create BigQuery Dataset<jupyter_code>if False:
    delete_contents=False
    bq_client.delete_dataset(DATASET, delete_contents=delete_contents)
    print(f'Dataset {dataset.dataset_id} deleted from project {dataset.project}.')
try:
    dataset = bq_client.get_dataset(dataset.dataset_id)
    print(f'Dataset {dataset.dataset_id} already exists '
          f'in location {dataset.location} in project {dataset.project}.')
except:
    dataset = bq_client.create_dataset(dataset)
    print(f'Dataset {dataset.dataset_id} created '
          f'in location {dataset.location} in project {dataset.project}.')<jupyter_output><empty_output><jupyter_text>Load Tables  Dataframe dtypes<jupyter_code>dtypes_orig = {
    'lectures': {
        'lecture_id': 'uint16',
        'tag': 'uint8',
        'part': 'uint8',
        'type_of': 'str',
    },
    'questions': {
        'question_id': 'uint16',
        'bundle_id': 'uint16',
        'correct_answer': 'uint8',
        'part': 'uint8',
        'tags': 'str',
        
    },
    'train': {
        'row_id': 'int64',
        'timestamp': 'int64',
        'user_id': 'int32',
        'content_id': 'int16',
        'content_type_id': 'int8',
        'task_container_id': 'int16',
        'user_answer': 'int8',
        'answered_correctly': 'int8',
        'prior_question_elapsed_time': 'float32', 
        'prior_question_had_explanation': 'bool'
    }
    
}

dtypes_new = {
    'lectures': {},
    'questions': {
        'tag__0': 'uint8',
        'part_correct_pct': 'uint8',
        'tag__0_correct_pct': 'uint8',
        'question_id_correct_pct': 'uint8'
    },
    'train': {
        'task_container_id_orig': 'int16',
        'answered_correctly_cumsum': 'int16',
        'answered_correctly_roll10sum': 'int8',
        'answered_incorrectly': 'int8',
        'answered_incorrectly_cumsum': 'int16',
        'answered_incorrectly_roll10sum': 'int8',
        'answered_correctly_cumsum_pct': 'int8',
        'answered_correctly_roll10sum_pct': 'int8',
        'answered_correctly_content_id_cumsum': 'int16',
        'answered_incorrectly_content_id_cumsum': 'int16',
        'answered_correctly_content_id_cumsum_pct': 'int16',
        'answered_correctly_cumsum10': 'int8',
        'prior_question_elapsed_time_roll10avg': 'float32',
        'lectures_cumcount': 'int16',
    }
}

one_hot_tags = False
if one_hot_tags:
    for tag in range(189):
        for table_id in ['questions', 'train']:
            dtypes_new[table_id][f'tag_{tag:03d}'] = 'uint8'

dtypes = {}
for table_id in dtypes_orig:
    dtypes[table_id] = {**dtypes_orig[table_id], **dtypes_new[table_id]}

dtypes = {
    **dtypes['lectures'],
    **dtypes['questions'],
    **dtypes['train']
}<jupyter_output><empty_output><jupyter_text>BigQuery Table Schemas<jupyter_code>type_map = {
    'int64': 'INTEGER',
    'int32': 'INTEGER',
    'int16': 'INTEGER',
    'int8': 'INTEGER',
    'uint8': 'INTEGER',
    'uint16': 'INTEGER',
    'str': 'STRING',
    'bool': 'BOOL',
    'float32': 'FLOAT'
}

schemas_orig = {table: [SchemaField(f, type_map[t]) for f, t in
                   fields.items()] for table, fields in dtypes_orig.items()}
schemas_orig['questions'][-1] = SchemaField('tags', 'INTEGER', 'REPEATED')

schemas = {}
for table_id, fields in dtypes_new.items():
    new_fields = [SchemaField(f, type_map[t]) for
                  f, t in fields.items()]
    schemas[table_id] = schemas_orig[table_id] + new_fields<jupyter_output><empty_output><jupyter_text>Load Tables<jupyter_code># Load questions from local json file - can't load tags as arrays
# from csv.

if False:
    bqh.del_table('questions')
    
    df_questions = pd.read_csv(f'gs://{BUCKET}/questions.csv')
    df_questions.tags = df_questions.tags.fillna('188')
    df_questions.tags = df_questions.tags.str.split()
    
    if one_hot_tags:
        mlb = MultiLabelBinarizer()
        one_hots = (mlb.fit_transform(df_questions.tags
                    .apply(lambda l: [f'tag_{int(t):03d}' for t in l])))
        df_one_hots = pd.DataFrame(one_hots, columns = mlb.classes_)
        df_questions = pd.concat([df_questions, df_one_hots], axis=1)
    
    df_questions.to_json('questions.json', orient="records", lines=True)
    lj = bqh.load_json_file('questions', schemas_orig).result()
if False:
    for table_id in ['lectures', 'train']:
        bqh.del_table(table_id)
        lj = bqh.load_csv_uri(table_id, schemas_orig).result()
bqh.get_df_jobs?
df_jobs = bqh.get_df_jobs(max_results=1000)
df_jobs

# if df_jobs.exception.isna().any():
#     _ = list(map(print, df_jobs[~df_jobs.exception.isna()].exception))
sum(df_jobs.bytes > 0)
job = bq_client.get_job(df_jobs.job_id[0])
job.estimated_bytes_processed
df_tables = bqh.get_df_tables()
df_tables<jupyter_output><empty_output><jupyter_text>Update Table Schemas<jupyter_code>if False:
    for table_id, schema in schemas.items():
        table = bqh.get_table(table_id)
        table.schema = schema
        table = bq_client.update_table(table, ['schema'])<jupyter_output><empty_output><jupyter_text>Engineer Features A good workflow here is:* Create a sample of the train table.* Use the BigQuery query editor user interface to get the SQL for a new feature worked out as a selection from the `train_sample` table. The user interface there has tab completion, syntax checking and displays results, which makes creating and debugging queries a snap.    * [BigQuery Console](https://console.cloud.google.com/bigquery?project=riiid-caleb) (Update project query string for your project.)    * [BigQuery Query syntax in Standard SQL](https://cloud.google.com/bigquery/docs/reference/standard-sql/query-syntax) is your friend.* Optional: create a local dataframe, using the export functions below, to confirm that it is working the right way.* Add a column to the appropriate table by adding a value to `dtypes_new`* Update the schema for the table in BigQuery by running the Update Table Schemas cell above* Recreate the `train_sample` table by running the cell below.* Use the BigQuery query editor user interface add the logic to update the new column.* Optional: create a local dataframe, using the export functions below, to confirm that the update is working the right way.* Copy the SQL to a new method in the `Queries` class above* Add the query to the appropriate `run_transformations` function above* Run transformations on `train_sample` table* Inspect `train_sample` table in BigQuery to confirm everything is working correctly* Optional: load load local dataframe using `get_df_query` function for further inspection* Run transformations on `train` table* Inspect `train` table in BigQuery to confirm everything is working correctly* Optional: load local dataframe using `get_df_query` function for further inspection  Perform Transformations * Add question columns    * Adding question part and the first associated tag. (There wasn't any official information regarding the order of the tags as recorded for each question, but they did not appear to be sorted so it seems possible the order in which they are recorded is significant.)* Update task_container_id to increase monotonically with timestamp    * There were some `task_conatiner_id`s that were out of order with respect to timestamp. They needed to be be ordered correctly so that cumulative and rolling sums partitioned by `task_container_id` would be include only interactions with earlier `timestamps`. Even though all interactions with the same `task_container_id` have the same `timestamp`, partioning by `timestamp` is much slower (because the range of values is so much wider?).* Calc answered_incorrectly    * `answered_correctly` for lectures was recorded as -1 and needed to be set to 0 to calculate cumulative and rolling sums correctly including lectures. As a consequence, `answered_incorrectly` could be calculated as the inverse of `answered_correctly`.* Calc cumsum and roll for answered_correctly and answered_incorrectly    * This is done so that the totals are as of the preceding `task_container_id`.<jupyter_code>window_specs = [
    dict(update_column_id='answered_correctly_cumsum'),
    
    dict(update_column_id='answered_correctly_roll10sum', window=10),
    
    dict(source_column_id='answered_incorrectly',
         update_column_id='answered_incorrectly_cumsum'),
    
    dict(source_column_id='answered_incorrectly',
         update_column_id='answered_incorrectly_roll10sum', window=10),

    dict(source_column_id='answered_correctly',
         update_column_id='answered_correctly_content_id_cumsum', window=10,
         part_content=True),

    dict(source_column_id='answered_incorrectly',
         update_column_id='answered_incorrectly_content_id_cumsum', window=10,
         part_content=True),

    dict(source_column_id='prior_question_elapsed_time',
         update_column_id='prior_question_elapsed_time_roll10avg', window=10,
         agg='AVG'),
    
    dict(source_column_id='content_type_id',
         update_column_id='lectures_cumcount'),
]

def run_update_window(spec):
    query, job_id_prefix = Q.update_train_window(**spec)
    job_id_prefix = f'{job_id_prefix}{spec["update_column_id"]}_'
    bqh.run_query(query=query, job_id_prefix=job_id_prefix, wait=True)
cumsum_pct_specs = [
    dict(column_id_correct='answered_correctly_cumsum',
         column_id_incorrect='answered_incorrectly_cumsum',
         update_column_id='answered_correctly_cumsum_pct'),
    
    dict(column_id_correct='answered_correctly_roll10sum',
         column_id_incorrect='answered_incorrectly_roll10sum',
         update_column_id='answered_correctly_roll10sum_pct'),
    
    dict(column_id_correct='answered_correctly_content_id_cumsum',
         column_id_incorrect='answered_incorrectly_content_id_cumsum',
         update_column_id='answered_correctly_content_id_cumsum_pct'),                   
]

def run_update_correct_cumsum_pct(spec):
    query, job_id_prefix = Q.update_correct_cumsum_pct(**spec)
    job_id_prefix = f'{job_id_prefix}{spec["update_column_id"]}_'
    bqh.run_query(query=query, job_id_prefix=job_id_prefix, wait=True)
def run_train_transforms(table_id=None):
    # Run serially to avoid update conflicts
    
    train_queries = [
        Q.update_task_container_id(table_id=table_id),
        Q.update_answered_incorrectly(table_id=table_id),
        Q.update_missing_values(table_id=table_id,
                                column_id='prior_question_had_explanation',
                                value='false'),
        Q.update_missing_values(table_id=table_id,
                                column_id='prior_question_elapsed_time',
                                value='0'),
    ]
    
    _ = [bqh.run_query(*q, wait=True) for q in train_queries]

    _ = [spec.update(table_id=table_id) for spec in window_specs]
    _ = list(map(run_update_window, window_specs))

    _ = bqh.run_query(*Q.update_train_window_upto(
            table_id=table_id, source_column_id='answered_correctly_cumsum',
            update_column_id='answered_correctly_cumsum10'), wait=True)

    _ = [spec.update(table_id=table_id) for spec in cumsum_pct_specs]
    _ = list(map(run_update_correct_cumsum_pct, cumsum_pct_specs))
def run_questions_transforms():
    """These have to be run after the transforms are run on the full
    train table.
    """
    
    questions_queries = [Q.update_questions_tag__0()]
    for column_id in ['question_id', 'part', 'tag__0']:
        questions_queries.append(Q.update_question_correct_pct(column_id))
    
    _ = [bqh.run_query(*q, wait=True).result() for q in questions_queries]
%%time
if False:
    run_train_transforms('train')
    run_questions_transforms()<jupyter_output><empty_output><jupyter_text>Check Output<jupyter_code>df_query = bqh.get_df_query(Q.select_train(excl_lectures=True), dtypes=None)
cols = [
        'row_id',
        'task_container_id_orig',
        'timestamp',
        'content_type_id',
        'user_id',
        'task_container_id',
        'part',
        'tag__0',
        'answered_correctly',
        'answered_incorrectly',
        'answered_correctly_cumsum',
        'answered_incorrectly_cumsum',
        'answered_correctly_content_id_cumsum',
        'answered_correctly_roll10sum',
        'answered_incorrectly_roll10sum',
        'answered_incorrectly_content_id_cumsum',
        'part_correct_pct',
        'tag__0_correct_pct',
        'question_id_correct_pct',
        'prior_question_elapsed_time',
        'prior_question_elapsed_time_roll10avg',
        'prior_question_had_explanation',
        'lectures_cumcount'
]

df_user = df_query[cols].copy()
df_user.timestamp = df_user.timestamp / (1000*60*60)

df_user.loc[df_user.user_id == 44331].head(20)
# <hide-input><jupyter_output><empty_output><jupyter_text>Visually Inspect Features The charts below can also be used to visually inspect whether the transformations have been performed correctly.<jupyter_code>groups = {
    'cum': {
        'columns': {
            'task_container_id': 0,
            'answered_correctly_cumsum': 2,
            'answered_incorrectly_cumsum': 1
        },
        'xaxis': 'elapsed_hours'
    },
    'roll': {
        'columns': {
            'answered_correctly_roll10sum': 2,
            'answered_correctly': 7,
            'answered_incorrectly_roll10sum': 1,
            'answered_incorrectly': 8,
            'part': 9
        },
        'xaxis': 'row_id'
    },  
    'correct_pct': {
        'columns': {
            'question_id_correct_pct': 0,
            'part_correct_pct': 5,
            'tag__0_correct_pct': 6
        },
        'xaxis': 'row_id'
    },  
    'prior_question_elapsed_time': {
        'columns': {
            'prior_question_elapsed_time': 0,
        },
        'xaxis': 'row_id'
    },  
    'prior_question_had_explanation': {
        'columns': {
            'prior_question_had_explanation': 0,
        },
        'xaxis': 'row_id'
    }
}

def plot_user_learning(user_id=None, group=None, suffix=None):
    theme = px.colors.qualitative.Plotly
    columns = list(group['columns'].keys())
    colors = [theme[c] for c in group['columns'].values()]

    df_query['elapsed_hours'] = df_query.timestamp / (1000*60*60)

    df = df_query.loc[(df_user.user_id == user_id) & (df_user.content_type_id == 0)]


    # labels = {'value': 'answer count'}

    fig = df.plot(x=group['xaxis'], y=columns, color_discrete_sequence=colors,
                  title=f'Learning Progress - user_id = {user_id} - {suffix}')
    fig.data

    return fig

use_random = False
user_id = np.random.choice(df_query.user_id.unique(), (1,))[0] if use_random else 5382

for k, v in groups.items():
    fig = plot_user_learning(user_id, v, k)
    fig.show()
# <hide-input><jupyter_output><empty_output><jupyter_text>Create Sample of Train Table for R&D<jupyter_code>%%time

if False:
    bqh.run_query(*Q.create_train_sample(), wait=True)
    q = Q.select_train(excl_lectures=True, table_id='train_sample')
    df_sample = bqh.get_df_query(q)<jupyter_output>CPU times: user 2 µs, sys: 1 µs, total: 3 µs
Wall time: 5.72 µs<jupyter_text>Create Local Training Dataframe With feature engineering being performed in BigQuery, data has to be exported to train models locally. The [Python Client for Google BigQuery](https://googleapis.dev/python/bigquery/latest/index.html) [to_dataframe()](https://googleapis.dev/python/bigquery/latest/generated/google.cloud.bigquery.job.QueryJob.html?highlight=to_dataframegoogle.cloud.bigquery.job.QueryJob.to_dataframe) makes it possible to create dataframes directly, but is prohibitively slow for large datasets. While it is not possible to export table directly to the local file system, it is possible to export to cloud storage and then download locally from there. This is reasonably efficient, taking a couple of minutes to run a query, export to cloud storage, download to the local file system and then read the files into a dataframe. The is another api, the [BigQuery Storage API](https://cloud.google.com/bigquery/docs/reference/storage), that a client can be created with that is really fast and works with the `to_dataframe` method, but unforunatley it isn't working with the current Kaggle kernel environment.The functions below take advantage of the fact BigQuery stores queries in temporary tables so that preveiously requested queries can be retrieved without having to run them again. Similarly, the functions below name the exported files with the reference to the BigQuery temporary table, so that if a function is run to create a dataframe from a query for which the files already exist in cloud storage or locally, they won't be exported or downloaded again.   Create DataFrame<jupyter_code>features = {
    'answered_correctly': True,
    'answered_correctly_content_id_cumsum': True,
    'answered_correctly_content_id_cumsum_pct': True,
    'answered_correctly_cumsum': True,
    'answered_correctly_cumsum10': True,
    'answered_correctly_cumsum_pct': True,
    'answered_correctly_roll10sum': True,
    'answered_correctly_roll10sum_pct': True,
    'answered_incorrectly': True,
    'answered_incorrectly_content_id_cumsum': True,
    'answered_incorrectly_cumsum': True,
    'answered_incorrectly_roll10sum': True,
    'bundle_id': False,
    'content_id': True,
    'content_type_id': True,
    'correct_answer': False,
    'lecture_id': False,
    'lectures_cumcnt': False,
    'lectures_cumcount': True,
    'part': True,
    'part_correct_pct': True,
    'prior_question_elapsed_time': True,
    'prior_question_elapsed_time_roll10avg': True,
    'prior_question_had_explanation': True,
    'question_id': False,
    'question_id_correct_pct': True,
    'row_id': True,
    'tag': False,
    'tag__0': True,
    'tag__0_correct_pct': True,
    'tags': False,
    'task_container_id': True,
    'task_container_id_orig': False,
    'timestamp': True,
    'type_of': False,
    'user_answer': False,
    'user_id': True
    }

columns_export = [f for f, v in features.items() if v]
# get user_ids for rows in thousands. will be approximate, excludes lectures
# and selects all records for user_ids less than specified.

if False:    
    r = Q.run_query(*Q.select_user_id_rows(rows=int(2e6))).result()
    user_id_max = list(r)[0].user_id
    print(user_id_max)
    
user_ids = {
    10: 91216,
    100: 2078569,
    1000: 20949024,
    2000: 42207371,
    10000: 216747867,
    30000: 643006676
}
%%time

if True:
    query = Q.select_train(columns=columns_export, user_id_max=user_ids[30000],
                           excl_lectures=True)
    df_train = bqh.get_df_query_gcs(query, dtypes=dtypes)
# <hide-output><jupyter_output><empty_output><jupyter_text>Train Model  Create Train and Validation Splits This is a first pass at a validation split to be able to have something to get the mechanics of evaluating the model up and running. It simply takes the last 20 `task_container_id`s for each user. The result is that all of the records in the validation set have `task_container_ids` greater than those in the training set for each user. There are also users in the validation set that are not present in the training set. However, a significant problem with this methodology is that the number of records per user in the validation set is much lower than it is in the training set.<jupyter_code># get unique user_id-task_container_id combinations
df_user_task = df_train.groupby(['user_id', 'task_container_id'])[['user_id', 'task_container_id', 'row_id']].head(1)

# get index of trailing number of unique user_id-task_container_id combinations
index_valid = df_user_task.groupby('user_id').tail(20).set_index(['user_id', 'task_container_id']).index

# use index to get ids of all rows in the chosen set of user_id-task_container combinations
row_valid = df_train.set_index(['user_id', 'task_container_id'])['row_id'].loc[index_valid].values

df_train['valid'] = df_train.row_id.isin(row_valid)
# <hide-input>
df_train.valid.value_counts().plot(kind='bar', title='Train and Validation Splits - Record Counts')
# <hide-input>
(df_train.groupby(['valid','user_id'])[['valid','user_id']].head(1)
 .reset_index().groupby('valid').count().user_id
 .plot(kind='bar', title='Count of Users by Split'))
# <hide-input>
g_user_ct = df_train[['valid', 'row_id', 'user_id']].groupby(['valid', 'user_id']).count()
g_user_ct['bin'] = pd.cut(g_user_ct.row_id, bins=[0,10,20,50,100,250,500,1000,2500,5000,20000], duplicates='drop')
g_counts = g_user_ct.reset_index().groupby(['valid', 'bin'])['row_id'].count().reset_index()

px.bar(x=g_counts.bin.apply(str), y=g_counts.row_id,
       facet_col=g_counts.valid.map({True: 'Validation', False: 'Train'}),
       title='Count of Users by Count of Interactions by Split',
       labels={'x': 'Count of Interactions', 'y': 'Count of Users', 'facet_col': 'Validation Split'})
# <hide-input><jupyter_output><empty_output><jupyter_text>Select Columns for Training<jupyter_code>y_train_col = ['answered_correctly']

columns_export_excl = [
                       'answered_incorrectly',
                       'content_type_id',
                       'row_id',
                       'user_id'
                       ]

x_train_cols = ([c for c in columns_export if c not in columns_export_excl
                 + y_train_col])

train_matrix = xgb.DMatrix(data=df_train.loc[~df_train.valid][x_train_cols],
                           label=df_train.loc[~df_train.valid][y_train_col])

valid_matrix = xgb.DMatrix(data=df_train.loc[df_train.valid][x_train_cols],
                           label=df_train.loc[df_train.valid][y_train_col])<jupyter_output><empty_output><jupyter_text>Train Model<jupyter_code>params = {
    'eta': 0.25,
    'max_depth': 10,
    'max_bin': 512,
    'tree_method': 'gpu_hist',
    'grow_policy': 'lossguide',
    'sampling_method': 'gradient_based',
    'objective': 'binary:logistic',
    'eval_metric': ['error', 'logloss', 'auc'],
    'lambda': 1
}

experiment = Experiment()
evals_result = {}
model = xgb.train(params=params, dtrain=train_matrix, num_boost_round=200,
                  evals=[(train_matrix, 'train'), (valid_matrix, 'valid')],
                  evals_result=evals_result, early_stopping_rounds=10)
experiment.end()
# <hide-output><jupyter_output><empty_output><jupyter_text>Evaluate Model<jupyter_code>def get_evals_df(evals_result):
    evals_list = []
    for k,v in evals_result.items():
        for j,u in v.items():
            evals_list.extend([{'epoch': i, 'split': k, 'metric': j, 'result': r} for i,r in enumerate(u)])
    
    df_evals = pd.DataFrame(evals_list).set_index(['split', 'metric', 'epoch']).unstack('metric')
    df_evals.columns = df_evals.columns.get_level_values(1)
    df_evals.columns.name = None
    
    return df_evals.reset_index()

df_evals = get_evals_df(evals_result)
# <hide-input>
df_evals.plot(x='epoch', y=['auc', 'logloss'], facet_col='split', title='Learning Curves')
# <hide-input>
imps = model.get_score(importance_type='gain').items()
df_imp = pd.DataFrame(imps, columns=['feature', 'importance'])
df_imp = df_imp.set_index('feature').sort_values('importance', ascending=False)
df_imp.plot(kind='bar', y='importance', title='Feature Importances - Gain')
# <hide-input><jupyter_output><empty_output><jupyter_text>Download Final Users State<jupyter_code>%%time

if False:
    query = Q.select_user_final_state() 
    prefix = export_query_gcs(query, wait=True)
    file_paths = get_table_gcs(prefix)
    df_users = get_df_files(file_paths).set_index('user_id').sort_index()
    df_users.head()
# <hide-input><hide-output><jupyter_output><empty_output><jupyter_text>Download Questions TableWork in progress: to get the right table and update logic for the rolling sums and by user_id, by content_id working correctly.<jupyter_code>def get_df_table(table_id, max_results=10000, dtypes=None):
    table = get_table(table_id)
    df_table = (bq_client.list_rows(table, max_results=max_results)
                .to_dataframe(dtypes=dtypes,
                              progress_bar_type='tqdm_notebook')
               )
    return df_table
# <hide-input>
%%time

if False:
    # only 13k rows, so it downloaded directly from BigQuery
    df_questions = get_df_table('questions', max_results=None, dtypes=dtypes)
    df_questions = df_questions.set_index('question_id').sort_index()
    df_questions.head()
# <hide-input><hide-output><jupyter_output><empty_output><jupyter_text>Update Submission Dataset<jupyter_code>%%time

if False:
    Path(KAGGLE_SUBMIT_DATASET).mkdir(exist_ok=True)

    model.save_model(f'{KAGGLE_SUBMIT_DATASET}/model.xgb')
    
    df_files = {
        'df_users.pkl': df_users,
        'df_questions.pkl': df_questions,
    }

    for file_path, variable in df_files.items():
        variable.to_pickle(f'{KAGGLE_SUBMIT_DATASET}/{file_path}')
            
    kaggle_id = f"{os.getenv('KAGGLE_USERNAME')}/{KAGGLE_SUBMIT_DATASET}"
    
    metadata = {
        "licenses": [{"name": "CC0-1.0"}],
        "id": kaggle_id,
        "title": KAGGLE_SUBMIT_DATASET
           }

    with open(f'{KAGGLE_SUBMIT_DATASET}/dataset-metadata.json', 'w') as f:
        json.dump(metadata, f)
            
    if kaggle_api.dataset_status(kaggle_id):
        kaggle_api.dataset_create_version(KAGGLE_SUBMIT_DATASET,
                                          version_notes='update dataset',
                                          delete_old_versions=True,
                                          dir_mode='tar',
                                          quiet=True
                                         )
    else:
        kaggle_api.dataset_create_new(KAGGLE_SUBMIT_DATASET,
                                      dir_mode='tar', quiet=True)
# <hide-input><jupyter_output><empty_output><jupyter_text>Submit From Kernel * Go to [RIIID Submit](https://www.kaggle.com/calebeverett/riiid-submit), fork and update to reference your dataset.  Push Kernel to Kaggle<jupyter_code>if NOT_KAGGLE:
    if True:
        from requests import get
        
        notebook_url = 'https://raw.githubusercontent.com/CalebEverett/riiid-2020/master/riiid-2020.ipynb'
        code_file = notebook_url.split('/')[-1]
        notebook_json = get(notebook_url, headers={'Cache-Control': 'no-cache'}).json()

        # update show/hide code cells for Kaggle
        for i, cell in enumerate(notebook_json['cells']):
            if cell['cell_type'] == 'code':
                for h in ['input', 'output']:
                    if cell['source'][-1].find(f'hide-{h}') > 1:
                        notebook_json['cells'][i]['metadata'].update({f'_kg_hide-{h}': True})
                    else:
                        notebook_json['cells'][i]['metadata'].pop(f'_kg_hide-{h}', None)

        if Path(code_file).exists():
            Path(code_file).unlink()
        
        with open(f'{code_file}', 'w') as f:
            json.dump(notebook_json, f)

        data = {'id': 'calebeverett/riiid-bigquery-xgboost-end-to-end',
                        'title': 'RIIID: BigQuery-XGBoost End-to-End',
                        'code_file': code_file,
                        'language': 'python',
                        'kernel_type': 'notebook',
                        'is_private': 'false',
                        'enable_gpu': 'true',
                        'enable_internet': 'true',
                        'dataset_sources': [],
                        'competition_sources': ['riiid-test-answer-prediction'],
                        'kernel_sources': []}
        
        with open('kernel-metadata.json', 'w') as f:
            json.dump(data, f)

        kaggle_api.kernels_push('.')
# <hide-input><jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10832861481443352, 'fraction_numerical': 0.011453132238636948, 'mean_word_length': 3.0981963927855714, 'pattern_counts': {'":': 4, '<': 253, '<?xml version=': 0, '>': 255, 'https://': 17, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 43, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '20', 'dir': 'jupyter-structured-clean-dedup', 'id': '355815', 'license': 'MIT', 'n_tokens_mistral': 12550, 'n_tokens_neox': 11806, 'n_words': 2863, 'path': 'riiid_2020.ipynb', 'repo_name': 'CalebEverett/AIND-VUI-Capstone', 'size': '38854'}