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-07-09 12:15:05 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 = ''
for index,row in df.iterrows():
    meta = metricdf.loc[row['i']]
    mdString += '- [' + meta['name'] + '](#' + meta['name'].replace(' ','-') + ')' + "\n"
    
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())

Idaho Dead

Daily COVID-19 deaths in Idaho

Projected value for yesterday was 0 but actual was 4

West Virginia Confirmed

Confirmed daily COVID-19 cases in West Virginia

Projected value for yesterday was 86 but actual was 202

West Virginia Event Journal

Date Title
2020-07-06 West Virginia orders adults and kids 9 and older to wear masks when they can't social distance
2020-07-08 West Virginia sets September 8 as target date for reopening schools

South Carolina Dead

Daily COVID-19 deaths in South Carolina

Projected value for yesterday was 15 but actual was 38

South Carolina Event Journal

Date Title
2020-06-25 Charleston, South Carolina, approves ordinance requiring face coverings
2020-06-26 South Carolina governor says he will not lift restrictions until Covid-19 is under control
2020-07-03 South Carolina reports 20.7% positivity rate from new coronavirus tests

Texas Dead

Daily COVID-19 deaths in Texas

Projected value for yesterday was 43 but actual was 97

Texas Event Journal

Date Title
2020-06-25 Texas cities could see "apocalyptic" numbers of Covid-19 cases, expert warns
Texas governor issues order suspending elective surgeries in 4 counties to expand hospital capacity
Texas governor pauses additional reopening phases as cases increase
Health expert on Texas' "dire" case spike: "I can't stress enough how concerned I am"
2020-06-26 The pandemic is "roaring" and states like Texas will have to partially shut down, expert warns
Texas governor limits certain businesses to contain the spread of Covid-19
Texas lawmakers call for extension for federal coronavirus test site funding
This Texas county is close to running out of hospital capacity
Texas governor announces extension of federally-supported Covid-19 testing sites
Texas Tech University announces 23 student-athletes and staff have tested positive for Covid-19
Texas reports 28 new coronavirus-related deaths
New daily high in Texas coronavirus cases is a "warning shot," health expert says
2020-06-27 Texas reports 5,747 new coronavirus cases today
Texas governor says "in hindsight" he reopened bars too soon
2020-06-28 Texas Medical Center published incomplete data on ICU beds Saturday. Governor calls for accurate and timely data
2020-06-29 Bar owners in Texas and Florida fear crushing impacts of this second shutdown
University of Texas at Austin will shorten its fall semester
Houston, Texas reporting a 10% increase in Covid-19 positivity rate, mayor says
2020-06-30 Texas reports highest daily total of Covid-19 cases
2020-07-01 Teens test positive for Covid-19 after attending 300-person party in Texas
Texas reports highest single-day increase in coronavirus cases
Texas bar owner plans to hold mask-free July 4 event
23-year-old who recovered from coronavirus says she thinks Texas opened too soon
Why the "dimmer switch" approach to pausing reopening won't work in Arizona, Florida and Texas
2020-07-02 Texas health care worker: If you want to see August 1, stay home on July 4
Texas governor implements statewide mask mandate
Texas governor orders most residents to wear masks in public
2020-07-03 Texas Rangers player tests positive for Covid-19
Here's the latest coronavirus update from Texas
What you need to know about the mask requirement in Texas
Texas cell phones get public safety alert to wear masks
2020-07-04 Hospitals in two Texas counties reach capacity; judges urge residents to avoid gatherings
Texas reported its second-highest day of new Covid-19 cases
2020-07-05 Texas distributes antiviral drug remdesivir to more than 150 hospitals across the state
2020-07-06 Officials say states like Arizona and Texas reopened too quickly after soaring cases
Coronavirus cases are rising in Texas child care facilities
Texas reports more than 5,300 new Covid-19 cases
2020-07-07 US military deploys medical personnel to Texas as coronavirus cases accelerate
Cases are skyrocketing in Florida, Texas and Arizona. Here's a look at the latest figures.
At least 84 offenders and 9 employees have died from Covid-19 in Texas prisons
Texas county judge wants to issue stay-at-home order
More than 10,000 new coronavirus cases reported in Texas
Texas Education Agency says parents have option to choose remote learning for their children
Texas Republicans have no plans to cancel in-person convention in Houston 
2020-07-08 Birx lays out "encouraging" trends for coronavirus in Arizona, Texas and Florida
Texas reports highest number of Covid-19 deaths in a single day 
Texas and Arizona ER doctors say they are losing hope as hospitals reach capacity

Colorado Confirmed

Confirmed daily COVID-19 cases in Colorado

Projected value for yesterday was 299 but actual was 452

Colorado Event Journal

Date Title
2020-06-30 Colorado governor orders bars and nightclubs to close

North Dakota Confirmed

Confirmed daily COVID-19 cases in North Dakota

Projected value for yesterday was 44 but actual was 73

Saudi Arabia Dead

Daily COVID-19 deaths in Saudi Arabia

Projected value for yesterday was 53 but actual was 42

California Dead

Daily COVID-19 deaths in California

Projected value for yesterday was 64 but actual was 145

California Event Journal

