1. grades.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "1e1313a3-8f12-49ec-8962-f4ba4d192a62",
   "metadata": {},
   "source": [
    "# COVID learning gaps run deep among Los Angeles students, data shows \n",
    "By [Iris Lee](https://twitter.com/irisslee)  \n",
    "\n",
    "Times examined report card grades received by students in six through 12th grades at the Los Angeles Unified School District. Share of Ds and Fs increased by more than five percentage points since before the pandemic. More than a quarter of grades in spring semester of 2020-2021 school year were Ds and Fs.\n",
    "\n",
    "Read the full story [here](https://www.latimes.com/california/story/2021-10-21/covid-era-learning-challenges-lausd-after-school-closures)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "91076c6b-a71b-43c3-8154-2f8e130b818f",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import altair as alt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "66e2afe8-6671-4eb1-8239-caa8ad76c570",
   "metadata": {},
   "outputs": [],
   "source": [
    "pd.set_option('display.max_columns', None)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a15db44f-590a-4843-a4e5-7f44d82a70fd",
   "metadata": {},
   "source": [
    "## Import grades file for students in in grades six through 12"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "0d7704ca-ff6f-492a-ab88-60de7e42c20b",
   "metadata": {},
   "outputs": [],
   "source": [
    "grades = pd.read_csv(\"input/all-grades-complete.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "332ece06-e8de-440d-925f-8bc5427abb19",
   "metadata": {},
   "source": [
    "Cleaning functions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "eca7c131-2b15-4552-8e9d-af1c7fe2f112",
   "metadata": {},
   "outputs": [],
   "source": [
    "cleanup = lambda x:x.replace(\",\",\"\").replace(\"%\",\"\").replace(\"-\",\"\").replace(\" \",\"\") if type(x)==str else x"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "fa240a17-1b95-4210-ad53-4e6aca43d14b",
   "metadata": {},
   "outputs": [],
   "source": [
    "def simplify(x):\n",
    "    season = x.split('SEMESTER')[0]\n",
    "    year1 = x.split('SEMESTER')[1][:4]\n",
    "    year2 = x.split('SEMESTER')[1][-4:]\n",
    "    \n",
    "    final = f\"{year1}-{year2}-{season}\"\n",
    "    return final\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5cfbff1f-6781-4aec-ad2a-4b939fa12807",
   "metadata": {},
   "source": [
    "Clean up"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "ac67141b-3cec-4bda-b008-648799472439",
   "metadata": {},
   "outputs": [],
   "source": [
    "grades_clean = grades.applymap(cleanup)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7fdd2628-afe4-42c7-bc85-fbae5cd93c0b",
   "metadata": {},
   "outputs": [],
   "source": [
    "grades_clean.columns = [x.replace(\"'\",\"\") for x in grades_clean.columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "c405b10e-8b10-4c14-8b15-a1ea5771c74c",
   "metadata": {},
   "outputs": [],
   "source": [
    "grades_clean[\"school_year\"] = grades_clean[\"Semester School Year\"].apply(simplify)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4d51173a-0540-4385-abce-6cf589dd22fa",
   "metadata": {},
   "source": [
    "Change number columns to numeric"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "ca4afdfb-bf69-4199-8114-1812358823cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "grades_clean = grades_clean.apply(pd.to_numeric, errors=\"ignore\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f308390f-ad30-4319-83bc-f32386319b68",
   "metadata": {
    "tags": []
   },
   "source": [
    "### Trim out Spring 2019-2020 but keep the rest  \n",
    "No Fs were given in Spring 2019-2020 school year per LAUSD policy."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "968fef8c-3c77-4fcb-9d11-85e4829d1191",
   "metadata": {},
   "outputs": [],
   "source": [
    "grades_trimmed = grades_clean[~grades_clean.school_year.str.contains(\"2019-2020-SPRING\")].sort_values(\"school_year\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "7ec31011-2def-4da0-aa80-b855f1940089",
   "metadata": {},
   "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>Grades</th>\n",
       "      <th>Semester School Year</th>\n",
       "      <th># of As</th>\n",
       "      <th># of Bs</th>\n",
       "      <th># of Cs</th>\n",
       "      <th># of Ds</th>\n",
       "      <th># of Fs</th>\n",
       "      <th># of Ps</th>\n",
       "      <th># of Ns</th>\n",
       "      <th># of Is</th>\n",
       "      <th>Total Marks</th>\n",
       "      <th>% A</th>\n",
       "      <th>% B</th>\n",
       "      <th>% C</th>\n",
       "      <th>A-C %</th>\n",
       "      <th>% D</th>\n",
       "      <th>% F</th>\n",
       "      <th>% P</th>\n",
       "      <th>% N</th>\n",
       "      <th>% I</th>\n",
       "      <th>school_year</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>9to12</td>\n",
       "      <td>FALLSEMESTER20182019</td>\n",
       "      <td>301382</td>\n",
       "      <td>217981</td>\n",
       "      <td>189163</td>\n",
       "      <td>90078</td>\n",
       "      <td>101893.0</td>\n",
       "      <td>11342</td>\n",
       "      <td>21044</td>\n",
       "      <td>1308</td>\n",
       "      <td>934191</td>\n",
       "      <td>32.3</td>\n",
       "      <td>23.3</td>\n",
       "      <td>20.2</td>\n",
       "      <td>75.8</td>\n",
       "      <td>9.6</td>\n",
       "      <td>10.9</td>\n",
       "      <td>1.2</td>\n",
       "      <td>2.3</td>\n",
       "      <td>0.1</td>\n",
       "      <td>2018-2019-FALL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6to8</td>\n",
       "      <td>FALLSEMESTER20182019</td>\n",
       "      <td>221624</td>\n",
       "      <td>165766</td>\n",
       "      <td>130205</td>\n",
       "      <td>67168</td>\n",
       "      <td>54945.0</td>\n",
       "      <td>10174</td>\n",
       "      <td>66995</td>\n",
       "      <td>155</td>\n",
       "      <td>717032</td>\n",
       "      <td>30.9</td>\n",
       "      <td>23.1</td>\n",
       "      <td>18.2</td>\n",
       "      <td>72.2</td>\n",
       "      <td>9.4</td>\n",
       "      <td>7.7</td>\n",
       "      <td>1.4</td>\n",
       "      <td>9.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2018-2019-FALL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>9to12</td>\n",
       "      <td>SPRINGSEMESTER20182019</td>\n",
       "      <td>299486</td>\n",
       "      <td>210384</td>\n",
       "      <td>192475</td>\n",
       "      <td>88218</td>\n",
       "      <td>103546.0</td>\n",
       "      <td>15044</td>\n",
       "      <td>22173</td>\n",
       "      <td>1200</td>\n",
       "      <td>932526</td>\n",
       "      <td>32.1</td>\n",
       "      <td>22.6</td>\n",
       "      <td>20.6</td>\n",
       "      <td>75.3</td>\n",
       "      <td>9.5</td>\n",
       "      <td>11.1</td>\n",
       "      <td>1.6</td>\n",
       "      <td>2.4</td>\n",
       "      <td>0.1</td>\n",
       "      <td>2018-2019-SPRING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>6to8</td>\n",
       "      <td>SPRINGSEMESTER20182019</td>\n",
       "      <td>214864</td>\n",
       "      <td>159205</td>\n",
       "      <td>131509</td>\n",
       "      <td>67244</td>\n",
       "      <td>65109.0</td>\n",
       "      <td>9029</td>\n",
       "      <td>67722</td>\n",
       "      <td>64</td>\n",
       "      <td>714746</td>\n",
       "      <td>30.1</td>\n",
       "      <td>22.3</td>\n",
       "      <td>18.4</td>\n",
       "      <td>70.7</td>\n",
       "      <td>9.4</td>\n",
       "      <td>9.1</td>\n",
       "      <td>1.3</td>\n",
       "      <td>9.5</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2018-2019-SPRING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9to12</td>\n",
       "      <td>FALLSEMESTER20192020</td>\n",
       "      <td>306029</td>\n",
       "      <td>216670</td>\n",
       "      <td>186161</td>\n",
       "      <td>88136</td>\n",
       "      <td>95144.0</td>\n",
       "      <td>11645</td>\n",
       "      <td>19570</td>\n",
       "      <td>1149</td>\n",
       "      <td>924504</td>\n",
       "      <td>33.1</td>\n",
       "      <td>23.4</td>\n",
       "      <td>20.1</td>\n",
       "      <td>76.7</td>\n",
       "      <td>9.5</td>\n",
       "      <td>10.3</td>\n",
       "      <td>1.3</td>\n",
       "      <td>2.1</td>\n",
       "      <td>0.1</td>\n",
       "      <td>2019-2020-FALL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>6to8</td>\n",
       "      <td>FALLSEMESTER20192020</td>\n",
       "      <td>221648</td>\n",
       "      <td>163995</td>\n",
       "      <td>126887</td>\n",
       "      <td>65126</td>\n",
       "      <td>51454.0</td>\n",
       "      <td>9242</td>\n",
       "      <td>58241</td>\n",
       "      <td>77</td>\n",
       "      <td>696670</td>\n",
       "      <td>31.8</td>\n",
       "      <td>23.5</td>\n",
       "      <td>18.2</td>\n",
       "      <td>73.6</td>\n",
       "      <td>9.3</td>\n",
       "      <td>7.4</td>\n",
       "      <td>1.3</td>\n",
       "      <td>8.4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2019-2020-FALL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9to12</td>\n",
       "      <td>FALLSEMESTER20202021</td>\n",
       "      <td>313109</td>\n",
       "      <td>157401</td>\n",
       "      <td>148168</td>\n",
       "      <td>121639</td>\n",
       "      <td>106001.0</td>\n",
       "      <td>127461</td>\n",
       "      <td>3566</td>\n",
       "      <td>372</td>\n",
       "      <td>977717</td>\n",
       "      <td>32.0</td>\n",
       "      <td>16.1</td>\n",
       "      <td>15.2</td>\n",
       "      <td>63.3</td>\n",
       "      <td>12.4</td>\n",
       "      <td>10.8</td>\n",
       "      <td>13.0</td>\n",
       "      <td>0.4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2020-2021-FALL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>6to8</td>\n",
       "      <td>FALLSEMESTER20202021</td>\n",
       "      <td>204876</td>\n",
       "      <td>112867</td>\n",
       "      <td>109613</td>\n",
       "      <td>114456</td>\n",
       "      <td>48517.0</td>\n",
       "      <td>90312</td>\n",
       "      <td>2687</td>\n",
       "      <td>26</td>\n",
       "      <td>683354</td>\n",
       "      <td>30.0</td>\n",
       "      <td>16.5</td>\n",
       "      <td>16.0</td>\n",
       "      <td>62.5</td>\n",
       "      <td>16.7</td>\n",
       "      <td>7.1</td>\n",
       "      <td>13.2</td>\n",
       "      <td>0.4</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2020-2021-FALL</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>9to12</td>\n",
       "      <td>SPRINGSEMESTER20202021</td>\n",
       "      <td>308459</td>\n",
       "      <td>149633</td>\n",
       "      <td>145530</td>\n",
       "      <td>113384</td>\n",
       "      <td>125127.0</td>\n",
       "      <td>123639</td>\n",
       "      <td>5255</td>\n",
       "      <td>1028</td>\n",
       "      <td>972055</td>\n",
       "      <td>31.7</td>\n",
       "      <td>15.4</td>\n",
       "      <td>15.0</td>\n",
       "      <td>62.1</td>\n",
       "      <td>11.7</td>\n",
       "      <td>12.9</td>\n",
       "      <td>12.7</td>\n",
       "      <td>0.5</td>\n",
       "      <td>0.1</td>\n",
       "      <td>2020-2021-SPRING</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>6to8</td>\n",
       "      <td>SPRINGSEMESTER20202021</td>\n",
       "      <td>204608</td>\n",
       "      <td>106101</td>\n",
       "      <td>107037</td>\n",
       "      <td>101245</td>\n",
       "      <td>71660.0</td>\n",
       "      <td>86882</td>\n",
       "      <td>2168</td>\n",
       "      <td>137</td>\n",
       "      <td>679838</td>\n",
       "      <td>30.1</td>\n",
       "      <td>15.6</td>\n",
       "      <td>15.7</td>\n",
       "      <td>61.4</td>\n",
       "      <td>14.9</td>\n",
       "      <td>10.5</td>\n",
       "      <td>12.8</td>\n",
       "      <td>0.3</td>\n",
       "      <td>0.0</td>\n",
       "      <td>2020-2021-SPRING</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Grades    Semester School Year  # of As  # of Bs  # of Cs  # of Ds  \\\n",
       "0   9to12    FALLSEMESTER20182019   301382   217981   189163    90078   \n",
       "6    6to8    FALLSEMESTER20182019   221624   165766   130205    67168   \n",
       "1   9to12  SPRINGSEMESTER20182019   299486   210384   192475    88218   \n",
       "7    6to8  SPRINGSEMESTER20182019   214864   159205   131509    67244   \n",
       "2   9to12    FALLSEMESTER20192020   306029   216670   186161    88136   \n",
       "8    6to8    FALLSEMESTER20192020   221648   163995   126887    65126   \n",
       "4   9to12    FALLSEMESTER20202021   313109   157401   148168   121639   \n",
       "10   6to8    FALLSEMESTER20202021   204876   112867   109613   114456   \n",
       "5   9to12  SPRINGSEMESTER20202021   308459   149633   145530   113384   \n",
       "11   6to8  SPRINGSEMESTER20202021   204608   106101   107037   101245   \n",
       "\n",
       "     # of Fs  # of Ps  # of Ns  # of Is  Total Marks   % A   % B   % C  A-C %  \\\n",
       "0   101893.0    11342    21044     1308       934191  32.3  23.3  20.2   75.8   \n",
       "6    54945.0    10174    66995      155       717032  30.9  23.1  18.2   72.2   \n",
       "1   103546.0    15044    22173     1200       932526  32.1  22.6  20.6   75.3   \n",
       "7    65109.0     9029    67722       64       714746  30.1  22.3  18.4   70.7   \n",
       "2    95144.0    11645    19570     1149       924504  33.1  23.4  20.1   76.7   \n",
       "8    51454.0     9242    58241       77       696670  31.8  23.5  18.2   73.6   \n",
       "4   106001.0   127461     3566      372       977717  32.0  16.1  15.2   63.3   \n",
       "10   48517.0    90312     2687       26       683354  30.0  16.5  16.0   62.5   \n",
       "5   125127.0   123639     5255     1028       972055  31.7  15.4  15.0   62.1   \n",
       "11   71660.0    86882     2168      137       679838  30.1  15.6  15.7   61.4   \n",
       "\n",
       "     % D   % F   % P  % N  % I       school_year  \n",
       "0    9.6  10.9   1.2  2.3  0.1    2018-2019-FALL  \n",
       "6    9.4   7.7   1.4  9.3  0.0    2018-2019-FALL  \n",
       "1    9.5  11.1   1.6  2.4  0.1  2018-2019-SPRING  \n",
       "7    9.4   9.1   1.3  9.5  0.0  2018-2019-SPRING  \n",
       "2    9.5  10.3   1.3  2.1  0.1    2019-2020-FALL  \n",
       "8    9.3   7.4   1.3  8.4  0.0    2019-2020-FALL  \n",
       "4   12.4  10.8  13.0  0.4  0.0    2020-2021-FALL  \n",
       "10  16.7   7.1  13.2  0.4  0.0    2020-2021-FALL  \n",
       "5   11.7  12.9  12.7  0.5  0.1  2020-2021-SPRING  \n",
       "11  14.9  10.5  12.8  0.3  0.0  2020-2021-SPRING  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grades_trimmed"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "aad9b03d-b227-4674-bd32-93d6a8d40688",
   "metadata": {},
   "outputs": [],
   "source": [
    "grades_trim = grades_trimmed[['Grades', 'Semester School Year', '# of As', '# of Bs', '# of Cs',\n",
    "       '# of Ds', '# of Fs','Total Marks','school_year']].copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "228491e7-d980-4e2a-bc3a-0ec5e286d9fe",
   "metadata": {},
   "source": [
    "Groupby school year and sum"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "7905c692-c62a-4f5e-a0ea-0a843db7eff7",
   "metadata": {},
   "outputs": [],
   "source": [
    "grades_combined = grades_trim.groupby(\"school_year\").sum().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "742d339b-2487-43af-b563-641566e1a433",
   "metadata": {},
   "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>school_year</th>\n",
       "      <th># of As</th>\n",
       "      <th># of Bs</th>\n",
       "      <th># of Cs</th>\n",
       "      <th># of Ds</th>\n",
       "      <th># of Fs</th>\n",
       "      <th>Total Marks</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-2019-FALL</td>\n",
       "      <td>523006</td>\n",
       "      <td>383747</td>\n",
       "      <td>319368</td>\n",
       "      <td>157246</td>\n",
       "      <td>156838.0</td>\n",
       "      <td>1651223</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-2019-SPRING</td>\n",
       "      <td>514350</td>\n",
       "      <td>369589</td>\n",
       "      <td>323984</td>\n",
       "      <td>155462</td>\n",
       "      <td>168655.0</td>\n",
       "      <td>1647272</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-2020-FALL</td>\n",
       "      <td>527677</td>\n",
       "      <td>380665</td>\n",
       "      <td>313048</td>\n",
       "      <td>153262</td>\n",
       "      <td>146598.0</td>\n",
       "      <td>1621174</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-2021-FALL</td>\n",
       "      <td>517985</td>\n",
       "      <td>270268</td>\n",
       "      <td>257781</td>\n",
       "      <td>236095</td>\n",
       "      <td>154518.0</td>\n",
       "      <td>1661071</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-2021-SPRING</td>\n",
       "      <td>513067</td>\n",
       "      <td>255734</td>\n",
       "      <td>252567</td>\n",
       "      <td>214629</td>\n",
       "      <td>196787.0</td>\n",
       "      <td>1651893</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        school_year  # of As  # of Bs  # of Cs  # of Ds   # of Fs  Total Marks\n",
       "0    2018-2019-FALL   523006   383747   319368   157246  156838.0      1651223\n",
       "1  2018-2019-SPRING   514350   369589   323984   155462  168655.0      1647272\n",
       "2    2019-2020-FALL   527677   380665   313048   153262  146598.0      1621174\n",
       "3    2020-2021-FALL   517985   270268   257781   236095  154518.0      1661071\n",
       "4  2020-2021-SPRING   513067   255734   252567   214629  196787.0      1651893"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grades_combined"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5579e278-87b1-48c5-9960-bab16ab044a0",
   "metadata": {},
   "source": [
    "Sum up letter grades"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "ad7e3b09-2325-4d4c-a6e5-7920c9d8bd05",
   "metadata": {},
   "outputs": [],
   "source": [
    "grades_combined[\"total_letter_grades\"] = grades_combined[['# of As', '# of Bs', '# of Cs',\n",
    "       '# of Ds', '# of Fs']].sum(axis=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b9df09e-2fad-4bd9-bea0-d08477108d4a",
   "metadata": {},
   "source": [
    "Get share of the grades"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "13ffa3a2-6c8e-4a92-b4b5-ad08e0e9d8c7",
   "metadata": {},
   "outputs": [],
   "source": [
    "def create_pct(df):\n",
    "    cols = [\"A\",\"B\",\"C\",\"D\",\"F\"]\n",
    "    for c in cols:\n",
    "        df[f\"{c}%\"] = df[f\"# of {c}s\"]/df.total_letter_grades *100\n",
    "        \n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "cc1e4621-f719-494c-90f0-15221490d86e",
   "metadata": {},
   "outputs": [],
   "source": [
    "grades_combined_pct = create_pct(grades_combined)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "58b4b806-d0a1-40ee-8fcb-770e176849d9",
   "metadata": {},
   "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>school_year</th>\n",
       "      <th># of As</th>\n",
       "      <th># of Bs</th>\n",
       "      <th># of Cs</th>\n",
       "      <th># of Ds</th>\n",
       "      <th># of Fs</th>\n",
       "      <th>Total Marks</th>\n",
       "      <th>total_letter_grades</th>\n",
       "      <th>A%</th>\n",
       "      <th>B%</th>\n",
       "      <th>C%</th>\n",
       "      <th>D%</th>\n",
       "      <th>F%</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-2019-FALL</td>\n",
       "      <td>523006</td>\n",
       "      <td>383747</td>\n",
       "      <td>319368</td>\n",
       "      <td>157246</td>\n",
       "      <td>156838.0</td>\n",
       "      <td>1651223</td>\n",
       "      <td>1540205.0</td>\n",
       "      <td>33.956908</td>\n",
       "      <td>24.915320</td>\n",
       "      <td>20.735422</td>\n",
       "      <td>10.209420</td>\n",
       "      <td>10.182930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-2019-SPRING</td>\n",
       "      <td>514350</td>\n",
       "      <td>369589</td>\n",
       "      <td>323984</td>\n",
       "      <td>155462</td>\n",
       "      <td>168655.0</td>\n",
       "      <td>1647272</td>\n",
       "      <td>1532040.0</td>\n",
       "      <td>33.572883</td>\n",
       "      <td>24.123978</td>\n",
       "      <td>21.147229</td>\n",
       "      <td>10.147385</td>\n",
       "      <td>11.008525</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019-2020-FALL</td>\n",
       "      <td>527677</td>\n",
       "      <td>380665</td>\n",
       "      <td>313048</td>\n",
       "      <td>153262</td>\n",
       "      <td>146598.0</td>\n",
       "      <td>1621174</td>\n",
       "      <td>1521250.0</td>\n",
       "      <td>34.687067</td>\n",
       "      <td>25.023172</td>\n",
       "      <td>20.578340</td>\n",
       "      <td>10.074741</td>\n",
       "      <td>9.636680</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-2021-FALL</td>\n",
       "      <td>517985</td>\n",
       "      <td>270268</td>\n",
       "      <td>257781</td>\n",
       "      <td>236095</td>\n",
       "      <td>154518.0</td>\n",
       "      <td>1661071</td>\n",
       "      <td>1436647.0</td>\n",
       "      <td>36.055134</td>\n",
       "      <td>18.812415</td>\n",
       "      <td>17.943239</td>\n",
       "      <td>16.433752</td>\n",
       "      <td>10.755460</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-2021-SPRING</td>\n",
       "      <td>513067</td>\n",
       "      <td>255734</td>\n",
       "      <td>252567</td>\n",
       "      <td>214629</td>\n",
       "      <td>196787.0</td>\n",
       "      <td>1651893</td>\n",
       "      <td>1432784.0</td>\n",
       "      <td>35.809096</td>\n",
       "      <td>17.848748</td>\n",
       "      <td>17.627709</td>\n",
       "      <td>14.979857</td>\n",
       "      <td>13.734589</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        school_year  # of As  # of Bs  # of Cs  # of Ds   # of Fs  \\\n",
       "0    2018-2019-FALL   523006   383747   319368   157246  156838.0   \n",
       "1  2018-2019-SPRING   514350   369589   323984   155462  168655.0   \n",
       "2    2019-2020-FALL   527677   380665   313048   153262  146598.0   \n",
       "3    2020-2021-FALL   517985   270268   257781   236095  154518.0   \n",
       "4  2020-2021-SPRING   513067   255734   252567   214629  196787.0   \n",
       "\n",
       "   Total Marks  total_letter_grades         A%         B%         C%  \\\n",
       "0      1651223            1540205.0  33.956908  24.915320  20.735422   \n",
       "1      1647272            1532040.0  33.572883  24.123978  21.147229   \n",
       "2      1621174            1521250.0  34.687067  25.023172  20.578340   \n",
       "3      1661071            1436647.0  36.055134  18.812415  17.943239   \n",
       "4      1651893            1432784.0  35.809096  17.848748  17.627709   \n",
       "\n",
       "          D%         F%  \n",
       "0  10.209420  10.182930  \n",
       "1  10.147385  11.008525  \n",
       "2  10.074741   9.636680  \n",
       "3  16.433752  10.755460  \n",
       "4  14.979857  13.734589  "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "grades_combined_pct"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bb20e0b4-91d8-4d16-84fa-6df603ac5af2",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Findings"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3421fb2-af02-4ad2-80ab-081d38b8c561",
   "metadata": {},
   "source": [
    "Pre-pandemic average share of Ds & Fs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "2765d53c-5c3f-4155-bbd8-0dbcf4593913",
   "metadata": {},
   "outputs": [],
   "source": [
    "prePandemic = grades_combined_pct[~grades_combined_pct.school_year.isin([\"2020-2021-FALL\",\"2020-2021-SPRING\"])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "90316563-3d59-4070-b581-6e3163fb15ac",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.19067563285253541"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "prePandemic[[\"# of Ds\",\"# of Fs\"]].values.sum()/prePandemic[\"Total Marks\"].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de7309a1-eb98-42db-b131-478818b4f9c6",
   "metadata": {},
   "source": [
    "Pandemic average share of Ds & Fs"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "1a34ba3f-8067-4214-b8c9-a5570b0e00f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "pandemic = grades_combined_pct[grades_combined_pct.school_year.isin([\"2020-2021-FALL\",\"2020-2021-SPRING\"])]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "id": "2726b1cd-99c9-439d-9d62-4f8ef693417d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.24208805166612132"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pandemic[[\"# of Ds\",\"# of Fs\"]].values.sum()/pandemic[\"Total Marks\"].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c042a552-dd39-4d30-9758-f7c5064c499e",
   "metadata": {},
   "source": [
    "Prep for chart"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "id": "b2f41640-5043-4570-bd98-088fde3d3267",
   "metadata": {},
   "outputs": [],
   "source": [
    "grades_chart = grades_combined_pct.melt(id_vars=[\"school_year\"], value_vars=grades_combined_pct.columns[8:])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "id": "c9cdabdb-ef5f-4765-a29b-072ca295267d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<div id=\"altair-viz-428821c1016947a9826e4bc8cd59ff47\"></div>\n",
       "<script type=\"text/javascript\">\n",
       "  (function(spec, embedOpt){\n",
       "    let outputDiv = document.currentScript.previousElementSibling;\n",
       "    if (outputDiv.id !== \"altair-viz-428821c1016947a9826e4bc8cd59ff47\") {\n",
       "      outputDiv = document.getElementById(\"altair-viz-428821c1016947a9826e4bc8cd59ff47\");\n",
       "    }\n",
       "    const paths = {\n",
       "      \"vega\": \"https://cdn.jsdelivr.net/npm//vega@5?noext\",\n",
       "      \"vega-lib\": \"https://cdn.jsdelivr.net/npm//vega-lib?noext\",\n",
       "      \"vega-lite\": \"https://cdn.jsdelivr.net/npm//vega-lite@4.8.1?noext\",\n",
       "      \"vega-embed\": \"https://cdn.jsdelivr.net/npm//vega-embed@6?noext\",\n",
       "    };\n",
       "\n",
       "    function loadScript(lib) {\n",
       "      return new Promise(function(resolve, reject) {\n",
       "        var s = document.createElement('script');\n",
       "        s.src = paths[lib];\n",
       "        s.async = true;\n",
       "        s.onload = () => resolve(paths[lib]);\n",
       "        s.onerror = () => reject(`Error loading script: ${paths[lib]}`);\n",
       "        document.getElementsByTagName(\"head\")[0].appendChild(s);\n",
       "      });\n",
       "    }\n",
       "\n",
       "    function showError(err) {\n",
       "      outputDiv.innerHTML = `<div class=\"error\" style=\"color:red;\">${err}</div>`;\n",
       "      throw err;\n",
       "    }\n",
       "\n",
       "    function displayChart(vegaEmbed) {\n",
       "      vegaEmbed(outputDiv, spec, embedOpt)\n",
       "        .catch(err => showError(`Javascript Error: ${err.message}<br>This usually means there's a typo in your chart specification. See the javascript console for the full traceback.`));\n",
       "    }\n",
       "\n",
       "    if(typeof define === \"function\" && define.amd) {\n",
       "      requirejs.config({paths});\n",
       "      require([\"vega-embed\"], displayChart, err => showError(`Error loading script: ${err.message}`));\n",
       "    } else if (typeof vegaEmbed === \"function\") {\n",
       "      displayChart(vegaEmbed);\n",
       "    } else {\n",
       "      loadScript(\"vega\")\n",
       "        .then(() => loadScript(\"vega-lite\"))\n",
       "        .then(() => loadScript(\"vega-embed\"))\n",
       "        .catch(showError)\n",
       "        .then(() => displayChart(vegaEmbed));\n",
       "    }\n",
       "  })({\"config\": {\"view\": {\"continuousWidth\": 400, \"continuousHeight\": 300}}, \"data\": {\"name\": \"data-e3decd3fc39f2ed09ec59986b3cd2f5f\"}, \"mark\": \"bar\", \"encoding\": {\"color\": {\"type\": \"nominal\", \"field\": \"variable\"}, \"x\": {\"type\": \"nominal\", \"field\": \"school_year\"}, \"y\": {\"type\": \"quantitative\", \"field\": \"value\"}}, \"height\": 300, \"title\": \"Grades breakdown for Fall semesters\", \"width\": 400, \"$schema\": \"https://vega.github.io/schema/vega-lite/v4.8.1.json\", \"datasets\": {\"data-e3decd3fc39f2ed09ec59986b3cd2f5f\": [{\"school_year\": \"2018-2019-FALL\", \"variable\": \"A%\", \"value\": 33.95690833363091}, {\"school_year\": \"2018-2019-SPRING\", \"variable\": \"A%\", \"value\": 33.57288321453748}, {\"school_year\": \"2019-2020-FALL\", \"variable\": \"A%\", \"value\": 34.68706655710764}, {\"school_year\": \"2020-2021-FALL\", \"variable\": \"A%\", \"value\": 36.05513393338795}, {\"school_year\": \"2020-2021-SPRING\", \"variable\": \"A%\", \"value\": 35.8090961373103}, {\"school_year\": \"2018-2019-FALL\", \"variable\": \"B%\", \"value\": 24.915319713934185}, {\"school_year\": \"2018-2019-SPRING\", \"variable\": \"B%\", \"value\": 24.123978486201406}, {\"school_year\": \"2019-2020-FALL\", \"variable\": \"B%\", \"value\": 25.02317173377157}, {\"school_year\": \"2020-2021-FALL\", \"variable\": \"B%\", \"value\": 18.812415297564396}, {\"school_year\": \"2020-2021-SPRING\", \"variable\": \"B%\", \"value\": 17.84874761303867}, {\"school_year\": \"2018-2019-FALL\", \"variable\": \"C%\", \"value\": 20.73542158349051}, {\"school_year\": \"2018-2019-SPRING\", \"variable\": \"C%\", \"value\": 21.147228531892118}, {\"school_year\": \"2019-2020-FALL\", \"variable\": \"C%\", \"value\": 20.578340180772393}, {\"school_year\": \"2020-2021-FALL\", \"variable\": \"C%\", \"value\": 17.943238666144154}, {\"school_year\": \"2020-2021-SPRING\", \"variable\": \"C%\", \"value\": 17.627709410490347}, {\"school_year\": \"2018-2019-FALL\", \"variable\": \"D%\", \"value\": 10.209420174587148}, {\"school_year\": \"2018-2019-SPRING\", \"variable\": \"D%\", \"value\": 10.147385185765383}, {\"school_year\": \"2019-2020-FALL\", \"variable\": \"D%\", \"value\": 10.074741166803616}, {\"school_year\": \"2020-2021-FALL\", \"variable\": \"D%\", \"value\": 16.433751645324147}, {\"school_year\": \"2020-2021-SPRING\", \"variable\": \"D%\", \"value\": 14.979857396509175}, {\"school_year\": \"2018-2019-FALL\", \"variable\": \"F%\", \"value\": 10.182930194357246}, {\"school_year\": \"2018-2019-SPRING\", \"variable\": \"F%\", \"value\": 11.008524581603615}, {\"school_year\": \"2019-2020-FALL\", \"variable\": \"F%\", \"value\": 9.636680361544782}, {\"school_year\": \"2020-2021-FALL\", \"variable\": \"F%\", \"value\": 10.75546045757935}, {\"school_year\": \"2020-2021-SPRING\", \"variable\": \"F%\", \"value\": 13.73458944265151}]}}, {\"mode\": \"vega-lite\"});\n",
       "</script>"
      ],
      "text/plain": [
       "alt.Chart(...)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "alt.Chart(grades_chart).mark_bar().encode(\n",
    "    x='school_year',\n",
    "    y='value',\n",
    "    color='variable',  \n",
    ").properties(\n",
    "    title='Grades breakdown for Fall semesters',\n",
    "    width=400,\n",
    "    height=300\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b7eb179-a717-43fd-af00-e46268f7cef9",
   "metadata": {},
   "source": [
    "Export"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "id": "e7e89187-dd3d-4f56-948b-e941b7a934e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "grades_combined_pct.to_csv(\"output/combined.csv\",index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b36a56a9-bac9-4e93-a45e-a26d716feb25",
   "metadata": {},
   "source": [
    "# Detailed break down of grades by ethnicy and student groups"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "id": "2a3b6120-9362-4272-92b3-03e4141fff36",
   "metadata": {},
   "outputs": [],
   "source": [
    "hs_grades = pd.read_csv(\"input/grades9-12-complete.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce616daa-2a66-4a70-bb6d-da4eddce03f5",
   "metadata": {},
   "source": [
    "## High school"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "id": "caef462c-36b6-404f-8723-75f853edccfe",
   "metadata": {},
   "outputs": [],
   "source": [
    "hs_grades_clean[\"school_year\"] = hs_grades_clean[\"Semester School Year\"].apply(simplify)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0bb54676-96d4-4bd2-b34b-44a60b87682c",
   "metadata": {},
   "source": [
    "Clean up"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "id": "0ac8137f-f620-4202-a222-2246b9b01324",
   "metadata": {},
   "outputs": [],
   "source": [
    "hs_grades_clean.columns = [x.replace(\"'\",\"\") for x in hs_grades_clean.columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "id": "fab55e05-6a7a-4ccd-bd6e-ff3f7c9f2324",
   "metadata": {},
   "outputs": [],
   "source": [
    "hs_no_col_list = hs_grades_clean.columns.to_list()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "id": "9f6bcae7-8d7f-4133-9744-80ba76e28185",
   "metadata": {},
   "outputs": [],
   "source": [
    "hs_grades_clean = hs_grades.applymap(cleanup)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c427ed6e-16cd-455b-b771-315724314279",
   "metadata": {},
   "outputs": [],
   "source": [
    "hs_no_col_list = [ s for s in hs_no_col_list if s not in [\"type\",\"groupname\",\"Semester School Year\",\"school_year\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "70f6f2af-70b6-4e25-91b4-db38c54503ba",
   "metadata": {},
   "source": [
    "Numeric columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "id": "db8018ed-4902-426c-9274-13478ae43917",
   "metadata": {},
   "outputs": [],
   "source": [
    "hs_grades_clean = hs_grades_clean.apply(pd.to_numeric, errors=\"ignore\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "53bb5c0f-b6e0-469d-bf2d-57a6b5c76c42",
   "metadata": {},
   "source": [
    "Trim to 2019 Fall and 2021 Spring"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "id": "c8a0d99f-712f-4172-9222-c8b125693a20",
   "metadata": {},
   "outputs": [],
   "source": [
    "hs_1921 = hs_grades_clean[hs_grades_clean.school_year.isin([\"2019-2020-FALL\",\"2020-2021-SPRING\"])].sort_values(\"school_year\").copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9264d17d-a7cd-4ff8-aea9-e6fed5b7b37f",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Middle School"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "id": "dbd95685-c287-4d5c-a47e-593dfc687e4f",
   "metadata": {},
   "outputs": [],
   "source": [
    "middle = pd.read_csv(\"input/grades6-8-complete.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ccadffc-d70a-402f-918a-3d8e672785ee",
   "metadata": {},
   "source": [
    "Clean up"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "id": "42db9b11-6edd-4097-960e-bcc21a5e380e",
   "metadata": {},
   "outputs": [],
   "source": [
    "ms_grades_clean = middle.applymap(cleanup)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "id": "3bbff986-f473-4efb-bf8c-9545f25ff8ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "ms_grades_clean.columns = [x.replace(\"'\",\"\") for x in ms_grades_clean.columns]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "id": "33f92b50-5164-416f-85f8-b95d538da04b",
   "metadata": {},
   "outputs": [],
   "source": [
    "ms_grades_clean[\"school_year\"] = ms_grades_clean[\"Semester School Year\"].apply(simplify)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f5a8801-c06d-40d6-8684-aaa76caa33a9",
   "metadata": {},
   "source": [
    "Numeric columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "id": "dde4f39f-4749-4d55-98b2-530ee2dd3976",
   "metadata": {},
   "outputs": [],
   "source": [
    "ms_grades_clean = ms_grades_clean.apply(pd.to_numeric, errors=\"ignore\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "447e8702-6330-4714-a693-ebbe11128b44",
   "metadata": {},
   "source": [
    "Trim to 2019 Fall and 2021 Spring"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "id": "3a55eca4-04c1-41f7-aece-1379919589c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "ms_1921 = ms_grades_clean[ms_grades_clean.school_year.isin([\"2019-2020-FALL\",\"2020-2021-SPRING\"])].sort_values(\"school_year\").copy()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e7c666c-e578-4369-959e-4d5a173c13ba",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Combine all grades"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "616c6ce7-f598-4199-b421-54fca391fe37",
   "metadata": {},
   "outputs": [],
   "source": [
    "combined_cols = ['type','groupname', 'Semester School Year', '# of As', '# of Bs',\n",
    "       '# of Cs', '# of Ds', '# of Fs','school_year']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "id": "a44b5d08-1337-420b-9f39-4706aeafab26",
   "metadata": {},
   "outputs": [],
   "source": [
    "hs_ms_1921 = pd.concat([ms_1921[combined_cols],hs_1921[combined_cols]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "id": "a64e1805-e7e9-4816-bce2-59551c8d4d56",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "ethnicity    36\n",
       "group        12\n",
       "Name: type, dtype: int64"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hs_ms_1921.type.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "id": "1054ae9a-5398-4a2b-bcca-0ac6e0f6b917",
   "metadata": {},
   "outputs": [],
   "source": [
    "hs_ms_combined= hs_ms_1921.groupby([\"type\",\"groupname\",\"school_year\"]).sum().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "id": "01a42cca-907b-4314-92aa-7675f65f1ef3",
   "metadata": {},
   "outputs": [],
   "source": [
    "hs_ms_combined[\"total_letter_grades\"] = hs_ms_combined[['# of As', '# of Bs', '# of Cs',\n",
    "       '# of Ds', '# of Fs']].sum(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "id": "82d23550-ce94-4610-b89b-0ba5efa2bc5d",
   "metadata": {},
   "outputs": [],
   "source": [
    "hs_ms_grouped_pct = create_pct(hs_ms_combined)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "id": "6617d309-d87f-48d7-ab8f-acea8ad3d2cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "hs_ms_grouped_pct[\"A-C%\"] = hs_ms_grouped_pct[['A%', 'B%', 'C%']].sum(axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "id": "08e843b0-8587-4e95-9385-1d2f58cab852",
   "metadata": {},
   "outputs": [],
   "source": [
    "hs_ms_grouped_pct[\"groupname\"] = hs_ms_grouped_pct[\"groupname\"].apply(lambda x: x.lower())"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "354dd23a-d56b-44de-a116-5782e49b3766",
   "metadata": {},
   "source": [
    "## Calculate changes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "id": "5d8bb6bd-6a6b-437f-b835-4afd30f8bb18",
   "metadata": {},
   "outputs": [],
   "source": [
    "hs_ms_grouped_pct.reset_index(drop=True, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "id": "74d8a947-093f-4783-8499-aded493d0e73",
   "metadata": {},
   "outputs": [],
   "source": [
    "hs_ms_grouped_pct[\"A-C%_pt_change\"] = hs_ms_grouped_pct.groupby([\"groupname\",\"type\"])[\"A-C%\"].diff()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b11852b3-3369-47d6-ace3-5266e8456cb4",
   "metadata": {},
   "source": [
    "Which group had large changes?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "id": "4c7e0c95-60a3-4d04-9f80-36dbae3a1890",
   "metadata": {},
   "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>type</th>\n",
       "      <th>groupname</th>\n",
       "      <th>school_year</th>\n",
       "      <th># of As</th>\n",
       "      <th># of Bs</th>\n",
       "      <th># of Cs</th>\n",
       "      <th># of Ds</th>\n",
       "      <th># of Fs</th>\n",
       "      <th>total_letter_grades</th>\n",
       "      <th>A%</th>\n",
       "      <th>B%</th>\n",
       "      <th>C%</th>\n",
       "      <th>D%</th>\n",
       "      <th>F%</th>\n",
       "      <th>A-C%</th>\n",
       "      <th>A-C%_pt_change</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>ethnicity</td>\n",
       "      <td>pacificislander</td>\n",
       "      <td>2020-2021-SPRING</td>\n",
       "      <td>565</td>\n",
       "      <td>248</td>\n",
       "      <td>253</td>\n",
       "      <td>267</td>\n",
       "      <td>224.0</td>\n",
       "      <td>1557.0</td>\n",
       "      <td>36.287733</td>\n",
       "      <td>15.928067</td>\n",
       "      <td>16.249197</td>\n",
       "      <td>17.148362</td>\n",
       "      <td>14.386641</td>\n",
       "      <td>68.464997</td>\n",
       "      <td>-15.038247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>group</td>\n",
       "      <td>englishlearners</td>\n",
       "      <td>2020-2021-SPRING</td>\n",
       "      <td>39488</td>\n",
       "      <td>28398</td>\n",
       "      <td>36599</td>\n",
       "      <td>38100</td>\n",
       "      <td>36676.0</td>\n",
       "      <td>179261.0</td>\n",
       "      <td>22.028216</td>\n",
       "      <td>15.841706</td>\n",
       "      <td>20.416599</td>\n",
       "      <td>21.253926</td>\n",
       "      <td>20.459553</td>\n",
       "      <td>58.286521</td>\n",
       "      <td>-11.797839</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>ethnicity</td>\n",
       "      <td>hispanic</td>\n",
       "      <td>2020-2021-SPRING</td>\n",
       "      <td>346879</td>\n",
       "      <td>193100</td>\n",
       "      <td>200614</td>\n",
       "      <td>176182</td>\n",
       "      <td>166369.0</td>\n",
       "      <td>1083144.0</td>\n",
       "      <td>32.025197</td>\n",
       "      <td>17.827731</td>\n",
       "      <td>18.521452</td>\n",
       "      <td>16.265797</td>\n",
       "      <td>15.359823</td>\n",
       "      <td>68.374381</td>\n",
       "      <td>-10.205949</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>group</td>\n",
       "      <td>fostercareyouth</td>\n",
       "      <td>2020-2021-SPRING</td>\n",
       "      <td>3182</td>\n",
       "      <td>2343</td>\n",
       "      <td>3091</td>\n",
       "      <td>3382</td>\n",
       "      <td>3499.0</td>\n",
       "      <td>15497.0</td>\n",
       "      <td>20.533006</td>\n",
       "      <td>15.119055</td>\n",
       "      <td>19.945796</td>\n",
       "      <td>21.823579</td>\n",
       "      <td>22.578564</td>\n",
       "      <td>55.597858</td>\n",
       "      <td>-10.189321</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>group</td>\n",
       "      <td>socioeconomicallydisadvantaged</td>\n",
       "      <td>2020-2021-SPRING</td>\n",
       "      <td>414208</td>\n",
       "      <td>222253</td>\n",
       "      <td>230091</td>\n",
       "      <td>199825</td>\n",
       "      <td>184058.0</td>\n",
       "      <td>1250435.0</td>\n",
       "      <td>33.125112</td>\n",
       "      <td>17.774055</td>\n",
       "      <td>18.400876</td>\n",
       "      <td>15.980439</td>\n",
       "      <td>14.719518</td>\n",
       "      <td>69.300044</td>\n",
       "      <td>-9.774429</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>ethnicity</td>\n",
       "      <td>twoormore</td>\n",
       "      <td>2020-2021-SPRING</td>\n",
       "      <td>1585</td>\n",
       "      <td>768</td>\n",
       "      <td>653</td>\n",
       "      <td>561</td>\n",
       "      <td>473.0</td>\n",
       "      <td>4040.0</td>\n",
       "      <td>39.232673</td>\n",
       "      <td>19.009901</td>\n",
       "      <td>16.163366</td>\n",
       "      <td>13.886139</td>\n",
       "      <td>11.707921</td>\n",
       "      <td>74.405941</td>\n",
       "      <td>-9.108932</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ethnicity</td>\n",
       "      <td>americanindian/alaskanative</td>\n",
       "      <td>2020-2021-SPRING</td>\n",
       "      <td>639</td>\n",
       "      <td>377</td>\n",
       "      <td>366</td>\n",
       "      <td>312</td>\n",
       "      <td>211.0</td>\n",
       "      <td>1905.0</td>\n",
       "      <td>33.543307</td>\n",
       "      <td>19.790026</td>\n",
       "      <td>19.212598</td>\n",
       "      <td>16.377953</td>\n",
       "      <td>11.076115</td>\n",
       "      <td>72.545932</td>\n",
       "      <td>-7.645273</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>ethnicity</td>\n",
       "      <td>other</td>\n",
       "      <td>2020-2021-SPRING</td>\n",
       "      <td>13180</td>\n",
       "      <td>4429</td>\n",
       "      <td>3441</td>\n",
       "      <td>2553</td>\n",
       "      <td>1977.0</td>\n",
       "      <td>25580.0</td>\n",
       "      <td>51.524629</td>\n",
       "      <td>17.314308</td>\n",
       "      <td>13.451916</td>\n",
       "      <td>9.980453</td>\n",
       "      <td>7.728694</td>\n",
       "      <td>82.290852</td>\n",
       "      <td>-6.589512</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>ethnicity</td>\n",
       "      <td>black</td>\n",
       "      <td>2020-2021-SPRING</td>\n",
       "      <td>32663</td>\n",
       "      <td>20428</td>\n",
       "      <td>22533</td>\n",
       "      <td>18524</td>\n",
       "      <td>14035.0</td>\n",
       "      <td>108183.0</td>\n",
       "      <td>30.192359</td>\n",
       "      <td>18.882819</td>\n",
       "      <td>20.828596</td>\n",
       "      <td>17.122838</td>\n",
       "      <td>12.973388</td>\n",
       "      <td>69.903774</td>\n",
       "      <td>-6.072148</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>ethnicity</td>\n",
       "      <td>white</td>\n",
       "      <td>2020-2021-SPRING</td>\n",
       "      <td>66049</td>\n",
       "      <td>22964</td>\n",
       "      <td>16389</td>\n",
       "      <td>11020</td>\n",
       "      <td>9435.0</td>\n",
       "      <td>125857.0</td>\n",
       "      <td>52.479401</td>\n",
       "      <td>18.246105</td>\n",
       "      <td>13.021922</td>\n",
       "      <td>8.755969</td>\n",
       "      <td>7.496603</td>\n",
       "      <td>83.747428</td>\n",
       "      <td>-5.006683</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         type                       groupname       school_year  # of As  \\\n",
       "13  ethnicity                 pacificislander  2020-2021-SPRING      565   \n",
       "19      group                 englishlearners  2020-2021-SPRING    39488   \n",
       "9   ethnicity                        hispanic  2020-2021-SPRING   346879   \n",
       "21      group                 fostercareyouth  2020-2021-SPRING     3182   \n",
       "23      group  socioeconomicallydisadvantaged  2020-2021-SPRING   414208   \n",
       "15  ethnicity                       twoormore  2020-2021-SPRING     1585   \n",
       "1   ethnicity     americanindian/alaskanative  2020-2021-SPRING      639   \n",
       "11  ethnicity                           other  2020-2021-SPRING    13180   \n",
       "5   ethnicity                           black  2020-2021-SPRING    32663   \n",
       "17  ethnicity                           white  2020-2021-SPRING    66049   \n",
       "\n",
       "    # of Bs  # of Cs  # of Ds   # of Fs  total_letter_grades         A%  \\\n",
       "13      248      253      267     224.0               1557.0  36.287733   \n",
       "19    28398    36599    38100   36676.0             179261.0  22.028216   \n",
       "9    193100   200614   176182  166369.0            1083144.0  32.025197   \n",
       "21     2343     3091     3382    3499.0              15497.0  20.533006   \n",
       "23   222253   230091   199825  184058.0            1250435.0  33.125112   \n",
       "15      768      653      561     473.0               4040.0  39.232673   \n",
       "1       377      366      312     211.0               1905.0  33.543307   \n",
       "11     4429     3441     2553    1977.0              25580.0  51.524629   \n",
       "5     20428    22533    18524   14035.0             108183.0  30.192359   \n",
       "17    22964    16389    11020    9435.0             125857.0  52.479401   \n",
       "\n",
       "           B%         C%         D%         F%       A-C%  A-C%_pt_change  \n",
       "13  15.928067  16.249197  17.148362  14.386641  68.464997      -15.038247  \n",
       "19  15.841706  20.416599  21.253926  20.459553  58.286521      -11.797839  \n",
       "9   17.827731  18.521452  16.265797  15.359823  68.374381      -10.205949  \n",
       "21  15.119055  19.945796  21.823579  22.578564  55.597858      -10.189321  \n",
       "23  17.774055  18.400876  15.980439  14.719518  69.300044       -9.774429  \n",
       "15  19.009901  16.163366  13.886139  11.707921  74.405941       -9.108932  \n",
       "1   19.790026  19.212598  16.377953  11.076115  72.545932       -7.645273  \n",
       "11  17.314308  13.451916   9.980453   7.728694  82.290852       -6.589512  \n",
       "5   18.882819  20.828596  17.122838  12.973388  69.903774       -6.072148  \n",
       "17  18.246105  13.021922   8.755969   7.496603  83.747428       -5.006683  "
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hs_ms_grouped_pct.sort_values(\"A-C%_pt_change\").head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "375293fe-b32a-4d4b-8e8b-a43a7196e7ec",
   "metadata": {},
   "source": [
    "## Prep for chart"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "id": "4f098424-2e24-4ea6-bef3-29fac6a17aed",
   "metadata": {},
   "outputs": [],
   "source": [
    "hs_ms_final = hs_ms_grouped_pct[['groupname','school_year','A-C%']].copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "id": "904a0296-83f4-4b53-9d9c-9e023808385e",
   "metadata": {},
   "outputs": [],
   "source": [
    "hs_ms_final[\"school_year\"] = hs_ms_final[\"school_year\"].apply(lambda x: x.split(\"-\")[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "id": "0793629c-de4f-4299-bc83-c49780b11f39",
   "metadata": {},
   "outputs": [],
   "source": [
    "hs_ms_final_chart = hs_ms_final.pivot(index='school_year',columns='groupname').reset_index().droplevel(0, axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "id": "1bf3797c-dc9e-4536-97ca-e9c81e0265ef",
   "metadata": {},
   "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>groupname</th>\n",
       "      <th></th>\n",
       "      <th>americanindian/alaskanative</th>\n",
       "      <th>asian</th>\n",
       "      <th>black</th>\n",
       "      <th>englishlearners</th>\n",
       "      <th>filipino</th>\n",
       "      <th>fostercareyouth</th>\n",
       "      <th>hispanic</th>\n",
       "      <th>other</th>\n",
       "      <th>pacificislander</th>\n",
       "      <th>socioeconomicallydisadvantaged</th>\n",
       "      <th>twoormore</th>\n",
       "      <th>white</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2019</td>\n",
       "      <td>80.191205</td>\n",
       "      <td>93.939765</td>\n",
       "      <td>75.975922</td>\n",
       "      <td>70.084360</td>\n",
       "      <td>92.010628</td>\n",
       "      <td>65.787178</td>\n",
       "      <td>78.580330</td>\n",
       "      <td>88.880364</td>\n",
       "      <td>83.503244</td>\n",
       "      <td>79.074473</td>\n",
       "      <td>83.514872</td>\n",
       "      <td>88.754111</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020</td>\n",
       "      <td>72.545932</td>\n",
       "      <td>89.604501</td>\n",
       "      <td>69.903774</td>\n",
       "      <td>58.286521</td>\n",
       "      <td>87.238679</td>\n",
       "      <td>55.597858</td>\n",
       "      <td>68.374381</td>\n",
       "      <td>82.290852</td>\n",
       "      <td>68.464997</td>\n",
       "      <td>69.300044</td>\n",
       "      <td>74.405941</td>\n",
       "      <td>83.747428</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "groupname        americanindian/alaskanative      asian      black  \\\n",
       "0          2019                    80.191205  93.939765  75.975922   \n",
       "1          2020                    72.545932  89.604501  69.903774   \n",
       "\n",
       "groupname  englishlearners   filipino  fostercareyouth   hispanic      other  \\\n",
       "0                70.084360  92.010628        65.787178  78.580330  88.880364   \n",
       "1                58.286521  87.238679        55.597858  68.374381  82.290852   \n",
       "\n",
       "groupname  pacificislander  socioeconomicallydisadvantaged  twoormore  \\\n",
       "0                83.503244                       79.074473  83.514872   \n",
       "1                68.464997                       69.300044  74.405941   \n",
       "\n",
       "groupname      white  \n",
       "0          88.754111  \n",
       "1          83.747428  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "hs_ms_final_chart"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "id": "55096008-5b75-4d32-9d54-dde2298d7af6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<div id=\"altair-viz-f1f525eb737c472ebd7132755b3955d9\"></div>\n",
       "<script type=\"text/javascript\">\n",
       "  (function(spec, embedOpt){\n",
       "    let outputDiv = document.currentScript.previousElementSibling;\n",
       "    if (outputDiv.id !== \"altair-viz-f1f525eb737c472ebd7132755b3955d9\") {\n",
       "      outputDiv = document.getElementById(\"altair-viz-f1f525eb737c472ebd7132755b3955d9\");\n",
       "    }\n",
       "    const paths = {\n",
       "      \"vega\": \"https://cdn.jsdelivr.net/npm//vega@5?noext\",\n",
       "      \"vega-lib\": \"https://cdn.jsdelivr.net/npm//vega-lib?noext\",\n",
       "      \"vega-lite\": \"https://cdn.jsdelivr.net/npm//vega-lite@4.8.1?noext\",\n",
       "      \"vega-embed\": \"https://cdn.jsdelivr.net/npm//vega-embed@6?noext\",\n",
       "    };\n",
       "\n",
       "    function loadScript(lib) {\n",
       "      return new Promise(function(resolve, reject) {\n",
       "        var s = document.createElement('script');\n",
       "        s.src = paths[lib];\n",
       "        s.async = true;\n",
       "        s.onload = () => resolve(paths[lib]);\n",
       "        s.onerror = () => reject(`Error loading script: ${paths[lib]}`);\n",
       "        document.getElementsByTagName(\"head\")[0].appendChild(s);\n",
       "      });\n",
       "    }\n",
       "\n",
       "    function showError(err) {\n",
       "      outputDiv.innerHTML = `<div class=\"error\" style=\"color:red;\">${err}</div>`;\n",
       "      throw err;\n",
       "    }\n",
       "\n",
       "    function displayChart(vegaEmbed) {\n",
       "      vegaEmbed(outputDiv, spec, embedOpt)\n",
       "        .catch(err => showError(`Javascript Error: ${err.message}<br>This usually means there's a typo in your chart specification. See the javascript console for the full traceback.`));\n",
       "    }\n",
       "\n",
       "    if(typeof define === \"function\" && define.amd) {\n",
       "      requirejs.config({paths});\n",
       "      require([\"vega-embed\"], displayChart, err => showError(`Error loading script: ${err.message}`));\n",
       "    } else if (typeof vegaEmbed === \"function\") {\n",
       "      displayChart(vegaEmbed);\n",
       "    } else {\n",
       "      loadScript(\"vega\")\n",
       "        .then(() => loadScript(\"vega-lite\"))\n",
       "        .then(() => loadScript(\"vega-embed\"))\n",
       "        .catch(showError)\n",
       "        .then(() => displayChart(vegaEmbed));\n",
       "    }\n",
       "  })({\"config\": {\"view\": {\"continuousWidth\": 400, \"continuousHeight\": 300, \"stroke\": \"transparent\"}}, \"data\": {\"name\": \"data-4ede7fb6aa92a4e6215ab5cc2cb73882\"}, \"mark\": \"point\", \"encoding\": {\"color\": {\"type\": \"nominal\", \"field\": \"school_year\", \"legend\": {\"title\": \"Year\"}}, \"x\": {\"type\": \"quantitative\", \"axis\": {\"grid\": false}, \"field\": \"A-C%\", \"scale\": {\"zero\": false}, \"title\": \"A-C%\"}, \"y\": {\"type\": \"nominal\", \"axis\": {\"grid\": true}, \"field\": \"groupname\", \"sort\": \"x\", \"title\": \"\"}}, \"height\": {\"step\": 20}, \"title\": \"2019/ 2020 comparison\", \"$schema\": \"https://vega.github.io/schema/vega-lite/v4.8.1.json\", \"datasets\": {\"data-4ede7fb6aa92a4e6215ab5cc2cb73882\": [{\"groupname\": \"americanindian/alaskanative\", \"school_year\": \"2019\", \"A-C%\": 80.19120458891013}, {\"groupname\": \"americanindian/alaskanative\", \"school_year\": \"2020\", \"A-C%\": 72.54593175853017}, {\"groupname\": \"asian\", \"school_year\": \"2019\", \"A-C%\": 93.93976493633691}, {\"groupname\": \"asian\", \"school_year\": \"2020\", \"A-C%\": 89.60450063972303}, {\"groupname\": \"black\", \"school_year\": \"2019\", \"A-C%\": 75.97592166791537}, {\"groupname\": \"black\", \"school_year\": \"2020\", \"A-C%\": 69.90377416045035}, {\"groupname\": \"filipino\", \"school_year\": \"2019\", \"A-C%\": 92.01062834022765}, {\"groupname\": \"filipino\", \"school_year\": \"2020\", \"A-C%\": 87.23867892407219}, {\"groupname\": \"hispanic\", \"school_year\": \"2019\", \"A-C%\": 78.58032960933042}, {\"groupname\": \"hispanic\", \"school_year\": \"2020\", \"A-C%\": 68.37438050711631}, {\"groupname\": \"other\", \"school_year\": \"2019\", \"A-C%\": 88.88036442100216}, {\"groupname\": \"other\", \"school_year\": \"2020\", \"A-C%\": 82.29085222830336}, {\"groupname\": \"pacificislander\", \"school_year\": \"2019\", \"A-C%\": 83.5032437442076}, {\"groupname\": \"pacificislander\", \"school_year\": \"2020\", \"A-C%\": 68.46499678869621}, {\"groupname\": \"twoormore\", \"school_year\": \"2019\", \"A-C%\": 83.51487222454963}, {\"groupname\": \"twoormore\", \"school_year\": \"2020\", \"A-C%\": 74.40594059405942}, {\"groupname\": \"white\", \"school_year\": \"2019\", \"A-C%\": 88.75411089866157}, {\"groupname\": \"white\", \"school_year\": \"2020\", \"A-C%\": 83.74742763612672}, {\"groupname\": \"englishlearners\", \"school_year\": \"2019\", \"A-C%\": 70.08436014111152}, {\"groupname\": \"englishlearners\", \"school_year\": \"2020\", \"A-C%\": 58.286520771389206}, {\"groupname\": \"fostercareyouth\", \"school_year\": \"2019\", \"A-C%\": 65.78717836894899}, {\"groupname\": \"fostercareyouth\", \"school_year\": \"2020\", \"A-C%\": 55.59785764986772}, {\"groupname\": \"socioeconomicallydisadvantaged\", \"school_year\": \"2019\", \"A-C%\": 79.07447296500882}, {\"groupname\": \"socioeconomicallydisadvantaged\", \"school_year\": \"2020\", \"A-C%\": 69.30004358483248}]}}, {\"mode\": \"vega-lite\"});\n",
       "</script>"
      ],
      "text/plain": [
       "alt.Chart(...)"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "alt.Chart(hs_ms_final, title=\"2019/ 2020 comparison\").mark_point().encode(\n",
    "    alt.X(\n",
    "        'A-C%:Q',\n",
    "        title=\"A-C%\",\n",
    "        scale=alt.Scale(zero=False),\n",
    "        axis=alt.Axis(grid=False)\n",
    "    ),\n",
    "    alt.Y(\n",
    "        'groupname:N',\n",
    "        title=\"\",\n",
    "        sort='x',\n",
    "        axis=alt.Axis(grid=True)\n",
    "    ),\n",
    "    color=alt.Color('school_year:N', legend=alt.Legend(title=\"Year\")),\n",
    ").properties(\n",
    "    height=alt.Step(20)\n",
    ").configure_view(stroke=\"transparent\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 60,
   "id": "62627628-df62-462c-ae7c-3c47283f76dd",
   "metadata": {},
   "outputs": [],
   "source": [
    "hs_ms_final_chart.to_csv(\"output/combined-detail.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5651f961-fb7d-49c1-a346-dc243a3bca53",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}