<jupyter_start><jupyter_text>Make a minimal dataframe for analysis containing only final columns<jupyter_code>full_merged_df = pd.merge(
    merged_df_test_counts, ctp_516_positives_deaths, on=['state'], how='inner', sort=True, copy=True)
# rename some CDC columns
full_merged_df.rename(columns={
    'positive': 'ctp_total_cases',
    'death': 'ctp_total_death',
}, inplace=True)
full_merged_df.set_index('state', inplace=True)
full_merged_df.to_csv('merged_data_for_analysis.csv')<jupyter_output><empty_output><jupyter_text>How many nationwide tests according to CDC and CTP? What are the national positivity rates?<jupyter_code># CTP
print('CTP:')
ctp_us_positives = sum(full_merged_df.ctp_total_cases)
ctp_us_tests = sum(full_merged_df.ctp_total_tests_for_analysis)
print('National positives: %d' % ctp_us_positives)
print('National test count: %d' % ctp_us_tests)
print('National positivity rate: %s' % float(ctp_us_positives/ctp_us_tests))

# same for CDC
print('CDC:')
cdc_us_positives = sum(full_merged_df.cdc_total_cases)
cdc_us_tests = sum(full_merged_df.cdc_total_tests)
print('National positives: %d' % cdc_us_positives)
print('National test count: %d' % cdc_us_tests)
print('National positivity rate: %s' % float(cdc_us_positives/cdc_us_tests))
full_merged_df['test_count_diff'] = \
    full_merged_df['cdc_total_tests'] - full_merged_df['ctp_total_tests_for_analysis']
full_merged_df['abs_test_count_diff'] = \
    abs(full_merged_df['cdc_total_tests'] - full_merged_df['ctp_total_tests_for_analysis'])
full_merged_df['test_count_percent_diff'] = \
    full_merged_df['test_count_diff']/full_merged_df['ctp_total_tests_for_analysis']
full_merged_df['abs_test_count_percent_diff'] = \
    abs(full_merged_df['test_count_diff']/full_merged_df['ctp_total_tests_for_analysis'])
# sort by test_count_diff
full_merged_df.sort_values('test_count_diff', inplace=True)
full_merged_df.head()
# sns.set_style("whitegrid")

# plt.figure(figsize=(18,7))

# def get_color(row):
#     if row.test_count_diff > 0:
#         return '#ffad4a'
#     else:
#         return '#585bc1'
    
# palette = list(full_merged_df.apply(get_color, axis=1))
# ax = sns.barplot(x=full_merged_df.index, y='test_count_diff', data=full_merged_df,
#                  palette=palette)
# ax.set_xticklabels(rotation=45, labels=full_merged_df.index, fontsize=12)
# ax.set_ylabel('')
# ax.set_xlabel('')
# ax.set_title('Test count differences between CDC and CTP data', fontsize=20, pad=10)<jupyter_output><empty_output><jupyter_text>Test count comparison CTP vs CDC<jupyter_code># a bit too busy, let's pick a difference threshold - only show states with a >5% difference
sns.set_style("whitegrid")

plt.figure(figsize=(18,7))

def get_color(row):
    if row.test_count_diff > 0:
        return '#ffad4a'  # orange
    else:
        return '#585bc1'  # purple

data = full_merged_df.loc[full_merged_df.abs_test_count_percent_diff > 0.05]
palette = list(data.apply(get_color, axis=1))

ax = sns.barplot(x=data.index, y='test_count_diff', data=data, palette=palette)
sns.despine(ax=ax, left=True)
ax.set_xticklabels(rotation=90, labels=data.jurisdiction, fontsize=14)
ax.set_yticklabels(ax.get_yticks(), size=14)
ax.set_ylabel('')
ax.set_xlabel('')
# ax.set_title('States for which CDC and CTP testing counts differ by more than 5%', fontsize=20, pad=10)
ax.yaxis.set_major_locator(ticker.MultipleLocator(100000))
ax.yaxis.set_major_formatter(ticker.FuncFormatter(lambda x, p: format(int(x), ',')))

plt.savefig("test_count_differences_over_5_percent.png", dpi=100, bbox_inches = "tight")<jupyter_output><empty_output><jupyter_text>How close is CDC and CTP testing data numerically?<jupyter_code># how close is our data to CDC?

