> ## Content Index
> Fetch the complete content index at: https://www.bambooweekly.com/llms.txt
> Use this file to discover other available public pages before exploring further.

# Bamboo Weekly #28: Pret a Manger (solutions)
- URL: https://www.bambooweekly.com/bw-28-pret-a-manger-solution/
- Published: 2023-08-10T15:00:38.000Z
- Updated: 2026-08-23T09:37:08.000Z
- Description: Get practice with Excel files, dates and times, window functions, and filtering
- Author: Reuven M. Lerner
- Tags: excel, datetime, window-functions, filtering

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](https://www.ons.gov.uk/?ref=bambooweekly.com)). 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.

![https://pe-insights.com/wp-content/uploads/2020/10/pret-a-manger.jpg](https://storage.ghost.io/c/06/ba/06ba0cc0-be6f-4de7-af2f-5c20165279b9/content/images/image/fetch/f_auto,q_auto:good,fl_progressive:steep/https-3a-2f-2fsubstack-post-media.s3.amazonaws.com-2fpublic-2fimages-2fdfce91cb-fd91-448c-861f-7c2a3bb51f6d_2500x1875.jpg)

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](https://www.ons.gov.uk/economy/economicoutputandproductivity/output/bulletins/economicactivityandsocialchangeintheukrealtimeindicators/3august2023?ref=bambooweekly.com) .

### 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/transactionsatpretamanger
```

The 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](https://www.ons.gov.uk/economy/economicoutputandproductivity/output/methodologies/coronavirusandthelatestindicatorsfortheukeconomyandsocietymethodology?ref=bambooweekly.com) .

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 pd
```

Then we can load the Excel file. I downloaded it onto my computer, and was thus able to read it in with the [read\_excel](https://www.bambooweekly.com/pandas-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:

1. We can use “[loc](https://www.bambooweekly.com/pandas-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.
2. 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](https://www.bambooweekly.com/pandas-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: int64
```

But 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](https://www.bambooweekly.com/pandas-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: int64
```

Remember 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](https://www.bambooweekly.com/pandas-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: int64
```

In 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](https://www.bambooweekly.com/pandas-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: float64
```

Next, 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: float64
```

Finally, I want to see which three categories of stores are doing better than one year ago. Once again, I can use [head](https://www.bambooweekly.com/pandas-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: float64
```

We 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](https://www.bambooweekly.com/pandas-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](https://regexpcrashcourse.com/?ref=bambooweekly.com).)

But wait, I also asked you to use “[assign](https://www.bambooweekly.com/pandas-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](https://www.bambooweekly.com/pandas-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: float64
```

We 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:

![](https://storage.ghost.io/c/06/ba/06ba0cc0-be6f-4de7-af2f-5c20165279b9/content/images/image/fetch/f_auto,q_auto:good,fl_progressive:steep/https-3a-2f-2fsubstack-post-media.s3.amazonaws.com-2fpublic-2fimages-2f1e72ea9e-7803-471e-8c76-aadb7a63de52_3620x680.png)

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: float64
```

That’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: float64
```

Look 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](https://www.bambooweekly.com/pandas-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](https://www.bambooweekly.com/pandas-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:

![](https://storage.ghost.io/c/06/ba/06ba0cc0-be6f-4de7-af2f-5c20165279b9/content/images/image/fetch/f_auto,q_auto:good,fl_progressive:steep/https-3a-2f-2fsubstack-post-media.s3.amazonaws.com-2fpublic-2fimages-2f2dd5476a-f7d3-43cb-993e-c3e01c2f32c8_3656x624.png)

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](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.idxmin.html?ref=bambooweekly.com) and [idxmax](https://www.bambooweekly.com/pandas-idxmax/). But wait, how can I calculate both of those on our data frame? I can use the “[agg](https://www.bambooweekly.com/pandas-agg/)” method, passing it a list of the methods I want to run:

```
df.loc['2023'].resample('1M').mean().agg(['idxmax', 'idxmin'])
```

The result:

![](https://storage.ghost.io/c/06/ba/06ba0cc0-be6f-4de7-af2f-5c20165279b9/content/images/image/fetch/f_auto,q_auto:good,fl_progressive:steep/https-3a-2f-2fsubstack-post-media.s3.amazonaws.com-2fpublic-2fimages-2f4e7c0b2d-4f42-4d94-8b16-a16ea4b29eee_3660x342.png)

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](https://drive.google.com/file/d/1KILrY5cdkAyNm6I4%5FbvYtFR0AI2Kuc0D/view?usp=sharing&ref=bambooweekly.com)

I’ll be back next week with more Pandas and current events.

Reuven