<jupyter_start><jupyter_text>Standard process in data science!['CRISP_DM'](../reports/figures/CRISP_DM.png) Data Preparation* Data strcture must be clear and understandable* Visulize data into plots and graphs GitHub CSV data : <NAME> First we will scrap data for confirmed cases country wise and will do it for limited number of countries<jupyter_code>git_repo = 'https://github.com/CSSEGISandData/COVID-19.git'
git_clone = subprocess.Popen( "git clone " + git_repo ,
                         cwd = os.path.dirname( '../data/raw/' ),
                         shell = True,
                         stdout = subprocess.PIPE,
                         stderr = subprocess.PIPE )
(out, error) = git_clone.communicate()

print('out:', out)
print('error:', error)
# load data from csv file
filepath = '../data/raw/COVID-19/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_global.csv'
pd_raw_confirmed = pd.read_csv(filepath)

pd_raw_confirmed.head()<jupyter_output><empty_output><jupyter_text>Filter raw data<jupyter_code>t_idx = pd_raw_confirmed.columns[4:]

df_confirmed = pd.DataFrame({'date':t_idx})
df_confirmed.head()
# get daily cases for one counrty e.g. Germany
pd_raw_confirmed[pd_raw_confirmed['Country/Region']=='Germany'].iloc[:,4::].sum(axis=0)[-4:]
# do same for multiple countries
countries =['Italy', 'US', 'Spain', 'Germany', 'Russia' , 'India', 'Brazil'] 

for con in countries:
    df_confirmed[con]=np.array(pd_raw_confirmed[pd_raw_confirmed['Country/Region']==con].iloc[:,4::].sum(axis=0))
    
df_confirmed.tail()
df_confirmed.set_index('date').plot()
plt.xlabel('Date')
plt.ylabel('Total cases')
plt.gca().yaxis.set_major_formatter(FormatStrFormatter('%.0f'))<jupyter_output><empty_output><jupyter_text>Datatype of date<jupyter_code>df_confirmed.tail()
# convert to datetime df_confirmed
t_idx = [datetime.strptime(date,"%m/%d/%y") for date in df_confirmed.date] 
# convert back to date ISO norm (str)
t_str = [each.strftime('%Y-%m-%d') for each in t_idx] 

# set back to DataFrame
df_confirmed['date'] = t_idx
# cross check
type(df_confirmed['date'][0])
df_confirmed.to_csv('../data/processed/COVID_small_flat_table.csv',sep=';',index=False)<jupyter_output><empty_output><jupyter_text>Scrap recovered and currently infected cases and deaths<jupyter_code>def store_JH_small_data(filepath, country_list):
    # load data from csv file
    df = pd.read_csv(filepath)
    
    t_idx = df.columns[4:]
    
    df_processed = pd.DataFrame({'date':t_idx})
    
    for each in country_list:
        df_processed[each]=np.array(df[df['Country/Region']==each].iloc[:,4::].sum(axis=0))
    
    t_idx = [datetime.strptime(date,"%m/%d/%y") for date in df_processed.date] 
    df_processed['date'] = t_idx
    return df_processed<jupyter_output><empty_output><jupyter_text>Recovered<jupyter_code>filepath = '../data/raw/COVID-19/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_recovered_global.csv'
df_recovered = store_JH_small_data(filepath, countries)
df_recovered.tail()
df_recovered.to_csv('../data/processed/COVID_small_flat_table_recovered.csv',sep=';',index=False)<jupyter_output><empty_output><jupyter_text>Deaths<jupyter_code>filepath = '../data/raw/COVID-19/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_deaths_global.csv'
df_deaths = store_JH_small_data(filepath, countries)
df_deaths.tail()
df_deaths.to_csv('../data/processed/COVID_small_flat_table_deaths.csv',sep=';',index=False)<jupyter_output><empty_output><jupyter_text>Infected<jupyter_code>df_infected = pd.DataFrame()
df_infected['date'] = t_idx
df_infected = pd.concat([df_infected, df_confirmed.iloc[:, 1::] - df_recovered.iloc[:, 1::] - df_deaths.iloc[:, 1::]], 
                        axis=1)
df_infected.to_csv('../data/processed/COVID_small_flat_table_infected.csv',sep=';',index=False)<jupyter_output><empty_output><jupyter_text>Relational data model - defining a primary keyA primary key’s main features are:* It must contain a unique value for each row of data.* It cannot contain NaN 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()
# adjust column name
pd_data_base = pd_raw.rename(columns = {'Country/Region':'country',
                                    'Province/State':'state'})
pd_data_base['state'] = pd_data_base['state'].fillna('no')

# drop unnecessary columns
pd_data_base = pd_data_base.drop(['Lat','Long'],axis=1)
pd_data_base.head()
pd_relational=pd_data_base.set_index(['state','country']).T.stack(level=[0,1]).reset_index().rename(columns={'level_0': 'date',
                                                                                                             0:'confirmed'
                                                                                                            })
pd_relational.head()
pd_relational.dtypes
# chnage datatype of date
pd_relational['date'] = pd_relational.date.astype('datetime64[ns]')
pd_relational['confirmed'] = pd_relational.confirmed.astype(int)
pd_relational.dtypes
pd_relational[pd_relational['country']=='US'].tail()
pd_relational.to_csv('../data/processed/COVID_relational_confirmed.csv',sep=';',index=False)<jupyter_output><empty_output><jupyter_text>Rational data model for US region from <NAME> dataset<jupyter_code>data_path='../data/raw/COVID-19/csse_covid_19_data/csse_covid_19_time_series/time_series_covid19_confirmed_US.csv'
pd_raw_US=pd.read_csv(data_path)
pd_raw_US.head()
# remove unwated columns and chnage column names
pd_raw_US=pd_raw_US.drop(['UID', 'iso2', 'iso3', 'code3', 'Country_Region','FIPS', 'Admin2',  'Lat', 'Long_', 'Combined_Key'],axis=1)
pd_data_base_US=pd_raw_US.rename(columns={'Province_State':'state'}).copy()
# stack data in rational form
pd_relational_US=pd_data_base_US.set_index(['state']).T.stack().reset_index()                  \
                                    .rename(columns={'level_0':'date', 0:'confirmed'})

# convert to datetime
pd_relational_US['country']='US'
pd_relational_US['date']=[datetime.strptime( each,"%m/%d/%y") for each in pd_relational_US.date] 
pd_relational_US.head()
# merge US data into main rational DataFrame
pd_relational_model_all=pd_relational[pd_relational['country']!='US'].reset_index(drop=True)
pd_relational_model_all=pd.concat([pd_relational_model_all,pd_relational_US],ignore_index=True)

pd_relational_model_all[pd_relational_model_all['country']=='US'].tail()
# export data to csv
pd_relational_model_all.to_csv('../data/processed/20200730_COVID_relational_confirmed.csv',sep=';',index=False)<jupyter_output><empty_output>