{"cells":[{"metadata":{},"cell_type":"markdown","source":"# Thank you everyone!\nThis notebook is 62nd solution of this competition.\nI used single light GBM and got following score.\n* CV score 0.801\n* Public LB score 0.799\n* Private LB score 0.801\n\nBlending with catboost and SAKT did not work well for me.\n\nThroughout this competition, I learned a lot from many notebook and comments of other kagglers.\nI appreciate all these people and want to add them in this notebook. Correct me if I missed someone.\n\nDue to the luck of my programming skill, the code is very dirty. \nAny questions are wellcome.\n\n# The list of abbreviations in feature names\n- acc: answered_correctly\n- exp: prior_question_had_explanation\n- time: prior_question_elapsed_time\n- count: number of trial\n- count_lecture: number of lecture\n- tfl: timestamp from last nth trial to that from n+1th trial\n- dif: difficulty (1 - content-wise accuracy)\n- dpt: difficulty point (add difficulty if users answer was correct)\n- avg: average\n- std: standard deviation\n- sum: sum\n- hist: historical average with temporal decay of gamma 0.75 \n- hist2: historical average with temporal decay of gamma 0.25\n- u: user-wise\n- t: tag-wise\n- tg: taggroup-wise\n- p: part-wise\n- c: content-wise\n- b: bundle-wise\n- uc: user-content-wise\n- ub: user-bundle-wise\n- up: user-part-wise\n- time_per_count_u: timestamp/count_u\n- count_u_nondiagnostic: user-wise count of non-diagnostic questions\n- exp_avg_u_corrected: user-wise average of prior_question_had_explanation corrected for diagnostic questions. Th user cannot read explanation for diagnostic questions.\n- pp_ratio: ratio of paid part (part1, 3, 4, 6, 7) trials per all trials, only paid users of SANTA app can solve them if it is not diagnostic questions\n- listening_ratio: ratio of listening part trials per all trials\n- relative_dif: relative difficulty of questions calculated with acc_avg_c / dif_avg_u\n- notacc_sum: sum of failed trials\n- wp_ts: trueskill feature of win probability\n- umu_ts, usigma_ts, qmu_ts, qsigma_ts: trueskill features of mu, and sigma of user and content"},{"metadata":{"execution":{"iopub.execute_input":"2020-10-12T18:21:01.107461Z","iopub.status.busy":"2020-10-12T18:21:01.1067Z","iopub.status.idle":"2020-10-12T18:21:02.19541Z","shell.execute_reply":"2020-10-12T18:21:02.194464Z"},"lines_to_next_cell":2,"papermill":{"duration":1.131405,"end_time":"2020-10-12T18:21:02.195556","exception":false,"start_time":"2020-10-12T18:21:01.064151","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"import pandas as pd\nimport numpy as np\nfrom sklearn.metrics import roc_auc_score\nimport gc\nfrom collections import defaultdict\nfrom tqdm.notebook import tqdm\nimport lightgbm as lgb\nimport time\nimport pickle\nimport seaborn as sns\nimport dill\nimport re\nimport sqlite3\nfrom pathlib import Path\nfrom contextlib import contextmanager\nfrom trueskill import Rating, quality_1vs1, rate_1vs1\nimport trueskill\nimport math\nfrom sklearn.preprocessing import LabelEncoder\nfrom pandas.api.types import is_datetime64_any_dtype as is_datetime\nfrom pandas.api.types import is_categorical_dtype\npd.set_option('display.max_columns', 100)\npd.set_option('display.max_rows', 100)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"pd.set_option('display.max_columns', 100)\npd.set_option('display.max_rows', 100)\n\ndef pickle_dump_dill(obj, path):\n    with open(path, mode='wb') as f:\n        dill.dump(obj,f)\n\ndef pickle_load_dill(path):\n    with open(path, mode='rb') as f:\n        data = dill.load(f)\n        return data  \n\ndef pickle_dump(obj, path):\n    with open(path, mode='wb') as f:\n        pickle.dump(obj,f)\n\ndef pickle_load(path):\n    with open(path, mode='rb') as f:\n        data = pickle.load(f)\n        return data\n\n# Original code from https://www.kaggle.com/gemartin/load-data-reduce-memory-usage by @gemartin\n# Modified to support timestamp type, categorical type\n# Modified to add option to use float16 or not. feather format does not support float16.\n\ndef reduce_mem_usage(df, use_float16=False):\n    \"\"\" iterate through all the columns of a dataframe and modify the data type\n        to reduce memory usage.        \n    \"\"\"\n    start_mem = df.memory_usage().sum() / 1024**2\n    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            # skip datetime type or categorical type\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    print('Memory usage after optimization is: {:.2f} MB'.format(end_mem))\n    print('Decreased by {:.1f}%'.format(100 * (start_mem - end_mem) / start_mem))\n\n    return df","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Import data\nCV files are from [this great notebook](https://www.kaggle.com/its7171/cv-strategy) by @tito"},{"metadata":{"trusted":true},"cell_type":"code","source":"train_pickle = '../input/riiid-cross-validation-files/cv1_train.pickle'\nvalid_pickle = '../input/riiid-cross-validation-files/cv1_valid.pickle'\n\nfeld_needed = ['timestamp','user_id', 'content_id', 'content_type_id', 'answered_correctly', 'prior_question_elapsed_time', 'prior_question_had_explanation']\n\n#%%\ntrain = pd.read_pickle(train_pickle)[feld_needed]\nvalid = pd.read_pickle(valid_pickle)[feld_needed]","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"I trained my model on my local PC. In the Kaggle notebook, I reduced datasize for presentation."},{"metadata":{"trusted":true},"cell_type":"code","source":"kaggle=True\nif kaggle:\n    train=train[:10000]\n    valid=valid[:10000]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train.timestamp=(train.timestamp/1000).astype('int32')\nvalid.timestamp=(valid.timestamp/1000).astype('int32')\n\ntrain = reduce_mem_usage(train, use_float16=False)\nvalid = reduce_mem_usage(valid, use_float16=False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Add prior content_id, bundle_id, part, and taggroups\nprior_question_had_explanation and prior_question_elapsed_time are associated with prior bundles, not current ones. So, I add these features at first."},{"metadata":{"trusted":true},"cell_type":"code","source":"def add_prior_b(df, questions_cb, questions_bpt, prior_b_dict, verbose=True):\n    df = df.merge(questions_cb, how = 'left', on = 'content_id')\n    df['bundle_id'] = (df['bundle_id'].fillna(99999)).astype('int32')\n    prior_b = np.zeros(len(df), dtype=np.int32)\n    for cnt,row in (enumerate(tqdm(df[['user_id','content_type_id','bundle_id']].values)) if verbose else enumerate(df[['user_id','content_type_id','bundle_id']].values)):\n        user_id=int(row[0])\n        content_type_id=int(row[1])\n        bundle_id=int(row[2])\n        if prior_b_dict[user_id][0] != bundle_id:\n            prior_b[cnt] = prior_b_dict[user_id][0]\n            if (content_type_id == 0):\n                prior_b_dict[user_id][1] = prior_b_dict[user_id][0]\n                prior_b_dict[user_id][0] = bundle_id\n        else:\n            prior_b[cnt] = prior_b_dict[user_id][1]\n    df['prior_bundle']=prior_b\n    df = df.merge(questions_bpt, how = 'left', on = 'prior_bundle')\n    df['prior_bundle'] = df['prior_bundle'].fillna(99999)\n    df['prior_part'] = (df['prior_part'].fillna(9)).astype('int8')\n    df['prior_taggroup'] = (df['prior_taggroup'].fillna(99)).astype('int16')\n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"questions = pd.read_csv('../input/riid-preprocessed/questions_with_taggroup.csv')\nquestions_cb=questions[['question_id', 'bundle_id']].copy()\nquestions_cb = reduce_mem_usage(questions_cb, use_float16=False)\nquestions_cb.columns=['content_id', 'bundle_id']\nquestions_bpt=questions[['bundle_id', 'part', 'taggroup']].copy()\nquestions_bpt = reduce_mem_usage(questions_bpt, use_float16=False)\nquestions_bpt.columns=['prior_bundle', 'prior_part', 'prior_taggroup']\nquestions_bpt.drop_duplicates(subset='prior_bundle', keep='first', inplace=True)\nprior_b_dict = defaultdict(lambda:defaultdict(lambda:99999))\ntrain = add_prior_b(train, questions_cb, questions_bpt, prior_b_dict)\nvalid = add_prior_b(valid, questions_cb, questions_bpt, prior_b_dict)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Create dataframe to add content-wise, part-wise, tag-wise, and taggroup-wise features.\nI created dataframe to add content-wise, part-wise, tag-wise, and taggroup-wise features. Taggroup is from [this notebook](https://www.kaggle.com/radema/riiid-just-another-eda-tag-clustering-wip) by radema. I modified his notebook to increase group number (to 23) and add tag 188 to content_id 10033."},{"metadata":{"trusted":true},"cell_type":"code","source":"create_content_df=False\n\nif create_content_df:\n    content_df = train[train.content_type_id==0].groupby('content_id').\\\n                    agg({'answered_correctly': ['mean','sum', 'count', 'std']}).reset_index()\n    content_df.columns = ['content_id','acc_avg_c','acc_sum_c', 'count_c', 'acc_std_c']\n\n    bundle_df = train[train.content_type_id==0].groupby('prior_bundle').\\\n                    agg({'prior_question_had_explanation': ['mean','sum', 'count', 'std'],\n                         'prior_question_elapsed_time': ['mean','sum', 'count', 'std']}).reset_index()\n    bundle_df.columns = ['bundle_id','exp_avg_b', 'exp_sum_b', 'exp_count_b','exp_std_b','time_avg_b',  'time_sum_b', 'time_count_b','time_std_b']\n\n    questions.columns=['content_id', 'bundle_id', 'correct_answer', 'part', 'tags', 'taggroup']\n\n    content_df=content_df.merge(questions, how = 'left', on = 'content_id')\n    content_df=content_df.merge(bundle_df, how = 'left', on = 'bundle_id')\n\n    # sns.pairplot(content_df[['acc_avg_c','exp_avg_b','time_avg_b']])\n    # content_df[['acc_avg_c','exp_avg_b','time_avg_b']].corr()\n\n    #content_df.loc[content_df[\"tags\"].isnull(),\"tags\"]='188'\n    content_df.loc[10033,'tags']='188'\n    inv_rows = content_df[content_df['tags'].apply(type) == float].index\n    content_df.at[inv_rows, 'tags'] = ''\n    # split tag string into list of ints\n    content_df['tags'] = content_df['tags'].apply(lambda x: np.array(x.split()).astype(int))\n\n    tag_df=pd.DataFrame()\n    tag_df['tags']=range(189)\n    tag_columns=['acc_sum_c', 'count_c', 'exp_sum_b', 'exp_count_b','time_sum_b', 'time_count_b']\n    tag_df[tag_columns]=0\n    for i in range(len(content_df)):\n        tags=content_df.loc[i,'tags']\n        tag_df.loc[tags,tag_columns]=tag_df.loc[tags,tag_columns]+content_df.loc[i,tag_columns]\n    tag_df=tag_df.astype(int)\n    tag_df['acc_avg_t']=tag_df['acc_sum_c']/tag_df['count_c']\n    tag_df['exp_avg_t']=tag_df['exp_sum_b']/tag_df['exp_count_b']\n    tag_df['time_avg_t']=tag_df['time_sum_b']/tag_df['time_count_b']\n\n    #content_df[['acc_avg_t2', 'exp_avg_t2', 'time_avg_t2']]=0\n    for i in range(len(content_df)):\n        tags=content_df.loc[i,'tags']\n        content_df.loc[i,'acc_avg_t']=tag_df.loc[tags].mean()['acc_avg_t']\n        content_df.loc[i,'exp_avg_t']=tag_df.loc[tags].mean()['exp_avg_t']\n        content_df.loc[i,'time_avg_t']=tag_df.loc[tags].mean()['time_avg_t']\n        #content_df.loc[i,'acc_avg_t2']=tag_df.loc[tags].sum()['acc_sum_c']/tag_df.loc[tags].sum()['count_c']\n        #content_df.loc[i,'exp_avg_t2']=tag_df.loc[tags].sum()['exp_sum_b']/tag_df.loc[tags].sum()['exp_count_b']\n        #content_df.loc[i,'time_avg_t2']=tag_df.loc[tags].sum()['time_sum_b']/tag_df.loc[tags].sum()['time_count_b']\n\n    content_df['type_of']=4\n    content_df['col_for_merge']=(content_df['content_id'].astype('int32')*10).astype('int32')\n\n    lectures = pd.read_csv('/media/hama/ssd2/inubushi/kaggle/riid/riiid-test-answer-prediction/lectures.csv')\n\n    le = LabelEncoder()\n    le.fit(lectures['type_of'])\n    lectures['type_of'] = le.transform(lectures['type_of'])\n    lectures['col_for_merge']=(lectures['lecture_id']*10+1).astype('int32')\n\n    content_df=lectures.append(content_df)\n\n    # --- part-wise parameter\n    train = train.merge(questions[['content_id', 'part']], how = 'left', on = 'content_id')\n    part_df = train[train.content_type_id==0].groupby('part').\\\n                        agg({'answered_correctly': ['mean','sum', 'count', 'std']}).reset_index()\n    part_df.columns = ['part','acc_avg_p','acc_sum_p','count_p','acc_std_p']\n    part_df2 = train[train.content_type_id==0].groupby('prior_part').\\\n                        agg({'prior_question_had_explanation': ['mean','sum', 'count', 'std'],\n                             'prior_question_elapsed_time': ['mean','sum', 'count', 'std']}).reset_index()\n    part_df2.columns = ['part', 'exp_avg_p','exp_sum_p','exp_count_p','exp_std_p', 'time_avg_p','time_sum_p','time_count_p','time_std_p']\n    part_df=part_df.merge(part_df2, how = 'left', on = 'part')\n    train=train.drop('part', axis=1)\n    content_df = content_df.merge(part_df, how = 'left', on = 'part')\n\n    # --- taggroup-wise parameter\n    train = train.merge(questions[['content_id', 'taggroup']], how = 'left', on = 'content_id')\n    taggroup_df = train[train.content_type_id==0].groupby('taggroup').\\\n                        agg({'answered_correctly': ['mean','sum', 'count', 'std']}).reset_index()\n    taggroup_df.columns = ['taggroup','acc_avg_tg','acc_sum_tg','count_tg','acc_std_tg']\n    taggroup_df2 = train[train.content_type_id==0].groupby('prior_taggroup').\\\n                        agg({'prior_question_had_explanation': ['mean','sum', 'count', 'std'],\n                             'prior_question_elapsed_time': ['mean','sum', 'count', 'std']}).reset_index()\n    taggroup_df2.columns = ['taggroup', 'exp_avg_tg','exp_sum_tg','exp_count_tg','exp_std_tg', 'time_avg_tg','time_sum_tg','time_count_tg','time_std_tg']\n    taggroup_df=taggroup_df.merge(taggroup_df2, how = 'left', on = 'taggroup')\n    train=train.drop('taggroup', axis=1)\n    content_df = content_df.merge(taggroup_df, how = 'left', on = 'taggroup')\n\n    content_df = content_df.fillna(0)\n\n    content_df.to_csv('content_df.csv', index=False)\nelse:\n    content_df = pd.read_csv('../input/riid-preprocessed/content_df_all.csv')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"content_df=content_df[['col_for_merge', 'part', 'taggroup', 'acc_avg_t', 'exp_avg_t', 'time_avg_t',\n                       'acc_avg_p','acc_sum_p','count_p','acc_std_p',\n                       'exp_avg_p','exp_sum_p','exp_std_p', \n                       'time_avg_p','time_sum_p','time_std_p',\n                       'acc_avg_c','acc_sum_c', 'count_c','acc_std_c',\n                       'exp_avg_b', 'exp_sum_b', 'exp_std_b',\n                       'time_avg_b','time_sum_b','time_std_b',\n                       'acc_avg_tg','acc_sum_tg','count_tg','acc_std_tg',\n                       'exp_avg_tg','exp_sum_tg','exp_std_tg', \n                       'time_avg_tg','time_sum_tg','time_std_tg']]\n\ntrain['col_for_merge']=(train['content_id'].astype('int32')*10+train['content_type_id']).astype('int32')\nvalid['col_for_merge']=(valid['content_id'].astype('int32')*10+valid['content_type_id']).astype('int32')\ntrain = train.merge(content_df, how = 'left', on = 'col_for_merge')\nvalid = valid.merge(content_df, how = 'left', on = 'col_for_merge')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train['prior_question_elapsed_time'] = train['prior_question_elapsed_time'].fillna(25439.41)\ntrain['prior_question_had_explanation'] = train['prior_question_had_explanation'].fillna(False).astype('int8')\nvalid['prior_question_elapsed_time'] = valid['prior_question_elapsed_time'].fillna(25439.41)\nvalid['prior_question_had_explanation'] = valid['prior_question_had_explanation'].fillna(False).astype('int8')\n\ntrain = reduce_mem_usage(train, use_float16=False)\nvalid = reduce_mem_usage(valid, use_float16=False)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Add user-wise, user-content-wise, user-bundle-wise, and user-part-wise features with loop feature engineering\nThe code for adding these features are from [another great notebook](https://www.kaggle.com/its7171/lgbm-with-loop-feature-engineering) of @its7171."},{"metadata":{"trusted":true},"cell_type":"code","source":"GAMMA=0.75    \nGAMMA2=0.25  \ndef update_std(new_value, old_std, old_sum, old_count):\n    if old_count > 0:\n        old_avg = old_sum/old_count\n    else:\n        old_avg = 0\n    new_avg = (old_sum+new_value)/(old_count+1)\n    new_std = ((old_count*(old_std**2+old_avg**2)+new_value**2)/(old_count+1)-new_avg**2)**(1/2)\n    return new_std\n\ndef add_feats(df, acc_sum_u_dict, acc_std_u_dict, acc_hist_u_dict, acc_hist2_u_dict, \n                    exp_sum_u_dict, exp_std_u_dict, exp_hist_u_dict, exp_hist2_u_dict, \n                    time_sum_u_dict, time_std_u_dict, time_hist_u_dict, time_hist2_u_dict, \n                    dpt_sum_u_dict, dpt_std_u_dict, dpt_hist_u_dict, dpt_hist2_u_dict,\n                    dif_sum_u_dict, dif_std_u_dict, dif_hist_u_dict, dif_hist2_u_dict,\n                    count_u_dict, count_lecture_u_dict, \n                    last_time_u_dict, last_time2_u_dict, last_time3_u_dict,\n                    last_time4_u_dict, last_time5_u_dict, last_time6_u_dict,\n                    tfl_std_u_dict, tfl_hist_u_dict, tfl_hist2_u_dict, \n                    acc_sum_c_dict, exp_sum_b_dict, time_sum_b_dict,\n                    acc_std_c_dict, exp_std_b_dict, time_std_b_dict,\n                    count_c_dict, count_b_dict,\n                    acc_sum_up_dict, acc_hist_up_dict, acc_hist2_up_dict, \n                    count_up_dict, count_lecture_up_dict, last_time_up_dict, last_time2_up_dict,\n                    tfl_hist_up_dict,tfl_hist2_up_dict,\n                    exp_sum_up_dict, exp_hist_up_dict, exp_hist2_up_dict, \n                    time_sum_up_dict, time_hist_up_dict, time_hist2_up_dict, \n                    count_upp_dict, \n                    acc_sum_uc_dict, acc_hist_uc_dict, acc_hist2_uc_dict,  \n                    count_uc_dict, count_lecture_uc_dict, last_time_uc_dict, last_time2_uc_dict,\n                    tfl_hist_uc_dict, tfl_hist2_uc_dict,\n                    exp_sum_ub_dict, exp_hist_ub_dict, exp_hist2_ub_dict,\n                    time_sum_ub_dict, time_hist_ub_dict, time_hist2_ub_dict, \n                    count_ub_dict):           \n     acsu = np.zeros(len(df), dtype=np.int16)\n     acstdu = np.zeros(len(df), dtype=np.float32)   \n     achu = np.zeros(len(df), dtype=np.float32)\n     achu2 = np.zeros(len(df), dtype=np.float32)     \n     exsu = np.zeros(len(df), dtype=np.int16)\n     exstdu = np.zeros(len(df), dtype=np.float32)  \n     exhu = np.zeros(len(df), dtype=np.float32)\n     exhu2 = np.zeros(len(df), dtype=np.float32)\n     tisu = np.zeros(len(df), dtype=np.int32)\n     tistdu = np.zeros(len(df), dtype=np.float32)  \n     tihu = np.zeros(len(df), dtype=np.float32)\n     tihu2 = np.zeros(len(df), dtype=np.float32)\n     dpsu = np.zeros(len(df), dtype=np.float32)\n     dpstdu = np.zeros(len(df), dtype=np.float32)\n     dphu = np.zeros(len(df), dtype=np.float32)\n     dphu2 = np.zeros(len(df), dtype=np.float32)    \n     disu = np.zeros(len(df), dtype=np.float32)\n     distdu = np.zeros(len(df), dtype=np.float32)  \n     dihu = np.zeros(len(df), dtype=np.float32)\n     dihu2 = np.zeros(len(df), dtype=np.float32)  \n     cu = np.zeros(len(df), dtype=np.int16)\n     clu = np.zeros(len(df), dtype=np.int16)\n     tflu = np.zeros(len(df), dtype=np.int32)\n     tflu2 = np.zeros(len(df), dtype=np.int32)\n     tflu3 = np.zeros(len(df), dtype=np.int32)\n     tflu4 = np.zeros(len(df), dtype=np.int32)\n     tflu5 = np.zeros(len(df), dtype=np.int32)\n     tflstdu = np.zeros(len(df), dtype=np.float32)  \n     tflhu = np.zeros(len(df), dtype=np.int32)\n     tflhu2 = np.zeros(len(df), dtype=np.int32)\n     \n     acsc = np.zeros(len(df), dtype=np.int32)\n     acstdc = np.zeros(len(df), dtype=np.float32)\n     exsb = np.zeros(len(df), dtype=np.int32)\n     exstdb = np.zeros(len(df), dtype=np.float32)\n     tisb = np.zeros(len(df), dtype=np.int32)\n     tistdb = np.zeros(len(df), dtype=np.float32)\n     cc = np.zeros(len(df), dtype=np.int32)\n     cb = np.zeros(len(df), dtype=np.int32)   \n     \n     acsup = np.zeros(len(df), dtype=np.int16)\n     achup = np.zeros(len(df), dtype=np.float32)\n     achup2 = np.zeros(len(df), dtype=np.float32)\n     cup = np.zeros(len(df), dtype=np.int16)\n     clup = np.zeros(len(df), dtype=np.int16)\n     tflup = np.zeros(len(df), dtype=np.int32)    \n     tflhup = np.zeros(len(df), dtype=np.int32)    \n     tflhup2 = np.zeros(len(df), dtype=np.int32)     \n     exsup = np.zeros(len(df), dtype=np.int16)\n     exhup = np.zeros(len(df), dtype=np.float32)\n     exhup2 = np.zeros(len(df), dtype=np.float32)\n     tisup = np.zeros(len(df), dtype=np.int32)\n     tihup = np.zeros(len(df), dtype=np.float32)\n     tihup2 = np.zeros(len(df), dtype=np.float32)\n     cupp = np.zeros(len(df), dtype=np.int32)\n     \n     cup1 = np.zeros(len(df), dtype=np.int16)\n     cup2 = np.zeros(len(df), dtype=np.int16)\n     cup3 = np.zeros(len(df), dtype=np.int16)\n     cup4 = np.zeros(len(df), dtype=np.int16)\n     cup5 = np.zeros(len(df), dtype=np.int16)\n     cup6 = np.zeros(len(df), dtype=np.int16)\n     cup7 = np.zeros(len(df), dtype=np.int16)\n         \n     acsuc = np.zeros(len(df), dtype=np.int16)\n     achuc = np.zeros(len(df), dtype=np.float32)\n     achuc2 = np.zeros(len(df), dtype=np.float32)\n     cuc = np.zeros(len(df), dtype=np.int16)\n     cluc = np.zeros(len(df), dtype=np.int16)\n     tfluc = np.zeros(len(df), dtype=np.int32)\n     tflhuc = np.zeros(len(df), dtype=np.int32)\n     tflhuc2 = np.zeros(len(df), dtype=np.int32)\n     exsub = np.zeros(len(df), dtype=np.int32)\n     exhub = np.zeros(len(df), dtype=np.float32)\n     exhub2 = np.zeros(len(df), dtype=np.float32)\n     tisub = np.zeros(len(df), dtype=np.int32)\n     tihub = np.zeros(len(df), dtype=np.float32)\n     tihub2 = np.zeros(len(df), dtype=np.float32)\n     cub = np.zeros(len(df), dtype=np.int32)\n    \n     for cnt,row in enumerate(tqdm(df[['content_type_id',\n                                       'answered_correctly',\n                                       'prior_question_had_explanation',\n                                       'prior_question_elapsed_time',\n                                       'acc_avg_c',\n                                       'user_id',\n                                       'part',\n                                       'prior_part',\n                                       'content_id',\n                                       'bundle_id',\n                                       'prior_bundle',\n                                       'timestamp']].values)):\n         content_type_id=int(row[0])\n         answered_correctly=int(row[1])\n         prior_question_had_explanation=int(row[2])\n         prior_question_elapsed_time=int(row[3])\n         acc_avg_c=float(row[4])\n         user_id=int(row[5])\n         part=int(row[6])\n         prior_part=int(row[7])\n         content_id=int(row[8])\n         bundle_id=int(row[9])\n         prior_bundle=int(row[10])\n         timestamp=int(row[11])\n         \n         acsu[cnt] = acc_sum_u_dict[user_id]\n         acstdu[cnt] = acc_std_u_dict[user_id]\n         achu[cnt] = acc_hist_u_dict[user_id]\n         achu2[cnt] = acc_hist2_u_dict[user_id]\n         exsu[cnt] = exp_sum_u_dict[user_id]\n         exstdu[cnt] = exp_std_u_dict[user_id]\n         exhu[cnt] = exp_hist_u_dict[user_id]\n         exhu2[cnt] = exp_hist2_u_dict[user_id]\n         tisu[cnt] = time_sum_u_dict[user_id]\n         tistdu[cnt] = time_std_u_dict[user_id]\n         tihu[cnt] = time_hist_u_dict[user_id]\n         tihu2[cnt] = time_hist2_u_dict[user_id]\n         dpsu[cnt] = dpt_sum_u_dict[user_id]\n         dpstdu[cnt] = dpt_std_u_dict[user_id]\n         dphu[cnt] = dpt_hist_u_dict[user_id]\n         dphu2[cnt] = dpt_hist2_u_dict[user_id]\n         disu[cnt] = dif_sum_u_dict[user_id]\n         distdu[cnt] = dif_std_u_dict[user_id]\n         dihu[cnt] = dif_hist_u_dict[user_id]\n         dihu2[cnt] = dif_hist2_u_dict[user_id]    \n         cu[cnt] = count_u_dict[user_id]\n         clu[cnt] = count_lecture_u_dict[user_id]\n         \n         tflu[cnt] = timestamp-last_time_u_dict[user_id]\n         tflu2[cnt] = last_time_u_dict[user_id]-last_time2_u_dict[user_id]\n         tflu3[cnt] = last_time2_u_dict[user_id]-last_time3_u_dict[user_id]\n         tflu4[cnt] = last_time3_u_dict[user_id]-last_time4_u_dict[user_id]\n         tflu5[cnt] = last_time4_u_dict[user_id]-last_time5_u_dict[user_id]\n         tflstdu[cnt] = update_std(timestamp-last_time_u_dict[user_id], tfl_std_u_dict[user_id], last_time_u_dict[user_id], count_u_dict[user_id])\n         tflhu[cnt] = timestamp-last_time_u_dict[user_id]+tfl_hist_u_dict[user_id]*GAMMA\n         tflhu2[cnt] = timestamp-last_time_u_dict[user_id]+tfl_hist2_u_dict[user_id]*GAMMA2  \n         \n         acsc[cnt] = acc_sum_c_dict[content_id]\n         acstdc[cnt] = acc_std_c_dict[content_id]\n         exsb[cnt] = exp_sum_b_dict[bundle_id]\n         exstdb[cnt] = exp_std_b_dict[bundle_id]\n         tisb[cnt] = time_sum_b_dict[bundle_id]  \n         tistdb[cnt] = time_std_b_dict[bundle_id]  \n         cc[cnt] = count_c_dict[content_id]  \n         cb[cnt] = count_b_dict[bundle_id]         \n        \n         acsup[cnt] = acc_sum_up_dict[user_id][part]\n         achup[cnt] = acc_hist_up_dict[user_id][part]\n         achup2[cnt] = acc_hist2_up_dict[user_id][part]\n         cup[cnt] = count_up_dict[user_id][part]\n         clup[cnt] = count_lecture_up_dict[user_id][part]\n         \n         if count_up_dict[user_id][part]>0:\n             tflup[cnt] = timestamp-last_time_up_dict[user_id][part]\n             tflhup[cnt] = timestamp-last_time_up_dict[user_id][part]+tfl_hist_up_dict[user_id][part]*GAMMA\n             tflhup2[cnt] = timestamp-last_time_up_dict[user_id][part]+tfl_hist2_up_dict[user_id][part]*GAMMA2\n         \n         exsup[cnt] = exp_sum_up_dict[user_id][part]\n         exhup[cnt] = exp_hist_up_dict[user_id][part]\n         exhup2[cnt] = exp_hist2_up_dict[user_id][part]\n         tisup[cnt] = time_sum_up_dict[user_id][part]\n         tihup[cnt] = time_hist_up_dict[user_id][part]\n         tihup2[cnt] = time_hist2_up_dict[user_id][part]\n         cupp[cnt] = count_upp_dict[user_id][part]\n         \n         cup1[cnt] = count_up_dict[user_id][1]\n         cup2[cnt] = count_up_dict[user_id][2]\n         cup3[cnt] = count_up_dict[user_id][3]\n         cup4[cnt] = count_up_dict[user_id][4]\n         cup5[cnt] = count_up_dict[user_id][5]\n         cup6[cnt] = count_up_dict[user_id][6]\n         cup7[cnt] = count_up_dict[user_id][7]\n         \n         acsuc[cnt] = acc_sum_uc_dict[user_id][content_id]\n         achuc[cnt] = acc_hist_uc_dict[user_id][content_id]\n         achuc2[cnt] = acc_hist2_uc_dict[user_id][content_id]\n         cuc[cnt] = count_uc_dict[user_id][content_id]\n         cluc[cnt] = count_lecture_uc_dict[user_id][content_id]\n         \n         if count_uc_dict[user_id][content_id]>0:\n             tfluc[cnt] = timestamp-last_time_uc_dict[user_id][content_id]\n             tflhuc[cnt] = timestamp-last_time_uc_dict[user_id][content_id]+tfl_hist_uc_dict[user_id][content_id]*GAMMA\n             tflhuc2[cnt] = timestamp-last_time_uc_dict[user_id][content_id]+tfl_hist2_uc_dict[user_id][content_id]*GAMMA2\n         \n         exsub[cnt] = exp_sum_ub_dict[user_id][bundle_id]\n         exhub[cnt] = exp_hist_ub_dict[user_id][bundle_id]\n         exhub2[cnt] = exp_hist2_ub_dict[user_id][bundle_id]\n         tisub[cnt] = time_sum_ub_dict[user_id][bundle_id]\n         tihub[cnt] = time_hist_ub_dict[user_id][bundle_id]\n         tihub2[cnt] = time_hist2_ub_dict[user_id][bundle_id]\n         cub[cnt] = count_ub_dict[user_id][bundle_id]\n         \n         \n         if last_time_u_dict[user_id] == timestamp:\n             #if current is bundle. This part does not work for up when the first trial is bundled\n             tflu[cnt] = timestamp-last_time2_u_dict[user_id]\n             tflu2[cnt] = last_time2_u_dict[user_id]-last_time3_u_dict[user_id]\n             tflu3[cnt] = last_time3_u_dict[user_id]-last_time4_u_dict[user_id]\n             tflu4[cnt] = last_time4_u_dict[user_id]-last_time5_u_dict[user_id]\n             tflu5[cnt] = last_time5_u_dict[user_id]-last_time6_u_dict[user_id]\n             tflstdu[cnt] = update_std(timestamp-last_time2_u_dict[user_id], tfl_std_u_dict[user_id], last_time2_u_dict[user_id], count_u_dict[user_id])\n             tflhu[cnt] = timestamp-last_time2_u_dict[user_id]+tfl_hist_u_dict[user_id]*GAMMA\n             tflhu2[cnt] = timestamp-last_time2_u_dict[user_id]+tfl_hist2_u_dict[user_id]*GAMMA2\n             if count_up_dict[user_id][part]>0:\n                 tflup[cnt] = last_time_up_dict[user_id][part]-last_time2_up_dict[user_id][part]\n                 tflhup[cnt] = last_time_up_dict[user_id][part]-last_time2_up_dict[user_id][part]+tfl_hist_up_dict[user_id][part]*GAMMA\n                 tflhup2[cnt] = last_time_up_dict[user_id][part]-last_time2_up_dict[user_id][part]+tfl_hist2_up_dict[user_id][part]*GAMMA2\n\n         if content_type_id == 0:             \n             acc_std_u_dict[user_id] = update_std(answered_correctly, acc_std_u_dict[user_id], acc_sum_u_dict[user_id], count_u_dict[user_id])\n             acc_sum_u_dict[user_id] += answered_correctly\n             acc_hist_u_dict[user_id] = answered_correctly+acc_hist_u_dict[user_id]*GAMMA\n             acc_hist2_u_dict[user_id] = answered_correctly+acc_hist2_u_dict[user_id]*GAMMA2\n             exp_std_u_dict[user_id] = update_std(prior_question_had_explanation, exp_std_u_dict[user_id], exp_sum_u_dict[user_id], count_u_dict[user_id])\n             exp_sum_u_dict[user_id] += prior_question_had_explanation\n             exp_hist_u_dict[user_id] = prior_question_had_explanation+exp_hist_u_dict[user_id]*GAMMA\n             exp_hist2_u_dict[user_id] = prior_question_had_explanation+exp_hist2_u_dict[user_id]*GAMMA2\n             time_std_u_dict[user_id] = update_std(prior_question_elapsed_time, time_std_u_dict[user_id], time_sum_u_dict[user_id], count_u_dict[user_id])\n             time_sum_u_dict[user_id] += prior_question_elapsed_time\n             time_hist_u_dict[user_id] = prior_question_elapsed_time+time_hist_u_dict[user_id]*GAMMA\n             time_hist2_u_dict[user_id] = prior_question_elapsed_time+time_hist2_u_dict[user_id]*GAMMA2\n             dpt_std_u_dict[user_id] = update_std((1-acc_avg_c)*answered_correctly, dpt_std_u_dict[user_id], dpt_sum_u_dict[user_id], count_u_dict[user_id])\n             dpt_sum_u_dict[user_id] += (1-acc_avg_c)*answered_correctly\n             dpt_hist_u_dict[user_id] = (1-acc_avg_c)*answered_correctly+dpt_hist_u_dict[user_id]*GAMMA\n             dpt_hist2_u_dict[user_id] = (1-acc_avg_c)*answered_correctly+dpt_hist2_u_dict[user_id]*GAMMA2\n             dif_std_u_dict[user_id] = update_std(1-acc_avg_c, dif_std_u_dict[user_id], dif_sum_u_dict[user_id], count_u_dict[user_id])\n             dif_sum_u_dict[user_id] += 1-acc_avg_c\n             dif_hist_u_dict[user_id] = 1-acc_avg_c+dif_hist_u_dict[user_id]*GAMMA\n             dif_hist2_u_dict[user_id] = 1-acc_avg_c+dif_hist2_u_dict[user_id]*GAMMA2\n             count_u_dict[user_id] += 1             \n             \n             acc_std_c_dict[content_id] = update_std(answered_correctly, acc_std_c_dict[content_id], acc_sum_c_dict[content_id], count_c_dict[content_id])\n             acc_sum_c_dict[content_id] += answered_correctly\n             exp_std_b_dict[prior_bundle] = update_std(prior_question_had_explanation, exp_std_b_dict[prior_bundle], exp_sum_b_dict[prior_bundle], count_b_dict[prior_bundle])\n             exp_sum_b_dict[prior_bundle] += prior_question_had_explanation\n             time_std_b_dict[prior_bundle] = update_std(prior_question_elapsed_time, time_std_b_dict[prior_bundle], time_sum_b_dict[prior_bundle], count_b_dict[prior_bundle])\n             time_sum_b_dict[prior_bundle] += prior_question_elapsed_time\n             count_c_dict[content_id] += 1             \n             count_b_dict[prior_bundle] += 1             \n             \n             acc_sum_up_dict[user_id][part] += answered_correctly\n             acc_hist_up_dict[user_id][part] = answered_correctly+acc_hist_up_dict[user_id][part]*GAMMA\n             acc_hist2_up_dict[user_id][part] = answered_correctly+acc_hist2_up_dict[user_id][part]*GAMMA2\n             count_up_dict[user_id][part] += 1\n             \n             exp_sum_up_dict[user_id][prior_part] += prior_question_had_explanation\n             exp_hist_up_dict[user_id][prior_part] = prior_question_had_explanation+exp_hist_up_dict[user_id][prior_part]*GAMMA\n             exp_hist2_up_dict[user_id][prior_part] = prior_question_had_explanation+exp_hist2_up_dict[user_id][prior_part]*GAMMA2\n             time_sum_up_dict[user_id][prior_part] += prior_question_elapsed_time\n             time_hist_up_dict[user_id][prior_part] = prior_question_elapsed_time+time_hist_up_dict[user_id][prior_part]*GAMMA\n             time_hist2_up_dict[user_id][prior_part] = prior_question_elapsed_time+time_hist2_up_dict[user_id][prior_part]*GAMMA2\n             count_upp_dict[user_id][prior_part] += 1\n            \n             acc_sum_uc_dict[user_id][content_id] += answered_correctly\n             acc_hist_uc_dict[user_id][content_id] = answered_correctly+acc_hist_uc_dict[user_id][content_id]*GAMMA\n             acc_hist2_uc_dict[user_id][content_id] = answered_correctly+acc_hist2_uc_dict[user_id][content_id]*GAMMA2\n             count_uc_dict[user_id][content_id] += 1\n                 \n             exp_sum_ub_dict[user_id][prior_bundle] += prior_question_had_explanation\n             exp_hist_ub_dict[user_id][prior_bundle] = prior_question_had_explanation+exp_hist_ub_dict[user_id][prior_bundle]*GAMMA\n             exp_hist2_ub_dict[user_id][prior_bundle] = prior_question_had_explanation+exp_hist2_ub_dict[user_id][prior_bundle]*GAMMA2\n             time_sum_ub_dict[user_id][prior_bundle] += prior_question_elapsed_time\n             time_hist_ub_dict[user_id][prior_bundle] = prior_question_elapsed_time+time_hist_ub_dict[user_id][prior_bundle]*GAMMA\n             time_hist2_ub_dict[user_id][prior_bundle] = prior_question_elapsed_time+time_hist2_ub_dict[user_id][prior_bundle]*GAMMA2\n             count_ub_dict[user_id][prior_bundle] += 1\n             \n             if last_time_u_dict[user_id] < timestamp:\n                 tfl_std_u_dict[user_id] = update_std(timestamp-last_time_u_dict[user_id], tfl_std_u_dict[user_id], last_time_u_dict[user_id], count_u_dict[user_id]-1)\n                 tfl_hist_u_dict[user_id] = timestamp-last_time_u_dict[user_id]+tfl_hist_u_dict[user_id]*GAMMA\n                 tfl_hist2_u_dict[user_id] = timestamp-last_time_u_dict[user_id]+tfl_hist2_u_dict[user_id]*GAMMA2\n                 last_time6_u_dict[user_id] = last_time5_u_dict[user_id]\n                 last_time5_u_dict[user_id] = last_time4_u_dict[user_id]\n                 last_time4_u_dict[user_id] = last_time3_u_dict[user_id]\n                 last_time3_u_dict[user_id] = last_time2_u_dict[user_id]\n                 last_time2_u_dict[user_id] = last_time_u_dict[user_id]\n                 last_time_u_dict[user_id] = timestamp\n             else:\n                 tfl_std_u_dict[user_id] = update_std(timestamp-last_time2_u_dict[user_id], tfl_std_u_dict[user_id], last_time2_u_dict[user_id], count_u_dict[user_id]-1)\n                 \n                 \n             if last_time_up_dict[user_id][part] < timestamp:\n                 tfl_hist_up_dict[user_id][part] = timestamp-last_time_up_dict[user_id][part]+tfl_hist_up_dict[user_id][part]*GAMMA\n                 tfl_hist2_up_dict[user_id][part] = timestamp-last_time_up_dict[user_id][part]+tfl_hist2_up_dict[user_id][part]*GAMMA2\n                 last_time2_up_dict[user_id][part] = last_time_up_dict[user_id][part]\n                 last_time_up_dict[user_id][part] = timestamp\n                 \n             if last_time_uc_dict[user_id][content_id] < timestamp:\n                 tfl_hist_uc_dict[user_id][content_id] = timestamp-last_time_uc_dict[user_id][content_id]+tfl_hist_uc_dict[user_id][content_id]*GAMMA\n                 tfl_hist2_uc_dict[user_id][content_id] = timestamp-last_time_uc_dict[user_id][content_id]+tfl_hist2_uc_dict[user_id][content_id]*GAMMA2\n                 last_time2_uc_dict[user_id][content_id] = last_time_uc_dict[user_id][content_id]\n                 last_time_uc_dict[user_id][content_id] = timestamp\n\n                \n         if content_type_id == 1:\n             count_lecture_u_dict[user_id] += 1\n             count_lecture_up_dict[user_id][part] += 1\n             count_lecture_uc_dict[user_id][content_id] += 1\n             \n     df['acc_sum_u']=acsu\n     df['acc_std_u']=acstdu\n     df['acc_hist_u']=achu\n     df['acc_hist2_u']=achu2\n     df['exp_sum_u']=exsu\n     df['exp_std_u']=exstdu\n     df['exp_hist_u']=exhu\n     df['exp_hist2_u']=exhu2\n     df['time_sum_u']=tisu\n     df['time_std_u']=tistdu\n     df['time_hist_u']=tihu\n     df['time_hist2_u']=tihu2\n     df['dpt_sum_u']=dpsu\n     df['dpt_std_u']=dpstdu\n     df['dpt_hist_u']=dphu\n     df['dpt_hist2_u']=dphu2\n     df['dif_sum_u']=disu\n     df['dif_std_u']=disu\n     df['dif_hist_u']=dihu\n     df['dif_hist2_u']=dihu2\n     df['count_u']=cu\n     df['count_lecture_u']=clu\n     df['tfl_u']=tflu\n     df['tfl2_u']=tflu2\n     df['tfl3_u']=tflu3\n     df['tfl4_u']=tflu4\n     df['tfl5_u']=tflu5\n     df['tfl_std_u']=tflstdu\n     df['tfl_hist_u']=tflhu\n     df['tfl_hist2_u']=tflhu2\n     \n     #df['acc_sum_c']=acsc\n     #df['acc_std_c']=acstdc\n     #df['exp_sum_b']=exsb\n     #df['exp_std_b']=exstdb\n     #df['time_sum_b']=tisb\n     #df['time_std_b']=tistdb\n     #df['count_c']=cc\n     #df['count_b']=cb\n        \n     df['acc_sum_up']=acsup\n     df['acc_hist_up']=achup\n     df['acc_hist2_up']=achup2\n     df['count_up']=cup\n     df['count_lecture_up']=clup\n     df['tfl_up']=tflup\n     df['tfl_hist_up']=tflhup\n     df['tfl_hist2_up']=tflhup2\n     df['exp_sum_up']=exsup\n     df['exp_hist_up']=exhup\n     df['exp_hist2_up']=exhup2\n     df['time_sum_up']=tisup\n     df['time_hist_up']=tihup\n     df['time_hist2_up']=tihup2\n     df['count_upp']=cupp\n     \n     df['count_up1']=cup1\n     df['count_up2']=cup2\n     df['count_up3']=cup3\n     df['count_up4']=cup4\n     df['count_up5']=cup5\n     df['count_up6']=cup6\n     df['count_up7']=cup7\n     \n     df['acc_sum_uc']=acsuc\n     df['acc_hist_uc']=achuc\n     df['acc_hist2_uc']=achuc2\n     df['count_uc']=cuc\n     df['count_lecture_uc']=cluc\n     df['tfl_uc']=tfluc\n     df['tfl_hist_uc']=tflhuc\n     df['tfl_hist2_uc']=tflhuc2\n     df['exp_sum_ub']=exsub\n     df['exp_hist_ub']=exhub\n     df['exp_hist2_ub']=exhub2\n     df['time_sum_ub']=tisub\n     df['time_hist_ub']=tihub\n     df['time_hist2_ub']=tihub2\n     df['count_ub']=cub\n         \n     df['acc_avg_u'] = (df['acc_sum_u'] / df['count_u']).astype('float32')\n     df['exp_avg_u'] = (df['exp_sum_u'] / df['count_u']).astype('float32')\n     df['time_avg_u'] = (df['time_sum_u'] / df['count_u']).astype('float32')\n     df['dif_avg_u'] = (df['dif_sum_u'] / df['count_u']).astype('float32')\n     df['dpt_avg_u'] = (df['dpt_sum_u'] / df['count_u']).astype('float32')\n     \n     #df['acc_avg_c'] = (df['acc_sum_c'] / df['count_c']).astype('float32')\n     #df['exp_avg_b'] = (df['exp_sum_b'] / df['count_b']).astype('float32')\n     #df['time_avg_b'] = (df['time_sum_b'] / df['count_b']).astype('float32')\n     \n     df['acc_avg_up'] = (df['acc_sum_up'] / df['count_up']).astype('float32')\n     df['exp_avg_up'] = (df['exp_sum_up'] / df['count_upp']).astype('float32')\n     df['time_avg_up'] = (df['time_sum_up'] /df['count_upp']).astype('float32')\n     \n     df['acc_avg_uc'] = (df['acc_sum_uc'] / df['count_uc']).astype('float32')    \n     df['exp_avg_ub'] = (df['exp_sum_ub'] / df['count_ub']).astype('float32')\n     df['time_avg_ub'] = (df['time_sum_ub'] /df['count_ub']).astype('float32')\n     return df\n     \ndef update_u_db(user_id,acc_sum_u,acc_std_u,acc_hist_u,acc_hist2_u,\n                exp_sum_u,exp_std_u,exp_hist_u,exp_hist2_u,\n                time_sum_u,time_std_u,time_hist_u,time_hist2_u,\n                dpt_sum_u,dpt_std_u,dpt_hist_u,dpt_hist2_u,\n                dif_sum_u,dif_std_u,dif_hist_u,dif_hist2_u,\n                count_u,count_lecture_u,\n                last_time_u,last_time2_u,last_time3_u,last_time4_u,last_time5_u,last_time6_u,\n                tfl_std_u,tfl_hist_u,tfl_hist2_u,conn):\n    cur = conn.cursor()\n    cur.execute(\"INSERT OR REPLACE INTO u_feats VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)\",(user_id,acc_sum_u,acc_std_u,acc_hist_u,acc_hist2_u,exp_sum_u,exp_std_u,exp_hist_u,exp_hist2_u,time_sum_u,time_std_u,time_hist_u,time_hist2_u,dpt_sum_u,dpt_std_u,dpt_hist_u,dpt_hist2_u,dif_sum_u,dif_std_u,dif_hist_u,dif_hist2_u,count_u,count_lecture_u,last_time_u,last_time2_u,last_time3_u,last_time4_u,last_time5_u,last_time6_u,tfl_std_u,tfl_hist_u,tfl_hist2_u))\n    #conn.commit()\n    cur.close()\n        \ndef update_c_db(content_id,acc_sum_c,acc_std_c,count_c,conn):\n    cur = conn.cursor()\n    cur.execute(\"INSERT OR REPLACE INTO c_feats VALUES (?,?,?,?)\",(content_id,acc_sum_c,acc_std_c,count_c))\n    #conn.commit()\n    cur.close()\n    \ndef update_b_db(bundle_id,exp_sum_b,exp_std_b,time_sum_b,time_std_b,count_b,conn):\n    cur = conn.cursor()\n    cur.execute(\"INSERT OR REPLACE INTO b_feats VALUES (?,?,?,?,?,?)\",(bundle_id,exp_sum_b,exp_std_b,time_sum_b,time_std_b,count_b))\n    #conn.commit()\n    cur.close()\n\ndef update_up_db(user_id,part,\n                acc_sum_up,acc_hist_up,acc_hist2_up,\n                count_up,count_lecture_up,last_time_up,\n                last_time2_up,tfl_hist_up,tfl_hist2_up,\n                exp_sum_up,exp_hist_up,exp_hist2_up,\n                time_sum_up,time_hist_up,time_hist2_up,count_upp,conn):\n    cur = conn.cursor()\n    cur.execute(\"INSERT OR REPLACE INTO up_feats VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)\",(user_id,part,acc_sum_up,acc_hist_up,acc_hist2_up, count_up,count_lecture_up,last_time_up, last_time2_up,tfl_hist_up,tfl_hist2_up, exp_sum_up,exp_hist_up,exp_hist2_up,time_sum_up,time_hist_up,time_hist2_up,count_upp))\n    #conn.commit()\n    cur.close()\n    \ndef update_uc_db(user_id,content_id,\n                     acc_sum_uc,acc_hist_uc,acc_hist2_uc,\n                     count_uc,count_lecture_uc,last_time_uc,\n                     last_time2_uc,tfl_hist_uc,tfl_hist2_uc,conn):\n    cur = conn.cursor()\n    cur.execute(\"INSERT OR REPLACE INTO uc_feats VALUES (?,?,?,?,?,?,?,?,?,?,?)\",(user_id,content_id,acc_sum_uc, acc_hist_uc, acc_hist2_uc,count_uc,count_lecture_uc,last_time_uc,last_time2_uc,tfl_hist_uc,tfl_hist2_uc))\n    #conn.commit()\n    cur.close()\n    \ndef update_ub_db(user_id,bundle_id,\n                exp_sum_ub,exp_hist_ub,exp_hist2_ub,\n                time_sum_ub,time_hist_ub,time_hist2_ub,count_ub,conn):\n    cur = conn.cursor()\n    cur.execute(\"INSERT OR REPLACE INTO ub_feats VALUES (?,?,?,?,?,?,?,?,?)\",(user_id,bundle_id,exp_sum_ub,exp_hist_ub, exp_hist2_ub,time_sum_ub,time_hist_ub,time_hist2_ub,count_ub))\n    #conn.commit()\n    cur.close()\n    \ndef get_u_feats(user_id,conn):\n    cur = conn.cursor()\n    cur.execute(\"SELECT * FROM u_feats WHERE user_id = \"+str(user_id))\n    list_u=cur.fetchone()\n    cur.close()\n    if list_u==None:\n        acc_sum_u=0\n        acc_std_u=0\n        acc_hist_u=0\n        acc_hist2_u=0\n        exp_sum_u=0\n        exp_std_u=0\n        exp_hist_u=0\n        exp_hist2_u=0\n        time_sum_u=0\n        time_std_u=0\n        time_hist_u=0\n        time_hist2_u=0\n        dpt_sum_u=0\n        dpt_std_u=0\n        dpt_hist_u=0\n        dpt_hist2_u=0\n        dif_sum_u=0\n        dif_std_u=0\n        dif_hist_u=0\n        dif_hist2_u=0\n        count_u=0\n        count_lecture_u=0\n        last_time_u=0\n        last_time2_u=0\n        last_time3_u=0\n        last_time4_u=0\n        last_time5_u=0\n        last_time6_u=0\n        tfl_std_u=0\n        tfl_hist_u=0\n        tfl_hist2_u=0\n    else:\n        acc_sum_u=list_u[1]\n        acc_std_u=list_u[2]\n        acc_hist_u=list_u[3]\n        acc_hist2_u=list_u[4]\n        exp_sum_u=list_u[5]\n        exp_std_u=list_u[6]\n        exp_hist_u=list_u[7]\n        exp_hist2_u=list_u[8]\n        time_sum_u=list_u[9]\n        time_std_u=list_u[10]\n        time_hist_u=list_u[11]\n        time_hist2_u=list_u[12]\n        dpt_sum_u=list_u[13]\n        dpt_std_u=list_u[14]\n        dpt_hist_u=list_u[15]\n        dpt_hist2_u=list_u[16]\n        dif_sum_u=list_u[17]\n        dif_std_u=list_u[18]\n        dif_hist_u=list_u[19]\n        dif_hist2_u=list_u[20]\n        count_u=list_u[21]\n        count_lecture_u=list_u[22]\n        last_time_u=list_u[23]\n        last_time2_u=list_u[24]\n        last_time3_u=list_u[25]\n        last_time4_u=list_u[26]\n        last_time5_u=list_u[27]\n        last_time6_u=list_u[28]\n        tfl_std_u=list_u[29]\n        tfl_hist_u=list_u[30]\n        tfl_hist2_u=list_u[31]\n    return acc_sum_u,acc_std_u,acc_hist_u,acc_hist2_u,exp_sum_u,exp_std_u,exp_hist_u,exp_hist2_u, time_sum_u,time_std_u,time_hist_u,time_hist2_u,dpt_sum_u,dpt_std_u,dpt_hist_u,dpt_hist2_u,  dif_sum_u,dif_std_u,dif_hist_u,dif_hist2_u, count_u,count_lecture_u,last_time_u,last_time2_u,last_time3_u,last_time4_u,last_time5_u,last_time6_u,tfl_std_u,tfl_hist_u,tfl_hist2_u\n\ndef get_c_feats(content_id,conn):\n    cur = conn.cursor()\n    cur.execute(\"SELECT * FROM c_feats WHERE content_id = \"+str(content_id))\n    list_c=cur.fetchone()\n    cur.close()\n    if list_c==None:\n        acc_sum_c=0\n        acc_std_c=0\n        count_c=0\n    else:\n        acc_sum_c=list_c[1]\n        acc_std_c=list_c[2]\n        count_c=list_c[3]\n    return acc_sum_c,acc_std_c,count_c\n\ndef get_b_feats(bundle_id,conn):\n    cur = conn.cursor()\n    cur.execute(\"SELECT * FROM b_feats WHERE bundle_id = \"+str(bundle_id))\n    list_b=cur.fetchone()\n    cur.close()\n    if list_b==None:\n        exp_sum_b=0\n        exp_std_b=0\n        time_sum_b=0\n        time_std_b=0\n        count_b=0\n    else:\n        exp_sum_b=list_b[1]\n        exp_std_b=list_b[2]\n        time_sum_b=list_b[3]\n        time_std_b=list_b[4]\n        count_b=list_b[5]\n    return exp_sum_b,exp_std_b,time_sum_b,time_std_b,count_b\n\ndef get_up_feats(user_id,part,conn):\n    cur = conn.cursor()\n    cur.execute(\"SELECT * FROM up_feats WHERE user_id = \"+str(user_id)+' AND part = '+str(part))\n    list_up=cur.fetchone()\n    cur.close()\n    if list_up==None:\n        acc_sum_up=0\n        acc_hist_up=0\n        acc_hist2_up=0\n        count_up=0\n        count_lecture_up=0\n        last_time_up=0\n        last_time2_up=0\n        tfl_hist_up=0\n        tfl_hist2_up=0\n        exp_sum_up=0\n        exp_hist_up=0\n        exp_hist2_up=0\n        time_sum_up=0\n        time_hist_up=0\n        time_hist2_up=0\n        count_upp=0\n    else:\n        acc_sum_up=list_up[2]\n        acc_hist_up=list_up[3]\n        acc_hist2_up=list_up[4]\n        count_up=list_up[5]\n        count_lecture_up=list_up[6]\n        last_time_up=list_up[7]\n        last_time2_up=list_up[8]\n        tfl_hist_up=list_up[9]\n        tfl_hist2_up=list_up[10]\n        exp_sum_up=list_up[11]\n        exp_hist_up=list_up[12]\n        exp_hist2_up=list_up[13]\n        time_sum_up=list_up[14]\n        time_hist_up=list_up[15]\n        time_hist2_up=list_up[16]\n        count_upp=list_up[17]\n    return acc_sum_up,acc_hist_up,acc_hist2_up,count_up,count_lecture_up,last_time_up,last_time2_up,tfl_hist_up,tfl_hist2_up,exp_sum_up,exp_hist_up,exp_hist2_up,time_sum_up,time_hist_up,time_hist2_up,count_upp\n\ndef get_up_counts(user_id,conn):\n    cur = conn.cursor()\n    cur.execute(\"SELECT * FROM up_feats WHERE user_id = \"+str(user_id)+' AND part = 1')\n    list_up1=cur.fetchone()\n    cur.execute(\"SELECT * FROM up_feats WHERE user_id = \"+str(user_id)+' AND part = 2')\n    list_up2=cur.fetchone()\n    cur.execute(\"SELECT * FROM up_feats WHERE user_id = \"+str(user_id)+' AND part = 3')\n    list_up3=cur.fetchone()\n    cur.execute(\"SELECT * FROM up_feats WHERE user_id = \"+str(user_id)+' AND part = 4')\n    list_up4=cur.fetchone()\n    cur.execute(\"SELECT * FROM up_feats WHERE user_id = \"+str(user_id)+' AND part = 5')\n    list_up5=cur.fetchone()\n    cur.execute(\"SELECT * FROM up_feats WHERE user_id = \"+str(user_id)+' AND part = 6')\n    list_up6=cur.fetchone()\n    cur.execute(\"SELECT * FROM up_feats WHERE user_id = \"+str(user_id)+' AND part = 7')\n    list_up7=cur.fetchone()\n    cur.close()\n    if list_up1==None:\n        count_up1=0\n    else:\n        count_up1=list_up1[5]\n    if list_up2==None:\n        count_up2=0\n    else:\n        count_up2=list_up2[5]\n    if list_up3==None:\n        count_up3=0\n    else:\n        count_up3=list_up3[5]\n    if list_up4==None:\n        count_up4=0\n    else:\n        count_up4=list_up4[5]\n    if list_up5==None:\n        count_up5=0\n    else:\n        count_up5=list_up5[5]\n    if list_up6==None:\n        count_up6=0\n    else:\n        count_up6=list_up6[5]\n    if list_up7==None:\n        count_up7=0\n    else:\n        count_up7=list_up7[5]\n    return count_up1,count_up2,count_up3,count_up4,count_up5,count_up6,count_up7\n\ndef get_uc_feats(user_id,content_id,conn):\n    cur = conn.cursor()\n    cur.execute(\"SELECT * FROM uc_feats WHERE user_id = \"+str(user_id)+' AND content_id = '+str(content_id))\n    list_uc=cur.fetchone()\n    cur.close()\n    if list_uc==None:\n        acc_sum_uc=0\n        acc_hist_uc=0\n        acc_hist2_uc=0\n        count_uc=0\n        count_lecture_uc=0\n        last_time_uc=0\n        last_time2_uc=0\n        tfl_hist_uc=0\n        tfl_hist2_uc=0\n    else:\n        acc_sum_uc=list_uc[2]\n        acc_hist_uc=list_uc[3]\n        acc_hist2_uc=list_uc[4]\n        count_uc=list_uc[5]\n        count_lecture_uc=list_uc[6]\n        last_time_uc=list_uc[7]\n        last_time2_uc=list_uc[8]\n        tfl_hist_uc=list_uc[9]\n        tfl_hist2_uc=list_uc[10]\n    return acc_sum_uc,acc_hist_uc,acc_hist2_uc,count_uc,count_lecture_uc,last_time_uc,last_time2_uc,tfl_hist_uc,tfl_hist2_uc\n\ndef get_ub_feats(user_id,bundle_id,conn):\n    cur = conn.cursor()\n    cur.execute(\"SELECT * FROM ub_feats WHERE user_id = \"+str(user_id)+' AND bundle_id = '+str(bundle_id))\n    list_ub=cur.fetchone()\n    cur.close()\n    if list_ub==None:\n        exp_sum_ub=0\n        exp_hist_ub=0\n        exp_hist2_ub=0\n        time_sum_ub=0\n        time_hist_ub=0\n        time_hist2_ub=0\n        count_ub=0\n    else:\n        exp_sum_ub=list_ub[2]\n        exp_hist_ub=list_ub[3]\n        exp_hist2_ub=list_ub[4]\n        time_sum_ub=list_ub[5]\n        time_hist_ub=list_ub[6]\n        time_hist2_ub=list_ub[7]\n        count_ub=list_ub[8]\n    return exp_sum_ub,exp_hist_ub,exp_hist2_ub,time_sum_ub,time_hist_ub,time_hist2_ub,count_ub\n\ndef add_feats_without_update_sqlite(df, conn):\n     acsu = np.zeros(len(df), dtype=np.int16)\n     acstdu = np.zeros(len(df), dtype=np.float32)   \n     achu = np.zeros(len(df), dtype=np.float32)\n     achu2 = np.zeros(len(df), dtype=np.float32)     \n     exsu = np.zeros(len(df), dtype=np.int16)\n     exstdu = np.zeros(len(df), dtype=np.float32)  \n     exhu = np.zeros(len(df), dtype=np.float32)\n     exhu2 = np.zeros(len(df), dtype=np.float32)\n     tisu = np.zeros(len(df), dtype=np.int32)\n     tistdu = np.zeros(len(df), dtype=np.float32)  \n     tihu = np.zeros(len(df), dtype=np.float32)\n     tihu2 = np.zeros(len(df), dtype=np.float32)\n     dpsu = np.zeros(len(df), dtype=np.float32)\n     dpstdu = np.zeros(len(df), dtype=np.float32)  \n     dphu = np.zeros(len(df), dtype=np.float32)\n     dphu2 = np.zeros(len(df), dtype=np.float32)    \n     disu = np.zeros(len(df), dtype=np.float32)\n     distdu = np.zeros(len(df), dtype=np.float32)  \n     dihu = np.zeros(len(df), dtype=np.float32)\n     dihu2 = np.zeros(len(df), dtype=np.float32)  \n     cu = np.zeros(len(df), dtype=np.int16)\n     clu = np.zeros(len(df), dtype=np.int16)\n     tflu = np.zeros(len(df), dtype=np.int32)\n     tflu2 = np.zeros(len(df), dtype=np.int32)\n     tflu3 = np.zeros(len(df), dtype=np.int32)\n     tflu4 = np.zeros(len(df), dtype=np.int32)\n     tflu5 = np.zeros(len(df), dtype=np.int32)\n     tflstdu = np.zeros(len(df), dtype=np.float32)  \n     tflhu = np.zeros(len(df), dtype=np.int32)\n     tflhu2 = np.zeros(len(df), dtype=np.int32)\n          \n     acsc = np.zeros(len(df), dtype=np.int32)\n     exsb = np.zeros(len(df), dtype=np.int32)\n     tisb = np.zeros(len(df), dtype=np.int32)\n     cc = np.zeros(len(df), dtype=np.int32)\n     cb = np.zeros(len(df), dtype=np.int32)   \n     \n     acsup = np.zeros(len(df), dtype=np.int16)\n     achup = np.zeros(len(df), dtype=np.float32)\n     achup2 = np.zeros(len(df), dtype=np.float32)\n     cup = np.zeros(len(df), dtype=np.int16)\n     clup = np.zeros(len(df), dtype=np.int16)\n     tflup = np.zeros(len(df), dtype=np.int32)    \n     tflhup = np.zeros(len(df), dtype=np.int32)    \n     tflhup2 = np.zeros(len(df), dtype=np.int32)     \n     exsup = np.zeros(len(df), dtype=np.int16)\n     exhup = np.zeros(len(df), dtype=np.float32)\n     exhup2 = np.zeros(len(df), dtype=np.float32)\n     tisup = np.zeros(len(df), dtype=np.int32)\n     tihup = np.zeros(len(df), dtype=np.float32)\n     tihup2 = np.zeros(len(df), dtype=np.float32)\n     cupp = np.zeros(len(df), dtype=np.int32)\n     \n     cup1 = np.zeros(len(df), dtype=np.int16)\n     cup2 = np.zeros(len(df), dtype=np.int16)\n     cup3 = np.zeros(len(df), dtype=np.int16)\n     cup4 = np.zeros(len(df), dtype=np.int16)\n     cup5 = np.zeros(len(df), dtype=np.int16)\n     cup6 = np.zeros(len(df), dtype=np.int16)\n     cup7 = np.zeros(len(df), dtype=np.int16)\n         \n     acsuc = np.zeros(len(df), dtype=np.int16)\n     achuc = np.zeros(len(df), dtype=np.float32)\n     achuc2 = np.zeros(len(df), dtype=np.float32)\n     cuc = np.zeros(len(df), dtype=np.int16)\n     tfluc = np.zeros(len(df), dtype=np.int32)\n     tflhuc = np.zeros(len(df), dtype=np.int32)\n     tflhuc2 = np.zeros(len(df), dtype=np.int32)\n     exsub = np.zeros(len(df), dtype=np.int32)\n     exhub = np.zeros(len(df), dtype=np.float32)\n     exhub2 = np.zeros(len(df), dtype=np.float32)\n     tisub = np.zeros(len(df), dtype=np.int32)\n     tihub = np.zeros(len(df), dtype=np.float32)\n     tihub2 = np.zeros(len(df), dtype=np.float32)\n     cub = np.zeros(len(df), dtype=np.int32)\n     \n     for cnt,row in enumerate(df[['content_type_id',\n                                       'content_type_id',\n                                       'prior_question_had_explanation',\n                                       'prior_question_elapsed_time',\n                                       'acc_avg_c',\n                                       'user_id',\n                                       'part',\n                                       'prior_part',\n                                       'content_id',\n                                       'bundle_id',\n                                       'prior_bundle',\n                                       'timestamp']].values):\n         \n         content_type_id=int(row[0])\n         prior_question_had_explanation=int(row[2])\n         prior_question_elapsed_time=int(row[3])\n         acc_avg_c=float(row[4])\n         user_id=int(row[5])\n         part=int(row[6])\n         prior_part=int(row[7])\n         content_id=int(row[8])\n         bundle_id=int(row[9])\n         prior_bundle=int(row[10])\n         timestamp=int(row[11])\n         \n         acc_sum_u,acc_std_u,acc_hist_u,acc_hist2_u,exp_sum_u,exp_std_u,exp_hist_u,exp_hist2_u, time_sum_u,time_std_u,time_hist_u,time_hist2_u,dpt_sum_u,dpt_std_u,dpt_hist_u,dpt_hist2_u,dif_sum_u,dif_std_u,dif_hist_u,dif_hist2_u, count_u,count_lecture_u,last_time_u,last_time2_u,last_time3_u,last_time4_u,last_time5_u,last_time6_u,tfl_std_u,tfl_hist_u,tfl_hist2_u=get_u_feats(user_id,conn)\n         acc_sum_c,acc_std_c,count_c=get_c_feats(content_id,conn)\n         exp_sum_b,exp_std_b,time_sum_b,time_std_b,count_b=get_b_feats(bundle_id,conn)\n         acc_sum_up,acc_hist_up,acc_hist2_up,count_up,count_lecture_up,last_time_up,last_time2_up,tfl_hist_up,tfl_hist2_up,exp_sum_up,exp_hist_up,exp_hist2_up,time_sum_up,time_hist_up,time_hist2_up,count_upp=get_up_feats(user_id,part,conn)\n         acc_sum_uc,acc_hist_uc,acc_hist2_uc,count_uc,count_lecture_uc,last_time_uc,last_time2_uc,tfl_hist_uc,tfl_hist2_uc=get_uc_feats(user_id,content_id,conn)\n         exp_sum_ub,exp_hist_ub,exp_hist2_ub,time_sum_ub,time_hist_ub,time_hist2_ub,count_ub=get_ub_feats(user_id,bundle_id,conn)\n         count_up1,count_up2,count_up3,count_up4,count_up5,count_up6,count_up7=get_up_counts(user_id,conn)\n         \n         acsu[cnt] = acc_sum_u\n         acstdu[cnt] = acc_std_u\n         achu[cnt] = acc_hist_u\n         achu2[cnt] = acc_hist2_u\n         exsu[cnt] = exp_sum_u\n         exstdu[cnt] = exp_std_u\n         exhu[cnt] = exp_hist_u\n         exhu2[cnt] = exp_hist2_u\n         tisu[cnt] = time_sum_u\n         tistdu[cnt] = time_std_u\n         tihu[cnt] = time_hist_u\n         tihu2[cnt] = time_hist2_u\n         dpsu[cnt] = dpt_sum_u\n         dpstdu[cnt] = dpt_std_u\n         dphu[cnt] = dpt_hist_u\n         dphu2[cnt] = dpt_hist2_u\n         disu[cnt] = dif_sum_u\n         distdu[cnt] = dif_std_u\n         dihu[cnt] = dif_hist_u\n         dihu2[cnt] = dif_hist2_u    \n         cu[cnt] = count_u\n         clu[cnt] = count_lecture_u\n         \n         tflu[cnt] = timestamp-last_time_u\n         tflu2[cnt] = last_time_u-last_time2_u\n         tflu3[cnt] = last_time2_u-last_time3_u\n         tflu4[cnt] = last_time3_u-last_time4_u\n         tflu5[cnt] = last_time4_u-last_time5_u\n         tflstdu[cnt] = update_std(timestamp-last_time_u, tfl_std_u, last_time_u, count_u)\n         tflhu[cnt] = timestamp-last_time_u+tfl_hist_u*GAMMA\n         tflhu2[cnt] = timestamp-last_time_u+tfl_hist2_u*GAMMA2  \n                  \n         acsc[cnt] = acc_sum_c\n         exsb[cnt] = exp_sum_b\n         tisb[cnt] = time_sum_b  \n         cc[cnt] = count_c  \n         cb[cnt] = count_b         \n        \n         acsup[cnt] = acc_sum_up\n         achup[cnt] = acc_hist_up\n         achup2[cnt] = acc_hist2_up\n         cup[cnt] = count_up\n         clup[cnt] = count_lecture_up\n         \n         if count_up>0:\n             tflup[cnt] = timestamp-last_time_up\n             tflhup[cnt] = timestamp-last_time_up+tfl_hist_up*GAMMA\n             tflhup2[cnt] = timestamp-last_time_up+tfl_hist2_up*GAMMA2\n         \n         exsup[cnt] = exp_sum_up\n         exhup[cnt] = exp_hist_up\n         exhup2[cnt] = exp_hist2_up\n         tisup[cnt] = time_sum_up\n         tihup[cnt] = time_hist_up\n         tihup2[cnt] = time_hist2_up\n         cupp[cnt] = count_upp\n         \n         cup1[cnt] = count_up1\n         cup2[cnt] = count_up2\n         cup3[cnt] = count_up3\n         cup4[cnt] = count_up4\n         cup5[cnt] = count_up5\n         cup6[cnt] = count_up6\n         cup7[cnt] = count_up7\n         \n         acsuc[cnt] = acc_sum_uc\n         achuc[cnt] = acc_hist_uc\n         achuc2[cnt] = acc_hist2_uc\n         cuc[cnt] = count_uc\n         \n         if count_uc>0:\n             tfluc[cnt] = timestamp-last_time_uc\n             tflhuc[cnt] = timestamp-last_time_uc+tfl_hist_uc*GAMMA\n             tflhuc2[cnt] = timestamp-last_time_uc+tfl_hist2_uc*GAMMA2\n         \n         exsub[cnt] = exp_sum_ub\n         exhub[cnt] = exp_hist_ub\n         exhub2[cnt] = exp_hist2_ub\n         tisub[cnt] = time_sum_ub\n         tihub[cnt] = time_hist_ub\n         tihub2[cnt] = time_hist2_ub\n         cub[cnt] = count_ub\n         \n         \n         if last_time_u == timestamp:\n             #if current is bundle. This part does not work for up when the first trial is bundled\n             tflu[cnt] = timestamp-last_time2_u\n             tflu2[cnt] = last_time2_u-last_time3_u\n             tflu3[cnt] = last_time3_u-last_time4_u\n             tflu4[cnt] = last_time4_u-last_time5_u\n             tflu5[cnt] = last_time5_u-last_time6_u\n             tflstdu[cnt] = update_std(timestamp-last_time2_u, tfl_std_u, last_time2_u, count_u)\n             tflhu[cnt] = timestamp-last_time2_u+tfl_hist_u*GAMMA\n             tflhu2[cnt] = timestamp-last_time2_u+tfl_hist2_u*GAMMA2\n             if count_up>0:\n                 tflup[cnt] = last_time_up-last_time2_up\n                 tflhup[cnt] = last_time_up-last_time2_up+tfl_hist_up*GAMMA\n                 tflhup2[cnt] = last_time_up-last_time2_up+tfl_hist2_up*GAMMA2\n             #if count_uc[content_id]>0:\n             #    tfluc[cnt] = last_time_uc[content_id]-last_time2_uc[content_id]\n             #    tflhuc[cnt] = last_time_uc[content_id]-last_time2_uc[content_id]+tfl_hist_uc[content_id]*GAMMA\n             #    tflhuc2[cnt] = last_time_uc[content_id]-last_time2_uc[content_id]+tfl_hist2_uc[content_id]*GAMMA2\n        \n     \n     df['acc_sum_u']=acsu\n     df['acc_std_u']=acstdu\n     df['acc_hist_u']=achu\n     df['acc_hist2_u']=achu2\n     df['exp_sum_u']=exsu\n     df['exp_std_u']=exstdu\n     df['exp_hist_u']=exhu\n     df['exp_hist2_u']=exhu2\n     df['time_sum_u']=tisu\n     df['time_std_u']=tistdu\n     df['time_hist_u']=tihu\n     df['time_hist2_u']=tihu2\n     df['dpt_sum_u']=dpsu\n     df['dpt_std_u']=dpsu\n     df['dpt_hist_u']=dphu\n     df['dpt_hist2_u']=dphu2\n     df['dif_sum_u']=disu\n     df['dif_std_u']=disu\n     df['dif_hist_u']=dihu\n     df['dif_hist2_u']=dihu2\n     df['count_u']=cu\n     df['count_lecture_u']=clu\n     df['tfl_u']=tflu\n     df['tfl2_u']=tflu2\n     df['tfl3_u']=tflu3\n     df['tfl4_u']=tflu4\n     df['tfl5_u']=tflu5\n     df['tfl_std_u']=tflstdu\n     df['tfl_hist_u']=tflhu\n     df['tfl_hist2_u']=tflhu2\n     \n     df['acc_sum_c']=acsc\n     df['exp_sum_b']=exsb\n     df['time_sum_b']=tisb\n     df['count_c']=cc\n     df['count_b']=cb\n        \n     df['acc_sum_up']=acsup\n     df['acc_hist_up']=achup\n     df['acc_hist2_up']=achup2\n     df['count_up']=cup\n     df['count_lecture_up']=clup\n     df['tfl_up']=tflup\n     df['tfl_hist_up']=tflhup\n     df['tfl_hist2_up']=tflhup2\n     df['exp_sum_up']=exsup\n     df['exp_hist_up']=exhup\n     df['exp_hist2_up']=exhup2\n     df['time_sum_up']=tisup\n     df['time_hist_up']=tihup\n     df['time_hist2_up']=tihup2\n     df['count_upp']=cupp\n     \n     df['count_up1']=cup1\n     df['count_up2']=cup2\n     df['count_up3']=cup3\n     df['count_up4']=cup4\n     df['count_up5']=cup5\n     df['count_up6']=cup6\n     df['count_up7']=cup7\n     \n     df['acc_sum_uc']=acsuc\n     df['acc_hist_uc']=achuc\n     df['acc_hist2_uc']=achuc2\n     df['count_uc']=cuc\n     df['tfl_uc']=tfluc\n     df['tfl_hist_uc']=tflhuc\n     df['tfl_hist2_uc']=tflhuc2\n     df['exp_sum_ub']=exsub\n     df['exp_hist_ub']=exhub\n     df['exp_hist2_ub']=exhub2\n     df['time_sum_ub']=tisub\n     df['time_hist_ub']=tihub\n     df['time_hist2_ub']=tihub2\n     df['count_ub']=cub\n         \n     df['acc_avg_u'] = (df['acc_sum_u'] / df['count_u']).astype('float32')\n     df['exp_avg_u'] = (df['exp_sum_u'] / df['count_u']).astype('float32')\n     df['time_avg_u'] = (df['time_sum_u'] / df['count_u']).astype('float32')\n     df['dif_avg_u'] = (df['dif_sum_u'] / df['count_u']).astype('float32')\n     df['dpt_avg_u'] = (df['dpt_sum_u'] / df['count_u']).astype('float32')\n     \n     #df['acc_avg_c'] = (df['acc_sum_c'] / df['count_c']).astype('float32')\n     #df['exp_avg_b'] = (df['exp_sum_b'] / df['count_b']).astype('float32')\n     #df['time_avg_b'] = (df['time_sum_b'] / df['count_b']).astype('float32')\n     \n     df['acc_avg_up'] = (df['acc_sum_up'] / df['count_up']).astype('float32')\n     df['exp_avg_up'] = (df['exp_sum_up'] / df['count_upp']).astype('float32')\n     df['time_avg_up'] = (df['time_sum_up'] /df['count_upp']).astype('float32')\n     \n     df['acc_avg_uc'] = (df['acc_sum_uc'] / df['count_uc']).astype('float32')    \n     df['exp_avg_ub'] = (df['exp_sum_ub'] / df['count_ub']).astype('float32')\n     df['time_avg_ub'] = (df['time_sum_ub'] /df['count_ub']).astype('float32')\n     return df\n    \ndef update_feats_sqlite(df, conn):\n     for cnt,row in enumerate(df[['content_type_id',\n                                       'answered_correctly',\n                                       'prior_question_had_explanation',\n                                       'prior_question_elapsed_time',\n                                       'acc_avg_c',\n                                       'user_id',\n                                       'part',\n                                       'prior_part',\n                                       'content_id',\n                                       'bundle_id',\n                                       'prior_bundle',\n                                       'timestamp']].values):\n         \n         content_type_id=int(row[0])\n         answered_correctly=int(row[1])\n         prior_question_had_explanation=int(row[2])\n         prior_question_elapsed_time=int(row[3])\n         acc_avg_c=float(row[4])\n         user_id=int(row[5])\n         part=int(row[6])\n         prior_part=int(row[7])\n         content_id=int(row[8])\n         bundle_id=int(row[9])\n         prior_bundle=int(row[10])\n         timestamp=int(row[11])\n         \n         acc_sum_u,acc_std_u,acc_hist_u,acc_hist2_u,exp_sum_u,exp_std_u,exp_hist_u,exp_hist2_u, time_sum_u,time_std_u,time_hist_u,time_hist2_u,dpt_sum_u,dpt_std_u,dpt_hist_u,dpt_hist2_u,dif_sum_u,dif_std_u,dif_hist_u,dif_hist2_u, count_u,count_lecture_u,last_time_u,last_time2_u,last_time3_u,last_time4_u,last_time5_u,last_time6_u,tfl_std_u,tfl_hist_u,tfl_hist2_u=get_u_feats(user_id,conn)\n         acc_sum_c,acc_std_c,count_c=get_c_feats(content_id,conn)\n         exp_sum_b,exp_std_b,time_sum_b,time_std_b,count_b=get_b_feats(bundle_id,conn)\n         acc_sum_up,acc_hist_up,acc_hist2_up,count_up,count_lecture_up,last_time_up,last_time2_up,tfl_hist_up,tfl_hist2_up,exp_sum_up,exp_hist_up,exp_hist2_up,time_sum_up,time_hist_up,time_hist2_up,count_upp=get_up_feats(user_id,part,conn)\n         acc_sum_uc,acc_hist_uc,acc_hist2_uc,count_uc,count_lecture_uc,last_time_uc,last_time2_uc,tfl_hist_uc,tfl_hist2_uc=get_uc_feats(user_id,content_id,conn)\n         exp_sum_ub,exp_hist_ub,exp_hist2_ub,time_sum_ub,time_hist_ub,time_hist2_ub,count_ub=get_ub_feats(user_id,bundle_id,conn)\n         count_up1,count_up2,count_up3,count_up4,count_up5,count_up6,count_up7=get_up_counts(user_id,conn)\n         \n         if content_type_id == 0:             \n             acc_std_u = update_std(answered_correctly, acc_std_u, acc_sum_u, count_u)\n             acc_sum_u += answered_correctly\n             acc_hist_u = answered_correctly+acc_hist_u*GAMMA\n             acc_hist2_u = answered_correctly+acc_hist2_u*GAMMA2\n             exp_std_u = update_std(prior_question_had_explanation, exp_std_u, exp_sum_u, count_u)\n             exp_sum_u += prior_question_had_explanation\n             exp_hist_u = prior_question_had_explanation+exp_hist_u*GAMMA\n             exp_hist2_u = prior_question_had_explanation+exp_hist2_u*GAMMA2\n             time_std_u = update_std(prior_question_elapsed_time, time_std_u, time_sum_u, count_u)\n             time_sum_u += prior_question_elapsed_time\n             time_hist_u = prior_question_elapsed_time+time_hist_u*GAMMA\n             time_hist2_u = prior_question_elapsed_time+time_hist2_u*GAMMA2\n             dpt_std_u = update_std((1-acc_avg_c)*answered_correctly, dpt_std_u, dpt_sum_u, count_u)\n             dpt_sum_u += (1-acc_avg_c)*answered_correctly\n             dpt_hist_u = (1-acc_avg_c)*answered_correctly+dpt_hist_u*GAMMA\n             dpt_hist2_u = (1-acc_avg_c)*answered_correctly+dpt_hist2_u*GAMMA2\n             dif_std_u = update_std(1-acc_avg_c, dif_std_u, dif_sum_u, count_u)\n             dif_sum_u += 1-acc_avg_c\n             dif_hist_u = 1-acc_avg_c+dif_hist_u*GAMMA\n             dif_hist2_u = 1-acc_avg_c+dif_hist2_u*GAMMA2\n             count_u += 1             \n                          \n             acc_sum_c += answered_correctly\n             exp_sum_b += prior_question_had_explanation\n             time_sum_b += prior_question_elapsed_time\n             count_c += 1             \n             count_b += 1             \n             \n             acc_sum_up += answered_correctly\n             acc_hist_up = answered_correctly+acc_hist_up*GAMMA\n             acc_hist2_up = answered_correctly+acc_hist2_up*GAMMA2\n             count_up += 1\n             \n             exp_sum_up += prior_question_had_explanation\n             exp_hist_up = prior_question_had_explanation+exp_hist_up*GAMMA\n             exp_hist2_up = prior_question_had_explanation+exp_hist2_up*GAMMA2\n             time_sum_up += prior_question_elapsed_time\n             time_hist_up = prior_question_elapsed_time+time_hist_up*GAMMA\n             time_hist2_up = prior_question_elapsed_time+time_hist2_up*GAMMA2\n             count_upp += 1\n            \n             acc_sum_uc += answered_correctly\n             acc_hist_uc = answered_correctly+acc_hist_uc*GAMMA\n             acc_hist2_uc = answered_correctly+acc_hist2_uc*GAMMA2\n             count_uc += 1\n                 \n             exp_sum_ub += prior_question_had_explanation\n             exp_hist_ub = prior_question_had_explanation+exp_hist_ub*GAMMA\n             exp_hist2_ub = prior_question_had_explanation+exp_hist2_ub*GAMMA2\n             time_sum_ub += prior_question_elapsed_time\n             time_hist_ub = prior_question_elapsed_time+time_hist_ub*GAMMA\n             time_hist2_ub = prior_question_elapsed_time+time_hist2_ub*GAMMA2\n             count_ub += 1\n             \n             if last_time_u < timestamp:\n                 tfl_std_u = update_std(timestamp-last_time_u, tfl_std_u, last_time_u, count_u-1)\n                 tfl_hist_u = timestamp-last_time_u+tfl_hist_u*GAMMA\n                 tfl_hist2_u = timestamp-last_time_u+tfl_hist2_u*GAMMA2\n                 last_time6_u = last_time5_u\n                 last_time5_u = last_time4_u\n                 last_time4_u = last_time3_u\n                 last_time3_u = last_time2_u\n                 last_time2_u = last_time_u\n                 last_time_u = timestamp\n                 \n             else:\n                 tfl_std_u = update_std(timestamp-last_time2_u, tfl_std_u, last_time2_u, count_u-1)\n                 \n                 \n             if last_time_up < timestamp:\n                 tfl_hist_up = timestamp-last_time_up+tfl_hist_up*GAMMA\n                 tfl_hist2_up = timestamp-last_time_up+tfl_hist2_up*GAMMA2\n                 last_time2_up = last_time_up\n                 last_time_up = timestamp\n                 \n             if last_time_uc < timestamp:\n                 tfl_hist_uc = timestamp-last_time_uc+tfl_hist_uc*GAMMA\n                 tfl_hist2_uc = timestamp-last_time_uc+tfl_hist2_uc*GAMMA2\n                 last_time2_uc = last_time_uc\n                 last_time_uc = timestamp\n                 \n             update_u_db(user_id,acc_sum_u,acc_std_u,acc_hist_u,acc_hist2_u,\n                    exp_sum_u,exp_std_u,exp_hist_u,exp_hist2_u,\n                    time_sum_u,time_std_u,time_hist_u,time_hist2_u,\n                    dpt_sum_u,dpt_std_u,dpt_hist_u,dpt_hist2_u,\n                    dif_sum_u,dif_std_u,dif_hist_u,dif_hist2_u,\n                    count_u,count_lecture_u,\n                    last_time_u,last_time2_u,last_time3_u,last_time4_u,last_time5_u,last_time6_u,\n                    tfl_std_u,tfl_hist_u,tfl_hist2_u,conn)\n             update_c_db(content_id,acc_sum_c,acc_std_c,count_c,conn)\n             update_b_db(bundle_id,exp_sum_b,exp_std_b,time_sum_b,time_std_b,count_b,conn)\n             update_up_db(user_id,part,\n                    acc_sum_up,acc_hist_up,acc_hist2_up,\n                    count_up,count_lecture_up,last_time_up,\n                    last_time2_up,tfl_hist_up,tfl_hist2_up,\n                    exp_sum_up,exp_hist_up,exp_hist2_up,\n                    time_sum_up,time_hist_up,time_hist2_up,count_upp,conn)\n             update_uc_db(user_id,content_id,\n                         acc_sum_uc,acc_hist_uc,acc_hist2_uc,\n                         count_uc,count_lecture_uc,last_time_uc,\n                         last_time2_uc,tfl_hist_uc,tfl_hist2_uc,conn)\n             update_ub_db(user_id,bundle_id,\n                    exp_sum_ub,exp_hist_ub,exp_hist2_ub,\n                    time_sum_ub,time_hist_ub,time_hist2_ub,count_ub,conn)\n                \n         if content_type_id == 1:\n             count_lecture_u += 1\n             count_lecture_up += 1\n             update_u_db(user_id,acc_sum_u,acc_std_u,acc_hist_u,acc_hist2_u,\n                    exp_sum_u,exp_std_u,exp_hist_u,exp_hist2_u,\n                    time_sum_u,time_std_u,time_hist_u,time_hist2_u,\n                    dpt_sum_u,dpt_std_u,dpt_hist_u,dpt_hist2_u,\n                    dif_sum_u,dif_std_u,dif_hist_u,dif_hist2_u,\n                    count_u,count_lecture_u,\n                    last_time_u,last_time2_u,last_time3_u,last_time4_u,last_time5_u,last_time6_u,\n                    tfl_std_u,tfl_hist_u,tfl_hist2_u,conn)\n             update_up_db(user_id,part,\n                    acc_sum_up,acc_hist_up,acc_hist2_up,\n                    count_up,count_lecture_up,last_time_up,\n                    last_time2_up,tfl_hist_up,tfl_hist2_up,\n                    exp_sum_up,exp_hist_up,exp_hist2_up,\n                    time_sum_up,time_hist_up,time_hist2_up,count_upp,conn)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"acc_sum_u_dict = defaultdict(int)\nacc_std_u_dict = defaultdict(float)\nacc_hist_u_dict = defaultdict(float)\nacc_hist2_u_dict = defaultdict(float)\nexp_sum_u_dict = defaultdict(int)\nexp_std_u_dict = defaultdict(float)\nexp_hist_u_dict = defaultdict(float)\nexp_hist2_u_dict = defaultdict(float)\ntime_sum_u_dict = defaultdict(int)\ntime_std_u_dict = defaultdict(float)\ntime_hist_u_dict = defaultdict(float)\ntime_hist2_u_dict = defaultdict(float)\ndpt_sum_u_dict = defaultdict(float)\ndpt_std_u_dict = defaultdict(float)\ndpt_hist_u_dict = defaultdict(float)\ndpt_hist2_u_dict = defaultdict(float)\ndif_sum_u_dict = defaultdict(float)\ndif_std_u_dict = defaultdict(float)\ndif_hist_u_dict = defaultdict(float)\ndif_hist2_u_dict = defaultdict(float)\ncount_u_dict = defaultdict(int)\ncount_lecture_u_dict = defaultdict(int)\nlast_time_u_dict = defaultdict(int)\nlast_time2_u_dict = defaultdict(int)\nlast_time3_u_dict = defaultdict(int)\nlast_time4_u_dict = defaultdict(int)\nlast_time5_u_dict = defaultdict(int)\nlast_time6_u_dict = defaultdict(int)\ntfl_std_u_dict = defaultdict(float)\ntfl_hist_u_dict = defaultdict(float)\ntfl_hist2_u_dict = defaultdict(float)\nacc_sum_c_dict = defaultdict(int)\nexp_sum_b_dict = defaultdict(int)\ntime_sum_b_dict = defaultdict(int)\nacc_std_c_dict = defaultdict(float)\nexp_std_b_dict = defaultdict(float)\ntime_std_b_dict = defaultdict(float)\ncount_c_dict = defaultdict(int)\ncount_b_dict = defaultdict(int)\nacc_sum_up_dict = defaultdict(lambda:defaultdict(int))\nacc_hist_up_dict = defaultdict(lambda:defaultdict(float))\nacc_hist2_up_dict = defaultdict(lambda:defaultdict(float))\ncount_up_dict = defaultdict(lambda:defaultdict(int))\ncount_lecture_up_dict = defaultdict(lambda:defaultdict(int))\nlast_time_up_dict = defaultdict(lambda:defaultdict(int))\nlast_time2_up_dict = defaultdict(lambda:defaultdict(int))\ntfl_hist_up_dict = defaultdict(lambda:defaultdict(int))\ntfl_hist2_up_dict = defaultdict(lambda:defaultdict(int))\nexp_sum_up_dict = defaultdict(lambda:defaultdict(int))\nexp_hist_up_dict = defaultdict(lambda:defaultdict(float))\nexp_hist2_up_dict = defaultdict(lambda:defaultdict(float))\ntime_sum_up_dict = defaultdict(lambda:defaultdict(int))\ntime_hist_up_dict = defaultdict(lambda:defaultdict(float))\ntime_hist2_up_dict = defaultdict(lambda:defaultdict(float))\ncount_upp_dict = defaultdict(lambda:defaultdict(int))  \nacc_sum_uc_dict = defaultdict(lambda:defaultdict(int))\nacc_hist_uc_dict = defaultdict(lambda:defaultdict(float))\nacc_hist2_uc_dict = defaultdict(lambda:defaultdict(float)) \ncount_uc_dict = defaultdict(lambda:defaultdict(int))\ncount_lecture_uc_dict = defaultdict(lambda:defaultdict(int))\nlast_time_uc_dict = defaultdict(lambda:defaultdict(int))\nlast_time2_uc_dict = defaultdict(lambda:defaultdict(int))\ntfl_hist_uc_dict = defaultdict(lambda:defaultdict(float))\ntfl_hist2_uc_dict = defaultdict(lambda:defaultdict(float))\nexp_sum_ub_dict = defaultdict(lambda:defaultdict(int))\nexp_hist_ub_dict = defaultdict(lambda:defaultdict(float))\nexp_hist2_ub_dict = defaultdict(lambda:defaultdict(float))\ntime_sum_ub_dict = defaultdict(lambda:defaultdict(int))\ntime_hist_ub_dict = defaultdict(lambda:defaultdict(float))\ntime_hist2_ub_dict = defaultdict(lambda:defaultdict(float))\ncount_ub_dict = defaultdict(lambda:defaultdict(int))\n\ntrain = add_feats(train, acc_sum_u_dict, acc_std_u_dict, acc_hist_u_dict, acc_hist2_u_dict, \n                    exp_sum_u_dict, exp_std_u_dict, exp_hist_u_dict, exp_hist2_u_dict, \n                    time_sum_u_dict, time_std_u_dict, time_hist_u_dict, time_hist2_u_dict, \n                    dpt_sum_u_dict, dpt_std_u_dict, dpt_hist_u_dict, dpt_hist2_u_dict,\n                    dif_sum_u_dict, dif_std_u_dict, dif_hist_u_dict, dif_hist2_u_dict,\n                    count_u_dict, count_lecture_u_dict, \n                    last_time_u_dict, last_time2_u_dict, last_time3_u_dict,\n                    last_time4_u_dict, last_time5_u_dict, last_time6_u_dict,\n                    tfl_std_u_dict, tfl_hist_u_dict, tfl_hist2_u_dict, \n                    acc_sum_c_dict, exp_sum_b_dict, time_sum_b_dict,\n                    acc_std_c_dict, exp_std_b_dict, time_std_b_dict,\n                    count_c_dict, count_b_dict,\n                    acc_sum_up_dict, acc_hist_up_dict, acc_hist2_up_dict, \n                    count_up_dict, count_lecture_up_dict, last_time_up_dict, last_time2_up_dict,\n                    tfl_hist_up_dict,tfl_hist2_up_dict,\n                    exp_sum_up_dict, exp_hist_up_dict, exp_hist2_up_dict, \n                    time_sum_up_dict, time_hist_up_dict, time_hist2_up_dict, \n                    count_upp_dict, \n                    acc_sum_uc_dict, acc_hist_uc_dict, acc_hist2_uc_dict,  \n                    count_uc_dict, count_lecture_uc_dict, last_time_uc_dict, last_time2_uc_dict,\n                    tfl_hist_uc_dict, tfl_hist2_uc_dict,\n                    exp_sum_ub_dict, exp_hist_ub_dict, exp_hist2_ub_dict,\n                    time_sum_ub_dict, time_hist_ub_dict, time_hist2_ub_dict, \n                    count_ub_dict)\nvalid = add_feats(valid, acc_sum_u_dict, acc_std_u_dict, acc_hist_u_dict, acc_hist2_u_dict, \n                    exp_sum_u_dict, exp_std_u_dict, exp_hist_u_dict, exp_hist2_u_dict, \n                    time_sum_u_dict, time_std_u_dict, time_hist_u_dict, time_hist2_u_dict, \n                    dpt_sum_u_dict, dpt_std_u_dict, dpt_hist_u_dict, dpt_hist2_u_dict,\n                    dif_sum_u_dict, dif_std_u_dict, dif_hist_u_dict, dif_hist2_u_dict,\n                    count_u_dict, count_lecture_u_dict, \n                    last_time_u_dict, last_time2_u_dict, last_time3_u_dict,\n                    last_time4_u_dict, last_time5_u_dict, last_time6_u_dict,\n                    tfl_std_u_dict, tfl_hist_u_dict, tfl_hist2_u_dict, \n                    acc_sum_c_dict, exp_sum_b_dict, time_sum_b_dict,\n                    acc_std_c_dict, exp_std_b_dict, time_std_b_dict,\n                    count_c_dict, count_b_dict,\n                    acc_sum_up_dict, acc_hist_up_dict, acc_hist2_up_dict, \n                    count_up_dict, count_lecture_up_dict, last_time_up_dict, last_time2_up_dict,\n                    tfl_hist_up_dict,tfl_hist2_up_dict,\n                    exp_sum_up_dict, exp_hist_up_dict, exp_hist2_up_dict, \n                    time_sum_up_dict, time_hist_up_dict, time_hist2_up_dict, \n                    count_upp_dict, \n                    acc_sum_uc_dict, acc_hist_uc_dict, acc_hist2_uc_dict,  \n                    count_uc_dict, count_lecture_uc_dict, last_time_uc_dict, last_time2_uc_dict,\n                    tfl_hist_uc_dict, tfl_hist2_uc_dict,\n                    exp_sum_ub_dict, exp_hist_ub_dict, exp_hist2_ub_dict,\n                    time_sum_ub_dict, time_hist_ub_dict, time_hist2_ub_dict, \n                    count_ub_dict)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train = train.loc[train.index[train.content_type_id == False]]\nvalid = valid.loc[valid.index[valid.content_type_id == False]]\ntrain.drop('content_type_id', axis=1, inplace=True)\nvalid.drop('content_type_id', axis=1, inplace=True)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Add features related to diagnostic questions\nSome of first 30 questions were diagnostic questions.\nThe user cannnot read explanation when solving these questions."},{"metadata":{"trusted":true},"cell_type":"code","source":"diagnostic_questions=[[7900], [7876], [175], [1278], \n                      [2063, 2064, 2065], [2063, 2064, 2065], [2063, 2064, 2065], \n                      [3363, 3364, 3365], [3363, 3364, 3365], [3363, 3364, 3365], \n                      [2946, 2947, 2948], [2946, 2947, 2948], [2946, 2947, 2948], \n                      [2593, 2594, 2595], [2593, 2594, 2595], [2593, 2594, 2595], \n                      [4492], [4120], [4696], [6116], [6173], [6370], \n                      [6877, 6878, 6879, 6880], [6877, 6878, 6879, 6880], [6877, 6878, 6879, 6880], [6877, 6878, 6879, 6880], \n                      [7216, 7217, 7218, 7219], [7216, 7217, 7218, 7219], [7216, 7217, 7218, 7219], [7216, 7217, 7218, 7219]]\ndiagnostic_questions2=[128, 7860, 7922, 156, 51, 50, 7896, 7863, 152, 104, 108, 7900, 7901, 7971, 25, 183, 7926, 7927, 4, 7984, 45, 185, 55, 7876, 6, 172, 7898, 175, 100, 7859]\n\ndef add_is_diagnostic(df, diagnostic_u_dict,verbose=True):\n    isd = np.zeros(len(df), dtype=np.int8)\n    isds = np.zeros(len(df), dtype=np.int8)\n    for cnt,row in (enumerate(tqdm(df[['count_u','user_id','content_id']].values)) if verbose else enumerate(df[['count_u','user_id','content_id']].values)):\n    #for cnt,row in enumerate(df[['user_id','content_id','count_u']].values):\n        count_u=int(row[0])\n        user_id=int(row[1])\n        content_id=int(row[2])\n        isds[cnt] = diagnostic_u_dict[user_id]        \n        if count_u < 30:          \n            if content_id in diagnostic_questions[count_u]:\n                isd[cnt] =2\n                diagnostic_u_dict[user_id] += 1\n            elif content_id == diagnostic_questions2[count_u]:\n                isd[cnt] =1\n                diagnostic_u_dict[user_id] += 1\n    df['is_diagnostic']= isd\n    df['is_diagnostic_sum']= isds\n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"diagnostic_u_dict=defaultdict(int)\ntrain=add_is_diagnostic(train, diagnostic_u_dict)\nvalid=add_is_diagnostic(valid, diagnostic_u_dict)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Add trueskill features\nIdea to use true skill is from [the notebook](https://www.kaggle.com/zyy2016/0-780-unoptimized-lgbm-interesting-features) by @zyy2016. The code is from [the notebook](https://www.kaggle.com/neinun/understanding-microsoft-trueskill-with-riiid) by @neinun."},{"metadata":{"trusted":true},"cell_type":"code","source":"def win_probability(team1, team2):\n    delta_mu = team1.mu - team2.mu\n    sum_sigma = sum([team1.sigma ** 2, team2.sigma ** 2])\n    size = 2\n    denom = math.sqrt(size * (0.05 * 0.05) + sum_sigma)\n    ts = trueskill.global_env()\n    return ts.cdf(delta_mu / denom)\n\ndef add_trueskill(df, user_trueskill_dict, question_trueskill_dict, verbose=True):\n    wp = np.zeros(len(df), dtype=np.float32)\n    umu = np.zeros(len(df), dtype=np.float32)\n    usigma = np.zeros(len(df), dtype=np.float32)\n    qmu = np.zeros(len(df), dtype=np.float32)\n    qsigma = np.zeros(len(df), dtype=np.float32)\n    for cnt,row in (enumerate(tqdm(df[['user_id','content_id','answered_correctly']].values)) if verbose else enumerate(df[['user_id','content_id','answered_correctly']].values)):\n        user_id=int(row[0])\n        content_id=int(row[1])\n        answered_correctly=int(row[2])\n        old_user_rating = user_trueskill_dict[user_id]\n        old_question_rating = question_trueskill_dict[content_id]\n        wp[cnt] = win_probability(old_user_rating,old_question_rating)\n        umu[cnt] = old_user_rating.mu\n        usigma[cnt] = old_user_rating.sigma\n        qmu[cnt] = old_question_rating.mu\n        qsigma[cnt] = old_question_rating.sigma\n        if answered_correctly == 1:\n            new_user_rating,new_question_rating = rate_1vs1(old_user_rating,old_question_rating)\n        else:\n            new_question_rating,new_user_rating = rate_1vs1(old_question_rating,old_user_rating)\n        user_trueskill_dict[user_id] = new_user_rating\n        question_trueskill_dict[content_id] = new_question_rating\n        \n    df['wp_ts']=wp\n    df['umu_ts']=umu\n    df['usigma_ts']=usigma\n    df['qmu_ts']=qmu\n    df['qsigma_ts']=qsigma\n    return df\n\ndef update_trueskill(df, user_trueskill_dict, question_trueskill_dict, verbose=True):\n    for cnt,row in (enumerate(tqdm(df[['user_id','content_id','answered_correctly']].values)) if verbose else enumerate(df[['user_id','content_id','answered_correctly']].values)):\n        user_id=int(row[0])\n        content_id=int(row[1])\n        answered_correctly=int(row[2])\n        old_user_rating = user_trueskill_dict[user_id]\n        old_question_rating = question_trueskill_dict[content_id]\n        if answered_correctly == 1:\n            new_user_rating,new_question_rating = rate_1vs1(old_user_rating,old_question_rating)\n        else:\n            new_question_rating,new_user_rating = rate_1vs1(old_question_rating,old_user_rating)\n        user_trueskill_dict[user_id] = new_user_rating\n        question_trueskill_dict[content_id] = new_question_rating\n\ndef add_trueskill_without_update(df, user_trueskill_dict, question_trueskill_dict, verbose=True):\n    wp = np.zeros(len(df), dtype=np.float32)\n    umu = np.zeros(len(df), dtype=np.float32)\n    usigma = np.zeros(len(df), dtype=np.float32)\n    qmu = np.zeros(len(df), dtype=np.float32)\n    qsigma = np.zeros(len(df), dtype=np.float32)\n    for cnt,row in (enumerate(tqdm(df[['user_id','content_id']].values)) if verbose else enumerate(df[['user_id','content_id']].values)):\n        user_id=int(row[0])\n        content_id=int(row[1])\n        old_user_rating = user_trueskill_dict[user_id]\n        old_question_rating = question_trueskill_dict[content_id]\n        wp[cnt] = win_probability(old_user_rating,old_question_rating)\n        umu[cnt] = old_user_rating.mu\n        usigma[cnt] = old_user_rating.sigma\n        qmu[cnt] = old_question_rating.mu\n        qsigma[cnt] = old_question_rating.sigma\n        \n    df['wp_ts']=wp\n    df['umu_ts']=umu\n    df['usigma_ts']=usigma\n    df['qmu_ts']=qmu\n    df['qsigma_ts']=qsigma\n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"user_trueskill_dict = defaultdict(lambda:Rating())\nquestion_trueskill_dict = defaultdict(lambda:Rating())\ntrain=add_trueskill(train, user_trueskill_dict, question_trueskill_dict)\nvalid=add_trueskill(valid, user_trueskill_dict, question_trueskill_dict)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Add other columns and fillna"},{"metadata":{"trusted":true},"cell_type":"code","source":"def add_new_columns(df):\n    df['count_u_nondiagnostic']=df['count_u'] - df['is_diagnostic_sum']\n    df['exp_avg_u_corrected']=(df['exp_sum_u'] / (df['count_u_nondiagnostic'])).replace(np.inf,  0).fillna(value=0).astype('float32')\n    df['pp_count']=df['count_up1']+df['count_up3']+df['count_up4']+df['count_up6']+df['count_up7']\n    df['pp_ratio']=(df['pp_count']/df['count_u']).replace(np.inf,  np.nan)\n    df['listening_count']=df['count_up1']+df['count_up2']+df['count_up3']+df['count_up4']\n    df['listening_ratio']=(df['listening_count']/df['count_u']).replace(np.inf,  np.nan)\n    df['relative_dif']=((1-df['acc_avg_c']) / df['dif_avg_u'])\n    df['relative_dpt']=((1-df['acc_avg_c']) / df['dpt_avg_u']).replace(np.inf,  np.nan)\n    df['time_per_count_u']=(df['timestamp'] / df['count_u']).replace(np.inf,  np.nan)\n    df['notacc_sum_u']=df['count_u'] - df['acc_sum_u']\n    df['notacc_sum_up']=df['count_up'] - df['acc_sum_up']\n    df['notacc_sum_uc']=df['count_uc'] - df['acc_sum_uc']\n    df['notacc_sum_c']=df['count_c'] - df['acc_sum_c']\n    return df\n\ndef fillna_df(df):\n    df['acc_avg_u']=df['acc_avg_u'].fillna(value=0.6432132412788166).astype('float32')\n    df['exp_avg_u']=df['exp_avg_u'].fillna(value=0.8200458704602632).astype('float32')\n    df['time_avg_u']=df['time_avg_u'].fillna(value=25497.400011014917).astype('float32')\n    df['acc_avg_up']=df['acc_avg_up'].fillna(value=0.6416028023438022).astype('float32')\n    df['exp_avg_up']=df['exp_avg_up'].fillna(value=0.8654814629737391).astype('float32')\n    df['time_avg_up']=df['time_avg_up'].fillna(value=26303.666782974677).astype('float32')\n    df['acc_avg_c']=df['acc_avg_c'].fillna(value=0.6572577059673576).astype('float32')\n    df['exp_avg_b']=df['exp_avg_b'].fillna(value=0.9077998180208283).astype('float32')\n    df['time_avg_b']=df['time_avg_b'].fillna(value=26036.627957044348).astype('float32')\n    df['acc_avg_uc']=df['acc_avg_uc'].fillna(value=0.36168508143684996).astype('float32')\n    df['exp_avg_ub']=df['exp_avg_ub'].fillna(value=0.8596988233077392).astype('float32')\n    df['time_avg_ub']=df['time_avg_ub'].fillna(value=26215.743786481285).astype('float32')\n    df['time_per_count_u']=df['time_per_count_u'].fillna(value=18856.430915581953).astype('float32')\n    df['dpt_avg_u']=df['dpt_avg_u'].fillna(value=0.19122234479970104).astype('float32')\n    df['dif_avg_u']=df['dif_avg_u'].fillna(value=0.348174949891143).astype('float32')\n    df['relative_dif']=df['relative_dif'].fillna(value=0.9982924288104749).astype('float32')\n    df['relative_dpt']=df['relative_dpt'].fillna(value=1.924812794869729).astype('float32')\n    df['pp_ratio']=df['pp_ratio'].fillna(value=0.4008204857577879).astype('float32')\n    df['listening_ratio']=df['listening_ratio'].fillna(value=0.4540805930573127).astype('float32')    \n    return df","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train=add_new_columns(train)\nvalid=add_new_columns(valid)\ntrain=fillna_df(train)\nvalid=fillna_df(valid)","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Convert dictionaries to sqlite\nTo prevent OOM error, I converted dictonaries to sqlite DB. Idea to use sqlite is from the comments of @higepon and @calebeverett in [this discussion](https://www.kaggle.com/c/riiid-test-answer-prediction/discussion/202965)."},{"metadata":{"trusted":true},"cell_type":"code","source":"dbname = 'feat.db'\nconn = sqlite3.connect(dbname)\n# for u feats\ncur = conn.cursor()    \ncur.execute('CREATE TABLE u_feats (user_id INTEGER, \\\n            acc_sum_u INTEGER, \\\n            acc_std_u INTEGER, \\\n            acc_hist_u REAL, \\\n            acc_hist2_u REAL, \\\n            exp_sum_u INTEGER, \\\n            exp_std_u INTEGER, \\\n            exp_hist_u REAL, \\\n            exp_hist2_u REAL, \\\n            time_sum_u INTEGER, \\\n            time_std_u INTEGER, \\\n            time_hist_u REAL, \\\n            time_hist2_u REAL, \\\n            dpt_sum_u INTEGER, \\\n            dpt_std_u INTEGER, \\\n            dpt_hist_u REAL, \\\n            dpt_hist2_u REAL, \\\n            dif_sum_u INTEGER, \\\n            dif_std_u INTEGER, \\\n            dif_hist_u REAL, \\\n            dif_hist2_u REAL, \\\n            count_u INTEGER, \\\n            count_lecture_u INTEGER, \\\n            last_time_u INTEGER, \\\n            last_time2_u INTEGER, \\\n            last_time3_u INTEGER, \\\n            last_time4_u INTEGER, \\\n            last_time5_u INTEGER, \\\n            last_time6_u INTEGER, \\\n            tfl_std_u REAL, \\\n            tfl_hist_u REAL, \\\n            tfl_hist2_u REAL, \\\n            PRIMARY KEY (user_id))')\nconn.commit()\ncur = conn.cursor()\nuser_ids=list(count_u_dict.keys())\nfor user_id in tqdm(user_ids):\n    acc_sum_u=acc_sum_u_dict[user_id]\n    acc_std_u=acc_std_u_dict[user_id]\n    acc_hist_u=acc_hist_u_dict[user_id]\n    acc_hist2_u=acc_hist2_u_dict[user_id]\n    exp_sum_u=exp_sum_u_dict[user_id]\n    exp_std_u=exp_std_u_dict[user_id]\n    exp_hist_u=exp_hist_u_dict[user_id]\n    exp_hist2_u=exp_hist2_u_dict[user_id]\n    time_sum_u=time_sum_u_dict[user_id]\n    time_std_u=time_std_u_dict[user_id]\n    time_hist_u=time_hist_u_dict[user_id]\n    time_hist2_u=time_hist2_u_dict[user_id]\n    dpt_sum_u=dpt_sum_u_dict[user_id]\n    dpt_std_u=dpt_std_u_dict[user_id]\n    dpt_hist_u=dpt_hist_u_dict[user_id]\n    dpt_hist2_u=dpt_hist2_u_dict[user_id]\n    dif_sum_u=dif_sum_u_dict[user_id]\n    dif_std_u=dif_std_u_dict[user_id]\n    dif_hist_u=dif_hist_u_dict[user_id]\n    dif_hist2_u=dif_hist2_u_dict[user_id]\n    count_u=count_u_dict[user_id]\n    count_lecture_u=count_lecture_u_dict[user_id]\n    last_time_u=last_time_u_dict[user_id]\n    last_time2_u=last_time2_u_dict[user_id]\n    last_time3_u=last_time3_u_dict[user_id]\n    last_time4_u=last_time4_u_dict[user_id]\n    last_time5_u=last_time5_u_dict[user_id]\n    last_time6_u=last_time6_u_dict[user_id]\n    tfl_std_u=tfl_std_u_dict[user_id]\n    tfl_hist_u=tfl_hist_u_dict[user_id]\n    tfl_hist2_u=tfl_hist2_u_dict[user_id]\n    cur.execute(\"INSERT INTO u_feats VALUES (:user_id,\\\n                :acc_sum_u,:acc_std_u,:acc_hist_u,:acc_hist2_u,\\\n                :exp_sum_u,:exp_std_u,:exp_hist_u,:exp_hist2_u,\\\n                :time_sum_u,:time_std_u,:time_hist_u,:time_hist2_u,\\\n                :dpt_sum_u,:dpt_std_u,:dpt_hist_u,:dpt_hist2_u,\\\n                :dif_sum_u,:dif_std_u,:dif_hist_u,:dif_hist2_u,\\\n                :count_u,:count_lecture_u,\\\n                :last_time_u,:last_time2_u,:last_time3_u,:last_time4_u,:last_time5_u,:last_time6_u,\\\n                :tfl_std_u,:tfl_hist_u,:tfl_hist2_u)\",(user_id,acc_sum_u,acc_std_u,acc_hist_u,acc_hist2_u,exp_sum_u,exp_std_u,exp_hist_u,exp_hist2_u,time_sum_u,time_std_u,time_hist_u,time_hist2_u,dpt_sum_u,dpt_std_u,dpt_hist_u,dpt_hist2_u,dif_sum_u,dif_std_u,dif_hist_u,dif_hist2_u,count_u,count_lecture_u,last_time_u,last_time2_u,last_time3_u,last_time4_u,last_time5_u,last_time6_u,tfl_std_u,tfl_hist_u,tfl_hist2_u))\nconn.commit()\n# for c feats\ncur = conn.cursor()    \ncur.execute('CREATE TABLE c_feats (content_id INTEGER,\\\n            acc_sum_c INTEGER, \\\n            acc_std_c REAL, \\\n            count_c INTEGER, \\\n            PRIMARY KEY (content_id))')\n\nconn.commit()\ncur = conn.cursor()\ncontent_ids=list(count_c_dict.keys())\nfor content_id in tqdm(content_ids):\n    acc_sum_c=acc_sum_c_dict[content_id]\n    acc_std_c=acc_std_c_dict[content_id]\n    count_c=count_c_dict[content_id]\n    cur.execute(\"INSERT INTO c_feats VALUES (:content_id,:acc_sum_c,:acc_std_c, :count_c)\",(content_id,acc_sum_c,acc_std_c, count_c))\nconn.commit()\n# for b feats\ncur = conn.cursor()     \ncur.execute('CREATE TABLE b_feats (bundle_id INTEGER,\\\n            exp_sum_b INTEGER, \\\n            exp_std_b REAL, \\\n            time_sum_b INTEGER, \\\n            time_std_b REAL, \\\n            count_b INTEGER, \\\n            PRIMARY KEY (bundle_id))')    \nconn.commit()\ncur = conn.cursor()\nbundle_ids=list(count_b_dict.keys())\nfor bundle_id in tqdm(bundle_ids):\n    exp_sum_b=exp_sum_b_dict[bundle_id]\n    exp_std_b=exp_std_b_dict[bundle_id]\n    time_sum_b=time_sum_b_dict[bundle_id]\n    time_std_b=time_std_b_dict[bundle_id]\n    count_b=count_b_dict[bundle_id]\n    cur.execute(\"INSERT INTO b_feats VALUES (:bundle_id,:exp_sum_b,:exp_std_b, :time_sum_b,:time_std_b, :count_b)\",(bundle_id,exp_sum_b,exp_std_b, time_sum_b,time_std_b, count_b))\nconn.commit()\n# for up feats\ncur = conn.cursor()    \ncur.execute('CREATE TABLE up_feats (user_id INTEGER, part INTEGER,\\\n            acc_sum_up INTEGER, \\\n            acc_hist_up REAL, \\\n            acc_hist2_up REAL, \\\n            count_up INTEGER, \\\n            count_lecture_up INTEGER, \\\n            last_time_up INTEGER, \\\n            last_time2_up INTEGER, \\\n            tfl_hist_up REAL, \\\n            tfl_hist2_up REAL, \\\n            exp_sum_up INTEGER, \\\n            exp_hist_up REAL, \\\n            exp_hist2_up REAL, \\\n            time_sum_up INTEGER, \\\n            time_hist_up REAL, \\\n            time_hist2_up REAL, \\\n            count_upp INTEGER, \\\n            PRIMARY KEY (user_id, part))')\nconn.commit()\ncur = conn.cursor()\nuser_ids=list(count_up_dict.keys())\nfor user_id in tqdm(user_ids):\n    parts=list(count_up_dict[user_id].keys())\n    for part in parts:\n        acc_sum_up=acc_sum_up_dict[user_id][part]\n        acc_hist_up=acc_hist_up_dict[user_id][part]\n        acc_hist2_up=acc_hist2_up_dict[user_id][part]\n        count_up=count_up_dict[user_id][part]\n        count_lecture_up=count_lecture_up_dict[user_id][part]\n        last_time_up=last_time_up_dict[user_id][part]\n        last_time2_up=last_time2_up_dict[user_id][part]\n        tfl_hist_up=tfl_hist_up_dict[user_id][part]\n        tfl_hist2_up=tfl_hist2_up_dict[user_id][part]\n        exp_sum_up=exp_sum_up_dict[user_id][part]\n        exp_hist_up=exp_hist_up_dict[user_id][part]\n        exp_hist2_up=exp_hist2_up_dict[user_id][part]\n        time_sum_up=time_sum_up_dict[user_id][part]\n        time_hist_up=time_hist_up_dict[user_id][part]\n        time_hist2_up=time_hist2_up_dict[user_id][part]\n        count_upp=count_upp_dict[user_id][part]\n        cur.execute(\"INSERT INTO up_feats VALUES (:user_id,:part,\\\n                    :acc_sum_up,:acc_hist_up, :acc_hist2_up,\\\n                    :count_up,:count_lecture_up,\\\n                    :last_time_up,:last_time2_up,\\\n                    :tfl_hist_up,:tfl_hist2_up,\\\n                    :exp_sum_up,:exp_hist_up, :exp_hist2_up,\\\n                    :time_sum_up,:time_hist_up, :time_hist2_up,\\\n                    :count_upp)\",(user_id,part,acc_sum_up, acc_hist_up, acc_hist2_up,count_up,count_lecture_up,last_time_up,last_time2_up,tfl_hist_up,tfl_hist2_up,exp_sum_up,exp_hist_up, exp_hist2_up,time_sum_up,time_hist_up, time_hist2_up,count_upp))\nconn.commit()\n# for uc feats\ncur = conn.cursor()    \ncur.execute('CREATE TABLE uc_feats (user_id INTEGER, content_id INTEGER,\\\n            acc_sum_uc INTEGER, \\\n            acc_hist_uc REAL, \\\n            acc_hist2_uc REAL, \\\n            count_uc INTEGER, \\\n            count_lecture_uc INTEGER, \\\n            last_time_uc INTEGER, \\\n            last_time2_uc INTEGER, \\\n            tfl_hist_uc REAL, \\\n            tfl_hist2_uc REAL, \\\n            PRIMARY KEY (user_id, content_id))')\n\nconn.commit()\ncur = conn.cursor()\nuser_ids=list(count_uc_dict.keys())\nfor user_id in tqdm(user_ids):\n    content_ids=list(count_uc_dict[user_id].keys())\n    for content_id in content_ids:\n        acc_sum_uc=acc_sum_uc_dict[user_id][content_id]\n        acc_hist_uc=acc_hist_uc_dict[user_id][content_id]\n        acc_hist2_uc=acc_hist2_uc_dict[user_id][content_id]\n        count_uc=count_uc_dict[user_id][content_id]\n        count_lecture_uc=count_lecture_uc_dict[user_id][content_id]\n        last_time_uc=last_time_uc_dict[user_id][content_id]\n        last_time2_uc=last_time2_uc_dict[user_id][content_id]\n        tfl_hist_uc=tfl_hist_uc_dict[user_id][content_id]\n        tfl_hist2_uc=tfl_hist2_uc_dict[user_id][content_id]\n        cur.execute(\"INSERT INTO uc_feats VALUES (:user_id,:content_id,:acc_sum_uc,:acc_hist_uc, :acc_hist2_uc,:count_uc,:count_lecture_uc,:last_time_uc,:last_time2_uc,:tfl_hist_uc,:tfl_hist2_uc)\",(user_id,content_id,acc_sum_uc, acc_hist_uc, acc_hist2_uc,count_uc,count_lecture_uc,last_time_uc,last_time2_uc,tfl_hist_uc,tfl_hist2_uc))\nconn.commit()\n#for ub feats\ncur = conn.cursor()\ncur.execute('CREATE TABLE ub_feats (user_id INTEGER, bundle_id INTEGER,\\\n            exp_sum_ub INTEGER, \\\n            exp_hist_ub REAL, \\\n            exp_hist2_ub REAL, \\\n            time_sum_ub INTEGER, \\\n            time_hist_ub REAL, \\\n            time_hist2_ub REAL, \\\n            count_ub REAL, \\\n            PRIMARY KEY (user_id, bundle_id))')\nconn.commit()\n\ncur = conn.cursor()\nuser_ids=list(count_ub_dict.keys())\nfor user_id in tqdm(user_ids):\n    bundle_ids=list(count_ub_dict[user_id].keys())\n    for bundle_id in bundle_ids:\n        exp_sum_ub=exp_sum_ub_dict[user_id][bundle_id]\n        exp_hist_ub=exp_hist_ub_dict[user_id][bundle_id]\n        exp_hist2_ub=exp_hist2_ub_dict[user_id][bundle_id]\n        time_sum_ub=time_sum_ub_dict[user_id][bundle_id]\n        time_hist_ub=time_hist_ub_dict[user_id][bundle_id]\n        time_hist2_ub=time_hist2_ub_dict[user_id][bundle_id]\n        count_ub=count_ub_dict[user_id][bundle_id]\n        cur.execute(\"INSERT INTO ub_feats VALUES (:user_id,:bundle_id,:exp_sum_ub,:exp_hist_ub, :exp_hist2_ub,:time_sum_ub,:time_hist_ub,:time_hist2_ub,:count_ub)\",(user_id,bundle_id,exp_sum_ub,exp_hist_ub, exp_hist2_ub,time_sum_ub,time_hist_ub,time_hist2_ub,count_ub))\nconn.commit()\nconn.close()","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Train light GBM"},{"metadata":{},"cell_type":"markdown","source":"The meanings of abbreviations were as follows \n- acc: answered_correctly\n- exp: prior_question_had_explanation\n- time: prior_question_elapsed_time\n- count: number of trial\n- count_lecture: number of lecture\n- tfl: timestamp from last nth trial to that from n+1th trial\n- dif: difficulty (1 - content-wise accuracy)\n- dpt: difficulty point (add difficulty if users answer was correct)\n- avg: average\n- std: standard deviation\n- sum: sum\n- hist: historical average with temporal decay of gamma 0.75 \n- hist2: historical average with temporal decay of gamma 0.25\n- u: user-wise\n- t: tag-wise\n- tg: taggroup-wise\n- p: part-wise\n- c: content-wise\n- b: bundle-wise\n- uc: user-content-wise\n- ub: user-bundle-wise\n- up: user-part-wise\n- time_per_count_u: timestamp/count_u\n- count_u_nondiagnostic: user-wise count of non-diagnostic questions\n- exp_avg_u_corrected: user-wise average of prior_question_had_explanation corrected for diagnostic questions. Th user cannot read explanation for diagnostic questions.\n- pp_ratio: ratio of paid part (part1, 3, 4, 6, 7) trials per all trials, only paid users of SANTA app can solve them if it is not diagnostic questions\n- listening_ratio: ratio of listening part trials per all trials\n- relative_dif: relative difficulty of questions calculated with acc_avg_c / dif_avg_u\n- notacc_sum: sum of failed trials\n- wp_ts: trueskill feature of win probability\n- umu_ts, usigma_ts, qmu_ts, qsigma_ts: trueskill features of mu, and sigma of user and content"},{"metadata":{"trusted":true},"cell_type":"code","source":"TARGET = 'answered_correctly'\n\nFEATS = ['timestamp', 'prior_question_elapsed_time','prior_question_had_explanation', 'part', \n        'acc_avg_t','exp_avg_t', 'time_avg_t', \n        'acc_avg_tg','exp_avg_tg', 'time_avg_tg',\n        'acc_sum_tg', 'exp_sum_tg','time_sum_tg', \n        'acc_std_tg','exp_std_tg','time_std_tg',\n        'acc_avg_p', 'exp_avg_p','time_avg_p', \n        'acc_sum_p', 'exp_sum_p','time_sum_p', \n        'acc_std_p','exp_std_p','time_std_p',        \n        'acc_avg_c', 'exp_avg_b', 'time_avg_b',\n        'acc_sum_c', 'exp_sum_b','time_sum_b', \n        'acc_std_c','exp_std_b','time_std_b',\n        'count_c', \n        'acc_avg_u', 'exp_avg_u','time_avg_u', \n        'acc_sum_u','exp_sum_u', 'time_sum_u', \n        'acc_hist_u', 'exp_hist_u', 'time_hist_u', \n        'acc_hist2_u','exp_hist2_u', 'time_hist2_u',\n        'tfl_u','tfl2_u','tfl3_u','tfl4_u','tfl5_u',\n        'tfl_hist_u','tfl_hist2_u',\n        'dpt_avg_u','dpt_sum_u', 'dpt_hist_u', 'dpt_hist2_u',\n        'dif_avg_u', 'dif_sum_u', 'dif_hist_u','dif_hist2_u',\n        'count_u','count_lecture_u', \n        'acc_avg_up','exp_avg_up', 'time_avg_up', \n        'acc_sum_up', 'exp_sum_up', 'time_sum_up',\n        'acc_hist_up', 'exp_hist_up', 'time_hist_up',\n        'acc_hist2_up', 'time_hist2_up','exp_hist2_up', \n        'tfl_up','tfl_hist_up','tfl_hist2_up',\n        'count_up', \n        'count_lecture_up', \n        'acc_avg_uc','exp_avg_ub', 'time_avg_ub',\n        'acc_sum_uc','exp_sum_ub', 'time_sum_ub',\n        'acc_hist_uc', 'exp_hist_ub','time_hist_ub', \n        'acc_hist2_uc', 'exp_hist2_ub','time_hist2_ub',\n        'tfl_uc','tfl_hist_uc','tfl_hist2_uc',\n        'count_uc','time_per_count_u','pp_ratio',\n        'exp_avg_u_corrected',\n        'count_u_nondiagnostic','pp_count',\n        'is_diagnostic_sum','listening_ratio',\n        'relative_dif',\n        'notacc_sum_u','notacc_sum_up','notacc_sum_uc',\n        'wp_ts','umu_ts','usigma_ts','qmu_ts','qsigma_ts']","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"The code to convert dataframe to array is from @markwijkhuizen in [this discussion](https://www.kaggle.com/c/riiid-test-answer-prediction/discussion/198245)"},{"metadata":{"trusted":true},"cell_type":"code","source":"X_train=np.ndarray((len(train),len(FEATS)),dtype='float32')\nfor idx,feature in enumerate(FEATS):\n    X_train[:,idx]=train[feature].values.astype(np.float32)\nY_train=train[TARGET].values.astype('float32')\nX_valid=valid[FEATS].values.astype('float32')\nY_valid=valid[TARGET].values.astype('float32')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"dtrain = lgb.Dataset(X_train, Y_train, feature_name=FEATS)\ndvalid = lgb.Dataset(X_valid, Y_valid, feature_name=FEATS)  ","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"params = {\n        'objective': 'binary',\n        'lambda_l1': 9.5, \n        'lambda_l2': 0.005, \n        'num_leaves': 256, \n        'feature_fraction': 0.8, \n        'bagging_fraction': 0.9, \n        'bagging_freq': 6,\n        'min_child_samples': 20\n        #,'device':'gpu'\n    }\nlgb_model = lgb.train(params,\n                    #{'objective': 'binary'}, \n                    dtrain,\n                    valid_sets=[dtrain, dvalid],\n                    verbose_eval=100,\n                    num_boost_round=10000,\n                    early_stopping_rounds=100\n                    #,categorical_feature = ['content_id','part']\n                )\n\nlgb_result = lgb_model.predict(X_valid, num_iteration=lgb_model.best_iteration)\nprint('auc:', roc_auc_score(Y_valid,lgb_result))","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Inference\nI use [time series emulator](https://www.kaggle.com/its7171/time-series-api-iter-test-emulator) by @its7171 to debug my submission."},{"metadata":{"trusted":true},"cell_type":"code","source":"class Iter_Valid(object):\n    def __init__(self, df, max_user=1000):\n        df = df.reset_index(drop=True)\n        self.df = df\n        self.user_answer = df['user_answer'].astype(str).values\n        self.answered_correctly = df['answered_correctly'].astype(str).values\n        df['prior_group_responses'] = \"[]\"\n        df['prior_group_answers_correct'] = \"[]\"\n        self.sample_df = df[df['content_type_id'] == 0][['row_id']]\n        self.sample_df['answered_correctly'] = 0\n        self.len = len(df)\n        self.user_id = df.user_id.values\n        self.task_container_id = df.task_container_id.values\n        self.content_type_id = df.content_type_id.values\n        self.max_user = max_user\n        self.current = 0\n        self.pre_user_answer_list = []\n        self.pre_answered_correctly_list = []\n\n    def __iter__(self):\n        return self\n    \n    def fix_df(self, user_answer_list, answered_correctly_list, pre_start):\n        df= self.df[pre_start:self.current].copy()\n        sample_df = self.sample_df[pre_start:self.current].copy()\n        df.loc[pre_start,'prior_group_responses'] = '[' + \",\".join(self.pre_user_answer_list) + ']'\n        df.loc[pre_start,'prior_group_answers_correct'] = '[' + \",\".join(self.pre_answered_correctly_list) + ']'\n        self.pre_user_answer_list = user_answer_list\n        self.pre_answered_correctly_list = answered_correctly_list\n        return df, sample_df\n\n    def __next__(self):\n        added_user = set()\n        pre_start = self.current\n        pre_added_user = -1\n        pre_task_container_id = -1\n        pre_content_type_id = -1\n        user_answer_list = []\n        answered_correctly_list = []\n        while self.current < self.len:\n            crr_user_id = self.user_id[self.current]\n            crr_task_container_id = self.task_container_id[self.current]\n            crr_content_type_id = self.content_type_id[self.current]\n            if crr_user_id in added_user and (crr_user_id != pre_added_user or (crr_task_container_id != pre_task_container_id and crr_content_type_id == 0 and pre_content_type_id == 0)):\n                # known user(not prev user or (differnt task container and both question))\n                return self.fix_df(user_answer_list, answered_correctly_list, pre_start)\n            if len(added_user) == self.max_user:\n                if  crr_user_id == pre_added_user and (crr_task_container_id == pre_task_container_id or crr_content_type_id == 1):\n                    user_answer_list.append(self.user_answer[self.current])\n                    answered_correctly_list.append(self.answered_correctly[self.current])\n                    self.current += 1\n                    continue\n                else:\n                    return self.fix_df(user_answer_list, answered_correctly_list, pre_start)\n            added_user.add(crr_user_id)\n            pre_added_user = crr_user_id\n            pre_task_container_id = crr_task_container_id\n            pre_content_type_id = crr_content_type_id\n            user_answer_list.append(self.user_answer[self.current])\n            answered_correctly_list.append(self.answered_correctly[self.current])\n            self.current += 1\n        if pre_start < self.current:\n            return self.fix_df(user_answer_list, answered_correctly_list, pre_start)\n        else:\n            raise StopIteration()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"validaten_flg = False\nif validaten_flg:\n    target_df = pd.read_pickle('../input/riiid-cross-validation-files/cv1_train.pickle')\n    # I use the middle 2.5M range here.\n    target_df = target_df[50_000_000:52_500_000]\n    iter_test = Iter_Valid(target_df,max_user=1000)\n    predicted = []\n    def set_predict(df):\n        predicted.append(df)\nelse:\n    import riiideducation\n    env = riiideducation.make_env()\n    iter_test = env.iter_test()\n    set_predict = env.predict","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Load model and data\nI preprocessed data and trained model on my local PC.\nI loaded them here."},{"metadata":{"trusted":true},"cell_type":"code","source":"lgb_model = pickle.load(open('/kaggle/input/riid-preprocessed/lgb_model.pkl', 'rb'))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"from pylab import rcParams\nrcParams['figure.figsize'] = 7, 15\nax = lgb.plot_importance(lgb_model)\nax.figure.savefig('lgb_model_feature_importace.png')\nax = lgb.plot_importance(lgb_model, importance_type = \"gain\")\nax.figure.savefig('lgb_model_feature_importace_gain.png')","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"prior_b_dict = pickle_load_dill('../input/riid-preprocessed/prior_b_dict.pickle')\ndiagnostic_u_dict = pickle_load_dill('../input/riid-preprocessed/diagnostic_u_dict.pickle')    \nquestion_trueskill_dict = pickle_load_dill('../input/riid-preprocessed/question_trueskill_dict.pickle')\nuser_trueskill_dict = pickle_load_dill('../input/riid-preprocessed/user_trueskill_dict.pickle')","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"To use sqlite DB, we have to copy it to working directory."},{"metadata":{"trusted":true},"cell_type":"code","source":"!cp -r '../input/riid-preprocessed/feat_new.db' ./ \ndbname = './feat_new.db'","execution_count":null,"outputs":[]},{"metadata":{},"cell_type":"markdown","source":"# Submit"},{"metadata":{"trusted":true},"cell_type":"code","source":"pbar = tqdm(total=2500000)\nprevious_test_df = None\ncounter = 0\nconn = sqlite3.connect(dbname) \nfor (test_df, sample_prediction_df) in iter_test:\n    counter += 1\n    if previous_test_df is not None:\n        previous_test_df[TARGET] = eval(test_df[\"prior_group_answers_correct\"].iloc[0])\n        update_feats_sqlite(previous_test_df, conn)\n        update_trueskill(previous_test_df, user_trueskill_dict, question_trueskill_dict, verbose=False)\n        if not counter % 100:\n            conn.commit()\n    test_df.timestamp=(test_df.timestamp/1000).astype('int32')\n    test_df = add_prior_b(test_df, questions_cb, questions_bpt, prior_b_dict,verbose=False)\n    test_df['col_for_merge']=(test_df['content_id'].astype('int32')*10+test_df['content_type_id']).astype('int32')\n    test_df = test_df.merge(content_df, how = 'left', on = 'col_for_merge')\n    test_df.content_id=test_df.content_id.astype('int32')\n    test_df['prior_question_elapsed_time'] = test_df['prior_question_elapsed_time'].fillna(25439.41)\n    test_df['prior_question_had_explanation'] = test_df['prior_question_had_explanation'].fillna(False).astype('int8')\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_feats_without_update_sqlite(test_df, conn)\n    test_df = add_is_diagnostic(test_df, diagnostic_u_dict,verbose=False)\n    test_df = add_trueskill_without_update(test_df, user_trueskill_dict, question_trueskill_dict, verbose=False)\n    test_df = add_new_columns(test_df)\n    test_df = fillna_df(test_df)\n    lgb_result =  lgb_model.predict(test_df[FEATS])\n    test_df[TARGET] = lgb_result\n    set_predict(test_df[['row_id', TARGET]])\n    pbar.update(len(test_df))","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}