generate_score_report.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Generate the score report\n",
    "Extract score for each sheet and convert GitHub Repo name to student ID"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "base_folder = \"data/ITE3101_practical_tests/ite-3101-practical-test-ab-submissions/\"\n",
    "human_score_review = \"human_score_review (completed).xlsx\"\n",
    "assignment_name = \"ite-3101-practical-test-ab\"\n",
    "code_folder = \"lab/lab01/\"\n",
    "student_id_github = \"GitHub.xlsx\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "%pip install pandas python-docx"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Load the Excel file\n",
    "excel_file = pd.ExcelFile(base_folder + human_score_review)\n",
    "student_df = pd.read_excel(base_folder + student_id_github)\n",
    "\n",
    "# Student ID is string\n",
    "student_df['Student ID'] = student_df['Student ID'].astype(str)\n",
    "\n",
    "# Create an empty dictionary to store the sheet dataframes\n",
    "sheet_dict = {}\n",
    "\n",
    "# Iterate over each sheet in the Excel file\n",
    "for sheet_name in excel_file.sheet_names:\n",
    "    # Read the sheet data into a dataframe\n",
    "    sheet_df = excel_file.parse(sheet_name)\n",
    "    # Keep the Directory and Score columns\n",
    "    sheet_df = sheet_df[['Directory', 'Score']]\n",
    "\n",
    "    # Remove the assignment name from the directory\n",
    "    sheet_df['Directory'] = sheet_df['Directory'].str.replace(\n",
    "        assignment_name+\"-\", '')\n",
    "    # rename Directory to GitHub Name\n",
    "    sheet_df = sheet_df.rename(columns={'Directory': 'GitHub Name'})\n",
    "    # # join the dataframes on GitHub Name. if GitHub Name does not exist in the student_df, it will be NaN\n",
    "    # sheet_df = sheet_df.join(student_df.set_index('GitHub Name'), on='GitHub Name')\n",
    "\n",
    "    # Rename Score to sheet_name\n",
    "    sheet_df = sheet_df.rename(columns={'Score': sheet_name})\n",
    "\n",
    "    # Add the dataframe to the dictionary with the sheet name as the key\n",
    "    sheet_dict[sheet_name] = sheet_df\n",
    "\n",
    "# Join all the dataframes in the dictionary value together into one dataframe on the GitHub Name column\n",
    "df = pd.concat(sheet_dict.values(), axis=1, join='outer')\n",
    "# Keep the first GitHub Name column\n",
    "df = df.iloc[:, ~df.columns.duplicated()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Score'] = df.drop('GitHub Name', axis=1).sum(axis=1)\n",
    "\n",
    "# join the dataframes on GitHub Name. if GitHub Name does not exist in the student_df, it will be NaN\n",
    "df = df.join(student_df.set_index('GitHub Name'), on='GitHub Name')\n",
    "\n",
    "# Student ID to the first column, then Score to the second column\n",
    "cols = df.columns.tolist()\n",
    "cols = cols[-2:] + cols[:-2]\n",
    "df = df[cols]\n",
    "# swap the first two columns\n",
    "cols = df.columns.tolist()\n",
    "cols = cols[1:2] + cols[0:1] + cols[2:]\n",
    "df = df[cols]\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_excel(base_folder+assignment_name+\".xlsx\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Generate Script Sample\n",
    "5 set Samples:\n",
    "\n",
    "Combined scripts\n",
    "1. 3 Good, 3 Average, and 3 Weak.\n",
    "2. 5 Good, 5 Average, and 5 Weak.\n",
    "3. 3 Good, 3 Average, and 3 Weak above the passing mark.\n",
    "4. 5 Good, 5 Average, and 5 Weak above the passing mark."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "passingMark = 30"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "from docx import Document\n",
    "from docx.enum.text import WD_PARAGRAPH_ALIGNMENT\n",
    "from docx.shared import Pt\n",
    "\n",
    "# remove teachers \"GitHub Name\"\n",
    "df = df[df[\"GitHub Name\"] != \"cloud-and-data-centre-administration\"]\n",
    "df = df[df[\"GitHub Name\"] != \"it114115-student\"]\n",
    "\n",
    "sampling = df.sort_values(by=[\"Score\"], ascending=False)[\"Score\"]\n",
    "\n",
    "def add_headline(doc, text):\n",
    "    # Add a paragraph with \"GOOD\" in the center\n",
    "    paragraph = doc.add_paragraph()\n",
    "    run = paragraph.add_run(text)\n",
    "    run.bold = True\n",
    "    run.font.size = Pt(40)  # Set the font size to 40\n",
    "    paragraph.alignment = WD_PARAGRAPH_ALIGNMENT.CENTER\n",
    "    doc.add_page_break()\n",
    "\n",
    "\n",
    "def add_group(doc, name, index):\n",
    "    add_headline(doc, name)\n",
    "    for i in index:\n",
    "        row = df.loc[i]\n",
    "        doc.add_paragraph(\"Student ID: \" + str(row[\"Student ID\"]))\n",
    "        doc.add_paragraph(\"GitHub Name: \" + str(row[\"GitHub Name\"]))\n",
    "        doc.add_paragraph(\"Total Score: \" + str(row[\"Score\"]))\n",
    "        doc.add_page_break()\n",
    "        # inert horizontal line\n",
    "          \n",
    "\n",
    "        # Loop through the reset column and read the file as text\n",
    "        for column_name in df.columns:\n",
    "            if column_name not in [\"Student ID\", \"GitHub Name\", \"Score\"]:\n",
    "                code_file_path = os.path.join(\n",
    "                    base_folder, assignment_name+\"-\"+row[\"GitHub Name\"], code_folder, column_name)\n",
    "                with open(code_file_path, 'r') as file:\n",
    "                    text = file.read()\n",
    "                    doc.add_paragraph(\n",
    "                        \"Question: \" + column_name)\n",
    "                    doc.add_paragraph(\"Score: \" + str(row[column_name]))\n",
    "                    #add new line\n",
    "                    doc.add_paragraph()\n",
    "                    doc.add_paragraph(\"____________________________________________________________________________________\") \n",
    "                    doc.add_paragraph(text)\n",
    "                    doc.add_page_break()\n",
    "\n",
    "\n",
    "def take_sample(n, sampling, suffix=\"\"):\n",
    "    if len(sampling) < 3 * n:\n",
    "        n = int(len(sampling) / 3)\n",
    "    good = sampling.head(n)\n",
    "    weak = sampling.tail(n)\n",
    "    median = int(len(sampling) / 2)\n",
    "    take = int(n / 2)\n",
    "    average = sampling.iloc[median - take: median + take]\n",
    "\n",
    "    # Create a Document object\n",
    "    doc = Document()\n",
    "    add_group(doc, \"Good\", good.index)\n",
    "    add_group(doc, \"Average\", average.index)\n",
    "    add_group(doc, \"Weak\", weak.index)\n",
    "    doc.save(base_folder+\"/sampleOf\" + str(n) + suffix + \".docx\")\n",
    "\n",
    "\n",
    "take_sample(3, sampling)\n",
    "take_sample(5, sampling)\n",
    "# filter only pass\n",
    "sampling = df[df[\"Score\"] >= passingMark].sort_values(\n",
    "    by=[\"Score\"], ascending=False)[\"Score\"]\n",
    "take_sample(3, sampling, \"_only_pass\")\n",
    "take_sample(5, sampling, \"_only_pass\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Zip for backup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import shutil\n",
    "\n",
    "# Get the parent folder name\n",
    "parent_folder = os.path.basename(os.path.dirname(base_folder))\n",
    "\n",
    "# Create the zip file name\n",
    "zip_file_name = parent_folder + \"(completed).zip\"\n",
    "\n",
    "# get base_folder up one folder\n",
    "zip_file_name = os.path.join(base_folder,\"../\"+zip_file_name)\n",
    "zip_file_name\n",
    "\n",
    "# zip base_folder and save to zip_file_name\n",
    "shutil.make_archive(zip_file_name.replace(\".zip\", \"\"), 'zip', base_folder)"
   ]
  },
  {
   "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}