"""
When it comes to Europe and COVID-19, most headlines are usually about France or Italy and their recent developments. This notebooks take quick peek at the larger scheme, showing the latest situation across the region.

The first part of the notebook does that quite literally by showing a map of Europe based on total number of reported COVID-19 cases. Next this number will be reflected upon population in different countries. In the final part, mortality rate per capita in different countries will be analyzed in similar manner.

This notebook uses two different datasets available online. The COVID-19 data was compiled from the Johns Hopkins University dataset, which is updated regularly on GitHub. The population data used takes advantage of the **Our World In Data** project. External links to both datasets are below:

__[COVID-19 data](https://github.com/CSSEGISandData/COVID-19)__

__[population data (direct link to csv file)](https://covid.ourworldindata.org/data/ecdc/locations.csv)__

The research question of this notebook is to analyze whether the "hotspots" such as Italy, Spain or France actually have most cases per capita or highest mortality rate in Europe. Of course the COVID-19 pandemic is still very much subject to change on daily basis, so claiming anything definitive about it would not be very meaningful a task.

***December 2nd, 2020***<br>
***Jari Peltola***
"""
# import modules
import math
import pandas as pd

import seaborn as sns
import matplotlib.pyplot as plt
%matplotlib inline

import numpy as np
import plotly.express as px

import gc
import re
import folium
import pycountry
url = "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv"

# upload dataset as pandas dataframe
df = pd.read_csv(url)
"""
Since the focus of this notebook is Europe, it is worth noting that European countries are still in charge of regions other than their own actual territory. The following printouts show more about this.
"""
df_uk = df[(df['Country/Region']=='United Kingdom')]
df_uk.head(20)
df_fr = df[(df['Country/Region']=='France')]
df_fr.head(20)
df_de = df[(df['Country/Region']=='Denmark')]
df_de.head(20)
df_ne = df[(df['Country/Region']=='Netherlands')]
df_ne.head(20)
"""
Because we are about to analyze situation in Europe only, we will exclude all regions located outside Europe from our selection.
"""
# drop regions outside Europe
dropped_uk = df[(df['Province/State'].notnull()) & (df['Country/Region']=='United Kingdom')].index
df.drop(dropped_uk, inplace=True)

dropped_fr = df[(df['Province/State'].notnull()) & (df['Country/Region']=='France')].index
df.drop(dropped_fr, inplace=True)

dropped_de = df[(df['Province/State'].notnull()) & (df['Country/Region']=='Denmark')].index
df.drop(dropped_de, inplace=True)

dropped_ne = df[(df['Province/State'].notnull()) & (df['Country/Region']=='Netherlands')].index
df.drop(dropped_ne, inplace=True)
"""
The latest daily COVID-19 data is always located in the same column, so next we will make a date selection based on that location.
"""
# set latest date
current_date = df.iloc[:,-1]
"""
Now we can use that data on plotting a region map of Europe and COVID-19 cases.
"""
# set map

fig = px.choropleth(df, locations="Country/Region",
                    projection="natural earth", locationmode="country names", title="<b>COVID-19</b>:<br>total number of cases", color = current_date,
                    template="plotly", scope="europe", color_continuous_scale="Reds",range_color=[0,1000000] )

annotations = []

# data source
annotations.append(dict(xref='paper', yref='paper', x=0.88, y=-0.07,
                              xanchor='center', yanchor='top',
                              text='Source: Johns Hopkins University<br>Our World In Data',
                              font=dict(family='arial narrow',
                                        size=8,
                                        color='rgb(96,96,96)'),
                              showarrow=False))

fig.update_layout(annotations=annotations)
   
))

fig.update_layout(
    font=dict(family='calibri',
                                size=12,
                                color='rgb(64,64,64)'))   

fig.update_layout(coloraxis_colorbar=dict(
    title="cases"    
))

fig.show()
"""
To get the per capita number of COVID-19 cases in different countries, we need population data mentioned in the beginning.
"""
# load second dataset
url_two = "https://covid.ourworldindata.org/data/ecdc/locations.csv"

