{"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\nimport numpy as np","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 = 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":"def data_transform(df,FEATURE_COLS, is_training = True, is_validation = True): \n    #Casting types to reduce memory usage\n#     df = reduce_memory_usage(df)\n    \n    #Dropping columns from the beginning to accelerate further computations\n#     df.drop(['task_container_id'],axis=1,inplace=True)\n\n    #Joining average marks for questions with the main dataframe\n    df = df.join(Question_df['question_average'],on=['content_id'],rsuffix='_question_average')\n    \n    df = df.join(user_df[['performance','user_average', 'user_count']],on=['user_id'],rsuffix='_right')\n    \n    df['is_beginning'] = df['user_count'] < 20\n    df['attemp'] = df.groupby(['user_id','content_id']).content_id.transform('cumcount').astype(np.uint8)\n    #Recasting after join\n    #df['prior_question_had_explanation'] = df['prior_question_had_explanation'].astype('bool')\n    \n    df = df.loc[df['content_type_id'] == False]\n    \n    if is_training or is_validation:\n        df = df[FEATURE_COLS1 + TARGET_COL]\n    else:\n        df = df[FEATURE_COLS1]\n    return df\ndef build_user_df(prior_df,user_df,question_df):\n    \n    if (prior_df.shape[0] == 0):\n        return user_df\n    \n    #Dictionnary for user average\n    user_sum_prior = pd.DataFrame(prior_df.loc[prior_df['content_type_id'] == 0]\\\n                       .groupby(['user_id'])['answered_correctly_response'].sum())\\\n                       .rename(columns={'answered_correctly_response':'user_sum'})\n    \n    #Dictionnary for user count\n    user_count_prior = pd.DataFrame(prior_df.loc[prior_df['content_type_id'] == 0]\\\n                         .groupby(['user_id']).size(),columns=['user_count'])\n\n    #Joining the df with preexisting one\n    user_df = user_df.join(user_sum_prior,how='outer',rsuffix='_previous').join(user_count_prior,rsuffix='_previous')\n    \n    #Filling null values\n    user_df['performance'].fillna(0,inplace=True)\n    user_df['user_average'].fillna(0,inplace=True)\n    user_df['user_count'].fillna(0,inplace=True)\n    user_df['user_sum'].fillna(0,inplace=True)\n    user_df['user_count_previous'].fillna(0,inplace=True)\n    user_df['user_sum_previous'].fillna(0,inplace=True)\n    \n    #Computing the average of correct answers for the list of questions each user head in prior\n    user_df = user_df.join(question_average_sum_by_user(prior_df,question_df))\n    user_df['question_average_sum'].fillna(0,inplace=True)\n    \n    #Updating values\n    user_df['user_mean_performance'] = (user_df['user_sum'] - user_df['performance'] * user_df['user_count'] + user_df['question_average_sum']) / (user_df['user_count'] + user_df['user_count_previous'])\n    user_df['user_sum'] = user_df['user_sum'] + user_df['user_sum_previous']\n    user_df['user_count'] = user_df['user_count'] + user_df['user_count_previous']\n    user_df['user_average'] = user_df['user_sum'] / user_df['user_count']\n    user_df['performance'] = user_df['user_average'] - user_df['user_mean_performance']\n    user_df.drop(['user_sum_previous','user_count_previous','question_average_sum','user_mean_performance'],axis=1,inplace=True)\n    \n    return user_df\n\ndef build_question_df(prior_df,question_df):\n    \n    if (prior_df.shape[0] == 0):\n        return question_df\n    \n    #Dictionnary for questions average\n    question_sum_prior = pd.DataFrame(prior_df.loc[prior_df['content_type_id'] == 0]\\\n                           .groupby(['content_id'])['answered_correctly_response'].sum())\\\n                           .rename(columns={'answered_correctly_response':'question_sum'})\n    \n    #Dictionnary for questions count\n    question_count_prior = pd.DataFrame(prior_df.loc[prior_df['content_type_id'] == 0]\\\n                             .groupby(['content_id']).size(),columns=['question_count'])\n    \n    #Joining the two previous dataframes in one\n    question_df = question_df.join(question_sum_prior,rsuffix='_previous').join(question_count_prior,rsuffix='_previous')\n    \n    #Filling null values\n    question_df['question_average'].fillna(0,inplace=True)\n    question_df['question_count'].fillna(0,inplace=True)\n    question_df['question_sum'].fillna(0,inplace=True)\n    question_df['question_sum_previous'].fillna(0,inplace=True)\n    question_df['question_count_previous'].fillna(0,inplace=True)\n\n    #Updating values\n    question_df['question_sum'] = question_df['question_sum'] + question_df['question_sum_previous']\n    question_df['question_count'] = question_df['question_count'] + question_df['question_count_previous']\n    question_df['question_average'] = question_df['question_sum'] / question_df['question_count']\n    question_df.drop(['question_count_previous','question_sum_previous'],inplace=True,axis=1)\n    \n    return question_df\ndef add_answers_to_prior_df(current_df,prior_df):\n    prior_df_ = prior_df.copy()\n    if (prior_df.shape[0] > 0):\n        val = eval(current_df.iloc[0]['prior_group_answers_correct'])\n        if (len(val) == prior_df.shape[0]):\n            prior_df_['answered_correctly_response'] = val\n    return prior_df_\ndef question_average_sum_by_user(df,question_df):\n    my_dict = {}\n    group = df.groupby(['user_id'])\n    for user, val in group:\n        average_sum = 0.0\n        for row_index, row in val.iterrows():\n            if (row['content_type_id'] == False):\n                question_id = row['content_id']\n                question_average = question_df.at[question_id,'question_average']\n                average_sum += question_average\n    #         print(f'user = {user}, id = {question_id}, average = {question_average}, average_sum={average_sum}')\n        my_dict[user] = [average_sum]\n    return pd.DataFrame.from_dict(my_dict,orient='index',columns=['question_average_sum'])\nprior_df = pd.DataFrame()\ncurrent_df = pd.DataFrame()\nTARGET_COL = ['answered_correctly']\nFEATURE_COLS1 = ['row_id', 'user_count','performance', 'question_average']\nFEATURE_COLS2 = ['answered_correctly_avg_u', 'answered_correctly_sum_u', 'count_u', 'answered_correctly_avg_c', 'part', 'prior_question_had_explanation', 'prior_question_elapsed_time']\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"Question_df = pd.read_csv('../input/featuresriid/question.csv')\nuser_df = pd.read_csv('../input/featuresriid/user.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import lightgbm as lgb\nmodel_lgb1 = lgb.Booster(model_file='../input/featuresriid/model_twofeatures.txt')\nmodel_lgb2 = lgb.Booster(model_file='../input/7features760/model.txt')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"\ndef add_user_feats_without_update(df, answered_correctly_sum_u_dict, count_u_dict):\n    acsu = np.zeros(len(df), dtype=np.int32)\n    cu = np.zeros(len(df), dtype=np.int32)\n    for cnt,row in enumerate(df[['user_id']].values):\n        acsu[cnt] = answered_correctly_sum_u_dict[row[0]]\n        cu[cnt] = count_u_dict[row[0]]\n    user_feats_df = pd.DataFrame({'answered_correctly_sum_u':acsu, 'count_u':cu})\n    user_feats_df['answered_correctly_avg_u'] = user_feats_df['answered_correctly_sum_u'] / user_feats_df['count_u']\n    df = pd.concat([df, user_feats_df], axis=1)\n    return df\n\ndef update_user_feats(df, answered_correctly_sum_u_dict, count_u_dict):\n    for row in df[['user_id','answered_correctly','content_type_id']].values:\n        if row[2] == 0:\n            answered_correctly_sum_u_dict[row[0]] += row[1]\n            count_u_dict[row[0]] += 1","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\ndf_batch_prior = None\ncounter = 0\niter_nb = 0\nfrom collections import defaultdict\nTARGET = 'answered_correctly'\n\n\n\nfor test_batch in iter_test:\n    counter += 1\n    \n    (current_df, sample_prediction_df) = test_batch\n    if (iter_nb != 0):\n        prior_df = add_answers_to_prior_df(current_df,prior_df)\n        Question_df = build_question_df(prior_df,Question_df)\n        user_df = build_user_df(prior_df,user_df,Question_df)\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    prior_df = current_df.copy()\n    current_df = data_transform(current_df,FEATURE_COLS1,False,False)\n    predictions0 = model_lgb1.predict(current_df.iloc[:,1:])\n    iter_nb = 1\n    print('--')\n    \n    (test_df, sample_prediction_df) = test_batch\n    answered_correctly_sum_u_dict = defaultdict(int)\n    count_u_dict = defaultdict(int)\n    content_df = pd.read_csv('../input/7fetscontent/content.csv')\n    qUestion_df = pd.read_csv('../input/7fetscontent/question.csv')\n    if df_batch_prior is not None:\n        previous_test_df[TARGET] = eval(test_df[\"prior_group_answers_correct\"].iloc[0])\n        update_user_feats(previous_test_df, answered_correctly_sum_u_dict, count_u_dict)\n    previous_test_df = test_df.copy()\n    test_df = test_df[test_df['content_type_id'] == 0].reset_index(drop=True)\n    test_df = add_user_feats_without_update(test_df, answered_correctly_sum_u_dict, count_u_dict)\n    test_df = pd.merge(test_df, content_df, on='content_id',  how=\"left\")\n    test_df = pd.merge(test_df, qUestion_df, left_on='content_id', right_on='question_id', how='left')\n    test_df['prior_question_had_explanation'] = test_df.prior_question_had_explanation.fillna(False).astype('int8')\n    test_df['prior_question_elapsed_time_mean'] = test_df.prior_question_elapsed_time.fillna(25439.41)\n    predictions1 =  model_lgb2.predict(test_df[FEATURE_COLS2])\n    #print('--')\n\n    \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    #print('1')\n    # get state\n    df_batch = test_batch[0][test_batch[0].content_type_id == 0]\n    #print('2')\n\n    records = df_batch[batch_cols].fillna(0).to_records(index=False)\n    #print('3')\n\n    df_batch = pd.read_sql(select_state(batch_cols, records), conn)\n\n    # predict\n    predictions2 = model_xgb.predict(xgb.DMatrix(df_batch[test_cols]))\n    df_batch['answered_correctly'] = predictions0*0.4+predictions2*0.6#+predictions0)\n\n    #submit\n    env.predict(df_batch[['row_id', 'answered_correctly']])\n    #print('ok')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"current_df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"predictions2,predictions1,predictions0","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}