Image for post
Image for post
Photo by Elena Mozhvilo on Unsplash

Visualizing the Dilemma in Pandemic

controlling the virus or maintaining economic growth

import matplotlib.pyplot as plt
import pandas as pd
#Read the data
data = pd.read_csv(‘data_state.csv’)
# Aggregate the data
covid = data.groupby(by=[‘submission_date’])[[‘tot_cases’,’tot_death’,’new_case’,’new_death’]].sum()
#reset index to plot time series data
covid.reset_index(inplace=True)
covid[‘Date’] = pd.to_datetime(covid[‘submission_date’])
covid.set_index(‘Date’,inplace=True)
#plot subplots
figure, axes = plt.subplots(2,2,figsize=(12,8))
covid[‘new_case’].plot(title=’New Cases by Date’,ax = axes[0,0])
covid[‘new_death’].plot(title=’New Death by Date’,ax =axes[0,1])
covid[‘tot_cases’].plot(title=’Total Cases by Date’,ax=axes[1,0])
covid[‘tot_death’].plot(title=’Total Death by Date’, ax=axes[1,1])
plt.tight_layout()
Image for post
Image for post
Data Source: https://data.cdc.gov/Case-Surveillance/United-States-COVID-19-Cases-and-Deaths-by-State-o/9mfq-cb36
#Read the Unemployment Data
em_us = pd.read_excel('UNEMPLOY.xls',skiprow=range(0,9),header=10)
em_us['Date'] = pd.to_datetime(em_us['observation_date'])
em_us['month'] = em_us['Date'].dt.month
#Generate the month variable
covid['month'] = covid['Date'].dt.month
#Merge the datasets
us_data = covid.merge(em_us, on='month', how='left')
#Plot
us_data['submission_date']=pd.to_datetime(us_data['submission_date'])
us_data.set_index('submission_date',inplace=True)
us_data.plot(y=['UNEMPLOY','new_case','new_death'],figsize=(8,8))
Image for post
Image for post
#Read and Clean the Dataset
employment = pd.read_excel(‘em_county.xlsx’,skiprows=4).dropna()
employment_sc = employment[employment[‘County Name/State Abbreviation’]==”Santa Clara County, CA”]employment_sc [‘Period’] = pd.to_datetime(employment_sc [‘Period’], format=”%b-%y”)employment_sc[‘Month’] = employment_sc [‘Period’].dt.month
employment_sc[‘Year’]= employment_sc [‘Period’].dt.year
employment_sc= employment_sc[employment_sc[‘Year’]==2020][[‘Unemployed’,’(%)’,’Month’]]
## Read Cases
cases_sc = pd.read_csv(‘santa_clara.csv’)
cases_sc[‘Date’] = pd.to_datetime(cases_sc[‘Date’], format=’%m/%d/%Y’)cases_sc[‘Month’] = cases_sc[‘Date’].dt.month#Read Death
death_sc = pd.read_csv(‘death_sc.csv’)
death_sc=death_sc[[‘Date’,’Cumulative’]]
death_sc[‘Date’] = pd.to_datetime(death_sc[‘Date’])
#Merge Three Datasets
df = cases_sc.merge(death_sc, on=’Date’, how=’inner’)
sc = df.merge(employment_sc, on=’Month’, how=’left’)
sc.rename(columns={'(%)':'unemployment_rate','Cumulative':'Total_death'}, inplace=True)
#Plot the Data
figure, axes = plt.subplots(3,1,sharex=True,figsize=(12,10))
cols = ['Total_cases','Total_death','Unemployed']
for i in range(len(cols)):
sc.plot(x="Date", y=cols[i],ax=axes[i])
#define the text location
y_bar = max(sc[cols[i]])//2

#plot the shelter in place order
axes[i].axvline(pd.to_datetime('2020-03-16'), color='r', linestyle='--')
axes[i].text(pd.to_datetime('2020-03-16'),y_bar,'Shelter in Place',color='red')
#plot the reopen order
axes[i].axvline(pd.to_datetime('2020-07-02'), color='r', linestyle='--')
axes[i].text(pd.to_datetime('2020-07-02'),y_bar,'Reopen',color='red')
plt.suptitle('The Dilemma in the Santa Clara County')
Image for post
Image for post
Santa Clara County Situation

PhD in Economics | Certified in Data Science | Passion in Life | https://zijing0926.github.io/Portfolio/ | https://www.linkedin.com/in/zijingzhu/

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store