<jupyter_start><jupyter_text>NYT<jupyter_code>nyt = pd.read_csv('~/nyt_aug19.csv',sep=',',parse_dates=['date'])

nyt.index = nyt['date']
nyt.drop(['cases','date'],axis=1,inplace=True)
nyt.sort_values(['state','date'],inplace=True)

nyt_all = pd.DataFrame()

for i in np.unique(nyt['state']):
    nyt_agg= list()
    d = nyt[nyt['state']==i]['deaths']
    if '2020-02-01' in nyt[nyt['state']==i]['deaths'].index:
        nyt_agg.append(d['2020-04-25'] - d['2020-03-21'])
    else:
        nyt_agg.append(d['2020-04-25'])
        
    nyt_agg.append(d['2020-05-30'] - d['2020-04-25'])
    nyt_agg.append(d['2020-07-04'] - d['2020-05-30'])
    nyt_agg.append(d['2020-08-08'] - d['2020-07-04'])    
    nyt_all[i]  =  nyt_agg

nyt_df =  pd.DataFrame(nyt_all).drop(['Northern Mariana Islands','Puerto Rico','Virgin Islands','Guam'],axis=1).T

nyt_df.loc['AAusa'] = nyt_df.sum()  #  add all US
nyt_df.sort_index(inplace=True)

nyt_df.columns = mmwr
nyt_df['12-16_may'] = nyt_df['12-16_aug']  # add may col

nyt_df = nyt_df[cols]
nyt_df.to_csv(out+f'NYT_5MMWRagg.csv',sep=',')<jupyter_output><empty_output><jupyter_text>JHU/CSSE<jupyter_code>jhu = pd.read_csv('~/18AugCSSEtime_series_covid19_deaths_US.csv',sep=',')

jhu.drop(['UID','iso2','iso3','code3','FIPS','Admin2','Country_Region','Lat','Long_','Combined_Key','Population'],inplace=True,axis=1)

jhu_all = pd.DataFrame()

for i in  np.unique(jhu['Province_State']): 
    jhu_agg = list()
    ss = jhu[jhu['Province_State']==i].sum()
    jhu_agg.append(ss['4/25/20'] - ss['3/21/20'])
    jhu_agg.append(ss['5/30/20'] - ss['4/25/20'])
    jhu_agg.append(ss['7/4/20'] - ss['5/30/20'])
    jhu_agg.append(ss['8/8/20'] - ss['7/4/20'])
    jhu_all[i]  = jhu_agg
    
jhu_df =  pd.DataFrame(jhu_all).drop(['American Samoa','Grand Princess', 'Northern Mariana Islands',
                                      'Puerto Rico','Virgin Islands','Guam','Diamond Princess'],axis=1).T

jhu_df.loc['AAusa'] = jhu_df.sum()  #  add all US
jhu_df.sort_index(inplace=True)

jhu_df.columns = mmwr
jhu_df['12-16_may'] = jhu_df['12-16_aug']  # add may col

jhu_df = jhu_df[cols]

jhu_df.to_csv(out+f'JHU_5MMWRagg.csv',sep=',')<jupyter_output><empty_output><jupyter_text>USAFacts<jupyter_code>usafacts = pd.read_csv('~/18Augcovid_deaths_usafacts.csv',sep=',')
usa_all = pd.DataFrame()

for i in np.unique(usafacts['State']):
    usa_agg = list()
    ss = usafacts[usafacts['State']==i].iloc[:,4:].sum()
    usa_agg.append(ss['4/25/20'] - ss['3/21/20'])
    usa_agg.append(ss['5/30/20'] - ss['4/25/20'])
    usa_agg.append(ss['7/4/20'] - ss['5/30/20'])
    usa_agg.append(ss['8/8/20'] - ss['7/4/20'])
    usa_all[i] = usa_agg
    
usa_df =  pd.DataFrame(usa_all).T<jupyter_output><empty_output><jupyter_text>The Atlantic Covid Tracker<jupyter_code>atl = pd.read_csv('~/18AugVTrakStatesdaily.csv',parse_dates=['date'],sep=',')

