Bamboo Weekly #5: Ukrainian exports (solutions)

This week’s topic: Ukrainian exports

This week, we looked at various aspects of Ukraine’s agricultural exports in the wake of the Russian invasion, as well as the Black Sea Grain Initiative that allows products to be sent to other countries.

The URL to download the data in Excel format is:

https://docs.google.com/spreadsheets/d/e/2PACX-1vRisnQjodySbp6-XXPGhdsVMp2stg_gyuxw42pP41tuxeic63IARau6bV1TgjLiw_ciAWsTO5LarPqT/pub?output=xlsx

Here is what I asked you to do:

1. Read the Excel data into a data frame. Again, the “Data” sheet is what interests us.

2. Create a pivot table, showing how many tons of each commodity (rows) have left each port (columns).

3. Create a pivot table, showing how many tons of each commodity (columns) were going to each destination country (rows).

4. The worry was that lower-income countries would not have enough food to eat, because of the war. According to the data's "income group," what proportion of the grain are low-income countries receiving?

5. Have we seen growth in the total tonnage shipped each month to developing countries?

6. Finally, what 10 flags are most commonly used on the ships coming from Ukraine? Do any country's names appear more than once? If so, why, and how can you fix it?

Discussion

The first thing we need to do is read the data into a data frame. Fortunately, this is fairly straightforward; we can either download the file and load it from our local filesystem, or grab it directly via a URL. Because the data dictionary was in the Excel file’s first sheet, I decided to download it onto my computer, and load it into Pandas from there.

On my computer, the loading code looked like this:

df = pd.read_excel(filename, sheet_name='Data')

I used read_excel to bring the data into Pandas. And because the Excel file contained multiple sheets, I named the sheet that was of interest to me, giving me a single data frame.

However, if I load the data frame this way, there will be a problem down the road. Specifically, the “Departure” column is contains a date and time. Treating that column as a datetime not only saves memory, but also allows us to perform a number of time-based queries on the data. In theory, I could load the data as is, and then transform it into a datetime column with pd.to_datetime. However, I find it far more convenient to tell Pandas to set the dtype when it loads the file and creates the data frame. I do that by passing the column name as a value to the “parse_dates” keyword argument:

df = pd.read_excel(filename, sheet_name='Data', parse_dates=['Departure'])

With our data frame fully loaded into memory, we can start to attack the questions that I raised.

1. Create a pivot table, showing how many tons of each commodity (rows) have left each port (columns).

The Black Sea Grain Initiative ensures that Ukraine’s agricultural products can be exported via three ports on the Black Sea. The data set that we are looking at tracks each of the ships carrying products from Ukraine, indicating which port they left from, where they were going to, and what they were carrying.

We could find out how much grain was shipped from each port with a simple “groupby”. We could similarly find out how much of each product was shipped with a separate “groupby”. But we can also perform a two-dimensional “groupby”, known as a “pivot table,” which will allow us to see how much of each product was exported from each of the three ports.

A pivot table requires three pieces of data:

1. The name of a categorical column, whose values will be used as the index of the resulting table.
2. The name of a second categorical column, whose values will be used as the columns of the resulting table, and
3. The name of a numeric column, whose values will be aggregated for each intersection of row and column.

We can create a pivot table with the “pivot_table” method for data frames. We’ll need to pass at least three keyword arguments:

1. “index”, the name of the categorical column (“Commodity”) whose unique values will form the rows of the pivot table,
2. “columns”, the name of the categorical column (“Departure port”) whose unique values will form the columns of the pivot table, and
3. “values”, the name of the numeric column (“Tonnage”) whose values will be used for the intersection of every row and column.

We could thus create our pivot table as follows:

df.pivot_table(index='Commodity', columns='Departure port', values='Tonnage')

By default, pivot tables calculate the mean of all values for that row-column intersection. So the result that we’ll get will show how much tonnage, on average, of each commodity was sent from each port. That might be useful, but it’s not what we’re asking for.

We’ll thus need to add another keyword argument, “aggfunc”, naming the aggregation function we want to use. In this case, that’ll be “sum”:

df.pivot_table(index='Commodity', columns='Departure port', values='Tonnage', aggfunc='sum')

