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']

df_county_stats = pd.read_csv('/kaggle/input/uncover/county_health_rankings/county_health_rankings/us-county-health-rankings-2020.csv')[['fips', 'segregation_index', 'percent_black', 'median_household_income', 'percent_adults_with_obesity', 'percent_smokers', 'percent_with_access_to_exercise_opportunities', 'percent_some_college', 'percent_unemployed', 'percent_children_in_poverty']]
df_county_stats = df_county_stats.rename(columns={'fips': 'county_fips', 'segregation_index': 'segregation_level'}).set_index('county_fips')
df = df.join(df_county_stats)
df_county_stats = pd.read_csv('/kaggle/input/county-ranking-data/county_ranking.csv')[['fipscode', 'v052_rawvalue', 'v053_rawvalue', 'v044_rawvalue', 'v147_rawvalue', 'v002_cilow', 'v136_other_data_2']]
df_county_stats = df_county_stats.rename(columns={'fipscode': 'county_fips', 'v052_rawvalue': 'percent_below_18', 'v053_rawvalue': 'percent_above_65', 'v044_rawvalue': 'income_inequality', 'v147_rawvalue': 'life_expectancy', 'v002_cilow': 'poor_fair_health', 'v136_other_data_2': 'over_crowding'}).set_index('county_fips')
df = df.join(df_county_stats)
df = df.reset_index()
df['county_fips'] = df['county_fips'].astype(str).str.rjust(5, '0')
df.head()