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

connection = getConnection()

pd.set_option('max_colwidth',100)
In [2]:
# display(md('![Verticals](verticals.png)'))
display(md('<center><img src="verticals.png" width="300px"><center>'))

In [3]:
display(md('# COVID-19 Data Anomalies'))

COVID-19 Data Anomalies


In [4]:
display(md('These are data anomalies identified by Verticals, my automated analysis system.'))
display(md("Compiled "+datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")+" UTC."))

These are data anomalies identified by Verticals, my automated analysis system.

Compiled 2020-11-27 12:25:04 UTC.


In this report:

In [5]:
query = """
select
    id,
    name,
    description,
    up,
    down
from
    verticals.metric
where
    interval_type='daily'
    and active=1
"""
metricdf = generateTable(query,connection,['id','name','description','up','down'])
metricdf = metricdf.set_index('id')

query = f"""
select
    ma.*
from
    verticals.metric_analysis ma
    inner join verticals.metric_category_map mcm on ma.metric_id=mcm.metric_id
where
    ma.analyzed >= current_date() -- - interval 1 day -- if testing later in day (UTC issues)
    and mcm.category_id=1
order by if(ma.e = 0,10,abs((ma.projected - ma.value) / ma.e)) desc
"""
df = generateTable(query,connection,['ts','t','i','a','b','e','v','p'])

mdString = ''
rowsExist = False
for index,row in df.iterrows():
    meta = metricdf.loc[row['i']]
    mdString += '- [' + meta['name'] + '](#' + meta['name'].replace(' ','-') + ')' + "\n"
    rowsExist = True
    
if not rowsExist:
    mdString += "There were no anomalies yesterday."
    
display(md(mdString))

In [6]:
colors = {'GOOD':['#587834'],'BAD':['#d13525']}

def getMetricQuery(id):
    return f"""
    select
        interval_id,
        verticals.getDatetimeFromIntervalId(interval_id) as dt,
        value
    from
        verticals.metric_data
    where
        interval_id >= verticals.getIntervalIdFromDatetime(current_date - interval 30 day)
        and metric_id={id}
    order by interval_id
    """

def getJournalQuery(country):
    return f"""
    select
        date(verticals.getDatetimeFromIntervalId(interval_id)) as theDate,
        group_concat(title separator "<br>")
    from
        verticals.journal j
        inner join verticals.journal_category_map jcm on j.id = jcm.journal_id
        left join verticals.journal_label_map jlm on j.id = jlm.journal_id
    where
        jcm.category_id = 1
        and j.interval_id >= verticals.getIntervalIdFromDatetime(current_date - interval 14 day)
        and (
                (
                jlm.label is null
                and binary j.title like '%{country}%'
                )
            or jlm.label = '{country}'
            )
    group by theDate
    order by theDate
    """


today = datetime.date.today()
today = datetime.datetime(today.year,today.month,today.day)
windowStart = today - datetime.timedelta(days=1,hours=12)
windowEnd = today - datetime.timedelta(hours=12)
    
for index,row in df.iterrows():
    meta = metricdf.loc[row['i']]
    mdf = generateTable(getMetricQuery(row['i']),connection,['interval_id','dt','value'])
    display(md('# ' + meta['name']))
    
    # figure out the color
    if (row['v'] > row['p']):
        whichColor = meta['up']
    else:
        whichColor = meta['down']
        
    fig = generateGenericGraphDF(meta['name'],mdf,['value'],xCol='dt',labels=['value'])
    ba = BoxAnnotation(left=windowStart,right=windowEnd,fill_color=colors[whichColor][0],fill_alpha=0.2)
    fig.add_layout(ba)
    
    country = meta['name'].replace(' Dead','').replace(' Confirmed','')
    journal = generateTable(getJournalQuery(country),connection,['Date','Title'])
    journalY = float(mdf.min()['value']) - (float(mdf.max()['value'] - mdf.min()['value']) * 0.1)
    jds = ColumnDataSource(journal)
    which = fig.diamond(x='Date',y=journalY,source=jds,size=12)
    fig.add_tools(HoverTool(renderers=[which],tooltips=[('Events','@Title{safe}')],mode='vline'))
    
    show(fig)
    display(md('*' + meta['description'] + '*'))
    display(md('Projected value for yesterday was ' + str(round(row['p'])) + ' but actual was ' + str(round(row['v']))))
    
    if len(journal.index) > 0:
        display(md('## ' + country + ' Event Journal'))
        #fig,axis = getTimeline("Event Timeline",journal['Date'].apply(datetime.datetime.strftime,args=('%Y-%m-%d',)),journal['Title'].apply(lambda x,l : x[:l],args=(int(1000 / len(journal.index)),)),interval=5)
        #display(fig)
        display(journal.style.hide_index())

Turkey Confirmed

Confirmed daily COVID-19 cases in Turkey

Projected value for yesterday was 5960 but actual was 36008

Turkey Event Journal

Date Title
2020-11-23 Coronavirus pandemic hits a new peak in Turkey

Ohio Confirmed

Confirmed daily COVID-19 cases in Ohio

Projected value for yesterday was 10107 but actual was 0

Ohio Event Journal

Date Title
2020-11-17 Ohio issues statewide curfew starting Thursday

Oregon Confirmed

Confirmed daily COVID-19 cases in Oregon

Projected value for yesterday was 1342 but actual was 0

Oregon Event Journal

Date Title
2020-11-16 Oregon reports 868 new cases as 2-week "social freeze" is set to begin Wednesday

Minnesota Confirmed

Confirmed daily COVID-19 cases in Minnesota

Projected value for yesterday was 7892 but actual was 0

Minnesota Event Journal

Date Title
2020-11-19 Minnesota landmarks to light up purple in honor of Covid victims and frontline workers

New Hampshire Confirmed

Confirmed daily COVID-19 cases in New Hampshire

Projected value for yesterday was 493 but actual was 0

New Hampshire Event Journal

Date Title
2020-11-19 New Hampshire issues statewide mask mandate

Massachusetts Confirmed

Confirmed daily COVID-19 cases in Massachusetts

Projected value for yesterday was 3086 but actual was 0

Massachusetts Event Journal

Date Title
2020-11-13 More than 10,000 people have now died from Covid-19 in Massachusetts
Massachusetts governor says "innocent acts of small gatherings" driving spread of Covid-19
Field hospital to reopen at Massachusetts arena as Covid-19 cases "trending in the wrong direction"

Florida Confirmed

Confirmed daily COVID-19 cases in Florida

Projected value for yesterday was 8521 but actual was 0

Florida Event Journal

Date Title
2020-11-16 Here are the latest Covid-19 numbers from Florida
2020-11-20 Florida Sen. Rick Scott tests positive for coronavirus
2020-11-23 Florida's new cases per week have tripled since Gov. DeSantis reopened the state
Here's how Florida plans to distribute a Covid-19 vaccine 
2020-11-24 Florida health system is preparing to distribute Pfizer vaccine
Florida reports more than 8,000 new Covid-19 cases
2020-11-25 Florida governor says his goal is to provide Covid-19 vaccines to all long-term care facilities residents 

South Carolina Confirmed

Confirmed daily COVID-19 cases in South Carolina

Projected value for yesterday was 1722 but actual was 0

North Carolina Confirmed

Confirmed daily COVID-19 cases in North Carolina

Projected value for yesterday was 3641 but actual was 0

Idaho Confirmed

Confirmed daily COVID-19 cases in Idaho

Projected value for yesterday was 1579 but actual was 0

Idaho Event Journal

Date Title
2020-11-17 Idaho sees highest single-day rise in Covid-19 cases

US Confirmed

Confirmed daily COVID-19 cases in the US

Projected value for yesterday was 195565 but actual was 110611

US Event Journal

Date Title
2020-11-13 An influential Covid-19 model projects the US death toll could reach 439,000 by March 1
US surpasses record Covid-19 hospitalizations for the third consecutive day
California is now the second US state to top 1 million Covid-19 cases
In areas of the US hit hard by Covid-19, schools are going back to virtual learning
US Supreme Court justice raised religious liberty concerns about Covid restrictions
The US has reported more than 153,000 new cases as infections accelerate
US President-elect’s adviser says Biden won’t "shut the whole country down"
Trump says there will be no lockdown as coronavirus cases increase in the US
US hits new record high Covid-19 cases, with at least 154,576 daily infections reported
Hospitalization rates among Black and Latino people in US about 4 times higher than Whites, says CDC
More states shatter daily records as US tops 10.7 million cases and 244,000 deaths
2020-11-14 More US states shatter daily records as US tops 10.7 million cases and 244,000 deaths
US reported more than 184,000 Covid-19 cases on Friday in its worst day of the pandemic
Covid-19 hospitalizations set a new record across the US
US governors issue stringent new measures amid record cases and hospitalizations
2020-11-15 Coronavirus cases may soon push US hospitals past capacity and force states to shut down, says medical expert
The US has reported more than 100,000 new Covid-19 cases for 12 consecutive days
These US states reported a record rise in cases on Saturday
US Surgeon General says this symptom can help differentiate coronavirus from flu
The US has now reported more than 10.9 million coronavirus cases
Coronavirus spread across the US pushes Colorado to ramp up emergency response
More than 245,000 people have died from coronavirus in the US
2020-11-16 States enact more Covid-19 rules as the US hits 11 million cases
Washington state enacts new restrictions, months after it was first in the US to confront Covid-19
US reports more than 133,000 new Covid-19 cases
The US added 1 million Covid-19 cases in just 6 days
US Health secretary says Moderna's vaccine is "more flexible." Here's why.
US stocks rise sharply after Moderna says vaccine is 94.5% effective
More than 1 million US children have been diagnosed with Covid-19, pediatricians say
US has distributed 50 million point of care tests to keep schools open, White House testing czar says
US should track people with persistent post-Covid-19 symptoms, Fauci says
US tops 70,000 Covid-19 hospitalizations
2020-11-17 Covid-19 hospitalizations top 70,000 in US
Pfizer to test coronavirus vaccine distribution in 4 US states
US reports more than 166,000 new Covid-19 cases
More than 247,000 people have died from Covid-19 in the US
Navajo Nation president says strict public health measures helped them avoid one of the US pandemic waves
US government agency warns of possible coronavirus vaccine production bottlenecks
2020-11-18 US reports more than 161,000 new Covid-19 cases
With possible vaccine on the horizon, US is likely in "last big surge" of the pandemic's toughest phase
US stock market opens mixed following more good vaccine news
Health secretary says US will have 40 million doses of Covid-19 vaccine available by end of December
US surpasses 250,000 reported deaths from Covid-19
US military reported record high of Covid-19 cases on Tuesday
The US may see a "graded rollout" of vaccines in the coming months, says Fauci
2020-11-19 Covid-19 has killed 250,000 people in the US. That's 10 times the deaths from car crashes in a year
US reports more than 170,000 Covid-19 cases
A top US official says hospitalizations and deaths will keep climbing as Covid-19 cases explode nationwide
US stocks open lower over fears of coronavirus resurgence 
CDC’s ensemble forecast now projects up to 298,000 US Covid-19 deaths by Dec. 12
There's a coronavirus outbreak on a US Navy warship, official says
Birx asks Americans to remain vigilant as coronavirus infections rise in the US
US has 100 million kits for distributing coronavirus vaccines, Operation Warp Speed officer says
The US has reported more than 182,000 Covid-19 cases so far today. That's a new daily high
2020-11-20 Fauci confident the US CDC can get people vaccinated amid delay in transition of power
US reports more than 187,000 new Covid-19 cases in highest single day surge
Pfizer and BioNTech will submit for emergency authorization for Covid-19 vaccine in US today
What the US could learn from Europe to avert a pandemic worst-case scenario
US extends travel restrictions on northern and southern borders into December 
The US has reported more than 193,000 cases so far today -- a new daily high for the second straight day
2020-11-21 The US has reported more than 193,000 cases so far for Friday-- a new daily high for the second straight day
US reports more than 195,000 new Covid-19 cases -- a record one-day total for the second straight day
The spread in the US could be "faster, broader and longer" than before, expert warns
Here's the latest on the pandemic in the US as the nation prepares for Thanksgiving
Thanksgiving in Canada could be a cautionary tale for the US
The US is approaching 12 million coronavirus cases
How US medical experts are spending Thanksgiving during the pandemic
US surpasses 12 million coronavirus cases
Here's the latest on the coronavirus pandemic in the US as the country hits 12 million cases
2020-11-22 The US reports more than 177,000 Covid-19 cases
The US is seeing an unprecedented coronavirus resurgence as Americans prepare for Thanksgiving
US vaccine chief says first Americans could be vaccinated on Dec. 11
Here's how US medical experts are celebrating Thanksgiving this year
Nearly 25% of the US's coronavirus cases have been reported this month
The US has had more than 3 million Covid-19 cases in November — and the month isn't even over yet
The US is seeing an unprecedented coronavirus resurgence. Here's where things stand.
This is the 20th straight day with more than 100,000 new Covid-19 cases in the US
2020-11-23 The US has recorded more than 3 million new Covid-19 infections in November
US reports more than 142,000 new Covid-19 cases
Hospitalizations and cases soar in the US, as it starts one of the busiest travel weeks of the year
Air travel surges in the US ahead of Thanksgiving holiday despite CDC warning
“When they tell me I can get a vaccine, I will get it,” US Surgeon General says
Catch up: Here are the latest Covid-19 headlines in the US
US stocks rise at the opening bell after more good vaccine news
More than 256,000 people have died from coronavirus in the US
US set another record for new Covid cases in children last week, group says
2020-11-24 The US could have 20 million coronavirus cases by Inauguration Day, new model predicts
The US set another record for new Covid cases in children last week, AAP says
US reports more than 169,000 new Covid-19 cases
A negative Covid-19 test yesterday doesn’t mean you’re safe tomorrow, US surgeon general says
These are the US surgeon general's tips for holding a safer gathering 
Catch up: The latest Covid-19 trends in the US as cases and hospitalizations surge
US could distribute Covid-19 vaccine "soon after" Dec. 10, HHS secretary says
White House testing czar: US faces "dangerous point" in pandemic
Almost all US Navy installations across US are raising their health protection measures due to Covid-19
US sets record for Covid-19 hospitalizations
US reports more than 2,000 new Covid-19 deaths
US reviewing AstraZeneca's vaccine data to see if better efficacy is possible, Warp Speed chief says
2020-11-25 CDC director: US is going to see health care system "overwhelmed" in some areas during Covid surge
US reports more than 2,100 new Covid-19 deaths
Fauci says he's worried about what US Covid-19 case numbers will look like 3 weeks after Thanksgiving
Here are the latest Covid-19 headlines from across the US
White House considering lifting European travel restrictions as US Covid-19 cases surge
More than 260,000 people have died from coronavirus in the US
There's a rapid rise in Covid-19 cases in US nursing homes, health group CEO says
More than 2,000 deaths from Covid-19 reported in the US today

Kentucky Confirmed

Confirmed daily COVID-19 cases in Kentucky

Projected value for yesterday was 3130 but actual was 0

Kentucky Event Journal

Date Title
2020-11-14 Two dozen people died of Covid-19 in a Kentucky veterans center as infections in the community surged
2020-11-24 Kentucky is "being overwhelmed" by Covid-19, governor says

Oklahoma Confirmed

Confirmed daily COVID-19 cases in Oklahoma

Projected value for yesterday was 3695 but actual was 0

Oklahoma Event Journal

Date Title
2020-11-23 Oklahoma reports more than 3,000 new Covid-19 cases for the third day in a row 
2020-11-24 Oklahoma surpasses 180,000 total Covid-19 cases

Nebraska Confirmed

Confirmed daily COVID-19 cases in Nebraska

Projected value for yesterday was 2032 but actual was 5270

Nebraska Event Journal

Date Title
2020-11-17 This Nebraska dad is building hundreds of desks to help kids succeed in remote learning

Utah Confirmed

Confirmed daily COVID-19 cases in Utah

Projected value for yesterday was 3434 but actual was 0

Utah Event Journal

Date Title
2020-11-17 Utah's 7-day average for new coronavirus cases tops 3,000

Vermont Confirmed

Confirmed daily COVID-19 cases in Vermont

Projected value for yesterday was 111 but actual was 0

Florida Dead

Daily COVID-19 deaths in Florida

Projected value for yesterday was 75 but actual was 0

Florida Event Journal

Date Title
2020-11-16 Here are the latest Covid-19 numbers from Florida
2020-11-20 Florida Sen. Rick Scott tests positive for coronavirus
2020-11-23 Florida's new cases per week have tripled since Gov. DeSantis reopened the state
Here's how Florida plans to distribute a Covid-19 vaccine 
2020-11-24 Florida health system is preparing to distribute Pfizer vaccine
Florida reports more than 8,000 new Covid-19 cases
2020-11-25 Florida governor says his goal is to provide Covid-19 vaccines to all long-term care facilities residents 

Chile Dead

Daily COVID-19 deaths in Chile

Projected value for yesterday was 30 but actual was 97

Chile Event Journal

Date Title
2020-11-23 Chile to allow foreign visitors to fly into Santiago after eight-month shutdown

Minnesota Dead

Daily COVID-19 deaths in Minnesota

Projected value for yesterday was 55 but actual was 0

Minnesota Event Journal

Date Title
2020-11-19 Minnesota landmarks to light up purple in honor of Covid victims and frontline workers

South Dakota Confirmed

Confirmed daily COVID-19 cases in South Dakota

Projected value for yesterday was 1160 but actual was 0

South Dakota Event Journal

Date Title
2020-11-16 This South Dakota nurse says her Covid-19 patients often don’t believe the virus is real
2020-11-17 North and South Dakota doctors face ICU and staffing shortages as coronavirus cases skyrocket
2020-11-24 South Dakota tops 70,000 Covid-19 cases

Wyoming Confirmed

Confirmed daily COVID-19 cases in Wyoming

Projected value for yesterday was 932 but actual was 0

Wyoming Event Journal

Date Title
2020-11-24 Wyoming governor's office closed due to Covid-19 case
2020-11-25 Wyoming governor tests positive for Covid-19

Massachusetts Dead

Daily COVID-19 deaths in Massachusetts

Projected value for yesterday was 30 but actual was 0

Massachusetts Event Journal

Date Title
2020-11-13 More than 10,000 people have now died from Covid-19 in Massachusetts
Massachusetts governor says "innocent acts of small gatherings" driving spread of Covid-19
Field hospital to reopen at Massachusetts arena as Covid-19 cases "trending in the wrong direction"