> ## 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 #20: World inflation (solutions)
- URL: https://www.bambooweekly.com/bw-20-world-inflation-solution/
- Published: 2023-06-15T15:01:27.000Z
- Updated: 2026-08-23T09:37:12.000Z
- Description: Get practice working with Excel, cleaning, multi-index, sorting, plotting
- Author: Reuven M. Lerner
- Tags: excel, cleaning, multi-index, sorting, plotting

This week, we’re looking at the World Bank’s database of inflation information, comparing inflation levels in different countries and as measured in different ways. As I wrote yesterday, inflation is something that we have to measure, and there are different perspectives on how and what we measure. Should we include food prices? And if so, which food? Which brands? In which locations? It quickly gets very complicated.

I should add that Planet Money’s “Indicator” podcast followed a government economist who was checking and comparing prices. Have a listen here: [https://www.npr.org/2021/12/29/1068853249/how-do-you-measure-inflation-indicator-favorite](https://www.npr.org/2021/12/29/1068853249/how-do-you-measure-inflation-indicator-favorite?ref=bambooweekly.com)

From what I understand, the World Bank’s inflation database collects data from various countries, on the assumption that we can trust those countries’ measurements. (Whether we can is another question entirely!) The Excel document contains a number of different inflation measurements, among them:

- Headline consumer price index
- Food price index
- Energy price index
- Official core consumer price index, the main measure of inflation in most countries
- Producer price index

Each of these is measured on a monthly, quarterly, and annual basis. We will look at only a few of these, and only on an annual basis. (There are some other sheets as well in this document, but we’ll ignore those.)

### Data and questions

The World Bank recently released their latest report on inflation rates, as described here:

[https://www.worldbank.org/en/research/brief/inflation-database](https://www.worldbank.org/en/research/brief/inflation-database?ref=bambooweekly.com)

We’ll be working with this database, which is distributed as an Excel file. You can download it from here:

[/content/files/en/doc/1ad246272dbbc437c74323719506aa0c-0350012021/original/inflation-data.xlsx](https://www.bambooweekly.com/content/files/en/doc/1ad246272dbbc437c74323719506aa0c-0350012021/original/inflation-data.xlsx)

The Excel file contains a number of sheets. The first is a general introduction to the document, and the second outlines what the rest of the sheets contain. We’ll be loading a number of these into Pandas as part of our analysis.

There were nine questions this week. Here they are, along with my detailed solutions and explanations:

### From the Excel file, read the following three tabs: hcpi\_a, ccpi\_a, and ppi\_a.

As usual, the first thing that I have to do is load up Pandas:

```
import pandas as pd
```

With that in place, I want to load the Excel file into a data frame. I can normally do that with the “[read\_excel](https://www.bambooweekly.com/pandas-read-excel/)” function:

filename = 'inflation-data.xlsx'

df = pd.read\_excel(filename)

Except that with our current file, this won’t work. The reason is that the file contains multiple sheets — and when you invoke read\_excel on a multi-sheet Excel file, you don’t get a data frame back, but rather a dictionary. The dict’s keys are the sheet names, and the dict’s values are data frames.

There’s nothing wrong with this per se, but we are only interested in a handful of sheets from the file. We can specify which ones we want to load by passing the “sheet\_name” keyword argument to read\_excel:

```
all_dfs = pd.read_excel(filename, 
  sheet_name=['hcpi_a', 'ccpi_a', 'ppi_a'])
```

Note that we can specify sheet names either with strings (as I did here), using the same names as we see at the bottom of the spreadsheet, or with integers, numbering the sheets starting with 0\. In either case, the values that we pass to “sheet\_name” will be the keys of the dict — so they might be strings, and might be integers.

How can we then take all of the data frames that we received, and turn them into a single data frame? The most straightforward way is with [pd.concat](https://www.bambooweekly.com/pandas-concat/), a top-level function that takes an iterable of data frames and returns one new data frame, the result of stacking all of the input data frames vertically:

```
df = pd.concat(all_dfs.values())
```

This will only work if all of the data frames have the same columns, and if they have distinct values that we can use to identify them. Both are true in our case, giving us one long data frame.

We’ll now go through the process of turning our long data frame, the result of concatenating the three sheets that we imported, into a more useful form.

### Remove any rows in which either "Country" or "Series Name" is NaN.

We’ll want to modify our data frame such that the “Country” and “Series Name” columns are used as a multi-index. Unfortunately, some rows have NaN (“not a number”) values, which refer to [missing data](https://pandas.pydata.org/pandas-docs/stable/user%5Fguide/missing%5Fdata.html?ref=bambooweekly.com). While there’s no way around NaN values in Pandas, or in general, we do want to remove them from any columns we’ll use as an index.

The “[dropna](https://www.bambooweekly.com/pandas-dropna/)” method returns a new data frame, one without any rows containing NaN values. That’s right — if we call dropna on a data frame, then any row containing even a single NaN is removed.

That’s great, in that it guarantees you’ll get good data without any missing values. But if you try it on our data frame, you’ll find that it removes *all* of the rows! That’s because many of them contain NaN values in other columns.

One solution is to pass the “thresh” keyword argument to dropna. That allows us to indicate the minimum number of non-NaN values we require in order to keep a row. But that’s not very specific; we only care about NaN in Country and Series Name, and want to make sure that there are zero NaNs there.

Fortunately, dropna has a “subset” keyword argument, telling Pandas that it should only check specific columns for NaN values. We can thus write:

```
df = df.dropna(subset=['Country', 'Series Name'])
```

Notice that, as is almost always the case with Pandas, we get back a new data frame, rather than modifying the original. We thus assign the result back to df, ensuring that these two columns have known values before using them in a multi-index.

### Turn those columns ("Country" and "Series Name") into a multi-index.

Now that we have removed NaN values from Country and Series Name, we can turn them into an index. We can always call “[set\_index](https://www.bambooweekly.com/pandas-set-index/)” on a data frame, naming the column that should be used as an index. Or we can pass a list of strings, the names of multiple columns to be used in an index, known as a “multi-index”:

```
df = df.set_index(['Country', 'Series Name'])
```

As with “dropna”, our call to “set\_index” returns a new data frame based on the older one, containing a multi-index. The multi-index is constructed in the order that we specified, with the outer layer being “Country” and the inner layer being “Series Name”.

### Remove any columns that aren't year numbers.

Finally, I asked you to remove any columns that aren’t year numbers. There are a few ways that we could do this. One would be to iterate over the columns, finding only those that are integers, and using them to select those columns from the data frame:

```
df[[one_column
 for one_column in df.columns
 if isinstance(one_column, int)]]
```

Much as I love list comprehensions, this approach won’t fit into the method-chaining approach I wanted to use this week. However, I could find all of those columns that *aren’t* integers:

```
[one_column
 for one_column in df.columns
 if not isinstance(one_column, int)]
```

Then I can use it to drop all of those columns from the data frame:

```
df = df.drop([one_column
 for one_column in df.columns
 if not isinstance(one_column, int)], axis='columns')
```

Here, I used the “[drop](https://www.bambooweekly.com/pandas-dropna/)” method to remove a bunch of columns from our data frame. Again, this method returns a new data frame, identical to the older one except that it lacks the columns that we asked to remove.

Note that “drop” can be used to remove rows or columns. If you want to remove columns (as I did here), you have to pass the “axis” keyword argument. And yes, you can pass 0 or 1 as the value for that keyword argument, but I prefer to use the words “rows” and “columns” for additional clarity.

### Sort the data frame by its index. If you encounter a performance warning, level=df.index.names as an argument to the sorting method.

Finally, I want to sort the data frame by its index. Normally, I could do that with the simple “[sort\_index](https://www.bambooweekly.com/pandas-sort-index/)” method:

```
df = df.sort_index()
```

However, in preparing these solutions, I kept getting a weird “PerformanceWarning: indexing past lexsort depth may impact performance” warning. I had seen this warning in the past, but it was supposed to be solved by sorting the data frame, so I was surprised that it stuck around even after sorting.

It seems that there’s a bug in some versions of Pandas, such that running sort\_index doesn’t set a flag that Pandas uses to know whether the index is sorted. (See issue [https://github.com/pandas-dev/pandas/issues/19771](https://github.com/pandas-dev/pandas/issues/19771?ref=bambooweekly.com) for more details.) The solution, for now, is to be explicit about the index columns on which we want to sort:

```
df = df.sort_index(level=df.index.names)
```

After doing this, I found that the warnings disappeared. Note that the behavior (other than the warnings) shouldn’t change.

### Create a line plot, showing the headline consumer price index vs. the producer price index in the United States. The x axis should be years, and the y axis should be the inflation rates. Use the query method.

Finally, I asked you to graph two of the different inflation measures (the headline consumer price index and the producer price index) in the US over the entire timespan of the data set.

And yes, in case you’re wondering: I did this to be cruel.

First: If we want all of the rows having to do with the United States, we can take advantage of the fact that our multi-index has the countries on the outside, and thus use

```
def.loc['United States']
```

That’ll return all of the rows for the US; given that we have read in three different inflation metrics, that’ll give us three rows (one for each metric) and a column for each year in the database.

But we don’t want all three metrics. We want only two metrics. How can we select only two of them? Maybe we could use a slice? Yes, we could, except that the two metrics I asked for weren’t (in my data set) adjacent, which meant that I was slicing strings with a step size, which is a bit weird.

We’ll get back to all of that in a bit, because I decided to ask you to do it in a different way, namely with the “[query](https://www.bambooweekly.com/pandas-query/)” method. This method lets us make queries of our data frame using an SQL-like syntax, all put in a single string. (Note that the string must all be on a single line, for reasons that I cannot figure out. So even if you use a triple-quoted Python string, you cannot include newlines.)

I know that some people use “query” because they prefer the syntax, and it can even sometimes run faster than a regular query with .loc. But in this case, I found that it was simpler to describe what I wanted using “query”:

```
df.query("""Country == 'United States' and `Series Name` in ['Headline Consumer Price Inflation', 'Producer Price Inflation']""")
```

Notice how I’ve written it here: I can use “==” for comparison, the word “and” to combine two conditions, and the Python “in” operator to search in a list. It’s a weird mix of SQL and Python, in my eyes, but it’s still nicer in many ways than we could otherwise get with .loc and friends, as we’ll see.

Notice that we don’t need to use quotes around the column names, and that we can use the column names in our index as if they were regular columns — not something we can normally do with .loc. Indeed, using quotes around the column names will give you the wrong results, as if you want to compare two strings, rather than a variable and a string. I’m guessing that this is because “query” does some form of Python’s “[eval](https://docs.python.org/3/library/functions.html?highlight=eval&ref=bambooweekly.com#eval)” to get this to work.

Actually, if you’re a bit sharp eyed, you see that I did use quotes around the “Series Name” column name — but they aren’t regular quotes. Rather, they’re backticks (aka “backquotes”), which aren’t normally used much in Python. Here, they allow us to mention a column with a space in its name, as we have here.

After performing this query, we have the rows and columns that we want! But if we produce a line plot with this data, it’s going to look super weird. That’s because the data is in the opposite form than we want, with the metrics in the rows and the years in the columns. We can, however, transpose (with [T](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.T.html?highlight=t&ref=bambooweekly.com#pandas.DataFrame.T)) the rows and columns, and then plot:

```
df.query("""Country == 'United States' and `Series Name` in ['Headline Consumer Price Inflation', 'Producer Price Inflation']""").T.plot.line()
```

I get the following:

![](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-2f53853da9-1fbb-494a-ae59-d3ffd95b4323_554x416.jpg)

We can see here that the producer price index varies quite a bit more than the consumer inflation prices, even if it’s generally in the same direction. We can also see how high inflation was in the 1970s, how low it got over the last two decades, and now how it has skyrocketed up since the start of the pandemic.

### Redo steps 2-6 in a single query, without any assignments.

The above (perhaps with, perhaps without “query”) is roughly how I would normally solve this sort of problem — step by step, making changes to my data frame, and seeing the final result.

But there is another school of thought among Pandas users, namely those (led by [Matt Harrison](https://twitter.com/%5F%5Fmharrison%5F%5F/?ref=bambooweekly.com)) who feel that we should use method chaining, rather than assignment. I’m slowly but surely coming to see things his way, and even if you don’t like using this technique quite as much as he does, it’s worth knowing how to perform queries in this style.

A cornerstone of the method-chaining style is the fact that while Python is quite strict about having only one statement per line, you can get around it by opening parentheses. Moreover, you can put a period (.) at the end of one line inside of parentheses, and then continue with another method call on the next. For example:

```
(
  s.
  lower().
  split()[0]
)
```

The above is a toy example, but shows the syntax. The indentation, by the way, is optional; it makes things a bit easier to read, in my opinion.

I asked you to rewrite all of the above queries in a single Pandas expression, using method chaining. Here is how it turned out for me:

\# 7\. Redo steps 2-6 in a single query, without any assignments.

```
( 
    pd.concat(all_dfs.values()).
    dropna(subset=['Country', 'Series Name']).
    set_index(['Country', 'Series Name']).
    drop(['Country Code', 'IMF Country Code', 'Indicator Type', 'Note', 'Unnamed: 59'], axis='columns').
    sort_index(level=df.index.names).
    query("""Country == 'United States' and `Series Name` in ['Headline Consumer Price Inflation', 'Producer Price Inflation']""").
    T.
    plot.line()
)
```

I got the same plot as a result, so this isn’t a matter of better or worse outputs. It is, however, a question of readability and maintainability.

I’ve recently showed some of my classes how to write queries in this way, and they’ve generally said that they like the step-by-step, line-by-line approach. I’ll admit that I’ve gotten used to adding additional lines, and thus method calls, as I develop my query, too.

Notice that this style depends entirely on the fact that most Pandas methods return a new data frame. If methods were to return None, or another such value, then we wouldn’t be able to chain them. This is part of the reason why the core Pandas developers strongly discourage us from using inplace=True in a number of methods, so that we can use method chaining.

### Redo steps 2-6 in a single query, without any assignments, using xs and/or loc instead of query.

Unfortunately, I mis-wrote this question yesterday; I wanted to write that you should use some combination of [xs](https://www.bambooweekly.com/pandas-xs/) and [loc](https://www.bambooweekly.com/pandas-loc/), rather than just loc. Um…. oops! It’s definitely possible to do with “loc” alone, or with a combination of the two. Here, I’m going to show how I used xs alone. I’ve found that “xs” is an amazingly versatile tool to use when working with multi-indexed data frames. It’s worth trying it and working with it.

Let’s assume that we’ll keep the query as it was, up through the sorting of the data frame via its index:

```
(
    pd.concat(all_dfs.values()).
    dropna(subset=['Country', 'Series Name']).
    set_index(['Country', 'Series Name']).
    drop(['Country Code', 'IMF Country Code', 'Indicator Type', 'Note', 'Unnamed: 59'], axis='columns').
    sort_index(level=df.index.names)
)
```

We already saw how we can use “query” to retrieve particular rows and columns. How can we do it using “xs”?

The point of “xs” is to let us grab our data frame via a multi-index, specifying what level we’re interested in. If I were only interested in grabbing rows where the “Country” part of the index was “United States”, I would be able to say

```
df.xs(level='Country', key='United States')
```

If I were interested in finding those rows where the country was United States and the series name was “Producer Price Inflation”, then I could say:

```
df.xs(level=['Country', 'Series Name'], 
      key=('United States', 'Producer Price Inflation'))
```

Notice how the above follows the usual rule in Pandas, that wherever we can use a string for one column/row, we can use a list of strings for multiple columns/rows.

Except that when it comes to the keys, Pandas insists that we use a tuple. It used to allow for lists, and I’m not entirely sure why they switched, but you’ll now get a warning (or worse) if you use a list rather than a tuple.

That’s great, but we actually want *two* values for the key. I can do this by passing a [slice](https://docs.python.org/3/library/functions.html?highlight=slice&ref=bambooweekly.com#slice) object. We normally think of Python slices as what happens when we use colons inside of square brackets, such as mylist\[2:4\]. But that colon syntax is transformed into a call to “slice(2,4)”, a real object in Python. You could, in theory, type mylist\[slice(2,4)\], but you really don’t want to tick off too many people at work, so you should avoid that.

The thing is, the colon syntax for slices only works inside of square brackets. But you can always call “slice” to get a slice object. That’ll come in handy here; instead of asking for one particular value for “Series Name,” we can instead pass a slice object, indicating that we want everything from “Headline Consumer Price Inflation” to “Producer Price Inflation”:

```
(
    pd.concat(all_dfs.values()).
    dropna(subset=['Country', 'Series Name']).
    set_index(['Country', 'Series Name']).
    drop(['Country Code', 'IMF Country Code', 'Indicator Type', 'Note', 'Unnamed: 59'], axis='columns').
    sort_index(level=df.index.names).
    xs(level=['Country', 'Series Name'],
       key=('United States', slice('Headline Consumer Price Inflation',
                                   'Producer Price Inflation'))).
    T.
    plot.line()
)
```

This works just fine, except for the fact that it now plots *three* lines. That’s because we’ve asked for all of the rows in our multi-index from HCPI to PPI. (Pardon me for abbreviating, but enough is enough with these long names.) And it turns out that HCPI is the first row and PPI is the third, meaning that we’re getting everything.

Aha — but we have a slice, which means that we can add a third argument, the step size, in order to skip over the middle value:

```
(
    pd.concat(all_dfs.values()).
    dropna(subset=['Country', 'Series Name']).
    set_index(['Country', 'Series Name']).
    drop(['Country Code', 'IMF Country Code', 'Indicator Type', 'Note', 'Unnamed: 59'], axis='columns').
    sort_index(level=df.index.names).
    xs(level=['Country', 'Series Name'],
       key=('United States', slice('Headline Consumer Price Inflation',
                                   'Producer Price Inflation', 2))).
    T.
    plot.line()
)

```

This isn’t the most beautiful solution, but it works. I’m sure that with a bit more massaging with xs, I could get it to be even better.

The final query, including the plot, thus looks like this:

```
(
    pd.concat(all_dfs.values()).
    dropna(subset=['Country', 'Series Name']).
    set_index(['Country', 'Series Name']).
    drop(['Country Code', 'IMF Country Code', 'Indicator Type', 'Note', 'Unnamed: 59'], axis='columns').
    sort_index(level=df.index.names).
    xs(level=['Country', 'Series Name'],
       key=('United States', slice('Headline Consumer Price Inflation',
                                   'Producer Price Inflation', 2))).
    T.
    plot.line()
)

```

### Produce a line plot of the headline consumer price index for G7 nations (Canada, France, Germany, Italy, Japan, UK, and US), without any assignments, and using loc instead of query.

Finally, I asked you to create a line plot from just the HCPI, but for seven different countries. And I once again asked you to use loc, when I really meant that you should use a combination of loc and xs. (Whoops again!)

Given that the country is the outer part of our multi-index, we can retrieve those rows simply by using “loc” and providing a list of the countries we want:

```
(
    pd.concat(all_dfs.values()).
    dropna(subset=['Country', 'Series Name']).
    set_index(['Country', 'Series Name']).
    drop(['Country Code', 'IMF Country Code', 'Indicator Type', 'Note', 'Unnamed: 59'], axis='columns').
    sort_index(level=df.index.names).
    loc[['Canada', 'France', 'Germany', 'Italy', 'Japan', 'United Kingdom', 'United States']]
)
```

Now that we’ve restricted the rows to those for these seven countries, we can use “xs” to retrieve only those where the series name matches HPCI:

```
(
    pd.concat(all_dfs.values()).
    dropna(subset=['Country', 'Series Name']).
    set_index(['Country', 'Series Name']).
    drop(['Country Code', 'IMF Country Code', 'Indicator Type', 'Note', 'Unnamed: 59'], axis='columns').
    sort_index(level=df.index.names).
    loc[['Canada', 'France', 'Germany', 'Italy', 'Japan', 'United Kingdom', 'United States']].
    xs(level='Series Name',
       key='Headline Consumer Price Inflation')
)
```

Now that we have retrieved these rows, we can transpose the data frame using T, and then create the line plot:

```
(
    pd.concat(all_dfs.values()).
    dropna(subset=['Country', 'Series Name']).
    set_index(['Country', 'Series Name']).
    drop(['Country Code', 'IMF Country Code', 'Indicator Type', 'Note', 'Unnamed: 59'], axis='columns').
    sort_index(level=df.index.names).
    loc[['Canada', 'France', 'Germany', 'Italy', 'Japan', 'United Kingdom', 'United States']].
    xs(level='Series Name',
       key='Headline Consumer Price Inflation').
    T.
    plot.line()
)
```

The result is a bit messy, but it works:

![](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-2f09f02ea6-9477-42c9-b0f0-6ce70796aa9b_543x413.jpg)

While this graph is admittedly a bit crowded, we can see that the inflation levels in these countries marches somewhat in lockstep. They all had sky-high inflation in the 1970s (except for Germany, which had its own central bank and a historic fear of inflation), it sent down to zero after the Great Recession, and now it has gone up rather quickly, thanks in no small part to the pandemic and related effects.

And there we have it! What did you think of the method-chaining way of doing things? I’m curious to hear your opinion. And of course, I’d love to see your solutions, conclusions, and the like — do share!

Here’s my Jupyter notebook for this week: [https://drive.google.com/file/d/1scX1tSNKuent69Z6VVhZzqokMqTUgzcF/view?usp=drive\_link](https://drive.google.com/file/d/1scX1tSNKuent69Z6VVhZzqokMqTUgzcF/view?usp=drive%5Flink&ref=bambooweekly.com)

I’ll be back next Wednesday with another Pandas challenge inspired by the news.

Reuven