<jupyter_start><jupyter_text>Data Preparation 1) focus is always to understand the final data structure2) support each step by visual analytics  Johns Hopkins GITHUB csv data<jupyter_code>import pandas as pd
import numpy as np

from datetime import datetime

%matplotlib inline
pd.set_option('display.max_rows', 500)
data_path='../data/raw/COVID-19/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv'
pd_raw=pd.read_csv(data_path)
pd_raw.head()
time_idx=pd_raw.columns[4:]
df_plot = pd.DataFrame({
    'date':time_idx})
df_plot.head()
pd_raw['Country/Region']
pd_raw[pd_raw['Country/Region']=='Germany'].iloc[:,4::].sum(axis=0)[0:4]
country_list=['Italy',
              'US',
              'Spain',
              'Germany',
              'Korea, South',
             ]
for each in country_list:
    df_plot[each]=np.array(pd_raw[pd_raw['Country/Region']==each].iloc[:,4::].sum(axis=0))
%matplotlib inline
df_plot.set_index('date').plot()<jupyter_output><empty_output><jupyter_text>Data Type Date<jupyter_code>df_plot.head()
time_idx=[datetime.strptime( each,"%m/%d/%y") for each in df_plot.date] # convert to datetime
time_str=[each.strftime('%Y-%m-%d') for each in time_idx] # convert back to date ISO norm (str)
df_plot['date']=time_idx
type(df_plot['date'][0])
df_plot.head()
df_plot.to_csv('../data/processed/COVID_small_flat_table.csv',sep=';',index=False)<jupyter_output><empty_output><jupyter_text>Relational data model - defining a primary key A primary key’s main features are:1) It must contain a unique value for each row of data2) It cannot contain null values<jupyter_code>data_path='../data/raw/COVID-19/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv'
pd_raw=pd.read_csv(data_path)
pd_raw.head()
pd_data_base=pd_raw.rename(columns={'Country/Region':'country',
                      'Province/State':'state'})
pd_data_base=pd_data_base.drop(['Lat','Long'],axis=1)
pd_data_base.head()
test_pd=pd_data_base.set_index(['state', 'country']).T
test_pd.columns
test_pd.stack(level=[0,1]).reset_index()
pd_relational_model=pd_data_base.set_index(['state','country']) \
                                .T                              \
                                .stack(level=[0,1])             \
                                .reset_index()                  \
                                .rename(columns={'level_0':'date',
                                                   0:'confirmed'},
                                                  )
pd_relational_model.head()
pd_relational_model.dtypes
pd_relational_model['date']=pd_relational_model.date.astype('datetime64[ns]')
pd_relational_model.dtypes
df_plot.to_csv('../data/processed/COVID_relational_confirmed.csv',sep=';')<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13841201716738197, 'fraction_numerical': 0.01430615164520744, 'mean_word_length': 3.4823717948717947, 'pattern_counts': {'":': 0, '<': 13, '<?xml version=': 0, '>': 13, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 6, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '3', 'dir': 'jupyter-structured-clean-dedup', 'id': '585644', 'license': 'FTL', 'n_tokens_mistral': 974, 'n_tokens_neox': 936, 'n_words': 139, 'path': 'notebooks/Data Science_COVID19-Data_Preparation.ipynb', 'repo_name': 'pritampunde1995/COVID-19', 'size': '2796'}