<jupyter_start><jupyter_text>WHO Coronavirus disease (COVID-2019) 24 hour reports24 hour report from https://covid19.who.int/table<jupyter_code>import pandas as pd
import pycountry
import requests
import os
import re
import numpy
from datetime import datetime
# papermill parameters
output_folder = "../output/"
url = "https://covid19.who.int/WHO-COVID-19-global-table-data.csv"
df = pd.read_csv(url)
df["Date"] = datetime.utcnow().strftime("%Y-%m-%d")
df["Transmission Classification"] = ""
df["Name"] = df["Name"].str.replace("\[1\]", "")
df["Name"] = df["Name"].replace(r"(.*)\s+\(.*\)", r"\1", regex=True)
df["ISO3166_1"] = ""
countries = list(df["Name"].unique())
for name in countries:
    search_name = name
    if name == "Global":
        continue
    elif name == "The United Kingdom":
        search_name = "United Kingdom"
    elif name == "United States of America":
        search_name = "United States"
    elif name == "occupied Palestinian territory, including east Jerusalem":
        search_name = "Jerusalem"
    elif name == "Pitcairn Islands":
        search_name = "Pitcairn"
    elif name == "Côte d’Ivoire":
        search_name = "Côte d'Ivoire"
    elif name == "Democratic Republic of the Congo":
        search_name = "Congo, The Democratic Republic of the"
    elif name == "United States Virgin Islands":
        search_name = "Virgin Islands, U.S."
    
    
    try:
        pyc = pycountry.countries.get(name=search_name)
        
        if pyc:
            df["ISO3166_1"].loc[name == df["Name"]] = pyc.alpha_2
            df["Name"].loc[name == df["Name"]] = pyc.name
            continue
        try:
            pyc_list = pycountry.countries.search_fuzzy(search_name)
            if len(pyc_list):

                df["ISO3166_1"].loc[name == df["Name"]] = pyc_list[0].alpha_2
                df["Name"].loc[name == df["Name"]] = pyc_list[0].name
                continue
        except:
            pass
        pass
    except LookupError:
        try:
            pyc_list = pycountry.countries.search_fuzzy(search_name)
            if len(pyc_list):
                df["ISO3166_1"].loc[name == df["Name"]] = pyc_list[0].alpha_2
                df["Name"].loc[name == df["Name"]] = pyc_list[0].name
                continue
        except:
            pass
        pass
column_map = {
    "Name": "COUNTRY_REGION",
    "Cases - cumulative total": "CASES_TOTAL",
    "Cases - cumulative total per 100000 population": "CASES_TOTAL_PER_100000",
    "Cases - newly reported in last 24 hours": "CASES",
    "Deaths - cumulative total": "DEATHS_TOTAL",
    "Deaths - cumulative total per 100000 population": "DEATHS_TOTAL_PER_100000",
    "Deaths - newly reported in last 24 hours": "DEATHS",
    "Transmission Classification": "TRANSMISSION_CLASSIFICATION",
    "Date": "DATE",
    "ISO3166_1": "ISO3166_1"
}
df = df.rename(columns=column_map)
df.dtypes<jupyter_output><empty_output><jupyter_text>```sqlCREATE TABLE WHO_DAILY_REPORT (    COUNTRY_REGION varchar,    CASES_TOTAL int,    CASES_TOTAL_PER_100000 float,    CASES int,    DEATHS_TOTAL int,    DEATHS_TOTAL_PER_100000 float,    DEATHS int,    TRANSMISSION_CLASSIFICATION varchar,    DATE timestamp_ntz,    ISO3166_1 VARCHAR(2))```<jupyter_code>df.to_csv(output_folder + "WHO_DAILY_REPORT.csv", index=False, columns=column_map.values())<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13507884558167213, 'fraction_numerical': 0.02945551919071705, 'mean_word_length': 3.031175059952038, 'pattern_counts': {'":': 18, '<': 9, '<?xml version=': 0, '>': 9, 'https://': 2, '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': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '499786', 'license': 'BSD-3-Clause', 'n_tokens_mistral': 1212, 'n_tokens_neox': 1099, 'n_words': 243, 'path': 'notebooks/WHO_DAILY_REPORT.ipynb', 'repo_name': 'tfoldi/COVID-19-data', 'size': '3361'}