Sure enough, we get a table with three columns (the ports) and 18 rows (one for each commodity). Wherever we have a NaN value, we know that none of that commodity was shipped from that port.

By the way, it’s confusing that Pandas has both “pivot” and “pivot_table” methods. The “pivot” method doesn’t let you specify the aggregation function you want to use. So I tend to use “pivot_table” in my work.

2. Create a pivot table, showing how many tons of each commodity (columns) were going to each destination country (rows).

Now we’re going to create a second pivot table, showing what commodities were sent to which destination countries. Once again, we’ll need to pass four arguments:

1. “index”, the name of the column (“Country”) whose unique values will be used for the rows of the pivot table,
2. “columns”, the name of the column (“Commodity”) whose unique values will be used for the columns of the pivot table,
3. “values”, the name of the numeric column (“Tonnage”) whose unique values will be aggregated, and
4. “aggfunc”, the name of the function (“sum”) we want to apply to all values at each row-column intersection.

The code we’ll need to write is almost identical to what we used above, except that we’ll be choosing different columns:

df.pivot_table(columns='Commodity', index='Country', values='Tonnage', aggfunc='sum')

There are lots of NaNs there, indicating which commodities aren’t imported by various countries. But wow, Ukraine really supplies quite a bit of wheat and barley to the rest of the world! (Just to Israel, where I live, they exported 40k tons of barley and 11k tons of wheat, and 14k tons of sunflower meal. What the heck is sunflower meal, anyway? Ooh, it’s fed to livestock. But I digress.)

Notice, by the way, that just before the column for “Wheat,” we have a column for “What,” which only has a value for Italy. My guess is that this reflects a typo, which could be cleaned up if we were doing more serious analysis of this topic.

3. The worry was that lower-income countries would not have enough food to eat, because of the war. According to the data's "income group," what proportion of the grain are low-income countries receiving?

First, we need to find out how many tons are being shipped to each of the income groups. This is a classic situation for “groupby”, since “Income group” is a categorical column, and we want to run the “sum” aggregation function for each of them:

df.groupby('Income group')['Tonnage'].sum()

I got the following results:

Income group
high-income            10475110.40
low-income               604786.00
lower-middle income     3716334.91
upper-middle-income     7675765.69
Name: Tonnage, dtype: float64

We can already see that low-income countries have received the least amount of Ukraine’s exports. That really surprised me, given everything I had heard about having to supply low-income countries with food from Ukraine.

But my surprise aside, I wanted to know what the raw numbers were. Rather, I wanted to know what proportion of the exports were going to each income level.

Fortunately, the result of our groupby was a series. And a Pandas series is always able to run math operations with a scalar. In such a case, the scalar value is applied, with the operation, to every element of the series. We can thus total the “Tonnage” column, giving us a number, and divide the “groupby” result by it:

df.groupby('Income group')['Tonnage'].sum() / df['Tonnage'].sum()

Now we can see the proportions:

Income group
high-income            0.466141
low-income             0.026913
lower-middle income    0.165376
upper-middle-income    0.341570
Name: Tonnage, dtype: float64

But let’s go a bit further than this, sorting the results by value. To do that, I’ll need to put parentheses around the entire expression. Then I can run sort_values on the resulting series:

(df.groupby('Income group')['Tonnage'].sum() / df['Tonnage'].sum()).sort_values()

The result looks like this:

Income group
low-income             0.026913
lower-middle income    0.165376
upper-middle-income    0.341570
high-income            0.466141
Name: Tonnage, dtype: float64

So low-income countries are getting 2 percent of Ukraine’s exports? And high-income countries are getting just under half of it, at 46 percent? Maybe it’s just me, but that doesn’t reflect the worry that I read about back when the invasion occurred.

4. Have we seen growth in the total tonnage shipped each month to developing countries?

I was curious to know if, since the Black Sea Grain Initiative began, we had seen a monthly increase in the tonnage of products exported each month. Let’s take a look, and see if that’s the case.

The easiest way to do this would be to:

1. Get the total tonnage per month
2. Compare each month’s tonnage with the previous month’s amount

Calculating “tonnage per month” sounds like another job for “groupby”. But what would we be grouping on? We normally need a categorical column, and run the aggregation function once per value in that column.

