Photo by Elena Mozhvilo on Unsplash

Visualizing the Dilemma in Pandemic

controlling the virus or maintaining economic growth

Zijing Zhu
Dec 8, 2020 · 6 min read

California Governor Gavin Newsom has recently announced new stay-at-home orders in coping with the increased number of confirmed coronavirus cases. Following the curfew order a few weeks ago, now residents in the regions with ICU capacity below 15% in the Bay Area are advised to stay at home until January 4th, 2021. The Bay Area's economic activities are widely affected by adjusting the indoor capacities in the restaurants, shopping centers, etc., and limiting outdoor activities.

This is not the first time for residents in the Bay Area to experience this. In March 2020, the shelter-in-place orders were issued to cope with the first wave of the world pandemic. Large amounts of business, especially the small, private business, are impaired, and many employees, especially those in the service industries, are laid off. With the enormous cost of the economy, we need to ask one question — were the shelter-in-place orders help prevent the spread of the virus? This article will not discover the casual links here, but will use data visualization to show the correlation of shelter-in-place orders and the confirmed coronavirus cases.

US Aggregate Conditions

This analysis will look at the aggregate data in the US first to examine the overall situation. Besides, this analysis will focus on Santa Clara County in the Bay Area. The County-level data provides better accuracy in giving insights about what is going on in the specific region facing customized orders.

The US Centers for Disease Control (CDC) provides detailed datasets for the US's coronavirus cases across different regions, genders, ages, etc. After downloading the dataset showing the COVID-19 cases and death by the State, I use the following code to draw the subplots below:

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()
Data Source: https://data.cdc.gov/Case-Surveillance/United-States-COVID-19-Cases-and-Deaths-by-State-o/9mfq-cb36

The graph shows both the total confirmed cases and death keep increasing since March. The increasing speeds, showing by the slope of the total number or by the new cases and death, fluctuate throughout the year. The numbers in confirmed cases and death increased rapidly in March, stabilized through July, and had another spike after July. How are these numbers correlated with the economic conditions? I used the unemployment levels in the US to answer this question.

I downloaded the number of unemployed people from Fred, Economic Data, collected through the US Bureau of Labor Statistics. The data is updating every month, which is the highest frequency for data describing the economic conditions. To match the Covid cases with the economic situation, we need to merge the two datasets by the month:

#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))

The drawback of this graph is that the data are at different frequencies. However, the slopes and levels of the curves can still tell the story. March was the month when both coronavirus and economic conditions got worse. From March to June, the spread of the virus didn’t worsen as the unemployment level maintained high. After July, the unemployment level started to go down, but the confirmed cases went up, with a spike in the death rate. Shutting down economic activities by closing the restaurants, issuing shelter-in-place orders, etc., slows down the economy but helps prevent the spread of the virus (from March to June); reopening and maintaining economic activities increase the spread of the virus (After July). The graph roughly shows the dilemma between economic recovery and controlling the virus during the pandemic.

Even though March to June was when the whole country acted up to prevent the virus from spreading, different states, even different counties, have different policies issued by the local government. Only looking at the aggregate data may not be enough to find the relationship between unemployment level and COVID-19 cases. Thus in the next section, I will focus on visualizing the data at a specific local area, the Santa Clara County in the Bay Area.

Facing the first wave of the world pandemic, Santa Clara County announced the shelter-in-place order on March 16th, and as the situation got better, the economy slowly reopened in May. On July 2nd, the county announced the Risk Reduction Orders to the economic activities to be reopened. We can collect the data to see the unemployment level and COVID situations around the two dates.

For the unemployment level data at Santa Clara County, I get the county-level unemployment data from the US Bureau of Labor Statistics, Local Area Unemployment Statistics, and filter out the rows describing the Santa Clara County.

#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’]]

For COVID-19 case data, the Santa Clara County government holds its own website: County of Santa Clara, Open Data Portal: Covid-19 case counts by date for confirmed cases, County of Santa Clara, Open Data Portal: count of deaths with Covid-19 by date for death.

## 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')
Santa Clara County Situation

The dilemma is shown much more clearly in Santa Clara County. During the shelter-in-place periods from March to June, the unemployment level spiked but the confirmed cases and death are increasing much slower than when the economy reopened in July. However, after the reopening, the unemployment level decreased much faster than in the previous months.

This is not the dilemma only faced by one local government or one country. In fact, the whole world is dealing with Pandemic together. I once came across a great YouTube video discussing the choice that every country faces to choose Lives vs. Economic and the impacts of their choices in terms of their own countries and the entire world. Fortunately, the research on vaccines is in the final steps, and with the effective vaccine, the world should step out of the dilemma soon. 2020 has been different and difficult for everyone. Let’s stay strong and hope that we have already passed the darkest moment, and the dawn is in the very near future.

Thank you for reading! Here is the list of all my blog posts. Check them out if you are interested!

An Idea (by Ingenious Piece)

Everything Begins With An Idea

Zijing Zhu

Written by

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

An Idea (by Ingenious Piece)

No Matter What People Tell You, Words And Ideas Can Change The World.

Zijing Zhu

Written by

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

An Idea (by Ingenious Piece)

No Matter What People Tell You, Words And Ideas Can Change The World.

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

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