atl[['date','state','death','deathConfirmed','deathProbable']].head(5)
atl.index = atl['date']

atl_all = pd.DataFrame()

for i in np.unique(atl['state']):  # ['AL']:
    atl_agg = list()
    ss = atl[atl['state']==i]['death']
    if len(ss['20200201']):
        atl_agg.append(ss['20200425'].item() - ss['20200321'].item())
    else:
        atl_agg.append(ss['20200425'].item())
    atl_agg.append(ss['20200530'].item() - ss['20200425'].item())
    atl_agg.append(ss['20200704'].item() - ss['20200530'].item())
    atl_agg.append(ss['20200808'].item() - ss['20200704'].item())
    atl_all[i] = atl_agg

atl_df =  pd.DataFrame(atl_all).drop(['AS', 'PR', 'VI','GU','MP'],axis=1).T

us_state_abbrev = {
    'Alabama': 'AL',
    'Alaska': 'AK',
    'American Samoa': 'AS',
    'Arizona': 'AZ',
    'Arkansas': 'AR',
    'California': 'CA',
    'Colorado': 'CO',
    'Connecticut': 'CT',
    'Delaware': 'DE',
    'District of Columbia': 'DC',
    'Florida': 'FL',
    'Georgia': 'GA',
    'Guam': 'GU',
    'Hawaii': 'HI',
    'Idaho': 'ID',
    'Illinois': 'IL',
    'Indiana': 'IN',
    'Iowa': 'IA',
    'Kansas': 'KS',
    'Kentucky': 'KY',
    'Louisiana': 'LA',
    'Maine': 'ME',
    'Maryland': 'MD',
    'Massachusetts': 'MA',
    'Michigan': 'MI',
    'Minnesota': 'MN',
    'Mississippi': 'MS',
    'Missouri': 'MO',
    'Montana': 'MT',
    'Nebraska': 'NE',
    'Nevada': 'NV',
    'New Hampshire': 'NH',
    'New Jersey': 'NJ',
    'New Mexico': 'NM',
    'New York': 'NY',
    'North Carolina': 'NC',
    'North Dakota': 'ND',
    'Northern Mariana Islands':'MP',
    'Ohio': 'OH',
    'Oklahoma': 'OK',
    'Oregon': 'OR',
    'Pennsylvania': 'PA',
    'Puerto Rico': 'PR',
    'Rhode Island': 'RI',
    'South Carolina': 'SC',
    'South Dakota': 'SD',
    'Tennessee': 'TN',
    'Texas': 'TX',
    'Utah': 'UT',
    'Vermont': 'VT',
    'Virgin Islands': 'VI',
    'Virginia': 'VA',
    'Washington': 'WA',
    'West Virginia': 'WV',
    'Wisconsin': 'WI',
    'Wyoming': 'WY'
}

inv_map = {v: k for k, v in us_state_abbrev.items()}
# Convert state abbreviations to state names for usafacts and atlantic datasets indices
new_cols = []
for i in usa_df.index.to_list():
    new_cols.append(inv_map[i])
    
usa_df.index = new_cols
atl_df.index = new_cols

atl_df.sort_index(inplace=True)
usa_df.sort_index(inplace=True)
# Add all US and may col for USAFacts
usa_df.loc['AAusa'] = usa_df.sum()  #  add all US
usa_df.sort_index(inplace=True)

usa_df.columns = mmwr
usa_df['12-16_may'] = usa_df['12-16_aug']  # add may col
usa_df = usa_df[cols]

# Add all US and may col for The Atlantic
atl_df.loc['AAusa'] = atl_df.sum()  #  add all US
atl_df.sort_index(inplace=True)

