Economic statistics can take a long time to collect and publish. That’s because they can be complex to collect, complex to compute, and then complex to validate. These statistics can have a big impact, so you don’t want to get them wrong. The downside, of course, is that the delay between the data’s collection and publication also leads to a lag in policy-makers’ ability to make changes rapidly.
That’s presumably part of the reasoning behind the experimental real-time data collection project run by the UK’s Office for National Statistics (https://www.ons.gov.uk). They have identified a number of ways to collect and publish economic data quickly, in order to help government and business leaders to act more quickly.
This week, we looked at data the ONS collects about Pret a Manger, a popular chain of sandwich shops in the UK. The data describes how many in-store purchases were made in a given week at Pret shops. We don’t know what they bought, how much they paid, or how many people were eating together. We also don’t know how many orders were made at any given location; the data is grouped into regional categories.
Moreover, we don’t have the raw sales numbers. Rather, they are indexed to January 2020. If the data shows 100 for a given region’s weekly report, that means sales are equivalent to those in January 2020. A 50 would mean half that amount, and a 200 would mean twice that amount. In that way, we can see how in-store sales have changed in the last few years.
You can read about the latest real-time data, including Pret a Manger, at https://www.ons.gov.uk/economy/economicoutputandproductivity/output/bulletins/economicactivityandsocialchangeintheukrealtimeindicators/3august2023 .
Data and questions
The data can be downloaded from the main page for the Pret a Manger research:
https://www.ons.gov.uk/economy/economicoutputandproductivity/output/datasets/transactionsatpretamangerThe excel file itself can be downloaded from here:
/content/files/file.xlsx A full description of these store locations, and of the overall methodology for this study, can be found at https://www.ons.gov.uk/economy/economicoutputandproductivity/output/methodologies/coronavirusandthelatestindicatorsfortheukeconomyandsocietymethodology .
This week, I gave you seven questions and tasks. Let’s get to them; a link to the Jupyter notebook that I used to solve these problems follows the answers themselves.
Read the data into a data frame, treating the "Week Ending" column as dates and using it as the index.
While CSV is certainly a popular format for data distribution,
We’ll start by loading Pandas:
import pandas as pdThen we can load the Excel file. I downloaded it onto my computer, and was thus able to read it in with the read_excel method. However, a simple call to read_excel won’t be enough:
- The data is located on the third sheet of the Excel document, which we need to tell to “read_excel”,
- The data doesn’t start until row 4 of the sheet,
- We want to parse the “Week Ending” column as a date, and
- We want to set the “Week Ending” column to be the index of our data frame.
Put together, we’ll need the following code:
filename = 'transactionsatpretamangerdataset030823.xlsx'
df = pd.read_excel(filename,
sheet_name=2,
header=3,
parse_dates=['Week Ending'],
index_col='Week Ending')Notice that because Python (and Pandas) use zero-based indexing, sheet number 3 is identified with an index of 2, and line 4 is passed to the “header” keyword argument with a value of 3. It’s a bit confusing, especially when the rows are explicitly numbered in Excel — but we manage to read the data.
In the end, our data frame has 126 rows (one for each week during which data was collected, indexed by the final day in that week) and 10 columns (one for reach regional category). The dtypes of the non-index columns are all int64, which is overkill for the numbers we’ll be using, but that’s not something to worry about in such a small data frame.
What three categories of stores are doing best, as of the latest data, vs. the baseline?
We can get the most recent data — that is, the final row in the data frame — in either of two ways:
- We can use “loc” to retrieve that row via its date. After all, we can always retrieve a row from a data frame using “loc” and the row’s index.
- We can also use “iloc” to retrieve the row via its numeric position, much as we retrieve elements from strings, lists, and tuples in Python. No matter what the index, iloc lets us use numbers. You can even use -1 to indicate that you want to count from the end, rather than from the start.
I decided to use iloc:
df.iloc[-1]This retrieved the final row:
Yorkshire 123
London: Suburban 112
Manchester 104
London: West End 96
London: Stations 90
Regional Towns 107
Scotland 96
London: Airports 163
London: City Worker 82
Regional Stations 82
Name: 2023-07-27 00:00:00, dtype: int64But I wasn’t interested in finding all of the values. Rather, I wanted to find the locations with the three greatest values vs. their original baseline. To get this, I’ll need to run “sort_values” on the series we got back. Here, I’m going to switch to a multi-line, chained-method approach, even if it isn’t strictly necessary:
(
df
.iloc[-1]
.sort_values(ascending=False)
)This gives me the following result:
London: Airports 163
Yorkshire 123
London: Suburban 112
Regional Towns 107
Manchester 104
London: West End 96
Scotland 96
London: Stations 90
London: City Worker 82
Regional Stations 82
Name: 2023-07-27 00:00:00, dtype: int64Remember that this chained approach works because Python’s normally very strict rules for indentation and line endings melt away when you open parentheses. This allows us to put part of a query on each line, making it more readable, as well as easier to comment on and edit.
Our results are great, but I’m only interested in the three highest scores. I can use head for that:
(
df
.iloc[-1]
.sort_values(ascending=False)
.head(3)
)The result:
London: Airports 163
Yorkshire 123
London: Suburban 112
Name: 2023-07-27 00:00:00, dtype: int64In other words, compared with January 2020, the stores that are having the greatest number of in person sales are located at London’s four airports, in Yorkshire, and in suburban London.
This already hints at what we’re going to see, namely that in-person purchases of lunch food in areas with office buildings went down during the pandemic and haven’t completely recovered, even if they’re doing better than was the case. By contrast, airports are doing very well, reflecting the “revenge tourism” attitude that I’ve both heard about and personally experienced, flying everywhere you can, now that you can.
What three categories of stores have had the greatest increase since one year ago (i.e., 52 weeks ago), vs. the baseline?
To answer this question, we’ll need to compare the latest data (which we already found with iloc[-1] with the data from 52 weeks ago (which we could retrieve with iloc[-53]. We could do that with this code:
df.iloc[-1] - df.iloc[-53]There’s nothing wrong with this, per se, except that there’s an easier approach: We can use the “diff” method, which normally compares each row with its predecessor, returning a new data frame with the same index as the original but with the numeric diffs in place of the original values. (The first row is typically NaN.)
Here, I want to do something similar, except that I don’t want to compare each row against its predecessor. Rather, I want to compare with 52 weeks previous. Fortunately, “diff” has a “periods” keyword argument, telling Pandas how much to look back when making such a comparison:
df.diff(periods=52)Let’s put this into our nicer, chained-method format:
(
df
.diff(periods=52)
)This produces a new data frame, as I mentioned above. But I’m actually just interested in the comparison we calculated between the most recent data and 52 weeks before. I can thus retrieve the final row of the diff’ed data frame:
(
df
.diff(periods=52)
.iloc[-1]
)Since I’m only retrieving a single row from the data frame, it comes back as a series:
Yorkshire -3.0
London: Suburban -2.0
Manchester 12.0
London: West End 16.0
London: Stations 2.0
Regional Towns 25.0
Scotland 7.0
London: Airports 23.0
London: City Worker 5.0
Regional Stations 9.0
Name: 2023-07-27 00:00:00, dtype: float64Next, I want to sort the values. I can do that with “sort_values”, as before:
(
df
.diff(periods=52)
.iloc[-1]
.sort_values(ascending=False)
)That gives me the same values as before, but sorted:
Regional Towns 25.0
London: Airports 23.0
London: West End 16.0
Manchester 12.0
Regional Stations 9.0
Scotland 7.0
London: City Worker 5.0
London: Stations 2.0
London: Suburban -2.0
Yorkshire -3.0
Name: 2023-07-27 00:00:00, dtype: float64Finally, I want to see which three categories of stores are doing better than one year ago. Once again, I can use head for that:
(
df
.diff(periods=52)
.iloc[-1]
.sort_values(ascending=False)
.head(3)
) The result:
Regional Towns 25.0
London: Airports 23.0
London: West End 16.0
Name: 2023-07-27 00:00:00, dtype: float64We see that the regional towns are doing the best — but in London, the biggest improvement has been at the airports and in the West End. I’m going to guess (or maybe just speculate) that the growth in regional towns is from people working from home, and at London airports and the West End because of a boom in tourism.
In the latest data, how do London stores do, on average, vs. non-London stores?
On the face of it, this seems like an easy question to answer. We find the stores in London in the latest data, find the stores not in London from the latest data, and then compare their means.
But how can we retrieve only those locations? That actually seems a bit difficult.
My solution was to use a combination of “filter”, which lets us select columns based on text or a pattern of text, and regular expressions. (If you don’t know regular expressions, then you should learn them — check out my free, e-mail based Regular Expressions Crash course.)
But wait, I also asked you to use “assign” here, rather than performing explicit assignment with Python’s = operator.
So, where to start? Let’s begin by remembering that “assign” lets us add a new column to a data frame via keyword argument. The argument’s name is the new column name, while the value is the set of values we want to assign to it.
Here, I’m going to create two new columns. One will be called “AllLondon”, and it’ll contain the mean of all columns with “London” in the name. The second, because I’m so creative at naming, will be called “NonLondon”, and it’ll contain the mean of all columns without “London” in the name.
If I want all of the columns containing the word “London”, then I can use filter as follows:
df.filter(regex='^London', axis='columns')Note that when you filter based on column names, you have to specify that you want axis=“columns”. Otherwise, it’ll work via the index, on the rows. This returns a subset of our original data frame, with only those columns starting with the word “London”. (I indicate that the string has to start with “London” with the ^ character, which acts as an “anchor.”)
I can then invoke “mean” on this mini-data frame, again specifying that I want to work on the columns:
df.filter(regex='^London', axis='columns').mean(axis='columns')I can then pass this to assign, in order to create a new column:
(
df
.assign(AllLondon=df.filter(regex='^London', axis='columns').mean(axis='columns')
)Notice that doing things in this way means that we get back a new data frame with the new column, but that the new column hasn’t been assigned back to df.
I can do the same thing to create the NotLondon column, using a regexp that allows for anything which doesn’t start with the letter L. Since no other column does, we’re safe:
(
df
.assign(AllLondon=df.filter(regex='^London', axis='columns').mean(axis='columns'),
NonLondon=df.filter(regex='^[^L]', axis='columns').mean(axis='columns'))
) We now have a data frame containing all of our original columns, plus our two summary columns. Let’s use “filter” again to keep only those final columns. I’ll again use anchoring, both at the start (^) and end ($) of the string, allowing for three characters before the word “London”. Whadaya know, only two columns match that pattern — AllLondon and NotLondon:
(
df
.assign(AllLondon=df.filter(regex='^London', axis='columns').mean(axis='columns'),
NonLondon=df.filter(regex='^[^L]', axis='columns').mean(axis='columns'))
.filter(regex='^...London$')
) Finally, I select the final row, representing the most recent data:
(
df
.assign(AllLondon=df.filter(regex='^London', axis='columns').mean(axis='columns'),
NonLondon=df.filter(regex='^[^L]', axis='columns').mean(axis='columns'))
.filter(regex='^...London$')
.iloc[-1]
) The result:
AllLondon 108.6
NonLondon 102.4
Name: 2023-07-27 00:00:00, dtype: float64We can see that on average, London-based stores and those outside of London did better last week than they did in early 2022. The data isn’t seasonally adjusted, and it might not be fair to compare July with January — but we can see that London shops did, on average, a bit better this past week than they had in January 2020.
Which London locations did the best in the first month of 2021? Compare the results with the most recent week.
When someone says “the first month of 2021,” you probably think about January, right? But our data doesn’t include January 2021 — it starts in March 2021. So, that was a bit unfair of me, I must admit.
But how can I retrieve values from March 2021? It’s actually way, because our index contains datetime values. We can thus use “loc” and specify only the year and month, leaving off the date. That makes the date a wildcard, and we get all of the rows for any day in March 2021:
(
df
.loc['2021-03']
)Here’s what I got:
Not bad, but of course I want to get the mean for each of these locations. I can thus say:
(
df
.loc['2021-03']
.mean()
)Because I’ve asked for the mean on each column, I now get back a series:
Yorkshire 62.25
London: Suburban 73.25
Manchester 50.50
London: West End 47.75
London: Stations 41.00
Regional Towns 51.25
Scotland 28.50
London: Airports 12.75
London: City Worker 34.25
Regional Stations 19.50
dtype: float64That’s good, but now I can sort the values and find out which places did best and worst:
(
df
.loc['2021-03']
.mean()
.sort_values(ascending=False)
)asdfa
London: Suburban 73.25
Yorkshire 62.25
Regional Towns 51.25
Manchester 50.50
London: West End 47.75
London: Stations 41.00
London: City Worker 34.25
Scotland 28.50
Regional Stations 19.50
London: Airports 12.75
dtype: float64Look at the airport figures — today, the airports are doing great, but back in March 2021 — not surprisingly — the figures were awful. Suburban London shops were doing well then, and are doing well now, and I’m going to guess that it has to do with people being at home in general, as well as contiuing to work from home.
Calculate the mean value for each location in each calendar year.
Calculating the mean value for each location in each calendar year sounds suspiciously like a grouping operation, for which we would use “groupby”. And yes, we could retrieve the year information from each of the dates, and do something group-ish there.
But why work so hard, when we have the amazing “resample” method at our fingertips? We just tell it what unit of granularity we want (e.g., “1Y” for one year), and then tell it to calculate the mean on all of the columns:
df.resample('1Y').mean()We get the following output, with one row per year in the data set and one column for each … well, for each column, aka each location category:
I’ll add that while our data here doesn’t have any holes, “resample” calculates all time periods from the start of your data until the end. If there is missing data, you’ll get NaN during that time period.
Calculate the mean value for each location during each month in 2023. In which month did each regional category have its best and worst results?
Finally, let’s calculate the mean value for each location, for each month in 2023. In which month did each category have best and worst results?
To do this, I’m first going to use “loc” to pare down our data frame to only include 2023:
df.loc['2023']Then, with this smaller data frame, I’m going to run “resample,” asking for one-month blocks (“1M”):
df.loc['2023'].resample('1M')I’m then going to ask for the mean of each month in 2023, for each location:
df.loc['2023'].resample('1M').mean()Finally, I want to know the date of the highest and lowest value for each location. I can get those with idxmin and idxmax. But wait, how can I calculate both of those on our data frame? I can use the “agg” method, passing it a list of the methods I want to run:
df.loc['2023'].resample('1M').mean().agg(['idxmax', 'idxmin'])The result:
That’s it for this week’s analysis! Any thoughts, comments, or corrections? Post them to our forum.
You can download the Jupyter notebook that I used from here: https://drive.google.com/file/d/1KILrY5cdkAyNm6I4_bvYtFR0AI2Kuc0D/view?usp=sharing
I’ll be back next week with more Pandas and current events.
Reuven