# upload dataset as pandas dataframe
df_population = pd.read_csv(url_two)

# select and drop original columns relevant to task at hand
cols = ['countriesAndTerritories', 'population_year']
df_population = df_population.drop(cols, axis=1)

# rename column for merge
df_population.rename(columns = {'location':'Country/Region'}, inplace = True) 

# select European countries only
df_population_eu =  df_population[(df_population['continent'] == 'Europe')]

df_population_eu.head(10)
# check unique locations
df_population_eu['Country/Region'].unique()
"""
Next we merge the population data with COVID-19 case data.
"""
# merge dataframe on Country/Region column with left join 
df_top_eu = pd.merge(df, df_population_eu, on='Country/Region', how='left')

# choose Europe as continent
df_top_eu = df_top_eu[(df_top_eu['continent']=='Europe')]

# select and drop original columns relevant to task at hand
col = ['Province/State']
df_top_eu = df_top_eu.drop(col, axis=1)

df_top_eu.head(20)
"""
With the merged dataset it is now possible to calculate per capita number of COVID-19 cases.
"""
# values to two lists
# list_1 consists of values from the latest date (third last column)
list_1 = df_top_eu.iloc[:,-3].values.tolist()
list_2 = df_top_eu['population'].values.tolist()

# empty list
RelPop = []

# function to calculate relative infection rate using two lists of values
def relative_population(x1, x2): 
                    result =  [(x1 / x2 * 100) for (x1, x2) in zip(list_1,list_2)] 
                    RelPop.append(result)   
            
# execute function on list values            
relative_population (list_1,list_2)

# flatten results list
RelPop = np.array(RelPop).flatten()

# round to four digits
RelPop = np.round(RelPop, 4)

#create new column
df_top_eu['CasesPerCapita'] = np.array(RelPop)

# values to descending order
df_top_eu = df_top_eu.sort_values(by ='CasesPerCapita', ascending=False)

df_top_eu.head(10)
"""
Next two new dataframes will be created, based on the CasesPerCapita value. First dataframe will include ten countries with highest per capita cases, whereas the second dataframe includes ten lowest countries.
"""
# create Top 10 nations on both ends
df_most_eu = df_top_eu[:10]  
# same as df.head(10)
df_least_eu = df_top_eu[-10:] 
# same as df.tail(10)

# values to asscending order
df_most_eu = df_most_eu.sort_values(by ='CasesPerCapita', ascending=True)
df_least_eu = df_least_eu.sort_values(by ='CasesPerCapita', ascending=True)

df_most_eu.head(10)
df_least_eu.head(10)
"""
Now we can plot the two dataframes and actually see the situation.
"""
# define parameters
fig = px.bar(df_most_eu, x='Country/Region', y ='CasesPerCapita', text = 'CasesPerCapita', color= 'CasesPerCapita', height=600)

# set graphics
fig.data[0].marker.line.width = 0.5
fig.data[0].marker.line.color = "black"

fig.update_traces(texttemplate='%{text}', textposition='outside')
fig.update_layout(uniformtext_mode='hide')  

fig.update_layout(
    xaxis=dict(
        showline=True,
        showgrid=False,
        showticklabels=True,
        linecolor='rgb(204, 204, 204)',
        linewidth=2,
        ticks='outside',
        tickfont=dict(
            family='Arial',
            size=12,
            color='rgb(82, 82, 82)',
        )))

fig.update_layout(
    yaxis=dict(
        showline=True,
        linecolor='rgb(204, 204, 204)',
        linewidth=2,
        ticks='outside',
        tickfont=dict(
            family='Arial',
            size=12,
            color='rgb(82, 82, 82)',
        )))

# set annotations
annotations = []

# data source
annotations.append(dict(xref='paper', yref='paper', x=0.88, y=-0.16,
                              xanchor='center', yanchor='top',
                              text='Data: Johns Hopkins University,<br>Our World In Data',
                              font=dict(family='arial narrow',
                                        size=8,
                                        color='rgb(96,96,96)'),
                              showarrow=False))

