<jupyter_start><jupyter_text>*** COVID-19 Vaccination Progress - Data cleaning****  Dataset Overview+ Country - this is the country for which the vaccination information is provided+ Country ISO Code - ISO code for the country;+ Date- date for the data entry; for some of the dates we have only the daily vaccinations, for others, only the (cumulative) total;+ Total number of vaccinations - this is the absolute number of processed vaccinations per country;+ Total number of people vaccinated - a person, depending on the immunization scheme, will receive one or more (typically 2) dosis of a vaccine; at a certain moment, the number of vaccination might be larger than the number of people;+ Total number of people fully vaccinated - this is the number of people that received the entire set of immunization according to the immunization scheme (typically 2); at a certain moment in time, there might be a certain number of people that received one vaccine and another number (smaller) of people that received all vaccines in the scheme;+ Daily vaccinations (raw) - for a certain data entry, the number of vaccination for that date/country; only changed compared to previous day+ Daily vaccinations - for a certain data entry, the number of vaccination for that date/country;+ Total vaccinations per hundred - ratio (in percent) between vaccination number and total population up to the date in the country;+ Total number of people vaccinated per hundred - ratio (in percent) between population immunized and total population up to the date in the country;+ Total number of people fully vaccinated per hundred - ratio (in percent) between population fully immunized and total population up to the date in the country;+ Number of vaccinations per day - number of daily vaccination for that day and country;+ Daily vaccinations per million - ratio (in ppm) between vaccination number and total population for the current date in the country;+ Vaccines used in the country - total number of vaccines used in the country (up to date);+ Source name - source of the information (national authority, international organization, local organization etc.);+ Source website - website of the source of information;<jupyter_code>#common imports:
import numpy as np
import pandas as pd
from datetime import datetime
import os
import matplotlib.dates as md

#import for visualization
from matplotlib.ticker import FuncFormatter
import matplotlib.pyplot as plt
%matplotlib inline
import matplotlib.colors
import seaborn as sns
import plotly.graph_objects as go
import plotly.express as px
#for Worldmap
#import shapefile as shp


#for timeseries:
import statsmodels.formula.api as smf
from statsmodels.tsa.seasonal import seasonal_decompose
from scipy.ndimage import gaussian_filter


from sklearn.metrics import mean_squared_error
from math import sqrt
from statsmodels.tsa.stattools import adfuller,kpss

from statsmodels.tsa.arima_model import ARIMA
from statsmodels.graphics.tsaplots import plot_pacf



#from wordcloud import WordCloud
from pandas_profiling import ProfileReport

sns.set(rc={'figure.figsize':(20,15)})


#suppress pandas future warnings:
import warnings
warnings.simplefilter(action='ignore', category=FutureWarning)

# Where to save the figures
PROJECT_ROOT_DIR = "."
CHAPTER_ID = "end_to_end_project"
IMAGES_PATH = os.path.join(PROJECT_ROOT_DIR, "images", CHAPTER_ID)
os.makedirs(IMAGES_PATH, exist_ok=True)

def save_fig(fig_id, tight_layout=True, fig_extension="png", resolution=300):
    path = os.path.join(IMAGES_PATH, fig_id + "." + fig_extension)
    print("Saving figure", fig_id)
    if tight_layout:
        plt.tight_layout()
    plt.savefig(path, format=fig_extension, dpi=resolution)<jupyter_output><empty_output><jupyter_text>First Data overview<jupyter_code>#import dataset country_vaccinations:
df = pd.read_csv('data/country_vaccinations.csv')
#first check:
df.head()
df.tail()<jupyter_output><empty_output><jupyter_text>Cleaning tasks:- First check of data shows a lot missing values.<jupyter_code>#Number of columns and rows:
shape1 = df.shape
print(f'We have {shape1[1]} columns and {shape1[0]} rows in our dataset.')
#Information about datatypes per column:
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 8079 entries, 0 to 8078
Data columns (total 15 columns):
 #   Column                               Non-Null Count  Dtype  
---  ------                               --------------  -----  
 0   country                              8079 non-null   object 
 1   iso_code                             8079 non-null   object 
 2   date                                 8079 non-null   object 
 3   total_vaccinations                   4984 non-null   float64
 4   people_vaccinated                    4423 non-null   float64
 5   people_fully_vaccinated              3021 non-null   float64
 6   daily_vaccinations_raw               4192 non-null   float64
 7   daily_vaccinations                   7891 non-null   float64
 8   total_vaccinations_per_hundred       4984 non-null   float64
 9   people_vaccinated_per_hundred        4423 non-null   float64
 10  people_fully_vaccinated_per_hundred  3021 non-null   float64
 11  daily_vaccinations_per_million[...]<jupyter_text>Cleaning task: - change object to datetime for column date- Check missing values in all columns with rows less than 8079<jupyter_code>df.nunique()