print('Within 1000: %d states' % sum(
    full_merged_df.apply(lambda row: abs(row.test_count_diff) < 1000, axis=1)))
print('Within 10 percent: %d states' % sum(
    full_merged_df.apply(lambda row: abs(row.test_count_percent_diff) < 0.1, axis=1)))
print('Within 20 percent: %d states' % sum(
    full_merged_df.apply(lambda row: abs(row.test_count_percent_diff) < 0.2, axis=1)))
print('Within 30 percent: %d states' % sum(
    full_merged_df.apply(lambda row: abs(row.test_count_percent_diff) < 0.3, axis=1)))
print('Over 25 percent: %d states' % sum(
    full_merged_df.apply(lambda row: abs(row.test_count_percent_diff) > 0.25, axis=1)))<jupyter_output>Within 1000: 5 states
Within 10 percent: 29 states
Within 20 percent: 35 states
Within 30 percent: 41 states
Over 25 percent: 13 states<jupyter_text>What are the most divergent states, by test counts?<jupyter_code># top 10 most divergent states by test counts
cols = ['jurisdiction', 'ctp_total_tests_for_analysis', 'cdc_total_tests', 'test_count_diff']
full_merged_df.sort_values('abs_test_count_diff', ascending=False).head(10)[cols]<jupyter_output><empty_output><jupyter_text>Same thing but by percentage difference?<jupyter_code># Most divergent states by % difference in test counts

cols = ['jurisdiction', 'ctp_total_tests_for_analysis', 'cdc_total_tests', 'test_count_percent_diff']
full_merged_df.sort_values('abs_test_count_percent_diff', ascending=False).head(10)[cols]<jupyter_output><empty_output><jupyter_text>Correlation between CTP and CDC data: tests, positives, deaths<jupyter_code># plot all 3 together

fig, axes = plt.subplots(nrows=1, ncols=3, figsize=(12,4))
fig.tight_layout(pad=3.0)

def plot_gray_yx(ax):
    lims = [
        np.min([ax.get_xlim(), ax.get_ylim()]),  # min of both axes
        np.max([ax.get_xlim(), ax.get_ylim()]),  # max of both axes
    ]
    # now plot both limits against eachother
    ax.plot(lims, lims, 'k-', alpha=0.75, zorder=0, linestyle='--', color='gray')
    
    # remove gridlines!
    ax.grid(False)

# Case counts

ax=axes[0]
sns.scatterplot(x='ctp_total_cases', y='cdc_total_cases', data=full_merged_df, ax=ax, color='#585bc1')
ax.set_ylabel('CDC', fontsize=14, rotation=0, labelpad=14)
ax.set_xlabel('The COVID Tracking Project (CTP)', fontsize=14)
ax.set_xticklabels('')
ax.set_yticklabels('')
ax.set_title('Cases', fontsize=18, pad=8)
plot_gray_yx(ax)

# Death counts

ax = axes[1]
sns.scatterplot(x='ctp_total_death', y='cdc_total_death', data=full_merged_df, ax=ax, color='#585bc1')
# do a 2nd scatter plot on top, make the NY deaths dot in orange
sns.scatterplot(x='ctp_total_death', y='cdc_total_death',
                data=full_merged_df.loc[full_merged_df.index == 'NY'], ax=ax, color="#ffad4a")
ax.set_xticklabels('')
ax.set_yticklabels('')
ax.set_ylabel('CDC', fontsize=14, rotation=0, labelpad=14)
ax.set_xlabel('CTP', fontsize=14)
ax.set_title('Deaths', fontsize=18, pad=8)
plot_gray_yx(ax)

# Testing counts

ax = axes[2]
sns.scatterplot(x='ctp_total_tests_for_analysis', y='cdc_total_tests', data=full_merged_df, ax=ax,
                color='#585bc1')
ax.set_xticklabels('')
ax.set_yticklabels('')
ax.set_ylabel('CDC', fontsize=14, rotation=0, labelpad=14)
ax.set_xlabel('CTP', fontsize=14)
ax.set_title('Tests', fontsize=18, pad=8)
plot_gray_yx(ax)

