<jupyter_start><jupyter_text>Covid-19 Step By Step Data AnalysisA exercise for self practising purpose. Thanks to the contributors below for providing the tutorial and explanation.Credit to- [COVID-19 data processing with Pandas DataFrame](https://towardsdatascience.com/covid-19-data-processing-58aaa3663f6) by B.Chen- [Automated ETL for LIVE Tableau Public Visualizations](https://towardsdatascience.com/automated-etl-for-live-tableau-public-visualizations-54f2b8652224) by Eklavya Saxena- [COVID-19 Forecasting Challenges (Week 2) Data Prep](https://www.kaggle.com/benhamner/covid-19-forecasting-challenges-week-2-data-prep)  0. PreparationThe plotly version in Kaggle is 4.8.1, downgrade to 3.10.0 to use `ploty.ploty`<jupyter_code>!pip install plotly==3.10.0
# This Python 3 environment comes with many helpful analytics libraries installed
# It is defined by the kaggle/python docker image: https://github.com/kaggle/docker-python
# For example, here's several helpful packages to load in 

import numpy as np # linear algebra
import pandas as pd # data processing, CSV file I/O (e.g. pd.read_csv)
import seaborn as sns # visualization library
import matplotlib.pyplot as plt # visualization library
import plotly.plotly as py # visualization library
from plotly.offline import init_notebook_mode, iplot # plotly offline mode
init_notebook_mode(connected=True) 
import plotly.graph_objs as go # plotly graphical object
from datetime import date, datetime, timedelta


# Input data files are available in the "../input/" directory.
# For example, running this (by clicking run or pressing Shift+Enter) will list the files in the input directory
import os
print(os.listdir("../input"))
for dirname, _, filenames in os.walk('/kaggle/input'):
    for filename in filenames:
        print(os.path.join(dirname, filename))
# import warnings library
import warnings        
# ignore filters
warnings.filterwarnings("ignore") # if there is a warning after some codes, this will avoid us to see them.
plt.style.use('ggplot') # style of plots. ggplot is one of the most used style, I also like it.
# Any results you write to the current directory are saved as output.<jupyter_output><empty_output><jupyter_text>1. Data ProcessingAlways observe the raw data format and have some insight of how to manipulating data.Data is downloaded from[Johns Hopkins CSSE public Github page](https://github.com/CSSEGISandData/COVID-19/tree/master/csse_covid_19_data/csse_covid_19_time_series)<jupyter_code>confirmed = pd.read_csv("../input/time_series_covid19_confirmed_global.csv")
deaths = pd.read_csv("../input/time_series_covid19_deaths_global.csv")
recovered = pd.read_csv("../input/time_series_covid19_recovered_global.csv")
confirmed.head()<jupyter_output><empty_output><jupyter_text>Observation:- There are 3 time series data, *confirmed*, *deaths* and *recovered* that are stored in different CSV files. This would be an issue when trying to plot in the same chart in data visualization.- Dates are in columns, it's not possible to simply use Dates as one of the axis. (Have to call all date columns to show all dates at once)- *confirmed*, *deaths* and *recovered* are not matched due to missing / mismatch, and some countries do not have the recovered figures in Province/State level- Cruise ships are mismatch in Province/State column<jupyter_code>confirmed.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 266 entries, 0 to 265
Columns: 154 entries, Province/State to 6/19/20
dtypes: float64(2), int64(150), object(2)
memory usage: 320.2+ KB<jupyter_text>All tables have the same columns `Province_State`, `Country_Region`, `Lat`, `Long`, following by the dates<jupyter_code>print(confirmed.columns)
print(deaths.columns)
print(recovered.columns)<jupyter_output>Index(['Province/State', 'Country/Region', 'Lat', 'Long', '1/22/20', '1/23/20',
       '1/24/20', '1/25/20', '1/26/20', '1/27/20',
       ...
       '6/10/20', '6/11/20', '6/12/20', '6/13/20', '6/14/20', '6/15/20',
       '6/16/20', '6/17/20', '6/18/20', '6/19/20'],
      dtype='object', length=154)
Index(['Province/State', 'Country/Region', 'Lat', 'Long', '1/22/20', '1/23/20',
       '1/24/20', '1/25/20', '1/26/20', '1/27/20',
       ...
       '6/10/20', '6/11/20', '6/12/20', '6/13/20', '6/14/20', '6/15/20',
       '6/16/20', '6/17/20', '6/18/20', '6/19/20'],
      dtype='object', length=154)
Index(['Province/State', 'Country/Region', 'Lat', 'Long', '1/22/20', '1/23/20',
       '1/24/20', '1/25/20', '1/26/20', '1/27/20',
       ...
       '6/10/20', '6/11/20', '6/12/20', '6/13/20', '6/14/20', '6/15/20',
       '6/16/20', '6/17/20', '6/18/20', '6/19/20'],
      dtype='object', length=154)<jupyter_text>2. Data Formatting- Format Date from `m/dd/yy` to `yyyy-mm-dd`- Rename 'Province/State' as `Province_State` and 'Country/Region' as `Country_Region`<jupyter_code>confirmed.columns = list(confirmed.columns[:4]) + [datetime.strptime(d, "%m/%d/%y").date().strftime("%Y-%m-%d") for d in confirmed.columns[4:]]
deaths.columns    = list(deaths.columns[:4])    + [datetime.strptime(d, "%m/%d/%y").date().strftime("%Y-%m-%d") for d in deaths.columns[4:]]
recovered.columns = list(recovered.columns[:4]) + [datetime.strptime(d, "%m/%d/%y").date().strftime("%Y-%m-%d") for d in recovered.columns[4:]]
confirmed.rename(columns={"Province/State": "Province_State", "Country/Region": "Country_Region"}, inplace=True)
deaths.rename(columns={"Province/State": "Province_State", "Country/Region": "Country_Region"}, inplace=True)
recovered.rename(columns={"Province/State": "Province_State", "Country/Region": "Country_Region"}, inplace=True)
confirmed.head()<jupyter_output><empty_output><jupyter_text>Merging tables of Confirmed, Deaths and Recovereduse `melt()` to unpivot DataFrames from wide format to long format- Identifier variables: `Province_State`, `Country_Region`, `Lat`, `Long`- Unpivor date columns (`columns[4:]`) with variable column `Date` and value column `Confirmed`<jupyter_code>dates = confirmed.columns[4:]

confirmed_long = confirmed.melt(
    id_vars=['Province_State', 'Country_Region', 'Lat', 'Long'], 
    value_vars=dates, 
    var_name='Date', 
    value_name='Confirmed'
)
deaths_long = deaths.melt(
    id_vars=['Province_State', 'Country_Region', 'Lat', 'Long'], 
    value_vars=dates, 
    var_name='Date', 
    value_name='Deaths'
)
recovered_long = recovered.melt(
    id_vars=['Province_State', 'Country_Region', 'Lat', 'Long'], 
    value_vars=dates, 
    var_name='Date', 
    value_name='Recovered'
)<jupyter_output><empty_output><jupyter_text>This would return a long format table that- ordered by `Date` and `Country_Region` (raw data was ordered by these columns in ASC order)<jupyter_code>confirmed_long
(confirmed_long['Country_Region'] == "Canada").value_counts()
recovered_long[recovered_long['Country_Region']=='Canada']<jupyter_output><empty_output><jupyter_text>Remove recovered data for Canada due to mismatch issue (Canada recovered data is counted by Country-wise rather than Province/State-wise).<jupyter_code>recovered_long = recovered_long[recovered_long['Country_Region']!='Canada']
# Check if there is Cana
(recovered_long['Province_State']=='Canada').value_counts()<jupyter_output><empty_output><jupyter_text>Use `merge()` to merge 3 DataFrame one after anotherMerging 1: Use confirmed table as referenced table, left joined by the `deaths_long` table and result as `full_table`Merging 2: `full_table` left joined by the `recovered_long`<jupyter_code># Merging 1: create full_table using confirmed_long and left joined by deaths_long
full_table = confirmed_long.merge(
    right = deaths_long,
    how = 'left',
    on = ['Province_State', 'Country_Region', 'Date', 'Lat', 'Long']
)

# Merging 2: merge full_table with recovered_long
full_table = full_table.merge(
    right = recovered_long,
    how = 'left',
    on = ['Province_State', 'Country_Region', 'Date', 'Lat', 'Long']
)<jupyter_output><empty_output><jupyter_text>Now we have a full table with `confirmed`, `deaths` and `recovered`<jupyter_code>full_table<jupyter_output><empty_output><jupyter_text>3. Data Cleansing1. ~~Convert `Date` from `string` to `datetime`~~ (Done in Data Formatting)2. Replace missing value `Nan`3. Coronavirus cases reported from 3 cruise ships are mismatched<jupyter_code># Date is in datetime format
full_table.dtypes
# Convert Date from String to DateTime
#full_table['Date'] = pd.to_datetime(full_table['Date'])
#full_table.dtypes<jupyter_output><empty_output><jupyter_text>Detect missing values `NaN` by running `df.isna().sum()`There is a lot `NaN` in Province_State, it makes sense as some countries report recovered cases in country levelWe will replace the `NaN` with `0`<jupyter_code>full_table.isna().sum()
full_table['Recovered'] = full_table['Recovered'].fillna(0)
full_table[full_table['Recovered']==0]<jupyter_output><empty_output><jupyter_text>The `recovered` columns are now filled with 0 but when you check the Dtype it's in `float64`Let's convert back to `int` to keep data consistency<jupyter_code>full_table.info()
full_table['Recovered'] = full_table['Recovered'].astype('int')
full_table.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
Int64Index: 39900 entries, 0 to 39899
Data columns (total 8 columns):
 #   Column          Non-Null Count  Dtype  
---  ------          --------------  -----  
 0   Province_State  12150 non-null  object 
 1   Country_Region  39900 non-null  object 
 2   Lat             39900 non-null  float64
 3   Long            39900 non-null  float64
 4   Date            39900 non-null  object 
 5   Confirmed       39900 non-null  int64  
 6   Deaths          39900 non-null  int64  
 7   Recovered       39900 non-null  int64  
dtypes: float64(2), int64(3), object(3)
memory usage: 2.7+ MB<jupyter_text>Cruises Ship DataThe table included data of 3 cruise ships: **Grand Princess, Diamond Princess and MS Zaandam**However, cruise data are mapped to `Province_State` and `Country_Region` and some of them are mismatched to `Canada` as Country<jupyter_code>lst = ['Grand Princess', 'Diamond Princess', 'MS Zaandam']
lst = '|'.join(lst)
full_table.loc[full_table['Province_State'].str.contains(lst, na=False) | full_table['Country_Region'].str.contains(lst, na=False)]<jupyter_output><empty_output><jupyter_text>We will remove the cruise data by extracting them out of the `full_table`<jupyter_code>ship_rows = full_table['Province_State'].str.contains('Grand Princess') | full_table['Province_State'].str.contains('Diamond Princess') | full_table['Country_Region'].str.contains('Diamond Princess') | full_table['Country_Region'].str.contains('MS Zaandam')
full_ship = full_table[ship_rows]
full_ship<jupyter_output><empty_output><jupyter_text>Exclude the ship data with `~(ship_rows)`*Q: What is the tilde (~) sign?*Ans: Boolean operatorsYou can negate boolean expressions with the word not or the ~ operator.```In [256]: df.query('~bools')Out[256]:           a         b         c  bools2  0.697753  0.212799  0.329209  False7  0.275396  0.691034  0.826619  False8  0.190649  0.558748  0.262467  FalseIn [257]: df.query('not bools')Out[257]:           a         b         c  bools2  0.697753  0.212799  0.329209  False7  0.275396  0.691034  0.826619  False8  0.190649  0.558748  0.262467  False```<jupyter_code>full_table = full_table[~(ship_rows)]
full_table.loc[full_table['Province_State'].str.contains(lst, na=False) | full_table['Country_Region'].str.contains(lst, na=False)]<jupyter_output><empty_output><jupyter_text>4. Data AggregationCreate a column of `Active` representing the active covid cases based on,`Active = Confirmed - Deaths - Recovered`<jupyter_code># Active Case = confirmed - deaths - recovered
full_table['Active'] = full_table['Confirmed'] - full_table['Deaths'] - full_table['Recovered']<jupyter_output><empty_output><jupyter_text>Aggregated by Countries- `sum()` is to get the total count of `Confirmed`, `Deaths`, `Recovered`, `Active` for the given `Date` and `Country_Region`.- `reset_index()` reset the index and use the default one, which is `Date` and `Country_Region`.<jupyter_code>full_grouped = full_table.groupby(['Date', 'Country_Region'])['Confirmed', 'Deaths', 'Recovered', 'Active'].sum().reset_index()
full_grouped<jupyter_output><empty_output><jupyter_text>New cases, New deaths, New recovered- To get the latest day data by deducting the corresponding accumulative data on previous dayLet't go through the code below step by step```temp = full_grouped.groupby(['Country/Region', 'Date', ])['Confirmed', 'Deaths', 'Recovered']temp = temp.sum().diff().reset_index()mask = temp['Country/Region'] != temp['Country/Region'].shift(1)temp.loc[mask, 'Confirmed'] = np.nantemp.loc[mask, 'Deaths'] = np.nantemp.loc[mask, 'Recovered'] = np.nan renaming columnstemp.columns = ['Country/Region', 'Date', 'New cases', 'New deaths', 'New recovered'] merging new valuesfull_grouped = pd.merge(full_grouped, temp, on=['Country/Region', 'Date']) filling na with 0full_grouped = full_grouped.fillna(0) fixing data typescols = ['New cases', 'New deaths', 'New recovered']full_grouped[cols] = full_grouped[cols].astype('int') full_grouped['New cases'] = full_grouped['New cases'].apply(lambda x: 0 if x<0 else x)```<jupyter_code># new cases 
temp = full_grouped.groupby(['Country_Region', 'Date', ])['Confirmed', 'Deaths', 'Recovered']
temp.head()
temp = temp.sum().diff().reset_index()
temp
mask = temp['Country_Region'] != temp['Country_Region'].shift(1)
mask
temp.loc[mask, 'Confirmed'] = np.nan
temp.loc[mask, 'Deaths'] = np.nan
temp.loc[mask, 'Recovered'] = np.nan
temp.loc[mask, 'Confirmed']
# renaming columns
temp.columns = ['Country_Region', 'Date', 'New cases', 'New deaths', 'New recovered']
# merging new values
full_grouped = pd.merge(full_grouped, temp, on=['Country_Region', 'Date'])
# filling na with 0
full_grouped = full_grouped.fillna(0)
# fixing data types
cols = ['New cases', 'New deaths', 'New recovered']
full_grouped[cols] = full_grouped[cols].astype('int')
# 
full_grouped['New cases'] = full_grouped['New cases'].apply(lambda x: 0 if x<0 else x)
full_grouped
full_grouped.to_csv('COVID-19-time-series-clean-complete.csv')<jupyter_output><empty_output><jupyter_text>5. Data Exploration<jupyter_code>import pandas as pd
import altair as alt
full_grouped = pd.read_csv('COVID-19-time-series-clean-complete.csv', parse_dates=['Date'])
sg = full_grouped[full_grouped['Country_Region'] == 'Singapore']
base = alt.Chart(sg).mark_bar().encode(
    x='monthdate(Date):O',
).properties(
    width=500
)
red = alt.value('#f54242')
base.encode(y='Confirmed').properties(title='Total confirmed') | base.encode(y='Deaths', color=red).properties(title='Total deaths')
red = alt.value('#f54242')
base.encode(y='New cases').properties(title='Daily new cases') | base.encode(y='New deaths', color=red).properties(title='Daily new deaths')<jupyter_output><empty_output><jupyter_text>Showing the coronavirus spreads<jupyter_code>import pandas as pd
import altair as alt
full_grouped = pd.read_csv('COVID-19-time-series-clean-complete.csv', parse_dates=['Date'])
countries = ['US', 'Italy', 'China', 'Spain', 'Germany', 'France', 'Iran', 'United Kingdom', 'Switzerland','Singapore']
selected_countries = full_grouped[full_grouped['Country_Region'].isin(countries)]
selected_countries
alt.Chart(selected_countries).mark_circle().encode(
    x='monthdate(Date):O',
    y='Country_Region',
    color='Country_Region',
    size=alt.Size('New cases:Q',
        scale=alt.Scale(range=[0, 1000]),
        legend=alt.Legend(title='Daily new cases')
    ) 
).properties(
    width=1200,
    height=500
)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.14501278772378517, 'fraction_numerical': 0.04008951406649616, 'mean_word_length': 5.447238252267106, 'pattern_counts': {'":': 6, '<': 86, '<?xml version=': 0, '>': 84, 'https://': 5, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 3, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '21', 'dir': 'jupyter-structured-clean-dedup', 'id': '417144', 'license': 'Apache-2.0', 'n_tokens_mistral': 5652, 'n_tokens_neox': 5189, 'n_words': 1319, 'path': 'covid-19-step-by-step-data-analysis.ipynb', 'repo_name': 'stellactyy/Covid19-Data-Analysis-Kaggle', 'size': '15640'}