Graph construction.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "from sklearn import preprocessing\n",
    "from sklearn.metrics.pairwise import pairwise_distances, euclidean_distances\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Loading data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/sharonedayan/tensorflow/lib/python3.5/site-packages/numpy/lib/arraysetops.py:463: FutureWarning: elementwise comparison failed; returning scalar instead, but in the future will perform elementwise comparison\n",
      "  mask |= (ar1 == a)\n"
     ]
    }
   ],
   "source": [
    "# open data & preprocess\n",
    "data = pd.read_csv('20171121_data.csv', index_col=0)\n",
    "exercise = pd.read_csv('exerciseId.tsv', sep='\\t', index_col=0)\n",
    "chapter = pd.read_csv('20171122_chapter.tsv', sep='\\t')\n",
    "data['subject_id'] = data['chapter_id'].map(chapter['subject_id'])\n",
    "data['beginningDate'] = pd.to_datetime(data['beginningDate'])\n",
    "data['endDate'] = pd.to_datetime(data['endDate'])\n",
    "data['spentTime'] = data['endDate'] - data['beginningDate']\n",
    "data['spentTime'] = data['spentTime'].dt.total_seconds()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "subject = pd.read_csv('20171122_subject.tsv', sep='\\t')\n",
    "subject = subject[['id','fullTitle']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Merging exercise_id with all data\n",
    "df = data.merge(exercise, how='left', left_index=True, right_index=True)\n",
    "df = df[np.isfinite(df['user_id']) & np.isfinite(df['exercise_id'])].drop_duplicates() #ATTENTION user_id manquants\n",
    "dff = df.reset_index().drop_duplicates('id').set_index('id') #drop duplicates indices ### PROBLEME SUPPRIME\n",
    "\n",
    "# Encoding question types\n",
    "le = preprocessing.LabelEncoder()\n",
    "dff['type_ae'] = le.fit_transform(dff['type'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "nb user 24,966\n",
      "nb exercise 15,372\n",
      "nb users x nb exercises 383,777,352\n"
     ]
    }
   ],
   "source": [
    "# General informations\n",
    "print('nb user %s' % \"{:,}\".format(len(dff.user_id.unique())))\n",
    "print('nb exercise %s' % \"{:,}\".format(len(dff.exercise_id.unique()))) \n",
    "print('nb users x nb exercises %s' % \"{:,}\".format(len(dff.user_id.unique())*len(dff.exercise_id.unique()))) "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Adjacency matrix"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Graph nodes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## HYPOTHESIS : we keep only the first answer of a question by a student \n",
    "## (if he has done it multiple times)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "graph = dff.drop_duplicates(['user_id',\n",
    "                            'exercise_id'], keep ='first').groupby(['exercise_id']).agg({\n",
    "                                                                    'user_id':'count',\n",
    "                                                                     'difficulty' : 'mean', \n",
    "                                                                     'valid':'mean',\n",
    "                                                                     'spentTime':'mean',\n",
    "                                                                     'skip':'mean', \n",
    "                                                                     'topic_id':'mean', \n",
    "                                                                     'type_ae':'mean'})#.reset_index()\n",
    "g = graph.dropna() # NA on difficulty : 15264 non-null for 15372 exercise_id\n",
    "# keeping only exercise_id with non-null difficulty\n",
    "saved_ex_id = list(g.index) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>skip</th>\n",
       "      <th>user_id</th>\n",
       "      <th>valid</th>\n",
       "      <th>topic_id</th>\n",
       "      <th>difficulty</th>\n",
       "      <th>type_ae</th>\n",
       "      <th>spentTime</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>15264.000000</td>\n",
       "      <td>15264.000000</td>\n",
       "      <td>15264.000000</td>\n",
       "      <td>15264.000000</td>\n",
       "      <td>15264.000000</td>\n",
       "      <td>15264.000000</td>\n",
       "      <td>1.526400e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>0.057552</td>\n",
       "      <td>331.521030</td>\n",
       "      <td>0.461161</td>\n",
       "      <td>523.538915</td>\n",
       "      <td>0.491981</td>\n",
       "      <td>2.322655</td>\n",
       "      <td>1.548057e+02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>0.096089</td>\n",
       "      <td>469.535926</td>\n",
       "      <td>0.261242</td>\n",
       "      <td>545.583299</td>\n",
       "      <td>0.227834</td>\n",
       "      <td>1.138739</td>\n",
       "      <td>1.282009e+04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>-4.500000e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>0.008929</td>\n",
       "      <td>34.000000</td>\n",
       "      <td>0.252393</td>\n",
       "      <td>139.000000</td>\n",
       "      <td>0.200000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>2.007669e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>0.025571</td>\n",
       "      <td>166.000000</td>\n",
       "      <td>0.461538</td>\n",
       "      <td>298.000000</td>\n",
       "      <td>0.500000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>3.158426e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>0.066896</td>\n",
       "      <td>430.000000</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>747.000000</td>\n",
       "      <td>0.800000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>5.228206e+01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>4555.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1861.000000</td>\n",
       "      <td>0.800000</td>\n",
       "      <td>4.000000</td>\n",
       "      <td>1.582158e+06</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               skip       user_id         valid      topic_id    difficulty  \\\n",
       "count  15264.000000  15264.000000  15264.000000  15264.000000  15264.000000   \n",
       "mean       0.057552    331.521030      0.461161    523.538915      0.491981   \n",
       "std        0.096089    469.535926      0.261242    545.583299      0.227834   \n",
       "min        0.000000      1.000000      0.000000      1.000000      0.200000   \n",
       "25%        0.008929     34.000000      0.252393    139.000000      0.200000   \n",
       "50%        0.025571    166.000000      0.461538    298.000000      0.500000   \n",
       "75%        0.066896    430.000000      0.666667    747.000000      0.800000   \n",
       "max        1.000000   4555.000000      1.000000   1861.000000      0.800000   \n",
       "\n",
       "            type_ae     spentTime  \n",
       "count  15264.000000  1.526400e+04  \n",
       "mean       2.322655  1.548057e+02  \n",
       "std        1.138739  1.282009e+04  \n",
       "min        0.000000 -4.500000e+01  \n",
       "25%        2.000000  2.007669e+01  \n",
       "50%        3.000000  3.158426e+01  \n",
       "75%        3.000000  5.228206e+01  \n",
       "max        4.000000  1.582158e+06  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "g.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### History matrix"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Question history\n",
    "hist = dff.drop_duplicates(['user_id','exercise_id'], keep ='first').groupby(['exercise_id','user_id'])['valid'].mean()\n",
    "hist = hist.to_frame().reset_index()\n",
    "# keeping only exercise_id with non-null difficulty\n",
    "hist = hist[hist.exercise_id.isin(saved_ex_id)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# done questions : valid/not valid\n",
    "h = hist.pivot(index = 'exercise_id', columns = 'user_id', values = 'valid') \n",
    "h = h.fillna(-100) # undone questions \n",
    "h = h.astype(int)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Graph edges"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# preprocessing for the 5 categorical features\n",
    "g_ = g[['skip','valid','type_ae','difficulty','topic_id']]\n",
    "x = g_.values #returns a numpy array\n",
    "min_max_scaler = preprocessing.MinMaxScaler()\n",
    "x_scaled = min_max_scaler.fit_transform(x)\n",
    "g2 = pd.DataFrame(x_scaled)\n",
    "\n",
    "# distance for the 5 features\n",
    "dist_notime = pairwise_distances(g2, metric='l1') #L1 norm\n",
    "# distance for the last continuous feature : spentTime\n",
    "g_time = g[['spentTime']]\n",
    "dist_time = pairwise_distances(g_time, metric='canberra') #canberra : somme de (soustraction /somme) 2\n",
    "# distance for all the 6 features\n",
    "dist = dist_time + dist_notime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# quantile de \"crédibilité\" : nb de fois que la question a été répondue\n",
    "q75 = g.user_id.quantile(q=0.75)\n",
    "q25 = g.user_id.quantile(q=0.25)\n",
    "ex_slice1 = list(g[g.user_id < q25].index)\n",
    "ex_slice2 = list(g[(g.user_id >= q25) & (g.user_id < q75)].index)\n",
    "ex_slice3 = list(g[g.user_id >= q75].index)\n",
    "\n",
    "# création de 3 tranches\n",
    "g_user = g[['user_id']].copy()\n",
    "g_user.iloc[g_user.index.isin(ex_slice1)] = 1\n",
    "g_user.iloc[g_user.index.isin(ex_slice2)] = 2\n",
    "g_user.iloc[g_user.index.isin(ex_slice3)] = 3\n",
    "\n",
    "# distance \n",
    "k = pairwise_distances(g_user, metric='l1')\n",
    "credi = pd.DataFrame(1-k/10) #0.8, 0.9 1\n",
    "credi.index = g.index\n",
    "credi.columns = g.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "sim = 1 - dist/6 #similarité : 1 - normalized distance\n",
    "W = pd.DataFrame(sim)\n",
    "W.index = g.index\n",
    "W.columns = g.index\n",
    "\n",
    "## SIMILARITY MATRIX\n",
    "W = W.mul(credi)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "idx = np.random.choice(W.columns, 5)\n",
    "W[idx].loc[idx]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Focusing on only one subject"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "list_ex_hist_3e = dff[dff['subject_id']==1]['exercise_id'].unique()\n",
    "W_small = W[W.index.isin(list_ex_hist_3e)][list_ex_hist_3e]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "h_small = h[h.index.isin(list_ex_hist_3e)]\n",
    "h_small = h_small.loc[:, (h_small != -100).any()]\n",
    "print(W_small.shape, h_small.shape)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Saving matrices"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "W.to_pickle('adjacency.pkl')\n",
    "h.to_hdf('history.hdf', key = 'hist')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "W_small.to_pickle('adjacency_small.pkl')\n",
    "h_small.to_hdf('history_small.hdf', key = 'hist')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### For kegra"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "((15264, 6), (5060337, 2))"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "node = g[['topic_id','spentTime','skip','user_id','difficulty','type_ae']]\n",
    "node = node.as_matrix() #cora.content\n",
    "node\n",
    "link = hist[['exercise_id','user_id']]\n",
    "link = link.as_matrix() #cora.cites\n",
    "node.shape, link.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "#student = 142954.0"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.4"
  },
  "toc": {
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": "block",
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}