fig.update_layout(annotations=annotations)

# set plot title
fig.update_layout(
    title='<b>COVID-19</b>:<br>highest number of cases per capita (0-100%)',
                font=dict(family='calibri',
                                size=12,
                                color='rgb(64,64,64)'))

# set axe titles etc.
fig.update_xaxes(showgrid=True, gridwidth=1, gridcolor='LightGrey')
fig.update_yaxes(showgrid=True, gridwidth=1, gridcolor='LightGrey')

fig.update_yaxes(title_text='Cases Per Capita')
fig.update_xaxes(title_text='Country')

fig.update_yaxes(title_font=dict(size=14))
fig.update_xaxes(title_font=dict(size=14))

fig.update_layout(coloraxis_colorbar=dict(
    title="cases per capita"    
))

fig.update_layout(xaxis_showgrid=False)

# show figure
fig.show()
#plot second datafraem

# define parameters
fig = px.bar(df_least_eu, x='Country/Region', y='CasesPerCapita', text = 'CasesPerCapita', color= 'CasesPerCapita', height=600)

# set graphics
fig.data[0].marker.line.width = 0.5
fig.data[0].marker.line.color = "black"

fig.update_traces(texttemplate='%{text}', textposition='outside')
fig.update_layout(uniformtext_mode='hide')  

fig.update_layout(
    xaxis=dict(
        showline=True,
        showgrid=False,
        showticklabels=True,
        linecolor='rgb(204, 204, 204)',
        linewidth=2,
        ticks='outside',
        tickfont=dict(
            family='Arial',
            size=12,
            color='rgb(82, 82, 82)',
        )))

fig.update_layout(
    yaxis=dict(
        showline=True,
        linecolor='rgb(204, 204, 204)',
        linewidth=2,
        ticks='outside',
        tickfont=dict(
            family='Arial',
            size=12,
            color='rgb(82, 82, 82)',
        )))

# set annotations
annotations = []

# data source
annotations.append(dict(xref='paper', yref='paper', x=0.88, y=-0.12,
                              xanchor='center', yanchor='top',
                              text='Data: Johns Hopkins University,<br>Our World In Data',
                              font=dict(family='arial narrow',
                                        size=8,
                                        color='rgb(96,96,96)'),
                              showarrow=False))

fig.update_layout(annotations=annotations)

# set plot title
fig.update_layout(
    title='<b>COVID-19</b>:<br>lowest number of cases per capita (0-100%)',
                font=dict(family='calibri',
                                size=12,
                                color='rgb(64,64,64)'))

# set axe titles etc.
fig.update_xaxes(showgrid=True, gridwidth=1, gridcolor='LightGrey')
fig.update_yaxes(showgrid=True, gridwidth=1, gridcolor='LightGrey')

fig.update_yaxes(title_text='Cases Per Capita')
fig.update_xaxes(title_text='Country')

fig.update_yaxes(title_font=dict(size=14))
fig.update_xaxes(title_font=dict(size=14))

fig.update_layout(coloraxis_colorbar=dict(
    title="cases per capita"    
))

fig.update_layout(xaxis_showgrid=False)

# show figure
fig.show()
"""
In the final part of this notebook, we will add mortality rate to all this. To achieve the task, a new dataset consisting on reported COVID-19 deaths is used.
"""
# load dataset from Github
url_three = "https://raw.githubusercontent.com/CSSEGISandData/COVID-19/master/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_deaths_global.csv"

# upload dataset as pandas dataframe
df = pd.read_csv(url_three)

df.head(10)
"""
In similar manner to reported COVID-19 cases data, regions outside Europe will be excluded. Also, a new dataframe "de_eu_data" will be created. It will include selected columns from our earlier calculations on per capita cases etc.
"""
# drop regions outside Europe
dropped_uk = df[(df['Province/State'].notnull()) & (df['Country/Region']=='United Kingdom')].index
df.drop(dropped_uk, inplace=True)

