<jupyter_start><jupyter_text>Johns Hopkins GITHUB csv Data<jupyter_code>data_path='/users/sayali/ads_covid-19/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 Data<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()<jupyter_output><empty_output><jupyter_text>Relational data model - defining a primary key<jupyter_code>df_plot.to_csv('/Users/SAYALI/ads_covid-19/data/processed/COVID_small_flat_table.csv',sep=';',index=False)
data_path='/Users/SAYALI/ads_covid-19/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['state']=pd_data_base['state'].fillna('no')  #ensure a sring, important for indexing
pd_data_base=pd_data_base.drop(['Lat','Long'],axis=1)
pd_data_base.head()
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
pd_relational_model.confirmed=pd_relational_model.confirmed.astype(int)

pd_relational_model.to_csv('/Users/SAYALI/ads_covid-19/data/processed/COVID_relational_confirmed.csv',sep=';',index=False)
print(pd_relational_model[pd_relational_model['country']=='US'].tail())<jupyter_output>date state country  confirmed
47866 2020-07-19    no      US    3773260
48132 2020-07-20    no      US    3834677
48398 2020-07-21    no      US    3899211
48664 2020-07-22    no      US    3970085
48930 2020-07-23    no      US    4038748<jupyter_text>Attention due to the new structure of the Johns Hopkins data set the state information US is in a second file<jupyter_code>data_path='/Users/SAYALI/ads_covid-19/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()
# drop all non relevant fields! 
# Many of this fields do not belong to 
# a transactional data. These fields belong to so called master data
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()
## the stack command has problems when only one information is available within a stacking level
pd_relational_model_US=pd_data_base_US.set_index(['state']) \
                                .T                              \
                                .stack()             \
                                .reset_index()                  \
                                .rename(columns={'level_0':'date',
                                                   0:'confirmed'},
                                                  )
pd_relational_model_US['country']='US'
pd_relational_model_US['date']=[datetime.strptime( each,"%m/%d/%y") for each in pd_relational_model_US.date] # convert to datetime
pd_relational_model_US.head()
# update the large relational file
pd_relational_model_all=pd_relational_model[pd_relational_model['country']!='US'].reset_index(drop=True)
pd_relational_model_all=pd.concat([pd_relational_model_all,pd_relational_model_US],ignore_index=True)
pd_relational_model_all.to_csv('/Users/SAYALI/ads_covid-19/data/processed/20200424_COVID_relational_confirmed.csv',sep=';',index=False)
print(pd_relational_model_all[pd_relational_model_all['country']=='US'])<jupyter_output>date                     state country  confirmed
48760  2020-01-22            American Samoa      US          0
48761  2020-01-22                      Guam      US          0
48762  2020-01-22  Northern Mariana Islands      US          0
48763  2020-01-22               Puerto Rico      US          0
48764  2020-01-22               Puerto Rico      US          0
...           ...                       ...     ...        ...
663315 2020-07-23                      Utah      US        318
663316 2020-07-23                      Utah      US         67
663317 2020-07-23                      Utah      US       2594
663318 2020-07-23                      Utah      US        128
663319 2020-07-23                      Utah      US       2201

[614560 rows x 4 columns]<jupyter_text>cross check on full sum US data<jupyter_code>check_US=pd_relational_model_all[pd_relational_model_all['country']=='US']
check_US[['date','country','confirmed']].groupby(['date','country']).sum()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.12005527724995682, 'fraction_numerical': 0.056831922611850064, 'mean_word_length': 2.641509433962264, 'pattern_counts': {'":': 0, '<': 19, '<?xml version=': 0, '>': 19, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 14, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '5', 'dir': 'jupyter-structured-clean-dedup', 'id': '451580', 'license': 'FTL', 'n_tokens_mistral': 2119, 'n_tokens_neox': 1891, 'n_words': 282, 'path': 'notebooks/Data_Preparation.ipynb', 'repo_name': 'sayali111255/Advanced_DataScience_COVID-19', 'size': '5789'}