exam_analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "<figure>\n",
    "  <IMG SRC=\"https://raw.githubusercontent.com/mbakker7/exploratory_computing_with_python/master/tudelft_logo.png\" WIDTH=250 ALIGN=\"right\">\n",
    "</figure>\n",
    "\n",
    "## Analyse exam results for TU Delft course ASSESS\n",
    "\n",
    "This notebook can be used to perform the analyses, provided you use EXACTLY the same excel template for analyzing the test \n",
    "results, as provided within the course. Please make sure you have matplotlib and pandas installed. \n",
    "\n",
    "*Developed by Dr. Ir. Hessel Winsemius (CEG - Water Management)*\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%matplotlib inline\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Below, a function is provided that reads metadata and the actual data into memory. The function also normalizes the data with respect to the maximum score per subquestion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def examread(fn):\n",
    "    \"\"\"\n",
    "    Read data from Excel tab called \"Scores\" into memory and return metadata, scores, and normalized scores\n",
    "    Input: fn - string containing file name (excel)\n",
    "    Output:\n",
    "        los:    list of learning objectives \n",
    "        res:    the scores (with missing students excluded!)\n",
    "        res_norm: scores normalised to the maximum score\n",
    "    \"\"\"\n",
    "    # first read the grading info into memory, and only use the first rows for some metadata\n",
    "    grade_info = pd.read_excel(fn, header=None, sheet_name='Scores', skiprows=9).iloc[:, 6:]\n",
    "    numbers = grade_info.iloc[0:3, 0:1]\n",
    "    no_part, no_qs, no_los = numbers.values[:, 0]\n",
    "\n",
    "    qw = grade_info.iloc[7, 0:no_qs].values  # weight\n",
    "    los = grade_info.iloc[8, 0:no_qs].values # learning objectives\n",
    "    qn = grade_info.iloc[9, 0:no_qs].values # question names\n",
    "    \n",
    "    # now that we know the amount of questions and students, read the body of the results in memory\n",
    "    body = pd.read_excel(fn, sheet_name='Scores', skiprows=18).iloc[:no_part]\n",
    "    # extract the results only\n",
    "    res = body.iloc[:, 6:]\n",
    "    # remove no shows\n",
    "    show_idx = res.sum(axis=1) > 1\n",
    "    res = res[show_idx]\n",
    "    sn = body.iloc[:, 3]\n",
    "    name = body.iloc[:, 0]\n",
    "\n",
    "    # normalize scores between zero and 100\n",
    "    res_norm = pd.DataFrame(res/np.array(qw)*100, dtype=np.float64)\n",
    "    # return any data we may need in the rest of the analysis\n",
    "    return pd.Series(los, index=res.columns), res, res_norm   # ,  (no_part, no_qs, no_los), "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# fill in the location of your results filename below \n",
    "# NOTE: use the exact Excel template as provided in the ASSESS training! \n",
    "# An example with random numbers is provided with this notebook, see \"fn = ...\" below.\n",
    "fn = r'analysis_exam_EXAMPLE.xlsx'\n",
    "\n",
    "# Below we read the data into memory (see function description to see what the variables mean)\n",
    "los, res, res_norm = examread(fn)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We already have all data for a nice boxplot. Let's plot that!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# sort columns on learning objectives\n",
    "gr = los.index.groupby(los.values)\n",
    "ordered_columns = [value for values in gr.values() for value in values]\n",
    "\n",
    "plt.figure(figsize=(16, 8))\n",
    "# res_norm.boxplot()\n",
    "### comment line above, and uncomment line below to sort the questions according to learning objectives\n",
    "res_norm[ordered_columns].boxplot()\n",
    "los.name = 'los'\n",
    "# los['los']\n",
    "plt.xlabel('question number'); plt.ylabel('percentage correct [%]');\n",
    "plt.ylim([-5, 105])\n",
    "plt.title('Boxplot per subquestion')\n",
    "plt.savefig('Boxplot.png', dpi=300, bbox_inches='tight')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Below we do some data analysis on the results (stored in DataFrame res)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "p = res_norm.mean()  # difficulty\n",
    "\n",
    "max_score = res.max() # maximum score over all students\n",
    "min_score = res.min() # minimum score over all students\n",
    "\n",
    "# compute the item-total and item-rest correlation\n",
    "sum_score = res.sum(axis=1)\n",
    "sumrest_score = pd.DataFrame([res.drop(columns=i).sum(axis=1) for i in res.columns], index=res.columns).transpose()\n",
    "Rit = res.corrwith(sum_score)\n",
    "Rir = res.corrwith(sumrest_score)\n",
    "stdev = res.std()\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's make a good looking plot of the results"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "f = plt.figure(figsize=(16, 10))\n",
    "\n",
    "ax1 = plt.subplot(511)\n",
    "Rit.plot(ax=ax1, color='b', label='Rit', xticks=range(len(Rit)))\n",
    "Rir.plot(ax=ax1, color='r', label='Rir')\n",
    "ax1.set_ylabel('Correlation coef. [-]')\n",
    "ax1.set_xlim([0-0.5, len(Rit)-0.5])\n",
    "ax1.set_xticklabels([])\n",
    "ax1.legend()\n",
    "ax1.grid()\n",
    "\n",
    "ax2 = plt.subplot(512)\n",
    "stdev.plot(ax=ax2, color='b', label='standard dev.', xticks=range(len(stdev)))\n",
    "ax2.legend()\n",
    "ax2.set_ylabel('Standard dev.')\n",
    "ax2.set_xlim([0-0.5, len(stdev)-0.5])\n",
    "ax2.set_xticklabels([])\n",
    "\n",
    "ax2.grid()\n",
    "\n",
    "ax3 = plt.subplot(513)\n",
    "(res_norm.max()).plot(ax=ax3, color='b', label='maximum score', xticks=range(len(res_norm)))\n",
    "(res_norm.min()).plot(ax=ax3, color='r', label='minimum score')\n",
    "ax3.legend()\n",
    "ax3.set_ylabel('Normalized score [%]')\n",
    "ax3.set_xlim([0-0.5, len(stdev)-0.5])\n",
    "ax3.set_xticklabels([])\n",
    "ax3.grid()\n",
    "\n",
    "ax4 = plt.subplot(514)\n",
    "p.plot(ax=ax4, color='b', label='difficulty', xticks=range(len(p)))\n",
    "ax4.set_ylabel('Difficulty [-]')\n",
    "ax4.set_xlim([0-0.5, len(p)-0.5])\n",
    "ax4.set_xticklabels([])\n",
    "\n",
    "ax4.grid()\n",
    "\n",
    "ax5 = plt.subplot(515)\n",
    "res.mean().plot.bar(ax=ax5, color='k', label='mean score')\n",
    "max_score.plot(ax=ax5, color='b', label='maximum score')\n",
    "min_score.plot(ax=ax5, color='r', label='minimum score')\n",
    "ax5.legend()\n",
    "ax5.set_ylabel('Mean score')\n",
    "ax5.grid()\n",
    "\n",
    "f.savefig('Analysis_results.png', dpi=300, bbox_inches='tight')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Finally we compute summarised statistics for the entire exam"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "N, N_q = res_norm.shape # nr of students, nr of questions\n",
    "rest = res.sum(axis=1)\n",
    "\n",
    "sigx = rest.std() # standard deviation of the total score\n",
    "alpha = N_q/(N_q - 1)*(sigx**2-(stdev**2).sum())/sigx**2 # Cronbach's alpha\n",
    "\n",
    "SEM_68 = sigx*(1-alpha)**.5 # 68% confidence interval of grades (SEM)\n",
    "\n",
    "# make a nice plot\n",
    "plt.figure()\n",
    "ax = plt.axes()\n",
    "rest.plot.hist(bins=range(5, 100, 10), ax=ax, xticks=range(0, 100, 10))\n",
    "ax.set_xlabel('Grade [%]')\n",
    "ax.set_ylabel('# Students')\n",
    "ax.grid()\n",
    "ax.set_title(\"Cronbach's alpha: {:.2f}, SEM: {:.2f}\".format(alpha, SEM_68))\n",
    "# TODO add results per learning objectives in fitting suplots\n",
    "plt.savefig('summary_results.png', dpi=300, bbox_inches='tight')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.6.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}