Skip to content
Bamboo Weekly
12 min read csv plotting seaborn sorting multi-index pivot-table

Bamboo Weekly #21: Electric cars (solutions)

Get practice working with CSV files, plotting with Seaborn, sorting, multi-indexes, and pivot tables.

Bamboo Weekly #21: Electric cars (solutions)
Share Share Share Share Share Email

This week, we looked at data describing the growth in the electric-car industry. The numbers, as we saw, are growing at breakneck speed, and the expectations are for even bigger growth in the years to come.

BTW, I’m sending this out a bit earlier than usual, so that anyone who wants to read my solution before office hours start will be able to do so.

Without further ado, let’s get to it:

Data

This week’s data came from the IEA (International Energy Agency (https://iea.org), which has some data about cars, oil consumption, and the like. I correctly told you that we were going to look at data about electric-car usage to date, but then I mistakenly told you that we were going to look at oil saved by using electric cars — when I actually decided to use the future projections for electric-car sales, instead. Whoops!

The data comes from this page:

https://www.iea.org/data-and-statistics/data-tools/global-ev-data-explorer

First, there is historical data about electric vehicles, at

https://api.iea.org/evs/?parameter=EV%20sales&mode=Cars&category=Historical&csv=true

Future projections of electric-car sales are here:

https://api.iea.org/evs/?parameter=EV%20sales&mode=Cars&category=Projection-STEPS&csv=true

These files are in CSV format.

(And yes, I flubbed it in yesterday’s message, telling you to download data about how much oil was saved by using electric cars. I had planned to include such data this week, but decided to look at the data you eventually saw. I hope that you saw the comment I attached to the message with a correction.)

Questions

Now that I’ve given you the correct data, let’s see if I can get you the right questions (and solutions). There were a total of seven questions:

This week’s learning goals are: Combining data frames, complex queries, pivot tables, multi-indexes, and plotting with Seaborn.

Create a data frame for the historical data.

The first thing that I’ll do is load up Pandas and Seaborn, since I’ll be using the latter for some visualization later on:

import pandas as pd
import seaborn as sns
from pandas import Series, DataFrame

The third line isn’t really necessary, but I always like to have “Series” and “DataFrame” available in the current namespace, rather than write “pd.” before everything. As usual, I load up Seaborn using the standard alias of “sns”.

With that in place, I can now load up the data:

historical_filename = 'IEA-EV-dataEV salesCarsHistorical.csv'
historical_df = pd.read_csv(historical_filename)

Notice two things: First of all, I used longer and more explicit variable names here than I usually do. (Yes, I should use better variable names, since “filename” and “df” are overused and non-descriptive. Guilty as charged.) But today I’m doing this because I’ll have a second data frame in a bit, and I want to be able to distinguish the two.

Fortunately for us, this CSV file has no irregularities, no “NaN” values, no dates, and no bad values. It’s surprisingly clean, which means that it loads into memory without a hitch.

In 2022 (the most recent year for which we have data), if we look at numbers for the entire world, which sold more, BEVs or PHEVs? How much more?

Now that we’ve loaded our data, we get to our first question: In 2022, in the “World” region, did people buy more battery-operated cars, or plug-in hybrids? There are a few different ways we can get to this solution. Here’s what I came up with:

In other words:

(
    historical_df.
    set_index(['region', 'year', 'powertrain']).
    sort_index().
    loc[('World', 2022), 'value'].
    diff()
)

(And yes, I’m slowly but surely migrating over to the Matt Harrison style of writing my queries. If nothing else, it makes these multi-part queries far more readable. This means putting the entire query inside of parentheses, and putting the dot at the end of each line, making it possible to invoke the method on the next line.)

I start off with historical_df, our data frame. I set its index using set_index, which returns a new data frame — identical to the previous one, but with those three columns set to be a multi-index. Yes, I could have run a query that retrieves based on the values we want, but in this case it seemed easier to do it via an index.

Next, in order to ensure that my results will work, and that Pandas won’t complain about retrieving from an unsorted data frame, I sort the data frame by its index. Because we have a three-part multi-index, Pandas will first sort by region, then by year, then by powertrain. Truth be told, that doesn’t matter much to us, given that we’ll be retrieving (and thus removing) one region and one year — but it’s predictable, which is a good place to be.

Then we retrieve all of the rows with “World” and 2022 in the outer two layers of the multi-index. We’ll get a data frame back, one in which the index contains only “BEV” and “PHEV”. However, we still have a number of columns around. We’re only interested in the “value” column, so we retrieve it via the second argument to “loc”.

Finally, we run diff, which returns NaN for BEV, but 4,400,000 for PHEV. Which means that in 2022, the IEA estimates that people bought 4,400,000 more battery-operated cars than plug-in hybrids. That’s quite a large number, given that all-electric cars barely existed a decade ago.

What five regions (not including the whole world and EU27) sold the most BEVs in 2010?

Looking again at the historical data: Let’s exclude the “World” region and also the “EU27” region, since it’s similar enough to the “Europe” region, for our purposes.

Then, when we’ve done that, we need to retrieve only BEVs from 2010, getting the number of vehicles sold from each region and then sorting them.

This time, I decided to use a more traditional query, rather than “set_index” as we did above. Here’s my query:

(
    historical_df.
    loc[((historical_df['year'] == 2010) & 
         (historical_df['powertrain'] == 'BEV') &
         (~historical_df['region'].isin(['World', 
                'EU27']))), 
         ['region', 'value']].
    set_index('region').
    sort_values(by='value', ascending=False).
    head(5)
)

The biggest part of this query is where I use “loc”. Once again, we’re using the two-argument form of “loc”, with the first argument being the row selector and the second argument being the column selector.

For the row selector, I combined three different queries that resulted in boolean series:

Notice my use of the “isin” method. I love this method for checking whether a value is one of several possibilities. The problem, of course, is that it returns precisely the opposite boolean value from what we want. Fortunately, I can flip those results with ~, the tilde — which Python normally uses for the bitwise “not” operator. But thanks to a bit of operator overloading magic, we can use it with a boolean Pandas series to reverse the values.

The columns that we want from “loc” are “region” and “value”. We can then take the resulting two-column data frame and turn it into a one-column data frame in which “region” is the index.

Finally, we sort our one-column data frame by value using the “sort_values” method. Note that if this were a series, we wouldn’t need to specify “by”, indicating which column should be used in sorting. But of course, this is a data frame, and we thus need to give Pandas a hint — even if it could probably figure it out with a bit of checking.

Also note that sort_values sorts a data frame in ascending order by default. We can reverse the sort order by passing “ascending=False”. Then we can pick off the five regions that bought the most electric cars in 2010 from the top of that result.

And the results?

region
Japan       2400
Europe      2000
USA         1200
China       1100
Portugal     720
Name: value, dtype: int64

Back in 2010, Japan was the country buying the most electric vehicles. And that amounted to under 2,500 per year! All of Europe bought even fewer than that, at 2,000, followed by the US with 1,200, China with 1,100, and then Portugal (?) with 720. I mean, I really like Portugal, but that wasn’t where I would have expected to see them.

What five regions (not including the whole world and EU27) sold the most BEVs in 2022?

This is exactly the same query as we just did, but now we’re looking at 2022. The point, of course, is to compare results from these two queries.

First, here’s the query itself:

(
    historical_df.
    loc[((historical_df['year'] == 2022) & 
         (historical_df['powertrain'] == 'BEV') &
         (~historical_df['region'].isin(['World', 
                'EU27']))), 
         ['region', 'value']].
    set_index('region').
    sort_values(by='value', ascending=False).
    head(5)
)

The differences from 2010 are rather striking:

region
China             4400000
Europe            1600000
USA                800000
Germany            470000
United Kingdom     270000
Name: value, dtype: int64

Wow: China sold more electric vehicles than anyone else, by a long shot — and 4,400,000 cars is so much more than were sold in 2010, there’s almost no comparison. (And while I haven’t been in China in three years, I can tell you that there were indeed many electric vehicles on the roads while I was there, including many of my taxis.) Europeans are also buying a lot of them (1,600,000), albeit many fewer than China. Then comes the US (800,000), Germany (470,000), and the UK (270,000).

Not only are the numeric differences striking; it’s amazing to see how far China has shot up, and that Japan is not in the top five. (Or even in the top 10!)

In 2022, what proportion of regions in this data sold more BEVs than PHEVs?

BEVs, from everything I can tell, are the way that EVs are going, and PHEVs are a transitional technology. Nevertheless, I was curious to know the number of regions in which BEVs outsold PHEVs.

Putting this query together took a bit of thinking and time. In order to perform this calculation, I’m going to want a data frame in which the regions are our index (row labels), the different types of vehicles are our columns, and the values are the number of vehicles of that type, sold for that region.

How can we extract that from our data frame? These factors are currently all mixed together, with one column containing all of the regions, another column containing the drivetrains, and a third column containing the numbers.

This is precisely the kind of problem that a pivot table is designed to solve:

But wait: We’re only interested in data from the year 2022. How can we use only those?

My suggestion is to set the index to be the “year” column. Then we can extract only those rows from the year 2022, followed by a call to “pivot_table”. The result of our call will be a table showing, for each region, the number of BEVs and PHEVs sold in 2022.

That’s almost good enough, but not quite: If I want to know which sold better, I need to subtract the PHEV column from the BEV column. I would normally use the “diff” method, but it compares rows. Here, we want to compare columns, so what can we do?

Here’s what: We pass the axis=”columns” keyword argument to diff, which does the job beautifully:

(
    historical_df.
    set_index('year').
    loc[2022].
    pivot_table(index='region', columns='powertrain', values='value').
    diff(axis='columns')
)

This is great; we now know how many more BEVs were sold in each region than PHEVs. But this isn’t what I wanted to know — rather, I wanted to know the proportion of regions in which BEVs outsold PHEVs.

This means counting those where BEVs sold more, and then counting those where PHEVs sold more, and calculating the proportion of regions in each category.

First, we can find out which regions sold more BEVs by checking where the PHEV column is greater than 0. We’ll get a boolean (True/False) value back.

Then the magic really happens: We use my favorite method, value_counts, to count the number of True values vs. False values. Then we pass the “normalize=True” keyword argument to value_counts, to get the proportion:

((
    historical_df.
    set_index('year').
    loc[2022].
    pivot_table(index='region', columns='powertrain', values='value').
    diff(axis='columns')
)['PHEV'] > 0).value_counts(normalize=True)

I got the following results:

PHEV
False    0.833333
True     0.166667
Name: proportion, dtype: float64

Wherever PHEV is > 0, that means there are more BEVs being sold. Which means that right now, PHEVs are far more common.

Will that remain true in the future? That’s where the projected future data comes into play.

Load the projected data into a data frame. Remove any data from before 2023, and for the entire world.

We’re now going to load up a second data frame, one for the projections file:

projected_filename = IEA-EV-dataEV salesCarsProjection-STEPS.csv'
projected_df = pd.read_csv(projected_filename)

That gives us the entire CSV file as a data frame. For reasons that I don’t quite understand, the “projections” file includes years from the past. I asked you to remove anything from before 2023, which I did as follows:

projected_df = projected_df.loc[projected_df['year'] >= 2023]

I also asked you to remove rows from the “World” region, because they would mess up our analysis. I did that using another comparison with “loc”:

projected_df = projected_df.loc[projected_df['region'] != 'World']

With this in hand, our “projected_df” data frame is now ready to be used in our analysis.

Using Seaborn, create a line plot that combines both historical data and projected data. Put BEVs and PHEVs on separate plots. Include only those regions that appear in the projection file, except for "world" (which you already removed).

Here is where it all comes together:

  1. First, we need to modify historical_df, keeping only those indexes that exist in projected_df. (The projections contain fewer regions, so this is an easy way to reduce our data to a manageable size.)
  2. Then we’ll combine historical_df with projected_df, pretending that there is no difference between them — some are from the past and some are from the future, but we want them both on the same graph.
  3. Then we’ll take the combined data frame, and use Seaborn to create a line plot with the BEV and PHEV sales. Put the two types of powertrains in different line plots, so that we can see them separately from one another.

First, we’ll modify historical_df, keeping only those rows whose index is in projected_df. That’s possible with the following:

historical_df = historical_df.loc[historical_df['region'].isin(projected_df['region'])]

First, I get the values of projected_df[‘region’], passing it as an argument to “isin”, which we run on historical_df[‘region’]. Basically, this will return True when the value of “region” is in the projected data frame’s regions, and False otherwise. We can use this as a filter (using “loc”) on historical_df, ensuring that we keep only those rows from projected locations. We get back a new data frame, which we assign back to historical_df.

That’s great, but it’s not enough: We need to combine the two data frames into a single one. That is, we want a new data frame that contains all of the rows of historical_df, and all of the rows of projected_df. The fact that we have whittled them down to have the same columns is certainly helpful here!

While we talk a lot about “joins” and “merges” in the world of Pandas, sometimes we just want to stack two existing data frames on top of one another. That’s most easily accomplished with pd.concat, which takes a list of data frames and returns a single new frame based on them:

pd.concat([historical_df, projected_df])

What are we going to do with this data frame? Plot it — this time, with Seaborn. I want the plot’s x axis to be the years in the newly combined data frame, and the y axis to be the number of vehicles sold. I want to see a different line for each country/region, and to have two separate graphs, one for BEVs and one for PHEVs.

Turns out that Seaborn makes this quite easy! This is classified in Seaborn-land as a “relplot,” because we’re plotting the relations between two sets of numerical data. We’ll pass:

Here’s the code:

sns.relplot(data=pd.concat([historical_df, projected_df]).sort_index(),
            x='year',
            y='value',
            row='powertrain',
            hue='region',
            kind='line')

And the result? Looks pretty nice to me:

We can see that PHEVs have been fairly flat, but that they’re going to grow, at least a bit, in the coming years. However, we can see that the curve is going to slow down — or in the case of China (blue line), even decline.

Whatever growth we see in the PHEV market is nothing compared with what we can expect to see with BEVs. We can expect to see tons of them sold in the coming decade, with numbers skyrocketing beyond even the impressive numbers we’re seeing now.

Here’s the link to this week’s Jupyter notebook: https://drive.google.com/file/d/1aqBgYcS67gCAAEx_NFLUKhXP4EIKM8Lb/view?usp=sharing

So, any thoughts about this week’s topic or problems?

Thanks in advance for any or all feedback. And of course, I hope to see you at office hours soon.

Reuven

Share Share Share Share Share Email

Read next

Reminder: Office hours today

BW office hours, June 22nd 2023