dropped_fr = df[(df['Province/State'].notnull()) & (df['Country/Region']=='France')].index
df.drop(dropped_fr, inplace=True)

dropped_de = df[(df['Province/State'].notnull()) & (df['Country/Region']=='Denmark')].index
df.drop(dropped_de, inplace=True)

dropped_ne = df[(df['Province/State'].notnull()) & (df['Country/Region']=='Netherlands')].index
df.drop(dropped_ne, inplace=True)

# create new dataframe
df_eu_data = df_top_eu [['Country/Region', 'population', 'continent', 'CasesPerCapita']].copy()

# merge new dataframe on Country/Region column with left join 
df_mortality_eu = pd.merge(df, df_eu_data, on='Country/Region', how='left')

# choose Europe as continent
df_mortality_eu = df_mortality_eu[(df_mortality_eu['continent']=='Europe')]

# select and drop original columns relevant to task at hand
col = ['Province/State']
df_mortality_eu = df_mortality_eu.drop(col, axis=1)

df_mortality_eu.head()
"""
Similar to per capita COVID-19, the mortality rate in different countries can now be calculated.
"""
# values to two lists
list_1 = df_mortality_eu.iloc[:,-4].values.tolist()
list_2 = df_mortality_eu['population'].values.tolist()

# empty list
RelPop = []

# function to calculate relative mortality rate using two lists of values
def relative_population(x1, x2): 
                    result =  [(x1 / x2 * 100) for (x1, x2) in zip(list_1,list_2)] 
                    RelPop.append(result)   
            
# execute function on list values            
relative_population (list_1,list_2)

# flatten results list
RelPop = np.array(RelPop).flatten()

# round to four digits
RelPop = np.round(RelPop, 4)

#create new column
df_mortality_eu['MortalityPerCapita'] = np.array(RelPop)

# values to descending order
df_mortality_eu = df_mortality_eu.sort_values(by ='MortalityPerCapita', ascending=False)

df_mortality_eu.head(10)
"""
Again we concentrate on lowest and highest relative values.
"""
# create Top 10 nations on both ends
df_most_d_eu = df_mortality_eu[:10]  
# same as df.head(10)
df_least_d_eu = df_mortality_eu[-10:] 
# same as df.tail(10)

# values to asscending order
df_most_d_eu = df_most_d_eu.sort_values(by ='MortalityPerCapita', ascending=True)
df_least_d_eu = df_least_d_eu.sort_values(by ='MortalityPerCapita', ascending=True)

df_most_d_eu.head(10)
df_least_d_eu.head(10)
"""
The next two plots gives us visual outlook on COVID-19 mortality rate in different European countries.
"""
# define parameters
fig = px.bar(df_most_d_eu, x='Country/Region', y ='MortalityPerCapita', text = 'MortalityPerCapita', color= 'MortalityPerCapita', height=600)

# set graphics
fig.data[0].marker.line.width = 0.5
fig.data[0].marker.line.color = "black"

fig.update_traces(texttemplate='%{text}', textposition='outside')
fig.update_layout(uniformtext_mode='hide')  

fig.update_layout(
    xaxis=dict(
        showline=True,
        showgrid=False,
        showticklabels=True,
        linecolor='rgb(204, 204, 204)',
        linewidth=2,
        ticks='outside',
        tickfont=dict(
            family='Arial',
            size=12,
            color='rgb(82, 82, 82)',
        )))

fig.update_layout(
    yaxis=dict(
        showline=True,
        linecolor='rgb(204, 204, 204)',
        linewidth=2,
        ticks='outside',
        tickfont=dict(
            family='Arial',
            size=12,
            color='rgb(82, 82, 82)',
        )))

# set annotations
annotations = []

