notebooks/satisfaction-calculations.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "d1292b3f-e631-4b8d-b271-2dd99e042443",
   "metadata": {},
   "outputs": [],
   "source": [
    "import polars as pl\n",
    "from scipy.stats import binom\n",
    "import pathlib"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fee1fe29-9023-46f0-952b-77a5c7c19f1e",
   "metadata": {},
   "source": [
    "## Load the raw response data"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d4d977d1-0050-495b-a912-353a1ead6749",
   "metadata": {},
   "source": [
    "Each Excel file uses a slightly different layout and variable names, so the following functions standardize a few key columns:\n",
    "\n",
    "- Grant ID\n",
    "- Provider name\n",
    "- Response date\n",
    "- Q1 response (as a number)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "6a5c8d33-1746-41ce-a4f2-42f7a3ba7a5d",
   "metadata": {},
   "outputs": [],
   "source": [
    "def fix_cols_1(df):\n",
    "    return (\n",
    "        df\n",
    "        .with_columns(\n",
    "            pl.col(\"EndDate\").cast(int).alias(\"date_int\")\n",
    "        )\n",
    "        .rename({\n",
    "            \"Custom Variable 1\": \"provider_name\",\n",
    "            \"Custom Variable 2\": \"grant_id\",\n",
    "        })\n",
    "    )\n",
    "\n",
    "def fix_cols_2(df):\n",
    "    return (\n",
    "        df\n",
    "        .drop(\"Q1\")\n",
    "        .rename({\n",
    "            \"GRANTEEID\": \"grant_id\",\n",
    "            \"LtCallDt\": \"date_int\",\n",
    "            \"Q1 Num\": \"Q1\",\n",
    "        })\n",
    "        .with_columns(\n",
    "            pl.lit(None).cast(str).alias(\"provider_name\")\n",
    "        )\n",
    "    )\n",
    "\n",
    "def fix_cols_3(df):\n",
    "    return (\n",
    "        df\n",
    "        .rename({\n",
    "            \"LEGAL_NAME\": \"provider_name\",\n",
    "            \"GRANT_ID\": \"grant_id\",\n",
    "            \"Timestamp\": \"date_int\",\n",
    "        })\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "4aed2961-e4a8-4b78-89b2-1d6d71797ab2",
   "metadata": {},
   "outputs": [],
   "source": [
    "def load_responses(path):\n",
    "    df = pl.read_excel(\n",
    "        path,\n",
    "        read_csv_options=dict(infer_schema_length=0),\n",
    "        xlsx2csv_options=dict(dateformat=\"%Y%m%d\"),\n",
    "    )\n",
    "    if \"Q8.4-c_6_TEXT\" in df.columns:\n",
    "        fixer = fix_cols_1\n",
    "    elif \"GRANTEEID\" in df.columns:\n",
    "        fixer = fix_cols_2\n",
    "    else:\n",
    "        fixer = fix_cols_3\n",
    "        \n",
    "    return (\n",
    "        df\n",
    "        .pipe(fixer)\n",
    "        .with_columns(\n",
    "            pl.col(\"Q1\").cast(int),\n",
    "            pl.col(\"date_int\").cast(str).str.strptime(pl.Date, \"%Y%m%d\").alias(\"date\")\n",
    "        )\n",
    "        .with_columns(\n",
    "            (pl.col(\"date\").dt.year() + (pl.col(\"date\").dt.month() >= 10)).alias(\"fiscal_year\")\n",
    "        )\n",
    "        .select(\n",
    "            \"grant_id\",\n",
    "            \"provider_name\",\n",
    "            \"date\",\n",
    "            \"fiscal_year\",\n",
    "            \"Q1\"\n",
    "        )\n",
    "    )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "33225393-04f1-43fc-a13a-8cd7775bde02",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[PosixPath('../data/raw/FINAL Data - FY 2016-2017_Excluding PII.xlsx'),\n",
       " PosixPath('../data/raw/FINAL Data - FY 2018-2020_Excluding PII.xlsx'),\n",
       " PosixPath('../data/raw/SSVF Annual National Report Data Oct 21 - Sep 22_Excluding PII.xlsx')]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "paths = sorted(pathlib.Path(\"../data/raw/\").glob(\"*.xlsx\"))\n",
    "paths"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "afc89238-9b9b-4606-b4ae-553dfe348c9e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Rows: 39544\n",
      "Columns: 5\n",
      "$ grant_id       <str> 13-GA-102, 13-NV-118, 14-CA-169, 14-MA-209, 14-AR-156, 14-ZZ-153, 14-AL-155, 14-NC-235, C2015-DC-500B, 14-WV-303\n",
      "$ provider_name  <str> Decatur Cooperative Ministry  Inc., Salvation Army  a California Corporation, Families in Transition of Santa Cruz County  Inc., Veterans Northeast Outreach Center  Inc., Seven Hills Homeless Center, United Way of Central Alabama, Family Endeavors  Inc., Family Endeavors  Inc., Friendship Place, The Greater Wheeling Coalition for the Homeless  Inc.\n",
      "$ date          <date> 2016-05-01, 2016-05-01, 2016-05-01, 2016-05-01, 2016-05-01, 2016-05-01, 2016-05-01, 2016-05-01, 2016-05-01, 2016-05-01\n",
      "$ fiscal_year    <i32> 2016, 2016, 2016, 2016, 2016, 2016, 2016, 2016, 2016, 2016\n",
      "$ Q1             <i64> 1, 4, 4, 4, 2, 5, 3, 4, 4, 5\n",
      "\n"
     ]
    }
   ],
   "source": [
    "responses = pl.concat(map(load_responses, paths))\n",
    "responses.glimpse()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "2c71e4f0-fb56-490f-881a-035b88ab4aa0",
   "metadata": {},
   "outputs": [],
   "source": [
    "assert responses[\"Q1\"].is_null().mean() == 0"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "76cc110c-e6d2-47c3-adc4-a21baf8136b9",
   "metadata": {},
   "source": [
    "Total responses:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "4bd83e8c-9490-4fb3-88f1-6d8118b50b04",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "39544"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(responses)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed693771-c75b-4ebf-a5ee-519152b0cfcf",
   "metadata": {},
   "source": [
    "By fiscal year:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "de2b8a4c-8e06-4e52-ab7c-d1c982b2e6e1",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr > th,\n",
       ".dataframe > tbody > tr > td {\n",
       "  text-align: right;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (6, 2)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>fiscal_year</th><th>counts</th></tr><tr><td>i32</td><td>u32</td></tr></thead><tbody><tr><td>2016</td><td>2766</td></tr><tr><td>2017</td><td>5696</td></tr><tr><td>2018</td><td>10137</td></tr><tr><td>2019</td><td>8859</td></tr><tr><td>2020</td><td>7464</td></tr><tr><td>2022</td><td>4622</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (6, 2)\n",
       "┌─────────────┬────────┐\n",
       "│ fiscal_year ┆ counts │\n",
       "│ ---         ┆ ---    │\n",
       "│ i32         ┆ u32    │\n",
       "╞═════════════╪════════╡\n",
       "│ 2016        ┆ 2766   │\n",
       "│ 2017        ┆ 5696   │\n",
       "│ 2018        ┆ 10137  │\n",
       "│ 2019        ┆ 8859   │\n",
       "│ 2020        ┆ 7464   │\n",
       "│ 2022        ┆ 4622   │\n",
       "└─────────────┴────────┘"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    responses\n",
    "    [\"fiscal_year\"]\n",
    "    .value_counts()\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f69ca7d3-9c6a-44e0-8fec-ccd099357e65",
   "metadata": {},
   "source": [
    "By quearter:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "d25e9043-1f0d-4c31-a642-5533f28ec35e",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr > th,\n",
       ".dataframe > tbody > tr > td {\n",
       "  text-align: right;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (23, 2)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>quarter</th><th>count</th></tr><tr><td>date</td><td>u32</td></tr></thead><tbody><tr><td>2016-01-01</td><td>150</td></tr><tr><td>2016-04-01</td><td>1174</td></tr><tr><td>2016-07-01</td><td>1442</td></tr><tr><td>2016-10-01</td><td>1165</td></tr><tr><td>2017-01-01</td><td>1401</td></tr><tr><td>2017-04-01</td><td>1349</td></tr><tr><td>2017-07-01</td><td>1781</td></tr><tr><td>2017-10-01</td><td>1898</td></tr><tr><td>2018-01-01</td><td>2799</td></tr><tr><td>2018-04-01</td><td>2711</td></tr><tr><td>2018-07-01</td><td>2729</td></tr><tr><td>2018-10-01</td><td>2038</td></tr><tr><td>2019-01-01</td><td>2353</td></tr><tr><td>2019-04-01</td><td>2183</td></tr><tr><td>2019-07-01</td><td>2285</td></tr><tr><td>2019-10-01</td><td>2112</td></tr><tr><td>2020-01-01</td><td>2490</td></tr><tr><td>2020-04-01</td><td>1229</td></tr><tr><td>2020-07-01</td><td>1633</td></tr><tr><td>2021-10-01</td><td>734</td></tr><tr><td>2022-01-01</td><td>1282</td></tr><tr><td>2022-04-01</td><td>1364</td></tr><tr><td>2022-07-01</td><td>1242</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (23, 2)\n",
       "┌────────────┬───────┐\n",
       "│ quarter    ┆ count │\n",
       "│ ---        ┆ ---   │\n",
       "│ date       ┆ u32   │\n",
       "╞════════════╪═══════╡\n",
       "│ 2016-01-01 ┆ 150   │\n",
       "│ 2016-04-01 ┆ 1174  │\n",
       "│ 2016-07-01 ┆ 1442  │\n",
       "│ 2016-10-01 ┆ 1165  │\n",
       "│ …          ┆ …     │\n",
       "│ 2021-10-01 ┆ 734   │\n",
       "│ 2022-01-01 ┆ 1282  │\n",
       "│ 2022-04-01 ┆ 1364  │\n",
       "│ 2022-07-01 ┆ 1242  │\n",
       "└────────────┴───────┘"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    responses\n",
    "    .sort(\"date\")\n",
    "    .groupby_dynamic(\n",
    "        pl.col(\"date\").alias(\"quarter\"),\n",
    "        every=\"1q\"\n",
    "    )\n",
    "    .agg(pl.count())\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b5a49cc7-9b30-4054-aecc-95357ca115b3",
   "metadata": {},
   "source": [
    "## Check data records vs. aggregates in PDF for FY2020"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "f699aa19-4263-4db2-913c-aaff36c05c55",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "7464"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "responses_fy20 = (\n",
    "    responses\n",
    "    .filter(pl.col(\"fiscal_year\") == 2020)\n",
    ")\n",
    "\n",
    "len(responses_fy20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "534decce-fd37-44f8-8357-5d5882ff4ad0",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr > th,\n",
       ".dataframe > tbody > tr > td {\n",
       "  text-align: right;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 5)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>grant_id</th><th>provider_name</th><th>responses_total</th><th>responses_top_2</th><th>performance_rating</th></tr><tr><td>str</td><td>str</td><td>i64</td><td>i64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;18-ZZ-034&quot;</td><td>&quot;Volunteers of …</td><td>43</td><td>22</td><td>0.0</td></tr><tr><td>&quot;14-MD-214&quot;</td><td>&quot;Project PLASE …</td><td>35</td><td>18</td><td>0.1</td></tr><tr><td>&quot;14-NY-259&quot;</td><td>&quot;Volunteers of …</td><td>13</td><td>4</td><td>0.1</td></tr><tr><td>&quot;14-ZZ-153&quot;</td><td>&quot;United Way of …</td><td>23</td><td>10</td><td>0.1</td></tr><tr><td>&quot;20-CA-017&quot;</td><td>&quot;The Salvation …</td><td>27</td><td>13</td><td>0.1</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 5)\n",
       "┌───────────┬──────────────────────────┬─────────────────┬─────────────────┬────────────────────┐\n",
       "│ grant_id  ┆ provider_name            ┆ responses_total ┆ responses_top_2 ┆ performance_rating │\n",
       "│ ---       ┆ ---                      ┆ ---             ┆ ---             ┆ ---                │\n",
       "│ str       ┆ str                      ┆ i64             ┆ i64             ┆ f64                │\n",
       "╞═══════════╪══════════════════════════╪═════════════════╪═════════════════╪════════════════════╡\n",
       "│ 18-ZZ-034 ┆ Volunteers of America of ┆ 43              ┆ 22              ┆ 0.0                │\n",
       "│           ┆ Illinoi…                 ┆                 ┆                 ┆                    │\n",
       "│ 14-MD-214 ┆ Project PLASE Inc.       ┆ 35              ┆ 18              ┆ 0.1                │\n",
       "│ 14-NY-259 ┆ Volunteers of            ┆ 13              ┆ 4               ┆ 0.1                │\n",
       "│           ┆ America-Greater Ne…      ┆                 ┆                 ┆                    │\n",
       "│ 14-ZZ-153 ┆ United Way of Central    ┆ 23              ┆ 10              ┆ 0.1                │\n",
       "│           ┆ Alabama                  ┆                 ┆                 ┆                    │\n",
       "│ 20-CA-017 ┆ The Salvation Army a     ┆ 27              ┆ 13              ┆ 0.1                │\n",
       "│           ┆ California …             ┆                 ┆                 ┆                    │\n",
       "└───────────┴──────────────────────────┴─────────────────┴─────────────────┴────────────────────┘"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pdf_counts_fy20 = pl.read_csv(\"../data/from-pdfs/provider-satisfaction-fy20.csv\")\n",
    "pdf_counts_fy20.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a7c3cf62-3533-48da-acf4-0a90bc214494",
   "metadata": {},
   "source": [
    "Note: \"performance rating\" is a calculation described on p. 15 the FY20 PDF report; it represents the likelihood that an \"average\" provider would receive fewer top-2 (i.e., \"Excellent\" or \"Above Average\") responses to Q1 than this particular provider, given the number of respondents for the provider. In the cell below, we confirm the calculation, which we implement via `scipy`'s `binom.cdf(numerator, denominator, propensity)` method."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "f61b8afa-2459-4ecc-97d7-ebd717c186ba",
   "metadata": {},
   "outputs": [],
   "source": [
    "# From examples in FY20 report, p. 15\n",
    "assert round(binom.cdf(8, 10, 0.7), 3) == 0.851\n",
    "assert round(binom.cdf(150, 200, 0.7), 3) == 0.949"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9be890fd-df68-4ea6-9e0d-b01a9ecb8343",
   "metadata": {},
   "source": [
    "By the Data Liberation Project's reading of the methodology, the \"average provider\"'s propensity for top-2 responses should be equal to the overall proportion of top-2 responses, calculated in the following cell. (This number matches up with the PDF, which notes, \"The systemwide percentage of responses to Q1 that were in the Top 2 [...] was 74.4%.\" But as demonstrated further below, it seems a different average was used for the performance ratings in the report's Appendix A."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "61fd9fb4-8de5-4b9c-96fa-3fbeaaa4012a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.744"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "POPULATION_MEAN_FY20 = responses_fy20[\"Q1\"].is_in([ 4, 5 ]).mean()\n",
    "round(POPULATION_MEAN_FY20, 3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "65878f78-9753-4135-abc9-e3287973ef84",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr > th,\n",
       ".dataframe > tbody > tr > td {\n",
       "  text-align: right;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 4)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>grant_id</th><th>responses_total</th><th>responses_top_2</th><th>performance_rating</th></tr><tr><td>str</td><td>u32</td><td>u32</td><td>f64</td></tr></thead><tbody><tr><td>&quot;20-ZZ-026&quot;</td><td>249</td><td>225</td><td>100.0</td></tr><tr><td>&quot;20-ZZ-113&quot;</td><td>175</td><td>147</td><td>99.6</td></tr><tr><td>&quot;19-FL-025&quot;</td><td>169</td><td>121</td><td>9.7</td></tr><tr><td>&quot;19-CA-009&quot;</td><td>153</td><td>101</td><td>0.3</td></tr><tr><td>&quot;18-TX-074&quot;</td><td>145</td><td>110</td><td>49.5</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 4)\n",
       "┌───────────┬─────────────────┬─────────────────┬────────────────────┐\n",
       "│ grant_id  ┆ responses_total ┆ responses_top_2 ┆ performance_rating │\n",
       "│ ---       ┆ ---             ┆ ---             ┆ ---                │\n",
       "│ str       ┆ u32             ┆ u32             ┆ f64                │\n",
       "╞═══════════╪═════════════════╪═════════════════╪════════════════════╡\n",
       "│ 20-ZZ-026 ┆ 249             ┆ 225             ┆ 100.0              │\n",
       "│ 20-ZZ-113 ┆ 175             ┆ 147             ┆ 99.6               │\n",
       "│ 19-FL-025 ┆ 169             ┆ 121             ┆ 9.7                │\n",
       "│ 19-CA-009 ┆ 153             ┆ 101             ┆ 0.3                │\n",
       "│ 18-TX-074 ┆ 145             ┆ 110             ┆ 49.5               │\n",
       "└───────────┴─────────────────┴─────────────────┴────────────────────┘"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "MEAN_USED = 0.761905\n",
    "metrics_fy20 = (\n",
    "    responses_fy20\n",
    "    .groupby(\"grant_id\")\n",
    "    .agg(\n",
    "        pl.count().alias(\"responses_total\"),\n",
    "        pl.col(\"Q1\").is_in([ 4, 5 ]).sum().alias(\"responses_top_2\"),\n",
    "    )\n",
    "    .with_columns(\n",
    "        (\n",
    "            pl.struct([\"responses_top_2\", \"responses_total\"])\n",
    "            .apply(lambda row: round(100 * binom.cdf(row[\"responses_top_2\"], row[\"responses_total\"], MEAN_USED), 1))\n",
    "            .alias(\"performance_rating\")\n",
    "        )\n",
    "    )\n",
    "    .sort(\"responses_total\", descending=True)\n",
    ")\n",
    "\n",
    "metrics_fy20.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "f24ce0f3-5e0d-46e9-b15c-6387f91f4933",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr > th,\n",
       ".dataframe > tbody > tr > td {\n",
       "  text-align: right;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 7)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>grant_id</th><th>responses_total</th><th>responses_top_2</th><th>performance_rating</th><th>responses_total_pdf</th><th>responses_top_2_pdf</th><th>performance_rating_pdf</th></tr><tr><td>str</td><td>u32</td><td>u32</td><td>f64</td><td>i64</td><td>i64</td><td>f64</td></tr></thead><tbody><tr><td>&quot;18-ZZ-034&quot;</td><td>43</td><td>22</td><td>0.0</td><td>43</td><td>22</td><td>0.0</td></tr><tr><td>&quot;14-MD-214&quot;</td><td>35</td><td>18</td><td>0.1</td><td>35</td><td>18</td><td>0.1</td></tr><tr><td>&quot;14-NY-259&quot;</td><td>13</td><td>4</td><td>0.1</td><td>13</td><td>4</td><td>0.1</td></tr><tr><td>&quot;14-ZZ-153&quot;</td><td>23</td><td>10</td><td>0.1</td><td>23</td><td>10</td><td>0.1</td></tr><tr><td>&quot;20-CA-017&quot;</td><td>27</td><td>13</td><td>0.1</td><td>27</td><td>13</td><td>0.1</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 7)\n",
       "┌───────────┬──────────────┬──────────────┬──────────────┬─────────────┬─────────────┬─────────────┐\n",
       "│ grant_id  ┆ responses_to ┆ responses_to ┆ performance_ ┆ responses_t ┆ responses_t ┆ performance │\n",
       "│ ---       ┆ tal          ┆ p_2          ┆ rating       ┆ otal_pdf    ┆ op_2_pdf    ┆ _rating_pdf │\n",
       "│ str       ┆ ---          ┆ ---          ┆ ---          ┆ ---         ┆ ---         ┆ ---         │\n",
       "│           ┆ u32          ┆ u32          ┆ f64          ┆ i64         ┆ i64         ┆ f64         │\n",
       "╞═══════════╪══════════════╪══════════════╪══════════════╪═════════════╪═════════════╪═════════════╡\n",
       "│ 18-ZZ-034 ┆ 43           ┆ 22           ┆ 0.0          ┆ 43          ┆ 22          ┆ 0.0         │\n",
       "│ 14-MD-214 ┆ 35           ┆ 18           ┆ 0.1          ┆ 35          ┆ 18          ┆ 0.1         │\n",
       "│ 14-NY-259 ┆ 13           ┆ 4            ┆ 0.1          ┆ 13          ┆ 4           ┆ 0.1         │\n",
       "│ 14-ZZ-153 ┆ 23           ┆ 10           ┆ 0.1          ┆ 23          ┆ 10          ┆ 0.1         │\n",
       "│ 20-CA-017 ┆ 27           ┆ 13           ┆ 0.1          ┆ 27          ┆ 13          ┆ 0.1         │\n",
       "└───────────┴──────────────┴──────────────┴──────────────┴─────────────┴─────────────┴─────────────┘"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "metrics_fy20_comparison = (\n",
    "    metrics_fy20\n",
    "    .join(\n",
    "        pdf_counts_fy20.select(pl.exclude(\"provider_name\")),\n",
    "        how = \"outer\",\n",
    "        on = \"grant_id\",\n",
    "        suffix = \"_pdf\",\n",
    "    )\n",
    ")\n",
    "\n",
    "metrics_fy20_comparison.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "8fe8abaa-97b8-409b-b0c4-fc454c34c7d1",
   "metadata": {},
   "outputs": [],
   "source": [
    "mismatched = (\n",
    "    metrics_fy20_comparison\n",
    "    .filter(\n",
    "        (pl.col(\"performance_rating\") != pl.col(\"performance_rating_pdf\")) |\n",
    "        (pl.col(\"responses_total\") != pl.col(\"responses_total_pdf\")) |\n",
    "        (pl.col(\"responses_top_2\") != pl.col(\"responses_top_2_pdf\"))\n",
    "    )\n",
    ")\n",
    "\n",
    "assert len(mismatched) == 0"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c88f544c-832a-4816-ad09-5bd08576de67",
   "metadata": {},
   "source": [
    "## Generalizing to produce provider aggregates for other years"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "eafe0314-6004-47f0-93f1-4bac6c58cfd4",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr > th,\n",
       ".dataframe > tbody > tr > td {\n",
       "  text-align: right;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 2)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>grant_id</th><th>provider_name_latest</th></tr><tr><td>str</td><td>str</td></tr></thead><tbody><tr><td>&quot;C15-NY-600B&quot;</td><td>&quot;HELP Social Se…</td></tr><tr><td>&quot;14-MD-214&quot;</td><td>&quot;Project PLASE …</td></tr><tr><td>&quot;16-NY-057&quot;</td><td>&quot;Veterans Outre…</td></tr><tr><td>&quot;18-OH-263&quot;</td><td>&quot;Talbert House …</td></tr><tr><td>&quot;14-CA-163&quot;</td><td>&quot;Carrillo Couns…</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 2)\n",
       "┌─────────────┬───────────────────────────────────┐\n",
       "│ grant_id    ┆ provider_name_latest              │\n",
       "│ ---         ┆ ---                               │\n",
       "│ str         ┆ str                               │\n",
       "╞═════════════╪═══════════════════════════════════╡\n",
       "│ C15-NY-600B ┆ HELP Social Service Corporation   │\n",
       "│ 14-MD-214   ┆ Project PLASE Inc                 │\n",
       "│ 16-NY-057   ┆ Veterans Outreach Center, Inc.    │\n",
       "│ 18-OH-263   ┆ Talbert House Inc.                │\n",
       "│ 14-CA-163   ┆ Carrillo Counseling Services Inc… │\n",
       "└─────────────┴───────────────────────────────────┘"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# See addendum below\n",
    "\n",
    "latest_names = (\n",
    "    responses\n",
    "    .filter(~pl.col(\"provider_name\").is_null())    \n",
    "    .sort(\"date\")\n",
    "    .groupby(\"grant_id\")\n",
    "    .agg(pl.col(\"provider_name\").last().alias(\"provider_name_latest\"))\n",
    ")\n",
    "\n",
    "latest_names.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "9dae36a2-e693-4798-90ec-ed22860de182",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr > th,\n",
       ".dataframe > tbody > tr > td {\n",
       "  text-align: right;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (5, 12)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>fiscal_year</th><th>grant_id</th><th>provider_name</th><th>provider_name_latest</th><th>responses_total</th><th>q1_1</th><th>q1_2</th><th>q1_3</th><th>q1_4</th><th>q1_5</th><th>top_2_count</th><th>top_2_pct</th></tr><tr><td>i32</td><td>str</td><td>str</td><td>str</td><td>u32</td><td>u32</td><td>u32</td><td>u32</td><td>u32</td><td>u32</td><td>u32</td><td>f64</td></tr></thead><tbody><tr><td>2022</td><td>&quot;12-AK-001&quot;</td><td>null</td><td>&quot;Catholic Socia…</td><td>2</td><td>0</td><td>0</td><td>0</td><td>0</td><td>2</td><td>2</td><td>100.0</td></tr><tr><td>2022</td><td>&quot;12-AL-002&quot;</td><td>null</td><td>&quot;Housing First …</td><td>2</td><td>0</td><td>0</td><td>0</td><td>1</td><td>1</td><td>2</td><td>100.0</td></tr><tr><td>2022</td><td>&quot;12-CA-013&quot;</td><td>null</td><td>&quot;Goodwill of Si…</td><td>6</td><td>1</td><td>0</td><td>0</td><td>0</td><td>5</td><td>5</td><td>83.3</td></tr><tr><td>2022</td><td>&quot;12-CA-016&quot;</td><td>null</td><td>&quot;Shelter Inc of…</td><td>2</td><td>0</td><td>0</td><td>0</td><td>1</td><td>1</td><td>2</td><td>100.0</td></tr><tr><td>2022</td><td>&quot;12-CA-018&quot;</td><td>null</td><td>&quot;Volunteers of …</td><td>19</td><td>1</td><td>1</td><td>1</td><td>7</td><td>9</td><td>16</td><td>84.2</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (5, 12)\n",
       "┌─────────────┬───────────┬──────────────┬─────────────┬───┬──────┬──────┬─────────────┬───────────┐\n",
       "│ fiscal_year ┆ grant_id  ┆ provider_nam ┆ provider_na ┆ … ┆ q1_4 ┆ q1_5 ┆ top_2_count ┆ top_2_pct │\n",
       "│ ---         ┆ ---       ┆ e            ┆ me_latest   ┆   ┆ ---  ┆ ---  ┆ ---         ┆ ---       │\n",
       "│ i32         ┆ str       ┆ ---          ┆ ---         ┆   ┆ u32  ┆ u32  ┆ u32         ┆ f64       │\n",
       "│             ┆           ┆ str          ┆ str         ┆   ┆      ┆      ┆             ┆           │\n",
       "╞═════════════╪═══════════╪══════════════╪═════════════╪═══╪══════╪══════╪═════════════╪═══════════╡\n",
       "│ 2022        ┆ 12-AK-001 ┆ null         ┆ Catholic    ┆ … ┆ 0    ┆ 2    ┆ 2           ┆ 100.0     │\n",
       "│             ┆           ┆              ┆ Social      ┆   ┆      ┆      ┆             ┆           │\n",
       "│             ┆           ┆              ┆ Services    ┆   ┆      ┆      ┆             ┆           │\n",
       "│ 2022        ┆ 12-AL-002 ┆ null         ┆ Housing     ┆ … ┆ 1    ┆ 1    ┆ 2           ┆ 100.0     │\n",
       "│             ┆           ┆              ┆ First Inc   ┆   ┆      ┆      ┆             ┆           │\n",
       "│ 2022        ┆ 12-CA-013 ┆ null         ┆ Goodwill of ┆ … ┆ 0    ┆ 5    ┆ 5           ┆ 83.3      │\n",
       "│             ┆           ┆              ┆ Silicon     ┆   ┆      ┆      ┆             ┆           │\n",
       "│             ┆           ┆              ┆ Valley (dba ┆   ┆      ┆      ┆             ┆           │\n",
       "│             ┆           ┆              ┆ …           ┆   ┆      ┆      ┆             ┆           │\n",
       "│ 2022        ┆ 12-CA-016 ┆ null         ┆ Shelter Inc ┆ … ┆ 1    ┆ 1    ┆ 2           ┆ 100.0     │\n",
       "│             ┆           ┆              ┆ of Contra   ┆   ┆      ┆      ┆             ┆           │\n",
       "│             ┆           ┆              ┆ Costa Coun… ┆   ┆      ┆      ┆             ┆           │\n",
       "│ 2022        ┆ 12-CA-018 ┆ null         ┆ Volunteers  ┆ … ┆ 7    ┆ 9    ┆ 16          ┆ 84.2      │\n",
       "│             ┆           ┆              ┆ of America  ┆   ┆      ┆      ┆             ┆           │\n",
       "│             ┆           ┆              ┆ of Greater… ┆   ┆      ┆      ┆             ┆           │\n",
       "└─────────────┴───────────┴──────────────┴─────────────┴───┴──────┴──────┴─────────────┴───────────┘"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "results_by_fiscal_year = (\n",
    "    responses\n",
    "    .sort(\"date\")\n",
    "    .groupby(\n",
    "        \"fiscal_year\",\n",
    "        \"grant_id\",\n",
    "    )\n",
    "    .agg(\n",
    "        pl.col(\"provider_name\").last(),\n",
    "        pl.count().alias(\"responses_total\"),\n",
    "        *(pl.col(\"Q1\").eq(x).alias(f\"q1_{x}\").sum() for x in range(1, 6)),\n",
    "        pl.col(\"Q1\").is_in([ 4, 5 ]).sum().alias(\"top_2_count\"),\n",
    "    )\n",
    "    .with_columns(\n",
    "        (100 * pl.col(\"top_2_count\") / pl.col(\"responses_total\")).round(1).alias(\"top_2_pct\"),\n",
    "    )\n",
    "    .sort(\"fiscal_year\", \"grant_id\", descending=(True, False))\n",
    "    .join(latest_names, on=\"grant_id\")\n",
    "    .select(\n",
    "        \"fiscal_year\",\n",
    "        \"grant_id\",\n",
    "        \"provider_name\",\n",
    "        \"provider_name_latest\",\n",
    "        \"responses_total\",\n",
    "        *(f\"q1_{x}\" for x in range(1, 6)),\n",
    "        \"top_2_count\",\n",
    "        \"top_2_pct\",\n",
    "    )\n",
    ")\n",
    "\n",
    "results_by_fiscal_year.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "6518cac3-1395-4f7e-bd5f-5024a7e138cd",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr > th,\n",
       ".dataframe > tbody > tr > td {\n",
       "  text-align: right;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (1_782, 12)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>fiscal_year</th><th>grant_id</th><th>provider_name</th><th>provider_name_latest</th><th>responses_total</th><th>q1_1</th><th>q1_2</th><th>q1_3</th><th>q1_4</th><th>q1_5</th><th>top_2_count</th><th>top_2_pct</th></tr><tr><td>i32</td><td>str</td><td>str</td><td>str</td><td>u32</td><td>u32</td><td>u32</td><td>u32</td><td>u32</td><td>u32</td><td>u32</td><td>f64</td></tr></thead><tbody><tr><td>2022</td><td>&quot;12-AK-001&quot;</td><td>null</td><td>&quot;Catholic Socia…</td><td>2</td><td>0</td><td>0</td><td>0</td><td>0</td><td>2</td><td>2</td><td>100.0</td></tr><tr><td>2022</td><td>&quot;12-AL-002&quot;</td><td>null</td><td>&quot;Housing First …</td><td>2</td><td>0</td><td>0</td><td>0</td><td>1</td><td>1</td><td>2</td><td>100.0</td></tr><tr><td>2022</td><td>&quot;12-CA-013&quot;</td><td>null</td><td>&quot;Goodwill of Si…</td><td>6</td><td>1</td><td>0</td><td>0</td><td>0</td><td>5</td><td>5</td><td>83.3</td></tr><tr><td>2022</td><td>&quot;12-CA-016&quot;</td><td>null</td><td>&quot;Shelter Inc of…</td><td>2</td><td>0</td><td>0</td><td>0</td><td>1</td><td>1</td><td>2</td><td>100.0</td></tr><tr><td>2022</td><td>&quot;12-CA-018&quot;</td><td>null</td><td>&quot;Volunteers of …</td><td>19</td><td>1</td><td>1</td><td>1</td><td>7</td><td>9</td><td>16</td><td>84.2</td></tr><tr><td>2022</td><td>&quot;12-CT-021&quot;</td><td>null</td><td>&quot;Community Rene…</td><td>6</td><td>0</td><td>0</td><td>0</td><td>2</td><td>4</td><td>6</td><td>100.0</td></tr><tr><td>2022</td><td>&quot;12-FL-024&quot;</td><td>null</td><td>&quot;Advocate Progr…</td><td>29</td><td>8</td><td>3</td><td>1</td><td>3</td><td>14</td><td>17</td><td>58.6</td></tr><tr><td>2022</td><td>&quot;12-FL-028&quot;</td><td>null</td><td>&quot;Jewish Family …</td><td>6</td><td>0</td><td>1</td><td>0</td><td>1</td><td>4</td><td>5</td><td>83.3</td></tr><tr><td>2022</td><td>&quot;12-GA-029&quot;</td><td>null</td><td>&quot;Central Savann…</td><td>23</td><td>0</td><td>1</td><td>7</td><td>4</td><td>11</td><td>15</td><td>65.2</td></tr><tr><td>2022</td><td>&quot;12-ID-032&quot;</td><td>null</td><td>&quot;El-Ada Inc&quot;</td><td>14</td><td>1</td><td>0</td><td>2</td><td>5</td><td>6</td><td>11</td><td>78.6</td></tr><tr><td>2022</td><td>&quot;12-IL-033&quot;</td><td>null</td><td>&quot;Thresholds&quot;</td><td>8</td><td>2</td><td>0</td><td>0</td><td>1</td><td>5</td><td>6</td><td>75.0</td></tr><tr><td>2022</td><td>&quot;12-LA-038&quot;</td><td>null</td><td>&quot;Volunteers of …</td><td>17</td><td>2</td><td>1</td><td>0</td><td>4</td><td>10</td><td>14</td><td>82.4</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>2016</td><td>&quot;C2015-DC-500B&quot;</td><td>&quot;Friendship Pla…</td><td>&quot;Friendship Pla…</td><td>11</td><td>1</td><td>1</td><td>4</td><td>2</td><td>3</td><td>5</td><td>45.5</td></tr><tr><td>2016</td><td>&quot;C2015-FL-519A&quot;</td><td>&quot;Society of St.…</td><td>&quot;Society of St …</td><td>8</td><td>0</td><td>1</td><td>1</td><td>2</td><td>4</td><td>6</td><td>75.0</td></tr><tr><td>2016</td><td>&quot;C2015-IL-510F&quot;</td><td>&quot;Associates of …</td><td>&quot;Associates of …</td><td>3</td><td>0</td><td>0</td><td>0</td><td>1</td><td>2</td><td>3</td><td>100.0</td></tr><tr><td>2016</td><td>&quot;C2015-MT-500B&quot;</td><td>&quot;Volunteers of …</td><td>&quot;Volunteers of …</td><td>25</td><td>1</td><td>2</td><td>2</td><td>10</td><td>10</td><td>20</td><td>80.0</td></tr><tr><td>2016</td><td>&quot;C2015-NV-500E&quot;</td><td>&quot;U.S.VETS  Las …</td><td>&quot;United States …</td><td>3</td><td>0</td><td>0</td><td>0</td><td>0</td><td>3</td><td>3</td><td>100.0</td></tr><tr><td>2016</td><td>&quot;C2015-OR-502B&quot;</td><td>&quot;Access&quot;</td><td>&quot;Access&quot;</td><td>6</td><td>1</td><td>0</td><td>2</td><td>2</td><td>1</td><td>3</td><td>50.0</td></tr><tr><td>2016</td><td>&quot;C2015-OR-505A&quot;</td><td>&quot;Community Acti…</td><td>&quot;Community Acti…</td><td>14</td><td>2</td><td>1</td><td>1</td><td>3</td><td>7</td><td>10</td><td>71.4</td></tr><tr><td>2016</td><td>&quot;C2015-SC-502B&quot;</td><td>&quot;One-Eighty Pla…</td><td>&quot;One-Eighty Pla…</td><td>2</td><td>0</td><td>0</td><td>0</td><td>0</td><td>2</td><td>2</td><td>100.0</td></tr><tr><td>2016</td><td>&quot;C2015-TX-607B&quot;</td><td>&quot;Volunteers of …</td><td>&quot;Volunteers of …</td><td>1</td><td>0</td><td>0</td><td>0</td><td>1</td><td>0</td><td>1</td><td>100.0</td></tr><tr><td>2016</td><td>&quot;C2015-TX-607G&quot;</td><td>&quot;Lubbock Region…</td><td>&quot;Lubbock Region…</td><td>1</td><td>0</td><td>0</td><td>0</td><td>0</td><td>1</td><td>1</td><td>100.0</td></tr><tr><td>2016</td><td>&quot;C2015-TX-607H&quot;</td><td>&quot;Family Endeavo…</td><td>&quot;Family Endeavo…</td><td>22</td><td>1</td><td>1</td><td>3</td><td>4</td><td>13</td><td>17</td><td>77.3</td></tr><tr><td>2016</td><td>&quot;C2015-WA-501E&quot;</td><td>&quot;Metropolitan D…</td><td>&quot;Metropolitan D…</td><td>4</td><td>0</td><td>0</td><td>0</td><td>1</td><td>3</td><td>4</td><td>100.0</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (1_782, 12)\n",
       "┌─────────────┬─────────────┬─────────────┬─────────────┬───┬──────┬──────┬────────────┬───────────┐\n",
       "│ fiscal_year ┆ grant_id    ┆ provider_na ┆ provider_na ┆ … ┆ q1_4 ┆ q1_5 ┆ top_2_coun ┆ top_2_pct │\n",
       "│ ---         ┆ ---         ┆ me          ┆ me_latest   ┆   ┆ ---  ┆ ---  ┆ t          ┆ ---       │\n",
       "│ i32         ┆ str         ┆ ---         ┆ ---         ┆   ┆ u32  ┆ u32  ┆ ---        ┆ f64       │\n",
       "│             ┆             ┆ str         ┆ str         ┆   ┆      ┆      ┆ u32        ┆           │\n",
       "╞═════════════╪═════════════╪═════════════╪═════════════╪═══╪══════╪══════╪════════════╪═══════════╡\n",
       "│ 2022        ┆ 12-AK-001   ┆ null        ┆ Catholic    ┆ … ┆ 0    ┆ 2    ┆ 2          ┆ 100.0     │\n",
       "│             ┆             ┆             ┆ Social      ┆   ┆      ┆      ┆            ┆           │\n",
       "│             ┆             ┆             ┆ Services    ┆   ┆      ┆      ┆            ┆           │\n",
       "│ 2022        ┆ 12-AL-002   ┆ null        ┆ Housing     ┆ … ┆ 1    ┆ 1    ┆ 2          ┆ 100.0     │\n",
       "│             ┆             ┆             ┆ First Inc   ┆   ┆      ┆      ┆            ┆           │\n",
       "│ 2022        ┆ 12-CA-013   ┆ null        ┆ Goodwill of ┆ … ┆ 0    ┆ 5    ┆ 5          ┆ 83.3      │\n",
       "│             ┆             ┆             ┆ Silicon     ┆   ┆      ┆      ┆            ┆           │\n",
       "│             ┆             ┆             ┆ Valley (dba ┆   ┆      ┆      ┆            ┆           │\n",
       "│             ┆             ┆             ┆ …           ┆   ┆      ┆      ┆            ┆           │\n",
       "│ 2022        ┆ 12-CA-016   ┆ null        ┆ Shelter Inc ┆ … ┆ 1    ┆ 1    ┆ 2          ┆ 100.0     │\n",
       "│             ┆             ┆             ┆ of Contra   ┆   ┆      ┆      ┆            ┆           │\n",
       "│             ┆             ┆             ┆ Costa Coun… ┆   ┆      ┆      ┆            ┆           │\n",
       "│ …           ┆ …           ┆ …           ┆ …           ┆ … ┆ …    ┆ …    ┆ …          ┆ …         │\n",
       "│ 2016        ┆ C2015-TX-60 ┆ Volunteers  ┆ Volunteers  ┆ … ┆ 1    ┆ 0    ┆ 1          ┆ 100.0     │\n",
       "│             ┆ 7B          ┆ of America  ┆ of America  ┆   ┆      ┆      ┆            ┆           │\n",
       "│             ┆             ┆             ┆ Texas       ┆   ┆      ┆      ┆            ┆           │\n",
       "│ 2016        ┆ C2015-TX-60 ┆ Lubbock     ┆ Lubbock     ┆ … ┆ 0    ┆ 1    ┆ 1          ┆ 100.0     │\n",
       "│             ┆ 7G          ┆ Regional    ┆ Regional    ┆   ┆      ┆      ┆            ┆           │\n",
       "│             ┆             ┆ Mental      ┆ Mental      ┆   ┆      ┆      ┆            ┆           │\n",
       "│             ┆             ┆ Health M…   ┆ Health M…   ┆   ┆      ┆      ┆            ┆           │\n",
       "│ 2016        ┆ C2015-TX-60 ┆ Family      ┆ Family      ┆ … ┆ 4    ┆ 13   ┆ 17         ┆ 77.3      │\n",
       "│             ┆ 7H          ┆ Endeavors,  ┆ Endeavors   ┆   ┆      ┆      ┆            ┆           │\n",
       "│             ┆             ┆ Inc.        ┆ Inc.        ┆   ┆      ┆      ┆            ┆           │\n",
       "│ 2016        ┆ C2015-WA-50 ┆ Metropolita ┆ Metropolita ┆ … ┆ 1    ┆ 3    ┆ 4          ┆ 100.0     │\n",
       "│             ┆ 1E          ┆ n           ┆ n           ┆   ┆      ┆      ┆            ┆           │\n",
       "│             ┆             ┆ Development ┆ Development ┆   ┆      ┆      ┆            ┆           │\n",
       "│             ┆             ┆ Council     ┆ Council     ┆   ┆      ┆      ┆            ┆           │\n",
       "└─────────────┴─────────────┴─────────────┴─────────────┴───┴──────┴──────┴────────────┴───────────┘"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(results_by_fiscal_year)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "7f5068fd-eabe-46b4-b33a-80f843a0d984",
   "metadata": {},
   "outputs": [],
   "source": [
    "results_by_fiscal_year.write_csv(\n",
    "    \"../data/output/provider-satisfaction-by-fiscal-year.csv\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2decd1e5-8ce4-471f-80b5-c832ce5ecea7",
   "metadata": {},
   "source": [
    "## Addendum note on provider names"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "66a27e08-e55e-4885-9f94-9c73aeeec471",
   "metadata": {},
   "source": [
    "Names generally stay the same, with stylistic changes, but not always:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "id": "aaa6bf05-292b-4748-b129-7218174abf02",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div><style>\n",
       ".dataframe > thead > tr > th,\n",
       ".dataframe > tbody > tr > td {\n",
       "  text-align: right;\n",
       "}\n",
       "</style>\n",
       "<small>shape: (58, 3)</small><table border=\"1\" class=\"dataframe\"><thead><tr><th>grant_id</th><th>total_names</th><th>names</th></tr><tr><td>str</td><td>u32</td><td>list[str]</td></tr></thead><tbody><tr><td>&quot;13-IN-106&quot;</td><td>2</td><td>[&quot;COMMUNITY ACTION OF NORTHEAST INDIANA(DBA BRIGHTPOINT)&quot;, &quot;COMMUNITY ACTION OF NORTHEAST INDIANA&quot;]</td></tr><tr><td>&quot;14-NY-251&quot;</td><td>2</td><td>[&quot;VETERANS AND COMMUNITY HOUSING COALITION&quot;, &quot;SARATOGA COUNTY RURAL PRESERVATION COMPANY&quot;]</td></tr><tr><td>&quot;14-MI-218&quot;</td><td>2</td><td>[&quot;BLUEWATER CENTER FOR INDEPENDENT LIVING&quot;, &quot;BLUE WATER CENTER FOR INDEPENDENT LIVING&quot;]</td></tr><tr><td>&quot;12-FL-025&quot;</td><td>2</td><td>[&quot;CARRFOUR SUPPORTIVE HOUSING&quot;, &quot;PURPOSE BUILT FAMILIES FOUNDATION&quot;]</td></tr><tr><td>&quot;12-NY-063&quot;</td><td>2</td><td>[&quot;WESTCHESTER COMMUNITY OPPORTUNITY PROGRAM(WESTCOP)&quot;, &quot;WESTCHESTER COMMUNITY OPPORTUNITY PROGRAM&quot;]</td></tr><tr><td>&quot;12-OH-064&quot;</td><td>2</td><td>[&quot;FRONTLINE&quot;, &quot;MENTAL HEALTH SERVICES FOR HOMELESS PERSONSDBA FRONTLINE SERVICE&quot;]</td></tr><tr><td>&quot;13-NC-114&quot;</td><td>2</td><td>[&quot;ASHEVILLE BUNCOMBE COMMUNITY CHRISTIAN MINISTRY&quot;, &quot;ASHEVILLE-BUNCOMBE COMMUNITY CHRISTIAN MINISTRY&quot;]</td></tr><tr><td>&quot;C15-NV-500B&quot;</td><td>2</td><td>[&quot;UNITED STATES VETERANS INITIATIVE&quot;, &quot;USVETS LAS VEGAS&quot;]</td></tr><tr><td>&quot;12-ZZ-020&quot;</td><td>2</td><td>[&quot;ROCKY MOUNTAIN HUMAN SERVICES (DBA DENVER OPTIONS&quot;, &quot;ROCKY MOUNTAIN HUMAN SERVICES (DBA DENVER OPTIONS)&quot;]</td></tr><tr><td>&quot;14-GA-189&quot;</td><td>2</td><td>[&quot;UNITED WAY OF GREATER ATLANTA&quot;, &quot;UNITED WAY OF METROPOLITAN ATLANTA&quot;]</td></tr><tr><td>&quot;12-CA-013&quot;</td><td>2</td><td>[&quot;GOODWILL OF SILICON VALLEY (DBA GOODWILL INDUSTRIES OF SANTA CLARA COUNTY)&quot;, &quot;GOODWILL INDUSTRIES OF SANTA CLARA COUNTY&quot;]</td></tr><tr><td>&quot;12-FL-028&quot;</td><td>2</td><td>[&quot;JEWISH FAMILY &amp; CHILDRENS SERVICE OF THE SUNCOAST&quot;, &quot;JEWISH FAMILY &amp; CHILDRENS SERVICE OF SARASOTA-MANATEE&quot;]</td></tr><tr><td>&hellip;</td><td>&hellip;</td><td>&hellip;</td></tr><tr><td>&quot;C15-MA-500A&quot;</td><td>2</td><td>[&quot;VIETNAM VETERANS WORKSHOP&quot;, &quot;NEW ENGLAND CENTER AND HOME FOR VETERANS&quot;]</td></tr><tr><td>&quot;13-OR-126&quot;</td><td>2</td><td>[&quot;CENTRAL OREGON VETERANS OUTREACH&quot;, &quot;CENTRAL OREGON VETERAN&#x27;S OUTREACH&quot;]</td></tr><tr><td>&quot;12-NY-061&quot;</td><td>2</td><td>[&quot;SAMARITAN DAYTOP VILLAGE&quot;, &quot;SAMARITAN VILLAGE&quot;]</td></tr><tr><td>&quot;14-TN-287&quot;</td><td>2</td><td>[&quot;CATHOLIC CHARITIES(DBA CATHOLIC CHARITIES OF THE DIOCESE OF MEMPHIS)&quot;, &quot;CATHOLIC CHARITIES&quot;]</td></tr><tr><td>&quot;15-PA-334&quot;</td><td>3</td><td>[&quot;VETERANS MULTI-SERVICE&quot;, &quot;VETERANS MULTI-SERVICE &amp; EDUCATION CENTER&quot;, &quot;VETERANS MULTI-SERVICE CENTER&quot;]</td></tr><tr><td>&quot;C15-CO-504A&quot;</td><td>2</td><td>[&quot;DENVER OPTIONSDBA ROCKY MOUNTAIN HUMAN SERVICES&quot;, &quot;ROCKY MOUNTAIN HUMAN SERVICES (DBA DENVER OPTIONS)&quot;]</td></tr><tr><td>&quot;12-CA-010&quot;</td><td>3</td><td>[&quot;HOMEFIRST SERVICES OF SANTA CLARA COUNTY&quot;, &quot;EMERGENCY HOUSING CONSORTIUM OF SANTA CLARA COUNTY&quot;, &quot;HOME FIRST SERVICES OF SANTA CLARA COUNTY&quot;]</td></tr><tr><td>&quot;13-MS-111&quot;</td><td>3</td><td>[&quot;REGION XII COMMISSION ON MENTAL HEALTH&quot;, &quot;REGION XII COMMISSION ON MENTAL HEALTH &amp; RETARDATION (PINE BELT MENTAL HEALTH)&quot;, &quot;REGION XII COMMISSION ON MENTAL HEALTH &amp; RETARDATION (DBA PINE BELT MENTAL HEALTH)&quot;]</td></tr><tr><td>&quot;14-VA-296&quot;</td><td>2</td><td>[&quot;HAMPTON ROADS COMMUNITY ACTION PROGRAM&quot;, &quot;OFFICE OF HUMAN AFFAIRS&quot;]</td></tr><tr><td>&quot;14-OH-265&quot;</td><td>2</td><td>[&quot;COMMUNITY ACTION PROGRAM CORPORATION OF WASHINGTON-MORGAN COUNTIES&quot;, &quot;COMMUNITY ACTION PROGRAM CORPORATION OF WASHINGTON-MORGAN COUNTIES OHIO&quot;]</td></tr><tr><td>&quot;C2015-TX-607B&quot;</td><td>2</td><td>[&quot;VOLUNTEERS OF AMERICA TEXAS&quot;, &quot;VOLUNTEERS OF AMERICA&quot;]</td></tr><tr><td>&quot;C15-FL-600A&quot;</td><td>2</td><td>[&quot;PURPOSE BUILT FAMILIES FOUNDATION&quot;, &quot;CARRFOUR SUPPORTIVE HOUSING&quot;]</td></tr></tbody></table></div>"
      ],
      "text/plain": [
       "shape: (58, 3)\n",
       "┌───────────────┬─────────────┬───────────────────────────────────┐\n",
       "│ grant_id      ┆ total_names ┆ names                             │\n",
       "│ ---           ┆ ---         ┆ ---                               │\n",
       "│ str           ┆ u32         ┆ list[str]                         │\n",
       "╞═══════════════╪═════════════╪═══════════════════════════════════╡\n",
       "│ 13-IN-106     ┆ 2           ┆ [\"COMMUNITY ACTION OF NORTHEAST … │\n",
       "│ 14-NY-251     ┆ 2           ┆ [\"VETERANS AND COMMUNITY HOUSING… │\n",
       "│ 14-MI-218     ┆ 2           ┆ [\"BLUEWATER CENTER FOR INDEPENDE… │\n",
       "│ 12-FL-025     ┆ 2           ┆ [\"CARRFOUR SUPPORTIVE HOUSING\", … │\n",
       "│ …             ┆ …           ┆ …                                 │\n",
       "│ 14-VA-296     ┆ 2           ┆ [\"HAMPTON ROADS COMMUNITY ACTION… │\n",
       "│ 14-OH-265     ┆ 2           ┆ [\"COMMUNITY ACTION PROGRAM CORPO… │\n",
       "│ C2015-TX-607B ┆ 2           ┆ [\"VOLUNTEERS OF AMERICA TEXAS\", … │\n",
       "│ C15-FL-600A   ┆ 2           ┆ [\"PURPOSE BUILT FAMILIES FOUNDAT… │\n",
       "└───────────────┴─────────────┴───────────────────────────────────┘"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    responses\n",
    "    .filter(~pl.col(\"provider_name\").is_null())\n",
    "    .with_columns(\n",
    "        (\n",
    "            pl.col(\"provider_name\")\n",
    "            .str.to_uppercase()\n",
    "            .str.replace_all(r\"[,.]\", \"\")\n",
    "            .str.replace_all(r\"\\s+\", \" \")\n",
    "            .str.replace_all(r\" INC\\s*\", \"\")\n",
    "            .str.strip()\n",
    "            .alias(\"name_normalized\")\n",
    "        )\n",
    "    )\n",
    "    .groupby(\"grant_id\")\n",
    "    .agg(\n",
    "        pl.col(\"name_normalized\").n_unique().alias(\"total_names\"),\n",
    "        pl.col(\"name_normalized\").unique().alias(\"names\")\n",
    "    )\n",
    "    .filter(pl.col(\"total_names\") > 1)\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "43dae4be-cd98-4fc5-9e8b-61aa3db5ccb4",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "---\n",
    "\n",
    "---\n"
   ]
  }
 ],
 "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}