enrollmentPrivate.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fff54993",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%javascript\n",
    "IPython.OutputArea.prototype._should_scroll = function(lines) {\n",
    "    return false;\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3e8be860-6c6c-4693-9108-8b1a8a518c02",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "pd.set_option('display.max_columns', None) # set the max columns to display to none\n",
    "#Private data source: https://www.sbe.wa.gov/our-work/private-schools\n",
    "d2018 = pd.read_excel('WA_privateschools/Final Excel P105B Combined Data for 201819.xlsx')\n",
    "d2019 = pd.read_excel('WA_privateschools/Copy of 2019-20 Private School Enrollment_073120.xlsx', sheet_name=1, skiprows=[0,1])\n",
    "#d2020 = pd.read_excel('WA_privateschools/Copy of 2020-21 Private School Enrollment x Grade_for the website.xlsx')\n",
    "d2020 = pd.read_excel('WA_privateschools/Fixed - Copy of 2020-21 Private School Enrollment x Grade_for the website.xlsx')\n",
    "d2021 = pd.read_excel('WA_privateschools/2021-22 Private School Enrollment (Website).xlsx')\n",
    "d2022 = pd.read_excel('WA_privateschools/2022 - 2023 Private School Enrollment (website).xlsx')\n",
    "d2023 = pd.read_excel('WA_privateschools/2023 - 2024 Private School Enrollment (website).xlsx')\n",
    "\n",
    "regionSubstring = \"ellevue\"  # Bellevue et al.  Replace with other districts and re-run if you wish"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b477fcb8-96ed-42db-8d16-ce36913747ea",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "gradeMap = {'Total PreK':'P',\n",
    " 'Total KG':'K',\n",
    " 'Total G1':'1',\n",
    " 'Total G2':'2',\n",
    " 'Total G3':'3',\n",
    " 'Total G4':'4',\n",
    " 'Total G5':'5',\n",
    " 'Total G6':'6',\n",
    " 'Total G7':'7',\n",
    " 'Total G8':'8',\n",
    " 'Total G9':'9',\n",
    " 'Total G10':'10',\n",
    " 'Total G11':'11',\n",
    " 'Total G12':'12',\n",
    " 'PK Total': 'P',\n",
    "#        'PK Total.1',\n",
    " 'KG Total': 'K',\n",
    " 'G1 Total': '1',\n",
    " 'G2 Total': '2',\n",
    " 'G3 Total': '3',\n",
    " 'G4 Total': '4',\n",
    " 'G5 Total': '5',\n",
    " 'G6 Total': '6',\n",
    " 'G7 Total': '7',\n",
    " 'G8 Total': '8',\n",
    " 'G9 Total': '9',\n",
    " 'G10 Total': '10',\n",
    " 'G11 Total': '11',\n",
    " 'G12 Total': '12',\n",
    "\n",
    " 'PreK TOTAL': 'P',\n",
    " 'KG TOTAL': 'K',\n",
    " 'G1 TOTAL': '1',\n",
    " 'G2 TOTAL': '2',\n",
    " 'G3 TOTAL': '3',\n",
    " 'G4 TOTAL': '4',\n",
    " 'G5 TOTAL': '5',\n",
    " 'G6 TOTAL': '6',\n",
    " 'G7 TOTAL': '7',\n",
    " 'G8 TOTAL': '8',\n",
    " 'G9 TOTAL': '9',\n",
    " 'G10 TOTAL': '10',\n",
    " 'GI0 TOTAL': '10',\n",
    " 'G11 TOTAL': '11',\n",
    " 'G12 TOTAL': '12',\n",
    "\n",
    " 'PreK': 'P',\n",
    " 'KG': 'K',\n",
    " 'Grade 1':'1',\n",
    " 'Grade2':'2',\n",
    " 'Grade 2':'2',\n",
    " 'Grade 3':'3',\n",
    " 'Grade 4':'4',\n",
    " 'Grade 5':'5',\n",
    " 'Grade 6':'6',\n",
    " 'Grade 7':'7',\n",
    " 'Grade 8':'8',\n",
    " 'Grade 9':'9',\n",
    " 'Grade 10':'10',\n",
    " 'Grade 11':'11',\n",
    " 'Grade 12':'12',            \n",
    "  1:'1', 2:'2', 3:'3', 4:'4', 5:'5', 6:'6', 7:'7', 8:'8', 9:'9', 10:'10', 11:'11', 12:'12'}\n",
    "\n",
    "# Final columns, except Grade, Total\n",
    "datacols = [ \n",
    "  'School Name',\n",
    "  # 'Street Address', # Missing in some\n",
    "  # Some are \"District Name\", some are \"City\"\n",
    "  # Will use to hold both and search for substrings: \"Bellevue\" \n",
    "  \"Region\",\n",
    "  # 'State',\n",
    "  # 'Zipcode', Missing in 2018\n",
    "  # 'County'\n",
    "  'Year', # Added \n",
    "]\n",
    "allFinalCols = datacols + [\"Grade\", \"Total\"]\n",
    "# Final grade categories\n",
    "grades= [\"P\", \"K\"] + [str(g) for g in range(1, 13)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "e57bfe8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "def checkNulls(df):\n",
    "    schools = set()\n",
    "    for col in ['Grade', 'Year', 'Total', \n",
    "                'School Name',\n",
    "                'Region', \n",
    "#                 'Street Address'\n",
    "               ]:\n",
    "        mask = (df[col].isna() | df[col].isnull() )\n",
    "        if mask.sum():\n",
    "            print(f\"{col} has {len(mask)} nulls\")\n",
    "            schools.intersection(df.loc[mask, \"School Name\"].unique())\n",
    "    for sname in schools:\n",
    "        display(df[df[\"School Name\"]==sname])\n",
    "#                 display(df.loc[mask])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "01aaaa79-189c-411c-8ded-535be4f74134",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Normalize 2018 --- Not used. Format is quite a lot difference. Suspicious of the changes\n",
    "# d2018_ = d2018.rename(columns={\"District Name\": \"Region\"}) # \"City\" is missing in this dataset. We only have \"District Name\"\n",
    "# d2018_['Grade'] = d2018['Grade'].replace(gradeMap)\n",
    "# d2018_['Year'] = 2018\n",
    "# d2018_ = d2018_[d2018_['School Name'].notnull()]\n",
    "# print(d2018_.shape, list(d2018_[allFinalCols].columns))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "de0d42ee-938e-419f-a5a4-342ea24e0e29",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Normalize 2019\n",
    "df = d2019.rename(columns=gradeMap)\n",
    "df = df.rename(columns={\"Total\":\"_Total\", \"Name of School\": \"School Name\", \"City\": \"Region\"})\n",
    "# print(sorted(df.columns))\n",
    "df = df.melt(id_vars=set(df.columns)-set(grades), value_vars=grades, var_name=\"Grade\", value_name=\"Total\")\n",
    "df['Year'] = 2019\n",
    "df = df[df['School Name'].notnull()]\n",
    "print(df.shape, list(df[allFinalCols].columns))\n",
    "checkNulls(df)\n",
    "d2019_ = df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65ef9a7d-1287-487d-be0a-e99266fa9a3d",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Normalize 2020\n",
    "df = d2020.rename(columns=gradeMap).rename(columns={\"City\": \"Region\"})\n",
    "print(df.shape, list(df.columns))\n",
    "df[grades]  # The grade columns should all exist\n",
    "df = df.melt(id_vars=set(df.columns)-set(grades), value_vars=grades, var_name=\"Grade\", value_name=\"Total\")\n",
    "df['Grade'] = df['Grade'].replace(gradeMap)\n",
    "df['Year'] = 2020\n",
    "df = df[df['School Name'].notnull()]\n",
    "checkNulls(df)\n",
    "# display(df[df[\"Region\"].isnull()])\n",
    "# display(d2020[\"City\"].isnull().sum())\n",
    "print(f'null cities: {d2020[\"City\"].isnull().sum()}')\n",
    "# print(f'Schools with null cities:')\n",
    "# for school in d2020.loc[d2020[\"City\"].isnull(),\"School Name\"].unique():\n",
    "#     display(d2020[d2020[\"School Name\"]==school])\n",
    "# print(f'Schools with null totals:')\n",
    "# for school in df.loc[df[\"Total\"].isnull(),\"School Name\"].unique():\n",
    "#     display(d2020[d2020[\"School Name\"]==school])\n",
    "\n",
    "# display(df[df[\"Total\"].isnull()])\n",
    "# display(df[df[\"Region\"].isnull() & (df[\"School Name\"]==\"Carden Country School\")])\n",
    "# display(df[df[\"School Name\"]==\"Carden Country School\"])\n",
    "d2020_ = df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "47c1e53c-944c-4184-9425-000e2b4e755f",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Normalize 2021\n",
    "def _normalize(df):\n",
    "    df = df.rename(columns=gradeMap).rename(columns={\"Name of School\": \"School Name\", 'ZIP': \"Zipcode\", \"City\": \"Region\"})\n",
    "    df[grades]  # The grade columns should all exist\n",
    "    df = df.melt(id_vars=set(df.columns)-set(grades), value_vars=grades, var_name=\"Grade\", value_name=\"Total\")\n",
    "    df['Grade'] = df['Grade'].replace(gradeMap)\n",
    "    df['Year'] = 2021\n",
    "    df.loc[df[\"Total\"].isna(), \"Total\"] = 0\n",
    "    df[\"Total\"] = df[\"Total\"].astype(int)\n",
    "\n",
    "    df = df[df['School Name'].notnull()]\n",
    "    checkNulls(df)\n",
    "    print(df.shape, list(df[allFinalCols].columns))\n",
    "    return df\n",
    "print(d2021.columns)\n",
    "d2021_ = _normalize(d2021)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16c88034-d960-4f90-85fe-688a0df8b3d7",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Normalize 2022\n",
    "def _normalize(df):\n",
    "    df = df.rename(columns=gradeMap).rename(columns={'ZIP': \"Zipcode\", \"City\": \"Region\", \"Address\": \"Street Address\"})\n",
    "    print(df.columns)\n",
    "    df[grades]  # The grade columns should all exist\n",
    "    df = df.melt(id_vars=set(df.columns)-set(grades), value_vars=grades, var_name=\"Grade\", value_name=\"Total\")\n",
    "    df['Grade'] = df['Grade'].replace(gradeMap)\n",
    "    df['Year'] = 2022\n",
    "    df.loc[df[\"Total\"].isna(), \"Total\"] = 0\n",
    "    df[\"Total\"] = df[\"Total\"].astype(int)\n",
    "    df = df[df['School Name'].notnull()]\n",
    "\n",
    "    checkNulls(df)\n",
    "#     print(df.shape, list(df[allFinalCols].columns))\n",
    "    print(df.shape, list(df[allFinalCols].columns))\n",
    "    return df\n",
    "\n",
    "# Index(['School Name', 'Street Address', 'Region', 'State', 'Zipcode', 'P',\n",
    "#        'PK Total.1', 'K', '1', '2', '3', '4', '5', '6', '7', '8', '9', '10',\n",
    "#        '11', '12', 'K12 Total Reported'],\n",
    "#       dtype='object')\n",
    "d2022_ = _normalize(d2022)\n",
    "\n",
    "# Index(['School Name', 'Street Address', 'Region', 'State', 'Zipcode', 'County',\n",
    "#        'District', 'PreK TOTAL', 'KG TOTAL', 'G1 TOTAL', 'G2 TOTAL',\n",
    "#        'G3 TOTAL', 'G4 TOTAL', 'G5 TOTAL', 'G6 TOTAL', 'G7 TOTAL', 'G8 TOTAL',\n",
    "#        'G9 TOTAL', 'GI0 TOTAL', 'G11 TOTAL', 'G12 TOTAL'],\n",
    "#       dtype='object')\n",
    "d2023_ = _normalize(d2023)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "feb47512",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = d2023_\n",
    "for c in df.columns:\n",
    "    df = df[df[c].isna()]\n",
    "    if len(df)==0: continue\n",
    "    display(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d3138b9-7aaf-4d34-b649-89290269be82",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "def normalizeAddresses(df):\n",
    "    df = df.copy()\n",
    "    def simplifyAddr(a):\n",
    "        try:\n",
    "            a = a.upper().replace(\".\", \"\").replace(\"AVENUE\", \"AVE\")\n",
    "            a = \" \".join(a.split()[:3])\n",
    "            return a\n",
    "        except:\n",
    "            print(\"Faied address\", a)\n",
    "            raise\n",
    "    df[\"Street Address\"] = df[\"Street Address\"].apply(simplifyAddr)\n",
    "    return df\n",
    "\n",
    "def fixDuplicateAddress(df):\n",
    "    df_ = [[\"Region\", \"School Name\", \"Street Address\"]].sort_values([\"Region\", \"School Name\", \"Street Address\"])\\\n",
    "        .drop_duplicates() \\\n",
    "        .reset_index()\n",
    "    for idx, region, school, addr in df_[df_[[\"Region\", \"School Name\"]].duplicated(keep=False)].itertuples():\n",
    "        mask = (df[\"Region\"]==region) & (df[\"School Name\"]==school)\n",
    "        newschool = f\"{school} {addr}\"\n",
    "        print(f\"Renaming {repr(school)}, {repr(addr)} -> {repr(newschool)}\")\n",
    "        df.loc[mask, \"School Name\"] = newschool\n",
    "\n",
    "# 2018 seems to have some very different reporting. Not sure it is consistent. Dropped here\n",
    "dAll = pd.concat([d2019_, d2020_, d2021_, d2022_]).reindex()\n",
    "# dAll = pd.concat([d2022_, ]).reindex()\n",
    "# Place the important data in the first columns. \n",
    "dAll = dAll[allFinalCols + list(set(dAll.columns)-set(allFinalCols))].sort_values([\"Year\", \"Grade\", \"School Name\"])\n",
    "dAll = dAll[~(dAll[\"School Name\"].isnull())] # Zap rows without school names\n",
    "dAll = dAll[~(dAll[\"School Name\"].astype(str)=='NaN')] # Zap rows without school names\n",
    "dAll = dAll[~(dAll[\"School Name\"].isna())] # Zap rows without school names\n",
    "dAll = dAll[dAll[\"School Name\"]!=\"State Total\"] # Zap total rows\n",
    "dAll.loc[dAll[\"Total\"].isna(), \"Total\"] = 0\n",
    "dAll = dAll.astype({\"Total\": int})\n",
    "dAll = dAll.reset_index()\n",
    "\n",
    "# display(dAll[dAll[\"Street Address\"].isna()])\n",
    "# dAll = normalizeAddresses(dAll)\n",
    "# dAll = fixDuplicateAddress(dAll)\n",
    "\n",
    "# There are a number of school in the data without a district or city, or a district of 0 for some reason.\n",
    "# We'll just ignore them from bellevue processing since none are in Bellevie\n",
    "# dAll = dAll[dAll[\"Region\"].astype(str)!='0'] # 0 is not a region\n",
    "print(f\"Years reporting {dAll['Year'].unique()}\")\n",
    "\n",
    "print(\"Schools reporting their city/district as 0\")\n",
    "display(dAll[dAll[\"Region\"].isnull()][\"School Name\"].unique())\n",
    "\n",
    "print(\"\\nTotal WA private enrollment by year\")\n",
    "display(dAll.groupby(\"Year\")[\"Total\"].sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "43031e0f-9793-4fe8-ae67-b609142c5bcf",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "print(f'Regions considered:')\n",
    "\n",
    "dAllRegionOfInterest = dAll[[\"Grade\", \"School Name\", \"Region\", \"Total\", \"Year\"]]\n",
    "dAllRegionOfInterest = dAllRegionOfInterest[~(dAllRegionOfInterest[\"Region\"].isnull())]\n",
    "dAllRegionOfInterest = dAllRegionOfInterest[dAllRegionOfInterest[\"Region\"].astype(str).str.contains(regionSubstring)]\n",
    "dAllRegionOfInterest[\"School Name\"] = dAllRegionOfInterest[\"School Name\"].str.strip().replace(\n",
    "     {\"SARODGINI CHILDREN'S ACADEMY\": \"Sarodgini Children's Academy\",\n",
    "     \"Dartmoor School - Bellevue\": \"Dartmoor School\",\n",
    "     \"Cedar Park Christian - Bellevue\": \"Cedar Park Christian School\",\n",
    "      \"Living Montessori\": \"Living Montessori Education Community\"\n",
    "     })\n",
    "\n",
    "zeroReportedAllYear = dAllRegionOfInterest.groupby(\"School Name\")[\"Total\"].sum()\n",
    "zeroReportedAllYear = zeroReportedAllYear[zeroReportedAllYear==0]\n",
    "print(f'\\nSchool reporting 0 enrollment (all year): {len(zeroReportedAllYear)}')\n",
    "display(zeroReportedAllYear.to_frame())\n",
    "#display(dAllRegionOfInterest[dAllRegionOfInterest[\"School Name\"].isin(zeroReportedAnyYear.index)])\n",
    "\n",
    "# Removes schools not reporting any data at all\n",
    "# print(len(dAllRegionOfInterest[\"School Name\"].unique()), len(dAllRegionOfInterest), dAllRegionOfInterest[\"Total\"].sum())\n",
    "#dAllRegionOfInterest = dAllRegionOfInterest[~(dAllRegionOfInterest[\"School Name\"].isin(zeroReportedAnyYear.index))]\n",
    "# print(len(dAllRegionOfInterest[\"School Name\"].unique()), len(dAllRegionOfInterest), dAllRegionOfInterest[\"Total\"].sum())\n",
    "\n",
    "for school in sorted(dAllRegionOfInterest[\"School Name\"].unique()):\n",
    "    print(school, dAllRegionOfInterest.loc[dAllRegionOfInterest[\"School Name\"]==school, \"Total\"].sum())\n",
    "print(\"====\")\n",
    "    \n",
    "zeroReportedAllYear = dAllRegionOfInterest.groupby(\"School Name\")[\"Total\"].sum()\n",
    "zeroReportedAllYear = zeroReportedAllYear[zeroReportedAllYear==0]\n",
    "print(f'\\nSchool reporting 0 enrollment (all year): {zeroReportedAllYear.size}')\n",
    "\n",
    "\n",
    "regions = dAllRegionOfInterest[\"Region\"].unique()\n",
    "display(regions)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "66bd008e",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "print(f\"\\nTotal {', '.join(regions)} private enrollment by year\")\n",
    "display(dAllRegionOfInterest.groupby(\"Year\")[\"Total\"].sum().to_frame())\n",
    "\n",
    "print(f'\\nSchools reporting: {dAllRegionOfInterest[\"School Name\"].unique().size}.')\n",
    "schools = sorted(dAllRegionOfInterest[\"School Name\"].unique())\n",
    "for school in schools:\n",
    "    print(school)\n",
    "    \n",
    "# display(schools)\n",
    "\n",
    "\n",
    "# print(df[\"Year\"].dtype)\n",
    "for year in range(2019, 2022+1):\n",
    "    df = dAllRegionOfInterest[dAllRegionOfInterest[\"Year\"]==year]\n",
    "\n",
    "# display(dAllRegionOfInterest.groupby(\"Year\")[\"Total\"].sum())\n",
    "# for year, df in dAllRegionOfInterest.groupby(\"Year\"):\n",
    "#     print(f\"{year} ------------\")\n",
    "#     display(df.groupby(\"School Name\")[\"Total\"].sum())\n",
    "\n",
    "# for school, df in dAllRegionOfInterest.groupby(\"School Name\"):\n",
    "#     for year, df in df.groupby(\"Year\"):\n",
    "#         display(df[\"Total\"].sum().to_frame())\n",
    "# for (school,year), df in dAllRegionOfInterest.groupby([\"School Name\", \"Year\"]):\n",
    "#     #for year, df in df.groupby(\"Year\"):\n",
    "#         display(df[\"Total\"].sum().to_frame())\n",
    "print(\"-----------\")\n",
    "\n",
    "for year in range(2019, 2022+1):\n",
    "    reportingZero = []\n",
    "    df = dAllRegionOfInterest[dAllRegionOfInterest[\"Year\"]==year]\n",
    "    for school in schools:\n",
    "        total = df[df[\"School Name\"]==school][\"Total\"].sum()\n",
    "        print(f\"{year}: Total:{total} {school}\")\n",
    "        if total==0:\n",
    "            reportingZero.append(school)\n",
    "    print(f\"Total: {df['Total'].sum()}\")\n",
    "    print(f'\\nSchool reporting 0 enrollment ({year}): {len(reportingZero)}')\n",
    "    display(reportingZero)\n",
    "\n",
    "\n",
    "# The ArtSci Company  No data\n",
    "# Ventures Academy    No data\n",
    "\n",
    "# 'BASIS Independent Bellevue', opened 2022/23\n",
    "# \"Bellevue Children's Academy\", opened 1999. Reopened onsite 2021\n",
    "# 'Eastside Community School',  # Colocated with international friends school? Moved to Issaquah\n",
    "# International Montessori Academy - Eastgate   #Closed 2019,2020?\n",
    "# International Friends school  # Opened 2018. Open during covid, but not reporting\n",
    "# 'Seattle VocTech School',   ~10 students\n",
    "# 'Tarteel School'  Closed 2020/21?   Only data from 2022/23? (180 students)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "66b869fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Show enrollment changes from baseline year\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.colors as _colors  \n",
    "import itertools, random\n",
    "import matplotlib.ticker as mticker\n",
    "import math\n",
    "\n",
    "baselineYear = 2019\n",
    "allGrades = [\"P\",\"K\"]+[str(s) for s in range(1,13)]\n",
    "\n",
    "colors = list(_colors.TABLEAU_COLORS.values())\n",
    "random.Random(0).shuffle(colors)\n",
    "styles = itertools.cycle(itertools.product(['dashdot', 'solid', 'dashed', 'dashdot', 'dotted'], colors))\n",
    "\n",
    "_school2style = {}\n",
    "def school2style(school):\n",
    "    s = _school2style.get(school)\n",
    "    if s is None:\n",
    "        s = next(styles)\n",
    "#         print(s)\n",
    "        _school2style[school] = s\n",
    "    return s\n",
    "[school2style(s) for s in sorted(dAllRegionOfInterest[\"School Name\"].unique())]\n",
    "\n",
    "#def plot(genFunc, baselineYear, selectGrades, title, xlabel, ylabel):\n",
    "# 1:\n",
    "xlabel = \"Year\"\n",
    "ylabel = \"Enrollment\"\n",
    "df = dAllRegionOfInterest.copy()\n",
    "for school, sgrp in df.groupby(\"School Name\"):\n",
    "    f = plt.figure()\n",
    "    f.set_figwidth(10)\n",
    "    f.set_figheight(10)\n",
    "\n",
    "    print(school)\n",
    "    \n",
    "#     df = dAllRegionOfInterest #[\"Grade\"]\n",
    "#     df = df[df[\"School Name\"]==\"America's Child Montessori\"]\n",
    "    # display(df)   \n",
    "    # df = df.sort_values(by=\"Grade\", key=lambda col:col.map(grade2Order)).set_index(\"Year\")\n",
    "    # for grade, grp in df.groupby(\"Grade\"):\n",
    "    display(sgrp.groupby(\"Year\")['Total'].sum().to_frame())\n",
    "    sgrp = sgrp.set_index(\"Year\")\n",
    "    sgrp = sgrp[sgrp[\"Total\"]!=0]\n",
    "    print(f\"Total: {sgrp['Total'].sum()}\")\n",
    "    display(sgrp)\n",
    "    for grade in allGrades:\n",
    "        df_ = sgrp[sgrp[\"Grade\"]==grade][\"Total\"]\n",
    "        if len(df_)==0: continue\n",
    "        if df_.sum()==0: continue\n",
    "        y = df_.values\n",
    "        x = df_.index\n",
    "#         print(grade)\n",
    "#         display(df_)\n",
    "        linestyle, color = school2style(school)\n",
    "        plt.plot(x, y, linestyle=linestyle, \n",
    "                 #color=color,\n",
    "                 marker='o',\n",
    "                label=grade)\n",
    "\n",
    "    plt.xlabel(xlabel)\n",
    "    plt.ylabel(ylabel)\n",
    "    plt.title(school)\n",
    "    plt.gca().xaxis.set_major_locator(mticker.MultipleLocator(1))\n",
    "    plt.legend(loc='center left', bbox_to_anchor=(1, 0.5))\n",
    "    plt.show()\n",
    "#     break\n",
    "        \n",
    "    \n",
    "    \n",
    "# for name, select in [\n",
    "#     (\"P-3\", [\"P\", \"K\", \"1\", \"2\", \"3\"]),\n",
    "#     (\"K-5\", [\"K\", \"1\", \"2\", \"3\",\" 4\", \"5\"]),\n",
    "#     (\"P-5\", [\"P\", \"K\", \"1\", \"2\", \"3\", \"4\", \"5\"]),\n",
    "#     (\"K-3\", [\"K\", \"1\", \"2\", \"3\"]),\n",
    "#     (\"K-12\", [\"K\"]+[str(s) for s in range(1,13)]),\n",
    "#     (\"P-12\", allGrades),\n",
    "#     (\"All\", None),  # Sanity check, should match P-12\n",
    "#   ] + [(g, [g]) for g in grades]:\n",
    "    \n",
    "#   if select:\n",
    "#     bvDiff = dAllRegionOfInterest[dAllRegionOfInterest[\"Grade\"].isin(select)].copy()\n",
    "#   else:\n",
    "#     bvDiff = bvDiff.copy()\n",
    "#   bvDiff = bvDiff[bvDiff['School Name']==\"America's Child Montessori\"]\n",
    "#   display(bvDiff)\n",
    "\n",
    "#   bvDiffTotals = bvDiff.groupby(\"Year\")[\"Total\"].sum().to_frame()\n",
    "\n",
    "#   display(bvDiffTotals)\n",
    "#   totalName = f\"'Grade {name}' private enrollment, Δ since {baselineYear}\"\n",
    "#   bvDiffTotals[totalName] = bvDiffTotals[\"Total\"] - bvDiffTotals[\"Total\"][baselineYear]\n",
    "#   bvDiffTotals[f\"%change\"] = 100*(bvDiffTotals[totalName] / bvDiffTotals[\"Total\"][baselineYear])\n",
    "  \n",
    "#   g = bvDiff.groupby([\"School Name\", \"Year\"])[\"Total\"].sum().to_frame().reset_index()\n",
    "      \n",
    "#   bvDiffTotals[f\"# schools reporting\"] = g.groupby([\"Year\"]).count()[\"Total\"]\n",
    "#   bvDiffTotals[f\"# reporting 0\"] = g[g[\"Total\"]==0].groupby([\"Year\"]).count()[\"Total\"]\n",
    "#   bvDiffTotals[f\"# reporting not 0\"] = g[g[\"Total\"]!=0].groupby([\"Year\"]).count()[\"Total\"]\n",
    "\n",
    "#   display(bvDiffTotals)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "695fc8be",
   "metadata": {},
   "outputs": [],
   "source": [
    "df = sgrp.set_index(\"Year\")\n",
    "for grade in allGrades:\n",
    "    df_ = sgrp[sgrp[\"Grade\"]==grade][\"Total\"]\n",
    "    if len(df_)==0: continue\n",
    "    if df_.sum()==0: continue"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "db103f95",
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "grade2Order = {g:o for o,g in enumerate(allGrades)}.get\n",
    "\n",
    "allGrades = [\"P\", \"K\"]+[str(s) for s in range(12)]\n",
    "df = dAllRegionOfInterest #[\"Grade\"]\n",
    "df = df[df[\"School Name\"]==\"America's Child Montessori\"]\n",
    "# display(df)   \n",
    "# df = df.sort_values(by=\"Grade\", key=lambda col:col.map(grade2Order)).set_index(\"Year\")\n",
    "# for grade, grp in df.groupby(\"Grade\"):\n",
    "df = df.set_index(\"Year\")\n",
    "for grade in allGrades:\n",
    "    df_ = df[df[\"Grade\"]==grade]\n",
    "    print(grade)\n",
    "    if len(df_)==0: continue\n",
    "    if df_[\"Total\"].sum()==0: continue\n",
    "    display(df_[\"Total\"])\n",
    "#     df = df.sort_values(by=\"Grade\", key=lambda col:col.map(grade2Order))\n",
    "df\n",
    "# dAllRegionOfInterest[\"Year\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "75e2489c",
   "metadata": {},
   "outputs": [],
   "source": [
    "grade2Order(\"K\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f35d6be3",
   "metadata": {},
   "outputs": [],
   "source": [
    "grp"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f398b2e3-5662-4063-8d35-c44e9cebd0f3",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# Show enrollment changes from baseline year\n",
    "baselineYear = 2019\n",
    "allGrades = [\"P\",\"K\"]+[str(s) for s in range(1,13)]\n",
    "for name, select in [\n",
    "    (\"P-3\", [\"P\", \"K\", \"1\", \"2\", \"3\"]),\n",
    "    (\"K-5\", [\"K\", \"1\", \"2\", \"3\",\" 4\", \"5\"]),\n",
    "    (\"P-5\", [\"P\", \"K\", \"1\", \"2\", \"3\", \"4\", \"5\"]),\n",
    "    (\"K-3\", [\"K\", \"1\", \"2\", \"3\"]),\n",
    "    (\"K-12\", [\"K\"]+[str(s) for s in range(1,13)]),\n",
    "    (\"P-12\", allGrades),\n",
    "    (\"All\", None),  # Sanity check, should match P-12\n",
    "  ] + [(g, [g]) for g in grades]:\n",
    "    \n",
    "  if select:\n",
    "    bvDiff = dAllRegionOfInterest[dAllRegionOfInterest[\"Grade\"].isin(select)].copy()\n",
    "  else:\n",
    "    bvDiff = bvDiff.copy()\n",
    "  bvDiff = bvDiff[bvDiff['School Name']==\"America's Child Montessori\"]\n",
    "  display(bvDiff)\n",
    "\n",
    "  bvDiffTotals = bvDiff.groupby(\"Year\")[\"Total\"].sum().to_frame()\n",
    "\n",
    "  display(bvDiffTotals)\n",
    "  totalName = f\"'Grade {name}' private enrollment, Δ since {baselineYear}\"\n",
    "  bvDiffTotals[totalName] = bvDiffTotals[\"Total\"] - bvDiffTotals[\"Total\"][baselineYear]\n",
    "  bvDiffTotals[f\"%change\"] = 100*(bvDiffTotals[totalName] / bvDiffTotals[\"Total\"][baselineYear])\n",
    "  \n",
    "  g = bvDiff.groupby([\"School Name\", \"Year\"])[\"Total\"].sum().to_frame().reset_index()\n",
    "      \n",
    "  bvDiffTotals[f\"# schools reporting\"] = g.groupby([\"Year\"]).count()[\"Total\"]\n",
    "  bvDiffTotals[f\"# reporting 0\"] = g[g[\"Total\"]==0].groupby([\"Year\"]).count()[\"Total\"]\n",
    "  bvDiffTotals[f\"# reporting not 0\"] = g[g[\"Total\"]!=0].groupby([\"Year\"]).count()[\"Total\"]\n",
    "\n",
    "  display(bvDiffTotals)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "402ddc6a-7744-4443-9ec2-2a44a4b9a2c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.colors as _colors  \n",
    "import itertools, random\n",
    "import matplotlib.ticker as mticker\n",
    "import math\n",
    "\n",
    "print(\"Full data\")\n",
    "def gen():\n",
    "    df = dAllRegionOfInterest[[\"Year\", \"School Name\", \"Grade\", \"Total\"]].sort_values([\"Year\", \"School Name\", \"Grade\"])\n",
    "    for (school, year), dfbyschool in df.groupby([\"School Name\", \"Year\"]):\n",
    "        if school in ['Bellevue Montessori School', 'International Montessori Academy', 'Sacred Heart School']: continue\n",
    "        dfbyschool[\"X\"] = f\"{year} {school}\"\n",
    "        display(dfbyschool)\n",
    "        d = dfbyschool.pivot(index=\"X\", columns=\"Grade\",values=\"Total\")\n",
    "        display(d)\n",
    "\n",
    "\n",
    "\n",
    "with pd.option_context('display.max_rows', None, 'display.max_columns', None): \n",
    "#     gen()\n",
    "    \n",
    "#     df = pd.concat(gen())\n",
    "#     display(df)\n",
    "\n",
    "    for (school, year), dfbyschool in df.groupby([\"School Name\", \"Year\"]):\n",
    "#         print(school)\n",
    "#         dfbyschool[\"X\"] = f\"{year} {school}\"\n",
    "#         display(dfbyschool.pivot(index=\"X\", columns=\"Grade\",values=\"Total\"))\n",
    "#         # display(df[[\"Grade\", \"Total\"]].set_index(\"Grade\").pivot(\n",
    "#         #          # columns=\"Grade\",\n",
    "#         #          values=\"Total\"))\n",
    "#         break\n",
    "\n",
    "        f = plt.figure()\n",
    "        f.set_figwidth(5)\n",
    "        f.set_figheight(5)\n",
    "        for grade, dfbygrade in dfbyschool.groupby([\"Grade\"]):\n",
    "           x, y = dfbygrade[\"Year\"].values, dfbygrade[\"Total\"].values\n",
    "           if y.sum()==0:\n",
    "              print(f\"No data for grade {grade}\")\n",
    "              continue\n",
    "           plt.plot(x, y, #linestyle=linestyle, color=color, \n",
    "                    label=grade)\n",
    "        print(x)\n",
    "        print(y)\n",
    "        plt.show()            \n",
    "        break\n",
    "        plt.xlabel('Year')\n",
    "        plt.ylabel('Enrollment')\n",
    "        plt.title(school)                \n",
    "        plt.gca().xaxis.set_major_locator(mticker.MultipleLocator(1))\n",
    "        plt.legend(loc='center left', bbox_to_anchor=(1, 0.5))\n",
    "        plt.show()            \n",
    "#         # print(dfbyyear)\n",
    "\n",
    "# .pivot(#index=, \n",
    "#                                      columns=\"Grade\",\n",
    "#                                      values=\"Total\"))\n",
    "\n",
    "  # display(dAllRegionOfInterest[[\"School Name\", \"Grade\", \"Year\", \"Total\"]]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c4251cc5-25d7-4ea6-8dd5-bf5e3e3e8aed",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "# print(df_)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9a9ddfcb-d516-4396-9f4a-60a530b8d3d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# df_.loc[mask.values, \"School Name\"]\n",
    "mask.shape, df_.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6c622778",
   "metadata": {},
   "outputs": [],
   "source": [
    "mask.sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "41745454",
   "metadata": {},
   "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.10.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}