Here, we want to use months, which aren’t in their own column. But that’s OK; because the “Departure” column is set to be a “datetime”, we can use the “dt” accessor to retrieve the month.

Rather than giving a string to “groupby”, we’ll thus tell it to use the month that it retrieves from the “Departure” column:

df.groupby(df['Departure'].dt.month)

As of this writing, the program has been running for only six months. That means there won’t be any overlap across the data. But what if the program lasts for another year or two? (I certainly hope that the war will conclude before then.) Our analysis will then mix together values from the same month in different years.

I suggest that we thus group by two different columns — both the year, and the month. That’ll ensure that our analysis can work as far into the future as is needed:

df.groupby([df['Departure'].dt.year, df['Departure'].dt.month])

With that in place, we can now complete our “groupby”, getting the total amount of grain exported in each month since the program began:

df.groupby([df['Departure'].dt.year, df['Departure'].dt.month])['Tonnage'].sum()

I can get the difference between each month and the previous month with the “diff” method. But I think that it would make more sense to get the percentage change in each month, using pct_change:

df.groupby([df['Departure'].dt.year, df['Departure'].dt.month])['Tonnage'].sum().pct_change()

The result:

Departure  Departure
2022       7                  NaN
           8            62.242847
           9             1.358251
           10            0.037230
           11           -0.307251
           12            0.311340
2023       1            -0.207666
           2             0.077856
Name: Tonnage, dtype: float64

The first line is NaN, because there’s nothing to compare it with. In August 2022, we see that exports jumped a whopping 62 percent. Since then, though, the exports seem to have stayed relatively stable, going up and down a bit each month.

Again, I’m far from an expert in international trade, but I kind of figured that the numbers would grow each month. That definitely didn’t happen.

5. Finally, what 10 flags are most commonly used on the ships coming from Ukraine? Do any country's names appear more than once? If so, why, and how can you fix it?

Every ship flies under the flag of a country, and is thus under the laws of that country. This has led to a cottage industry in which ships fly the flags of countries they’ve never (or rarely) been to. This is something known as flying a “flag of convenience.”

I was thus curious to know which flags ships were using when they left Ukraine. We can find the 10 most common flags by running the “value_counts” method on the “Flag” column, then taking the 10 top values from that list:

df['Flag'].value_counts().head(10)

The most common flag? Liberia, followed by Panama and the Marshall Islands. You could maybe, sorta, kinda understand why Panama (which bridges two oceans) might make sense. But Liberia and the Marshall Islands? According to the Wikipedia article on “flags of convenience,” more than 40 percent of the world’s ships are registered to those three countries. And since the 1960s, Liberia has had more ship registrations than the UK.

This might have been a cute tidbit to investigate, if it weren’t for an odd result that I saw here, namely that the Marshall Islands appeared twice. Given that “value_counts” returns a series whose index contains unique values, this seemed very odd to me, and I decided to investigate.

It turns out that sometimes, the string “Marshall Islands” had a space between the two words. But sometimes, it had something that looked like a space character, but was a distinct value in Unicode, known as a “non-breaking space.” Because spaces and non-breaking spaces look the same, but are distinct characters in Unicode, the two versions of “Marshall Islands” appeared separately.

How can we fix this? By replacing all occurrences of non-breaking space (\xa0 in a Python string) with a regular space. To do that, we’ll use the “.str” accessor on the “Flag” column, invoking the “replace” method:

df['Flag'] = df['Flag'].str.replace('\xa0', ' ')

The invocation of “str.replace” returned a new series, which I assigned back to df[‘Flag’]. Then I could again run “value_counts”, getting the top 10 different countries:

Liberia             162
Panama              145
Marshall Islands    112
Malta                91
Barbados             66
Palau                38
Comoros              31
Türkiye              28
Belize               24
Vanuatu              19
Name: Flag, dtype: int64

And there you have it!

Questions? Thoughts? Alternative solutions? Post them to the thread!

And of course, if you have ideas for how I can make Bamboo Weekly better, don’t hesitate to e-mail me.

Meanwhile, here’s my Jupyter notebook for this week: https://drive.google.com/file/d/1vY-lwEa0wl83cLuGScUSjPBJClS2V8XJ/view?usp=drive_link

Until next week,

Reuven