atl_df.columns = mmwr
atl_df['12-16_may'] = atl_df['12-16_aug']  # add may col
atl_df = atl_df[cols]
assert np.all(usa_df.index  == atl_df.index)
assert np.all(jhu_df.index == nyt_df.index)
assert np.all(jhu_df.index == usa_df.index)
atl_df.to_csv(out+f'Atlantic_5MMWRagg.csv',sep=',')
usa_df.to_csv(out+f'USAFacts_5MMWRagg.csv',sep=',')
# Compute average of public trackers
tracker_mean = (jhu_df + nyt_df + usa_df + atl_df)/4
tracker_mean.to_csv(out+'public_trackers_mean.csv',sep=',')<jupyter_output><empty_output><jupyter_text>CDC May 21 dataset<jupyter_code>cdc_may = pd.read_csv('~/21MayI9C1AbnWeekly_Select_Causes_Sept_19-20.csv',parse_dates=['Week Ending Date'],sep=',')  
cdc_may.index = cdc_may['Week Ending Date']

cdc_may_all = pd.DataFrame()
for i in np.unique(cdc_may['Jurisdiction of Occurrence']):
    cdc_may_agg=list()
    ss= cdc_may[cdc_may['Jurisdiction of Occurrence'] == i]['COVID-19 (U071, Underlying Cause of Death)']
    cdc_may_agg.append(ss['2020-03-21':'2020-04-25'].sum())
    cdc_may_all[i] = cdc_may_agg

    
cdc_may_all['New York'] = cdc_may_all['New York'] + cdc_may_all['New York City']
cdc_may_all.drop(['New York City'],axis=1,inplace=True)

cdc_may_df =  pd.DataFrame(cdc_may_all).drop(['United States','Puerto Rico'],axis=1).T
cdc_may_df.columns = ['12-16_may']

cdc_may_df.loc['AAusa'] = cdc_may_df.sum()
cdc_may_df.sort_index(inplace=True)<jupyter_output><empty_output><jupyter_text>CDC August dataset<jupyter_code>PATH_IN =  '~/26AugustI9C1AbnWeekly_Select_Causes__2019-2020.csv'
df = pd.read_csv(PATH_IN,sep=',',parse_dates=['Week Ending Date'])
df.index = df['Week Ending Date']
cdc_aug_all = pd.DataFrame()
for i in np.unique(df['Jurisdiction of Occurrence']):
    cdc_aug_agg=list()
    ss= df[df['Jurisdiction of Occurrence'] == i]['COVID-19 (U071, Underlying Cause of Death)']
    cdc_aug_agg.append(ss['2020-03-21':'2020-04-25'].sum())
    cdc_aug_agg.append(ss['2020-04-26':'2020-05-30'].sum())
    cdc_aug_agg.append(ss['2020-05-31':'2020-07-04'].sum())
    cdc_aug_agg.append(ss['2020-07-05':'2020-08-08'].sum())
    cdc_aug_all[i] = cdc_aug_agg
    
cdc_aug_all['New York'] = cdc_aug_all['New York'] + cdc_aug_all['New York City']
cdc_aug_all.drop(['New York City'],axis=1,inplace=True)
cdc_aug_df =  pd.DataFrame(cdc_aug_all).drop(['United States','Puerto Rico'],axis=1).T
cdc_aug_df.columns = mmwr

cdc_aug_df.loc['AAusa'] = cdc_aug_df.sum()
cdc_aug_df.sort_index(inplace=True)
assert np.all(cdc_aug_df.index == tracker_mean.index)
assert np.all(cdc_aug_df.index == cdc_may_df.index)
cdc_data = pd.concat([cdc_may_df,cdc_aug_df],axis=1)
cdc_data.to_csv(out+'CDC_5MMWR_agg.csv',sep=',')
ratios = cdc_data/tracker_mean
ratios.to_csv(out+'ratios_for_heatmap.csv',sep=',')<jupyter_output><empty_output><jupyter_text>Compute Coefficient of Variation<jupyter_code># Find weekly death counts for all 4 public trackers for each state


##############################################
###############   NYT   ######################
nyt_all_WK = pd.DataFrame()

