4. MFT at the county level.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "e2d5d1d5",
   "metadata": {},
   "source": [
    "# Aggregating MFT to the county level\n",
    "\n",
    "In order to merge the MFT data (recorded at the zipcode level) with the IRS/ACS data (recorded at the county level), it is necessary to get convert from zip code to county as the summary level. To do this, we will use the Census' [zipcode to county relationship file](https://www.census.gov/geographies/reference-files/time-series/geo/relationship-files.2010.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "4d504093",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "494f630e",
   "metadata": {},
   "source": [
    "### Merging relationship file and MFT data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "37f69ff9",
   "metadata": {},
   "outputs": [],
   "source": [
    "rel_df = pd.read_csv(\"./data/zcta_county_rel_10.csv\", dtype={'ZCTA5': object})\n",
    "mft_df = pd.read_csv(\"./data/mft_county_rel.csv\", dtype={'ZCTA5': object})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "95d0ec99",
   "metadata": {},
   "outputs": [],
   "source": [
    "mft_county_rel = pd.merge(rel_df, mft_df, how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "018bce62",
   "metadata": {},
   "source": [
    "Checking that we didn't leave anything behind, by looking for any rows where the zip code was in the MFT dataset but the United Way field is empty. There shouldn't be any rows where this is true."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "d3a87894",
   "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>ZCTA5</th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>GEOID</th>\n",
       "      <th>POPPT</th>\n",
       "      <th>HUPT</th>\n",
       "      <th>AREAPT</th>\n",
       "      <th>AREALANDPT</th>\n",
       "      <th>ZPOP</th>\n",
       "      <th>ZHU</th>\n",
       "      <th>...</th>\n",
       "      <th>COPOPPCT</th>\n",
       "      <th>COHUPCT</th>\n",
       "      <th>COAREAPCT</th>\n",
       "      <th>COAREALANDPCT</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>County</th>\n",
       "      <th>Org ID</th>\n",
       "      <th>United Way</th>\n",
       "      <th>#e-filed returns</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>0 rows × 30 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "Empty DataFrame\n",
       "Columns: [ZCTA5, STATE, COUNTY, GEOID, POPPT, HUPT, AREAPT, AREALANDPT, ZPOP, ZHU, ZAREA, ZAREALAND, COPOP, COHU, COAREA, COAREALAND, ZPOPPCT, ZHUPCT, ZAREAPCT, ZAREALANDPCT, COPOPPCT, COHUPCT, COAREAPCT, COAREALANDPCT, City, State, County, Org ID, United Way, #e-filed returns]\n",
       "Index: []\n",
       "\n",
       "[0 rows x 30 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mft_county_rel[mft_county_rel.ZCTA5.isin(mft_df.ZCTA5) & pd.isnull(mft_county_rel['United Way'])]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c4489303",
   "metadata": {},
   "source": [
    "Everything looks good!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "bfc852f8",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['ZCTA5', 'STATE', 'COUNTY', 'GEOID', 'POPPT', 'HUPT', 'AREAPT',\n",
       "       'AREALANDPT', 'ZPOP', 'ZHU', 'ZAREA', 'ZAREALAND', 'COPOP', 'COHU',\n",
       "       'COAREA', 'COAREALAND', 'ZPOPPCT', 'ZHUPCT', 'ZAREAPCT', 'ZAREALANDPCT',\n",
       "       'COPOPPCT', 'COHUPCT', 'COAREAPCT', 'COAREALANDPCT', 'City', 'State',\n",
       "       'County', 'Org ID', 'United Way', '#e-filed returns'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mft_county_rel.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b7cc90e8",
   "metadata": {},
   "source": [
    "### Aggregating to the county\n",
    "\n",
    "Because zipcodes and counties are not co-terminous, some zipcodes are in multiple counties. The column `ZPOPPCT` in the zipcode to county relationship file denotes how much of the zipcode's population is in each county. For example, zip code `39573` stretches across 5 counties:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "76145aae",
   "metadata": {
    "scrolled": true
   },
   "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>ZCTA5</th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>County</th>\n",
       "      <th>STATE</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>ZPOPPCT</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>16570</th>\n",
       "      <td>39573</td>\n",
       "      <td>Perkinston</td>\n",
       "      <td>MS</td>\n",
       "      <td>Stone</td>\n",
       "      <td>28</td>\n",
       "      <td>39</td>\n",
       "      <td>3.56</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16571</th>\n",
       "      <td>39573</td>\n",
       "      <td>Perkinston</td>\n",
       "      <td>MS</td>\n",
       "      <td>Stone</td>\n",
       "      <td>28</td>\n",
       "      <td>45</td>\n",
       "      <td>21.91</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16572</th>\n",
       "      <td>39573</td>\n",
       "      <td>Perkinston</td>\n",
       "      <td>MS</td>\n",
       "      <td>Stone</td>\n",
       "      <td>28</td>\n",
       "      <td>47</td>\n",
       "      <td>1.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16573</th>\n",
       "      <td>39573</td>\n",
       "      <td>Perkinston</td>\n",
       "      <td>MS</td>\n",
       "      <td>Stone</td>\n",
       "      <td>28</td>\n",
       "      <td>59</td>\n",
       "      <td>1.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16574</th>\n",
       "      <td>39573</td>\n",
       "      <td>Perkinston</td>\n",
       "      <td>MS</td>\n",
       "      <td>Stone</td>\n",
       "      <td>28</td>\n",
       "      <td>109</td>\n",
       "      <td>1.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16575</th>\n",
       "      <td>39573</td>\n",
       "      <td>Perkinston</td>\n",
       "      <td>MS</td>\n",
       "      <td>Stone</td>\n",
       "      <td>28</td>\n",
       "      <td>131</td>\n",
       "      <td>70.59</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       ZCTA5        City State County  STATE  COUNTY  ZPOPPCT\n",
       "16570  39573  Perkinston    MS  Stone     28      39     3.56\n",
       "16571  39573  Perkinston    MS  Stone     28      45    21.91\n",
       "16572  39573  Perkinston    MS  Stone     28      47     1.11\n",
       "16573  39573  Perkinston    MS  Stone     28      59     1.52\n",
       "16574  39573  Perkinston    MS  Stone     28     109     1.31\n",
       "16575  39573  Perkinston    MS  Stone     28     131    70.59"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mft_county_rel[mft_county_rel.ZCTA5 == '39573'][['ZCTA5', 'City', 'State', 'County', 'STATE', 'COUNTY', 'ZPOPPCT']]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3099d7db",
   "metadata": {},
   "source": [
    "We want one county per zipcode, so we'll take only the row with the highest percentage of people. In the case of zipcode `38573`, that would be the last row (the one with a county FIPS code of `131`)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "e6f2445f",
   "metadata": {},
   "outputs": [],
   "source": [
    "mft_county_rel = mft_county_rel.sort_values(['ZCTA5', 'COUNTY', 'ZPOPPCT']).drop_duplicates(['ZCTA5', 'COUNTY'], keep='last')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "01f410db",
   "metadata": {
    "scrolled": true
   },
   "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>STATE</th>\n",
       "      <th>COUNTY</th>\n",
       "      <th>GEOID</th>\n",
       "      <th>POPPT</th>\n",
       "      <th>HUPT</th>\n",
       "      <th>AREAPT</th>\n",
       "      <th>AREALANDPT</th>\n",
       "      <th>ZPOP</th>\n",
       "      <th>ZHU</th>\n",
       "      <th>ZAREA</th>\n",
       "      <th>...</th>\n",
       "      <th>COAREALAND</th>\n",
       "      <th>ZPOPPCT</th>\n",
       "      <th>ZHUPCT</th>\n",
       "      <th>ZAREAPCT</th>\n",
       "      <th>ZAREALANDPCT</th>\n",
       "      <th>COPOPPCT</th>\n",
       "      <th>COHUPCT</th>\n",
       "      <th>COAREAPCT</th>\n",
       "      <th>COAREALANDPCT</th>\n",
       "      <th>#e-filed returns</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>44409.000000</td>\n",
       "      <td>44409.000000</td>\n",
       "      <td>44409.000000</td>\n",
       "      <td>44409.000000</td>\n",
       "      <td>44409.000000</td>\n",
       "      <td>4.440900e+04</td>\n",
       "      <td>4.440900e+04</td>\n",
       "      <td>44409.000000</td>\n",
       "      <td>44409.000000</td>\n",
       "      <td>4.440900e+04</td>\n",
       "      <td>...</td>\n",
       "      <td>4.440900e+04</td>\n",
       "      <td>44409.000000</td>\n",
       "      <td>44409.000000</td>\n",
       "      <td>44409.000000</td>\n",
       "      <td>44409.000000</td>\n",
       "      <td>44409.000000</td>\n",
       "      <td>44409.000000</td>\n",
       "      <td>44409.000000</td>\n",
       "      <td>44409.000000</td>\n",
       "      <td>21906.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>30.124142</td>\n",
       "      <td>87.833637</td>\n",
       "      <td>30211.975140</td>\n",
       "      <td>7036.027607</td>\n",
       "      <td>3002.365939</td>\n",
       "      <td>1.711418e+08</td>\n",
       "      <td>1.673306e+08</td>\n",
       "      <td>9111.244207</td>\n",
       "      <td>3901.043572</td>\n",
       "      <td>2.626092e+08</td>\n",
       "      <td>...</td>\n",
       "      <td>3.466145e+09</td>\n",
       "      <td>74.255175</td>\n",
       "      <td>73.894835</td>\n",
       "      <td>74.578953</td>\n",
       "      <td>74.578992</td>\n",
       "      <td>7.252251</td>\n",
       "      <td>7.248556</td>\n",
       "      <td>6.705254</td>\n",
       "      <td>6.923092</td>\n",
       "      <td>12.697982</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>15.323056</td>\n",
       "      <td>85.813008</td>\n",
       "      <td>15333.111889</td>\n",
       "      <td>12341.371141</td>\n",
       "      <td>5040.099625</td>\n",
       "      <td>5.626424e+08</td>\n",
       "      <td>5.504662e+08</td>\n",
       "      <td>13235.141549</td>\n",
       "      <td>5399.305229</td>\n",
       "      <td>6.597467e+08</td>\n",
       "      <td>...</td>\n",
       "      <td>1.220071e+10</td>\n",
       "      <td>38.601151</td>\n",
       "      <td>38.688594</td>\n",
       "      <td>35.947392</td>\n",
       "      <td>35.956118</td>\n",
       "      <td>14.184957</td>\n",
       "      <td>13.916541</td>\n",
       "      <td>10.568559</td>\n",
       "      <td>10.867822</td>\n",
       "      <td>22.525799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>1.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1001.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>2.270000e+03</td>\n",
       "      <td>2.270000e+03</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>5.094000e+03</td>\n",
       "      <td>...</td>\n",
       "      <td>5.176813e+06</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>18.000000</td>\n",
       "      <td>31.000000</td>\n",
       "      <td>18127.000000</td>\n",
       "      <td>277.000000</td>\n",
       "      <td>139.000000</td>\n",
       "      <td>1.692736e+07</td>\n",
       "      <td>1.606413e+07</td>\n",
       "      <td>867.000000</td>\n",
       "      <td>432.000000</td>\n",
       "      <td>3.982343e+07</td>\n",
       "      <td>...</td>\n",
       "      <td>1.214167e+09</td>\n",
       "      <td>42.560000</td>\n",
       "      <td>40.490000</td>\n",
       "      <td>45.980000</td>\n",
       "      <td>46.040000</td>\n",
       "      <td>0.610000</td>\n",
       "      <td>0.650000</td>\n",
       "      <td>0.830000</td>\n",
       "      <td>0.890000</td>\n",
       "      <td>2.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>29.000000</td>\n",
       "      <td>71.000000</td>\n",
       "      <td>29221.000000</td>\n",
       "      <td>1324.000000</td>\n",
       "      <td>635.000000</td>\n",
       "      <td>6.267203e+07</td>\n",
       "      <td>6.080415e+07</td>\n",
       "      <td>2968.000000</td>\n",
       "      <td>1390.000000</td>\n",
       "      <td>1.249640e+08</td>\n",
       "      <td>...</td>\n",
       "      <td>1.727139e+09</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>2.230000</td>\n",
       "      <td>2.330000</td>\n",
       "      <td>3.120000</td>\n",
       "      <td>3.300000</td>\n",
       "      <td>6.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>42.000000</td>\n",
       "      <td>119.000000</td>\n",
       "      <td>42063.000000</td>\n",
       "      <td>7345.000000</td>\n",
       "      <td>3222.000000</td>\n",
       "      <td>1.641167e+08</td>\n",
       "      <td>1.596084e+08</td>\n",
       "      <td>11927.000000</td>\n",
       "      <td>5188.000000</td>\n",
       "      <td>2.794483e+08</td>\n",
       "      <td>...</td>\n",
       "      <td>2.562955e+09</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>6.550000</td>\n",
       "      <td>6.720000</td>\n",
       "      <td>8.120000</td>\n",
       "      <td>8.390000</td>\n",
       "      <td>13.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>72.000000</td>\n",
       "      <td>840.000000</td>\n",
       "      <td>72153.000000</td>\n",
       "      <td>113916.000000</td>\n",
       "      <td>47617.000000</td>\n",
       "      <td>3.510842e+10</td>\n",
       "      <td>3.478610e+10</td>\n",
       "      <td>113916.000000</td>\n",
       "      <td>47617.000000</td>\n",
       "      <td>3.510842e+10</td>\n",
       "      <td>...</td>\n",
       "      <td>3.768557e+11</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>456.000000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>8 rows × 24 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "              STATE        COUNTY         GEOID          POPPT          HUPT  \\\n",
       "count  44409.000000  44409.000000  44409.000000   44409.000000  44409.000000   \n",
       "mean      30.124142     87.833637  30211.975140    7036.027607   3002.365939   \n",
       "std       15.323056     85.813008  15333.111889   12341.371141   5040.099625   \n",
       "min        1.000000      1.000000   1001.000000       0.000000      0.000000   \n",
       "25%       18.000000     31.000000  18127.000000     277.000000    139.000000   \n",
       "50%       29.000000     71.000000  29221.000000    1324.000000    635.000000   \n",
       "75%       42.000000    119.000000  42063.000000    7345.000000   3222.000000   \n",
       "max       72.000000    840.000000  72153.000000  113916.000000  47617.000000   \n",
       "\n",
       "             AREAPT    AREALANDPT           ZPOP           ZHU         ZAREA  \\\n",
       "count  4.440900e+04  4.440900e+04   44409.000000  44409.000000  4.440900e+04   \n",
       "mean   1.711418e+08  1.673306e+08    9111.244207   3901.043572  2.626092e+08   \n",
       "std    5.626424e+08  5.504662e+08   13235.141549   5399.305229  6.597467e+08   \n",
       "min    2.270000e+03  2.270000e+03       0.000000      0.000000  5.094000e+03   \n",
       "25%    1.692736e+07  1.606413e+07     867.000000    432.000000  3.982343e+07   \n",
       "50%    6.267203e+07  6.080415e+07    2968.000000   1390.000000  1.249640e+08   \n",
       "75%    1.641167e+08  1.596084e+08   11927.000000   5188.000000  2.794483e+08   \n",
       "max    3.510842e+10  3.478610e+10  113916.000000  47617.000000  3.510842e+10   \n",
       "\n",
       "       ...    COAREALAND       ZPOPPCT        ZHUPCT      ZAREAPCT  \\\n",
       "count  ...  4.440900e+04  44409.000000  44409.000000  44409.000000   \n",
       "mean   ...  3.466145e+09     74.255175     73.894835     74.578953   \n",
       "std    ...  1.220071e+10     38.601151     38.688594     35.947392   \n",
       "min    ...  5.176813e+06      0.000000      0.000000      0.000000   \n",
       "25%    ...  1.214167e+09     42.560000     40.490000     45.980000   \n",
       "50%    ...  1.727139e+09    100.000000    100.000000    100.000000   \n",
       "75%    ...  2.562955e+09    100.000000    100.000000    100.000000   \n",
       "max    ...  3.768557e+11    100.000000    100.000000    100.000000   \n",
       "\n",
       "       ZAREALANDPCT      COPOPPCT       COHUPCT     COAREAPCT  COAREALANDPCT  \\\n",
       "count  44409.000000  44409.000000  44409.000000  44409.000000   44409.000000   \n",
       "mean      74.578992      7.252251      7.248556      6.705254       6.923092   \n",
       "std       35.956118     14.184957     13.916541     10.568559      10.867822   \n",
       "min        0.000000      0.000000      0.000000      0.000000       0.000000   \n",
       "25%       46.040000      0.610000      0.650000      0.830000       0.890000   \n",
       "50%      100.000000      2.230000      2.330000      3.120000       3.300000   \n",
       "75%      100.000000      6.550000      6.720000      8.120000       8.390000   \n",
       "max      100.000000    100.000000    100.000000    100.000000     100.000000   \n",
       "\n",
       "       #e-filed returns  \n",
       "count      21906.000000  \n",
       "mean          12.697982  \n",
       "std           22.525799  \n",
       "min            1.000000  \n",
       "25%            2.000000  \n",
       "50%            6.000000  \n",
       "75%           13.000000  \n",
       "max          456.000000  \n",
       "\n",
       "[8 rows x 24 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mft_county_rel.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3a306a7a",
   "metadata": {},
   "source": [
    "And, since some counties have lots of zipcodes, we need to get a total per county."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "11f07118",
   "metadata": {},
   "outputs": [],
   "source": [
    "mft_by_county = mft_county_rel.groupby(['STATE', 'COUNTY']).agg(\n",
    "    {\n",
    "        '#e-filed returns': 'sum',\n",
    "        'United Way': lambda gp: ', '.join(\n",
    "            [x for x in set(gp.astype('str')) if x != 'nan']\n",
    "        )\n",
    "    }).reset_index()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e11e0bd",
   "metadata": {},
   "source": [
    "### Reality Checks\n",
    "\n",
    "Making sure all of the counties made it to `mft_county_rel` and that all e-filed returns made it to `mft_by_county"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "96752b75",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(mft_county_rel.groupby(['STATE', 'COUNTY']).groups) == len(rel_df.groupby(['STATE', 'COUNTY']).groups)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "94f8cbf9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mft_county_rel['#e-filed returns'].sum() == mft_by_county['#e-filed returns'].sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "868cc55f",
   "metadata": {},
   "source": [
    "### Export"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "28552af5",
   "metadata": {},
   "outputs": [],
   "source": [
    "mft_by_county.to_csv(\"./data/mft_at_county_level.csv\", index=False)"
   ]
  }
 ],
 "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.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}