analyze_classmates.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "deb5956b",
   "metadata": {},
   "source": [
    "# Classmate"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "04c2466e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import json\n",
    "from pathlib import Path\n",
    "\n",
    "import ngender\n",
    "import pandas as pd\n",
    "import plotly.express as px"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def load_json(file):\n",
    "    with open(file, \"r\", encoding=\"utf-8\") as f:\n",
    "        return json.load(f)\n",
    "\n",
    "\n",
    "college = load_json(\"college.json\")\n",
    "department = load_json(\"department.json\")\n",
    "department_graduate = load_json(\"department_graduate.json\")\n",
    "class_id = load_json(\"class_id.json\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6d6df9d9",
   "metadata": {},
   "outputs": [],
   "source": [
    "path = Path(\"classmate\")\n",
    "classmates = pd.read_csv(path / \"classmates.csv\")\n",
    "# classmates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5df09eb5",
   "metadata": {},
   "outputs": [],
   "source": [
    "def filter(df, condition):\n",
    "    df = df.loc[condition, :]\n",
    "    df = df.set_index([\"E-Mail\", \"姓名\", \"學號\", \"系所班別\"]).sort_index()\n",
    "    # with pd.option_context(\"display.max_rows\", None):\n",
    "    #     display(df)\n",
    "    print(f\"Count: {df.index.nunique()}\")\n",
    "    unique_names = df.index.unique(level=1)\n",
    "    print(\", \".join(unique_names))"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "071b27a0",
   "metadata": {},
   "source": [
    "## E-mails with two or more student IDs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3d644e3a",
   "metadata": {},
   "outputs": [],
   "source": [
    "emails_with_two_or_more_ids = classmates.duplicated(\n",
    "    subset=\"E-Mail\"\n",
    ") ^ classmates.duplicated(subset=\"學號\")\n",
    "filter(classmates, emails_with_two_or_more_ids)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Names with two or more e-mails"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a7a935fa",
   "metadata": {},
   "outputs": [],
   "source": [
    "names_with_two_or_more_emails = classmates.duplicated(\n",
    "    subset=\"E-Mail\"\n",
    ") ^ classmates.duplicated(subset=\"姓名\")\n",
    "filter(classmates, names_with_two_or_more_emails)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Community Students"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "955b124a",
   "metadata": {},
   "outputs": [],
   "source": [
    "community = classmates.loc[:, \"系所班別\"].str.contains(\"社會人士\")\n",
    "filter(classmates, community)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## UST Students"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e057eaf3",
   "metadata": {},
   "outputs": [],
   "source": [
    "ust = classmates.loc[:, \"系所班別\"].str.contains(\"交大|陽明|政治|中央\")\n",
    "filter(classmates, ust)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "97c7bf7d",
   "metadata": {},
   "outputs": [],
   "source": [
    "def convert_to_str(value):\n",
    "    return str(value)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "nthu = ~(community | ust)\n",
    "classmates.loc[nthu, \"畢業系所\"] = classmates.loc[nthu, \"系所班別\"].str.extract(\n",
    "    \"(.{3})\", expand=False\n",
    ")\n",
    "classmates.loc[~nthu, \"畢業系所\"] = classmates.loc[~nthu, \"系所班別\"]\n",
    "\n",
    "columns = [\"username\", \"domain\"]\n",
    "classmates.loc[:, columns] = (\n",
    "    classmates.loc[:, \"E-Mail\"]\n",
    "    .str.extract(r\"(.+)@(.+)\")\n",
    "    .set_axis(columns, axis=\"columns\")\n",
    ")\n",
    "\n",
    "columns = [\"入學年度\", \"學院代碼\", \"系所代碼\", \"班級代碼\", \"流水號\"]\n",
    "classmates[columns] = pd.DataFrame(columns=columns, dtype=\"object\")\n",
    "classmates.loc[nthu, columns] = (\n",
    "    classmates.loc[nthu, \"學號\"]\n",
    "    .str.extract(r\"^(\\d{2,3})(\\d{1,2})(\\d)(\\d)(\\d{2})$\")\n",
    "    .set_axis(columns, axis=\"columns\")\n",
    ")\n",
    "classmates.loc[nthu, \"學院代碼\"] = classmates.loc[nthu, \"學院代碼\"].str.zfill(2)\n",
    "\n",
    "columns = [\"入學年度\", \"流水號\"]\n",
    "classmates.loc[community, columns] = (\n",
    "    classmates.loc[community, \"學號\"]\n",
    "    .str.extract(r\"^X?(\\d{2,3})(\\d{4})S?$\")\n",
    "    .set_axis(columns, axis=\"columns\")\n",
    ")\n",
    "\n",
    "# classmates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def foreign_student(s):\n",
    "    serial_number = int(s[\"流水號\"])\n",
    "    if s[\"班級\"] in [\"單班\", \"清班\", \"華班\", \"梅班\"]:\n",
    "        if serial_number >= 95:\n",
    "            return \"陸生\"\n",
    "        elif serial_number >= 81:\n",
    "            return \"外籍生\"\n",
    "        elif serial_number >= 61:\n",
    "            return \"僑生\"\n",
    "    elif s[\"班級\"] in [\"碩班境外生\"]:\n",
    "        if serial_number >= 86:\n",
    "            return \"預留\"\n",
    "        elif serial_number >= 66:\n",
    "            return \"陸生\"\n",
    "        elif serial_number >= 56:\n",
    "            return \"TIGP\"\n",
    "        elif serial_number >= 21:\n",
    "            return \"外籍生\"\n",
    "        elif serial_number >= 1:\n",
    "            return \"僑生\"\n",
    "    elif s[\"班級\"] in [\"博士一般學生\", \"學士逕讀博士\"]:\n",
    "        if serial_number >= 91:\n",
    "            return \"外籍生\"\n",
    "        elif serial_number >= 86:\n",
    "            return \"預留\"\n",
    "        elif serial_number >= 81:\n",
    "            return \"僑生\"\n",
    "        elif serial_number >= 71:\n",
    "            return \"陸生\"\n",
    "        elif serial_number >= 61:\n",
    "            return \"TIGP\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "classmates[\"學院\"] = classmates[\"學院代碼\"].map(college)\n",
    "classmates[\"班級\"] = classmates[\"班級代碼\"].map(class_id)\n",
    "classmates[\"碩士生\"] = classmates[\"班級\"].isin([\"碩士一般學生\", \"碩班境外生\"])\n",
    "classmates[\"博士生\"] = classmates[\"班級\"].isin([\"博士一般學生\", \"學士逕讀博士\"])\n",
    "classmates[\"研究生\"] = classmates[\"碩士生\"] | classmates[\"博士生\"]\n",
    "classmates[\"系所\"] = classmates.set_index([\"學院代碼\", \"系所代碼\"]).index.map(\n",
    "    pd.DataFrame(department).unstack()\n",
    ")\n",
    "classmates[\"境外生\"] = classmates.dropna(subset=[\"流水號\"]).apply(foreign_student, axis=1)\n",
    "# classmates"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f4e541ab",
   "metadata": {},
   "outputs": [],
   "source": [
    "def filter_class(df, class_name):\n",
    "    condition = df[\"班級\"] == class_name\n",
    "    filter(df, condition)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ab0c9d1c",
   "metadata": {},
   "source": [
    "## Early Entry Students"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "filter_class(classmates, \"碩士提前入學\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## PhD Students"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "filter_class(classmates, \"博士一般學生\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Direct Pursuit PhD Students"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "filter_class(classmates, \"學士逕讀博士\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8335c831",
   "metadata": {},
   "source": [
    "## Foreign Master Students\n",
    "(not necessary master student)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4d0df301",
   "metadata": {},
   "outputs": [],
   "source": [
    "filter_class(classmates, \"碩班境外生\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "def filter_foreign(df, foreign_type):\n",
    "    condition = df[\"境外生\"] == foreign_type\n",
    "    filter(df, condition)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Overseas Compatriot Students"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "filter_foreign(classmates, \"僑生\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Foreign Students"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "filter_foreign(classmates, \"外籍生\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## TIGP Students"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "filter_foreign(classmates, \"TIGP\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Chinese Students"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "filter_foreign(classmates, \"陸生\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Other Students"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "filter_foreign(classmates, \"預留\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ecbe81c0-e4de-4693-a237-c66e9786f4c4",
   "metadata": {},
   "outputs": [],
   "source": [
    "def guess_gender(name):\n",
    "    name = name.replace(\" \", \"\")\n",
    "    try:\n",
    "        gender, prob = ngender.guess(name)\n",
    "        return gender  # male or female or unknown, prob\n",
    "    except Exception as e:\n",
    "        # print(e)\n",
    "        return \"error\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f1e54e31-0240-497e-a712-84d894b2652a",
   "metadata": {},
   "outputs": [],
   "source": [
    "classmates[\"猜測性別\"] = classmates[\"姓名\"].map(guess_gender)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Unpredictable Gender"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "unpredictable_gender = (classmates[\"猜測性別\"] != \"male\") & (classmates[\"猜測性別\"] != \"female\")\n",
    "filter(classmates, unpredictable_gender)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "823d1497",
   "metadata": {},
   "source": [
    "## Switch Major Students"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bf4fa83b",
   "metadata": {},
   "outputs": [],
   "source": [
    "def switch_major(s):\n",
    "    if \"學士班\" in s[\"系所\"] or \"跨系所招生\" in s[\"系所\"]:\n",
    "        return False\n",
    "    elif s[\"畢業系所\"] == \"運科系\" and s[\"系所\"] == \"體育學系\":\n",
    "        return False\n",
    "    elif set(s[\"畢業系所\"]).issubset(s[\"系所\"]):\n",
    "        return False\n",
    "    else:\n",
    "        return True\n",
    "\n",
    "\n",
    "classmates[\"轉系\"] = classmates.dropna(subset=[\"畢業系所\", \"系所\"]).apply(switch_major, axis=1)\n",
    "filter(classmates, classmates[\"轉系\"] == True)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c883531c",
   "metadata": {},
   "source": [
    "## Defer Graduation Students"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c200cf63",
   "metadata": {},
   "outputs": [],
   "source": [
    "def defer_graduation(s):\n",
    "    year = int(s[\"學年\"]) - int(s[\"入學年度\"])\n",
    "    if year >= 5:\n",
    "        return True\n",
    "    elif s[\"碩士生\"] and year >= 3:\n",
    "        return True\n",
    "    else:\n",
    "        return False\n",
    "\n",
    "\n",
    "classmates[\"延畢\"] = classmates.dropna(subset=[\"入學年度\", \"學年\"]).apply(\n",
    "    defer_graduation, axis=1\n",
    ")\n",
    "filter(classmates, classmates[\"延畢\"] == True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "987caede",
   "metadata": {},
   "outputs": [],
   "source": [
    "# some classmates may have no E-Mail address, so we use student ID instead\n",
    "same_class = classmates.groupby([\"學號\"]).size()\n",
    "df = classmates.set_index(\"學號\")\n",
    "df.loc[same_class.index, \"同班次數\"] = same_class\n",
    "classmates = df.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e190dd71",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_student_id = classmates.drop_duplicates(subset=\"學號\", ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d08c0438",
   "metadata": {},
   "outputs": [],
   "source": [
    "px.pie(unique_student_id, names=\"學院\", title=\"College Distribution\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a80b2bdd",
   "metadata": {},
   "outputs": [],
   "source": [
    "px.pie(\n",
    "    unique_student_id.dropna(subset=[\"境外生\"]),\n",
    "    names=\"境外生\",\n",
    "    title=\"Foreign Student Distribution\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "11bf0950",
   "metadata": {},
   "outputs": [],
   "source": [
    "px.pie(unique_student_id, names=\"猜測性別\", title=\"Gender Distribution\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "390cdfb2",
   "metadata": {},
   "outputs": [],
   "source": [
    "px.histogram(\n",
    "    unique_student_id,\n",
    "    x=\"學院\",\n",
    "    color=\"猜測性別\",\n",
    "    title=\"Student Count by College and Gender\",\n",
    ").update_layout(yaxis_title=\"人數\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "78c4b83d",
   "metadata": {},
   "outputs": [],
   "source": [
    "course = \"課程\"\n",
    "# course = \"Course\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "px.histogram(\n",
    "    unique_student_id,\n",
    "    x=course,\n",
    "    color=\"猜測性別\",\n",
    "    title=\"Student Count by Course and Gender\",\n",
    ").update_layout(yaxis_title=\"人數\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65a393f9-2b99-4a16-87ba-4da0996d2cb1",
   "metadata": {},
   "outputs": [],
   "source": [
    "entry_year = unique_student_id.dropna(subset=[\"入學年度\"])\n",
    "entry_year.loc[:, \"入學年度\"] = entry_year.loc[:, \"入學年度\"].astype(\"int\")\n",
    "px.histogram(\n",
    "    entry_year,\n",
    "    x=\"入學年度\",\n",
    "    color=\"學年\",\n",
    "    title=\"Student Count by Admission Year and Course Year\",\n",
    ").update_layout(yaxis_title=\"人數\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e8dd04be-bd6d-4e7f-85e0-34de24d9655e",
   "metadata": {},
   "outputs": [],
   "source": [
    "px.histogram(\n",
    "    unique_student_id,\n",
    "    x=\"domain\",\n",
    "    color=\"domain\",\n",
    "    title=\"Student Count by E-Mail Domain\",\n",
    ").update_layout(yaxis_title=\"人數\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3c00af88",
   "metadata": {},
   "outputs": [],
   "source": [
    "unique_student_id_same_class = unique_student_id.loc[\n",
    "    unique_student_id.loc[:, \"同班次數\"] > 1, :\n",
    "]\n",
    "unique_student_id_same_class = unique_student_id_same_class.sort_values(\n",
    "    \"同班次數\", ascending=False\n",
    ")\n",
    "classmates_same_class = classmates.loc[classmates.loc[:, \"同班次數\"] > 1, :]\n",
    "classmates_same_class = classmates_same_class.sort_values(\"同班次數\", ascending=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c5d0435a",
   "metadata": {},
   "outputs": [],
   "source": [
    "px.histogram(\n",
    "    unique_student_id_same_class,\n",
    "    x=\"同班次數\",\n",
    "    color=\"系所\",\n",
    "    title=\"Student Count by Same Class Count and Department\",\n",
    ").update_layout(yaxis_title=\"人數\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16bf99f3-7b2f-47fa-a84a-a54f5d00116e",
   "metadata": {},
   "outputs": [],
   "source": [
    "px.histogram(\n",
    "    unique_student_id_same_class,\n",
    "    x=\"同班次數\",\n",
    "    color=course,\n",
    "    title=\"Student Count by Same Class Count and Course\",\n",
    ").update_layout(yaxis_title=\"人數\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "062aa4a1-e0ab-4c28-8282-1904319efb77",
   "metadata": {},
   "outputs": [],
   "source": [
    "px.strip(\n",
    "    classmates_same_class,\n",
    "    x=course,\n",
    "    y=\"同班次數\",\n",
    "    color=\"系所\",\n",
    "    hover_name=\"姓名\",\n",
    "    hover_data=[\"學號\"],\n",
    "    title=\"Same Class Count by Course\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6a2a9b81",
   "metadata": {},
   "outputs": [],
   "source": [
    "px.strip(\n",
    "    classmates_same_class,\n",
    "    x=\"系所\",\n",
    "    y=\"同班次數\",\n",
    "    color=course,\n",
    "    hover_name=\"姓名\",\n",
    "    hover_data=[\"學號\"],\n",
    "    title=\"Same Class Count by Department\",\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f0bdee3f",
   "metadata": {},
   "outputs": [],
   "source": [
    "classmates.to_csv(path / \"classmates_parsed.csv\", index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "classmate",
   "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.12.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}