This week’s topic: Oil prices
This week’s data came from the OECD, about 30 countries that pool data for (in theory) a better understanding of trade, economics, education, and governance. Here in Israel, the OECD is constantly making suggestions regarding how government policies; our governments sometimes even listen to those suggestions.
The OECD is a treasure trove of data, and among other things, they keep track of energy-related expenses — including how much each country spent on oil. I asked you to download a CSV file from here:
https://data.oecd.org/energy/crude-oil-import-prices.htm
I noted that you should ask for the “full indicator data” for all years (1980-2021). Moreover, I suggested that you download the file, rather than retrieve the data from a URL. That was mostly to avoid including network latency variations in our comparison between NumPy-based Pandas and PyArrow-based Pandas.
Our questions for this week are:
- Load the data into a data frame. We only want to load five columns: LOCATION, FREQUENCY, and TIME, Value, and Flag Codes. (Note the odd capitalization.)
- Check the memory usage of the data frame. (You don’t need to time this.)
- Keep only those rows with a monthly frequency (i.e., M).
- Create two new integer columns, YEAR and MONTH, based on the existing “TIME” column.
- What has been the per-country import price, taken over all measurements? Which countries have paid above the mean?
- Grouping by YEAR then MONTH, find the mean oil-import price across all countries. When was the mean price the highest? The lowest?
Let’s get to it! I’ll start by doing everything as per usual, with a traditional Pandas data frame created via read_csv. Once we’ve collected some benchmark information, we’ll then do everything again via PyArrow. And then we’ll be able to make some comparisons, and understand the trade-offs.
Load the data into a data frame. We only want to load five columns: LOCATION, FREQUENCY, and TIME, Value, and Flag Codes. (Note the odd capitalization.)
First, I’ll need to load the basics:
import pandas as pd
from pandas import Series, DataFrameI’ve downloaded the file into the current directory, thus allowing me to import it this way:
filename = 'DP_LIVE_04042023115115682.csv'Your file will also presumably start with “DP_LIVE” and then (from what I can tell) the day number, month number, year, and then — perhaps? — the number of seconds in the day, or something along those lines. Regardless, this is a pretty standard CSV file, and other than asking you to import only a subset of the columns, nothing really exciting is going on here:
df = pd.read_csv(filename,
usecols=['LOCATION', 'FREQUENCY',
'TIME', 'Value', 'Flag Codes'])Sure enough, we get a data frame.
But wait: I wanted you to check how long it took to perform this action. There are several ways to do this, but if I’m in Jupyter (or IPython, its textual cousin), then I like to use the %timeit and %%timeit magic commands.
Magic commands all start with %, because they aren’t allowed in Python identifiers. This means that Jupyter can notice and intercept the magic command before it ever gets to Python. These commands can be rather simple or complex; the point is that they give instructions to Jupyter.
Magic commands with a single % operate on a single line. Those with two %% at the start run on an entire cell. (They must also be at the start of the cell; don’t try to put Python comments before they begin, or you’ll get weird error messages.) If you want to run one line of code, then just put %timeit before, and the timing will be checked.
Behind the scenes, these magic commands are actually running the “timeit” module’s “timeit” method, from the Python standard library. It’ll run the code a number of times, giving you the average of the times that it ran your code, in case there was a great deal of variation.
In order to see how long it took to load our code, I put the following in a Jupyter cell:
%%timeit
df = pd.read_csv(filename,
usecols=['LOCATION', 'FREQUENCY',
'TIME', 'Value', 'Flag Codes'])Here’s the result that I got:
9.82 ms ± 698 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)The mean running time was 9.82 ms, with a standard deviation of ± 698 µs. (There are 1,000 µs, aka microseconds, in one ms, aka milisecond.) That sounds pretty fast, especially given that there are only about 21,000 rows in this data frame.
Check the memory usage of the data frame.
How much memory are we using? My favorite way to check is by running df.info(), a method that tells us about the data frame object. (This is different from df.describe(), which returns descriptive statistics on our data frame’s values.)
df.info() returns quite a bit of information about our data frame: Its index type, its column names and dtypes, a summary of how many columns we have of each dtype, and then a description of how much memory we’re using.
The thing is, if you invoke df.info(), the memory description will be a bit weird:
memory usage: 822.6+ KBWhat the heck is going on there? Can’t the computer keep track of how much memory is in the data frame? Why is it telling us that there are 822.6+ KB?
The problem is that we have several columns of type “object,” which often means that they’re Python strings. Pandas doesn’t store strings in NumPy, because that would be too limiting. As a result, it stores references to Python strings, which exist in Python memory. For Pandas to do and count the memory usage on each of these Python strings might take a long time. So instead, it tells us how much memory the string references are using, and then says, “It might be more, too!” by way of the +.
If we want to get an exact reading, we need to pass memory_usage='deep' to df.info(). That’ll take more time, but it’ll also give us the true memory usage:
memory usage: 4.7 MBWow, that’s a lot bigger! I mean, it was previously reporting about 20% of the actual memory being used.
So we now know that our data frame is using 4.7 MB of memory, thanks to the combination of Pandas references and Python objects.
Keep only those rows with a monthly frequency (i.e., M).
Our data set contains several types of pricing information — annual, quarterly, and monthly — and indicates which of these is in each row with a single letter. We’re going to keep only the monthly prices, which means only keeping those rows with the letter M.
Here’s how I’ll do it:
df = df.loc[df['FREQUENCY'] == 'M']I’m first asking to find where the FREQUENCY column contains the single letter ‘M’. That returns a boolean series. I can then apply that boolean series to df.loc as a mask index. I’ll get a data frame back, containing all of the rows of df with the value M for FREQUENCY.
Remember that df.loc returns a new data frame (or portion of one), and doesn’t modify the original one. I thus assign this new, M-only data frame back to df.
How long did this take? I timed it, of course:
%%timeit
df.loc[df['FREQUENCY'] == 'M']Wait a second — why didn’t I assign to df here?
%%timeit wraps our code into a function of some sort. Which means that any variable to which we assign is treated as a local variable. If you assign to a local variable before you’ve given it a value, then you’ll get an UnboundLocalError. I timed things without assignment, in order to avoid such trouble.
Moreover, even if you can get the assignment to work, you’ll find that it doesn’t affect our global df variable. In my Jupyter notebook, I thus did each of the actions here twice, once within a %%timeit block and once on its own, with assignment.
Oh, and the timing? When I timed the removal of these lines, it took 1.65 ms.
Create two new integer columns, YEAR and MONTH, based on the existing “TIME” column.
Now that I only have rows with monthly reports (frequency of “M”), I know that every value in TIME has a four-digit year, followed by a minus sign, followed by a two-digit month. I want to turn these into two different columns, one for the year and one for the month. How can I do that?
In traditional Pandas data frames, strings are stored as Python objects. Such a column has a dtype of “object”. We can invoke string methods on each of the elements there via the “str” accessor. This gives us access to all of the regular Python string methods, plus a bunch of methods that implement operators, and a few other methods thrown in for good reason.
My initial thought was to use “str.split”, which operators much like the regular string “split” method in Python. But that’ll give me a list of strings back, which then requires I invoke another method to get the
Plus — and here’s a spoiler! — the PyArrow API doesn’t support a whole bunch of string methods. That’s right; some of them do work, but a bunch do not. One of the methods that doesn’t work is str.split. In order for my comparison to be a fair one, I wanted to use the same methods with traditional Pandas and my PyArrow-powered data.
I thus decided to use a slice. In Python, we normally create slices with square brackets and a colon, as in “s[a:b]”. In Pandas, we instead use the str.slice method, passing values as “s.str.slice(a, b)”. Since I know the format of each of the strings in this column, I can retrieve the year and month from fixed, known indexes:
df['YEAR'] = df['TIME'].str.slice(0, 4).astype('int64')
df['MONTH'] = df['TIME'].str.slice(5, 7).astype('int64')The result of invoking str.slice on each of these series is a new series, also containing strings. Which means that if I want to be able to work with them as integers, I’ll need to convert them. In Pandas, we convert a series to a new di
Notice that after grabbing the year and the month, they’re still strings. For that reason, I then run “astype” on each of these new columns, passing the string ‘int64’. In this way, I ensure that they’ll both be 64-bit integer columns. I could have used smaller integers, but decided that it wouldn’t make that much difference, given the relatively small size of the data set.
How long did this take? About 7.12 ms, according to timeit.
What has been the per-country import price, taken over all measurements? Which countries have paid above the mean?
In order to calculate the mean price for each location, I need to use “groupby”. I know that I want to get one value per country (i.e., LOCATION), that I want to calculate it on the “Value” column, and that I want to calculate the mean. This means doing the following:
df.groupby('LOCATION')['Value'].mean()Sure enough, I get the mean import price of oil for each country in the data set. So far, so good. Using timeit, I find that I can calculate this in 1.41 ms.
Then I asked how many countries have prices higher than this mean value. I decided to assign the means to a new data frame, and then find which of the elements of that data frame exceeded the mean:
country_means = df.groupby('LOCATION')['Value'].mean()
country_means.loc[country_means > country_means.mean()]First: I was a bit surprised to find which countries have above-average oil prices! But beyond that, it didn’t take long to calculate, about 1.41 ms.
Grouping by YEAR then MONTH, find the mean oil-import price across all countries. When was the mean price the highest? The lowest?
Finally, I asked you to group by year and month, and then find the mean price across all countries. This involved a two-column grouping, using the columns that we created.
Grouping by two columns follows the general rule in Pandas that wherever you can pass a single column name (as a string), you can also pass a list of column names (as a list of strings). We’ll once again be grouping on the “Value” column, and calculating the mean:
df.groupby(['YEAR', 'MONTH'])['Value'].mean()Remember that when we run a “groupby”, the index of the resulting series or data frame contains values from the grouped-on columns. Which means that the index will contain a year and month.
Thus, if we want to get the year and month of the highest and lowest prices, we can run “idxmin” and “idxmax”, two methods that return the index of the lowest and highest values, respectively.
We could run these separately — or we can use the “agg” method, which lets us pass a list of other aggregation methods we want to run. And that’s exactly what I do here:
df.groupby(['YEAR', 'MONTH'])['Value'].mean().agg(['idxmin', 'idxmax'])I get the following results:
idxmin (1998, 12)
idxmax (2008, 7)
Name: Value, dtype: objectAnd it took about 1.5 ms for Pandas to make this calculation.
So, we got a bunch of results, and we even know how long it took to calculate them. Fantastic!
Using PyArrow
I’m now going to repeat all of my calculations and timings, but using PyArrow instead of the default Pandas backend. How will that look, code-wise? And how will it change the timing?
Let’s start by remembering that there are actually two places where PyArrow can help us here: One is in loading the data into Pandas, using the PyArrow CSV-reading engine. The second is in using PyArrow, and not NumPy, as the basis for our values. Given how long NumPy has been a part of the Pandas world, it still seems a bit weird to talk about something other than NumPy being used to store data… but it’s going to be increasingly common.
I can tell Pandas to use the PyArrow loading engine by passing “engine='pyarrow'“ to pd.read_csv. And I can tell it to use PyArrow for our back-end values with the new keyword argument, “dtype_backend”. Here’s how that code looks:
df = pd.read_csv(filename,
usecols=['LOCATION', 'FREQUENCY', 'TIME', 'Value', 'Flag Codes'],
engine='pyarrow', dtype_backend='pyarrow')After loading the data this way, we can see the difference for ourselves by checking “df.dtypes”:
LOCATION string[pyarrow]
FREQUENCY string[pyarrow]
TIME string[pyarrow]
Value double[pyarrow]
Flag Codes string[pyarrow]Notice that the dtypes are completely different than we’ve seen in the NumPy world over the years: They all have “[pyarrow]” suffixes, to make that clear. But instead of “object,” we have a real string type. Instead of “float,” we have “double.” There are other types as well, but these are pretty indicative of how things look and work if you’re using PyArrow.
How quickly did things load using PyArrow? It took my computer 2.65 ms. Which is four times faster than the default CSV-loading engine in Pandas. That’s pretty fast! Others have reported similar speedups.
How about the memory usage? I can once again invoke df.info():
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 21054 entries, 0 to 21053
Data columns (total 5 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 LOCATION 21054 non-null string[pyarrow]
1 FREQUENCY 21054 non-null string[pyarrow]
2 TIME 21054 non-null string[pyarrow]
3 Value 11275 non-null double[pyarrow]
4 Flag Codes 21054 non-null string[pyarrow]
dtypes: double[pyarrow](1), string[pyarrow](4)
memory usage: 728.3 KBNotice that the output from df.info() always shows us the dtypes, which can be quite useful. Also notice that the memory is 728 KB, about six times less than the default Pandas way of storing things, using Python strings. Moreover, because all of the data is in PyArrow, we don’t need to do anything special to get the true memory usage. We get it right away.
So in terms of loading speed and memory usage, there’s no comparison. PyArrow is, far and away, the winner.
How about doing actual calculations and operations?
For example, I asked you to keep only those rows that had “M” as the value for FREQUENCY. That involves matching rows, creating a boolean index, filtering, and assigning. PyArrow did that in 1.05 ms, about 50% less time than the default NumPy backend.
Next, we created two new columns, MONTH and YEAR, using “str.slice”, then using “astype” to convert the values to integers, and finally assigning to new columns:
df['YEAR'] = df['TIME'].str.slice(0, 4).astype('int64[pyarrow]')
df['MONTH'] = df['TIME'].str.slice(5, 7).astype('int64[pyarrow]')Notice that I had to specify the PyArrow types here. Otherwise, we would have gotten basic NumPy types, and that would have ruined the experiment, as well as any chance of benefitting from PyArrow.
And indeed, using PyArrow seems to have worked rather well! PyArrow did this in 972 µs, for about a 7x speedup. I’m not sure how much of that had to do with the slicing vs. the call to “astype” vs. the assigning to new columns, but the overall speedup was rather impressive.
But here, I have to point out that there is an issue: PyArrow’s string types don’t support all of the “str” accessor methods that Pandas provides. If you try to run “str.split”, for example, you’ll get a “NotImplementedError” exception, with the message, “str.split not supported with pd.ArrowDtype(pa.string()).” I assume that over time, all of the string methods will indeed be re-implemented for PyArrow. But until then, this means that you might need to modify your code.
What about our grouping and calculations?
I asked to find out, per location, the mean oil price in the data set. This is where we start to see PyArrow’s advantages go away: While it only took 980 µs with the traditional NumPy dtypes, it took nearly 4x longer, 3.08 ms, to perform the same calculation under PyArrow. And this is a small data set; I have to imagine that the differences would be even greater with a larger one.
When I calculated which countries paid more than the mean price over time, I got a similar difference — whereas NumPy took 1.41 ms, my PyArrow-based data frame took 4.79 ms.
Based on what we’re seeing here, using PyArrow with Pandas seems to have some performance problems with grouping. I have to assume that this is well known and is being handled by the developers, but it means that PyArrow isn’t a panacea for Pandas performance issues.
We see similar issues with our final calculation, grouping by YEAR and MONTH, and then finding the dates with the lowest and highest prices. PyArrow took 4.48 ms to calculate this, which was about 4x as long as the NumPy-based data frame took.
The bottom line? PyArrow is amazing, no doubt about it, when it comes to loading CSV files, saving memory, and simple operations. But as is so often the case, there’s a trade-off here, and if you’re going to be doing lots of grouping, then it might cause some problems. Of course, if PyArrow means that your data can fit into memory, and if it wouldn’t using NumPy, then the performance penalty is more than offset!
Regardless, I see PyArrow, and its integration into Pandas, as an amazing step forward. Having a totally separate backend work with Pandas is no small feat; kudos to everyone involved with this effort. I have no doubt that we’ll see increasing improvements in its integration and performance over time, too.
What do you think? Did you get similar results? Leave your thoughts in the comment section here!
Here’s my Jupyter notebook: https://drive.google.com/file/d/1_XjCMiSkNGiFK2JKdj4K6z9YLCCA81t0/view?usp=drive_link
Meanwhile, I’ll be back next Wednesday with another question.
Reuven