Recreating the Financial Times’ Economic Recovery Graph With Google Mobility Data

At this point, I think it’s safe to say we’ve all seen more than our fair share of news about COVID-19, as well as graphs that show how the number of cases has been increasing or decreasing in recent times. The latter provides us with tools to see for ourselves how the situation has been evolving since the start of the year and to make estimates as to when it will end.

But there’s another type of graph that should be getting just as much attention. That type consisting of graphs that present how society is reacting to the pandemic. After all, even if cases go down, things won’t be back to normal until the economy is back up and people are circulating as they did before. That’s where posts such as The Financial Times’ “Pandemic crisis: Global economic recovery tracker” come into action. They give us an early picture of how close or far away we are from the world pre-crisis.

Photo by Julian Wan on Unsplash

In this post, we’ll dive into how to make a graph that reflects society’s current state using the data from Google’s COVID-19 Community Mobility Reports and taking inspiration from the Leisure graph from the financial times’ post.

We’ll be using Python along with pandas to process the data and Matplotlib to visualize it. The code will be available in Google Colab and the result should look something like this:

Plots for 6 different countries displaying how much people have been staying home. (the value 0 references the pre-crisis state)

Plots for 6 different countries displaying how much people have been frequenting their workplaces.

After downloading the .csv file or using the link directly, we read it using panda’s built-in read_csv() function and begin our work.

A basic look at the data frame using the pandas df.info() method.

The dataset contains a column for countries and 2 for sub-regions, which can vary depending on the country since different countries decide to organize their territories differently.

For our purposes, we’ll be dropping the sub-regions columns but you can choose to keep them if you want to do a more in-depth analysis for any country of your choice. To do that, we’ll save a copy of the data frame into a different variable, keeping only what we want. I’ll also be dropping the metro_area and iso_3166_2_code columns since they’re also not of use for what we want.

df_countries = df.loc[df.sub_region_1.isnull(), :].iloc[:, [1, 7, 8, 9, 10, 11, 12, 13]].copy()

After filtering what we want to keep from what we can throw away, we still need to do a few things to be able to work with this data.

First, we’ll change the dates, which are currently stored as strings, into the DateTime data type, which will allow us to manipulate them better, perform operations, and all that fancy stuff. Then, we’ll set them as the new index for our data frame since it’s way easier to sort by dates than it is by your standard sequence of integers.

