<jupyter_start><jupyter_text>Data loading stage<jupyter_code># read the time series data about corona infections, deaths and recoveries worldwide
data_infections = pd.read_csv('../data/time_series_covid_19_confirmed.csv')
data_infections = data_infections.rename(columns={'Country/Region': 'country'})

data_deaths = pd.read_csv('../data/time_series_covid_19_deaths.csv')
data_deaths = data_deaths.rename(columns={'Country/Region': 'country'})

data_recovered = pd.read_csv('../data/time_series_covid_19_recovered.csv')
data_recovered = data_recovered.rename(columns={'Country/Region': 'country'})

data_infections.head()
def reshape_timeseries(data, name):
    # summarize latlong data by averaging over province and state
    agg_dict = {"Lat": "mean", "Long": "mean"}
    
    #sum over the rest
    for col in data.columns[4:]:
        agg_dict[col] = 'sum'
    
    data = data.groupby('country').agg(agg_dict).reset_index() 

    
    # transpose and convert to proper time-series format
    data = data.set_index('country')
    data_timeseries_unstacked = data.iloc[:, 4:].transpose().unstack().reset_index()
    data = data.reset_index()
    result = pd.merge(data_timeseries_unstacked, data[['country', 'Lat', 'Long']], on="country")
    
    result = result.rename(columns={'level_1': 'date', 0: name})
    
    return result
    
data_infections = reshape_timeseries(data_infections, 'infections')
data_deaths = reshape_timeseries(data_deaths, 'deaths')
data_recovered = reshape_timeseries(data_recovered, 'recovered')
data_infections.head()
data_deaths.head()
data_recovered.head()
data = pd.concat([data_infections, data_deaths['deaths'], data_recovered['recovered']], axis=1)
# rename 'US' to 'United States'
data['country'] = data.country.str.replace('US', 'United States')
#for country in data['country'].unique():
#    print(country, (data['country'] == country).sum())
# read the data about medical capacity per country
data_beds = pd.read_csv("../data/beds_per_country.csv")
data_beds = data_beds.rename(columns={'Country/territory': 'country'})
data_beds.head()
# read the data about population by country
data_pop = pd.read_csv("../data/population_by_country.csv")
data_pop = data_pop.rename(columns={'Country (or dependent territory)': 'country'})
data_pop.head()
# read data about latitude and longitude of countries
#data_latlong = pd.read_csv("../data/time_series_covid_19_confirmed.csv")
#data_latlong = data_latlong[['Country/Region', 'Lat', 'Long']]
#data_latlong = data_latlong.rename(columns={'Country/Region': 'country'})
#print(data_latlong.shape)
#data_latlong.head()
# read data bout age-distribution per country
data_age = pd.read_csv("../data/age_per_country.csv")
data_age = data_age.rename(columns={'Country': 'country'})
data_age.head()<jupyter_output><empty_output><jupyter_text>Data filtering stage<jupyter_code># plot the progression of infections and deaths in the Netherlands
fig, ax = plt.subplots(1,1)
fig.set_size_inches(10, 5)
data[data['country'] == "Netherlands"]['infections'].plot(ax=ax)
data[data['country'] == "Netherlands"]['deaths'].plot(ax=ax)
data[data['country'] == "Netherlands"]['recovered'].plot(ax=ax)
plt.title("Corona infections and deaths in the Netherlands")
plt.legend(['Infections', 'Deaths', 'Recovered'])
# plot the progression of infections and deaths in China
fig, ax = plt.subplots(1,1)
fig.set_size_inches(10, 5)
data[data['country'] == "China"]['infections'].plot(ax=ax)
data[data['country'] == "China"]['deaths'].plot(ax=ax)
data[data['country'] == "China"]['recovered'].plot(ax=ax)
plt.title("Corona infections and deaths in China")
plt.legend(['Infections', 'Deaths', 'Recovered'])
# rename 'United Kingdom (more)' to 'United Kingdom'
data_beds['country'] = data_beds['country'].str.replace(r" \(.*\)", "")

# identify the countries occurring in the hospital data
data_beds['country'].unique()
data['country'] = data['country'].str.replace(r" \(.*\)", "").str.replace("*", "")
# identify the countries occurring in the timeseries data
data['country'].unique()
# compute the overlap between the two sets
countries = np.intersect1d(data['country'], data_beds['country'])
countries, countries.shape
# drop countries not in 'countries' array
def drop_unneeded_countries(data, countries):
    data['country'] = data.country.apply(lambda x: np.nan if x not in countries else x)
    # get indices of nonmissing
    data = data[~data['country'].isnull()]
    
    
    return data
