<jupyter_start><jupyter_text>Covid-19 - Data PreprocessingThis notebook covers the data preprocessing for the Covid-19 Tableau Dashboard by <NAME>.Link to Dashboard: [Tableau Public](https://public.tableau.com/views/covid19_15924716772030/Dashboard?:language=en&:display_count=y&publish=yes&:origin=viz_share_link)The Center for Systems Science and Engineering (CSSE) at Johns Hopkins University provides one of the best data repositories on the Covid-19 Pandemic available.Source: [CSSE](https://github.com/CSSEGISandData/COVID-19)<jupyter_code>import pandas as pd
import numpy as np
# Read datasets from CSSE github repo
confirmed = pd.read_csv('https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv')
deaths = pd.read_csv('https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_deaths_global.csv')
recoveries = pd.read_csv('https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_recovered_global.csv')
confirmed.head(5)
deaths.head(5)
recoveries.head(5)<jupyter_output><empty_output><jupyter_text>Transforming Wide to Long formatData sources from CSSE are in wide format, which is not ideal to work in Tableau. Therefore, a major task in data preprocessing is to transform these data into long format.<jupyter_code># Transform wide format to long format
confirmed = pd.melt(confirmed, id_vars=confirmed.columns[:4], value_vars = confirmed.columns[4:], var_name = 'date', value_name = 'confirmed')
deaths = pd.melt(deaths, id_vars=deaths.columns[:4], value_vars = deaths.columns[4:], var_name = 'date', value_name = 'deaths')
recoveries = pd.melt(recoveries, id_vars=recoveries.columns[:4], value_vars = recoveries.columns[4:], var_name = 'date', value_name = 'recoveries')
confirmed.head(5)
deaths.head(5)
recoveries.head(5)<jupyter_output><empty_output><jupyter_text>Combining tablesThe next step is to combine confirmed, deaths and recoveries tables into a single one for more convenient analysis.One problem emerges, however, with Canada. The `confirmed` & `deaths` tables present Canada data by `Province/State`, while the `recoveries` table only displays the total number of cases in the whole country.This conflict will need to be addressed first before combining the tables together as unmatched join keys will be omitted.<jupyter_code># Summarize confirmed and deaths data by date
confirmed_canada = confirmed[confirmed['Country/Region'] == 'Canada'].groupby('date').sum()[['confirmed']]
deaths_canada = deaths[deaths['Country/Region'] == 'Canada'].groupby('date').sum()[['deaths']]

# Extract columns from recoveries table
recoveries_canada = recoveries[recoveries['Country/Region'] == 'Canada']
canada_template = recoveries_canada[recoveries_canada.columns[:-1]].reset_index(drop=True)

# Join aggrregated confirmed and deaths data with extracted columns
confirmed_canada = canada_template.merge(confirmed_canada, how='inner', left_on='date', right_index=True)
deaths_canada = canada_template.merge(deaths_canada, how='inner', left_on='date', right_index=True)

# Add the agrregated data for Canada back to confirmed and deaths table
confirmed = confirmed[confirmed['Country/Region'] != 'Canada'].append(confirmed_canada)
deaths = deaths[deaths['Country/Region'] != 'Canada'].append(deaths_canada)
# Join confirmed, deaths and recoveries data together
data = confirmed.merge(deaths, how='inner',on=['Country/Region','Province/State','date']).merge(recoveries, how='inner',on=['Country/Region','Province/State','date'])
data = data[['Province/State','Country/Region','date','Lat','Long','confirmed','deaths','recoveries']]
data.sample(10)<jupyter_output><empty_output><jupyter_text>Population DataOne metric used in the Covid-19 dashboard is infection rate: $confirmed / population$. Countries' population is not available in the CSSE dataset so we will need to combine with another source. Source: [<NAME>](https://www.kaggle.com/tanuprabhu/population-by-country-2020)One very common problems when combining different data sources is unmatched value names.<jupyter_code># Read dataset
population = pd.read_csv('./data/population.csv')
population.sample(10)
for c in data['Country/Region'].unique():
  if c not in population['Country (or dependency)'].unique():
    print(c)<jupyter_output>Burma
Congo (Brazzaville)
Congo (Kinshasa)
Cote d'Ivoire
Czechia
Diamond Princess
Korea, South
Kosovo
MS Zaandam
Saint Kitts and Nevis
Saint Vincent and the Grenadines
Sao Tome and Principe
Taiwan*
US
West Bank and Gaza<jupyter_text>There are a few of them, Unfortunately they have to be manually replaced.<jupyter_code>country_mapper = {
    'Congo (Brazzaville)': 'Congo',
    'Congo (Kinshasa)': 'Congo',
    "Cote d'Ivoire": "Côte d'Ivoire",
    'Czechia': 'Czech Republic (Czechia)',
    'Korea, South': 'South Korea',
    'Saint Vincent and the Grenadines': 'St. Vincent & Grenadines',
    'Taiwan*': 'Taiwan',
    'US': 'United States',
    'West Bank and Gaza': 'Israel',
    'Saint Kitts and Nevis': 'Saint Kitts & Nevis',
    'Burma': 'Myanmar',
    'Sao Tome and Principe': 'Sao Tome & Principe'
}

data['Country/Region'] = data['Country/Region'].replace(country_mapper)
data.index = data['Country/Region']
# Export data
data.to_csv('covid19.csv')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12120654772852676, 'fraction_numerical': 0.013426521979032555, 'mean_word_length': 6.950292397660819, 'pattern_counts': {'":': 1, '<': 22, '<?xml version=': 0, '>': 22, 'https://': 6, 'lorem ipsum': 0, 'www.': 1, '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': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '352120', 'license': 'MIT', 'n_tokens_mistral': 1836, 'n_tokens_neox': 1721, 'n_words': 441, 'path': 'COVIDdash/Covid-19 Data Preprocessing.ipynb', 'repo_name': 'CoderAryanAnand/fun_projects', 'size': '5453'}