notebooks/calculate-metro-area-proportions.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Calculating the proportion of population, cases, and deaths in largest US metro areas\n",
    "\n",
    "This notebook aggregates COVID-19 cases and death counts to the level of Census-defined [Core-Based Statistical Areas](https://www.census.gov/topics/housing/housing-patterns/about/core-based-statistical-areas.html) (i.e., metropolitan and micropolitan ares), and then calculates the proportion of population, cases, and deaths within the 15 largest, in relation to US totals."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load CBSA and COVID data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "CBSA information — their names, populations, and constituent counties — comes from the Census Bureau's [\"delineation files\"](https://www.census.gov/geographies/reference-files/time-series/demo/metro-micro/delineation-files.html) and [Population Estimates API](https://www.census.gov/data/developers/data-sets/popest-popproj/popest.html):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>cbsa_code</th>\n",
       "      <th>cbsa_title</th>\n",
       "      <th>metro_micro</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10100</td>\n",
       "      <td>Aberdeen, SD</td>\n",
       "      <td>Micropolitan Statistical Area</td>\n",
       "      <td>42668</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10140</td>\n",
       "      <td>Aberdeen, WA</td>\n",
       "      <td>Micropolitan Statistical Area</td>\n",
       "      <td>75061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10180</td>\n",
       "      <td>Abilene, TX</td>\n",
       "      <td>Metropolitan Statistical Area</td>\n",
       "      <td>172060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10220</td>\n",
       "      <td>Ada, OK</td>\n",
       "      <td>Micropolitan Statistical Area</td>\n",
       "      <td>38284</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10300</td>\n",
       "      <td>Adrian, MI</td>\n",
       "      <td>Micropolitan Statistical Area</td>\n",
       "      <td>98451</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  cbsa_code    cbsa_title                    metro_micro  population\n",
       "0     10100  Aberdeen, SD  Micropolitan Statistical Area       42668\n",
       "1     10140  Aberdeen, WA  Micropolitan Statistical Area       75061\n",
       "2     10180   Abilene, TX  Metropolitan Statistical Area      172060\n",
       "3     10220       Ada, OK  Micropolitan Statistical Area       38284\n",
       "4     10300    Adrian, MI  Micropolitan Statistical Area       98451"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cbsas = (\n",
    "    pd.read_csv(\n",
    "        \"../data/county-data/cbsas.csv\",\n",
    "        dtype = {\n",
    "            \"cbsa_code\": str,\n",
    "            \"population\": int,\n",
    "        }\n",
    "    )\n",
    ")\n",
    "\n",
    "cbsas.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>cbsa_code</th>\n",
       "      <th>county_fips</th>\n",
       "      <th>county_name</th>\n",
       "      <th>state_name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10100</td>\n",
       "      <td>46013</td>\n",
       "      <td>Brown County</td>\n",
       "      <td>South Dakota</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10100</td>\n",
       "      <td>46045</td>\n",
       "      <td>Edmunds County</td>\n",
       "      <td>South Dakota</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10140</td>\n",
       "      <td>53027</td>\n",
       "      <td>Grays Harbor County</td>\n",
       "      <td>Washington</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10180</td>\n",
       "      <td>48059</td>\n",
       "      <td>Callahan County</td>\n",
       "      <td>Texas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10180</td>\n",
       "      <td>48253</td>\n",
       "      <td>Jones County</td>\n",
       "      <td>Texas</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  cbsa_code county_fips          county_name    state_name\n",
       "0     10100       46013         Brown County  South Dakota\n",
       "1     10100       46045       Edmunds County  South Dakota\n",
       "2     10140       53027  Grays Harbor County    Washington\n",
       "3     10180       48059      Callahan County         Texas\n",
       "4     10180       48253         Jones County         Texas"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cbsa_counties = (\n",
    "    pd.read_csv(\n",
    "        \"../data/county-data/cbsa-counties.csv\",\n",
    "        dtype = {\n",
    "            \"cbsa_code\": str,\n",
    "            \"county_fips\": str,\n",
    "        }\n",
    "    )\n",
    ")\n",
    "\n",
    "cbsa_counties.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "County-level COVID-19 counts [come from the New York Times](https://github.com/nytimes/covid-19-data):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>date</th>\n",
       "      <th>county</th>\n",
       "      <th>state</th>\n",
       "      <th>county_fips</th>\n",
       "      <th>cases</th>\n",
       "      <th>deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2020-01-21</td>\n",
       "      <td>Snohomish</td>\n",
       "      <td>Washington</td>\n",
       "      <td>53061</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2020-01-22</td>\n",
       "      <td>Snohomish</td>\n",
       "      <td>Washington</td>\n",
       "      <td>53061</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2020-01-23</td>\n",
       "      <td>Snohomish</td>\n",
       "      <td>Washington</td>\n",
       "      <td>53061</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2020-01-24</td>\n",
       "      <td>Cook</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>17031</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2020-01-24</td>\n",
       "      <td>Snohomish</td>\n",
       "      <td>Washington</td>\n",
       "      <td>53061</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date     county       state county_fips  cases  deaths\n",
       "0  2020-01-21  Snohomish  Washington       53061      1       0\n",
       "1  2020-01-22  Snohomish  Washington       53061      1       0\n",
       "2  2020-01-23  Snohomish  Washington       53061      1       0\n",
       "3  2020-01-24       Cook    Illinois       17031      1       0\n",
       "4  2020-01-24  Snohomish  Washington       53061      1       0"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nyt_counties = (\n",
    "    pd.read_csv(\n",
    "        \"../data/county-data/nyt-county-counts.csv\",\n",
    "        dtype = {\n",
    "            \"fips\": str\n",
    "        }\n",
    "    )\n",
    "    .rename(columns = {\n",
    "        \"fips\": \"county_fips\"\n",
    "    })\n",
    ")\n",
    "\n",
    "nyt_counties.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Select only the most recent counts for each county"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>date</th>\n",
       "      <th>county</th>\n",
       "      <th>state</th>\n",
       "      <th>county_fips</th>\n",
       "      <th>cases</th>\n",
       "      <th>deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>114279</th>\n",
       "      <td>2020-05-04</td>\n",
       "      <td>New York City</td>\n",
       "      <td>New York</td>\n",
       "      <td>NaN</td>\n",
       "      <td>175661</td>\n",
       "      <td>13207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114278</th>\n",
       "      <td>2020-05-04</td>\n",
       "      <td>Nassau</td>\n",
       "      <td>New York</td>\n",
       "      <td>36059</td>\n",
       "      <td>36965</td>\n",
       "      <td>2221</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113810</th>\n",
       "      <td>2020-05-04</td>\n",
       "      <td>Wayne</td>\n",
       "      <td>Michigan</td>\n",
       "      <td>26163</td>\n",
       "      <td>17314</td>\n",
       "      <td>1924</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>113147</th>\n",
       "      <td>2020-05-04</td>\n",
       "      <td>Cook</td>\n",
       "      <td>Illinois</td>\n",
       "      <td>17031</td>\n",
       "      <td>43715</td>\n",
       "      <td>1802</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114298</th>\n",
       "      <td>2020-05-04</td>\n",
       "      <td>Suffolk</td>\n",
       "      <td>New York</td>\n",
       "      <td>36103</td>\n",
       "      <td>35077</td>\n",
       "      <td>1325</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              date         county     state county_fips   cases  deaths\n",
       "114279  2020-05-04  New York City  New York         NaN  175661   13207\n",
       "114278  2020-05-04         Nassau  New York       36059   36965    2221\n",
       "113810  2020-05-04          Wayne  Michigan       26163   17314    1924\n",
       "113147  2020-05-04           Cook  Illinois       17031   43715    1802\n",
       "114298  2020-05-04        Suffolk  New York       36103   35077    1325"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nyt_counties_latest = (\n",
    "    nyt_counties\n",
    "    .sort_values([ \"date\" ], ascending = False)\n",
    "    .drop_duplicates(subset = [ \"state\", \"county\" ])\n",
    "    .loc[lambda df: df[\"date\"] == df[\"date\"].max()]\n",
    "    .sort_values(\"deaths\", ascending = False)\n",
    ")\n",
    "\n",
    "nyt_counties_latest.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "cases     1180527\n",
       "deaths      63213\n",
       "dtype: int64"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    nyt_counties_latest\n",
    "    [[\n",
    "        \"cases\",\n",
    "        \"deaths\"\n",
    "    ]]\n",
    "    .sum()\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Identify non-county entities in NYT data and move them to intra-CBSA counties\n",
    "\n",
    "See the [\"Geographic Exceptions\" section](https://github.com/nytimes/covid-19-data#geographic-exceptions) of the New York Times' methodology for background."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>date</th>\n",
       "      <th>county</th>\n",
       "      <th>state</th>\n",
       "      <th>county_fips</th>\n",
       "      <th>cases</th>\n",
       "      <th>deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>114279</th>\n",
       "      <td>2020-05-04</td>\n",
       "      <td>New York City</td>\n",
       "      <td>New York</td>\n",
       "      <td>NaN</td>\n",
       "      <td>175661</td>\n",
       "      <td>13207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>114020</th>\n",
       "      <td>2020-05-04</td>\n",
       "      <td>Kansas City</td>\n",
       "      <td>Missouri</td>\n",
       "      <td>NaN</td>\n",
       "      <td>659</td>\n",
       "      <td>16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              date         county     state county_fips   cases  deaths\n",
       "114279  2020-05-04  New York City  New York         NaN  175661   13207\n",
       "114020  2020-05-04    Kansas City  Missouri         NaN     659      16"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(\n",
    "    nyt_counties_latest\n",
    "    .loc[lambda df: df[\"county_fips\"].isnull()]\n",
    "    .loc[lambda df: df[\"county\"] != \"Unknown\"]\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>county_fips</th>\n",
       "      <th>cases</th>\n",
       "      <th>deaths</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>01001</td>\n",
       "      <td>53</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>01003</td>\n",
       "      <td>188</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>01005</td>\n",
       "      <td>45</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>01007</td>\n",
       "      <td>42</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>01009</td>\n",
       "      <td>40</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  county_fips  cases  deaths\n",
       "0       01001     53       3\n",
       "1       01003    188       4\n",
       "2       01005     45       1\n",
       "3       01007     42       0\n",
       "4       01009     40       0"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "county_counts = (\n",
    "    nyt_counties_latest\n",
    "    .assign(\n",
    "        county_fips = lambda df: (\n",
    "            df[\"county\"]\n",
    "            .apply({\n",
    "                \"New York City\": \"36061\", # New York City -> Manhattan\n",
    "                \"Kansas City\": \"29095\", # Kansas City -> Jackson County\n",
    "            }.get)\n",
    "            .fillna(df[\"county_fips\"])\n",
    "        )\n",
    "    )\n",
    "    .groupby([ \"county_fips\" ])\n",
    "    [[\n",
    "        \"cases\",\n",
    "        \"deaths\"\n",
    "    ]]\n",
    "    .sum()\n",
    "    .reset_index()\n",
    ")\n",
    "\n",
    "county_counts.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Aggregated data to the CBSA level, by merging the NYT and Census datasets"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>cbsa_code</th>\n",
       "      <th>cases</th>\n",
       "      <th>deaths</th>\n",
       "      <th>cbsa_title</th>\n",
       "      <th>metro_micro</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10100</td>\n",
       "      <td>65</td>\n",
       "      <td>0</td>\n",
       "      <td>Aberdeen, SD</td>\n",
       "      <td>Micropolitan Statistical Area</td>\n",
       "      <td>42668</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10140</td>\n",
       "      <td>12</td>\n",
       "      <td>0</td>\n",
       "      <td>Aberdeen, WA</td>\n",
       "      <td>Micropolitan Statistical Area</td>\n",
       "      <td>75061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10180</td>\n",
       "      <td>429</td>\n",
       "      <td>5</td>\n",
       "      <td>Abilene, TX</td>\n",
       "      <td>Metropolitan Statistical Area</td>\n",
       "      <td>172060</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10220</td>\n",
       "      <td>10</td>\n",
       "      <td>2</td>\n",
       "      <td>Ada, OK</td>\n",
       "      <td>Micropolitan Statistical Area</td>\n",
       "      <td>38284</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10300</td>\n",
       "      <td>110</td>\n",
       "      <td>2</td>\n",
       "      <td>Adrian, MI</td>\n",
       "      <td>Micropolitan Statistical Area</td>\n",
       "      <td>98451</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  cbsa_code  cases  deaths    cbsa_title                    metro_micro  \\\n",
       "0     10100     65       0  Aberdeen, SD  Micropolitan Statistical Area   \n",
       "1     10140     12       0  Aberdeen, WA  Micropolitan Statistical Area   \n",
       "2     10180    429       5   Abilene, TX  Metropolitan Statistical Area   \n",
       "3     10220     10       2       Ada, OK  Micropolitan Statistical Area   \n",
       "4     10300    110       2    Adrian, MI  Micropolitan Statistical Area   \n",
       "\n",
       "   population  \n",
       "0       42668  \n",
       "1       75061  \n",
       "2      172060  \n",
       "3       38284  \n",
       "4       98451  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cbsa_counts = (\n",
    "    county_counts\n",
    "    .merge(\n",
    "        cbsa_counties\n",
    "        [[\n",
    "            \"cbsa_code\",\n",
    "            \"county_fips\",\n",
    "        ]],\n",
    "        how = \"left\",\n",
    "        validate = \"1:1\"\n",
    "    )\n",
    "    .groupby(\"cbsa_code\")\n",
    "    [[\n",
    "        \"cbsa_code\",\n",
    "        \"cases\",\n",
    "        \"deaths\",\n",
    "    ]]\n",
    "    .sum()\n",
    "    .reset_index()\n",
    "    .merge(\n",
    "        cbsas,\n",
    "        how = \"left\",\n",
    "        on = \"cbsa_code\",\n",
    "        validate = \"1:1\",\n",
    "    )\n",
    ")\n",
    "\n",
    "cbsa_counts.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Identify the 15 largest CBSAs / metro areas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>cbsa_code</th>\n",
       "      <th>cases</th>\n",
       "      <th>deaths</th>\n",
       "      <th>cbsa_title</th>\n",
       "      <th>metro_micro</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>598</th>\n",
       "      <td>35620</td>\n",
       "      <td>402032</td>\n",
       "      <td>25513</td>\n",
       "      <td>New York-Newark-Jersey City, NY-NJ-PA</td>\n",
       "      <td>Metropolitan Statistical Area</td>\n",
       "      <td>19216182</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>491</th>\n",
       "      <td>31080</td>\n",
       "      <td>29076</td>\n",
       "      <td>1313</td>\n",
       "      <td>Los Angeles-Long Beach-Anaheim, CA</td>\n",
       "      <td>Metropolitan Statistical Area</td>\n",
       "      <td>13214799</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>16980</td>\n",
       "      <td>61243</td>\n",
       "      <td>2550</td>\n",
       "      <td>Chicago-Naperville-Elgin, IL-IN-WI</td>\n",
       "      <td>Metropolitan Statistical Area</td>\n",
       "      <td>9458539</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>207</th>\n",
       "      <td>19100</td>\n",
       "      <td>9164</td>\n",
       "      <td>254</td>\n",
       "      <td>Dallas-Fort Worth-Arlington, TX</td>\n",
       "      <td>Metropolitan Statistical Area</td>\n",
       "      <td>7573136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>382</th>\n",
       "      <td>26420</td>\n",
       "      <td>10144</td>\n",
       "      <td>219</td>\n",
       "      <td>Houston-The Woodlands-Sugar Land, TX</td>\n",
       "      <td>Metropolitan Statistical Area</td>\n",
       "      <td>7066141</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>880</th>\n",
       "      <td>47900</td>\n",
       "      <td>31284</td>\n",
       "      <td>1259</td>\n",
       "      <td>Washington-Arlington-Alexandria, DC-VA-MD-WV</td>\n",
       "      <td>Metropolitan Statistical Area</td>\n",
       "      <td>6280487</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>540</th>\n",
       "      <td>33100</td>\n",
       "      <td>21783</td>\n",
       "      <td>782</td>\n",
       "      <td>Miami-Fort Lauderdale-Pompano Beach, FL</td>\n",
       "      <td>Metropolitan Statistical Area</td>\n",
       "      <td>6166488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>654</th>\n",
       "      <td>37980</td>\n",
       "      <td>41021</td>\n",
       "      <td>2211</td>\n",
       "      <td>Philadelphia-Camden-Wilmington, PA-NJ-DE-MD</td>\n",
       "      <td>Metropolitan Statistical Area</td>\n",
       "      <td>6102434</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>12060</td>\n",
       "      <td>14499</td>\n",
       "      <td>560</td>\n",
       "      <td>Atlanta-Sandy Springs-Alpharetta, GA</td>\n",
       "      <td>Metropolitan Statistical Area</td>\n",
       "      <td>6020364</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>655</th>\n",
       "      <td>38060</td>\n",
       "      <td>5219</td>\n",
       "      <td>181</td>\n",
       "      <td>Phoenix-Mesa-Chandler, AZ</td>\n",
       "      <td>Metropolitan Statistical Area</td>\n",
       "      <td>4948203</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>102</th>\n",
       "      <td>14460</td>\n",
       "      <td>52491</td>\n",
       "      <td>3030</td>\n",
       "      <td>Boston-Cambridge-Newton, MA-NH</td>\n",
       "      <td>Metropolitan Statistical Area</td>\n",
       "      <td>4873019</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>745</th>\n",
       "      <td>41860</td>\n",
       "      <td>5960</td>\n",
       "      <td>189</td>\n",
       "      <td>San Francisco-Oakland-Berkeley, CA</td>\n",
       "      <td>Metropolitan Statistical Area</td>\n",
       "      <td>4731803</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>704</th>\n",
       "      <td>40140</td>\n",
       "      <td>6536</td>\n",
       "      <td>280</td>\n",
       "      <td>Riverside-San Bernardino-Ontario, CA</td>\n",
       "      <td>Metropolitan Statistical Area</td>\n",
       "      <td>4650631</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>226</th>\n",
       "      <td>19820</td>\n",
       "      <td>31415</td>\n",
       "      <td>3397</td>\n",
       "      <td>Detroit-Warren-Dearborn, MI</td>\n",
       "      <td>Metropolitan Statistical Area</td>\n",
       "      <td>4319629</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>760</th>\n",
       "      <td>42660</td>\n",
       "      <td>10667</td>\n",
       "      <td>627</td>\n",
       "      <td>Seattle-Tacoma-Bellevue, WA</td>\n",
       "      <td>Metropolitan Statistical Area</td>\n",
       "      <td>3979845</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    cbsa_code   cases  deaths                                    cbsa_title  \\\n",
       "598     35620  402032   25513         New York-Newark-Jersey City, NY-NJ-PA   \n",
       "491     31080   29076    1313            Los Angeles-Long Beach-Anaheim, CA   \n",
       "160     16980   61243    2550            Chicago-Naperville-Elgin, IL-IN-WI   \n",
       "207     19100    9164     254               Dallas-Fort Worth-Arlington, TX   \n",
       "382     26420   10144     219          Houston-The Woodlands-Sugar Land, TX   \n",
       "880     47900   31284    1259  Washington-Arlington-Alexandria, DC-VA-MD-WV   \n",
       "540     33100   21783     782       Miami-Fort Lauderdale-Pompano Beach, FL   \n",
       "654     37980   41021    2211   Philadelphia-Camden-Wilmington, PA-NJ-DE-MD   \n",
       "45      12060   14499     560          Atlanta-Sandy Springs-Alpharetta, GA   \n",
       "655     38060    5219     181                     Phoenix-Mesa-Chandler, AZ   \n",
       "102     14460   52491    3030                Boston-Cambridge-Newton, MA-NH   \n",
       "745     41860    5960     189            San Francisco-Oakland-Berkeley, CA   \n",
       "704     40140    6536     280          Riverside-San Bernardino-Ontario, CA   \n",
       "226     19820   31415    3397                   Detroit-Warren-Dearborn, MI   \n",
       "760     42660   10667     627                   Seattle-Tacoma-Bellevue, WA   \n",
       "\n",
       "                       metro_micro  population  \n",
       "598  Metropolitan Statistical Area    19216182  \n",
       "491  Metropolitan Statistical Area    13214799  \n",
       "160  Metropolitan Statistical Area     9458539  \n",
       "207  Metropolitan Statistical Area     7573136  \n",
       "382  Metropolitan Statistical Area     7066141  \n",
       "880  Metropolitan Statistical Area     6280487  \n",
       "540  Metropolitan Statistical Area     6166488  \n",
       "654  Metropolitan Statistical Area     6102434  \n",
       "45   Metropolitan Statistical Area     6020364  \n",
       "655  Metropolitan Statistical Area     4948203  \n",
       "102  Metropolitan Statistical Area     4873019  \n",
       "745  Metropolitan Statistical Area     4731803  \n",
       "704  Metropolitan Statistical Area     4650631  \n",
       "226  Metropolitan Statistical Area     4319629  \n",
       "760  Metropolitan Statistical Area     3979845  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "largest_metros = (\n",
    "    cbsa_counts\n",
    "    .nlargest(15, \"population\")\n",
    ")\n",
    "\n",
    "largest_metros"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Calculate those CBSAs' total populations, cases, and deaths — and their proportion relative to entire US"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Proportion of US population\n",
    "\n",
    "US total figure [via Census Population Estimates](https://www.census.gov/newsroom/press-releases/2019/popest-nation.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "108601700"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "largest_metros[\"population\"].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.331"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(largest_metros[\"population\"].sum() / 328239523).round(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Proportion of cases and deaths"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "cases     732534\n",
       "deaths     42365\n",
       "dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "largest_metros[[\"cases\", \"deaths\"]].sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "cases     0.621\n",
       "deaths    0.670\n",
       "dtype: float64"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(largest_metros[[ \"cases\", \"deaths\" ]].sum() / nyt_counties_latest[[ \"cases\", \"deaths\" ]].sum()).round(3)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "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.8.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}