<jupyter_start><jupyter_text>Generate CSVs from COVID-19 WHO data  Dependencies<jupyter_code>import pandas as pd
import os
from datetime import datetime

INPUT_PATH = 'input/'
OUTPUT_PATH = 'public/data/'

POPULATION_CSV_PATH = os.path.join(INPUT_PATH, 'un', 'population_thousands.csv')
REGIONS_CSV_PATH = os.path.join(INPUT_PATH, 'regions.csv')
DEATHS_CSV_PATH = os.path.join(INPUT_PATH, 'deaths.csv')
CASES_CSV_PATH = os.path.join(INPUT_PATH, 'cases.csv')
!mkdir -p $OUTPUT_PATH<jupyter_output><empty_output><jupyter_text>Load the data Population figures<jupyter_code>df_pop = pd.read_csv(POPULATION_CSV_PATH)
# WHO includes counts for Kosovo in Serbia
# https://www.who.int/countries/srb/en/
df_pop['entity'] = df_pop['entity'].replace({ 'Serbia (including Kosovo)': 'Serbia' })
df_pop['population'] = df_pop['population_thousands'] * 1000
def find_closest_year(df, year=2020):
    df = df.copy()
    df['year'] = df['year'].sort_values(ascending=True)
    return df.loc[df['year'].map(lambda x: abs(x - 2020)).idxmin()]
current_pop = pd.DataFrame([
    find_closest_year(df, 2020)
    for loc, df in df_pop.groupby('entity')
]) \
.dropna() \
.drop(columns=['population_thousands']) \
.rename(columns={'entity': 'location'})<jupyter_output><empty_output><jupyter_text>WHO regions & OWID names<jupyter_code>df_regions = pd.read_csv(REGIONS_CSV_PATH)
df_regions = df_regions.merge(
    current_pop[['location', 'population']],
    how='left',
    left_on='OWID Country Name',
    right_on='location'
).drop(columns=['location'])
# Convert population figures to ints
df_regions['population'] = df_regions['population'].round().astype('Int64')
# Add a more descriptive header
df_regions = df_regions.rename(columns={'population': 'Population (UN 2020 projection)'})<jupyter_output><empty_output><jupyter_text>Transform the "wide" format into "long" format, which is easier to work with.<jupyter_code>def melt_csv(df, var_name):
    return df.melt(
        id_vars=df.columns[0], 
        value_vars=df.columns[1:], 
        var_name='location', 
        value_name=var_name
    ).dropna()
