<jupyter_start><jupyter_text>2019 Novel Coronavirus (SARS-CoV-2) and COVID-19 Unpivoted DataThe following script takes data from the repository of the 2019 Novel Coronavirus Visual Dashboard operated by Johns Hopkins University's Center for Systems Science and Engineering (JHU CSSE). It will apply necessary cleansing/reformatting to make it use in traditional relational databases and data visualization tools.<jupyter_code>import pandas as pd
import os
from datetime import datetime, date, timedelta
import pycountry
from copy import deepcopy
# papermill parameters
output_folder = "../output/"<jupyter_output><empty_output><jupyter_text>Data until 22MAR2020 is stored in a cache. This is collated and reshaped data from previous days.<jupyter_code>pre_2203_data = pd.read_csv("https://s3-us-west-1.amazonaws.com/starschema.covid/CSSEGISandData_COVID-19_until_0322.csv",keep_default_na=False)<jupyter_output><empty_output><jupyter_text>Daily reports from and including 23MAR2020 are downloaded from the JHU CSSE GIS and Data Github repository.<jupyter_code>def urls():
    return [template.format(month=dt.month, day=dt.day, year=dt.year) for dt in dates]
def retrieve_and_merge():
    dates = [date(year=2020, month=3, day=23) + timedelta(n) for n in range(int((datetime.now().date() - datetime(year=2020, month=3, day=23).date()).days))]
    template = "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_daily_reports/{month:02d}-{day:02d}-{year}.csv"
    
    res = pd.DataFrame()
    
    for dt in dates:
        df = pd.read_csv(template.format(year=dt.year,
                                         month=dt.month,
                                         day=dt.day),keep_default_na=False)
        df["Date"] = dt
        res = res.append(df, ignore_index=True)
    return res.melt(id_vars=[col for col in res.columns if col not in ["Confirmed", "Deaths", "Recovered", "Active"]],
                    var_name="Case_Type",
                    value_name="Cases").drop(["Last_Update"], axis=1).rename(columns={"Long_": "Long",
                                                                                      "Country_Region": "Country/Region",
                                                                                      "Province_State": "Province/State"})
df = retrieve_and_merge()<jupyter_output><empty_output><jupyter_text>The original dataset stores the number of `Cases` for a given day in columns. This is not useful for reporting, thus we move these date columns to rows:  Data Quality We use `pycountry` to resolve geographies. A number of states have inconsistent naming or special characters, such as `Taiwan*`. These are normalised through a replacement `dict` with ISO3166-1 compliant names. Data is then aggregated for each division by date and case type.<jupyter_code>changed_names = {
    "Holy See": "Holy See (Vatican City State)",
    "Vatican City": "Holy See (Vatican City State)",
    "Hong Kong SAR": "Hong Kong",
    "Iran (Islamic Republic of)": "Iran, Islamic Republic of",
    "Iran": "Iran, Islamic Republic of",
    "Macao SAR": "Macao",
    "Macau": "Macao",
    "Republic of Korea": "Korea, Republic of",
    "South Korea": "Korea, Republic of",
    "Korea, South": "Korea, Republic of",
    "Republic of Moldova": "Moldova, Republic of",
    "Russia": "Russian Federation",
    "Saint Martin": "Sint Maarten (Dutch part)",
    "St. Martin": "Sint Maarten (Dutch part)",
    "Taipei and environs": "Taiwan, Province of China",
    "Vietnam": "Viet Nam",
    "occupied Palestinian territory": "Palestine, State of",
    "Taiwan*": "Taiwan, Province of China",
    "Congo (Brazzaville)": "Congo",
    "Congo (Kinshasa)": "Congo, The Democratic Republic of the",
    "Gambia, The": "Gambia",
    "The Gambia": "Gambia",
    "Tanzania": "Tanzania, United Republic of",
    "US": "United States",
    "Curacao": "Curaçao",
    "Brunei": "Brunei Darussalam",
    "Cote d'Ivoire": "Côte d'Ivoire",
    "Moldova": "Moldova, Republic of",
    "The Bahamas": "Bahamas",
    "Venezuela": "Venezuela, Bolivarian Republic of",
    "Bolivia": "Bolivia, Plurinational State of",
    "East Timor": "Timor-Leste",
    "Cape Verde": "Cabo Verde"
}

def normalize_names(df):
    df["Country/Region"] = df["Country/Region"].replace(changed_names)
    df["Cases"] = df["Cases"].replace('',0).astype(int)
        
    return(df.groupby(by=["Country/Region","Province/State", "Date", "Case_Type"], as_index=False).agg({"Cases": "sum", "Long": "first", "Lat": "first"}))
