process_grades.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Linux Sysadmin DeCal Grade Processor\n",
    "Last updated: Spring 2021\n",
    "\n",
    "Created by bencuan\n",
    "\n",
    "## About\n",
    "This tool use Gradescope grade exports and generates an interactive grade report. (TODO: Automatically email students with their grade)\n",
    "\n",
    "There is a precommit hook that automatically removes all output from the notebook, so don't worry about accidentally exposing sensitive data. Just don't commit the csv's themselves.\n",
    "\n",
    "## Install\n",
    "To run Jupyter notebooks, you can [install it here](https://jupyter.org/install) or use [JupyterHub](https://datahub.berkeley.edu/). vscode also seems to have ipynb support now so that's pretty cool. If running locally, ensure you have pandas (`pip install pandas`).\n",
    "\n",
    "## Usage\n",
    "1. Export grade CSV's from Gradescope and place them in the `data/` folder.\n",
    "1. In the code box below, assign `beginner_path` and `advanced_path` to point to the respective csv's.\n",
    "2. If the lab names changed, you may also need to modify the `beginner_labs` and `advanced_labs` variables. Make them a list with each entry being a string containing one lab name (e.g. 'b1')\n",
    "4. Run all of the cells!\n",
    "5. The final cell exports the result to the file `data/grades_compiled.csv`.\n",
    "\n",
    "## IMPORTANT NOTE\n",
    "This will probably produce a LOT of NP's since many students have submitted late lab requests, etc. Go through each NP manually to ensure that individual cases have been dealt with."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# IMPORTS\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# CONFIGURATION\n",
    "pd.option_context('display.max_rows', None, 'display.max_columns', None) # display full chart\n",
    "\n",
    "beginner_path = 'Beginner_Spring_2021_grades.csv'\n",
    "advanced_path = 'Advanced_Spring_2021_grades.csv'\n",
    "\n",
    "beginner_labs = [f'b{i}' for i in range(1, 11)] + ['11']\n",
    "advanced_labs = [f'a{i}' for i in range(1, 11)] + ['11']\n",
    "\n",
    "COMPLETE_LAB_MIN = 9 # At least this number of labs turned in overall for P\n",
    "LATE_LAB_MAX = 3 # At least this number of labs turned in for P"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read grade data\n",
    "b_grades = pd.read_csv(f'data/{beginner_path}')\n",
    "a_grades = pd.read_csv(f'data/{advanced_path}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Show uncleaned data\n",
    "a_grades.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "b_grades.head(5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Make human readable\n",
    "\n",
    "def process(grades, labs):\n",
    "    statuses = {0: 'INC', 1: 'LATE', 2: 'DONE'}\n",
    "    cleaned = grades.loc[:,['Name', 'SID', 'Email']]\n",
    "    cleaned.insert(3, 'Completed', 0)\n",
    "    cleaned.insert(4, 'Num Late', 0)\n",
    "    cleaned.insert(3, 'Grade', 'NP')\n",
    "\n",
    "    for lab in labs:\n",
    "        cleaned[lab] = grades[f'Lab {lab}'].ge(grades[f'Lab {lab} - Max Points'] / 2).astype(int)\n",
    "        cleaned.loc[cleaned[lab] == 1, lab] = cleaned[lab].astype(int) + (grades[f'Lab {lab} - Lateness (H:M:S)'] == '00:00:00').astype(int)\n",
    "        cleaned[lab] = cleaned[lab].map(lambda x: statuses[x])\n",
    "        cleaned['Completed'] += cleaned[lab] == 'DONE'\n",
    "        cleaned['Completed'] += cleaned[lab] == 'LATE'\n",
    "        cleaned['Num Late'] += cleaned[lab] == 'LATE'\n",
    "        cleaned.loc[(cleaned['Completed'] >= COMPLETE_LAB_MIN) & (cleaned['Num Late'] <= LATE_LAB_MAX), 'Grade'] = 'P'\n",
    "\n",
    "    return cleaned\n",
    "\n",
    "b_cleaned = process(b_grades, beginner_labs)\n",
    "a_cleaned = process(a_grades, advanced_labs)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Show beginner grades\n",
    "with pd.option_context('display.max_rows', None, 'display.max_columns', None):\n",
    "    display(b_cleaned)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Show advanced grades\n",
    "display(a_cleaned)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Show not-a-number warnings (should be manually addressed)\n",
    "b_cleaned = b_cleaned.fillna('ERROR')\n",
    "a_cleaned = a_cleaned.fillna('ERROR')\n",
    "\n",
    "# ...advanced errors (if empty, everything is good!)\n",
    "a_cleaned[~a_cleaned['SID'].astype(str).str.isnumeric()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# ...beginner errors\n",
    "b_cleaned[~b_cleaned['SID'].astype(str).str.isnumeric()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Drop the bad rows\n",
    "a_cleaned = a_cleaned[a_cleaned['SID'].astype(str).str.isnumeric()]\n",
    "b_cleaned = b_cleaned[b_cleaned['SID'].astype(str).str.isnumeric()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Export Grades for Submission\n",
    "The script below takes in an empty grade sheet (contact Hilfinger or Cindy to get) and fills it in with P's and NP's. It is recommended that you manually look over the output and enter comments for all of the NP's.\n",
    "\n",
    "All of the above cells must be run before this section."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "EMPTY_ROSTER_PATH = 's2021-grades-blank.csv'\n",
    "blank = pd.read_csv(f'data/{EMPTY_ROSTER_PATH}')\n",
    "blank = blank.drop(columns = ['Grade'])\n",
    "blank"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "a_brief = a_cleaned.loc[:, ['SID', 'Grade']]\n",
    "b_brief = b_cleaned.loc[:, ['SID', 'Grade']]\n",
    "b_brief['SID'] = pd.to_numeric(b_brief.SID)\n",
    "a_brief['SID'] = pd.to_numeric(a_brief.SID)\n",
    "combined = a_brief.merge(b_brief, how='outer', on='SID')\n",
    "filled = blank[['SID', 'Grading Basis', 'Name']]\n",
    "filled = filled.merge(combined, how='left', on='SID')\n",
    "filled['Grade_x'] = filled['Grade_x'].fillna(filled['Grade_y'])\n",
    "filled = filled.rename(columns = {'Grade_x': 'Grade'})\n",
    "filled = filled.drop(columns = ['Grade_y'])\n",
    "filled = filled[['SID', 'Name', 'Grade', 'Grading Basis']]\n",
    "filled['Comments'] = ''\n",
    "\n",
    "with pd.option_context('display.max_rows', None, 'display.max_columns', None):\n",
    "    display(filled)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Missing Students\n",
    "The following students are not on the official roster, but are enrolled on Gradescope."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "a2 = a_cleaned.loc[:, ['SID', 'Name']]\n",
    "b2 = b_cleaned.loc[:, ['SID', 'Name']]\n",
    "b2['SID'] = pd.to_numeric(b2.SID)\n",
    "a2['SID'] = pd.to_numeric(a2.SID)\n",
    "combined2 = a2.merge(b2, how='outer', on='SID')\n",
    "missing = combined2[~combined2.SID.isin(filled.SID)]\n",
    "missing = missing.rename(columns = {'Name_x': 'Advanced', 'Name_y': 'Beginner'})\n",
    "missing"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Run the following to export!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Export\n",
    "filled.to_csv('data/grades_compiled.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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.9.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}