df_countries.date = pd.to_datetime(df_countries.date)
df_countries.index = df_countries.date
df_countries.drop(labels='date', axis=1, inplace=True

And finally, we’ll change the column names so they’re easier to work with.

df_countries.columns = ['country', 'date', 'retail', 'grocery', 'parks', 'transit', 'workplaces', 'residential']

With that basic pre-processing out of the way, we can get down to making our plot and we start by applying a rolling average of 7 days to our dataset so our data is more reliable. Since, as we know, how many people will go grocery shopping on a given day doesn’t depend solely on the fact that there’s a pandemic happening and those numbers can and will fluctuate.

fdf_countries = df_countries.copy()
fdf_countries.iloc[:, 1:] = df_countries.rolling(window=7).mean()

Once that’s done, we define the plot style we’ll be using, create a figure with, in this case, 6 columns, since I’ll be displaying different countries, and create a variable with the item we’ll be analyzing and the countries that will be a part of our analysis. I also added one more line to change the color of the background to match that of the financial times’ page.

plt.style.use("fivethirtyeight")
fig, ax = plt.subplots(nrows=1,ncols=6,figsize=(20,4))
item = "workplaces"
countries = ["Japan","United States","Canada","Spain","Brazil", "South Africa"]
fig.patch.set_facecolor('#fff9f4')

The rest of the code will be executed inside a for loop, as it will recreate the plot multiple times (once for every country). It loops over the list of countries created above and is made using the enumerate() function so that we have access to both the name of the country and its index.

for i,country in enumerate(countries):

First, we’ll plot the points that indicate the instance in the graph with the lowest value for the item we’re analyzing, as well as the most recent one, so that we can measure how much has increased since reaching the bottom.

To do that we first need to find those values. And, to do that, we’ll store into a new variable all instances where we reach the lowest value of the plot for the country we’re studying, which could be one or multiple. Then, we store the most recent instance in the data frame for that country into another variable.

country_low = fdf_countries[(fdf_countries.country == country) & (fdf_countries[item] == fdf_countries.groupby(by='country').min()[item][country])].copy()
country_last = fdf_countries[df_countries.country == country].iloc[-1, :]

We then use the most recent value we stored in country_last and the first of the values we stored in country_low to plot our points.

ax[i].plot([country_low.index[0]], country_low.iloc[0,:][item], 'wo', zorder=3,markersize=10)
ax[i].plot([country_low.index[0]], country_low.iloc[0,:][item], 'bo', zorder=3,markersize=7)
ax[i].plot(country_last.name, country_last[item], 'wo', zorder=3,markersize=10)
ax[i].plot(country_last.name, country_last[item], 'bo', zorder=3,markersize=7)

After that, we draw the lines that connect the two points for easier visualization and add the texts that display the values of the graph at each point as well as the increase between them.

# Drawing the lines to indicate how much the item has increasced over time
ax[i].plot(country_last.name, country_last[item] - 3.5, 'ro', zorder=3,markersize=6, marker = '^')
ax[i].plot([country_low.index[0], country_last.name], [country_low.iloc[0,:][item], country_low.iloc[0,:][item]], '--', color='gray', linewidth= 2)
ax[i].plot([country_last.name, country_last.name], [country_low.iloc[0,:][item], country_last[item] - 3.5], '--', color='red', linewidth= 2)
# Adding the texts that indicate the values in the marked points as well as the increasce between them
ax[i].text([country_low.index[0]], country_low.iloc[0,:][item] - 6, round(country_low.iloc[0,:][item], 1))
ax[i].text(country_last.name - datetime.timedelta(33), country_last[item] + 3, round(country_last[item], 1), fontsize=12)
ax[i].text(country_last.name - datetime.timedelta(70), country_low.iloc[0,:][item] -6, str(abs(round((country_last[item] - country_low.iloc[0,:][item])/country_low.iloc[0,:][item]*100, 1))) + "%", color='red')

Next up comes the heart of the plot. We’ll plot the data frame data relating to our chosen item over time for all countries in the dataset. This is why in the image of the plot shown at the start of this post there are all of those black lines. Each one of those pertains to a different country. Of course, you can choose to limit the number of countries shown to facilitate reading each one of them.

Then we’ll plot the same thing again, but only for our country of interest, allowing it to contrast with the line plots of the other countries.

df_countries.groupby(by=[df_countries.index, 'country']).mean().unstack()[item].rolling(window=7).mean().plot(legend=False,color="grey",linewidth=1, alpha=0.1, ax=ax[i], zorder=0)
df_countries.groupby(by=[df_countries.index, 'country']).mean().unstack()[item][country].rolling(window=7).mean().plot(legend=False,color="white",linewidth=5, alpha=1, ax=ax[i], zorder=1)
df_countries.groupby(by=[df_countries.index, 'country']).mean().unstack()[item][country].rolling(window=7).mean().plot(legend=False,color="blue",linewidth=3, alpha=1, ax=ax[i], zorder=2)

After that just comes formatting the graph to make it easier to read and I have also added a few more lines to change the color of the frames and inside of the plot to match that of the Financial Times as I did previously.

# Setting background color
ax[i].set_facecolor('#fff9f4')
ax[i].spines['bottom'].set_color('#fff9f4')
ax[i].spines['top'].set_color('#fff9f4')
ax[i].spines['left'].set_color('#fff9f4')
ax[i].spines['right'].set_color('#fff9f4')
# Formatting for better visualization
fdf_countries = df_countries.copy()
fdf_countries.iloc[:, 1:] = df_countries.rolling(window=7).mean()
country_low = fdf_countries[(fdf_countries.country == country) & (fdf_countries[item] == fdf_countries.groupby(by='country').min()[item][country])].copy()
ax[i].set_title(country,fontsize=12,ha='right')
ax[i].xaxis.grid(False)
ax[i].set_xlabel("")
ax[i].set_xticklabels(["","Mar","","","Jun","","","Sep"])
ax[i].xaxis.set_tick_params(labelsize=12, labelrotation=0)
ax[i].yaxis.set_tick_params(labelsize=12)
if (i==0) or (i==5):
   ax[i].yaxis.tick_right()
else:
   ax[i].set_yticklabels([])

And we finish by using the plt.show() method to see the fruits of our labor.

Again, this is what we get once we finish all the steps above.

By changing the item variable to one of the other variables in the dataset, you can get some very interesting insights as to how different countries have dealt and are dealing with the pandemic and even how certain variables could have influenced others.

These graphs show us how society is dealing with the current times and are one of the best ways to know how close we are to going back to normal, and that’s why they shouldn’t be overlooked.