df_deaths = melt_csv(pd.read_csv(DEATHS_CSV_PATH, header=1).rename(columns={ 'Date': 'date' }), 'total_deaths')
df_cases = melt_csv(pd.read_csv(CASES_CSV_PATH, header=1).rename(columns={ 'Date': 'date' }), 'total_cases')<jupyter_output><empty_output><jupyter_text>Convert all numbers from floating point to integers:<jupyter_code>df_deaths['total_deaths'] = df_deaths['total_deaths'].astype('Int64')
df_cases['total_cases'] = df_cases['total_cases'].astype('Int64')<jupyter_output><empty_output><jupyter_text>Calculations Join cases & deaths into one dataframe<jupyter_code>df_merged = df_cases.merge(
    df_deaths, 
    how='outer', 
    left_on=['date', 'location'], 
    right_on=['date', 'location']
).sort_values(by=['location', 'date'])<jupyter_output><empty_output><jupyter_text>Standardize names to OWID names<jupyter_code>df_regions_merged = df_regions.merge(
    df_merged[['location']].drop_duplicates(),
    how="outer",
    left_on="WHO Country Name",
    right_on="location"
)
df_regions_merged[df_regions_merged['OWID Country Name'].isnull()]
assert(df_regions_merged['OWID Country Name'].isnull().any() == False)
who_name_replace_map = { r['WHO Country Name']: r['OWID Country Name'] for r in df_regions_merged.to_dict('records') }
df_merged['location'] = df_merged['location'].replace(who_name_replace_map)<jupyter_output><empty_output><jupyter_text>Calculate daily cases & deaths<jupyter_code># Convert to Int64 to handle <NA>
df_merged['new_cases'] = df_merged.groupby('location')['total_cases'].diff().astype('Int64')
df_merged['new_deaths'] = df_merged.groupby('location')['total_deaths'].diff().astype('Int64')<jupyter_output><empty_output><jupyter_text>Create a `World` aggregate<jupyter_code>df_global = df_merged.groupby('date').sum().reset_index()
df_global['location'] = 'World'
df_merged = pd.concat([df_merged, df_global], sort=True)<jupyter_output><empty_output><jupyter_text>Calculate per population variables<jupyter_code>df_merged_pop = df_merged.merge(
    current_pop,
    how='left',
    on='location'
)
# These entities do not exist in the UN population dataset but do in the WHO data
df_merged_pop[df_merged_pop['population'].isnull()]['location'].unique()
df_merged_pop['total_cases_per_million'] = df_merged_pop['total_cases'] / (df_merged_pop['population'] / 1e6)
df_merged_pop['total_deaths_per_million'] = df_merged_pop['total_deaths'] / (df_merged_pop['population'] / 1e6)
df_merged_pop['new_cases_per_million'] = df_merged_pop['new_cases'] / (df_merged_pop['population'] / 1e6)
df_merged_pop['new_deaths_per_million'] = df_merged_pop['new_deaths'] / (df_merged_pop['population'] / 1e6)
df_merged = df_merged_pop.drop(columns=['year', 'population'])<jupyter_output><empty_output><jupyter_text>Calculate days since 100th case<jupyter_code>THRESHOLD = 100
DAYS_SINCE_COL_NAME = 'days_since_%sth_case' % THRESHOLD
DAYS_SINCE_COL_NAME_POSITIVE = 'days_since_%sth_case_positive' % THRESHOLD
def get_date_of_nth_case(df, nth):
    try:
        df_gt_nth = df[df['total_cases'] >= nth]
        earliest = df.loc[pd.to_datetime(df_gt_nth['date']).idxmin()]
        return earliest['date']
    except:
        return None
date_of_nth_case = pd.DataFrame([
    (loc, get_date_of_nth_case(df, THRESHOLD)) 
    for loc, df in df_merged.groupby('location')
], columns=['location', 'date_of_nth_case']).dropna()
def inject_days_since(df, ref_date):
    df = df[['date', 'location']].copy()
    df[DAYS_SINCE_COL_NAME] = df['date'].map(lambda date: (pd.to_datetime(date) - pd.to_datetime(ref_date)).days)
    return df
df_grouped = df_merged.groupby('location')
df_days_since_nth_case = pd.concat([
    inject_days_since(df_grouped.get_group(row['location']), row['date_of_nth_case']) 
    for _, row in date_of_nth_case.iterrows()
])
df_merged = df_merged.merge(
    df_days_since_nth_case,
    how='outer',
    on=['date', 'location'],
)
df_merged[DAYS_SINCE_COL_NAME] = df_merged[DAYS_SINCE_COL_NAME].astype('Int64')
df_merged[DAYS_SINCE_COL_NAME_POSITIVE] = df_merged[DAYS_SINCE_COL_NAME] \
    .map(lambda x: x if (pd.notna(x) and x >= 0) else None).astype('Int64')<jupyter_output><empty_output><jupyter_text>Calculate doubling rates<jupyter_code>def get_days_to_double(df, col_name):
    try:
        # verbose because being very careful not to modify original data with dates
        latest = df.loc[pd.to_datetime(df['date']).idxmax()]
        df_lt_half = df[df[col_name] <= (latest[col_name] / 2)]
        half = df_lt_half.loc[pd.to_datetime(df_lt_half['date']).idxmax()]
        return (pd.to_datetime(latest['date']) - pd.to_datetime(half['date'])).days
    except:
        return None
