{"cells":[{"metadata":{"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","trusted":true},"cell_type":"code","source":"import pandas as pd\nfrom pathlib import Path\nimport sqlite3\nimport riiideducation\nfrom sklearn.metrics import roc_auc_score\nfrom tqdm.notebook import tqdm","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"class PredictEnv:\n    def __init__(self, folds_path, folds):\n        self.conn = sqlite3.connect(':memory:')\n        self.c = self.conn.cursor()\n        self.setup_folds(folds_path, folds)\n\n    def setup_folds(self, folds_path, folds):        \n        self.c.executescript(f\"\"\"\n            ATTACH DATABASE \"{folds_path}\" AS folds_db;\n\n            DROP TABLE IF EXISTS b_records;\n\n            CREATE TABLE b_records AS\n            SELECT row_id, timestamp, user_id, content_id, content_type_id, task_container_id, prior_question_elapsed_time,\n                prior_question_had_explanation, answered_correctly, user_answer\n            FROM folds_db.train\n            WHERE fold in ({(', ').join(list(map(str,folds)))})\n            ORDER BY user_id, task_container_id, row_id;\n\n            CREATE INDEX user_id_task_container_id_index ON b_records (user_id, task_container_id);\n\n            DROP TABLE IF EXISTS b_users;\n\n            CREATE TABLE b_users AS\n            SELECT user_id, MIN(task_container_id) - 1 task_container_id_next, MAX(task_container_id) task_container_id_max\n            FROM b_records\n            GROUP BY user_id\n                ORDER BY user_id, task_container_id_next;\n\n            CREATE UNIQUE INDEX user_id_index ON b_users (user_id);\n\n            ALTER TABLE b_users\n                ADD COLUMN group_num INTEGER;\n\n        \"\"\").fetchone()\n\n        self.group_num = 0\n        self.records_remaining = self.c.execute('SELECT COUNT(*) FROM b_records').fetchone()[0]\n        self.df_users = pd.read_sql('SELECT * FROM b_users', self.conn)\n\n\n    def iter_test(self):\n        next_correct = '[]'\n        next_responses = '[]'\n\n        while self.records_remaining:\n            self.c.execute(f\"\"\"\n                INSERT INTO b_users (user_id)\n                SELECT user_id\n                FROM b_users\n                WHERE task_container_id_next <= task_container_id_max\n                LIMIT 1 + ABS(RANDOM() % 40) + ABS(RANDOM() % 1000) * (ABS(RANDOM() % 100) < 5)\n                ON CONFLICT (user_id) DO UPDATE SET\n                    task_container_id_next = task_container_id_next + 1,\n                    group_num = {self.group_num};\n            \"\"\").fetchone()\n            \n            self.conn.commit()\n\n            df_b = pd.read_sql(f\"\"\"\n                SELECT r.*\n                FROM b_records r\n                JOIN b_users u\n                ON group_num = {self.group_num}\n                    AND r.user_id = u.user_id\n                    AND r.task_container_id = u.task_container_id_next\n            \"\"\", self.conn)\n\n            if len(df_b):\n                df_b['group_num'] = self.group_num\n                df_b['prior_group_answers_correct'] = None\n                df_b.at[0, 'prior_group_answers_correct'] = next_correct\n\n                df_b['prior_group_responses'] = None\n                df_b.at[0, 'prior_group_responses'] = next_responses\n\n                next_correct = f'[{(\", \").join(df_b.answered_correctly.astype(str))}]'\n                next_responses = f'[{(\", \").join(df_b.user_answer.astype(str))}]'\n                del df_b['answered_correctly']\n                del df_b['user_answer']\n\n                df_b = df_b.set_index('group_num')\n\n                df_p = df_b[['row_id']].copy()\n                df_p['answered_correctly'] = 0.5\n                \n                self.records_remaining -= len(df_b)\n\n                yield df_b, df_p\n            \n            self.group_num += 1\n\n    def predict(self, df_pred):\n        if (df_pred.answered_correctly == -1).any():\n            raise\n        else:\n            df_pred.reset_index().to_sql('predictions', self.conn, if_exists='append', index=False)\n\n    def get_predictions(self):\n        df_preds = pd.read_sql(\"\"\"\n            SELECT p.row_id, b.answered_correctly y_true, p.answered_correctly y_pred\n            FROM predictions p\n            JOIN b_records b\n            ON p.row_id = b.row_id\n        \"\"\", self.conn)\n\n        self.score = roc_auc_score(df_preds.y_true, df_preds.y_pred)\n\n        print(f'ROC AUC Score: {self.score:0.4f}')\n\n        return df_preds","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Folds specified in [RIIID: Folding is Fun!](https://www.kaggle.com/calebeverett/riiid-folding-is-fun)."},{"metadata":{"trusted":true},"cell_type":"code","source":"mock = True\n\nif mock:\n    FOLDS = Path('../input/riiid-folds/riiid.db')\n    env = PredictEnv(FOLDS, [0, 1])\n    iter_test = env.iter_test()\n\nelse:\n    env = riiideducation.make_env()\n    iter_test = env.iter_test()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%time\nif mock:\n    pbar = tqdm(total=env.records_remaining)\n\ndf_batch_prior = None\n\nfor test_batch in iter_test:\n    \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\n    df_batch_prior = test_batch[0]\n    \n    df_batch = test_batch[0][test_batch[0].content_type_id == 0].copy()\n    if len(df_batch):\n        df_batch['answered_correctly'] = 0.65\n        env.predict(df_batch[['row_id', 'answered_correctly']])\n        \n    if mock:\n        pbar.update(len(df_batch_prior))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"if mock:\n    df_pred = env.get_predictions()\n    df_pred.head()","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}