> ## 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 #16: Consumer oil prices (solutions)
- URL: https://www.bambooweekly.com/bw-16-consumer-oil-prices-solution/
- Published: 2023-05-18T15:01:38.000Z
- Updated: 2026-08-23T09:37:14.000Z
- Description: Get better at: Excel files, missing data, dates and times, string manipulation, and plotting
- Author: Reuven M. Lerner
- Tags: excel, missing-data, datetime, strings, plotting

This week, we’re looking at oil prices — not how much a barrel of oil costs, although that’s generally what you’ll see in the news, but rather how much petroleum-based products costs to consumers. Given how much we’ve heard about heating oil in Europe over the last year, and how much people in the US are talking about gasoline prices as the summer starts, I thought it might be interesting to look at this.

The International Energy Agency (IEA) tracks this sort of information and makes it publicly available. This weeks’ data came from [https://www.iea.org/data-and-statistics/data-product/monthly-oil-price-statistics-2](https://www.iea.org/data-and-statistics/data-product/monthly-oil-price-statistics-2?ref=bambooweekly.com), where they offer monthly updates on the prices for three products: Gasoline, home heating oil, and diesel fuel.

As I noted yesterday, you'll need to register for a free [IEA](https://iea.org/?ref=bambooweekly.com) account in order to download the data. Once you do that, you'll want to download the Excel (xslx) version of the monthly prices excerpt; the file that I retrieved was last updated on May 10th, 2023.

Let’s get to it!

### Load the "raw\_data" tab of the Excel file into Pandas as a data frame.

Before we can do anything else, we’ll need to load up Pandas:

```
import numpy as np
import pandas as pd
```

Strictly speaking, you don’t need to import NumPy. But I want to make sure that we have it available so that I can reference dtypes later on.

With Pandas imported, I’ll load the Excel file into a data frame with “[read\_excel](https://www.bambooweekly.com/pandas-read-excel/)”:

```
filename = 'IEA_Energy_Prices_Monthly_Excerpt_052023.xlsx'
df = pd.read_excel(filename, sheet_name='raw_data')
```

There are two things to notice about how I loaded the Excel file here: First, because the Excel file contains several sheets, I needed to indicate which sheet I wanted to load. You can do this either by specifying the sheet name (which I did here) or by using its numeric index, starting with 0\. If you don’t specify a sheet, then you get the first one (i.e., index 0), which is definitely not what we wanted.

The other thing to notice is that when we load a CSV file, Pandas has to parse the text in each row and column, and then decide which dtype it wants to use. We can give it hints by specifying a “dtype” keyword argument, but it’s up to us to go beyond the default guesses of int64, float64, and object (which is basically a catchall for everything else, but mostly means strings).

By contrast, Excel knows all about different data types. Which means that when we read an Excel file into Pandas, it knows precisely what sort of data we’re loading. This means (as we’ll see in a bit) that the TIME column will automatically be loaded as a “datetime” object; there is no need for a “parse\_dates” parameter like we would use in [read\_csv](https://www.bambooweekly.com/pandas-read-csv/).

### Remove rows in which the VALUE column is \`..\`. Turn the VALUE column into a float.

You would think that the IEA would be nice and smart enough to mark missing data with something mainstream. But no — they put a two-character string, “..”, wherever they didn’t have data.

What’s wrong with that? It means that in Excel, the column that should contain floating-point data actually contains textual data. Which means that when we read it into Pandas, we get a column with a dtype of “object”. If we want to have a numeric column, we’ll need to remove the “..” strings, and then convert the column.

We can remove the rows in which VALUE is “..” with a simple comparison:

```
df['VALUE'] == '..'
```

This returns a boolean series, with True/False values based on whether the value is indeed “..”. We can find all of the rows where that is *not* the case by using the \~ (tilde) to reverse the logic, and then by applying that boolean series as a mask index on [df.loc](https://www.bambooweekly.com/pandas-loc/):

```
df = df.loc[~(df['VALUE'] == '..')]
```

This gives us the subset of “df” which doesn’t have such values for VALUE. We can then set the dtype to be float:

```
df['VALUE'] = df['VALUE'].astype(np.float64)
```

This works, and there isn’t anything wrong with it, per se. But there’s an easier way: What if we just tell Pandas that when it reads the Excel file, it should treat “..” as a synonym for NaN? We can do that with the “na\_values” keyword argument, which can take a list of values it should treat as NaN. Note that these values are in addition to the usual, default values:

```
df = pd.read_excel(filename, sheet_name='raw_data', na_values=['..'])
```

With our data frame looking like this, we can remove all of the rows that contain NaN using [drop\_na](https://www.bambooweekly.com/pandas-dropna/). Or, if we prefer, we can keep them, knowing that they might (under some circumstances) be useful. Either way, because NaN is a float, the result of reading the data frame means that the dtype for that column is a float, without any extra effort on our part:

```
COUNTRY            object
PRODUCT            object
FLOW               object
UNIT               object
TIME       datetime64[ns]
VALUE             float64
dtype: object
```

### When reading data from a CSV file, we need to tell Pandas to interpret a column as a \`datetype\` dtype. Why didn't we have to do that here?

As I mentioned above, CSV is a text format. Which means that if we don’t specify the dtypes of the columns we read, Pandas has to guess. In the case of Excel, Pandas doesn’t have to guess; the file format itself specifies, rather clearly, what type of data each column contains. Which means that we don’t need to tell Pandas to treat one or more columns as datetime values; it gets that from the Excel file.

### Each piece of data is repeated, once in US dollars and once in their national currency. So that we can compare prices, keep only the US dollars.

The “UNIT” column contains two values, “US dollars” and “National currency.” Which makes sense, since every country operates in its own currency, but we want to make apples-to-apples comparisons across countries.

If we were looking at a particular country, then we might want to keep the national currency rows. But we’ll only keep dollars. How can we do that?

We’ll just find those rows where UNIT is equal to “US dollars”. That’ll give us a boolean series back:

```
df['UNIT'] == 'US dollars'
```

We can apply this boolean series to df.loc as a mask index, getting back only those rows that use dollars, and then assigning the result back to df:

```
df = df.loc[df['UNIT'] == 'US dollars']
```

In the end, we have a data frame that is solely in US dollars.

### Remove the '(unit/litre)' string from products.

I have nothing against liters (or even litres), but the fact that every product contains that string is a bit annoying. We know that they’re all prices in unit/liters. How can we remove that ending from all of the strings in the “PRODUCT” column?

Python recently (in version 3.9) added a new method, “[str.removesuffix](https://docs.python.org/3.9/library/stdtypes.html?ref=bambooweekly.com#str.removesuffix)”, which returns a new string that lacks the original suffix. Ideally, we’ll just run removesuffix on each of the elements of PRODUCT, taking off the “(unit/litre)” text.

But how can we run this method on every element of the column? Generally speaking, we don’t want to use “for” loops or other iterations in Pandas.

Fortunately, we can use the “str” accessor, which gives us the ability to run any string method we want on each element of a column. The result is a new string column. Here’s how I can run it:

```
df['PRODUCT'].str.removesuffix('(unit/litre)')
```

Before we assign this string series back to df\[‘PRODUCT’\], let’s also remove any whitespace that might be at the front or back of the string:

```
df['PRODUCT'].str.removesuffix('(unit/litre)').str.strip()
```

Notice how I can chain string calls. I must use “str” each time, but I often call one string method with the “str” accessor, and then a second string method on its result. We can then assign the total result back to PRODUCT:

```
df['PRODUCT'] = df['PRODUCT'].str.removesuffix('(unit/litre)').str.strip()
```

### On average, is diesel fuel cheaper per liter than gasoline?

With our data in place, we can now start to ask questions about it. I’ve often heard that diesel fuel is cheaper than gasoline. I don’t know where I heard it, or if it’s true, but I decided that this is a good opportunity to answer that question.

Given our data, how can I find out whether diesel is cheaper than gasoline? I could find all of the rows with the diesel product, and get the mean of their values. Then I could find all of the rows with the gasoline product, and get the mean of their values. Then I could compare them.

But why work so hard? Instead, I could run a “[groupby](https://www.bambooweekly.com/pandas-groupby/)” operation:

- Group on the products
- Calculate on the VALUE column
- Run the “mean” method

Here’s how I can run such a query:

```
df.groupby('PRODUCT')['VALUE'].mean()
```

I get the following answer to this query:

```
PRODUCT
Diesel                  1.388030
Domestic heating oil    0.992507
Gasoline                1.468518
Name: VALUE, dtype: float64
```

Notice that I also got the mean price of domestic heating oil. In theory, I could have excluded those rows from my query, but the data set is relatively small, and I thought that it didn’t hurt to have it, too.

We can see from here that on average, diesel is indeed a bit cheaper than gasoline. However, is this a truly useful analysis? I’m far from convinced; taking all of the prices, from all countries, for each of these products is probably far less useful than looking at the difference between their prices, and looking at that difference over time. So I did get an answer to my question, but I’m sure that there are better and smarter ways to answer it.

### Which countries have, historically, the average cheapest gasoline?

It never ceases to amaze me to hear Americans complain about how expensive gasoline is, when it’s so much cheaper than everywhere else. At least, that’s what I’ve always thought; given this data set, I figured it might be useful to check if that’s true. So I asked you to find the average gasoline price per country. Let’s find out which countries truly have cheap gasoline.

For starters, we’ll need to get only those rows for the gasoline product:

```
df.loc[df['PRODUCT'] == 'Gasoline']
```

Here, I’m getting a boolean series based on where PRODUCT is gasoline, and then applying it via “.loc” to df. The result is the subset of df rows that have to do with gasoline.

Then, because I want to know how much gasoline costs, on average, in each country. That’ll require a “groupby”:

- Group on each country
- Calculate on VALUE
- Run the “mean” method

The query looks like this:

```
df.loc[df['PRODUCT'] == 'Gasoline'].groupby('COUNTRY')['VALUE'].mean()
```

The result is a series with an index of country names, and with values showing the mean gasoline price, over the entire history of this data set. If we want to find the cheapest (or most expensive) gasoline, though, we’ll need to sort the values:

```python
df.loc[df['PRODUCT'] == 'Gasoline'].groupby('COUNTRY')['VALUE'].mean().sort_values()

```

The result? The US is indeed the cheapest, with Canada and Brazil in second and third place. But let’s just point out that the US has an average of 0.7167 dollars, while Canada has an average of 0.9573 dollars — about one third more expensive!

### Which countries have, in the most recent monthly measurement, the cheapest gasoline?

The problem with measuring the gasoline prices over the entire data set is that… well, that it’s historical. Maybe things have changed in the last few years. So let’s only look at the most recent data, and see what the prices are like in various countries.

How can we get the most recent data? The easiest way, in my opinion, is to find the most recent time — aka, calculate max on the TIME column:

```
df['PRODUCT'] == 'Gasoline', 'TIME'].max()
```

The above gives us the maximum value of TIME for rows about gasoline. We can say that we only want rows where TIME is equal to that:

```
df['TIME'] == df.loc[df['PRODUCT'] == 'Gasoline', 'TIME'].max()
```

And then we can combine that (using the & operator) to get rows about gasoline from the most recent data:

```
df.loc[(df['PRODUCT'] == 'Gasoline') &
       (df['TIME'] == df.loc[df['PRODUCT'] == 'Gasoline', 'TIME'].max())]
```

But we don’t need all of the columns; we only need two, COUNTRY and VALUE. So I can pass, as a second argument to “loc”, those two columns:

```
df.loc[(df['PRODUCT'] == 'Gasoline') &
       (df['TIME'] == df.loc[df['PRODUCT'] == 'Gasoline', 'TIME'].max()),
      ['COUNTRY', 'VALUE']]
```

I now have the COUNTRY and VALUE columns for gasoline, from the most recent data received. Which means that if we sort by VALUE, we’ll get the countries listed from cheapest to most expensive:

```
df.loc[(df['PRODUCT'] == 'Gasoline') &
       (df['TIME'] == df.loc[df['PRODUCT'] == 'Gasoline', 'TIME'].max()),
      ['COUNTRY', 'VALUE']].sort_values('VALUE')
```

And the winners? The United States is still cheapest, followed by Brazil and Canada. But the US is only 15% more expensive than #2 Brazil nowadays. (It is about 26 percent more than Canada, so some things have stayed relatively stable!)

### We often hear that heating oil is, on average, most expensive during winter months. Does that seem to be true, historically?

First, let’s find all of the rows for domestic heating oil:

```
df.loc[df['PRODUCT'] == 'Domestic heating oil']
```

I want to find out how much, on average, the oil cost per month. This sounds like a “groupby” operation… but what do I want to group by?

In theory, I’d like to retrieve the month from the TIME column, and group on that. Luckily, we can do that by using the “dt” accessor, which lets us retrieve values from a datetime column. If I group on df\[‘TIME’\].dt.month, and then get the mean price for each month, I can produce a simple comparison:

```
df.loc[df['PRODUCT'] == 'Domestic heating oil'].groupby(df['TIME'].dt.month)['VALUE'].mean()
```

Finally, let’s sort these values from least expensive to most expensive:

```
df.loc[df['PRODUCT'] == 'Domestic heating oil'].groupby(df['TIME'].dt.month)['VALUE'].mean().sort_values()
```

Here’s what I got:

```
TIME
8     0.978667
5     0.981037
12    0.981660
9     0.983167
2     0.987419
1     0.987778
4     0.993018
7     0.995875
11    1.000445
6     1.004917
10    1.006543
3     1.008556
Name: VALUE, dtype: float64
```

It’s true that three of the most expensive months, October, November, and March are cold, even if they’re not the depths of winter. But June and July are also there, which means that this doesn’t seem to be the case.

### I've heard that US gasoline prices are the most expensive during summer months. Is that historically true?

Oops! I meant *summer* months, when people are driving a lot on summer vacation. The query is the same, even if the analysis is slightly different.

Here, I’m interested in gasoline prices in the United States. Which means that I’m interested in a subset of df:

```
df.loc[(df['PRODUCT'] == 'Gasoline') & (df['COUNTRY'] == 'United States')]
```

Given this subset, I’d like to find out the mean price per month. Once again, we can group by the month, pulling it out of each TIME value using the “dt” accessor. We’ll then calculate the mean:

```
df.loc[(df['PRODUCT'] == 'Gasoline') & (df['COUNTRY'] == 'United States')].groupby(df['TIME'].dt.month)['VALUE'].mean()
```

Finally, we’ll sort the values from lowest to highest:

```
df.loc[(df['PRODUCT'] == 'Gasoline') & (df['COUNTRY'] == 'United States')].groupby(df['TIME'].dt.month)['VALUE'].mean().sort_values()
```

The result? The five most expensive months for gasoline were indeed warm, on average: August, April, May, July, and June. So yes, it would seem that when Americans are driving around on vacation, gas prices are (on average) more expensive.

### Produce a line plot showing gasoline prices. The x axis should show dates, and there should be one line for each country.

In order to create this kind of line plot in Pandas, we need to have a table in which the rows are the dates, the columns are the countries, and the values are .. well, they’re the price for that country on that date.

How can we rejigger our data frame to give us such a table? With a pivot table, of course:

- The columns of the pivot table will be the unique values from COUNTRY
- The rows of the pivot table will be the unique dates from TIME
- The values will be from VALUE

We can create the pivot table as follows:

```
df.loc[df['PRODUCT'] == 'Gasoline'].pivot_table(columns='COUNTRY', index='TIME', values='VALUE')
```

With that in place, we can create a simple line plot:

```
df.loc[df['PRODUCT'] == 'Gasoline'].pivot_table(columns='COUNTRY', index='TIME', values='VALUE').plot.line()
```

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-2f78c76ae4-c723-4c45-a2b5-b4cf370a55b7_640x480.jpg)

Removing the legend isn’t good for understanding what each country was spending, but allows us to see crazy dip that occurred in early 2020\. Gee, I wonder what happened then?

![](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-2fb2e1f5ed-6508-410e-a1f4-41f517f3a7ab_640x480.jpg)

What do you think? I’d love to hear from you in the comments!

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

I’ll be back next week with some more questions about Pandas and the news.

Reuven