{"cells":[{"metadata":{},"cell_type":"markdown","source":"## Source Kernel\nThis kernel generates and submits predictions using the model and features developed in the kernel titled [RIIID: BigQuery-XGBoost End-to-End](https://www.kaggle.com/calebeverett/riiid-bigquery-xgboost-end-to-end)."},{"metadata":{"trusted":true},"cell_type":"code","source":"import gc\nimport json\nimport pandas as pd\nfrom pathlib import Path\nimport sqlite3\nimport riiideducation\nimport time\nimport xgboost as xgb","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"env = riiideducation.make_env()\niter_test = env.iter_test()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Load Model"},{"metadata":{"trusted":true},"cell_type":"code","source":"PATH = Path('../input/riiid-submission')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-output":true},"cell_type":"code","source":"model = xgb.Booster(model_file=PATH/'model.xgb')\nprint('model loaded')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Load State"},{"metadata":{"trusted":true},"cell_type":"code","source":"dtypes = {\n    'answered_correctly': 'int8',\n    'answered_correctly_content_id_cumsum': 'int16',\n    'answered_correctly_content_id_cumsum_pct': 'int16',\n    'answered_correctly_cumsum': 'int16',\n    'answered_correctly_cumsum_pct': 'int8',\n    'answered_correctly_cumsum_upto': 'int8',\n    'answered_correctly_rollsum': 'int8',\n    'answered_correctly_rollsum_pct': 'int8',\n    'answered_incorrectly': 'int8',\n    'answered_incorrectly_content_id_cumsum': 'int16',\n    'answered_incorrectly_cumsum': 'int16',\n    'answered_incorrectly_rollsum': 'int8',\n    'bundle_id': 'uint16',\n    'content_id': 'int16',\n    'content_type_id': 'int8',\n    'correct_answer': 'uint8',\n    'lecture_id': 'uint16',\n    'lectures_cumcount': 'int16',\n    'part': 'uint8',\n    'part_correct_pct': 'uint8',\n    'prior_question_elapsed_time': 'float32',\n    'prior_question_elapsed_time_rollavg': 'float32',\n    'prior_question_had_explanation': 'bool',\n    'question_id': 'uint16',\n    'question_id_correct_pct': 'uint8',\n    'row_id': 'int64',\n    'tag': 'uint8',\n    'tag__0': 'uint8',\n    'tag__0_correct_pct': 'uint8',\n    'tags': 'str',\n    'task_container_id': 'int16',\n    'task_container_id_orig': 'int16',\n    'timestamp': 'int64',\n    'type_of': 'str',\n    'user_answer': 'int8',\n    'user_id': 'int32'\n}\n\nbatch_cols_all = [\n    'user_id',\n    'content_id',\n    'row_id',\n    'task_container_id',\n    'timestamp',\n    'prior_question_elapsed_time',\n    'prior_question_had_explanation'\n]\n\nbatch_cols_prior = [\n    'user_id',\n    'content_id',\n    'content_type_id'\n]\n\nwith open(PATH/'columns.json') as cj:\n    test_cols = json.load(cj)\n\nbatch_cols = ['user_id', 'content_id', 'row_id'] + [c for c in batch_cols_all if c in test_cols]\n\nprint('test_cols:')\n_ = list(map(print, test_cols))\n\ndtypes_test = {k: v for k,v in dtypes.items() if k in test_cols}\ndtypes_test = {**dtypes_test, **{'user_id': 'int32', 'content_id': 'int16'}}","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Load Users-Content"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_users_content = pd.read_pickle(PATH/'df_users_content.pkl')\ndf_users_content.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Create Users Dataframe"},{"metadata":{"trusted":true},"cell_type":"code","source":"df_users = df_users_content[['user_id', 'answered_correctly', 'answered_incorrectly']].groupby('user_id').sum().reset_index()\ndf_users = df_users.astype({'user_id': 'int32', 'answered_correctly': 'int16', 'answered_incorrectly': 'int16'})\ndf_users.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Load Questions\nQuestion related features joined with batches received from competition api prior to making predictions."},{"metadata":{"trusted":true},"cell_type":"code","source":"df_questions = pd.read_pickle(PATH/'df_questions.pkl')\ndf_questions.head()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Create Database"},{"metadata":{"trusted":true},"cell_type":"code","source":"conn = sqlite3.connect(':memory:')\ncursor = conn.cursor()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Create Users-Content Table"},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\n\nchunk_size = 20000\ntotal = len(df_users_content)\nn_chunks = (total // chunk_size + 1)\n\ni = 0\nwhile i < n_chunks:\n    df_users_content.iloc[i * chunk_size:(i + 1) * chunk_size].to_sql('users_content', conn, method='multi', if_exists='append', index=False)\n    i += 1\n\nconn.execute('CREATE UNIQUE INDEX users_content_index ON users_content (user_id, content_id)')\ndel df_users_content\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\npd.read_sql('SELECT * from users_content LIMIT 5', conn)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Create Users Table"},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\n\nchunk_size = 20000\ntotal = len(df_users)\nn_chunks = (total // chunk_size + 1)\n\ni = 0\nwhile i < n_chunks:\n    df_users.iloc[i * chunk_size:(i + 1) * chunk_size].to_sql('users', conn, method='multi', if_exists='append', index=False)\n    i += 1\n\n_ = conn.execute('CREATE UNIQUE INDEX users_index ON users (user_id)')\ndel df_users\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\npd.read_sql('SELECT * from users LIMIT 5', conn)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Create Questions Table"},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\n\nq_cols = [\n    'question_id',\n    'part',\n    'tag__0',\n    'part_correct_pct',\n    'tag__0_correct_pct',\n    'question_id_correct_pct'\n]\n\ndf_questions[q_cols].to_sql('questions', conn, method='multi', index=False)\n_ = conn.execute('CREATE UNIQUE INDEX question_id_index ON questions (question_id)')\ndel df_questions\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\npd.read_sql('SELECT * from questions LIMIT 5', conn)","execution_count":null,"outputs":[]},{"metadata":{"trusted":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":{"trusted":true},"cell_type":"code","source":"import sys\nif True:\n    local_vars = list(locals().items())\n    for var, obj in local_vars:\n        size = sys.getsizeof(obj)\n        if size > 1e7:\n            print(f'{var:<18}{size/1e6:>10,.1f} MB')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"## Predict"},{"metadata":{},"cell_type":"markdown","source":"### Get State"},{"metadata":{"trusted":true},"cell_type":"code","source":"def select_state(batch_cols, records):\n    return f\"\"\"\n        WITH b ({(', ').join(batch_cols)}) AS (\n        VALUES {(', ').join(list(map(str, records)))}\n        )\n        SELECT\n            {(', ').join([f'b.{col}' for col in batch_cols])},\n            IFNULL(answered_correctly_cumsum, 0) answered_correctly_cumsum, \n            IFNULL(answered_incorrectly_cumsum, 0) answered_incorrectly_cumsum,\n            IIF(\n                (answered_correctly_cumsum + answered_incorrectly_cumsum) > 0,\n                answered_correctly_cumsum * 100 / (answered_correctly_cumsum + answered_incorrectly_cumsum),\n                0\n            ) answered_correctly_cumsum_pct,\n            IFNULL(answered_correctly_content_id_cumsum, 0) answered_correctly_content_id_cumsum,\n            IFNULL(answered_incorrectly_content_id_cumsum, 0) answered_incorrectly_content_id_cumsum,\n            {(', ').join(q_cols)}\n        FROM b\n        LEFT JOIN (\n            SELECT user_id, answered_correctly answered_correctly_cumsum,\n                answered_incorrectly answered_incorrectly_cumsum\n            FROM users\n            WHERE {(' OR ').join([f'user_id = {r[0]}' for r in records])}\n        ) u ON (u.user_id = b.user_id)\n        LEFT JOIN (\n            SELECT user_id, content_id, answered_correctly answered_correctly_content_id_cumsum, \n            answered_incorrectly answered_incorrectly_content_id_cumsum\n            FROM users_content uc\n            WHERE {(' OR ').join([f'(user_id = {r[0]} AND content_id = {r[1]})' for r in records])}\n        ) uc ON (uc.user_id = b.user_id AND uc.content_id = b.content_id)\n        LEFT JOIN (\n            SELECT {(', ').join(q_cols)}\n            FROM questions\n        ) q ON (q.question_id = b.content_id)\n    \"\"\"","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Update State"},{"metadata":{"trusted":true},"cell_type":"code","source":"def update_state(df):\n    \n    def get_select_params(r):\n        values_uc = f'({r.user_id}, {r.content_id}, {r.answered_correctly}, {1-r.answered_correctly})'\n        values_u = f'({r.user_id}, {r.answered_correctly}, {1-r.answered_correctly})'\n        return values_uc, values_u\n    \n    values = df.apply(get_select_params, axis=1, result_type='expand')\n    \n    return f\"\"\"\n        INSERT INTO users_content(user_id, content_id, answered_correctly, answered_incorrectly)\n        VALUES {(',').join(values[0])}\n        ON CONFLICT(user_id, content_id) DO UPDATE SET\n            answered_correctly = answered_correctly + excluded.answered_correctly,\n            answered_incorrectly = answered_incorrectly + excluded.answered_incorrectly;\n             \n        INSERT INTO users(user_id, answered_correctly, answered_incorrectly)\n        VALUES {(',').join(values[1])}\n        ON CONFLICT(user_id) DO UPDATE SET\n            answered_correctly = answered_correctly + excluded.answered_correctly,\n            answered_incorrectly = answered_incorrectly + excluded.answered_incorrectly;\n    \"\"\"","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\ndf_batch_prior = None\ncounter = 0\n\nfor test_batch in iter_test:\n    counter += 1\n\n    # update state\n    if df_batch_prior is not None:\n        answers = eval(test_batch[0]['prior_group_answers_correct'].iloc[0])\n        df_batch_prior['answered_correctly'] = answers\n        cursor.executescript(update_state(df_batch_prior[df_batch_prior.content_type_id == 0]))\n\n        if not counter % 100:\n            conn.commit()\n\n    # save prior batch for state update\n    df_batch_prior = test_batch[0][batch_cols_prior].astype({k: dtypes[k] for k in batch_cols_prior})\n\n    # get state\n    df_batch = test_batch[0][test_batch[0].content_type_id == 0]\n    records = df_batch[batch_cols].fillna(0).to_records(index=False)\n    df_batch = pd.read_sql(select_state(batch_cols, records), conn)\n\n    # predict\n    predictions = model.predict(xgb.DMatrix(df_batch[test_cols]))\n    df_batch['answered_correctly'] = predictions\n\n    #submit\n    env.predict(df_batch[['row_id', 'answered_correctly']])","execution_count":null,"outputs":[]}],"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}