{"cells":[{"metadata":{"_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_kg_hide-input":true,"_kg_hide-output":true,"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","execution":{"iopub.execute_input":"2020-12-18T08:37:04.945896Z","iopub.status.busy":"2020-12-18T08:37:04.945003Z","iopub.status.idle":"2020-12-18T08:37:04.977426Z","shell.execute_reply":"2020-12-18T08:37:04.97817Z"},"papermill":{"duration":0.063671,"end_time":"2020-12-18T08:37:04.978384","exception":false,"start_time":"2020-12-18T08:37:04.914713","status":"completed"},"tags":[],"trusted":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# 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":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.execute_input":"2020-12-18T08:37:05.055007Z","iopub.status.busy":"2020-12-18T08:37:05.054133Z","iopub.status.idle":"2020-12-18T08:37:05.058643Z","shell.execute_reply":"2020-12-18T08:37:05.059381Z"},"papermill":{"duration":0.04427,"end_time":"2020-12-18T08:37:05.059558","exception":false,"start_time":"2020-12-18T08:37:05.015288","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"import warnings\nwarnings.simplefilter(action='ignore', category=FutureWarning)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"_kg_hide-output":true,"execution":{"iopub.execute_input":"2020-12-18T08:37:05.134585Z","iopub.status.busy":"2020-12-18T08:37:05.133686Z","iopub.status.idle":"2020-12-18T08:37:13.882645Z","shell.execute_reply":"2020-12-18T08:37:13.883415Z"},"papermill":{"duration":8.790465,"end_time":"2020-12-18T08:37:13.883595","exception":false,"start_time":"2020-12-18T08:37:05.09313","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nimport dask.dataframe as dd\nimport tensorflow as tf\nfrom tensorflow import keras\nimport seaborn as sns\nimport matplotlib.pyplot as plt\nimport gc\nfrom sklearn.preprocessing import LabelEncoder,StandardScaler\nimport dill","execution_count":null,"outputs":[]},{"metadata":{"papermill":{"duration":0.034394,"end_time":"2020-12-18T08:37:13.952236","exception":false,"start_time":"2020-12-18T08:37:13.917842","status":"completed"},"tags":[]},"cell_type":"markdown","source":"### Track everything:\n\n1. In this notebook, I first show how to pick a subset of users from the training dataset in a way to balance both positive and negative classes. \n2. I use Autoencoder to reduce the tags into one column.  \n3. I preprocessed and did some features engineering on the selected subset, that I will use for further training.  \n4. I show how to track the last state of all users in each feature with a database.\n\nWe start by converting the dataset into **.feather** format :  https://www.kaggle.com/tchaye59/riiid-dataset-to-feather"},{"metadata":{"execution":{"iopub.execute_input":"2020-12-18T08:37:14.031143Z","iopub.status.busy":"2020-12-18T08:37:14.029282Z","iopub.status.idle":"2020-12-18T08:37:14.034394Z","shell.execute_reply":"2020-12-18T08:37:14.035139Z"},"papermill":{"duration":0.048876,"end_time":"2020-12-18T08:37:14.03535","exception":false,"start_time":"2020-12-18T08:37:13.986474","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"path_train = '/kaggle/input/riiid-dataset-to-feather/train.feather'\nnp.random.seed(123)\ntf.random.set_seed(123)","execution_count":null,"outputs":[]},{"metadata":{"_cell_guid":"79c7e3d0-c299-4dcb-8224-4455121ee9b0","_kg_hide-input":true,"_kg_hide-output":true,"_uuid":"d629ff2d2480ee46fbb7e2d37f6b5fab8052498a","execution":{"iopub.execute_input":"2020-12-18T08:37:14.127332Z","iopub.status.busy":"2020-12-18T08:37:14.120711Z","iopub.status.idle":"2020-12-18T08:37:14.13185Z","shell.execute_reply":"2020-12-18T08:37:14.131048Z"},"papermill":{"duration":0.061324,"end_time":"2020-12-18T08:37:14.131966","exception":false,"start_time":"2020-12-18T08:37:14.070642","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"#From https://www.kaggle.com/rohanrao/ashrae-half-and-half\nfrom pandas.api.types import is_datetime64_any_dtype as is_datetime\nfrom pandas.api.types import is_categorical_dtype\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":{"execution":{"iopub.execute_input":"2020-12-18T08:37:14.184834Z","iopub.status.busy":"2020-12-18T08:37:14.184168Z","iopub.status.idle":"2020-12-18T08:37:29.338145Z","shell.execute_reply":"2020-12-18T08:37:29.337453Z"},"papermill":{"duration":15.182763,"end_time":"2020-12-18T08:37:29.338263","exception":false,"start_time":"2020-12-18T08:37:14.1555","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"%%time\ntarget = 'answered_correctly'\ntrain_df = pd.read_feather(path_train)","execution_count":null,"outputs":[]},{"metadata":{"execution":{"iopub.execute_input":"2020-12-18T08:37:29.402495Z","iopub.status.busy":"2020-12-18T08:37:29.397385Z","iopub.status.idle":"2020-12-18T08:37:29.407608Z","shell.execute_reply":"2020-12-18T08:37:29.40686Z"},"papermill":{"duration":0.043724,"end_time":"2020-12-18T08:37:29.407773","exception":false,"start_time":"2020-12-18T08:37:29.364049","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"def get_splits(df,n=10):\n    res = []\n    while df.shape[0]:\n        tmp_df = df[['user_id','answered_correctly']].groupby('user_id').agg({'answered_correctly':['sum','count']}) \n        tmp_df.columns = ['class1','class_count']\n        tmp_df['class2'] = tmp_df.class_count - tmp_df.class1\n        # The difference between positive and negative class counts\n        tmp_df['class_diff'] = tmp_df.class1.values - tmp_df.class2.values\n        tmp_df.reset_index(inplace=True)\n        \n        s = 0\n        n_max = 1000000 # This helps in controlling the size of each split\n        user_ids = []\n        # We compute the diff between the 2 classes count and separate positive and negative values\n        negative_df = tmp_df[tmp_df.class_diff <= 0]\n        positive_df = tmp_df[tmp_df.class_diff > 0]\n        \n        # The order doesn't matter, but I prefer to give priority to big values\n        negative_df.sort_values(by=['class_count'],inplace=True,ascending=True,)\n        positive_df.sort_values(by=['class_count'],inplace=True,ascending=False,)\n        \n        #We collect user form the negative diff values\n        for i, row in negative_df.iterrows():\n            user_ids.append(row['user_id'])\n            s+=row['class_diff']\n            if s < -n_max:\n                break\n                \n        # 's' is negative  we collect user form the positive diff values to balance the two classes\n        for i, row in positive_df.iterrows():\n            user_ids.append(row['user_id'])\n            s+=row['class_diff']\n            if s >= 0:\n                break\n        \n        if len(user_ids) == 1:\n            user_ids = list(positive_df.user_id.values)\n            user_ids.extend(list(negative_df.user_id.values))\n            res.append(user_ids)\n            break\n            \n        res.append(user_ids)\n        if len(res) >= n:\n            break\n        # The remove the users from df and repeat the process\n        df = df[~df.user_id.isin(user_ids)]\n    return res\n        ","execution_count":null,"outputs":[]},{"metadata":{"papermill":{"duration":0.035194,"end_time":"2020-12-18T08:37:29.478764","exception":false,"start_time":"2020-12-18T08:37:29.44357","status":"completed"},"tags":[]},"cell_type":"markdown","source":"**get_splits** return list of user_ids. All splits classes are equally balanced except for the last one."},{"metadata":{"execution":{"iopub.execute_input":"2020-12-18T08:37:29.553021Z","iopub.status.busy":"2020-12-18T08:37:29.552183Z","iopub.status.idle":"2020-12-18T08:38:35.45261Z","shell.execute_reply":"2020-12-18T08:38:35.453354Z"},"papermill":{"duration":65.939787,"end_time":"2020-12-18T08:38:35.453523","exception":false,"start_time":"2020-12-18T08:37:29.513736","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"%%time\nsplits = get_splits(train_df[['user_id','answered_correctly']],n=10)\n[len(s) for s in splits if s]","execution_count":null,"outputs":[]},{"metadata":{"execution":{"iopub.execute_input":"2020-12-18T08:38:35.533451Z","iopub.status.busy":"2020-12-18T08:38:35.532423Z","iopub.status.idle":"2020-12-18T08:38:41.19441Z","shell.execute_reply":"2020-12-18T08:38:41.195116Z"},"papermill":{"duration":5.704014,"end_time":"2020-12-18T08:38:41.195312","exception":false,"start_time":"2020-12-18T08:38:35.491298","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"# let's look at the totals of first split\ntrain_df.loc[train_df.user_id.isin(splits[0])].groupby('answered_correctly').size()#It can be perfect but here we are","execution_count":null,"outputs":[]},{"metadata":{"execution":{"iopub.execute_input":"2020-12-18T08:38:41.266097Z","iopub.status.busy":"2020-12-18T08:38:41.265252Z","iopub.status.idle":"2020-12-18T08:38:41.422365Z","shell.execute_reply":"2020-12-18T08:38:41.423143Z"},"papermill":{"duration":0.188304,"end_time":"2020-12-18T08:38:41.423338","exception":false,"start_time":"2020-12-18T08:38:41.235034","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"del train_df\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"papermill":{"duration":0.038933,"end_time":"2020-12-18T08:38:41.511329","exception":false,"start_time":"2020-12-18T08:38:41.472396","status":"completed"},"tags":[]},"cell_type":"markdown","source":"## Reduce tags using AutoEncoder"},{"metadata":{"execution":{"iopub.execute_input":"2020-12-18T08:38:41.608073Z","iopub.status.busy":"2020-12-18T08:38:41.607106Z","iopub.status.idle":"2020-12-18T08:38:41.630797Z","shell.execute_reply":"2020-12-18T08:38:41.631405Z"},"papermill":{"duration":0.080816,"end_time":"2020-12-18T08:38:41.631556","exception":false,"start_time":"2020-12-18T08:38:41.55074","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"content_df = pd.read_csv(\"../input/riiid-test-answer-prediction/questions.csv\")\ncontent_df.rename(columns={'question_id': 'content_id'},inplace=True)","execution_count":null,"outputs":[]},{"metadata":{"execution":{"iopub.execute_input":"2020-12-18T08:38:41.708584Z","iopub.status.busy":"2020-12-18T08:38:41.702841Z","iopub.status.idle":"2020-12-18T08:38:41.827114Z","shell.execute_reply":"2020-12-18T08:38:41.827789Z"},"papermill":{"duration":0.168891,"end_time":"2020-12-18T08:38:41.827963","exception":false,"start_time":"2020-12-18T08:38:41.659072","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"def tags_fn(x):\n    if type(x) == str:\n        x=[int(i) for i in x.split()]\n    if type(x) in [int,float]:\n        x=[x,]\n    return sorted([y for y in x if y!=float('nan')])\n# Convert to list\ntags  = list(map(tags_fn,content_df.tags.fillna('0').values))\n# Get the max tag size\nmax_tags = max([len(x) for x in tags if x])\n# Pad Sequences\ntags = tf.keras.preprocessing.sequence.pad_sequences(tags)\n# Normalization\ntags = StandardScaler().fit_transform(tags)\nmax_tags","execution_count":null,"outputs":[]},{"metadata":{"execution":{"iopub.execute_input":"2020-12-18T08:38:41.92098Z","iopub.status.busy":"2020-12-18T08:38:41.920177Z","iopub.status.idle":"2020-12-18T08:38:42.182197Z","shell.execute_reply":"2020-12-18T08:38:42.18275Z"},"papermill":{"duration":0.31353,"end_time":"2020-12-18T08:38:42.182894","exception":false,"start_time":"2020-12-18T08:38:41.869364","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"encoder = keras.models.Sequential(\n            [\n                keras.layers.Dense(max_tags, input_shape=(max_tags,)),\n                keras.layers.Dense(4, activation=\"tanh\"),\n                keras.layers.Dense(2, activation=\"tanh\"),\n                keras.layers.Dense(1, activation=\"tanh\"),\n                \n            ]\n)\ndecoder = keras.models.Sequential(\n            [\n                keras.layers.Dense(1, activation=\"tanh\", input_shape=(1,)),\n                keras.layers.Dense(2, activation=\"tanh\"),\n                keras.layers.Dense(4, activation=\"tanh\"),\n                keras.layers.Dense(max_tags),\n            ]\n)\nautoencoder = keras.models.Sequential(\n            [encoder,decoder]\n) \nautoencoder.compile(loss=keras.losses.mse, \n                    optimizer=keras.optimizers.Adam(lr=0.00001))","execution_count":null,"outputs":[]},{"metadata":{"execution":{"iopub.execute_input":"2020-12-18T08:38:42.240969Z","iopub.status.busy":"2020-12-18T08:38:42.240354Z","iopub.status.idle":"2020-12-18T08:38:42.62406Z","shell.execute_reply":"2020-12-18T08:38:42.623441Z"},"papermill":{"duration":0.413895,"end_time":"2020-12-18T08:38:42.624185","exception":false,"start_time":"2020-12-18T08:38:42.21029","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"# Avoid training the AE every time we commit the notebook\n! cp /kaggle/input/riiid-preprocess-and-balance-the-dataset/autoencoder.h5 ./","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-output":true,"execution":{"iopub.execute_input":"2020-12-18T08:38:42.696263Z","iopub.status.busy":"2020-12-18T08:38:42.695477Z","iopub.status.idle":"2020-12-18T08:38:42.870516Z","shell.execute_reply":"2020-12-18T08:38:42.869814Z"},"papermill":{"duration":0.219269,"end_time":"2020-12-18T08:38:42.870691","exception":false,"start_time":"2020-12-18T08:38:42.651422","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"%%time\nif not os.path.exists('./autoencoder.h5'):\n    autoencoder.fit(tags,tags,\n                    shuffle=True,\n                    batch_size=256,\n                    epochs=30000)\n    autoencoder.save('autoencoder.h5')\nelse:\n    autoencoder = keras.models.load_model('autoencoder.h5')\nencoder = autoencoder.layers[0]\ndecoder = autoencoder.layers[1]\nautoencoder.summary()","execution_count":null,"outputs":[]},{"metadata":{"execution":{"iopub.execute_input":"2020-12-18T08:38:42.961594Z","iopub.status.busy":"2020-12-18T08:38:42.960674Z","iopub.status.idle":"2020-12-18T08:38:43.9024Z","shell.execute_reply":"2020-12-18T08:38:43.901809Z"},"papermill":{"duration":0.990045,"end_time":"2020-12-18T08:38:43.902515","exception":false,"start_time":"2020-12-18T08:38:42.91247","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"autoencoder.evaluate(tags,tags)","execution_count":null,"outputs":[]},{"metadata":{"execution":{"iopub.execute_input":"2020-12-18T08:38:43.973354Z","iopub.status.busy":"2020-12-18T08:38:43.972421Z","iopub.status.idle":"2020-12-18T08:38:43.994143Z","shell.execute_reply":"2020-12-18T08:38:43.993576Z"},"papermill":{"duration":0.059524,"end_time":"2020-12-18T08:38:43.994269","exception":false,"start_time":"2020-12-18T08:38:43.934745","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"# get the tag\ncontent_df['tag'] = encoder(tags).numpy()\ndel content_df['tags']","execution_count":null,"outputs":[]},{"metadata":{"papermill":{"duration":0.032244,"end_time":"2020-12-18T08:38:44.059061","exception":false,"start_time":"2020-12-18T08:38:44.026817","status":"completed"},"tags":[]},"cell_type":"markdown","source":"#### Add some features to content_df"},{"metadata":{"execution":{"iopub.execute_input":"2020-12-18T08:38:44.143437Z","iopub.status.busy":"2020-12-18T08:38:44.133127Z","iopub.status.idle":"2020-12-18T08:38:44.345485Z","shell.execute_reply":"2020-12-18T08:38:44.344918Z"},"papermill":{"duration":0.254166,"end_time":"2020-12-18T08:38:44.345607","exception":false,"start_time":"2020-12-18T08:38:44.091441","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"#bundle_size\ntmp = content_df[['bundle_id','content_id']].groupby('bundle_id').agg({'content_id':'count'})\ntmp.columns = ['bundle_size',]\ncontent_df = content_df.merge(tmp,how='left',on='bundle_id')\n\n#part_size\ntmp = content_df[['part','content_id']].groupby('part').agg({'content_id':'count'})\ntmp.columns = ['part_size',]\ncontent_df = content_df.merge(tmp,how='left',on='part')\n\n#bundle_part_size\ntmp = content_df[['part','bundle_id']].groupby(['part','bundle_id']).tail(1)\ntmp = tmp.groupby(['part',]).agg({'bundle_id':'count'})\ntmp.columns = ['part_bundle_size',]\ncontent_df = content_df.merge(tmp,how='left',on=['part',])\ncontent_df.fillna(0,inplace=True)\ndel tmp\ncontent_df = reduce_mem_usage(content_df)\ngc.collect()","execution_count":null,"outputs":[]},{"metadata":{"papermill":{"duration":0.033982,"end_time":"2020-12-18T08:38:44.414468","exception":false,"start_time":"2020-12-18T08:38:44.380486","status":"completed"},"tags":[]},"cell_type":"markdown","source":"### Utility functions"},{"metadata":{"execution":{"iopub.execute_input":"2020-12-18T08:38:44.489883Z","iopub.status.busy":"2020-12-18T08:38:44.488835Z","iopub.status.idle":"2020-12-18T08:38:44.493442Z","shell.execute_reply":"2020-12-18T08:38:44.492737Z"},"papermill":{"duration":0.044416,"end_time":"2020-12-18T08:38:44.493555","exception":false,"start_time":"2020-12-18T08:38:44.449139","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"# metadata is typically a dictionary that holds the last user's state ","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"execution":{"iopub.execute_input":"2020-12-18T08:38:44.576427Z","iopub.status.busy":"2020-12-18T08:38:44.573387Z","iopub.status.idle":"2020-12-18T08:38:44.578441Z","shell.execute_reply":"2020-12-18T08:38:44.578973Z"},"papermill":{"duration":0.050543,"end_time":"2020-12-18T08:38:44.579114","exception":false,"start_time":"2020-12-18T08:38:44.528571","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"# Add or append rows to a key in the metadata\ndef metadata_append(metadata,df,key):\n    if type(key) == str:\n        key = [key,]\n    key_h = tuple(key)\n        \n    if all([k in df.columns for k in key]):\n        df.set_index(key,inplace=True)\n    if key_h not in metadata:\n        metadata[key_h] = df\n    else:\n        metadata[key_h] = metadata[key_h].append(df)\n\n# Add or append rows to a key in the metadata. \n# The difference with the previous one is that if the row already exists, \n# it is updated by the addition operation\ndef metadata_add_append(metadata,df,key):\n    if type(key) == str:\n        key = [key,]\n    key_h = tuple(key)\n    if all([k in df.columns for k in key]):\n        df.set_index(key,inplace=True)\n        \n    if key_h not in metadata:\n        metadata[key_h] = df\n    else:\n        metadata[key_h] = metadata[key_h].add(df,fill_value=0)","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-input":true,"execution":{"iopub.execute_input":"2020-12-18T08:38:44.651204Z","iopub.status.busy":"2020-12-18T08:38:44.650538Z","iopub.status.idle":"2020-12-18T08:38:44.673947Z","shell.execute_reply":"2020-12-18T08:38:44.674556Z"},"papermill":{"duration":0.062255,"end_time":"2020-12-18T08:38:44.674731","exception":false,"start_time":"2020-12-18T08:38:44.612476","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"# This function will add some rolling features to df\ndef user_roll_features(df,cols):\n    metadata = {}\n    for col in cols:\n        # Cols to build\n        c1 = f'{col}_roll_count'\n        c2 = f'{col}_roll_sum'\n        \n        tmp_df = df[[col,'user_id','answered_correctly','row_id']]\n        grp = tmp_df.groupby([col,'user_id'])\n        tmp_df[c1] = grp.answered_correctly.cumcount()+1\n        tmp_df[c2] = grp.answered_correctly.cumsum()\n        \n        del tmp_df['answered_correctly']\n        \n        win_cols = []\n        # In windows\n        for win in [50,10]:\n            sum_col = f'{col}_win{win}_sum'\n            count_col = f'{col}_win{win}_count'\n            win_id = f'win{win}_id'\n            win_cols.extend([sum_col,count_col])\n            \n            tmp_df[sum_col] = tmp_df[c2]\n            tmp_df[count_col] = tmp_df[c1]\n            tmp_df[win_id] = tmp_df[count_col] // win\n            \n            # pred_id\n            tmp = tmp_df[['user_id',col]]\n            tmp[win_id]  = (tmp_df[win_id]-1).values\n            tmp = tmp.merge(tmp_df.groupby(['user_id',col,win_id]).tail(1),how='left',on=[win_id,'user_id',col])\n            tmp.fillna(0,inplace=True)\n            \n            tmp_df[sum_col] = tmp_df[sum_col].values - tmp[sum_col].values\n            tmp_df[count_col] = tmp_df[count_col].values - tmp[count_col].values\n        \n        #save metadata \n        grp = tmp_df.groupby(['user_id',col]).tail(1)\n        metadata[('user_id',col)] = grp.set_index(['user_id',col])[[c1,c2,*win_cols]]\n        \n        # Now we can shift generated cols to prevent leaking some information\n        tmp_df['task_container_id'] = df.task_container_id.values\n        tmp = tmp_df.groupby(['user_id',col,'task_container_id']).tail(1)\n        for c in [*win_cols,c1,c2]:\n            tmp[c] = tmp[[col,c]].groupby([col])[c].shift(1,fill_value=0)\n        # Set the first state of each user to 0\n        row_id = tmp.groupby(['user_id',col]).head(1).row_id.values\n        tmp.loc[tmp.row_id.isin(row_id),[*win_cols,c1,c2]] = 0\n        \n        #add to df\n        k = [col,'user_id','task_container_id']\n        tmp_df = tmp_df[k].merge(tmp,how='left',on=k)\n        for c in [*win_cols,c1,c2]:\n            df[c] = tmp_df[c].values\n    return df,metadata","execution_count":null,"outputs":[]},{"metadata":{"execution":{"iopub.execute_input":"2020-12-18T08:38:44.746096Z","iopub.status.busy":"2020-12-18T08:38:44.745444Z","iopub.status.idle":"2020-12-18T08:38:44.780286Z","shell.execute_reply":"2020-12-18T08:38:44.780863Z"},"papermill":{"duration":0.072466,"end_time":"2020-12-18T08:38:44.781011","exception":false,"start_time":"2020-12-18T08:38:44.708545","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"def process(data,metadata={}):\n    global content_df\n\n    # Merge content file \n    data = data.merge(content_df,how='left',on=['content_id',])\n    \n    ### Build User Specific Statistics\n    \n    # User performances over time\n    tmp_df = data[['user_id','answered_correctly','task_container_id']]\n    grp = tmp_df.groupby(['user_id'])\n    tmp_df['user_id_roll_sum'] = grp['answered_correctly'].cumsum().values\n    tmp_df['user_id_roll_count'] = (grp['answered_correctly'].cumcount()+1).values\n    # also check user's performance in small windows\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        tmp_df[sum_col] = grp['answered_correctly'].rolling(win,min_periods=1).sum().values\n        tmp_df[count_col] = grp['answered_correctly'].rolling(win,min_periods=1).count().values\n    del tmp_df['answered_correctly']\n    \n    #We only need the last container's state\n    tmp_df = tmp_df.groupby(['user_id','task_container_id']).tail(1)\n    grp = tmp_df.groupby(['user_id',])\n    # shift to avoid leakage\n    tmp_df['user_id_roll_count'] = grp.user_id_roll_count.shift(1,fill_value=0)\n    tmp_df['user_id_roll_sum'] = grp.user_id_roll_sum.shift(1,fill_value=0)\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        tmp_df[sum_col] = grp[sum_col].shift(1,fill_value=0)\n        tmp_df[count_col] = grp[count_col].shift(1,fill_value=0)\n    del grp\n    # merge with data\n    data = data.merge(tmp_df,how='left',on=['user_id','task_container_id'])\n    \n    # Track last state\n    user_metadata_df = tmp_df.groupby(['user_id']).tail(1)\n    del user_metadata_df['task_container_id']\n    \n    # Shift these columns to prevent leakage\n    tmp_df = data[['user_answer','answered_correctly','user_id','timestamp']] .groupby('user_id').shift(1).fillna(-1)\n    for col in tmp_df.columns:\n        data[f'prev_{col}'] = tmp_df[col].values\n    tmp_df = data[['user_id',*[f'prev_{col}' for col in tmp_df.columns]]].groupby('user_id').tail(1)\n    user_metadata_df = user_metadata_df.merge(tmp_df,how='left',on=['user_id',])\n    metadata_append(metadata,user_metadata_df,'user_id')# save last state\n    del user_metadata_df\n    \n    #Compute the duration between last two prior_question_elapsed_time\n    tmp_df = data[['user_id','timestamp']]\n    data['prior_duration'] = tmp_df.timestamp.values-tmp_df.groupby('user_id').timestamp.shift(1,fill_value=0).values\n    \n    \n    # Add some rolling features\n    cols = ['content_id','bundle_id']\n    data,metadata_tmp = user_roll_features(data,cols)\n    # Update metadata \n    for key in metadata_tmp:\n        tmp = metadata_tmp[key]\n        if key == ('user_id','content_id'):\n            # It is very likely that the user answers well a question which he had already been answered correctly\n            #The prev user's answer in the same content.\n            tmp_df = data[['content_id','user_id','answered_correctly','row_id',]]\n            grp =  tmp_df.groupby(['content_id'])\n            tmp_df['prev_content_answered_correctly'] = grp.answered_correctly.shift(1).fillna(0)\n            # The first state is unknow so we set it to -1\n            row_id = tmp_df.groupby(['content_id','user_id',]).head(1).row_id.values\n            tmp_df.loc[tmp_df.row_id.isin(row_id),'prev_content_answered_correctly'] = -1\n            data['prev_content_answered_correctly'] = tmp_df.prev_content_answered_correctly.values\n            # metadata: We will get the last answer the user gave to content_id\n            tmp_df = tmp_df[['content_id','user_id','prev_content_answered_correctly',]]\n            tmp_df = tmp_df.groupby(['content_id','user_id',]).tail(1)\n            tmp_df.set_index(['user_id','content_id'],inplace=True)\n            tmp = tmp.merge(tmp_df,how='left',on=['user_id','content_id'])\n            del grp,tmp_df\n        metadata_append(metadata,tmp,key)\n    \n    print(\"Global statistics\")\n    # Global statistics\n    # content\n    tmp_df = data[['content_id','answered_correctly']].groupby(['content_id'])\n    tmp_df = tmp_df.agg({'answered_correctly': ['count','sum']})\n    tmp_df.columns = ['content_count','content_sum']\n    metadata_add_append(metadata,tmp_df,'content_id')\n\n    # bundle \n    tmp_df = data[['bundle_id','answered_correctly']]\n    tmp_df = tmp_df.groupby(['bundle_id']).agg({'answered_correctly':['count','sum']})\n    tmp_df.columns = ['bundle_count','bundle_sum']\n    metadata_add_append(metadata,tmp_df,'bundle_id')\n        \n    #no need content_type_id\n    del data['content_type_id']\n    data.fillna(0,inplace=True)\n    return data,metadata","execution_count":null,"outputs":[]},{"metadata":{"papermill":{"duration":0.034185,"end_time":"2020-12-18T08:38:44.849419","exception":false,"start_time":"2020-12-18T08:38:44.815234","status":"completed"},"tags":[]},"cell_type":"markdown","source":"## Process the training dataset"},{"metadata":{"execution":{"iopub.execute_input":"2020-12-18T08:38:44.921006Z","iopub.status.busy":"2020-12-18T08:38:44.920377Z","iopub.status.idle":"2020-12-18T08:38:44.948793Z","shell.execute_reply":"2020-12-18T08:38:44.94929Z"},"papermill":{"duration":0.066128,"end_time":"2020-12-18T08:38:44.949441","exception":false,"start_time":"2020-12-18T08:38:44.883313","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"# Ignore the last split\nsplits = splits[:-1]","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-output":true,"execution":{"iopub.execute_input":"2020-12-18T08:38:45.027003Z","iopub.status.busy":"2020-12-18T08:38:45.026123Z","iopub.status.idle":"2020-12-18T09:06:05.842753Z","shell.execute_reply":"2020-12-18T09:06:05.841963Z"},"papermill":{"duration":1640.859206,"end_time":"2020-12-18T09:06:05.842951","exception":false,"start_time":"2020-12-18T08:38:44.983745","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"%%time\nmetadata = {}\nfor i,ids in enumerate(splits):\n    print(f\"Split--> {i+1}\")\n    \n    df = pd.read_feather(path_train,)\n    # exclude lectures\n    df = df[df.answered_correctly!=-1]\n    df.prior_question_had_explanation.fillna(False,inplace=True)\n    df.replace([np.inf, -np.inf], 999999999,inplace=True)\n\n    data = df.loc[df.user_id.isin(ids)]\n    assert data.shape[0]\n    del df\n    gc.collect()\n    \n    print(f\"Processing {i+1} | Shape {data.shape}\")\n    data,metadata = process(data,metadata)\n    print(f\"Metadata {i+1}\")\n    for key in metadata:\n        metadata[key].fillna(0,inplace=True)\n        metadata[key] =  reduce_mem_usage(metadata[key],verbose=False)\n    data = reduce_mem_usage(data)\n    data.prior_question_had_explanation  = data.prior_question_had_explanation.values.astype(np.int8)  \n    data.to_feather(f'train{i}.feather')\n    del data\n    gc.collect()\ndel splits","execution_count":null,"outputs":[]},{"metadata":{"papermill":{"duration":0.056441,"end_time":"2020-12-18T09:06:05.956589","exception":false,"start_time":"2020-12-18T09:06:05.900148","status":"completed"},"tags":[]},"cell_type":"markdown","source":"## Collect metadata of remaining users"},{"metadata":{"execution":{"iopub.execute_input":"2020-12-18T09:06:06.081157Z","iopub.status.busy":"2020-12-18T09:06:06.080343Z","iopub.status.idle":"2020-12-18T09:07:03.290273Z","shell.execute_reply":"2020-12-18T09:07:03.290869Z"},"papermill":{"duration":57.277542,"end_time":"2020-12-18T09:07:03.291014","exception":false,"start_time":"2020-12-18T09:06:06.013472","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"%%time\ndf = pd.read_feather(path_train,)\n# exclude lectures\ndf = df[df.answered_correctly!=-1]\ndf = df.loc[~df.user_id.isin(metadata[('user_id',)].index.values)]\ndf.prior_question_had_explanation.fillna(False,inplace=True)\ndf.replace([np.inf, -np.inf], 999999,inplace=True)\n\nuser_ids = df['user_id'].unique()","execution_count":null,"outputs":[]},{"metadata":{"_kg_hide-output":true,"execution":{"iopub.execute_input":"2020-12-18T09:07:03.412691Z","iopub.status.busy":"2020-12-18T09:07:03.411784Z","iopub.status.idle":"2020-12-18T10:39:13.752226Z","shell.execute_reply":"2020-12-18T10:39:13.75109Z"},"papermill":{"duration":5530.404958,"end_time":"2020-12-18T10:39:13.752352","exception":false,"start_time":"2020-12-18T09:07:03.347394","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"%%time\nstep = 10000\nfor i in range(0,user_ids.shape[0],step):\n    users = user_ids[i:min(i+step,user_ids.shape[0])]\n    selector = df.user_id.isin(users)\n    data = df.loc[selector]\n    df = df.loc[~selector]\n    gc.collect()\n    \n    print(f\"Processing {i}/{user_ids.shape[0]} | Shape {data.shape}\")\n    _,metadata = process(data,metadata,)\n    print(f\"Metadata {i}/{user_ids.shape[0]}\")\n    for key in metadata:\n        metadata[key].fillna(0,inplace=True)\n        metadata[key] =  reduce_mem_usage(metadata[key],verbose=False)\n    del data\n    gc.collect()    ","execution_count":null,"outputs":[]},{"metadata":{"papermill":{"duration":0.29335,"end_time":"2020-12-18T10:39:14.341959","exception":false,"start_time":"2020-12-18T10:39:14.048609","status":"completed"},"tags":[]},"cell_type":"markdown","source":"We will save the metadata and perform the migration to SQLite DB in : https://www.kaggle.com/tchaye59/riiid-metadata-to-sqlite\n\n\nTo see how I train and submit my model you can check: https://www.kaggle.com/tchaye59/riiid-work-with-the-full-state-using-sqlalchemy"},{"metadata":{"execution":{"iopub.execute_input":"2020-12-18T10:39:14.949591Z","iopub.status.busy":"2020-12-18T10:39:14.946992Z","iopub.status.idle":"2020-12-18T10:39:18.993134Z","shell.execute_reply":"2020-12-18T10:39:18.991229Z"},"papermill":{"duration":4.352508,"end_time":"2020-12-18T10:39:18.993347","exception":false,"start_time":"2020-12-18T10:39:14.640839","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"with open('metadata.dill', 'wb') as file:\n    dill.dump(metadata, file)\ncontent_df.to_feather('content.feather')","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}