days_to_double_cases = pd.DataFrame([
    (loc, get_days_to_double(df, 'total_cases')) 
    for loc, df in df_merged.groupby('location')
], columns=['location', 'days_to_double_cases'])
days_to_double_cases['days_to_double_cases'] = days_to_double_cases['days_to_double_cases'].astype('Int64')<jupyter_output><empty_output><jupyter_text>Grapher data extract<jupyter_code>df_grapher = df_merged.copy()
df_grapher['date'] = pd.to_datetime(df_grapher['date']).map(lambda date: (date - datetime(2020, 1, 21)).days)
df_grapher = df_grapher[[
    'location', 'date', 
    'new_cases', 'new_deaths', 
    'total_cases', 'total_deaths',
    'new_cases_per_million', 'new_deaths_per_million', 
    'total_cases_per_million', 'total_deaths_per_million',
    DAYS_SINCE_COL_NAME, DAYS_SINCE_COL_NAME_POSITIVE]] \
    .rename(columns={
        'location': 'country',
        'date': 'year',
        'new_cases': 'Daily new confirmed cases of COVID-19',
        'new_deaths': 'Daily new confirmed deaths due to COVID-19',
        'total_cases': 'Total confirmed cases of COVID-19', 
        'total_deaths': 'Total confirmed deaths due to COVID-19',
        'new_cases_per_million': 'Daily new confirmed cases of COVID-19 per million people',
        'new_deaths_per_million': 'Daily new confirmed deaths due to COVID-19 per million people',
        'total_cases_per_million': 'Total confirmed cases of COVID-19 per million people', 
        'total_deaths_per_million': 'Total confirmed deaths due to COVID-19 per million people',
        DAYS_SINCE_COL_NAME: 'Days since the total confirmed cases of COVID-19 reached %s' % THRESHOLD,
        DAYS_SINCE_COL_NAME_POSITIVE: 'Days since the total confirmed cases of COVID-19 reached %s (positive only)' % THRESHOLD,
    
    })<jupyter_output><empty_output><jupyter_text>Inspect the results<jupyter_code># df_merged
# df_merged[df_merged['location'] == 'World']<jupyter_output><empty_output><jupyter_text>Write output files<jupyter_code># Should keep these append-only in case someone external depends on the order
df_merged[[
    'date', 'location',
    'new_cases', 'new_deaths', 
    'total_cases', 'total_deaths']] \
    .to_csv(os.path.join(OUTPUT_PATH, 'full_data.csv'), index=False)
for col_name in [
    'total_cases', 'total_deaths', 
    'new_cases', 'new_deaths',
    'total_cases_per_million', 'total_deaths_per_million', 
    'new_cases_per_million', 'new_deaths_per_million']:
    df_pivot = df_merged.pivot(index='date', columns='location', values=col_name)
    # move World to first column
    cols = df_pivot.columns.tolist()
    cols.insert(0, cols.pop(cols.index('World')))
    df_pivot[cols].to_csv(os.path.join(OUTPUT_PATH, '%s.csv' % col_name))
days_to_double_cases.to_csv(os.path.join(OUTPUT_PATH, 'days_to_double_cases.csv'), index=False)
df_regions.to_csv(os.path.join(OUTPUT_PATH, 'regions.csv'), index=False)
df_grapher.to_csv(os.path.join(OUTPUT_PATH, 'grapher.csv'), index=False)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14366807720094954, 'fraction_numerical': 0.00908246465063474, 'mean_word_length': 6.231343283582089, 'pattern_counts': {'":': 0, '<': 63, '<?xml version=': 0, '>': 64, 'https://': 1, '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': '15', 'dir': 'jupyter-structured-clean-dedup', 'id': '487312', 'license': 'MIT', 'n_tokens_mistral': 3713, 'n_tokens_neox': 3485, 'n_words': 584, 'path': 'covid-19-get-data/notebooks/who.ipynb', 'repo_name': 'BOBO-LU/covid-19-visualization', 'size': '9689'}