plt.savefig("cases_deaths_testing_scatterplots.png", dpi=200, bbox_inches = "tight")<jupyter_output><empty_output><jupyter_text>Make basic state-level summary tables, one per state<jupyter_code># make state-level tables
for state in full_merged_df.index:
    row = full_merged_df.loc[state]
    positive_range = cdc.loc[cdc.state == state].iloc[0].cdc_percent_positive_range

    data_types = [row.jurisdiction, 'Cases', 'Deaths', 'Tests']
    cases_percent_diff_str = '%d%%' % round((row.cdc_total_cases-row.ctp_total_cases)/row.ctp_total_cases*100)
    deaths_percent_diff_str = '%d%%' % round((row.cdc_total_death-row.ctp_total_death)/row.ctp_total_death*100)
    tests_percent_diff_str = '%d%%' % round(row.test_count_percent_diff*100)
    data = {
        row.jurisdiction: ['State', 'CDC', '% Difference'],
        'Cases': [int(row.ctp_total_cases), int(row.cdc_total_cases), cases_percent_diff_str
                  ],
        'Deaths': [int(row.ctp_total_death), int(row.cdc_total_death), deaths_percent_diff_str
                  ],
        'Tests': [int(row.ctp_total_tests_for_analysis), int(row.cdc_total_tests), tests_percent_diff_str],
    }

    pd.DataFrame(data).to_csv('states/%s.csv' % state, index=False)<jupyter_output><empty_output><jupyter_text>Specimens vs people counts ratios, for states that report both<jupyter_code># These states report both 
states = ['FL', 'ME', 'NV', 'VA']
subset_df = ctp_514_test_overrides.loc[ctp_514_test_overrides.state.isin(states)].copy()
subset_df['percent_more_specimens'] = (subset_df.total_tests_specimens/subset_df.total_tests_people - 1)*100
subset_df
# Get some ratio counts
specimens_people_df = ctp_514_test_overrides.loc[ctp_514_test_overrides.state.isin(states)].copy()[
    ['state', 'total_tests_specimens', 'total_tests_people']
]
specimens_people_df['ratio'] = specimens_people_df.total_tests_specimens/specimens_people_df.total_tests_people
specimens_people_df = specimens_people_df.set_index(['state'])
specimens_people_df.loc['Total'] = [
    sum(specimens_people_df.total_tests_specimens),
    sum(specimens_people_df.total_tests_people),
    np.mean(sum(specimens_people_df.total_tests_specimens)/sum(specimens_people_df.total_tests_people))
]
specimens_people_df
# alt ratio: average the individual ratios for each state
alt_ratio = np.mean(specimens_people_df.ratio)
alt_ratio<jupyter_output><empty_output><jupyter_text>Plotting specimens vs people for the 4 states that report both<jupyter_code>plt.figure(figsize=(18,6))

sns.barplot(x=specimens_people_df.index, y='total_tests_specimens', data=specimens_people_df, color="#ffad4a")
ax=sns.barplot(
    x=specimens_people_df.index, y='total_tests_people', data=specimens_people_df,
    color='#585bc1')

sns.despine(ax=ax, left=True)
labels = ['Florida', 'Maine', 'Nevada', 'Virginia', 'Four state total']
ax.set_xticklabels(labels=labels, fontsize=14)
ax.set_yticklabels(ax.get_yticks(), size=14)
ax.set_xlabel('')
ax.set_ylabel('')
ax.yaxis.set_major_locator(ticker.MultipleLocator(250000))
ax.yaxis.set_major_formatter(ticker.FuncFormatter(lambda x, p: format(int(x), ',')))
plt.savefig("people_vs_specimens.png", dpi=100, bbox_inches = "tight")<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13561482643655617, 'fraction_numerical': 0.021867032751519907, 'mean_word_length': 6.863531225905937, 'pattern_counts': {'":': 0, '<': 44, '<?xml version=': 0, '>': 45, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '331468', 'license': 'Apache-2.0', 'n_tokens_mistral': 4115, 'n_tokens_neox': 3824, 'n_words': 641, 'path': 'CDC vs CTP comparison.ipynb', 'repo_name': 'Erin-Boehmer/cdc-comparison', 'size': '10198'}