Date Title
2020-06-25 California is not shutting down its border due to Covid-19 spike, governor says
California governor declares budget emergency due to Covid-19
California reports more than 5,000 new coronavirus cases
California governor praises Disney's decision to delay Disneyland reopening
California will roll back reopening if hospitals become strained, governor says
Hospitalizations in California climb 32% in two weeks during surge of new coronavirus infections
2020-06-26 13 people test positive for Covid-19 after family party in California
Governor advises county in Southern California to reinstate stay-at-home order
Recent protests have contributed to California's coronavirus case increase, state official says
California governor says coronavirus-related death rates around the state "are lagging"
2020-06-27 California records single day increase of nearly 6,000 positive cases of Covid-19
2020-06-28 California governor closes bars in seven counties
2020-06-29 72% of California population is on coronavirus "watch list," governor says
More than 2,500 California inmates have coronavirus
"We did open too quickly," California county public health official says
2020-06-30 California records more than 6,300 new coronavirus cases in a day
California governor will announce more Covid-19 restrictions tomorrow: "We're gonna have to be tougher"
Covid-19 program leads to permanent housing for homeless in California
2020-07-01 California's daily coronavirus case count smashes new record
California's Orange County orders closure of bars until further notice
California governor closes all indoor activities for counties on "watch list"
Access to California's state beaches will be restricted over holiday weekend
California governor encourages cancellation of July 4 fireworks shows
California and Florida are taking different reopening paths as Covid-19 cases spike
California continues to set Covid-19 records. Here's the latest on the state's figures.
Here's what went wrong in California, according to a state senator
2020-07-02 California's coronavirus cases top 240,000
How California is encouraging people to stay home and wear a mask
California will withhold funds from people who don't comply with health orders, governor says
He posted his regrets over attending a party in California. The next day, he died of coronavirus
University of Southern California reverses decision to resume in-person classes this fall
California inmate found dead in cell tested positive for coronavirus, sheriff says
2020-07-03 A death row inmate died of coronavirus. Nearly half the cases in California jails are in the same facility
About 43% of California's total Covid-19 cases are located in Los Angeles County
2020-07-04 California's Huntington Beach was packed 3 weeks ago. Today, it's eerily empty.
2020-07-06 California's coronavirus positivity rate grows to 6.8%
In California, three more inmates died in San Quentin Prison Covid-19 outbreak
Business founded by California governor received at least $150,000 from Paycheck Protection Program
Top medical officer for California prisons replaced amid coronavirus outbreak
California reports more than 11,700 new cases, the biggest one-day jump in the US so far
California adds six more counties to its Covid-19 monitoring list
2020-07-07 Several California lawmakers test positive for Covid-19
California prisons replace their top medical officer as coronavirus outbreak worsens
California's coronavirus hospitalizations remain at all-time high
2020-07-08 California governor says schools will reopen when the data shows it is safe to do so
Covid-19 hospitalizations in California are up 44%, governor says

Tennessee Confirmed

Confirmed daily COVID-19 cases in Tennessee

Projected value for yesterday was 1476 but actual was 2472

Tennessee Event Journal

Date Title
2020-06-26 Tennessee reports highest daily number of new Covid-19 cases
2020-06-28 Tennessee announces delay in reporting Covid-19 data due to technical difficulties with database
2020-06-29 Tennessee extends state of emergency until August 29
2020-07-03 Tennessee governor gives mayors authority to issue mask requirements
2020-07-07 Bars ordered to close again in Shelby County, Tennessee

Delaware Confirmed

Confirmed daily COVID-19 cases in Delaware

Projected value for yesterday was 140 but actual was 48

Delaware Event Journal

Date Title
2020-06-30 Delaware to close bars at beaches due to increase in cases
2020-07-07 New York adds Delaware, Kansas and Oklahoma to traveler quarantine list

Tennessee Dead

Daily COVID-19 deaths in Tennessee

Projected value for yesterday was 8 but actual was 20

Tennessee Event Journal

Date Title
2020-06-26 Tennessee reports highest daily number of new Covid-19 cases
2020-06-28 Tennessee announces delay in reporting Covid-19 data due to technical difficulties with database
2020-06-29 Tennessee extends state of emergency until August 29
2020-07-03 Tennessee governor gives mayors authority to issue mask requirements
2020-07-07 Bars ordered to close again in Shelby County, Tennessee

New Mexico Dead

Daily COVID-19 deaths in New Mexico

Projected value for yesterday was 2 but actual was 8

New Mexico Event Journal

Date Title
2020-06-25 New Mexico pauses reopening plans, governor says
2020-07-01 New Mexico governor attributes rise in Covid-19 cases to people failing to wear face masks

Ecuador Confirmed

Confirmed daily COVID-19 cases in Ecuador

Projected value for yesterday was 914 but actual was 0

Ecuador Event Journal

Date Title
2020-06-25 Hospitals in Ecuador are overwhelmed after sharp rise in Covid-19 patients
2020-06-30 Ecuador announces more Covid-19 measures in Quito as country's cases pass 55,000
2020-07-03 Coronavirus cases in Ecuador top 60,000

Utah Dead

Daily COVID-19 deaths in Utah

Projected value for yesterday was 3 but actual was 7

Utah Event Journal

Date Title
2020-06-26 Utah governor approves mask requirement for 2 counties
2020-07-08 Utah reports record number of new Covid-19 cases

Portugal Dead

Daily COVID-19 deaths in Portugal

Projected value for yesterday was 7 but actual was 2

Portugal Event Journal

Date Title
2020-06-25 Portugal to bring back coronavirus lockdown to several areas of Lisbon, prime minister says
2020-07-01 Spain and Portugal declare land border open
2020-07-03 Portugal's foreign ministry criticizes UK's new "travel corridor" list

Ireland Dead

Daily COVID-19 deaths in Ireland

Projected value for yesterday was 0 but actual was -4

Ireland Event Journal

Date Title
2020-06-27 Northern Ireland discharges final ICU coronavirus patients