# remove unneeded countries from all data
data = drop_unneeded_countries(data, countries)
#data_latlong = drop_unneeded_countries(data_latlong, countries)
# summarize lat long by mean for countries with multiple states/regions
#data_latlong = data_latlong.groupby('country').mean().reset_index()
data_beds = drop_unneeded_countries(data_beds, countries)
data_age = drop_unneeded_countries(data_age, countries)
# remove added tokens from country names
data_pop['country'].head()
data_pop['country'] = data_pop['country'].str.replace(r'\[.\]', '').str.replace('\s\(.*\)', '')
data_pop = drop_unneeded_countries(data_pop, countries)<jupyter_output><empty_output><jupyter_text>Merging stage<jupyter_code># merge all the data by country
data = pd.merge(data, data_beds, on='country')
data = pd.merge(data, data_pop, on='country')
data = pd.merge(data, data_age, on='country')
# drop columns without value
data.columns
#data = data.drop(['GeoId', 'Actual', 'Proportional (%)', 'Rank_x', 'Rank_y', 'Date', 'Source'], axis=1)
data = data.drop(['Actual', 'Proportional (%)', 'Rank_x', 'Rank_y', 'Date', 'Source'], axis=1)
data[data['country'] == "Netherlands"].head()
data[data['country'] == "Netherlands"].tail()<jupyter_output><empty_output><jupyter_text>Cleaning stage<jupyter_code># remove percentage symbol from columns and convert to numeric
data['% of worldpopulation'] = data['% of worldpopulation'].str.replace('%', '').astype(float) / 100
data['age 0 to 14 years[1]'] = data['age 0 to 14 years[1]'].str.replace('%', '').astype(float) / 100
data['age 15 to 64 years[2]'] = data['age 15 to 64 years[2]'].str.replace('%', '').astype(float) / 100
data['age over 65 years[3]'] = data['age over 65 years[3]'].str.replace('%', '').astype(float) / 100
# divide percent value by 100
data['Occupancy (%)[2]'] = data['Occupancy (%)[2]'] / 100
# drop year columns (for now)
data = data.drop(['2013', '2014', '2015', '2016','2017'], axis=1)
# convert 'Population' column to numeric
data['Population'] = data['Population'].str.replace(',', '').str.replace('.', '').astype(float)
data[data['country'] == "Netherlands"].tail()
data['ICU-CCB beds/100,000 inhabitants'] = data['ICU-CCB beds/100,000 inhabitants'].str.replace(r"\[.*\]", "")
data['ICU-CCB beds/100,000 inhabitants'] = data['ICU-CCB beds/100,000 inhabitants'].str.replace(r"\[.+\]\[.+\]", "").astype(float)
# give columns easier names
data = data.rename(columns={'Lat_x': "latitude", 
                             'Long_x': 'longitude', 
                             'Occupancy (%)[2]': 'bed_occupancy',
                             'ICU-CCB beds/100,000 inhabitants':'ICU_beds',
                             '% of worldpopulation': 'population_world',
                            'age 0 to 14 years[1]': 'age_0_to_64',
                            'age 15 to 64 years[2]': 'age_15_to_64',
                            'age over 65 years[3]': 'age_over_65'})
data[data['country'] == "Netherlands"].tail()
# extract month and day from date column
data['month'] = data['date'].str.extract('(.)/').astype(float)
data['day'] = data['date'].str.extract('./(.+)/.').astype(float)
data.to_csv('../data/COVID19_timeseries.csv', index=None)
plt.figure(figsize=(10,5))
sns.heatmap(data.corr())
data.shape
data.country.unique()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.17628826036419992, 'fraction_numerical': 0.01911403848637479, 'mean_word_length': 5.447960033305579, 'pattern_counts': {'":': 2, '<': 17, '<?xml version=': 0, '>': 17, 'https://': 0, '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': '4', 'dir': 'jupyter-structured-clean-dedup', 'id': '475300', 'license': 'MIT', 'n_tokens_mistral': 2710, 'n_tokens_neox': 2541, 'n_words': 552, 'path': 'notebooks/data_preparation.ipynb', 'repo_name': 'paulozk/corona', 'size': '7743'}