{"cells":[{"metadata":{},"cell_type":"markdown","source":"# pyDataTable Starter\n\nFollowing on from Vopani's kernel ( https://www.kaggle.com/rohanrao/riiid-with-blazing-fast-rid) outlining the benefits of pydatatable over pandas in terms of memory usage, I have decided to right up a LGBM model using a datatbale processed using pydatatable instead of pandas. \n\n# So, is it better and does it solve memory problems? \n\nI would say that that this is a good alternative for preprocessing the data but out-of-memory still becomes an issue. I'm sure I could have written this notebook to be somehwat more memory efficient though ....\n\nAlso, one draw back is that I found I had issues with using categorical features with the LGBM after using pydatatable, which I didn't encounter with pandas so I ended up just mean encoding it. Also the env_iter method seems to only be compatible with pandas and not pydatatable objects so we need to convert the aggregated pydatatables to pandas dataframes first before using them in joins when dealing with the test data, but this is a small fix. \n\nI began writing this before finding out that BigQuery is okay for use in this competition and I think this will be my next approach but I have shared this as a point of interest."},{"metadata":{"_cell_guid":"b1076dfc-b9ad-4769-8c92-a6c4dae69d19","_kg_hide-output":false,"_uuid":"8f2839f25d086af736a60e9eeb907d3b93b6e0e5","execution":{"iopub.execute_input":"2020-10-08T17:18:38.703648Z","iopub.status.busy":"2020-10-08T17:18:38.70288Z","iopub.status.idle":"2020-10-08T17:18:58.57622Z","shell.execute_reply":"2020-10-08T17:18:58.575394Z"},"papermill":{"duration":19.889466,"end_time":"2020-10-08T17:18:58.576363","exception":false,"start_time":"2020-10-08T17:18:38.686897","status":"completed"},"tags":[],"trusted":true},"cell_type":"code","source":"# installation with internet\n# !pip install datatable==0.11.0","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# installation without internet\n!pip install ../input/python-datatable/datatable-0.11.0-cp37-cp37m-manylinux2010_x86_64.whl","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"import datatable as dt\nimport numpy as np\nfrom sklearn.preprocessing import LabelEncoder\nlb_make = LabelEncoder()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# saving the dataset in .jay (binary format)\n#timestamp\t\n#dt.fread(\"../input/riiid-test-answer-prediction/train.csv\", skip_to_line=90000000).to_jay(\"train.jay\")\n#train = dt.fread(\"train.jay\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# saving the dataset in .jay (binary format)\n#timestamp\t\ndt.fread(\"../input/riiid-test-answer-prediction/train.csv\", columns={\"timestamp\", \"user_id\", \"content_id\", \"content_type_id\",\"answered_correctly\", \"prior_question_elapsed_time\", \"prior_question_had_explanation\" }).to_jay(\"train.jay\")\ntrain = dt.fread(\"train.jay\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"from datatable import (dt, f, by, ifelse, update, sort,\n                      count, min, max, mean, sum, rowsum)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train = train[f.content_type_id==0,:]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#sort timestamp values ascending\ntrain = train[:, :, sort(f.timestamp)]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"num=round(0.8*98000000)\n\nuser_m_all = train[0:num, :]\nuser_m_all = user_m_all[:, mean(f.answered_correctly), by('user_id')]\nuser_m_all.names= ['user_id', 'user_mean']\n\nuser_m_all.key= (\"user_id\")\ntrain = train [:, :, dt.join(user_m_all)]\n\nuser_c_all = train[0:num, :]\nuser_c_all = user_c_all[:, count(f.answered_correctly), by('user_id')]\nuser_c_all.names= ['user_id', 'user_count']\n\nuser_c_all.key= (\"user_id\")\ntrain = train [:, :, dt.join(user_c_all)]\n\ncontent_m_all = train[0:num, :]\ncontent_m_all = content_m_all[:, mean(f.answered_correctly), by('content_id')]\ncontent_m_all.names= ['content_id', 'content_mean']\n\ncontent_m_all.key= (\"content_id\")\ntrain = train[:, :, dt.join(content_m_all)]\ntrain.tail()\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"# get question info\ndt.fread(\"../input/riiid-test-answer-prediction/questions.csv\", columns = {\"question_id\",\"part\", \"tags\"}).to_jay(\"questions.jay\")\nquestions = dt.fread(\"questions.jay\")\nquestions.names=['content_id', 'part', 'tags']\n\nquestions.key= (\"content_id\")\ntrain = train[:, :, dt.join(questions)]\n\npart_m_all = train[0:num,:]\npart_m_all = part_m_all[:, mean(f.answered_correctly), by('part')]\npart_m_all.names=[\"part\", \"part_mean\"]\n\npart_m_all.key= (\"part\")\ntrain = train[:, :, dt.join(part_m_all)]\ntrain.tail()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Can we get information from the question tag that is valuable?\n\ntag_m_all = train[0:num,:]\ntag_m_all = tag_m_all[:, mean(f.answered_correctly), by('tags')]\ntag_m_all.names=[\"tags\", \"tags_mean\"]\ntag_m_all.key= (\"tags\")\ntrain = train[:, :, dt.join(tag_m_all)]\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Deal with missing values\n\na= dt.math.isna(train[:,\"prior_question_elapsed_time\"])\nm = train[0:num,'prior_question_elapsed_time'].mean()\ntrain[a ,\"prior_question_elapsed_time\"] = m\n\na= dt.math.isna(train[:,\"user_count\"])\ntrain[a ,\"user_count\"] = 0\n\na= dt.math.isna(train[:,\"user_mean\"])\nm = train[0:num,\"user_mean\"].mean()\ntrain[a ,\"user_mean\"] = m\n\na= dt.math.isna(train[:,\"part_mean\"])\nm = train[0:num,\"part_mean\"].mean()\ntrain[a ,\"part_mean\"] = m\n\na= dt.math.isna(train[:,\"content_mean\"])\nm = train[0:num,\"content_mean\"].mean()\ntrain[a ,\"content_mean\"] = m\n\na= dt.math.isna(train[:,\"tags_mean\"])\nm = train[0:num,\"tags_mean\"].mean()\ntrain[a ,\"tags_mean\"] = m\n\na= dt.math.isna(train[:,\"prior_question_had_explanation\"])\ntrain[a ,\"prior_question_had_explanation\"] = False\n\n#Get means for later\nm1 = train[:,'prior_question_elapsed_time'].mean()\nm2 = train[:,\"user_mean\"].mean()\nm3 = train[:,\"part_mean\"].mean()\nm4 = train[:,\"content_mean\"].mean()\nm5 = train[:,\"tags_mean\"].mean()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train.tail()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train[\"prior_question_had_explanation_enc\"] = lb_make.fit_transform(train[\"prior_question_had_explanation\"])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"pq_m_all = train[0:num,:]\npq_m_all = pq_m_all[:, mean(f.answered_correctly), by('prior_question_had_explanation_enc')]\npq_m_all.names=[\"prior_question_had_explanation_enc\", \"pq_mean\"]\npq_m_all.key= (\"prior_question_had_explanation_enc\")\ntrain = train[:, :, dt.join(pq_m_all)]\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Create a hold out set for out-of-sample testing (approx. last 2 million rows)\n\nholdout = train[98000000:train.shape[0], :]\nX_hold = holdout[:,[\"user_mean\", \"user_count\", \"content_mean\", \"part_mean\" , \"prior_question_elapsed_time\", \"tags_mean\"] ].to_numpy()\nY_hold = holdout[:,\"answered_correctly\"].to_numpy().reshape(-1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"X_train = train[30000000:num, [\"user_mean\", \"user_count\", \"content_mean\", \"part_mean\" , \"prior_question_elapsed_time\", \"tags_mean\"]]\nX_val = train[num:98000000,[\"user_mean\", \"user_count\", \"content_mean\", \"part_mean\" , \"prior_question_elapsed_time\", \"tags_mean\"]]","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"Y_train = train[30000000:num, \"answered_correctly\"].to_numpy().reshape(-1)\nY_val = train[num:98000000, \"answered_correctly\"].to_numpy().reshape(-1)","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"X_train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#LGBM model\n\nimport lightgbm as lgb\n\nparams = {\n    'objective': 'binary',\n    'max_bin': 600,\n    'learning_rate': 0.02,\n    'num_leaves': 80\n}\n\n\nlgb_train = lgb.Dataset(X_train, Y_train)\nlgb_eval = lgb.Dataset(X_val, Y_val, reference=lgb_train)\n\nmodel = lgb.train(\n    params, lgb_train,\n    valid_sets=[lgb_train, lgb_eval],\n    verbose_eval=10,\n    num_boost_round=1000,\n    early_stopping_rounds=10\n)\n","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Evaluate\n\ny_pred = model.predict(X_hold)\ny_true = Y_hold\n\nfrom sklearn.metrics import roc_auc_score\nprint(roc_auc_score(y_true, y_pred))","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"train.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Lets prepare the test file and check the methodology works.\n\n#Get train again\nimport pandas as pd\ntrain=train[:, [ \"user_id\",\"content_id\", \"content_type_id\", \"answered_correctly\" , \"prior_question_elapsed_time\", \"prior_question_had_explanation_enc\", \"part\", \"tags\"]]\ntest = pd.read_csv(\"../input/riiid-test-answer-prediction/example_test.csv\")","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"test.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"num=train.shape[0]\n\nuser_m_all = train[0:num, :]\nuser_m_all = user_m_all[:, mean(f.answered_correctly), by('user_id')]\nuser_m_all.names= ['user_id', 'user_mean']\nuser_m_all = user_m_all.to_pandas()\ntest = pd.merge(test,user_m_all , on=[\"user_id\"], how=\"left\")\n\nuser_c_all = train[0:num, :]\nuser_c_all = user_c_all[:, count(f.answered_correctly), by('user_id')]\nuser_c_all.names= ['user_id', 'user_count']\nuser_c_all = user_c_all.to_pandas()\ntest = pd.merge(test,user_c_all , on=[\"user_id\"], how=\"left\")\n\ncontent_m_all = train[0:num, :]\ncontent_m_all = content_m_all[:, mean(f.answered_correctly), by('content_id')]\ncontent_m_all.names= ['content_id', 'content_mean']\ncontent_m_all = content_m_all.to_pandas()\ntest = pd.merge(test, content_m_all, on=[\"content_id\"], how=\"left\")\n\nquestions = questions.to_pandas()\ntest = pd.merge(test, questions, on=[\"content_id\"], how=\"left\")\n\npart_m_all = train[0:num,:]\npart_m_all = part_m_all[:, mean(f.answered_correctly), by('part')]\npart_m_all.names=[\"part\", \"part_mean\"]\npart_m_all = part_m_all.to_pandas()\ntest = pd.merge(test, part_m_all, on=[\"part\"], how=\"left\")\n\n\ntag_m_all = train[0:num,:]\ntag_m_all = tag_m_all[:, mean(f.answered_correctly), by('tags')]\ntag_m_all.names=[\"tags\", \"tags_mean\"]\ntag_m_all = tag_m_all.to_pandas()\ntest = pd.merge(test, tag_m_all, on=[\"tags\"], how=\"left\")\n\n\n\ntest.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Deal with missing values\n\ntest[\"prior_question_elapsed_time\"].fillna(float(m1.to_numpy().reshape(-1)), inplace=True)\ntest[\"user_count\"].fillna(0)\ntest[\"user_mean\"].fillna(float(m2.to_numpy().reshape(-1)), inplace=True)\ntest[\"part_mean\"].fillna(float(m3.to_numpy().reshape(-1)), inplace=True)\ntest[\"content_mean\"].fillna(float(m4.to_numpy().reshape(-1)), inplace=True)\n\ntest[\"prior_question_had_explanation\"].fillna(False)\ntest[\"prior_question_had_explanation_enc\"] = lb_make.fit_transform(test[\"prior_question_had_explanation\"])\n\npq_m_all = train[0:num,:]\npq_m_all = pq_m_all[:, mean(f.answered_correctly), by('prior_question_had_explanation_enc')]\npq_m_all.names=[\"prior_question_had_explanation_enc\", \"pq_mean\"]\npq_m_all = pq_m_all.to_pandas()\ntest = pd.merge(test, pq_m_all, on=[\"prior_question_had_explanation_enc\"], how=\"left\")\n\ntest.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"test['answered_correctly'] = model.predict( test[[ \"user_mean\", \"user_count\", \"content_mean\", \"part_mean\" , \"prior_question_elapsed_time\", \"tags_mean\"]])","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"test.head()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"#Run on the test set\nimport riiideducation\nenv = riiideducation.make_env()\niter_test = env.iter_test()","execution_count":null,"outputs":[]},{"metadata":{"trusted":true},"cell_type":"code","source":"for (test, sample_prediction_df) in iter_test:\n    \n    #Join\n    \n    \n    test = pd.merge(test,user_m_all , on=[\"user_id\"], how=\"left\")\n    test = pd.merge(test,user_c_all , on=[\"user_id\"], how=\"left\")\n    test = pd.merge(test,content_m_all , on=[\"content_id\"], how=\"left\")\n    test = pd.merge(test, questions, on=[\"content_id\"], how=\"left\")\n    test = pd.merge(test, part_m_all, on=[\"part\"], how=\"left\")\n    test = pd.merge(test, tag_m_all, on=[\"tags\"], how=\"left\")\n    \n    #Na fill\n    \n    test[\"prior_question_elapsed_time\"].fillna(float(m1.to_numpy().reshape(-1)), inplace=True)\n    test[\"user_count\"].fillna(0)\n    test[\"user_mean\"].fillna(float(m2.to_numpy().reshape(-1)), inplace=True)\n    test[\"part_mean\"].fillna(float(m3.to_numpy().reshape(-1)), inplace=True)\n    test[\"content_mean\"].fillna(float(m4.to_numpy().reshape(-1)), inplace=True)\n\n    #test[\"prior_question_had_explanation\"].fillna(False)\n    #test[\"prior_question_had_explanation_enc\"] = lb_make.fit_transform(test[\"prior_question_had_explanation\"])\n\n    #test = pd.merge(test, pq_m_all, on=[\"prior_question_had_explanation_enc\"], how=\"left\")\n    #Predict\n    \n    test['answered_correctly'] = model.predict( test[[ \"user_mean\", \"user_count\", \"content_mean\", \"part_mean\" , \"prior_question_elapsed_time\", \"tags_mean\"]])\n    env.predict(test.loc[test['content_type_id'] == 0, ['row_id', 'answered_correctly']])","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}