#basic statistic
df.describe().transpose().round()<jupyter_output><empty_output><jupyter_text>What we can see from the describtion:- we have a huge range from min to max for the various vaccination columns.- People fully vaccinated per hundred shows very few countries already vaccinated a high number of their citizens but 75% are only at 3% or less.  Data cleaning<jupyter_code>#check for missing values
missing_values = pd.DataFrame(df.isnull().sum(), columns=['ID'])
missing_values
#check for missing values in %
round(100*(df.isnull().sum()/len(df.index)),0)<jupyter_output><empty_output><jupyter_text>We have a high percentage of missing values in the vaccine columns.<jupyter_code># generate count statistics of duplicate entries
if len(df[df.duplicated()]) > 0:
    print("No. of duplicated entries: ", len(df[df.duplicated()]))
    print(df[df.duplicated(keep=False)].sort_values(by=list(df.columns)).head())
else:
    print("No duplicated entries found")<jupyter_output>No duplicated entries found<jupyter_text>Data cleaning tasks:- countries have different names (e.g. England, United Kingdom, Northern Ireland, Wales, Scotland)- drop daily_vaccinations_raw(set categorical data to categorical data tpye)  Data cleaning - NanWe have a lot of missing data and handle it as follows:- First we drop all rows with only Nans/0.0 for all vaccination columns. In these cases only the vaccines have been reported but vaccinations started later. This correction eliminanted the 2% missing data for daily vaccinations. - people_fully_vaccinated/peple_fully_vaccinated_per_hundred: fill NaNs with 0 as these are missing values because no one is fully vaccinated at that time.- For total_vaccinations 39% for the data are missing. This is surprising as values for daily_vaccinations are fully available. A comparison of total_vaccinations at 24.03.2021 and the summed up daily_vaccinations showed a big difference (e.g. for the US over 7.0 M less reported doses for daily_vaccinations as for total_vaccination. A further analysis where the data originally came from leads to the following explanation:"New doses administered per day (7-day smoothed). For countries that don't report data on a daily basis, we assume that doses changed equally on a daily basis over any periods in which no data was reported. This produces a complete series of daily figures, which is then averaged over a rolling 7-day window."(source: Our World in Data)- Because daily vaccinations are partially already calculated we will not fill missing data for total_vaccinations based on these data.<jupyter_code>#copy dataset before cleaning
df1 = df.copy()
#change type for date to datetime
df1['date'] = pd.to_datetime(df1['date'])
df1.head()
#test changes to date
df1.info()
# drop all rows with NaNs in 'people_fully_vaccinated', 'daily_vaccinations', 'daily_vaccinations_per_million':
df1.dropna(subset = ['people_fully_vaccinated', 'daily_vaccinations', 'daily_vaccinations_per_million'], axis = 0, how = 'all', inplace = True)

#Fill all NaNs for people fully vaccinated and people fully vaccinated per hundred with 0:
df1['people_fully_vaccinated'] = df1['people_fully_vaccinated'].fillna(0)
df1['people_fully_vaccinated_per_hundred'] = df1['people_fully_vaccinated_per_hundred'].fillna(0)
#check for missing data in 
round(100*(df1.isnull().sum()/len(df1.index)),0)
df1_corr = df1.copy()
df1[df1['country']== 'Israel']
df1_corr.head()
#Check difference between total sum of daily vaccinations vs. last value of total vaccinations 
country_sum = df1[df1['country']== 'United States']
Total = country_sum['daily_vaccinations'].sum()
Total
#Calculate sum of daily vaccinations for all days per country:
new_test1 = df1.groupby(['country'])['daily_vaccinations'].sum().reset_index()
print(new_test1)
#Select rows only for 2021-03-24:
only_total = df1[df1.date == '2021-03-24']
only_total.groupby('country')

#merge test and new_test1
test_m = pd.merge(only_total, new_test1, how='inner', on=['country'])
#Check difference between total sum of daily vaccinations/country with total vaccinations/country:
test_m['vacc_difference'] = test_m['total_vaccinations'] - test_m['daily_vaccinations_y']
test_m.sample(5)<jupyter_output><empty_output><jupyter_text>We found a big discrepancy between total vaccinations and daily vaccinations per country. For visualization purposes we will use both but later for modelling we decided to work with daily vaccinations only because the values are completly available.<jupyter_code>#check for duplicates in country and date:
df1[df1.duplicated(subset=['country','date'], keep=False)].shape
#drop column daily_vaccinations_raw, source_name, source_website:
lst = ['daily_vaccinations_raw', 'source_name', 'source_website']
df1.drop(lst, 1, inplace=True)
df1.head(3)
df1 = df1[df1.country.apply(lambda x: x not in ['England', 'Scotland', 'Wales', 'Northern Ireland'])]
lst2 = ['England', 'Scotland', 'Wales', 'Northern Ireland']
check1 = df1.loc[df['country'].isin(lst2)].loc[df1['date'] == '2021-03-15'].loc[:, 'daily_vaccinations'].sum()
check2 = df1.loc[df['country'] == 'United Kingdom'].loc[df1['date'] == '2021-03-15'].loc[:, 'daily_vaccinations'].values[0]
print(f'The sum of daily vaccinations in England, Scotland, Wales and Nothern Ireland on 15th March 2021 was: {check1}')
print(f'The total daily vaccinations in the United Kingdom on 15th March 2021 was: {check2}')
lst_empties = ['', []]
df.vaccines.isin(lst_empties).value_counts()<jupyter_output><empty_output><jupyter_text>Feature Engineering<jupyter_code>#df1['vaccines'].unique()
#df2[df2['country']== 'United States']
df2=df1.copy()
#df2 = df1.drop('vaccines', axis=1).join(df['vaccines'].str.split(', ', expand=True).stack().reset_index(level=1, drop=True).rename('vaccines'))
df3 = df2[['country', 'date', 'vaccines']]
df3.head()
#Number of countries included in dataset:
df6 = df2.copy()
df6 = df6.set_index(['date'])
ncountry = df6['country'].nunique()
print(F'{ncountry} countries are included in the Covid-19 vaccination progress dataset.')
#Create list with countries per region, create new column in df6 and map region to country:
Africa = ('Algeria','Angola','Chad', "Cote d'Ivoire",'Egypt','Equatorial Guinea','Ghana','Guinea','Kenya','Malawi','Maldives','Mali','Mauritius','Morocco','Mozambique','Namibia','Niger','Nigeria','Rwanda','Sao Tome and Principe', 'Senegal','Seychelles','Sierra Leone','Somalia','South Africa','South Sudan','Sudan','Swaziland','Tanzania','Togo','Tunisia','Uganda','Western Sahara', 'Zambia','Zimbabwe')

Asia = ('Afghanistan','Azerbaijan','Bangladesh','Bhutan','Brunei','Cambodia','China','East Timor','Hong Kong','India','Indonesia','Japan','Kazakhstan','Kyrgyzstan','Laos','Macao','Malaysia','Maldives','Mongolia','Myanmar','Nepal','North Korea','Pakistan','Philippines','Russia','Singapore','South Korea','Sri Lanka','Taiwan','Tajikistan','Thailand','Turkmenistan','Uzbekistan','Vietnam')
    
Middle_East = ('Bahrain','Iran','Iraq','Israel','Jordan','Kuwait','Lebanon','Oman','Qatar','Saudi Arabia','Syria','Turkey','United Arab Emirates','Yemen')
   
Europe = ('Albania','Andorra','Armenia','Austria','Belarus','Belgium','Bosnia and Herzegovina','Bulgaria','Croatia','Cyprus','Czechia','Denmark','Estonia','Faeroe Islands','Finland','France','Georgia','Germany','Greece','Greenland','Hungary','Iceland','Ireland','Italy','Latvia','Liechtenstein','Lithuania','Luxembourg','Macedonia','Malta','Moldova','Monaco','Montenegro','Netherlands','North Macedonia','Northern Cyprus','Norway','Poland','Portugal','Romania','San Marino','Serbia','Slovakia','Slovenia','Spain','Sweden','Switzerland','Ukraine','Vatican City')

UK_BritishOversea= ('United Kingdom','Anguilla','Antigua and Barbuda','Bermuda','Cayman Islands','Falkland Islands','Gibraltar','Guernsey','Isle of Man','Jersey', 'Montserrat', 'Saint Helena', 'Turks and Caicos Islands')

North_America = ('Canada','United States')
Central_America_Caribic = ('Antigua and Barbuda','Bahamas','Barbados','Belize','Costa Rica','Cuba','Dominica','Dominican Republic','El Salvador','Grenada','Guatemala','Haiti','Honduras','Jamaica','Mexico','Nicaragua','Panama','Saint Kitts and Nevis','Saint Lucia','Saint Vincent and the Grenadines','Trinidad and Tobago')
South_America = ('Argentina','Bolivia','Brazil','Chile','Colombia','Ecuador','Guyana','Paraguay','Peru','Suriname','Uruguay','Venezuela')
Australia_Oceania = ('Australia','Fiji','Kiribati','Marshall Islands','Micronesia','Nauru','New Zealand','Palau','Papua New Guinea','Samoa','Solomon Islands','Tonga','Tuvalu','Vanuatu')


continents = {country: 'Asia' for country in Asia}
continents.update({country: 'Europe' for country in Europe})
continents.update({country: 'Africa' for country in Africa})
continents.update({country: 'Middle_East' for country in Middle_East})
continents.update({country: 'UK_BritishOversea' for country in UK_BritishOversea})
continents.update({country: 'North_America' for country in North_America})
continents.update({country: 'Central_America_Caribic' for country in Central_America_Caribic})
continents.update({country: 'South_America' for country in South_America})
continents.update({country: 'Australia_Oceania' for country in Australia_Oceania})

df6['cgrouped'] = df6['country'].map(continents)


#Save cleaned and feature engineered dataframes as csv files to upload to EDA Notebook:
df2.to_csv('df2.csv', encoding='utf-8', index=False)
df3.to_csv('df3.csv', encoding='utf-8', index=False)
#df4.to_csv('df4.csv', encoding='utf-8', index=False)
#df5.to_csv('df5.csv', encoding='utf-8', index=False)
df6.to_csv('df6.csv', encoding='utf-8', index=False)<jupyter_output><empty_output><jupyter_text>Merge:<jupyter_code>df2.info()
df2[df2['country']== 'United States']<jupyter_output><empty_output><jupyter_text>Adding information to our datasetThe original Kaggle dataset about vaccination progress only includes information around counts of vaccinations or people vaccinated. For getting a deeper insight what supports a fast vaccination progress we add information about infections, deaths, population and gdp per capita and merge these with out dataset.<jupyter_code># load of additional dataset(cleaned in covid19_additional_info):
df_owid_covid_23march_1 = pd.read_csv('df_owid_covid_23march_1.csv')
df_owid_covid_23march_1.head()
#Information about datatypes per column:
df_owid_covid_23march_1.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 75099 entries, 0 to 75098
Data columns (total 24 columns):
 #   Column                           Non-Null Count  Dtype  
---  ------                           --------------  -----  
 0   iso_code                         75099 non-null  object 
 1   country                          75099 non-null  object 
 2   date                             75099 non-null  object 
 3   total_cases                      75099 non-null  float64
 4   new_cases                        75092 non-null  float64
 5   new_cases_smoothed               75099 non-null  float64
 6   total_deaths                     75099 non-null  float64
 7   new_deaths                       75099 non-null  float64
 8   new_deaths_smoothed              75099 non-null  float64
 9   total_cases_per_million          75099 non-null  float64
 10  new_cases_per_million            75092 non-null  float64
 11  new_cases_smoothed_per_million   75099 non-null  float64
 12  total_deaths_per_m[...]<jupyter_text>- Missing information for population density, median_age, aged_65_older, aged_70_older, gdp_per_capita, hospital_bed_per_thousand, life_expectancy, human_development_index- For further use only population density and gp_per_capita is important. Therefor we drop the other columns. For population density and gdp_per_capita the missing data can be find and has to be added.- Change type to datetime for column date.<jupyter_code>#Change data type to datetime:
df_owid_covid_23march_1['date'] = pd.to_datetime(df_owid_covid_23march_1['date'])
df2['date'] = pd.to_datetime(df2['date'])
#Combine dataset with pd.merge:
df_all = pd.merge(df2, df_owid_covid_23march_1, on='iso_code', how='right')
merge_df = pd.merge(df2, df_owid_covid_23march_1)#,left_on='date', right_on='country'
df_all.tail()
df_for_model = merge_df[['country', 'iso_code', 'date', 'daily_vaccinations', 'people_vaccinated_per_hundred', 'people_fully_vaccinated_per_hundred', 'population', 'population_density', 'gdp_per_capita']]
df_for_model.head()
df_for_model.to_csv('df_for_model.csv', encoding='utf-8', index=False)
df2[df2['country']== 'United States']
#check if merge is correct:
df_all[df_all['country_x']== 'United States']<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1141222857777531, 'fraction_numerical': 0.022991059032598436, 'mean_word_length': 5.487031700288185, 'pattern_counts': {'":': 0, '<': 48, '<?xml version=': 0, '>': 49, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '33555', 'license': 'MIT', 'n_tokens_mistral': 5825, 'n_tokens_neox': 5298, 'n_words': 1643, 'path': '02_Covid_Cleaning.ipynb', 'repo_name': 'BiBa-01/Capstone_Covid', 'size': '18007'}