def ts_complete_states(cls) -> pd.DataFrame:
"""
State-level time series models of confirmed cases, deaths, CC per 100k, deaths per 100k,
confirmed infection rate, death rate.
:return: df_states_ts dataframe
"""
# county-level time series confirmed cases and death dataframe
df_counties_ts = CovidCounties.ts_complete_counties()
df_states_stats = Stats.states_stats()
# group confirmed cases by state - rolling up county level to state level
df_conf_states_ts = df_counties_ts.groupby(
['State/Territory',
'Date'])['Confirmed Cases'].sum().reset_index()
# merge df_states_stats
df_conf_states_ts = pd.merge(df_conf_states_ts,
df_states_stats,
how='left',
on='State/Territory')
# merge state level deaths models
df_deaths_states_ts = df_counties_ts.groupby(
['State/Territory', 'Date'])['Deaths'].sum().reset_index()
df_states_ts = pd.merge(df_conf_states_ts,
df_deaths_states_ts,
how='left',
on=['State/Territory', 'Date'])
# create metrics: pop_factor, cc per 100k, deaths per 100k, death rate, confirmed infection rate
df_states_ts['pop_factor'] = df_states_ts['Population'].div(100000)
df_states_ts['Cases per 100k'] = df_states_ts['Confirmed Cases'].div(
df_states_ts['pop_factor']).replace((np.inf, -np.inf, np.nan),
(0, 0, 0)).round(0)
df_states_ts['Deaths per 100k'] = df_states_ts['Deaths'].div(
df_states_ts['pop_factor']).replace((np.inf, -np.inf, np.nan),
(0, 0, 0)).round(0)
df_states_ts['Death Rate (%)'] = 100 * df_states_ts['Deaths'].div(
df_states_ts['Confirmed Cases']).replace((np.inf, -np.inf, np.nan),
(0, 0, 0)).round(6)
return df_states_ts
def agg_complete_states(cls) -> pd.DataFrame:
"""
Cumulative confirmed cases and deaths by state
:return: dataframe
"""
# import and create base dataframes
df_counties_agg = CovidCounties.agg_complete_counties()
df_states_stats = Stats.states_stats()
# sum confirmed by state
df_confirmed_states_agg = df_counties_agg.groupby(
'State/Territory')['confirmed'].sum().reset_index()
# merge states stats
df_confirmed_states_agg = pd.merge(df_confirmed_states_agg,
df_states_stats,
how='left',
on='State/Territory')
# format population with separating commas
df_confirmed_states_agg['2019 Est Pop'] = df_confirmed_states_agg[
'Population'].fillna(0).astype(int).apply(
lambda x: '{:,}'.format(x))
# create 'Confirmed Cases' column formatted with commas and keep 'confirmed' column as integer
df_confirmed_states_agg['Confirmed Cases'] = df_confirmed_states_agg[
'confirmed'].apply(lambda x: '{:,}'.format(x))
# create dataframe deaths by state
df_deaths_states_agg = df_counties_agg.groupby(
'State/Territory')['deaths'].sum().reset_index()
# merge df_confirmed_states_agg and df_deaths_states_agg
df_states_agg = pd.merge(df_confirmed_states_agg,
df_deaths_states_agg,
on='State/Territory',
how='left')
# create formatted string Deaths column
df_states_agg['Deaths'] = df_states_agg['deaths'].fillna(0).apply(
lambda x: '{:,}'.format(x))
# create population 100k factor to calculate confirmed cases per 100k and deaths per 100k
df_states_agg['pop_factor'] = df_states_agg['Population'].div(
100000).replace((np.inf, -np.inf, np.nan), (0, 0, 0))
# calculate 'Confirmed Cases per 100k' people
df_states_agg['cc_per_100k'] = df_states_agg['confirmed'].div(
df_states_agg['pop_factor']).replace((np.nan, -np.inf, np.inf),
(0, 0, 0)).round(0)
# calculate 'Deaths per 100k' people
df_states_agg['d_per_100k'] = df_states_agg['deaths'].div(
df_states_agg['pop_factor']).replace((np.nan, np.inf, -np.inf),
(0, 0, 0)).round(0)
# create formatted hover models for Plotly US map
df_states_agg['Cases per 100k'] = df_states_agg['cc_per_100k'].apply(
lambda x: '{:,}'.format(x))
df_states_agg['Deaths per 100k'] = df_states_agg['d_per_100k'].apply(
lambda x: '{:,}'.format(x))
df_states_agg['Death Rate (%)'] = round(
100 *
df_states_agg['deaths'].div(df_states_agg['confirmed']).replace(
(np.nan, np.inf, -np.inf), (0, 0, 0)), 4)
remove_list = [
'American Samoa', 'Federated States of Micronesia', 'Palau',
'Guam', 'Virgin Islands', 'Marshall Islands',
'Northern Mariana Islands'
]
df_states_agg = df_states_agg.drop(df_states_agg.index[
df_states_agg['State/Territory'].isin(remove_list)])
return df_states_agg
from dash.dependencies import Input, Output, State
import dash_table
from dash_table.Format import Format
import dash_table.FormatTemplate as FormatTemplate
import plotly.express as px
from models.covid_counties import CovidCounties
from models.stats import Stats
from app import app
# build base dataframes
df_complete_counties_agg = CovidCounties.agg_complete_counties()
df_counties_ts = CovidCounties.ts_complete_counties()
df_state_location = Stats.states_stats()
# ---------------- Section 1: Define functions to generate Dash figures and datatables ------------------ #
def create_us_bubble_map_counties(metric_name):
# remove counties without lat and long
df_counties = df_complete_counties_agg[
(df_complete_counties_agg['Lat'] != 0)
& (df_complete_counties_agg['Lat'] != 0)]
# create figure
fig = px.scatter_mapbox(df_counties,
lat='Lat',
lon='Long_',
color=metric_name,