for i in np.unique(nyt['state']): 
    
    nyt_agg= list()
    ss = nyt[nyt['state']==i]['deaths']
    
    if not (('2020-03-21' in nyt[nyt['state']==i]['deaths'].index) or ('2020-03-28' in nyt[nyt['state']==i]['deaths'].index)):
        nyt_agg.append(0)
    elif (not ('2020-03-21' in nyt[nyt['state']==i]['deaths'].index)) and ('2020-03-28' in nyt[nyt['state']==i]['deaths'].index):
        nyt_agg.append(ss['2020-03-28'])
    elif ('2020-03-21' in nyt[nyt['state']==i]['deaths'].index) and ('2020-03-28' in nyt[nyt['state']==i]['deaths'].index):
        nyt_agg.append(ss['2020-03-28'] - ss['2020-03-21'])

    nyt_agg.append(ss['2020-04-04'] - ss['2020-03-29'])
    nyt_agg.append(ss['2020-04-11'] - ss['2020-04-05'])
    nyt_agg.append(ss['2020-04-18'] - ss['2020-04-12'])
    nyt_agg.append(ss['2020-04-25'] - ss['2020-04-19'])
    nyt_agg.append(ss['2020-05-02'] - ss['2020-04-26'])
    nyt_agg.append(ss['2020-05-09'] - ss['2020-05-03'])
    nyt_agg.append(ss['2020-05-16'] - ss['2020-05-10'])
    nyt_agg.append(ss['2020-05-23'] - ss['2020-05-17'])
    nyt_agg.append(ss['2020-05-30'] - ss['2020-05-24'])
    nyt_agg.append(ss['2020-06-06'] - ss['2020-05-31'])
    nyt_agg.append(ss['2020-06-13'] - ss['2020-06-07'])
    nyt_agg.append(ss['2020-06-20'] - ss['2020-06-14'])
    nyt_agg.append(ss['2020-06-27'] - ss['2020-06-21'])
    nyt_agg.append(ss['2020-07-04'] - ss['2020-06-28'])
    nyt_agg.append(ss['2020-07-11'] - ss['2020-07-05'])
    nyt_agg.append(ss['2020-07-18'] - ss['2020-07-12'])
    nyt_agg.append(ss['2020-07-25'] - ss['2020-07-19'])
    nyt_agg.append(ss['2020-08-01'] - ss['2020-07-26'])
    nyt_all_WK[i]  =  nyt_agg

nyt_df =  pd.DataFrame(nyt_all_WK).drop(['Northern Mariana Islands','Puerto Rico','Virgin Islands','Guam'],axis=1).T
nyt_df.loc['AAusa'] = nyt_df.sum()  #  add all US
nyt_df.sort_index(inplace=True)

nyt_df.columns = mmwr_wkly


##############################################
###############   JHU   ######################
jhu_all_WK = pd.DataFrame()

for i in  np.unique(jhu['Province_State']): 
    jhu_agg = list()
    ss = jhu[jhu['Province_State']==i].sum()
    jhu_agg.append(ss['3/28/20'] - ss['3/21/20'])
    jhu_agg.append(ss['4/4/20'] - ss['3/29/20'])
    jhu_agg.append(ss['4/11/20'] - ss['4/5/20'])
    jhu_agg.append(ss['4/18/20'] - ss['4/12/20'])
    jhu_agg.append(ss['4/25/20'] - ss['4/19/20'])
    jhu_agg.append(ss['5/2/20'] - ss['4/26/20'])
    jhu_agg.append(ss['5/9/20'] - ss['5/3/20'])
    jhu_agg.append(ss['5/16/20'] - ss['5/10/20'])
    jhu_agg.append(ss['5/23/20'] - ss['5/17/20'])
    jhu_agg.append(ss['5/30/20'] - ss['5/24/20'])
    jhu_agg.append(ss['6/6/20'] - ss['5/31/20'])
    jhu_agg.append(ss['6/13/20'] - ss['6/7/20'])
    jhu_agg.append(ss['6/20/20'] - ss['6/14/20'])
    jhu_agg.append(ss['6/27/20'] - ss['6/21/20'])
    jhu_agg.append(ss['7/4/20'] - ss['6/28/20'])
    jhu_agg.append(ss['7/11/20'] - ss['7/5/20'])
    jhu_agg.append(ss['7/18/20'] - ss['7/12/20'])
    jhu_agg.append(ss['7/25/20'] - ss['7/19/20'])
    jhu_agg.append(ss['8/1/20'] - ss['7/26/20'])
    jhu_all_WK[i]  = jhu_agg
    
