1_get_data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Weekly Counts of Deaths by Jurisdiction and Age\n",
    "\n",
    "* Download the CDC data from https://data.cdc.gov/NCHS/Weekly-Counts-of-Deaths-by-Jurisdiction-and-Age/y5bj-9g5w\n",
    "* Download the NYT COVID-19 Deaths from https://github.com/nytimes/covid-19-data\n",
    "* Download population data from https://www2.census.gov/programs-surveys/popest/datasets/2010-2020/state/asrh/\n",
    "* Merge all into `data/us_excess_deaths.rds`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-04-21T22:18:27.761910Z",
     "start_time": "2022-04-21T22:18:19.893Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "── \u001b[1mAttaching packages\u001b[22m ─────────────────────────────────────── tidyverse 1.3.1 ──\n",
      "\n",
      "\u001b[32m✔\u001b[39m \u001b[34mggplot2\u001b[39m 3.3.5     \u001b[32m✔\u001b[39m \u001b[34mpurrr  \u001b[39m 0.3.4\n",
      "\u001b[32m✔\u001b[39m \u001b[34mtibble \u001b[39m 3.1.6     \u001b[32m✔\u001b[39m \u001b[34mdplyr  \u001b[39m 1.0.8\n",
      "\u001b[32m✔\u001b[39m \u001b[34mtidyr  \u001b[39m 1.2.0     \u001b[32m✔\u001b[39m \u001b[34mstringr\u001b[39m 1.4.0\n",
      "\u001b[32m✔\u001b[39m \u001b[34mreadr  \u001b[39m 2.1.2     \u001b[32m✔\u001b[39m \u001b[34mforcats\u001b[39m 0.5.1\n",
      "\n",
      "── \u001b[1mConflicts\u001b[22m ────────────────────────────────────────── tidyverse_conflicts() ──\n",
      "\u001b[31m✖\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mfilter()\u001b[39m masks \u001b[34mstats\u001b[39m::filter()\n",
      "\u001b[31m✖\u001b[39m \u001b[34mdplyr\u001b[39m::\u001b[32mlag()\u001b[39m    masks \u001b[34mstats\u001b[39m::lag()\n",
      "\n"
     ]
    }
   ],
   "source": [
    "library(tidyverse)\n",
    "\n",
    "options(readr.show_col_types = F)\n",
    "\n",
    "download.file(\"https://data.cdc.gov/api/views/y5bj-9g5w/rows.csv?accessType=DOWNLOAD\",\n",
    "             destfile = \"data/Weekly_Counts_of_Deaths_by_Jurisdiction_and_Age.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Make one age bin of all age groups <65"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-04-21T22:18:30.297540Z",
     "start_time": "2022-04-21T22:18:19.897Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[1m\u001b[22m`summarise()` has grouped output by 'Jurisdiction', 'Year', 'Week'. You can\n",
      "override using the `.groups` argument.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A grouped_df: 6 × 5</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>Jurisdiction</th><th scope=col>Year</th><th scope=col>Week</th><th scope=col>Age Group</th><th scope=col>Number of Deaths</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Alabama</td><td>2015</td><td>1</td><td><span style=white-space:pre-wrap>&lt;65  </span></td><td>345</td></tr>\n",
       "\t<tr><td>Alabama</td><td>2015</td><td>1</td><td>65-74</td><td>202</td></tr>\n",
       "\t<tr><td>Alabama</td><td>2015</td><td>1</td><td>75-84</td><td>272</td></tr>\n",
       "\t<tr><td>Alabama</td><td>2015</td><td>1</td><td>85+  </td><td>320</td></tr>\n",
       "\t<tr><td>Alabama</td><td>2015</td><td>2</td><td><span style=white-space:pre-wrap>&lt;65  </span></td><td>333</td></tr>\n",
       "\t<tr><td>Alabama</td><td>2015</td><td>2</td><td>65-74</td><td>222</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A grouped\\_df: 6 × 5\n",
       "\\begin{tabular}{lllll}\n",
       " Jurisdiction & Year & Week & Age Group & Number of Deaths\\\\\n",
       " <chr> & <int> & <int> & <chr> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Alabama & 2015 & 1 & <65   & 345\\\\\n",
       "\t Alabama & 2015 & 1 & 65-74 & 202\\\\\n",
       "\t Alabama & 2015 & 1 & 75-84 & 272\\\\\n",
       "\t Alabama & 2015 & 1 & 85+   & 320\\\\\n",
       "\t Alabama & 2015 & 2 & <65   & 333\\\\\n",
       "\t Alabama & 2015 & 2 & 65-74 & 222\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A grouped_df: 6 × 5\n",
       "\n",
       "| Jurisdiction &lt;chr&gt; | Year &lt;int&gt; | Week &lt;int&gt; | Age Group &lt;chr&gt; | Number of Deaths &lt;dbl&gt; |\n",
       "|---|---|---|---|---|\n",
       "| Alabama | 2015 | 1 | &lt;65   | 345 |\n",
       "| Alabama | 2015 | 1 | 65-74 | 202 |\n",
       "| Alabama | 2015 | 1 | 75-84 | 272 |\n",
       "| Alabama | 2015 | 1 | 85+   | 320 |\n",
       "| Alabama | 2015 | 2 | &lt;65   | 333 |\n",
       "| Alabama | 2015 | 2 | 65-74 | 222 |\n",
       "\n"
      ],
      "text/plain": [
       "  Jurisdiction Year Week Age Group Number of Deaths\n",
       "1 Alabama      2015 1    <65       345             \n",
       "2 Alabama      2015 1    65-74     202             \n",
       "3 Alabama      2015 1    75-84     272             \n",
       "4 Alabama      2015 1    85+       320             \n",
       "5 Alabama      2015 2    <65       333             \n",
       "6 Alabama      2015 2    65-74     222             "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df <- read_csv('data/Weekly_Counts_of_Deaths_by_Jurisdiction_and_Age.csv',\n",
    "              col_types = \"ccciicicccc\")%>%\n",
    "  filter(Type != 'Predicted (weighted)')%>%\n",
    "  select(Jurisdiction, Year, Week, `Number of Deaths`, `Age Group`)%>%\n",
    "  mutate (age_lower = parse_number(gsub(\"Under 25 years\", \"0\", `Age Group`)))%>%\n",
    "  mutate(`Age Group` = case_when(age_lower<65 ~ \"<65\",\n",
    "                                 age_lower<75 ~ \"65-74\",\n",
    "                                 age_lower<85 ~ \"75-84\",\n",
    "                                            T ~ \"85+\"))%>%\n",
    "  group_by(Jurisdiction, Year, Week, `Age Group`)%>%\n",
    "  summarise(`Number of Deaths` = sum(ifelse(is.na(`Number of Deaths`), 0, `Number of Deaths`)))\n",
    "\n",
    "head(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Merge 'New York' and 'New York City'\n",
    "In the CDC data 'New York' means 'New York *excluding* New York City'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-04-21T22:18:31.841882Z",
     "start_time": "2022-04-21T22:18:19.900Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[1m\u001b[22m`summarise()` has grouped output by 'Jurisdiction', 'Year', 'Age Group'. You\n",
      "can override using the `.groups` argument.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A grouped_df: 6 × 5</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>Jurisdiction</th><th scope=col>Year</th><th scope=col>Age Group</th><th scope=col>Week</th><th scope=col>Number of Deaths</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Alabama</td><td>2015</td><td>&lt;65</td><td>1</td><td>345</td></tr>\n",
       "\t<tr><td>Alabama</td><td>2015</td><td>&lt;65</td><td>2</td><td>333</td></tr>\n",
       "\t<tr><td>Alabama</td><td>2015</td><td>&lt;65</td><td>3</td><td>303</td></tr>\n",
       "\t<tr><td>Alabama</td><td>2015</td><td>&lt;65</td><td>4</td><td>294</td></tr>\n",
       "\t<tr><td>Alabama</td><td>2015</td><td>&lt;65</td><td>5</td><td>316</td></tr>\n",
       "\t<tr><td>Alabama</td><td>2015</td><td>&lt;65</td><td>6</td><td>291</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A grouped\\_df: 6 × 5\n",
       "\\begin{tabular}{lllll}\n",
       " Jurisdiction & Year & Age Group & Week & Number of Deaths\\\\\n",
       " <chr> & <int> & <chr> & <int> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Alabama & 2015 & <65 & 1 & 345\\\\\n",
       "\t Alabama & 2015 & <65 & 2 & 333\\\\\n",
       "\t Alabama & 2015 & <65 & 3 & 303\\\\\n",
       "\t Alabama & 2015 & <65 & 4 & 294\\\\\n",
       "\t Alabama & 2015 & <65 & 5 & 316\\\\\n",
       "\t Alabama & 2015 & <65 & 6 & 291\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A grouped_df: 6 × 5\n",
       "\n",
       "| Jurisdiction &lt;chr&gt; | Year &lt;int&gt; | Age Group &lt;chr&gt; | Week &lt;int&gt; | Number of Deaths &lt;dbl&gt; |\n",
       "|---|---|---|---|---|\n",
       "| Alabama | 2015 | &lt;65 | 1 | 345 |\n",
       "| Alabama | 2015 | &lt;65 | 2 | 333 |\n",
       "| Alabama | 2015 | &lt;65 | 3 | 303 |\n",
       "| Alabama | 2015 | &lt;65 | 4 | 294 |\n",
       "| Alabama | 2015 | &lt;65 | 5 | 316 |\n",
       "| Alabama | 2015 | &lt;65 | 6 | 291 |\n",
       "\n"
      ],
      "text/plain": [
       "  Jurisdiction Year Age Group Week Number of Deaths\n",
       "1 Alabama      2015 <65       1    345             \n",
       "2 Alabama      2015 <65       2    333             \n",
       "3 Alabama      2015 <65       3    303             \n",
       "4 Alabama      2015 <65       4    294             \n",
       "5 Alabama      2015 <65       5    316             \n",
       "6 Alabama      2015 <65       6    291             "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df2 <-\n",
    "  df%>%\n",
    "  mutate(Jurisdiction = gsub(\"New York City\", \"New York\", Jurisdiction))%>%\n",
    "  group_by(Jurisdiction, Year, `Age Group`, Week)%>%\n",
    "  summarise(`Number of Deaths` = sum(`Number of Deaths`))\n",
    "\n",
    "write_csv(df2, \"data/tidy_deaths_age_state_CDC.csv\")\n",
    "\n",
    "head(df2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Download population data\n",
    "See https://www2.census.gov/programs-surveys/popest/technical-documentation/file-layouts/2010-2020/sc-est2020-agesex-civ.pdf\n",
    "for a description"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-04-21T22:18:32.911866Z",
     "start_time": "2022-04-21T22:18:19.902Z"
    }
   },
   "outputs": [],
   "source": [
    "download.file(\"https://www2.census.gov/programs-surveys/popest/datasets/2010-2020/state/asrh/SC-EST2020-AGESEX-CIV.csv\",\n",
    "             destfile = \"data/Population.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-04-21T22:18:33.017081Z",
     "start_time": "2022-04-21T22:18:19.905Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 6 × 19</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>SUMLEV</th><th scope=col>REGION</th><th scope=col>DIVISION</th><th scope=col>STATE</th><th scope=col>NAME</th><th scope=col>SEX</th><th scope=col>AGE</th><th scope=col>ESTBASE2010_CIV</th><th scope=col>POPEST2010_CIV</th><th scope=col>POPEST2011_CIV</th><th scope=col>POPEST2012_CIV</th><th scope=col>POPEST2013_CIV</th><th scope=col>POPEST2014_CIV</th><th scope=col>POPEST2015_CIV</th><th scope=col>POPEST2016_CIV</th><th scope=col>POPEST2017_CIV</th><th scope=col>POPEST2018_CIV</th><th scope=col>POPEST2019_CIV</th><th scope=col>POPEST2020_CIV</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>010</td><td>0</td><td>0</td><td>0</td><td>United States</td><td>0</td><td>0</td><td>3944160</td><td>3951495</td><td>3963264</td><td>3926731</td><td>3931411</td><td>3954973</td><td>3984144</td><td>3963268</td><td>3882437</td><td>3826908</td><td>3762227</td><td>3735010</td></tr>\n",
       "\t<tr><td>010</td><td>0</td><td>0</td><td>0</td><td>United States</td><td>0</td><td>1</td><td>3978090</td><td>3957904</td><td>3966768</td><td>3978210</td><td>3943348</td><td>3949559</td><td>3973828</td><td>4003586</td><td>3981864</td><td>3897917</td><td>3842257</td><td>3773884</td></tr>\n",
       "\t<tr><td>010</td><td>0</td><td>0</td><td>0</td><td>United States</td><td>0</td><td>2</td><td>4096939</td><td>4090799</td><td>3971498</td><td>3980139</td><td>3993047</td><td>3960015</td><td>3967672</td><td>3992657</td><td>4021261</td><td>3996742</td><td>3911822</td><td>3853025</td></tr>\n",
       "\t<tr><td>010</td><td>0</td><td>0</td><td>0</td><td>United States</td><td>0</td><td>3</td><td>4119051</td><td>4111869</td><td>4102429</td><td>3983007</td><td>3992839</td><td>4007852</td><td>3976277</td><td>3984985</td><td>4009060</td><td>4035053</td><td>4009037</td><td>3921526</td></tr>\n",
       "\t<tr><td>010</td><td>0</td><td>0</td><td>0</td><td>United States</td><td>0</td><td>4</td><td>4063186</td><td>4077511</td><td>4122252</td><td>4112849</td><td>3994539</td><td>4006407</td><td>4022785</td><td>3992241</td><td>4000394</td><td>4021907</td><td>4045996</td><td>4017847</td></tr>\n",
       "\t<tr><td>010</td><td>0</td><td>0</td><td>0</td><td>United States</td><td>0</td><td>5</td><td>4056872</td><td>4064653</td><td>4087770</td><td>4132349</td><td>4123745</td><td>4007123</td><td>4020489</td><td>4038022</td><td>4007233</td><td>4012789</td><td>4032231</td><td>4054336</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 6 × 19\n",
       "\\begin{tabular}{lllllllllllllllllll}\n",
       " SUMLEV & REGION & DIVISION & STATE & NAME & SEX & AGE & ESTBASE2010\\_CIV & POPEST2010\\_CIV & POPEST2011\\_CIV & POPEST2012\\_CIV & POPEST2013\\_CIV & POPEST2014\\_CIV & POPEST2015\\_CIV & POPEST2016\\_CIV & POPEST2017\\_CIV & POPEST2018\\_CIV & POPEST2019\\_CIV & POPEST2020\\_CIV\\\\\n",
       " <chr> & <dbl> & <dbl> & <dbl> & <chr> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl> & <dbl>\\\\\n",
       "\\hline\n",
       "\t 010 & 0 & 0 & 0 & United States & 0 & 0 & 3944160 & 3951495 & 3963264 & 3926731 & 3931411 & 3954973 & 3984144 & 3963268 & 3882437 & 3826908 & 3762227 & 3735010\\\\\n",
       "\t 010 & 0 & 0 & 0 & United States & 0 & 1 & 3978090 & 3957904 & 3966768 & 3978210 & 3943348 & 3949559 & 3973828 & 4003586 & 3981864 & 3897917 & 3842257 & 3773884\\\\\n",
       "\t 010 & 0 & 0 & 0 & United States & 0 & 2 & 4096939 & 4090799 & 3971498 & 3980139 & 3993047 & 3960015 & 3967672 & 3992657 & 4021261 & 3996742 & 3911822 & 3853025\\\\\n",
       "\t 010 & 0 & 0 & 0 & United States & 0 & 3 & 4119051 & 4111869 & 4102429 & 3983007 & 3992839 & 4007852 & 3976277 & 3984985 & 4009060 & 4035053 & 4009037 & 3921526\\\\\n",
       "\t 010 & 0 & 0 & 0 & United States & 0 & 4 & 4063186 & 4077511 & 4122252 & 4112849 & 3994539 & 4006407 & 4022785 & 3992241 & 4000394 & 4021907 & 4045996 & 4017847\\\\\n",
       "\t 010 & 0 & 0 & 0 & United States & 0 & 5 & 4056872 & 4064653 & 4087770 & 4132349 & 4123745 & 4007123 & 4020489 & 4038022 & 4007233 & 4012789 & 4032231 & 4054336\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 6 × 19\n",
       "\n",
       "| SUMLEV &lt;chr&gt; | REGION &lt;dbl&gt; | DIVISION &lt;dbl&gt; | STATE &lt;dbl&gt; | NAME &lt;chr&gt; | SEX &lt;dbl&gt; | AGE &lt;dbl&gt; | ESTBASE2010_CIV &lt;dbl&gt; | POPEST2010_CIV &lt;dbl&gt; | POPEST2011_CIV &lt;dbl&gt; | POPEST2012_CIV &lt;dbl&gt; | POPEST2013_CIV &lt;dbl&gt; | POPEST2014_CIV &lt;dbl&gt; | POPEST2015_CIV &lt;dbl&gt; | POPEST2016_CIV &lt;dbl&gt; | POPEST2017_CIV &lt;dbl&gt; | POPEST2018_CIV &lt;dbl&gt; | POPEST2019_CIV &lt;dbl&gt; | POPEST2020_CIV &lt;dbl&gt; |\n",
       "|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|\n",
       "| 010 | 0 | 0 | 0 | United States | 0 | 0 | 3944160 | 3951495 | 3963264 | 3926731 | 3931411 | 3954973 | 3984144 | 3963268 | 3882437 | 3826908 | 3762227 | 3735010 |\n",
       "| 010 | 0 | 0 | 0 | United States | 0 | 1 | 3978090 | 3957904 | 3966768 | 3978210 | 3943348 | 3949559 | 3973828 | 4003586 | 3981864 | 3897917 | 3842257 | 3773884 |\n",
       "| 010 | 0 | 0 | 0 | United States | 0 | 2 | 4096939 | 4090799 | 3971498 | 3980139 | 3993047 | 3960015 | 3967672 | 3992657 | 4021261 | 3996742 | 3911822 | 3853025 |\n",
       "| 010 | 0 | 0 | 0 | United States | 0 | 3 | 4119051 | 4111869 | 4102429 | 3983007 | 3992839 | 4007852 | 3976277 | 3984985 | 4009060 | 4035053 | 4009037 | 3921526 |\n",
       "| 010 | 0 | 0 | 0 | United States | 0 | 4 | 4063186 | 4077511 | 4122252 | 4112849 | 3994539 | 4006407 | 4022785 | 3992241 | 4000394 | 4021907 | 4045996 | 4017847 |\n",
       "| 010 | 0 | 0 | 0 | United States | 0 | 5 | 4056872 | 4064653 | 4087770 | 4132349 | 4123745 | 4007123 | 4020489 | 4038022 | 4007233 | 4012789 | 4032231 | 4054336 |\n",
       "\n"
      ],
      "text/plain": [
       "  SUMLEV REGION DIVISION STATE NAME          SEX AGE ESTBASE2010_CIV\n",
       "1 010    0      0        0     United States 0   0   3944160        \n",
       "2 010    0      0        0     United States 0   1   3978090        \n",
       "3 010    0      0        0     United States 0   2   4096939        \n",
       "4 010    0      0        0     United States 0   3   4119051        \n",
       "5 010    0      0        0     United States 0   4   4063186        \n",
       "6 010    0      0        0     United States 0   5   4056872        \n",
       "  POPEST2010_CIV POPEST2011_CIV POPEST2012_CIV POPEST2013_CIV POPEST2014_CIV\n",
       "1 3951495        3963264        3926731        3931411        3954973       \n",
       "2 3957904        3966768        3978210        3943348        3949559       \n",
       "3 4090799        3971498        3980139        3993047        3960015       \n",
       "4 4111869        4102429        3983007        3992839        4007852       \n",
       "5 4077511        4122252        4112849        3994539        4006407       \n",
       "6 4064653        4087770        4132349        4123745        4007123       \n",
       "  POPEST2015_CIV POPEST2016_CIV POPEST2017_CIV POPEST2018_CIV POPEST2019_CIV\n",
       "1 3984144        3963268        3882437        3826908        3762227       \n",
       "2 3973828        4003586        3981864        3897917        3842257       \n",
       "3 3967672        3992657        4021261        3996742        3911822       \n",
       "4 3976277        3984985        4009060        4035053        4009037       \n",
       "5 4022785        3992241        4000394        4021907        4045996       \n",
       "6 4020489        4038022        4007233        4012789        4032231       \n",
       "  POPEST2020_CIV\n",
       "1 3735010       \n",
       "2 3773884       \n",
       "3 3853025       \n",
       "4 3921526       \n",
       "5 4017847       \n",
       "6 4054336       "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df_pop <- read_csv('data/Population.csv')\n",
    "head(df_pop)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-04-21T23:14:25.757565Z",
     "start_time": "2022-04-21T23:14:25.468Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[1m\u001b[22m`summarise()` has grouped output by 'NAME'. You can override using the\n",
      "`.groups` argument.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A grouped_df: 6 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>Jurisdiction</th><th scope=col>Age Group</th><th scope=col>Population</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Alabama</td><td><span style=white-space:pre-wrap>&lt;65  </span></td><td>4033195</td></tr>\n",
       "\t<tr><td>Alabama</td><td>65-74</td><td> 517717</td></tr>\n",
       "\t<tr><td>Alabama</td><td>75-84</td><td> 263668</td></tr>\n",
       "\t<tr><td>Alabama</td><td>85+  </td><td>  92859</td></tr>\n",
       "\t<tr><td>Alaska </td><td><span style=white-space:pre-wrap>&lt;65  </span></td><td> 615848</td></tr>\n",
       "\t<tr><td>Alaska </td><td>65-74</td><td>  64158</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A grouped\\_df: 6 × 3\n",
       "\\begin{tabular}{lll}\n",
       " Jurisdiction & Age Group & Population\\\\\n",
       " <chr> & <chr> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Alabama & <65   & 4033195\\\\\n",
       "\t Alabama & 65-74 &  517717\\\\\n",
       "\t Alabama & 75-84 &  263668\\\\\n",
       "\t Alabama & 85+   &   92859\\\\\n",
       "\t Alaska  & <65   &  615848\\\\\n",
       "\t Alaska  & 65-74 &   64158\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A grouped_df: 6 × 3\n",
       "\n",
       "| Jurisdiction &lt;chr&gt; | Age Group &lt;chr&gt; | Population &lt;dbl&gt; |\n",
       "|---|---|---|\n",
       "| Alabama | &lt;65   | 4033195 |\n",
       "| Alabama | 65-74 |  517717 |\n",
       "| Alabama | 75-84 |  263668 |\n",
       "| Alabama | 85+   |   92859 |\n",
       "| Alaska  | &lt;65   |  615848 |\n",
       "| Alaska  | 65-74 |   64158 |\n",
       "\n"
      ],
      "text/plain": [
       "  Jurisdiction Age Group Population\n",
       "1 Alabama      <65       4033195   \n",
       "2 Alabama      65-74      517717   \n",
       "3 Alabama      75-84      263668   \n",
       "4 Alabama      85+         92859   \n",
       "5 Alaska       <65        615848   \n",
       "6 Alaska       65-74       64158   "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df_pop2 <-  \n",
    "  df_pop%>%\n",
    "  filter(SEX==0 & AGE != 999)%>%\n",
    "  mutate(`Age Group` = case_when(AGE<65 ~ \"<65\",\n",
    "                                 AGE<75 ~ \"65-74\",\n",
    "                                 AGE<85 ~ \"75-84\",\n",
    "                                      T ~ \"85+\"))%>%\n",
    "  group_by(NAME, `Age Group`)%>%\n",
    "  summarise(Population = sum(POPEST2020_CIV))%>%\n",
    "  rename(Jurisdiction = NAME)\n",
    "\n",
    "write_csv(df_pop2, \"data/tidy_pop_age_state.csv\")\n",
    "\n",
    "head(df_pop2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Excess Deaths Associated with COVID-19\n",
    "Download the CDC data from https://www.cdc.gov/nchs/nvss/vsrr/covid19/excess_deaths.htm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-04-21T22:18:35.291760Z",
     "start_time": "2022-04-21T22:18:19.909Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A grouped_df: 6 × 5</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>Jurisdiction</th><th scope=col>Year</th><th scope=col>Week</th><th scope=col>Age Group</th><th scope=col>Number of Deaths</th></tr>\n",
       "\t<tr><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;int&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>Alabama</td><td>2015</td><td>1</td><td><span style=white-space:pre-wrap>&lt;65  </span></td><td>345</td></tr>\n",
       "\t<tr><td>Alabama</td><td>2015</td><td>1</td><td>65-74</td><td>202</td></tr>\n",
       "\t<tr><td>Alabama</td><td>2015</td><td>1</td><td>75-84</td><td>272</td></tr>\n",
       "\t<tr><td>Alabama</td><td>2015</td><td>1</td><td>85+  </td><td>320</td></tr>\n",
       "\t<tr><td>Alabama</td><td>2015</td><td>2</td><td><span style=white-space:pre-wrap>&lt;65  </span></td><td>333</td></tr>\n",
       "\t<tr><td>Alabama</td><td>2015</td><td>2</td><td>65-74</td><td>222</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A grouped\\_df: 6 × 5\n",
       "\\begin{tabular}{lllll}\n",
       " Jurisdiction & Year & Week & Age Group & Number of Deaths\\\\\n",
       " <chr> & <int> & <int> & <chr> & <dbl>\\\\\n",
       "\\hline\n",
       "\t Alabama & 2015 & 1 & <65   & 345\\\\\n",
       "\t Alabama & 2015 & 1 & 65-74 & 202\\\\\n",
       "\t Alabama & 2015 & 1 & 75-84 & 272\\\\\n",
       "\t Alabama & 2015 & 1 & 85+   & 320\\\\\n",
       "\t Alabama & 2015 & 2 & <65   & 333\\\\\n",
       "\t Alabama & 2015 & 2 & 65-74 & 222\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A grouped_df: 6 × 5\n",
       "\n",
       "| Jurisdiction &lt;chr&gt; | Year &lt;int&gt; | Week &lt;int&gt; | Age Group &lt;chr&gt; | Number of Deaths &lt;dbl&gt; |\n",
       "|---|---|---|---|---|\n",
       "| Alabama | 2015 | 1 | &lt;65   | 345 |\n",
       "| Alabama | 2015 | 1 | 65-74 | 202 |\n",
       "| Alabama | 2015 | 1 | 75-84 | 272 |\n",
       "| Alabama | 2015 | 1 | 85+   | 320 |\n",
       "| Alabama | 2015 | 2 | &lt;65   | 333 |\n",
       "| Alabama | 2015 | 2 | 65-74 | 222 |\n",
       "\n"
      ],
      "text/plain": [
       "  Jurisdiction Year Week Age Group Number of Deaths\n",
       "1 Alabama      2015 1    <65       345             \n",
       "2 Alabama      2015 1    65-74     202             \n",
       "3 Alabama      2015 1    75-84     272             \n",
       "4 Alabama      2015 1    85+       320             \n",
       "5 Alabama      2015 2    <65       333             \n",
       "6 Alabama      2015 2    65-74     222             "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "download.file(\"https://data.cdc.gov/api/views/xkkf-xrst/rows.csv?accessType=DOWNLOAD\",\n",
    "             destfile = \"data/Excess_Deaths_Associated_with_COVID-19.csv\")\n",
    "head(df)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-04-21T23:22:39.165040Z",
     "start_time": "2022-04-21T23:22:38.638Z"
    }
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "\u001b[1m\u001b[22m`summarise()` has grouped output by 'Week Ending Date'. You can override using\n",
      "the `.groups` argument.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A grouped_df: 6 × 4</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>Week Ending Date</th><th scope=col>State</th><th scope=col>Average Expected Count</th><th scope=col>Observed Number</th></tr>\n",
       "\t<tr><th scope=col>&lt;date&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>2017-01-07</td><td>Alabama   </td><td>1034</td><td>1121</td></tr>\n",
       "\t<tr><td>2017-01-07</td><td>Alaska    </td><td>  79</td><td> 104</td></tr>\n",
       "\t<tr><td>2017-01-07</td><td>Arizona   </td><td>1118</td><td>1170</td></tr>\n",
       "\t<tr><td>2017-01-07</td><td>Arkansas  </td><td> 635</td><td> 651</td></tr>\n",
       "\t<tr><td>2017-01-07</td><td>California</td><td>5197</td><td>6061</td></tr>\n",
       "\t<tr><td>2017-01-07</td><td>Colorado  </td><td> 756</td><td> 793</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A grouped\\_df: 6 × 4\n",
       "\\begin{tabular}{llll}\n",
       " Week Ending Date & State & Average Expected Count & Observed Number\\\\\n",
       " <date> & <chr> & <dbl> & <dbl>\\\\\n",
       "\\hline\n",
       "\t 2017-01-07 & Alabama    & 1034 & 1121\\\\\n",
       "\t 2017-01-07 & Alaska     &   79 &  104\\\\\n",
       "\t 2017-01-07 & Arizona    & 1118 & 1170\\\\\n",
       "\t 2017-01-07 & Arkansas   &  635 &  651\\\\\n",
       "\t 2017-01-07 & California & 5197 & 6061\\\\\n",
       "\t 2017-01-07 & Colorado   &  756 &  793\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A grouped_df: 6 × 4\n",
       "\n",
       "| Week Ending Date &lt;date&gt; | State &lt;chr&gt; | Average Expected Count &lt;dbl&gt; | Observed Number &lt;dbl&gt; |\n",
       "|---|---|---|---|\n",
       "| 2017-01-07 | Alabama    | 1034 | 1121 |\n",
       "| 2017-01-07 | Alaska     |   79 |  104 |\n",
       "| 2017-01-07 | Arizona    | 1118 | 1170 |\n",
       "| 2017-01-07 | Arkansas   |  635 |  651 |\n",
       "| 2017-01-07 | California | 5197 | 6061 |\n",
       "| 2017-01-07 | Colorado   |  756 |  793 |\n",
       "\n"
      ],
      "text/plain": [
       "  Week Ending Date State      Average Expected Count Observed Number\n",
       "1 2017-01-07       Alabama    1034                   1121           \n",
       "2 2017-01-07       Alaska       79                    104           \n",
       "3 2017-01-07       Arizona    1118                   1170           \n",
       "4 2017-01-07       Arkansas    635                    651           \n",
       "5 2017-01-07       California 5197                   6061           \n",
       "6 2017-01-07       Colorado    756                    793           "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df <- \n",
    "  read_csv(\"data/Excess_Deaths_Associated_with_COVID-19.csv\")%>%\n",
    "  filter(Type==\"Unweighted\")%>%\n",
    "  select(State, `Week Ending Date`, `Average Expected Count`, `Observed Number`)%>%\n",
    "  mutate(State = gsub(\"New York City\", \"New York\", State))%>%\n",
    "  group_by(`Week Ending Date`, State)%>%\n",
    "  summarise(`Average Expected Count` = sum(`Average Expected Count`),\n",
    "           `Observed Number` = sum(`Observed Number`))\n",
    "\n",
    "write_csv(df, \"data/tidy_deaths_state_CDC.csv\")\n",
    "\n",
    "head(df)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Download COVID-19 deaths from the New York Times"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-04-21T23:22:43.697438Z",
     "start_time": "2022-04-21T23:22:42.108Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A tibble: 6 × 3</caption>\n",
       "<thead>\n",
       "\t<tr><th scope=col>Week Ending Date</th><th scope=col>State</th><th scope=col>Covid Deaths</th></tr>\n",
       "\t<tr><th scope=col>&lt;date&gt;</th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><td>2022-04-05</td><td>United States</td><td>566.5714</td></tr>\n",
       "\t<tr><td>2022-04-06</td><td>United States</td><td>560.7143</td></tr>\n",
       "\t<tr><td>2022-04-07</td><td>United States</td><td>559.1429</td></tr>\n",
       "\t<tr><td>2022-04-08</td><td>United States</td><td>530.9048</td></tr>\n",
       "\t<tr><td>2022-04-09</td><td>United States</td><td>516.4762</td></tr>\n",
       "\t<tr><td>2022-04-10</td><td>United States</td><td>488.0476</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A tibble: 6 × 3\n",
       "\\begin{tabular}{lll}\n",
       " Week Ending Date & State & Covid Deaths\\\\\n",
       " <date> & <chr> & <dbl>\\\\\n",
       "\\hline\n",
       "\t 2022-04-05 & United States & 566.5714\\\\\n",
       "\t 2022-04-06 & United States & 560.7143\\\\\n",
       "\t 2022-04-07 & United States & 559.1429\\\\\n",
       "\t 2022-04-08 & United States & 530.9048\\\\\n",
       "\t 2022-04-09 & United States & 516.4762\\\\\n",
       "\t 2022-04-10 & United States & 488.0476\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A tibble: 6 × 3\n",
       "\n",
       "| Week Ending Date &lt;date&gt; | State &lt;chr&gt; | Covid Deaths &lt;dbl&gt; |\n",
       "|---|---|---|\n",
       "| 2022-04-05 | United States | 566.5714 |\n",
       "| 2022-04-06 | United States | 560.7143 |\n",
       "| 2022-04-07 | United States | 559.1429 |\n",
       "| 2022-04-08 | United States | 530.9048 |\n",
       "| 2022-04-09 | United States | 516.4762 |\n",
       "| 2022-04-10 | United States | 488.0476 |\n",
       "\n"
      ],
      "text/plain": [
       "  Week Ending Date State         Covid Deaths\n",
       "1 2022-04-05       United States 566.5714    \n",
       "2 2022-04-06       United States 560.7143    \n",
       "3 2022-04-07       United States 559.1429    \n",
       "4 2022-04-08       United States 530.9048    \n",
       "5 2022-04-09       United States 516.4762    \n",
       "6 2022-04-10       United States 488.0476    "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df_covid <- \n",
    "  read_csv(\"https://github.com/nytimes/covid-19-data/raw/master/us-states.csv\")%>%\n",
    "  group_by(state)%>%\n",
    "  arrange(date)%>%\n",
    "  mutate(deaths = deaths-lag(deaths, 1, default = 0))%>%\n",
    "  mutate(`Covid Deaths` = zoo::rollmean(deaths, k=21, na.pad = T))%>%\n",
    "  rename(`Week Ending Date` = date,\n",
    "         State = state)%>%\n",
    "  select(`Week Ending Date`, State, `Covid Deaths`)%>%\n",
    "  na.omit()%>%\n",
    "  ungroup()\n",
    "\n",
    "# Sum up all states for the 'United States' estimate\n",
    "df_covid <- \n",
    "rbind.data.frame(df_covid,\n",
    "  df_covid%>%\n",
    "    group_by(`Week Ending Date`)%>%\n",
    "    summarise(`Covid Deaths` = sum(`Covid Deaths`))%>%\n",
    "    ungroup()%>%\n",
    "    mutate(State = \"United States\")%>%\n",
    "    select(`Week Ending Date`, `State`, `Covid Deaths`)\n",
    "  )\n",
    "tail(df_covid)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2022-04-21T23:22:48.460353Z",
     "start_time": "2022-04-21T23:22:44.108Z"
    }
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table class=\"dataframe\">\n",
       "<caption>A data.frame: 6 × 6</caption>\n",
       "<thead>\n",
       "\t<tr><th></th><th scope=col>State</th><th scope=col>Week Ending Date</th><th scope=col>Average Expected Count</th><th scope=col>Observed Number</th><th scope=col>Covid Deaths</th><th scope=col>Population</th></tr>\n",
       "\t<tr><th></th><th scope=col>&lt;chr&gt;</th><th scope=col>&lt;date&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th><th scope=col>&lt;dbl&gt;</th></tr>\n",
       "</thead>\n",
       "<tbody>\n",
       "\t<tr><th scope=row>1</th><td>Alabama</td><td>2019-06-01</td><td>999</td><td>1027</td><td>NA</td><td>4907439</td></tr>\n",
       "\t<tr><th scope=row>2</th><td>Alabama</td><td>2019-06-08</td><td>993</td><td>1029</td><td>NA</td><td>4907439</td></tr>\n",
       "\t<tr><th scope=row>3</th><td>Alabama</td><td>2019-06-29</td><td>982</td><td> 940</td><td>NA</td><td>4907439</td></tr>\n",
       "\t<tr><th scope=row>4</th><td>Alabama</td><td>2019-07-06</td><td>984</td><td>1028</td><td>NA</td><td>4907439</td></tr>\n",
       "\t<tr><th scope=row>5</th><td>Alabama</td><td>2019-06-15</td><td>988</td><td> 986</td><td>NA</td><td>4907439</td></tr>\n",
       "\t<tr><th scope=row>6</th><td>Alabama</td><td>2019-06-22</td><td>982</td><td> 990</td><td>NA</td><td>4907439</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "A data.frame: 6 × 6\n",
       "\\begin{tabular}{r|llllll}\n",
       "  & State & Week Ending Date & Average Expected Count & Observed Number & Covid Deaths & Population\\\\\n",
       "  & <chr> & <date> & <dbl> & <dbl> & <dbl> & <dbl>\\\\\n",
       "\\hline\n",
       "\t1 & Alabama & 2019-06-01 & 999 & 1027 & NA & 4907439\\\\\n",
       "\t2 & Alabama & 2019-06-08 & 993 & 1029 & NA & 4907439\\\\\n",
       "\t3 & Alabama & 2019-06-29 & 982 &  940 & NA & 4907439\\\\\n",
       "\t4 & Alabama & 2019-07-06 & 984 & 1028 & NA & 4907439\\\\\n",
       "\t5 & Alabama & 2019-06-15 & 988 &  986 & NA & 4907439\\\\\n",
       "\t6 & Alabama & 2019-06-22 & 982 &  990 & NA & 4907439\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/markdown": [
       "\n",
       "A data.frame: 6 × 6\n",
       "\n",
       "| <!--/--> | State &lt;chr&gt; | Week Ending Date &lt;date&gt; | Average Expected Count &lt;dbl&gt; | Observed Number &lt;dbl&gt; | Covid Deaths &lt;dbl&gt; | Population &lt;dbl&gt; |\n",
       "|---|---|---|---|---|---|---|\n",
       "| 1 | Alabama | 2019-06-01 | 999 | 1027 | NA | 4907439 |\n",
       "| 2 | Alabama | 2019-06-08 | 993 | 1029 | NA | 4907439 |\n",
       "| 3 | Alabama | 2019-06-29 | 982 |  940 | NA | 4907439 |\n",
       "| 4 | Alabama | 2019-07-06 | 984 | 1028 | NA | 4907439 |\n",
       "| 5 | Alabama | 2019-06-15 | 988 |  986 | NA | 4907439 |\n",
       "| 6 | Alabama | 2019-06-22 | 982 |  990 | NA | 4907439 |\n",
       "\n"
      ],
      "text/plain": [
       "  State   Week Ending Date Average Expected Count Observed Number Covid Deaths\n",
       "1 Alabama 2019-06-01       999                    1027            NA          \n",
       "2 Alabama 2019-06-08       993                    1029            NA          \n",
       "3 Alabama 2019-06-29       982                     940            NA          \n",
       "4 Alabama 2019-07-06       984                    1028            NA          \n",
       "5 Alabama 2019-06-15       988                     986            NA          \n",
       "6 Alabama 2019-06-22       982                     990            NA          \n",
       "  Population\n",
       "1 4907439   \n",
       "2 4907439   \n",
       "3 4907439   \n",
       "4 4907439   \n",
       "5 4907439   \n",
       "6 4907439   "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "df2 <-\n",
    "  df%>%\n",
    "  select(State, `Week Ending Date`, `Average Expected Count`, `Observed Number`)%>%\n",
    "  merge(df_covid, all = T)%>%\n",
    "  drop_na(`Observed Number`)%>%\n",
    "  merge(\n",
    "    read_csv(\"data/tidy_pop_age_state.csv\", show_col_types = FALSE)%>%\n",
    "      group_by(Jurisdiction)%>%\n",
    "      summarise(Population = sum(Population))%>%\n",
    "      rename(State = Jurisdiction)\n",
    "  )\n",
    "\n",
    "write_rds(df2, \"data/us_excess_deaths.rds\")\n",
    "\n",
    "head(df2)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "R",
   "language": "R",
   "name": "ir"
  },
  "language_info": {
   "codemirror_mode": "r",
   "file_extension": ".r",
   "mimetype": "text/x-r-source",
   "name": "R",
   "pygments_lexer": "r",
   "version": "4.1.3"
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}