text_plain_output_1.png image_output_1.png
from datetime import datetime, timedelta

import numpy as np

import pandas as pd

latest_date = datetime.today() - timedelta(days=1)
latest_date = latest_date.strftime('%m/%d/%y')[1:]
df_cases = pd.read_csv('https://usafactsstatic.blob.core.windows.net/public/data/covid-19/covid_confirmed_usafacts.csv')[['countyFIPS', 'County Name', 'State', latest_date]]
df_cases = df_cases.rename(columns={'countyFIPS': 'county_fips', latest_date: 'confirmed'}).set_index('county_fips')
df_deaths = pd.read_csv('https://usafactsstatic.blob.core.windows.net/public/data/covid-19/covid_deaths_usafacts.csv')[['countyFIPS', latest_date]]
df_deaths = df_deaths.rename(columns={'countyFIPS': 'county_fips', latest_date: 'deaths'}).set_index('county_fips')
df_pop = pd.read_csv('https://usafactsstatic.blob.core.windows.net/public/data/covid-19/covid_county_population_usafacts.csv')[['countyFIPS', 'population']]
df_pop = df_pop.rename(columns={'countyFIPS': 'county_fips'}).set_index('county_fips')
df = df_cases.join(df_deaths)
df = df.join(df_pop)
df = df[df.index > 999]
df = df[df.population > 0]
del df_cases, df_deaths, df_pop
df['mortality'] = df['deaths'] / df['confirmed']
df['mortality'] = df['mortality'].fillna(0)
df['deaths_per_million'] = df['deaths'] * 1000000 / df['population']
df['cases_per_million'] = df['confirmed'] * 1000000 / df['population']
df['likely_infected_high'] = np.round(df['confirmed'] * 80 / df['population'], 2)
df['likely_infected_high'] = np.clip(df['likely_infected_high'], 0, 1)
df['likely_infected_low'] = np.round(df['confirmed'] * 28 / df['population'], 2)
df['likely_infected_low'] = np.clip(df['likely_infected_low'], 0, 1)
df['county_state'] = df['County Name'] + ', ' + df['State']
print('Number of counties: ' + str(df.index.nunique()))