Repository navigation
Expand file tree
/
Copy pathpreprocess_data.py
More file actions
158 lines (136 loc) · 7.77 KB
/
Copy pathpreprocess_data.py
File metadata and controls
158 lines (136 loc) · 7.77 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
import numpy as np
import pandas as pd
from sqlalchemy import create_engine
import collect_data
from functools import reduce
def preprocess_case_df():
"""
Preprocess the case_data from sql db and output as a df
"""
# Accessing data from database
engine = create_engine('sqlite:///covid.db')
df = pd.read_sql('SELECT c.*, p.Population FROM case_data c LEFT JOIN pop_data p ON c.locality=p.Locality;',
engine)
# change datatype
df['report_date'] = pd.to_datetime(df.report_date)
df['total_cases'] = df.total_cases.astype(int)
df['hospitalizations'] = df.hospitalizations.astype(int)
df['deaths'] = df.deaths.astype(int)
df['Population'] = df.Population.astype(int)
df.replace({'vdh_health_district':"Thomas Jefferson"}, "Blue Ridge", inplace=True)
# add region
central=["Chesterfield", "Crater", "Chickahominy","Henrico","Piedmont","Richmond","Southside"]
eastern = ["Chesapeake", "Eastern Shore", "Hampton", "Norfolk", "Peninsula", "Portsmouth", "Three Rivers",
"Virginia Beach", "Western Tidewater"]
northern = ["Alexandria", "Arlington", "Fairfax", "Loudoun", "Prince William"]
northwest = ["Central Shenandoah", "Lord Fairfax", "Rappahannock", "Rappahannock Rapidan", "Blue Ridge"]
southwest = ["Alleghany", "Central Virginia", "Cumberland Plateau", "Lenowisco", "Mount Rogers", "New River",
"Pittsylvania-Danville", "Roanoke", "West Piedmont"]
region_dict = {"central": central,
"eastern": eastern,
"northern": northern,
"northwest": northwest,
"southwest": southwest}
region_map = {val:key for key, lst in region_dict.items() for val in lst}
df['region'] = df.vdh_health_district.map(region_map)
# get daily new nums
df.sort_values(['region','vdh_health_district', 'locality','report_date'],
inplace=True,ascending=True,ignore_index=True)
df[['prev_total_cases','prev_hosp','prev_deaths']] = (df
.groupby(['region','vdh_health_district','locality'])\
['total_cases','hospitalizations','deaths']
.apply(lambda grp: grp.shift(1)))
df.fillna(0, inplace=True)
def get_case_count(row):
counter = (row['total_cases'] - row['prev_total_cases'])
if counter < 0:
return 0
return counter
def get_hos_count(row):
counter = (row['hospitalizations'] - row['prev_hosp'])
if counter < 0:
return 0
return counter
def get_death_count(row):
counter = (row['deaths'] - row['prev_deaths'])
if counter < 0:
return 0
return counter
df['new_cases'] = df.apply(lambda row: get_case_count(row), axis=1 )
df['new_hosp'] = df.apply(lambda row: get_hos_count(row), axis=1 )
df['new_deaths'] = df.apply(lambda row: get_death_count(row), axis=1 )
# get weekly rolling avg
df['weekly_rolling_avg_cases'] = df.new_cases.rolling(7).mean()
df['total_cases_rate']=round(df.total_cases / df.Population * 100000).astype(int)
df['hospitalizations_rate']=round(df.hospitalizations / df.Population * 100000).astype(int)
df['deaths_rate']=round(df.deaths / df.Population * 100000).astype(int)
new_df = df[['report_date', 'fips', 'region', 'vdh_health_district', 'locality', 'Population',
'total_cases', 'hospitalizations', 'deaths', 'new_cases', 'new_hosp',
'new_deaths', 'weekly_rolling_avg_cases', 'total_cases_rate',
'hospitalizations_rate','deaths_rate']]
return new_df
case_df = preprocess_case_df()
case_df.to_csv('data/cases.csv', index=False)
###############################################################################
def preprocess_vax_df():
"""
Preprocess the case_data from sql db and output as 3 dfs
"""
# Access dataset from SQL db
engine = create_engine('sqlite:///covid.db')
df = pd.read_sql('SELECT v.*, p.Population FROM vaccine_data v LEFT JOIN pop_data p ON v.locality=p.Locality;'
,engine)
# Convert Datetime
df['administration_date'] = pd.to_datetime(df.administration_date)
df['vaccine_doses_administered'] = df.vaccine_doses_administered.astype(int)
df.Population.fillna(0, inplace=True)
df['Population'] = df.Population.astype(int)
# Add Region column
central=["Chesterfield", "Crater", "Chickahominy","Henrico","Piedmont","Richmond","Southside"]
eastern = ["Chesapeake", "Eastern Shore", "Hampton", "Norfolk", "Peninsula", "Portsmouth", "Three Rivers",
"Virginia Beach", "Western Tidewater"]
northern = ["Alexandria", "Arlington", "Fairfax", "Loudoun", "Prince William"]
northwest = ["Central Shenandoah", "Lord Fairfax", "Rappahannock", "Rappahannock Rapidan", "Blue Ridge"]
southwest = ["Alleghany", "Central Virginia", "Cumberland Plateau", "Lenowisco", "Mount Rogers", "New River",
"Pittsylvania-Danville", "Roanoke", "West Piedmont"]
other = ["Not Reported", "Out of State"]
region_dict = {"central": central,
"eastern": eastern,
"northern": northern,
"northwest": northwest,
"southwest": southwest,
"other": other}
region_map = {val:key for key, lst in region_dict.items() for val in lst}
df['region'] = df.health_district.map(region_map)
# separate out the federal doses
other_df = df[df.Population == 0]
county_df = df[df.Population != 0]
# dose in population percentage
county_df['pop_perc'] = county_df.vaccine_doses_administered / county_df.Population *100
# weekly rolling avg doses
county_df.sort_values(['region','health_district', 'locality','administration_date'],
inplace=True,ascending=True,ignore_index=True)
county_df['weekly_rolling_avg_dose'] = county_df.vaccine_doses_administered.rolling(7).mean()
county_df = county_df[['administration_date', 'fips', 'region','health_district','locality', 'Population',
'facility_type', 'vaccine_manufacturer', 'dose_number', 'vaccine_doses_administered',
'pop_perc','weekly_rolling_avg_dose']]
#dfs for county map
one_dose = county_df[county_df.dose_number =="1"].groupby(['fips','locality','Population']).vaccine_doses_administered.sum().reset_index()
one_dose.columns = ['fips','locality','population','One_dose']
full_vax = county_df[(county_df.dose_number =="2")|((county_df.dose_number =="1") & (county_df.vaccine_manufacturer =='J&J'))].groupby(['fips','locality','Population']).vaccine_doses_administered.sum().reset_index()
full_vax.columns = ['fips','locality','population','Fully_vaccinated']
total_dose = county_df.groupby(['fips','locality','Population']).vaccine_doses_administered.sum().reset_index()
total_dose.columns = ['fips','locality','population','Total_doses']
#merge dfs
data_frames = [total_dose, one_dose, full_vax]
df_merged = reduce(lambda left,right: pd.merge(left,right,on=['fips','locality','population'],
how='left'), data_frames)
df_merged['Total_doses_rate'] = round(df_merged.Total_doses / df_merged.population * 100000).astype(int)
df_merged['One_dose_rate'] = round(df_merged.One_dose / df_merged.population * 100000).astype(int)
df_merged['Fully_vaccinated_rate'] = round(df_merged.Total_doses / df_merged.population * 100000).astype(int)
return df, county_df, other_df, df_merged
df, county_df, other_df, df_merged= preprocess_vax_df()
df.to_csv('data/vax.csv', index=False)
county_df.to_csv('data/county_vax.csv', index=False)
other_df.to_csv('data/other_vax.csv',index=False)
df_merged.to_csv('data/vax_map.csv',index=False)