{"cells":[{"metadata":{"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"# This Python 3 environment comes with many helpful analytics libraries installed\n# It is defined by the kaggle/python Docker image: https://github.com/kaggle/docker-python\n# For example, here's several helpful packages to load\n\n \n\n# Input data files are available in the read-only \"../input/\" directory\n# For example, running this (by clicking run or pressing Shift+Enter) will list all files under the input directory\n\nimport os\nfor dirname, _, filenames in os.walk('/kaggle/input'):\n    for filename in filenames:\n        print(os.path.join(dirname, filename))\n\n# You can write up to 5GB to the current directory (/kaggle/working/) that gets preserved as output when you create a version using \"Save & Run All\" \n# You can also write temporary files to /kaggle/temp/, but they won't be saved outside of the current session","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# Check if GPU is enabled\nimport tensorflow as tf\nimport timeit\ngpu = None\ndevice_name = tf.test.gpu_device_name()\nif \"GPU\" in device_name:\n    gpu='gpu'\n    print('Found GPU at: {}'.format(device_name))","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":false,"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"from IPython.core.magic import register_cell_magic\n# This magic is used to skip some cells when running\n@register_cell_magic\ndef skip(line, cell=None):\n    '''Skips execution of the current line/cell if line evaluates to True.'''\n    if eval(line):\n        return\n        \n    get_ipython().run_cell(cell)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"#From https://www.kaggle.com/rohanrao/ashrae-half-and-half\n\nfrom pandas.api.types import is_datetime64_any_dtype as is_datetime\nfrom pandas.api.types import is_categorical_dtype\n\ndef reduce_mem_usage(df, use_float16=False,verbose=True):\n    \"\"\"\n    Iterate through all the columns of a dataframe and modify the data type to reduce memory usage.        \n    \"\"\"\n    \n    start_mem = df.memory_usage().sum() / 1024**2\n    if verbose :print(\"Memory usage of dataframe is {:.2f} MB\".format(start_mem))\n    \n    for col in df.columns:\n        if is_datetime(df[col]) or is_categorical_dtype(df[col]):\n            continue\n        col_type = df[col].dtype\n        \n        if col_type != object:\n            c_min = df[col].min()\n            c_max = df[col].max()\n            if str(col_type)[:3] == \"int\":\n                if c_min > np.iinfo(np.int8).min and c_max < np.iinfo(np.int8).max:\n                    df[col] = df[col].astype(np.int8)\n                elif c_min > np.iinfo(np.int16).min and c_max < np.iinfo(np.int16).max:\n                    df[col] = df[col].astype(np.int16)\n                elif c_min > np.iinfo(np.int32).min and c_max < np.iinfo(np.int32).max:\n                    df[col] = df[col].astype(np.int32)\n                elif c_min > np.iinfo(np.int64).min and c_max < np.iinfo(np.int64).max:\n                    df[col] = df[col].astype(np.int64)  \n            else:\n                if use_float16 and c_min > np.finfo(np.float16).min and c_max < np.finfo(np.float16).max:\n                    df[col] = df[col].astype(np.float16)\n                elif c_min > np.finfo(np.float32).min and c_max < np.finfo(np.float32).max:\n                    df[col] = df[col].astype(np.float32)\n                else:\n                    df[col] = df[col].astype(np.float64)\n        else:\n            df[col] = df[col].astype(\"category\")\n\n    end_mem = df.memory_usage().sum() / 1024**2\n    if verbose:print(\"Memory usage after optimization is: {:.2f} MB\".format(end_mem))\n    if verbose:print(\"Decreased by {:.1f}%\".format(100 * (start_mem - end_mem) / start_mem))\n    \n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"import random\nimport pandas as pd\nimport numpy as np\nimport seaborn as sns\nimport matplotlib.pyplot as plt\nimport gc\nfrom sklearn.metrics import roc_auc_score\nfrom sklearn.preprocessing import LabelEncoder\nfrom sklearn.preprocessing import StandardScaler,MinMaxScaler,PolynomialFeatures\nimport json\nfrom sklearn.metrics import roc_auc_score,accuracy_score\nimport lightgbm as lgb\nimport dill\nimport time\nfrom sklearn.pipeline import Pipeline,FeatureUnion\nfrom sklearn.decomposition import PCA\nfrom sklearn.feature_selection import SelectKBest, chi2, f_regression\nfrom sklearn.model_selection import train_test_split\nfrom sklearn.base import BaseEstimator, ClassifierMixin\nimport xgboost as xgb\nimport tensorflow as tf\nfrom tensorflow import keras","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Track everything:\n\nWe start by converting the dataset into **.feather** format. After we show how to pick a subset of users from the full dataset in a way to balance both positive and negative classes. We use Autoencoder to reduce the tags into one column.   We preprocessed and did some feature engineering on the selected subset. We also track all user's state within the metadata file that we migrated to SQLite files. \nhttps://www.kaggle.com/tchaye59/riiid-dataset-to-feather\n\nhttps://www.kaggle.com/tchaye59/riiid-preprocess-and-balance-the-dataset\n\nhttps://www.kaggle.com/tchaye59/riiid-metadata-to-sqlite\n\n# Current goal\nIn this notebook, we will show how to train and submit a model using the SQL files. We will also gradually update the database at each step."},{"metadata":{},"cell_type":"markdown","source":"Since training takes time, we will do it in two steps. We train first and then we submit"},{"metadata":{"trusted":true},"cell_type":"code","source":"np.random.seed(59)\ntf.random.set_seed(59)\nrandom.seed(59)\npath_train1 = '../input/riiid-preprocess-and-balance-the-dataset/train0.feather'\npath_train2 = '../input/riiid-preprocess-and-balance-the-dataset/train1.feather'\npath_content = '../input/riiid-preprocess-and-balance-the-dataset/content.feather'\npath_metadata = '../input/riiid-preprocess-and-balance-the-dataset/metadata.dill'","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# submit or train the models\nSUBMIT = True","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"We get back the models we train from the previous commit"},{"metadata":{"trusted":true},"cell_type":"code","source":"! cp -r /kaggle/input/riiid-work-with-the-full-state-using-sqlalchemy/*.dill ./","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Load data\nLoad the training data and metadata prepared from: https://www.kaggle.com/tchaye59/riiid-preprocess-and-balance-the-dataset"},{"metadata":{"trusted":true},"cell_type":"code","source":"%%skip SUBMIT\nmetadata = dill.load(open(path_metadata,'rb'))\n\ntarget = 'answered_correctly'\ntrain_df = pd.read_feather(path_train1)\ntrain_df = train_df.append(pd.read_feather(path_train2))\ntrain_df.shape","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Merge global statistics with the training dataset"},{"metadata":{"trusted":true},"cell_type":"code","source":"%%skip SUBMIT\nfor key in metadata:\n    if key in [('content_id',),('bundle_id',)]:\n        print(key)\n        train_df = train_df.merge(metadata[key],how='left',on=key)\ndel metadata","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-output":true},"cell_type":"code","source":"%%skip SUBMIT\ntrain_df.fillna(0,inplace=True)\ntrain_df = reduce_mem_usage(train_df)\ntrain_df.info()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Compute mean from counts and sums"},{"metadata":{"trusted":true},"cell_type":"code","source":"def add_mean(df,remove=False):\n    for col in ['user_id','content_id','bundle_id',]:\n        c1 = f'{col}_roll_count'\n        c2 = f'{col}_roll_sum'\n        c3 = f'{col}_roll_mean'\n        df[c3] = (df[c2]/df[c1]).astype(np.float32)\n        if remove:\n            del df[c1],df[c2]\n    for col in ['bundle','content']:\n        c1 = f'{col}_count'\n        c2 = f'{col}_sum'\n        c3 = f'{col}_mean'\n        df[c3] = (df[c2]/df[c1]).astype(np.float32)\n        if remove:\n            del df[c1],df[c2]\n    df.fillna(-1,inplace=True)\n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%skip SUBMIT\nignore_columns = ['user_answer','user_id','row_id']\ntrain_df = add_mean(train_df,remove=False)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"markdown","source":"# Build models"},{"metadata":{"trusted":true},"cell_type":"code","source":"%%skip SUBMIT\nfeatures = list(train_df.columns)\nfor col in ignore_columns+['answered_correctly']:\n    if col in features:features.remove(col)\nprint(features)\nprint('Features size: ',len(features))\ndill.dump(features,open('features.dill','wb'))\ndill.dump(ignore_columns,open('ignore_columns.dill','wb'))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Split the dataset"},{"metadata":{"trusted":true},"cell_type":"code","source":"%%skip SUBMIT\n\nX_test,y_test = None,None\nif True:\n    train_df,test_df = train_test_split(train_df,\n                                        stratify=train_df[target],\n                                        test_size=0.2, \n                                        random_state=1)\n    test_df.drop(columns=ignore_columns,inplace=True)\n    X_test,y_test = test_df[features].astype(np.float32),test_df[target]\n    del test_df\n# remove ignore_columns\ntrain_df.drop(columns=ignore_columns,inplace=True)\nX,y = train_df[features].astype(np.float32),train_df[target]\ndel train_df\nX.shape","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"gc.collect()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# LGB Model"},{"metadata":{"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"class CustomLGBClassifier(BaseEstimator, ClassifierMixin):\n    def __init__(self):\n        self.params = {\n            'objective': 'binary',\n            'learning_rate': 0.1,\n            #\"num_leaves\" : 10,\n            #\"max_depth\": 32,\n            'verbose': 1,\n            'device': gpu,\n            'metrics': ['binary','auc'],\n            'nthread':4,\n            'seed':1,\n        }\n        self.model = None\n        \n        self.pipe = Pipeline([\n            ('scaler',MinMaxScaler()),\n            #('kbest',SelectKBest(score_func=chi2,k=30)),\n        ])\n        self.score_value = None\n        \n\n    \n    def fit(self, X,y, X_test=None,y_test=None):\n        # preprocess the data\n        X = self.pipe.fit_transform(X,y)\n        train_data = lgb.Dataset(X, label=y)\n        valid_sets = [train_data,]\n        \n        # preprocess test data\n        val_data = None\n        if X_test is not None:\n            X_test = self.pipe.transform(X_test)\n            val_data = lgb.Dataset(X_test, label=y_test)\n            valid_sets.append(val_data)\n            \n        #Train\n        self.model = lgb.train(self.params,\n                                  train_data,\n                                  num_boost_round=3000,\n                                  valid_sets=valid_sets,\n                                  early_stopping_rounds=50,\n                                  verbose_eval=True\n                                 )\n        return self\n    \n    def predict(self, X):\n        return self.model.predict(self.pipe.transform(X))\n    \n    def predict_proba(self,X):\n        return self.predict(X)[:,np.newaxis]\n    \n    def score(self,X,y):\n        y_pred = self.predict(X)\n        auc = round(roc_auc_score(y, y_pred),2)\n        acc = round(accuracy_score(y, y_pred>0.5),2)\n        return auc,acc","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"Train"},{"metadata":{"trusted":true},"cell_type":"code","source":"train_lgbc = False","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"%%skip SUBMIT or not train_lgbc\nmodel = CustomLGBClassifier()\nmodel.fit(X,y,X_test,y_test)\ndill.dump(model,open(f'lgb_model.dill','wb'))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# XGB Model"},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"trusted":true},"cell_type":"code","source":"class CustomXGBClassifier(BaseEstimator, ClassifierMixin):\n    def __init__(self,):\n        self.params = {\n            'objective': 'binary:logistic',\n            'learning_rate': 0.1,\n            \"max_depth\": 10,\n            'eval_metric':'auc',\n            'subsample':0.6,\n            'colsample_bytree':0.6,\n            'tree_method': 'gpu_hist' if gpu else None,\n            #'nthread':4,\n            'seed': 1\n        }\n        self.model = None\n        \n        self.pipe = Pipeline([\n            ('scaler',MinMaxScaler()),\n            #('kbest',SelectKBest(score_func=chi2,k=40)),\n        ])\n        self.score_value = None\n        self.best_ntree_limit = 0\n        \n\n    \n    def fit(self, X,y, X_test=None,y_test=None):\n        # preprocess the data\n        X = self.pipe.fit_transform(X,y)\n        train_data = xgb.DMatrix(X, label=y,feature_names=features)\n        evals = [(train_data, 'train'),]\n        # preprocess test data\n        if X_test is not None:\n            X_test = self.pipe.transform(X_test)\n            val_data = xgb.DMatrix(X_test, label=y_test,feature_names=features)\n            evals.append((val_data, 'eval'))\n        \n        #Train\n        self.model = xgb.train(self.params,train_data,3000,\n                               evals = evals,\n                               early_stopping_rounds=50,\n                               verbose_eval=True\n                              )\n        self.best_ntree_limit = self.model.best_ntree_limit\n        return self\n    \n    def plot_features(self):\n        xgb.plot_importance(self.model,show_values=True,max_num_features=len(features))\n\n    \n    def init_thread(self):\n        self.best_ntree_limit = self.model.best_ntree_limit\n        self.model.save_model('tmp.model')\n        bst = xgb.Booster({'nthread': 4}) \n        bst.load_model('tmp.model')\n        self.model = bst\n    \n    def predict(self, X):\n        return self.model.predict(\n            xgb.DMatrix(self.pipe.transform(X),feature_names=features),\n            #ntree_limit=self.best_ntree_limit,\n        )\n    \n    def predict_proba(self,X):\n        return self.predict(X)[:,np.newaxis]\n    \n    def score(self,X,y):\n        y_pred = self.predict(X)\n        auc = round(roc_auc_score(y, y_pred),2)\n        return auc","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train_xgbc = False","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%skip SUBMIT or not train_xgbc\nmodel = CustomXGBClassifier()\nmodel.fit(X,y,X_test,y_test)\nprint('Saving...')\ndill.dump(model,open(f'xgb_model.dill','wb'))\nprint('Done...')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%skip SUBMIT or not train_xgbc\nmodel.plot_features()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Neural Network"},{"metadata":{"trusted":true},"cell_type":"code","source":"train_nn = False","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-output":true,"_kg_hide-input":true,"trusted":true},"cell_type":"code","source":"%%skip SUBMIT or not train_nn\n\n\nmodel_path=\"best_nn_model.h5\"\ncallbacks_list = [\n    keras.callbacks.ModelCheckpoint(model_path, \n                                    verbose=0,\n                                    monitor='auc', \n                                    save_best_only=True, \n                                    mode='max'),\n]\nbatch_size = 2**14\n\nval_data = None if X_test is None else (X_test,y_test)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%skip SUBMIT or not train_nn\n\nlayers = [keras.layers.BatchNormalization(input_shape=(len(features),)),]\n\nfor u in range(5):\n    layers.extend([\n        keras.layers.BatchNormalization(input_shape=(len(features),)),\n        keras.layers.Dense(256),\n        keras.layers.BatchNormalization(),\n        keras.layers.Activation('relu'),\n        #keras.layers.Dropout(0.1),\n    ])\n\nmodel = keras.Sequential([\n    *layers,\n    keras.layers.Dense(1,activation='sigmoid'),\n])\nif os.path.exists(\"nn_model.h5\"):\n    print('Loading...')\n    model = keras.models.load_model(\"nn_model.h5\")\n    \nmodel.compile(loss=keras.losses.binary_crossentropy,\n              optimizer=keras.optimizers.Adam(0.01),\n              metrics=[\n                'acc',\n                keras.metrics.AUC(name='auc'),\n             ]\n             )\nmodel.summary()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%skip SUBMIT or not train_nn\n\nhistory = model.fit(X,y,\n                    batch_size=batch_size,\n                    validation_data=val_data,\n                    epochs=100,\n                    callbacks=callbacks_list,\n                   )\nmodel.save(\"nn_model.h5\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%skip SUBMIT or not train_nn\npd.DataFrame(history.history).plot()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Make Submission"},{"metadata":{"trusted":true,"_kg_hide-input":false},"cell_type":"code","source":"%%skip not SUBMIT\nimport json\nfrom sklearn.metrics import roc_auc_score,accuracy_score\nfrom sqlalchemy import create_engine\nfrom sqlalchemy.sql import select,delete,and_\nfrom sqlalchemy.sql.expression import table,column\nimport multiprocessing as mp\nimport threading\nfrom queue import Queue\nfrom mlxtend.classifier import EnsembleVoteClassifier\n\nlock = mp.Lock()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Copy the databases files"},{"metadata":{"trusted":true},"cell_type":"code","source":"%%skip not SUBMIT\n! rm -rf ./*db.*\n! cp /kaggle/input/riiid-metadata-to-sqlite/*db.* ./","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"#### Select the model that we want to submit"},{"metadata":{"trusted":true},"cell_type":"code","source":"use_lgb = True\nuse_xgb = False\nuse_nn = False","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%skip not (use_lgb and SUBMIT)\nmodel = dill.load(open('lgb_model.dill','rb'))\nmodel","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%skip not (use_xgb and SUBMIT)\nmodel = dill.load(open('xgb_model.dill','rb'))\n#model.init_thread()\nmodel","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%skip not (use_nn and SUBMIT)\nmodel = keras.models.load_model(\"best_nn_model.h5\")","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Connect to databases"},{"metadata":{"trusted":true},"cell_type":"code","source":"%%skip not SUBMIT\n\nmetadata_info = dill.load(open('/kaggle/input/riiid-metadata-to-sqlite/metadata_info.dill','rb'))\nfeatures = dill.load(open('features.dill','rb'))\nignore_columns = dill.load(open('ignore_columns.dill','rb'))\ncontent_df = pd.read_feather('../input/riiid-preprocess-and-balance-the-dataset/content.feather')\nprint('Features size: ',len(features))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":false},"cell_type":"code","source":"%%skip not SUBMIT\nfor key in metadata_info:\n    name = f\"db.{'_'.join(key)}.sqlite\"\n    engine = create_engine(f'sqlite:///{name}?check_same_thread=False', echo=False)\n    sqlite_connection = engine.connect()\n    cols = metadata_info[key][-1]\n    cols = [column(col) for col in cols]+[column('index'),]\n    tab = table('_'.join(key), *cols)\n    metadata_info[key][0] = (sqlite_connection,tab)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"# Runs a process in a thread\nclass Worker(threading.Thread):\n    \n    def __init__(self, process,args,queue=None):\n        super().__init__()\n        self.queue = queue\n        self.process = process\n        self.args = args\n\n    def run(self):\n        res = self.process(self.args)\n        if self.queue:\n            self.queue.put(res)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"def build_index(x):\n    return '_'.join(map(lambda x:str(int(x)),x)) if type(x) not in [int,float] else str(int(x))\n    \n# Get data from the database\n# We also keep the DB result in a cache in order to updates it later\ndef getValues(args):\n    key,df,cache= args\n    df = df[list(key)]\n    index = df.index\n    df = df[list(key)]\n    db,tab = metadata_info[key][0]\n    cols = metadata_info[key][-1]\n    \n    ids = df.apply(build_index,axis=1).values\n    query = select([tab.c['index'],] + [tab.c[col] for col in cols]).where(tab.c.index.in_(np.unique(ids)))\n    result = list(db.execute(query))\n    \n    res_df = pd.DataFrame(result,columns=['tmp_key']+cols)\n    res_df.set_index('tmp_key',inplace=True)\n    df['tmp_key'] = ids\n    df = df.merge(res_df,how='left',on='tmp_key').fillna(0)\n    # keep cache for later updates\n    cache[key] = df.groupby([*key,]).tail(1).set_index([*key,])\n    df = df.drop(columns=[*key,'tmp_key'])\n    df.index = index\n    return df\n\ndef merge_metadata(df):\n    cache = {}\n    workers= []\n    queue = Queue()\n    for key in metadata_info:\n        worker =  Worker(getValues,(key,df,cache),queue)\n        worker.start()\n        workers.append(worker)\n    frames = [queue.get() for _ in workers]\n    frames.append(df)\n    for worker in workers:\n        worker.join()\n    return  pd.concat(frames,axis=1),cache\n\ndef update_metadata(args):\n    df,key = args\n    if not df.shape[0]:\n        return\n    key = (key,) if type(key) == str else tuple(key)\n    if key not in metadata_info:\n        return\n    (db,tab),cols = metadata_info[key]\n    \n    cols = [c for c in cols if c not in ignore_columns]\n    \n    #Make sur we have the right index in df.index\n    if all(k in df.index.names for k in key):\n        df = df.loc[:,[c for c in cols if c not in key]]\n    else:\n        df = df.set_index(list(key)).loc[:,cols]\n    db_index = list(map(build_index,df.index))\n    df.index = db_index\n    # Delete\n    query = delete(tab).where(tab.c.index.in_(db_index))\n    db.execute(query)\n    # Add new values\n    df.to_sql('_'.join(key), db, if_exists='append')\n    \ndef add_previous_state(df,key,cache):\n    # Make df.index is correct\n    key = (key,) if type(key) == str else tuple(key)\n    if not all(k in df.index.names for k in key):\n        df.set_index(list(key),inplace=True)\n    index = df.index\n    cols = [c for c in df.columns if c not in ignore_columns]\n    if not cols:\n        return df\n    return (df+cache[key])[cols]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true},"cell_type":"code","source":"def user_roll_features(df,cols):\n    metadata = {}\n    for col in cols:\n        #roll count\n        c1 = f'{col}_roll_count'\n        c2 = f'{col}_roll_sum'\n        \n        df_tmp = df[[col,'user_id','answered_correctly',]]\n        grp = df_tmp.groupby([col,'user_id'])\n        df_tmp[c1] = grp.answered_correctly.cumcount()+1\n        df_tmp[c2] = grp.answered_correctly.cumsum()\n        \n        # In windows\n        wincols = []\n        for win in [50,10]:\n            sum_col = f'{col}_win{win}_sum'\n            count_col = f'{col}_win{win}_count'\n            df_tmp[sum_col] = df_tmp[c2]\n            df_tmp[count_col] = df_tmp[c1]\n            wincols.append(sum_col)\n            wincols.append(count_col)\n        \n        #save metadata\n        grp = df_tmp.groupby(['user_id',col]).tail(1)\n        metadata[('user_id',col)] = grp.set_index(['user_id',col])[[c1,c2,*wincols]]\n            \n    return metadata","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"##### Do some tests before we submit"},{"metadata":{"trusted":true},"cell_type":"code","source":"# Skip tests when submitting the notebook\nskip_test = True","execution_count":null,"outputs":[]},{"metadata":{"trusted":true,"_kg_hide-input":true,"_kg_hide-output":true},"cell_type":"code","source":"%%skip skip_test\nval_df = pd.read_csv('/kaggle/input/riiid-test-answer-prediction/train.csv',\n                     header=0, \n                     skiprows=range(1,1000000*5),\n                     nrows=500000)\nval_df['row_id'] = range(val_df.shape[0])\nval_df = val_df[['row_id', 'timestamp', 'user_id', 'content_id', 'content_type_id',\n       'task_container_id', 'prior_question_elapsed_time',\n       'prior_question_had_explanation',\n        'answered_correctly','user_answer']]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%skip skip_test\n\nprev_test_df = None\nprev_cache = None\n\nauc_sum = 0 \nacc_sum = 0\ndf = val_df\ncount = 0\nwhile df.shape[0]:\n    count+=1\n    selector = df.task_container_id == df.task_container_id.min()\n    test_df = df.loc[selector]\n    df = df.loc[~selector]\n    print(f'{test_df.shape[0]}/{df.shape[0]}')\n    \n    \n    # merge content file \n    test_df = test_df.merge(content_df,how='left',on=['content_id',])\n    test_df.prior_question_had_explanation = test_df.prior_question_had_explanation.fillna(False).astype(np.int8)\n     \n    \n    test_state = test_df.loc[test_df['content_type_id'] == 0]\n    test_state.replace([np.inf, -np.inf], 999999999,inplace=True)\n    #Merge metadat\n    test_state,cache = merge_metadata(test_state)\n    # prior_duration\n    test_state['prior_duration'] = test_state.timestamp-test_state.prev_timestamp\n    #add means\n    test_state = add_mean(test_state)\n    \n    test_state['answered_correctly'] = model.predict(test_state[features])\n    \n    y = test_df[test_df['content_type_id'] == 0]['answered_correctly']\n    y_pred = test_state['answered_correctly']\n    auc = roc_auc_score(y, y_pred)\n    acc = accuracy_score(y, y_pred>0.5)\n    auc_sum += auc \n    acc_sum += acc\n    print(f\"AUC:{round(auc,4)} | Accuracy: {round(acc,4)} | AUC MEAN: {round(auc_sum/count,4)} | ACC MEAN: {round(acc_sum/count,4)}\")\n    # Update metadata\n    if prev_test_df is not None:\n        workers = []\n        \n        #prior_group_answers_correct\t= json.loads(test_df.iloc[0].prior_group_answers_correct)\n        #prior_group_responses = json.loads(test_df.iloc[0].prior_group_responses)\n        #prior_group_responses = np.array(prior_group_responses)\n        #prior_group_answers_correct = np.array(prior_group_answers_correct)\n        #prev_test_df['user_answer'] = prior_group_responses\n        #prev_test_df['answered_correctly'] = prior_group_answers_correct\n        prev_test_df = prev_test_df[prev_test_df.content_type_id == 0]\n        \n        # update user performances over time\n        user_df = prev_test_df[['user_id','answered_correctly']]\n        grp = user_df.groupby(['user_id'])\n        user_df['user_id_roll_sum'] = grp['answered_correctly'].cumsum()\n        user_df['user_id_roll_count'] = grp['answered_correctly'].cumcount()+1\n        # take last state\n        user_df = user_df.groupby(['user_id',]).tail(1)\n        user_df.set_index('user_id',inplace=True)\n        del user_df['answered_correctly']\n        # Also, update performance in the small window\n        win_cols = []\n        for win in [50,10]:\n            sum_col = f'user_id_win{win}_sum'\n            count_col = f'user_id_win{win}_count'\n            user_df[sum_col] = user_df.user_id_roll_sum\n            user_df[count_col] = user_df.user_id_roll_count\n            win_cols.append(sum_col)\n            win_cols.append(count_col)\n        win_current_state = user_df[win_cols] \n        user_df = add_previous_state(user_df,('user_id',),prev_cache)\n        # Don't let the count exeed the windows size\n        for win in [50,10]:\n            sum_col = f'user_id_win{win}_sum'\n            count_col = f'user_id_win{win}_count'\n            selector = (user_df[count_col]>win).values\n            user_df.loc[selector,[sum_col,count_col]] = win_current_state.loc[selector,[sum_col,count_col]]\n        \n        # prev answers\n        tmp_df = prev_test_df[['user_answer','answered_correctly','user_id','timestamp']].groupby('user_id').tail(1)\n        tmp_df.set_index('user_id',inplace=True)\n        tmp_df.columns = [f'prev_{col}' for col in tmp_df.columns]\n        user_df = user_df.merge(tmp_df,how='left',on=['user_id',])\n        worker =  Worker(update_metadata,(user_df,['user_id'],))\n        worker.start()\n        workers.append(worker)\n        \n        #user statistics\n        cols = ['content_id','bundle_id',]\n        metadata = user_roll_features(prev_test_df,cols)\n        for key in metadata:\n            col = key[-1]\n            tmp_df = metadata[key]\n            win_current_state = tmp_df\n            tmp_df = add_previous_state(tmp_df,key,prev_cache)\n            \n            # Don't let the count exeed the windows size\n            for win in [50,10]:\n                sum_col = f'{col}_win{win}_sum'\n                count_col = f'{col}_win{win}_count'\n                selector = (tmp_df[count_col]>win).values\n                tmp_df.loc[selector,[sum_col,count_col]] = win_current_state.loc[selector,[sum_col,count_col]]\n            \n            if key == ('user_id','content_id'):\n                #The prev user's answer in the content\n                tmp = prev_test_df[['content_id','user_id','answered_correctly',]]\n                tmp.rename(columns={'answered_correctly':'prev_content_answered_correctly'},inplace=True)\n                tmp = tmp.groupby(['content_id','user_id',]).tail(1)\n                tmp.set_index(['user_id','content_id'],inplace=True)\n                tmp_df = tmp_df.merge(tmp,how='left',on=['user_id','content_id'])\n            worker =  Worker(update_metadata,(tmp_df,key))\n            workers.append(worker)\n            worker.start()\n        # We wait for the workers to finish  \n        for worker in workers:\n            worker.join()\n    prev_test_df = test_df\n    prev_cache = cache","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"### Submit"},{"metadata":{"trusted":true,"_kg_hide-input":false,"_kg_hide-output":true},"cell_type":"code","source":"%%skip not SUBMIT\nimport riiideducation\nenv = riiideducation.make_env()\niter_test = env.iter_test()\n\nprev_test_df = None\nprev_cache = None\n\nfor (test_df, sample_prediction_df) in iter_test:\n    \n    # merge content file \n    test_df = test_df.merge(content_df,how='left',on=['content_id',])\n    test_df.prior_question_had_explanation = test_df.prior_question_had_explanation.fillna(False).astype(np.int8)\n     \n    \n    test_state = test_df.loc[test_df['content_type_id'] == 0]\n    test_state.replace([np.inf, -np.inf], 999999999,inplace=True)\n    #Merge metadat\n    test_state,cache = merge_metadata(test_state)\n    # prior_duration\n    test_state['prior_duration'] = test_state.timestamp-test_state.prev_timestamp\n    #add means\n    test_state = add_mean(test_state)\n    test_state['answered_correctly'] = model.predict(test_state[features])\n    \n    # Update metadata\n    if prev_test_df is not None:\n        workers = []\n        \n        prior_group_answers_correct\t= json.loads(test_df.iloc[0].prior_group_answers_correct)\n        prior_group_responses = json.loads(test_df.iloc[0].prior_group_responses)\n        prior_group_responses = np.array(prior_group_responses)\n        prior_group_answers_correct = np.array(prior_group_answers_correct)\n        prev_test_df['user_answer'] = prior_group_responses\n        prev_test_df['answered_correctly'] = prior_group_answers_correct\n        prev_test_df = prev_test_df[prev_test_df.content_type_id == 0]\n        \n        # update user performances over time\n        user_df = prev_test_df[['user_id','answered_correctly']]\n        grp = user_df.groupby(['user_id'])\n        user_df['user_id_roll_sum'] = grp['answered_correctly'].cumsum()\n        user_df['user_id_roll_count'] = grp['answered_correctly'].cumcount()+1\n        # take last state\n        user_df = user_df.groupby(['user_id',]).tail(1)\n        user_df.set_index('user_id',inplace=True)\n        del user_df['answered_correctly']\n        # Also, update performance in the small window\n        win_cols = []\n        for win in [50,10]:\n            sum_col = f'user_id_win{win}_sum'\n            count_col = f'user_id_win{win}_count'\n            user_df[sum_col] = user_df.user_id_roll_sum\n            user_df[count_col] = user_df.user_id_roll_count\n            win_cols.append(sum_col)\n            win_cols.append(count_col)\n        win_current_state = user_df[win_cols] \n        user_df = add_previous_state(user_df,('user_id',),prev_cache)\n        # Don't let the count exeed the windows size\n        for win in [50,10]:\n            sum_col = f'user_id_win{win}_sum'\n            count_col = f'user_id_win{win}_count'\n            selector = (user_df[count_col]>win).values\n            user_df.loc[selector,[sum_col,count_col]] = win_current_state.loc[selector,[sum_col,count_col]]\n        \n        # prev answers\n        tmp_df = prev_test_df[['user_answer','answered_correctly','user_id','timestamp']].groupby('user_id').tail(1)\n        tmp_df.set_index('user_id',inplace=True)\n        tmp_df.columns = [f'prev_{col}' for col in tmp_df.columns]\n        user_df = user_df.merge(tmp_df,how='left',on=['user_id',])\n        worker =  Worker(update_metadata,(user_df,['user_id'],))\n        worker.start()\n        workers.append(worker)\n        \n        #user statistics\n        cols = ['content_id','bundle_id',]\n        metadata = user_roll_features(prev_test_df,cols)\n        for key in metadata:\n            col = key[-1]\n            tmp_df = metadata[key]\n            win_current_state = tmp_df\n            tmp_df = add_previous_state(tmp_df,key,prev_cache)\n            \n            # Don't let the count exeed the windows size\n            for win in [50,10]:\n                sum_col = f'{col}_win{win}_sum'\n                count_col = f'{col}_win{win}_count'\n                selector = (tmp_df[count_col]>win).values\n                tmp_df.loc[selector,[sum_col,count_col]] = win_current_state.loc[selector,[sum_col,count_col]]\n            \n            if key == ('user_id','content_id'):\n                #The prev user's answer in the content\n                tmp = prev_test_df[['content_id','user_id','answered_correctly',]]\n                tmp.rename(columns={'answered_correctly':'prev_content_answered_correctly'},inplace=True)\n                tmp = tmp.groupby(['content_id','user_id',]).tail(1)\n                tmp.set_index(['user_id','content_id'],inplace=True)\n                tmp_df = tmp_df.merge(tmp,how='left',on=['user_id','content_id'])\n            worker =  Worker(update_metadata,(tmp_df,key))\n            workers.append(worker)\n            worker.start()\n        # We wait for the workers to finish  \n        for worker in workers:\n            worker.join()\n    prev_test_df = test_df\n    prev_cache = cache\n    \n    env.predict(test_state[['row_id', 'answered_correctly']])\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#pd.read_csv('submission.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"%%skip not SUBMIT\n! rm -rf ./*db.*","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}