jhu_df =  pd.DataFrame(jhu_all_WK).drop(['American Samoa','Grand Princess', 'Northern Mariana Islands',
                                      'Puerto Rico','Virgin Islands','Guam','Diamond Princess'],axis=1).T

jhu_df.loc['AAusa'] = jhu_df.sum()  #  add all US
jhu_df.sort_index(inplace=True)
jhu_df.columns = mmwr_wkly


##############################################
#############   Atlantic   ###################

atl_all_WK = pd.DataFrame()

for i in np.unique(atl['state']):  
    atl_agg = list()
    ss = atl[atl['state']==i]['death']
    
    if (len(ss['20200321']) == 1) and (len(ss['20200328']) == 1):
        atl_agg.append(ss['20200328'].item() - ss['20200321'].item()) 

    elif len(ss['20200321']) == 0 and len(ss['20200328']) == 1:
        atl_agg.append(ss['20200328'].item())
    elif len(ss['20200321']) == 0 and len(ss['20200328']) == 0:
            atl_agg.append(0)
    atl_agg.append(ss['20200404'].item() - ss['20200329'].item())
    atl_agg.append(ss['20200411'].item() - ss['20200405'].item())
    atl_agg.append(ss['20200418'].item() - ss['20200412'].item())
    atl_agg.append(ss['20200425'].item() - ss['20200419'].item())
    atl_agg.append(ss['20200502'].item() - ss['20200426'].item())
    atl_agg.append(ss['20200509'].item() - ss['20200503'].item())
    atl_agg.append(ss['20200516'].item() - ss['20200510'].item())
    atl_agg.append(ss['20200523'].item() - ss['20200517'].item())
    atl_agg.append(ss['20200530'].item() - ss['20200524'].item())
    atl_agg.append(ss['20200606'].item() - ss['20200531'].item())
    atl_agg.append(ss['20200613'].item() - ss['20200607'].item())
    atl_agg.append(ss['20200620'].item() - ss['20200614'].item())
    atl_agg.append(ss['20200627'].item() - ss['20200621'].item())
    atl_agg.append(ss['20200704'].item() - ss['20200628'].item())
    atl_agg.append(ss['20200711'].item() - ss['20200705'].item())
    atl_agg.append(ss['20200718'].item() - ss['20200712'].item())
    atl_agg.append(ss['20200725'].item() - ss['20200719'].item())
    atl_agg.append(ss['20200801'].item() - ss['20200726'].item())
    atl_all_WK[i]  = atl_agg

atl_df =  pd.DataFrame(atl_all_WK).drop(['AS', 'PR', 'VI','GU','MP'],axis=1).T



##############################################
##############  USAfacts   ###################
usa_all_WK = pd.DataFrame()