df = normalize_names(df)<jupyter_output><empty_output><jupyter_text>Adding ISO3166-1 and ISO3166-2 identifiers To facilitate easy recognition, ISO3166-1 identifiers are added to all countries and ISO3166-2 identifiers are added where appropriate. This is the case where subregional data exists:* Australia* Canada* France (`France` for metropolitan France, separate regions for DOM/TOMs* PRC* US* UK (the `UK` province identifier encompasses only Great Britain and Northern Ireland, other dependencies reporting to the UK authorities are separate subdivisions)* The Kingdom of the Netherlands (`Netherlands` encompasses the constituent country of the Netherlands, and the other constituent countries register cases as separate provinces of the Kingdom of the Netherlands)<jupyter_code>def resolve_iso3166_1(row):
    if row["Country/Region"] is not "Cruise Ship":
        if pycountry.countries.get(name=row["Country/Region"]):
            row["ISO3166-1"] = pycountry.countries.get(name=row["Country/Region"]).alpha_2
        else:
            row["ISO3166-1"] = ""
    return row
df = df.apply(resolve_iso3166_1, axis=1)<jupyter_output><empty_output><jupyter_text>We then encode level 2 IDs:<jupyter_code>fr_subdivisions = {"France": "FR",
                       "French Guiana": "GF",
                       "French Polynesia": "PF",
                       "Guadeloupe": "GUA",
                       "Mayotte": "YT",
                       "Reunion": "RE",
                       "Saint Barthelemy": "BL",
                       "St Martin": "MF"}

nl_subdivisions = {"Netherlands": "NL",
                   "Aruba": "AW",
                   "Curacao": "CW"}

cn_subdivisions = {'Jilin': 'CN-JL',
 'Xizang': 'CN-XZ',
 'Anhui': 'CN-AH',
 'Jiangsu': 'CN-JS',
 'Yunnan': 'CN-YN',
 'Beijing': 'CN-BJ',
 'Jiangxi': 'CN-JX',
 'Zhejiang': 'CN-ZJ',
 'Chongqing': 'CN-CQ',
 'Liaoning': 'CN-LN',
 'Fujian': 'CN-FJ',
 'Guangdong': 'CN-GD',
 'Inner Mongolia': 'CN-NM',
 'Gansu': 'CN-GS',
 'Ningxia': 'CN-NX',
 'Guangxi': 'CN-GX',
 'Qinghai': 'CN-QH',
 'Guizhou': 'CN-GZ',
 'Sichuan': 'CN-SC',
 'Henan': 'CN-HA',
 'Shandong': 'CN-SD',
 'Hubei': 'CN-HB',
 'Shanghai': 'CN-SH',
 'Hebei': 'CN-HE',
 'Shaanxi': 'CN-SN',
 'Hainan': 'CN-HI',
 'Shanxi': 'CN-SX',
 'Tianjin': 'CN-TJ',
 'Heilongjiang': 'CN-HL',
 'Hunan': 'CN-HN',
 'Xinjiang': 'CN-XJ',
 'Tibet': "CN-XZ"}

uk_subdivisions = {"United Kingdom": "UK",
                   "Cayman Islands": "KY",
                   "Channel Islands": "CHA",
                   "Gibraltar": "GI",
                   "Montserrat": "MS"}

subdivisions = {
    "AU": {subdivision.name: subdivision.code.replace("AU-", "") for subdivision in pycountry.subdivisions.get(country_code="AU")},
    "CA": {subdivision.name: subdivision.code.replace("CA-", "") for subdivision in pycountry.subdivisions.get(country_code="CA")},
    "US": {subdivision.name: subdivision.code.replace("US-", "") for subdivision in pycountry.subdivisions.get(country_code="US")},
    "GB": uk_subdivisions,
    "CN": cn_subdivisions,
    "NL": nl_subdivisions,
    "FR": fr_subdivisions
}
countries_with_subdivisions = list(subdivisions.keys())

def resolve_iso3166_2(row):
    if row["ISO3166-1"] in countries_with_subdivisions:
        row["ISO3166-2"] = subdivisions[row["ISO3166-1"]].get(row["Province/State"])
    else:
        row["ISO3166-2"] = ""
    return row
df = df.apply(resolve_iso3166_2, axis=1)<jupyter_output><empty_output><jupyter_text>Calculating case changes We concatenate the new import data frame, `df`, with the pre-23MAR2020 data to facilitate calculating differences, and sort it by primary keys and `Date`.<jupyter_code>result = pre_2203_data.append(df, sort="False")
result["Date"] = pd.to_datetime(result["Date"])<jupyter_output><empty_output><jupyter_text>Next, we sort the data by primary keys and `Date` to ensure we can add a `Difference` column as a window function.<jupyter_code>result = result.sort_values(by=["Country/Region", "Province/State", "Case_Type", "Date"], ascending=True)
result["Difference"] = result["Cases"] - result.groupby(["Country/Region", "Province/State", "Case_Type"])["Cases"].shift(periods=1)
result["Difference"] = result["Difference"].fillna(0)<jupyter_output><empty_output><jupyter_text>Adding timestamp Before we save the file locally, we add the `Last_Update_Date` in `UTC` time zone.<jupyter_code>result["Last_Update_Date"] = datetime.utcnow()<jupyter_output><empty_output><jupyter_text>Output Finally, we store the output in the `output` folder as `JHU_COVID-19.csv` as an unindexed CSV file.<jupyter_code>result.to_csv(output_folder + "JHU_COVID-19.csv", index=False, columns=["Country/Region",
                                                                        "Province/State",
                                                                        "Date",
                                                                        "Case_Type",
                                                                        "Cases",
                                                                        "Long",
                                                                        "Lat",
                                                                        "ISO3166-1",
                                                                        "ISO3166-2",
                                                                        "Difference",
                                                                        "Last_Update_Date"])<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13616979073688387, 'fraction_numerical': 0.016463354160468114, 'mean_word_length': 2.795257809559654, 'pattern_counts': {'":': 63, '<': 41, '<?xml version=': 0, '>': 41, 'https://': 2, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 43, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '666470', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 3276, 'n_tokens_neox': 3036, 'n_words': 790, 'path': 'notebooks/JHU_COVID-19.ipynb', 'repo_name': 'JoaoRodrigues/COVID-19-data', 'size': '10083'}