In [1]:
import sys
sys.path.insert(0,'../lib')
from generallib import *

connection = getConnection()

rollingDays = 2
daysBack = 60
In [2]:
display(md('# COVID-19 Pandemic'))

COVID-19 Pandemic


This is an analysis of the effects of COVID-19. This report is automatically updated each morning.

Data sourced from Johns Hopkins CSSE and is available here.

In [3]:
display(md("Compiled "+datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")+" UTC."))

Compiled 2020-06-01 16:27:01 UTC.

Why Should I Care?

2% case fatality rate may not seem high. Let's get some perspective by really looking at the numbers.

In recent years, the United States has an average annual mortality rate of 0.72%.(1) The average American knows around 600 people.(2) You may learn that someone you know personally has died once every two years or so.

In recent years, the common flu infects and produces symptoms in around 35 million Americans each year, or a little over 10% of the total population.(5) In your average-size circle of acquaintances, then, you may know around 60 people who show flu symptoms each year. The common flu has a mortality rate below 0.1%, so most people do not actually know anyone who has died from flu complications.

Now, let's compare this to our current situation. Mortality rate for COVID-19 has been calculated to be anywhere from 1% to 4%.(3,4) The consensus seems to be that the real mortality rate for COVID-19 will be around 2%. Imagine that COVID-19 spreads at the same rate as the common flu. On average, at least one person you know will die from COVID-19.

The situation may actually be worse, as recent research indicates that there is a high rate of asymptomatic COVID-19 infections, meaning people are walking around with it and have no idea.(4,6) This is what leads to the higher mortality calculations of 3% or 4%, because the deaths are carefully recorded+, while total infections may be under-counted by a large margin. If those estimates are true, two or three people you know may die.

Of course, all this is predicated on COVID-19 spreading as widely as the common flu. By following current guidelines and mandates for social distancing and sheltering in place, these numbers could be reduced.

+ Even this may not be true. Recent data out of Italy shows that many deaths may not have been counted at all,(7) falsely deflating reported mortality rates even further.

Disclaimer

I am not an epidemiologist. I do not work in the medical field. I am a data analyst whose perspective is "numbers are numbers", but as we all know, context is key. Please take this data with a grain of salt. Note that DDP metric below is completely made up by me after thinking through this data for about 10 minutes.

Infections and Deaths Overview

In [4]:
query = f"""
select
    dataDate as Date,
    sum(confirmed) as TotalInfections,
    sum(dead) as TotalDeaths,
    sum(recovered) as TotalRecovered
from
    covidMetrics
where
    dataDate >= current_date - interval {daysBack} day
group by dataDate
order by dataDate
"""
covidtotdf = generateTable(query,connection,columns=['Date','TotalInfections','TotalDeaths','TotalRecovered'])
In [5]:
fig = generateGenericGraphDF('Total Infections, Deaths, and Recovered Worldwide',covidtotdf,['TotalInfections','TotalDeaths','TotalRecovered'],labels=['infections','deaths','recovered'])
show(fig)

Total COVID-19 infections worldwide.

In [6]:
fig = generateGenericGraphDF('Total Deaths Worldwide',covidtotdf,['TotalDeaths'],labels=['deaths'],ylabel='Deaths')
show(fig)

Total deaths caused by COVID-19 worldwide.

World Epicenters

In [7]:
def getCountryQuery(country):
    return f"""
    select
        c1.dataDate,
        sum(c1.dead),
        sum(c1.confirmed),
        sum(c1.recovered),
        sum(c1.dead) - sum(c2.dead),
        sum(c1.confirmed) - sum(c1.recovered) - sum(c2.dead),
        sum(c1.confirmed) - sum(c2.confirmed)
    from
        covidMetrics c1
        inner join covidMetrics c2 on c1.province=c2.province and c1.country=c2.country and c1.dataDate=c2.dataDate + interval 1 day
    where
        c1.country='{country}'
        and c1.dataDate >= current_date - interval {daysBack} day
    group by c1.dataDate
    order by c1.dataDate
    """

covidusdf = generateTable(getCountryQuery('US'),connection,['Date','usdeath','usinf','usrecov','usdailydeath','usactive','usdailyconfirmed'])
covidusdf['usdeathprob'] = covidusdf['usdailydeath'].rolling(rollingDays).mean() / covidusdf['usactive'].rolling(rollingDays).mean()
covidusdf = covidusdf.set_index('Date')
for ctry in ['China','Italy','Spain']:
    temp = generateTable(getCountryQuery(ctry),connection,['Date','death','inf','recov','dailydeath','active','dailyconfirmed'])
    temp = temp.set_index('Date')
    covidusdf[ctry + 'death'] = temp['death']
    covidusdf[ctry + 'inf'] = temp['inf']
    covidusdf[ctry + 'recov'] = temp['recov']
    covidusdf[ctry + 'dailydeath'] = temp['dailydeath']
    covidusdf[ctry + 'active'] = temp['active']
    covidusdf[ctry + 'dailyconfirmed'] = temp['dailyconfirmed']
    covidusdf[ctry + 'deathprob'] = temp['dailydeath'].rolling(rollingDays).mean() / temp['active'].rolling(rollingDays).mean()

# fix some misleading data
# China arbitrarily adjusted Wuhan deaths up on this day
covidusdf.at[datetime.datetime.strptime('2020-04-17','%Y-%m-%d').date(),'Chinadeathprob'] = 0
covidusdf.at[datetime.datetime.strptime('2020-04-18','%Y-%m-%d').date(),'Chinadeathprob'] = 0
covidusdf.at[datetime.datetime.strptime('2020-04-19','%Y-%m-%d').date(),'Chinadeathprob'] = 0
covidusdf.at[datetime.datetime.strptime('2020-04-20','%Y-%m-%d').date(),'Chinadeathprob'] = 0
covidusdf.at[datetime.datetime.strptime('2020-04-21','%Y-%m-%d').date(),'Chinadeathprob'] = 0
covidusdf.at[datetime.datetime.strptime('2020-04-22','%Y-%m-%d').date(),'Chinadeathprob'] = 0
covidusdf.at[datetime.datetime.strptime('2020-04-23','%Y-%m-%d').date(),'Chinadeathprob'] = 0
# Spain had massive reduction in infections on this day
covidusdf.at[datetime.datetime.strptime('2020-04-24','%Y-%m-%d').date(),'Spaindailyconfirmed'] = 0
In [8]:
fig = generateGenericGraphDF('Infections in Epicenters',covidusdf,['usinf','Italyinf','Spaininf'],labels=['US','Italy','Spain'],ylabel='Infections')
show(fig)

COVID-19 infections reported in epicenters.

In [9]:
fig = generateGenericGraphDF('Daily New Infections in Epicenters',covidusdf,['usdailyconfirmed','Italydailyconfirmed','Spaindailyconfirmed'],labels=['US','Italy','Spain'],ylabel='Infections',rolling=7)
show(fig)

Daily new infections reported in epicenters, and 7-day rolling average.

Of note is that infections in Spain started a rebound on April 15, 3 days after the Spanish government announced a loosening of restrictions.

In [10]:
fig = generateGenericGraphDF('Deaths in Epicenters',covidusdf,['usdeath','Italydeath','Spaindeath'],labels=['US','Italy','Spain'],ylabel='Deaths')
show(fig)

COVID-19 deaths reported in epicenters.

In [11]:
fig = generateGenericGraphDF('Daily New Deaths in Epicenters',covidusdf,['usdailydeath','Italydailydeath','Spaindailydeath'],labels=['US','Italy','Spain'],ylabel='Deaths',rolling=7)
show(fig)

Daily new deaths reported in epicenters, and 7-day rolling average.

Note that spike in deaths in China on April 17 was due to a retrospective adjustment to Wuhan, Hubei death count.

Relationship Between Infections and Deaths

Data above show that deaths rise some time after infections. This makes sense, as it takes time for someone to become sick enough that they succumb to the disease. But how long does this take? If we observe a spike in infections, how long before we see a similar spike in deaths?

In [12]:
cordf = pd.read_csv('covidCorrelation.csv')
cordf = cordf.set_index('days')

fig = generateGenericGraphDF('Expected Days Between Infection and Death',cordf,['R2'],labels=['Correlation'],ylabel='Correlation',xType='auto',xCol='days')
ba = BoxAnnotation(left=11.5,right=14.5,fill_color='red',fill_alpha=0.1)
fig.add_layout(ba)
show(fig)

Expected number of days that pass between an increase in infections and proportionate change in deaths, by country. Based on data from January 22 to April 7.

When a country experiences an increase in infections, it is most likely that a proprtionate increase in deaths will occur 12-14 days later. It is this correlation that allows experts to predict when they think "peak deaths" will occur, though these predictions are made using different mathematical methods.

This was calculated by determining the correlation (R2) of the linear regression for each dataset where percentage increase in infections was plotted with percentage increase in deaths X days later, where X was 1 to 24 days. Only time periods that began with a country having at least 50 deaths were considered. The correlation between percent increase in infections and percent increase in deaths 12 days later was highest.

Daily Death Probability

Actively infected total is calculated by subtracting total previous deaths and total recovered from total infections. Daily Death Probability (DDP) is the ratio between deaths on a particular day and that total. This metric essentially answers the question, "For all people who were still infected as of that day, what was the probability that one selected at random would die on that day?" and may indicate quality of healthcare. Note that I am not an epidemiologist.

In [13]:
fig = generateGenericGraphDF('Daily Death Probability (DDP) in Epicenters',covidusdf,['usdeathprob','Chinadeathprob','Italydeathprob','Spaindeathprob'],labels=['US','China','Italy','Spain'],ylabel='Probability')
show(fig)

Probability that any actively infected person will die on a given day on a 2-day rolling average.

DDP smooths over time as both numbers of actively infected and deaths increase. Of note:

  • China's DDP excluded for 4/17 and 4/18, due to arbitrary increase in death count.
  • Italy's and Spain's DDPs started declining in late March. This coincides with their transition from exponential to linear growth in deaths and indicated "turning the corner" in those countries.
In [14]:
def getStateTopQuery(state):
    return f"""
    select
        city,
        ifnull(confirmed,0),
        ifnull(dead,0)
    from
        covidMetricsUs
    where
        state='{state}'
        and dataDate=(select max(dataDate) from covidMetricsUs)
        and (confirmed > 0 or dead > 0)
    order by confirmed desc
    """

def getStateQuery(state):
    return f"""
    select
        c1.dataDate,
        sum(c1.confirmed),
        sum(c1.dead),
        sum(c1.confirmed) - sum(c2.dead),
        sum(c1.dead) - sum(c2.dead)
    from
        covidMetricsUs c1
        inner join covidMetricsUs c2 on c1.state=c2.state and c1.city=c2.city and c1.dataDate=c2.dataDate + interval 1 day
    where
        c1.dataDate >= current_date - interval {daysBack} day
        and c1.state='{state}'
    group by c1.dataDate
    order by c1.dataDate
    """

statesdf = generateTable(getStateQuery('Washington'),connection,['Date','Washingtonconfirmed','Washingtondead','Washingtonactive','Washingtondailydead'])
statesdf = statesdf.set_index('Date')
statesdf['Washingtonddp'] = statesdf['Washingtondailydead'].rolling(rollingDays).mean() / statesdf['Washingtonactive'].rolling(rollingDays).mean()
for state in ['Florida','Georgia','Alaska','New York','California']:
    temp = generateTable(getStateQuery(state),connection,['Date','confirmed','dead','active','dailydead'])
    temp = temp.set_index('Date')
    state = state.replace(' ','_')
    statesdf[state + 'confirmed'] = temp['confirmed']
    statesdf[state + 'dead'] = temp['dead']
    statesdf[state + 'active'] = temp['active']
    statesdf[state + 'dailydead'] = temp['dailydead']
    statesdf[state + 'ddp'] = temp['dailydead'].rolling(rollingDays).mean() / temp['active'].rolling(rollingDays).mean()

By State

California

In [15]:
fig = generateGenericGraphDF('Infections in California',statesdf,['Californiaconfirmed'],labels=['infections in CA'],ylabel='Infections')
fig2 = generateGenericGraphDF('Deaths in California',statesdf,['Californiadead'],labels=['deaths in CA'],ylabel='Deaths')
fig.plot_width=300
fig2.plot_width=300
show(row(fig,fig2))

COVID-19 infections and deaths reported in California.

In [16]:
temp = generateTable(getStateTopQuery('California'),connection,['County','Infections','Deaths'])
temp = temp.set_index('County')
temp.loc['TOTAL'] = temp.sum(axis=0)
temp = temp.sort_values('Infections',axis=0,ascending=False)
display(temp.iloc[0:11])
Infections Deaths
County
TOTAL 111951 4172
Los Angeles 55001 2362
Riverside 7486 323
San Diego 7481 269
Orange 6261 147
San Bernardino 5246 204
Alameda 3390 96
Santa Clara 2776 141
San Francisco 2588 42
Kern 2250 38
San Mateo 2104 84

Top ten locations in California for infections.

New York

In [17]:
fig = generateGenericGraphDF('Infections in New York',statesdf,['New_Yorkconfirmed'],labels=['infections in NY'],ylabel='Infections')
fig2 = generateGenericGraphDF('Deaths in New York',statesdf,['New_Yorkdead'],labels=['deaths in NY'],ylabel='Deaths')
fig.plot_width=300
fig2.plot_width=300
show(row(fig,fig2))

COVID-19 infections and deaths reported in New York.

In [18]:
temp = generateTable(getStateTopQuery('New York'),connection,['County','Infections','Deaths'])
temp = temp.set_index('County')
temp.loc['TOTAL'] = temp.sum(axis=0)
temp = temp.sort_values('Infections',axis=0,ascending=False)
display(temp.iloc[0:11])
Infections Deaths
County
TOTAL 370770 29784
New York 203303 21569
Nassau 40396 2122
Suffolk 39643 1901
Westchester 33481 1371
Rockland 13151 631
Orange 10406 444
Erie 6070 516
Dutchess 3909 139
Monroe 2942 216
Onondaga 2170 131

Top ten locations in New York for infections.

Washington

In [19]:
fig = generateGenericGraphDF('Infections in Washington',statesdf,['Washingtonconfirmed'],labels=['infections in WA'],ylabel='Infections')
fig2 = generateGenericGraphDF('Deaths in Washington',statesdf,['Washingtondead'],labels=['deaths in WA'],ylabel='Deaths')
fig.plot_width=300
fig2.plot_width=300
show(row(fig,fig2))

COVID-19 infections and deaths reported in Washington.

Because Washington was the first state in the US hit by a major outbreak, it is worth watching how the pandemic there plays out.

In [20]:
temp = generateTable(getStateTopQuery('Washington'),connection,['County','Infections','Deaths'])
temp = temp.set_index('County')
temp.loc['TOTAL'] = temp.sum(axis=0)
temp = temp.sort_values('Infections',axis=0,ascending=False)
display(temp.iloc[0:11])
Infections Deaths
County
TOTAL 21702 1118
King 8092 567
Yakima 3585 95
Snohomish 2967 148
Pierce 1937 79
Benton 794 62
Spokane 596 32
Franklin 574 20
Clark 514 22
Skagit 434 15
Whatcom 396 36

Top ten locations in Washington for infections.

Florida

In [21]:
fig = generateGenericGraphDF('Infections in Florida',statesdf,['Floridaconfirmed'],labels=['infections in FL'],ylabel='Infections')
fig2 = generateGenericGraphDF('Deaths in Florida',statesdf,['Floridadead'],labels=['deaths in FL'],ylabel='Deaths')
fig.plot_width=300
fig2.plot_width=300
show(row(fig,fig2))

COVID-19 infections and deaths reported in Florida.

In [22]:
temp = generateTable(getStateTopQuery('Florida'),connection,['County','Infections','Deaths'])
temp = temp.set_index('County')
temp.loc['TOTAL'] = temp.sum(axis=0)
temp = temp.sort_values('Infections',axis=0,ascending=False)
display(temp.iloc[0:11])
Infections Deaths
County
TOTAL 56163 2451
Miami-Dade 18000 700
Broward 7123 313
Palm Beach 5996 337
Hillsborough 2201 81
Orange 2002 41
Lee 1908 105
Duval 1644 50
Collier 1539 49
Pinellas 1297 82
Manatee 1045 97

Top ten locations in Florida for infections.

Georgia

In [23]:
fig = generateGenericGraphDF('Infections in Georgia',statesdf,['Georgiaconfirmed'],labels=['infections in GA'],ylabel='Infections')
fig2 = generateGenericGraphDF('Deaths in Georgia',statesdf,['Georgiadead'],labels=['deaths in GA'],ylabel='Deaths')
fig.plot_width=300
fig2.plot_width=300
show(row(fig,fig2))

COVID-19 infections and deaths reported in Georgia.

In [24]:
temp = generateTable(getStateTopQuery('Georgia'),connection,['County','Infections','Deaths'])
temp = temp.set_index('County')
temp.loc['TOTAL'] = temp.sum(axis=0)
temp = temp.sort_values('Infections',axis=0,ascending=False)
display(temp.iloc[0:11])
Infections Deaths
County
TOTAL 47063 2053
Fulton 4524 235
Gwinnett 3780 128
DeKalb 3734 120
Cobb 3027 175
Hall 2467 47
Out of GA 2012 34
Dougherty 1770 148
Unassigned 1698 1
Clayton 1221 49
Cherokee 909 33

Top ten locations in Georgia for infections.

Alaska

In [25]:
fig = generateGenericGraphDF('Infections in Alaska',statesdf,['Alaskaconfirmed'],labels=['infections in AK'],ylabel='Infections')
fig2 = generateGenericGraphDF('Deaths in Alaska',statesdf,['Alaskadead'],labels=['deaths in AK'],ylabel='Deaths')
fig.plot_width=300
fig2.plot_width=300
show(row(fig,fig2))

COVID-19 infections and deaths reported in Alaska.

In [26]:
temp = generateTable(getStateTopQuery('Alaska'),connection,['City','Infections','Deaths'])
temp = temp.set_index('City')
temp.loc['TOTAL'] = temp.sum(axis=0)
temp = temp.sort_values('Infections',axis=0,ascending=False)
display(temp.iloc[0:11])
Infections Deaths
City
TOTAL 459 10
Anchorage 229 4
Fairbanks North Star 85 2
Kenai Peninsula 45 2
Juneau 33 0
Matanuska-Susitna 29 1
Ketchikan Gateway 16 0
Petersburg 4 1
Bethel 3 0
Southeast Fairbanks 3 0
Nome 3 0

Top ten locations in Alaska for infections.

COVID-19 Event Timeline

Below is a timeline of events related to COVID-19 in virus epicenters.

In [27]:
dates = [
    '2020-01-21',
    '2020-01-29',
    '2020-01-31',
    '2020-02-02',
    '2020-02-04',
    '2020-02-06',
    '2020-02-24',
    '2020-02-26',
    '2020-02-29',
    '2020-03-04',
    '2020-03-06',
    '2020-03-08',
    '2020-03-11',
    '2020-03-13',
    '2020-03-16',
    '2020-03-16',
    '2020-03-25',
    '2020-03-29',
    '2020-04-12',
    '2020-04-15',
    '2020-04-24',
    '2020-04-26',
]
names = [
    '1st US infection',
    'WH forms task force',
    'US travel restrictions',
    '1st non-Chinese death',
    'Diamond Princess',
    'Actual first',
    'Dow drops 1000',
    '1st untraceable US case',
    '1st death in US',
    '10 dead in WA',
    '100k worldwide',
    '500 US cases',
    'Pandemic',
    'Nat\'l emerg.',
    'NYSE halted',
    'Advisory',
    'Stimulus',
    'Extension',
    'Spain loosens',
    'Stimulus payments',
    'GA loosens',
    'CO loosens',
]
descriptions = [
    'First COVID-19 infection in the US',
    'White House announces a dedicated task force',
    'Travel restrictions for those entering the US who have recently traveled in China',
    'First death of a COVID-19 victim outside of China',
    'Diamond Princess quarantine reported by media',
    'Actual first death occurred in US, as reported April 22',
    'Dow Jones sheds 1000 points, beginning a five-day correction',
    'First case in the US that could not be traced to an origin',
    'First death of a COVID-19 victim in the US',
    'Four more dead in Washington state, bringing total to ten in that state',
    'Worldwide infections pass 100,000 mark',
    'Over 500 infections in the US',
    'WHO officially declares COVID-19 a pandemic',
    'President Trump declares national emergency',
    'NYSE temporarily halted after 2,725 point drop',
    'DHS issues "no unnecessary travel" advisory',
    'Congress agrees on $2 trillion stimulus bill',
    'Trump extends distancing guidelines through April 30',
    'Spain begins loosening restrictions',
    'Stimulus bill payments start going out',
    'Georgia to allow gyms, barbers, etc. to open',
    'Colorado to lift stay-at-home mandate, though still asking citizens to stay home',
]
timelinedf = pd.DataFrame()
timelinedf['Date'] = dates
timelinedf['Event'] = names
timelinedf['Description'] = descriptions
timelinedf = timelinedf.set_index('Date')
fig,axis = getTimeline("COVID-19 Event Timeline",dates,names,interval=5)
display(fig)

Details on most recent events:

In [28]:
timelinedf = timelinedf.reset_index()
def prettyDateFormat(val):
    valDate = datetime.datetime.strptime(val,'%Y-%m-%d')
    return valDate.strftime('%b %d')

timelinedf['Date'] = timelinedf.apply(lambda x: prettyDateFormat(x['Date']), axis=1)
pd.set_option('max_colwidth',100)
display(timelinedf[-10:].style.hide_index())
pd.reset_option('max_colwidth')
Date Event Description
Mar 11 Pandemic WHO officially declares COVID-19 a pandemic
Mar 13 Nat'l emerg. President Trump declares national emergency
Mar 16 NYSE halted NYSE temporarily halted after 2,725 point drop
Mar 16 Advisory DHS issues "no unnecessary travel" advisory
Mar 25 Stimulus Congress agrees on $2 trillion stimulus bill
Mar 29 Extension Trump extends distancing guidelines through April 30
Apr 12 Spain loosens Spain begins loosening restrictions
Apr 15 Stimulus payments Stimulus bill payments start going out
Apr 24 GA loosens Georgia to allow gyms, barbers, etc. to open
Apr 26 CO loosens Colorado to lift stay-at-home mandate, though still asking citizens to stay home

Why These States?

I have provided more detailed data for states that matter to my audience (mostly close friends and family). If you would like to see other states, just shoot me an email (me@lucasoman.com).

References

  1. "Mortality in the United States, 2018", CDC. (https://www.cdc.gov/nchs/products/databriefs/db355.htm)
  2. "The Average American Knows How Many People?", NY Times. (https://www.nytimes.com/2013/02/19/science/the-average-american-knows-how-many-people.html)
  3. "The WHO Estimated COVID-19 Mortality at 3.4%. That Doesn't Tell the Whole Story", Time. (https://time.com/5798168/coronavirus-mortality-rate/)
  4. "Coronavirus disease 2019 (COVID-19) Situation Report – 46", WHO. (https://www.who.int/docs/default-source/coronaviruse/situation-reports/20200306-sitrep-46-covid-19.pdf?sfvrsn=96b04adf_2)
  5. "Disease Burden of Influenza", CDC. (https://www.cdc.gov/flu/about/burden/index.html)
  6. "CDC Director On Models For The Months To Come: 'This Virus Is Going To Be With Us'", NPR. (https://www.npr.org/sections/health-shots/2020/03/31/824155179/cdc-director-on-models-for-the-months-to-come-this-virus-is-going-to-be-with-us)
  7. "Italy's Coronavirus Death Toll Is Far Higher Than Reported", MSN. (https://www.msn.com/en-us/news/world/italys-coronavirus-death-toll-is-far-higher-than-reported/ar-BB122vvc)