for i in np.unique(usafacts['State']):
    usa_agg = list()
    ss = usafacts[usafacts['State']==i].iloc[:,4:].sum()
    usa_agg.append(ss['3/28/20'] - ss['3/21/20'])
    usa_agg.append(ss['4/4/20'] - ss['3/29/20'])
    usa_agg.append(ss['4/11/20'] - ss['4/5/20'])
    usa_agg.append(ss['4/18/20'] - ss['4/12/20'])
    usa_agg.append(ss['4/25/20'] - ss['4/19/20'])
    usa_agg.append(ss['5/2/20'] - ss['4/26/20'])
    usa_agg.append(ss['5/9/20'] - ss['5/3/20'])
    usa_agg.append(ss['5/16/20'] - ss['5/10/20'])
    usa_agg.append(ss['5/23/20'] - ss['5/17/20'])
    usa_agg.append(ss['5/30/20'] - ss['5/24/20'])
    usa_agg.append(ss['6/6/20'] - ss['5/31/20'])
    usa_agg.append(ss['6/13/20'] - ss['6/7/20'])
    usa_agg.append(ss['6/20/20'] - ss['6/14/20'])
    usa_agg.append(ss['6/27/20'] - ss['6/21/20'])
    usa_agg.append(ss['7/4/20'] - ss['6/28/20'])
    usa_agg.append(ss['7/11/20'] - ss['7/5/20'])
    usa_agg.append(ss['7/18/20'] - ss['7/12/20'])
    usa_agg.append(ss['7/25/20'] - ss['7/19/20'])
    usa_agg.append(ss['8/1/20'] - ss['7/26/20'])
    usa_all_WK[i] = usa_agg
    
usa_df =  pd.DataFrame(usa_all_WK).T 


# Convert abbreviations to state names
new_cols = []
for i in usa_df.index.to_list():
    new_cols.append(inv_map[i])
    
usa_df.index = new_cols
atl_df.index = new_cols

atl_df.sort_index(inplace=True)
usa_df.sort_index(inplace=True)

# Add all US and may col for USAFacts and The Alantic
usa_df.loc['AAusa'] = usa_df.sum()  #  add all US
usa_df.sort_index(inplace=True)
usa_df.columns = mmwr_wkly

atl_df.loc['AAusa'] = atl_df.sum()  #  add all US
atl_df.sort_index(inplace=True)
atl_df.columns = mmwr_wkly


assert nyt_df.shape == jhu_df.shape
assert usa_df.shape == atl_df.shape
assert np.all(nyt_df.index == jhu_df.index)
assert np.all(usa_df.index == atl_df.index)
from scipy.stats import variation as coeffvar

cov_vec= pd.DataFrame()
for i in nyt_df.index: 

    d = pd.DataFrame([jhu_df.loc[i].values,nyt_df.loc[i].values,atl_df.loc[i].values,
            usa_df.loc[i].values],index= ['jhu','nyt','atl','usafacts'])
    cov_vec[i] = coeffvar(d)
    
coeff_vec = cov_vec.replace(np.nan,0).T
# compare to US Coeff. Var. (summed) total
i = 'AAusa'

d = pd.DataFrame([jhu_df.loc[i].values,nyt_df.loc[i].values,atl_df.loc[i].values,
            usa_df.loc[i].values],index= ['jhu','nyt','atl','usafacts'])

# compute US coeff. var. for entire time period
total_US_CoV  = d.sum(axis=1).std() / d.sum(axis=1).mean()


for i in nyt_df.index:
    if i is not 'AAusa':            # if a state has a higher Coeff. Var. than overall US more than 8 times, 
        if sum(coeff_vec.loc[i] > 2*total_US_CoV) > 8:  # we consider it to have a high Coeff. Var
            print(i)<jupyter_output>Colorado
Delaware
Idaho
Kansas
Michigan
Missouri
Nebraska
Nevada
New Jersey
New York
North Dakota
Texas
{'fraction_non_alphanumeric': 0.23705559532331186, 'fraction_numerical': 0.09025292293008828, 'mean_word_length': 4.201675457648154, 'pattern_counts': {'":': 0, '<': 28, '<?xml version=': 0, '>': 30, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '7', 'dir': 'jupyter-structured-clean-dedup', 'id': '132650', 'license': 'Apache-2.0', 'n_tokens_mistral': 8193, 'n_tokens_neox': 7133, 'n_words': 968, 'path': 'contrib/bstear/code/figure5.ipynb', 'repo_name': 'COV-IRT/dmwg-covid-reporting-qc', 'size': '16764'}