Assignment 5

1. Data Wrangling (20 pts)

We will begin with COVID-19 data from Johns Hopkins CSSE (JHU). That dataset includes a breakdown by administrative region (i.e. counties). For the purposes of the assignment, we will use the confirmed cases datasets for the United States last updated for April 15:

https://raw.githubusercontent.com/CSSEGISandData/COVID-19/30ce7eb4b0a6a37b274f12cda350554b95609baa/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_US.csv

import os
from urllib.request import urlretrieve
import pandas as pd

url = 'https://raw.githubusercontent.com/CSSEGISandData/COVID-19/30ce7eb4b0a6a37b274f12cda350554b95609baa/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_US.csv'
local_fname = url[url.rfind('/')+1:]
if not os.path.exists(local_fname):
    urlretrieve(url, local_fname)
df = pd.read_csv('time_series_covid19_confirmed_US.csv')

Our first task is to make the data tidy (longer). Currently, each date is a column, but this is harder to handle so we need to melt these columns, creating two new columns, one for date and one for cases. To use melt, we need to identify all value variables which is a lot of dates. Rather than copying or typing all of these dates, create a date_range from 1/22/20 to 4/15/20, and then convert it to the correct string format. Name the new columns date and cases. Keep at least Province_State and Admin2 as id columns (you may keep more if you want). Finally, convert the date column to true timestamp. A sample output dataframe is below:

2. Spatial Roll-Up (25 pts)

Notice that each state is divided into administrative regions; these include counties but also other counts like those cases contracted out of state or in prisons. We can thus treat these two columns as two levels of a spatial dimension. We can ask queries about counties (the counties with the highest number of reported cases) or states (the states with the lowest number of reported cases).

First, compute the counties with the highest number of reported cases as of April 15, 2020. Recall that this will correlate with population to some degree.

Second, compute the number of cases per state for each day. This is equivalent to rolling up from the county dimension to the state dimension. You should use a groupby that sums the cases across all the counties in each state. This second computation should look something like:

3. 3-Day Rolling Average (30 pts)

Our last task is related to smoothing out the data so it is less subject to changes in reporting for different days (e.g. fewer test results may be processed on the weekends, etc.). To do this, we will compute a three-day rolling average for each county.

Our goal is to have cases organized by state, then county, and finally date. Set a multi-level index to organize the data this way. Next, to compute a rolling average, we need to word around some restrictions in pandas. First, windows are always aligned to the right-most value; if I have data for 4/10, 4/11, 4/12, and 4/13, the rolling average for 4/12 will be the average of the values for 4/10, 4/11, and 4/12 (not 4/11, 4/12, and 4/13). Second, we cannot compute a rolling window by date on a data frame with a multi-index.

To work around the first issue, we can shift the data one day to the past (then, the rolling average above becomes the value for 4/11 instead of 4/12). To work around the second issue, we can apply the rolling window as part of a groupby. Both require creating a data frame where the date is the only index and the state and county are normal columns again. Important: Keep the original data frame around! Then, shift the dates, and apply a groupby (on state and county). Compute the rolling three-day window on the groupby and compute the mean value for that window. Store the new average cases as a new column in the original multi-index dataframe. These will match up because the groupby creates the same index structure.

Finally we will create a couple of line plots. First, create one for DeKalb County that shows the number of cases versus the average number of cases. Next, create one for Cook County but use a log-scale for the y-axis. Note that you will be able to see the curve starting the flatten, showing that cases while still growing, are not doubling at the same rate.

Extra Credit

• Draw a plot that compares Cook County to Los Angeles County
• Create a data frame that shows the new cases reported each week by county.
• Write code to use the latitude and longitude values in the JHU dataset to allow a user to find the counts of all cases within a specific region (e.g. the rectangle with an upper-left corner at (43.236334, -89.652084) and a lower-right corner at (41.258110, -86.746049)).