Covid-19 Testing Importance¶

response = requests.get('https://ourworldindata.org/coronavirus-testing-source-data')
soup = BeautifulSoup(response.content)

table_soup = soup.find("div", {"class": "tableContainer"}).findAll("tr")[1:] # skip the headers

# RegEx to extract the country name, number of test, date
reg = re.compile("<tr><td>(.+)<\/td><td>([\d,]+)<\/td><td>([\w\s]+)<\/td><td>.*<\/td><td>.*<\/td><\/tr>")
testing_data = []
for row in table_soup:
    reg_res = reg.match(str(row))
    if reg_res is not None:
        testing_data.append(reg_res.groups())
    else:
        # Print the rows that failed to be parsed
        print(row)
    

# Create the dataframe
testing_df = pd.DataFrame(testing_data, columns=["Country", "Tests", "Date"])
testing_df['Tests'] = testing_df['Tests'].str.replace(',','').astype(int)    # Transform the x,xxx string to integers
testing_df['Date'] = pd.to_datetime(testing_df['Date'])    # Cast Date from string to date type

# Set country name as index of dataframe
testing_df = testing_df.set_index('Country', drop=False)

# For Canada and Australia we have info about specific regions
# Since on this notebook we will examine country response we merge them

# Australia, dropping all the province info and keeping the aggregated "Australia"
testing_df = testing_df[~testing_df.Country.str.contains("Australia –")]

# Canada
testing_df = testing_df[~testing_df.Country.str.contains("Canada –")]

# Usa has two trackers I will keep the most recent one
testing_df = testing_df.drop('United States – CDC samples tested')

testing_df = testing_df.drop("Hong Kong")

# We rename some countries so as to have the expected country name with the John Hopkins dataset
testing_df = testing_df.rename({
    "China – Guangdong": "China",
    "United States": "US",
    "Czech Republic": "Czechia",
    "South Korea": "Korea, South",
    "Taiwan": "Taiwan*",
    "Faeroe Islands":"Faroe Islands"
})

# Drop Palestine since it is not included on the John Hopkins dataset
testing_df = testing_df.drop("Palestine")

# Drop Faroe Islands since they are merged with Denmark
testing_df = testing_df.drop("Faroe Islands")

testing_df = testing_df.drop('Country', axis=1)

display(testing_df)

def transform_date_to_str(date_str):
    """Transforms the date strings to the John Hopkins one: mm/dd/yy """
    if date_str[8] != '0':
        return date_str[6:7] + "/" + date_str[8:10] + "/20"
    else:
        return date_str[6:7] + "/" + date_str[9:10] + "/20"

def read_john_hopkins_dataset(url, column_name):
    john_hopkins_df = pd.read_csv(url, index_col=['Country/Region'])
    john_hopkins_df = john_hopkins_df.drop('Lat', axis=1)
    john_hopkins_df = john_hopkins_df.drop('Long', axis=1)

    # We must sum the cases on countries that are displayed by region eg US and China
    john_hopkins_df_grouped = john_hopkins_df.groupby(['Country/Region']).sum()

    testing_df[column_name] = np.nan
    for index, row in testing_df.iterrows():
        try:
            testing_df.at[index, column_name] = john_hopkins_df_grouped.loc[index][transform_date_to_str(str(row['Date']))]
        except KeyError:
            # We are recovering from some missing keys
            print(index)

# Add confirmed cases column            
read_john_hopkins_dataset('https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Confirmed.csv',
                          "Confirmed")

# Add recovered cases column
read_john_hopkins_dataset('https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Recovered.csv',
                          "Recovered")   

# Add deaths column
read_john_hopkins_dataset('https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_19-covid-Deaths.csv',
                          "Deaths")

display(testing_df)

# Plot the cases-axis, tests axis
fig, ax = plt.subplots()
fig.set_size_inches(5, 5)
ax.set_yscale('log')
ax.set_xscale('log')
ax.scatter(testing_df.Tests, testing_df.Confirmed)

# We do the +0.1 to avoid having 0 values on a log scale, this +1 will not be used on calculating the correlation value
ax.set_xlim([min(testing_df.Tests)+ 0.1,max(testing_df.Tests) + 0.1])
ax.set_ylim([min(testing_df.Confirmed) + 0.1,max(testing_df.Confirmed) + 0.1])
ax.grid(True)

for i, row in enumerate(testing_df.iterrows()):
    # Add the annotation of the country name if the country has many tests
    # to avoid text cluttering
    if testing_df.Tests[i] > 2e4:
        ax.annotate(row[0], (testing_df.Tests[i], testing_df.Confirmed[i]))

fig.suptitle('Tests vs Confirmed Cases', fontsize=20)
plt.xlabel('Tests (Log)', fontsize=18)
plt.ylabel('Cases (Log)', fontsize=16)    

plt.show()

# Calculate the correlation value
corr_cases, _ = spearmanr(testing_df.Tests, testing_df.Confirmed)
print('Spearmans correlation: %.3f' % corr_cases)

testing_df['DeathRatio'] = testing_df.Deaths / testing_df.Confirmed

testing_df_death_ratio = testing_df[['Tests', 'DeathRatio']].dropna()   # Remove null values

# Remove countries with no deaths so as to not clutter the plot
testing_df_death_ratio = testing_df_death_ratio[testing_df_death_ratio['DeathRatio'] != 0]

# Plot the cases-axis, tests axis
fig, ax = plt.subplots()
fig.set_size_inches(10, 10)
ax.scatter(testing_df_death_ratio.Tests, testing_df_death_ratio.DeathRatio)

ax.grid(True)

for i, row in enumerate(testing_df_death_ratio.iterrows()):
    ax.annotate(row[0], (testing_df_death_ratio.Tests[i], testing_df_death_ratio.DeathRatio[i]))

fig.suptitle('Tests vs Death Ratio', fontsize=20)
plt.xlabel('Tests', fontsize=18)
plt.ylabel('Death Ratio', fontsize=16)    

plt.show()