# data source
annotations.append(dict(xref='paper', yref='paper', x=0.88, y=-0.22,
                              xanchor='center', yanchor='top',
                              text='Data: Johns Hopkins University,<br>Our World In Data',
                              font=dict(family='arial narrow',
                                        size=8,
                                        color='rgb(96,96,96)'),
                              showarrow=False))

fig.update_layout(annotations=annotations)

# set plot title
fig.update_layout(
    title='<b>COVID-19</b>:<br>highest mortality rate in Europe (0-100%)',
                font=dict(family='calibri',
                                size=12,
                                color='rgb(64,64,64)'))

# set axe titles etc.
fig.update_xaxes(showgrid=True, gridwidth=1, gridcolor='LightGrey')
fig.update_yaxes(showgrid=True, gridwidth=1, gridcolor='LightGrey')

fig.update_yaxes(title_text='Mortality Per Capita')
fig.update_xaxes(title_text='Country')

fig.update_yaxes(title_font=dict(size=14))
fig.update_xaxes(title_font=dict(size=14))

fig.update_layout(coloraxis_colorbar=dict(
    title="mortality per capita"    
))

fig.update_layout(xaxis_showgrid=False)

# show figure
fig.show()
# define parameters
fig = px.bar(df_least_d_eu, x='Country/Region', y='MortalityPerCapita', text = 'MortalityPerCapita', color= 'MortalityPerCapita', height=600)

# set graphics
fig.data[0].marker.line.width = 0.5
fig.data[0].marker.line.color = "black"

fig.update_traces(texttemplate='%{text}', textposition='outside')
fig.update_layout(uniformtext_mode='hide')  

fig.update_layout(
    xaxis=dict(
        showline=True,
        showgrid=False,
        showticklabels=True,
        linecolor='rgb(204, 204, 204)',
        linewidth=2,
        ticks='outside',
        tickfont=dict(
            family='Arial',
            size=12,
            color='rgb(82, 82, 82)',
        )))

fig.update_layout(
    yaxis=dict(
        showline=True,
        linecolor='rgb(204, 204, 204)',
        linewidth=2,
        ticks='outside',
        tickfont=dict(
            family='Arial',
            size=12,
            color='rgb(82, 82, 82)',
        )))

# set annotations
annotations = []

# data source
annotations.append(dict(xref='paper', yref='paper', x=0.88, y=-0.12,
                              xanchor='center', yanchor='top',
                              text='Data: Johns Hopkins University,<br>Our World In Data',
                              font=dict(family='arial narrow',
                                        size=8,
                                        color='rgb(96,96,96)'),
                              showarrow=False))

fig.update_layout(annotations=annotations)

# set plot title
fig.update_layout(
    title='<b>COVID-19</b>:<br>lowest mortality rate in Europe (0-100%)',
                font=dict(family='calibri',
                                size=12,
                                color='rgb(64,64,64)'))

# set axe titles etc.
fig.update_xaxes(showgrid=True, gridwidth=1, gridcolor='LightGrey')
fig.update_yaxes(showgrid=True, gridwidth=1, gridcolor='LightGrey')

fig.update_yaxes(title_text='Mortality Per Capita')
fig.update_xaxes(title_text='Country')

fig.update_yaxes(title_font=dict(size=14))
fig.update_xaxes(title_font=dict(size=14))

fig.update_layout(coloraxis_colorbar=dict(
    title="mortality per capita"    
))

fig.update_layout(xaxis_showgrid=False)

# show figure
fig.show()
"""
As mentioned before, COVID-19 pandemic and the data related to it changes on daily basis. What we can see however is that sometimes a single COVID-19 case or death in small population has higher relative statistical significance than in more populated countries. One aspect worth further inspecting would be to analyze whether the relatively high COVID-19 per capita cases and deaths in Belgium have anything to do with the fact that Belgium - especially Brussels - is the decision-making hub for the European Union along with Strasbourg in France. This of course means constant travel and movement of people across Europe also during national and regional lockdowns etc. However, all this is speculation and cannot directly be deducted from data used in this notebook.
"""