> ## 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 #50: Red Sea shipping (solutions)
- URL: https://www.bambooweekly.com/bw-50-red-sea-shipping-solution/
- Published: 2024-01-25T17:28:11.000Z
- Updated: 2026-08-23T09:36:56.000Z
- Description: Get better at: CSV, multiple files, filtering, regular expressions, dates and times, window functions
- Author: Reuven M. Lerner
- Tags: csv, multiple-files, filtering, regular-expressions, datetime, window-functions

This week, we looked at data describing the movement of ships near and through the Red Sea. The Houthi rebels in Yemen recently threatened and attacked ships entering the Red Sea via Bab el Mandeb, the "Strait of Tears” that I marked with a red dot on the below map. They are doing this in solidarity with Hamas, which killed and kidnapped hundreds of Israelis on October 7th, and which is now at war with Israel in the wake of those attacks. The threatened ships are typically on their way to or from Egypt’s Suez Canal, marked with a purple dot on the map below.

![](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-2f667b7ecb-dcf6-44b8-851d-0ebbc13d61f7_1562x1916.jpg)

A huge proportion of the world’s goods pass through the Suez Canal. Given the greater risk that ships face when passing through that area, I’ve read and heard that companies have changed their routes to go around South Africa’s Cape of Good Hope. Needless to say, makes for a longer and more expensive journey. The Houthis’ actions have the potential to raise prices food goods around the world.

### Data and seven questions

This week, we looked at data from PortWatch ([https://portwatch.imf.org/](https://portwatch.imf.org/?ref=bambooweekly.com)), a joint project between the International Monetary Fund and the University of Oxford. They are using a variety of sources, including satellite trackers, to count the number of ships that pass through numerous ports around the world. Because of the situation at the Red Sea, they have created a special page ([https://portwatch.imf.org/pages/573013af3b6545deaeb50ed1cbaf9444](https://portwatch.imf.org/pages/573013af3b6545deaeb50ed1cbaf9444?ref=bambooweekly.com)) describing and depicting the number of ships passing through Bab el Mandeb, the Suez Canal, and the Cape of Good Hope.

I asked you to download the CSV files associated with those three data points. On my computer, they were named:

- bab-el-mandeb-strait-dai.csv
- suez-canal-daily-transit.csv
- cape-of-good-hope-daily.csv

Based on these three files, I asked seven question; as always, a link to the Jupyter notebook that I used to solve the problems is at the bottom of this issue.

### Create a dictionary of data frames, in which the keys are short (3-5 letter) nickname for the data source, and the values are data frames, one for each of the three CSV files. In each data frame, the original "DateTime" column should be the index, and parsed as a "datetime" dtype. Keep only the "Number of Cargo Ships" and "Number of Tanker Ships" columns, renamed to something shorter.

Before doing anything else, I loaded up Pandas:

```
import pandas as pd
```

We can always load a CSV file into Pandas with “[read\_csv](https://www.bambooweekly.com/pandas-read-csv/)”. If we have to load several CSV files into a single data frame, then we can often do so with a combination of a list comprehension and “[pd.concat](https://www.bambooweekly.com/pandas-concat/)”.

But here, things are a bit different: I want to merge the data from each CSV file into a single data frame, but each file contains parallel data, with identical column names.

Here’s how I did it:

First, I created a dict whose keys were strings, the nickname that I assigned to each data set. The values of that dict were the filenames from which I was going to read the data. Note that I didn’t repeat the “.csv” extension, since I can add that later:

```
all_filenames = {'mandeb': 'bab-el-mandeb-strait-dai',
                 'suez': 'suez-canal-daily-transit',
                 'gh': 'cape-of-good-hope-daily'}

```

I wanted to create a second dict, one whose keys would be identical to all\_filenames, but whose values would be data frames based on those filenames. Since I have an iterable (a dict) and I want to create a new dict based on it, I decided that a dict comprehension would be a good way to go:

```
all_dfs = { 
    key  : 
    pd.read_csv(os.path.join('/Users/reuven/Downloads', 
                             f'{filename}.csv'))
    for key, filename in all_filenames.items() 
}
```

The above dict comprehension iterates over all\_filenames using the “[dict.items](https://docs.python.org/3.11/library/stdtypes.html?ref=bambooweekly.com#dict.items)” method, giving us each key-value pair from all\_filenames, one at a time.

I took the key, and used it (as is) for the key in the output dict. Because the filenames were all in my “Downloads” directory, I used “[os.path.join](https://docs.python.org/3/library/os.path.html?ref=bambooweekly.com#os.path.join)” to produce a filename based on its arguments. It’s here, as you can see, that I added the “.csv” extension to each filename.

The good news is that the above code works, giving me a dict of data frames. But there were a few more things that I had to do in order to get it into shape:

First, I wanted to change the names of the columns to something shorter. The good news is that each file named the columns identically, giving us some consistency. The bad news is that you cannot have identically names columns in a data frame. As a result, I decided to rename the columns not only to be shorter, but to guarantee uniqueness. I could do this with the “names” keyword argument for read\_csv:

```
all_dfs = { 
    key  : 
    pd.read_csv(os.path.join('/Users/reuven/Downloads', 
                             f'{filename}.csv'),
                names=['date', f'cargo_{key}', f'tanker_{key}'])
    for key, filename in all_filenames.items() 
}
```

However, the above code won’t work. That’s because I’ve only given names to three of the five columns in the file. I can select which columns are loaded using the “usecols” keyword argument. But because I’ve given alternative names to the columns, I’ll have to refer to them by integer indexes. Moreover, I’ll have to tell Pandas that the columns are given names on the first row (i.e., row index 0) of each file, but that we should ignore that row:

```
all_dfs = { 
    key  : 
    pd.read_csv(os.path.join('/Users/reuven/Downloads', 
                             f'{filename}.csv'),
                usecols=[0, 1, 2],
                names=['date', f'cargo_{key}', f'tanker_{key}'],
                header=0)
    for key, filename in all_filenames.items() 
}
```

Notice that I used f-strings to set the names. Students in my courses often believe that f-strings are always used along with the “print” function, but I tell them that actually, we can use them to define strings anywhere in a program. This is one example of where they’re useful beyond printing.

Finally, I asked you to have Pandas parse the date column as a “datetime” dtype, which we can do via the “parse\_dates” keyword argument to read\_csv. Plus, I asked you to make it the index, which we can do with the “index\_col” keyword argument to read\_csv.

In the end, I managed to create this dict of data frames as follows:

```
all_dfs = { 
    key  : 
    pd.read_csv(os.path.join('/Users/reuven/Downloads', 
                             f'{filename}.csv'),
                parse_dates=[0],
                usecols=[0, 1, 2],
                names=['date', f'cargo_{key}', f'tanker_{key}'],
                header=0,
                index_col=0)
    for key, filename in all_filenames.items() 
}
```

Could I have created a list, rather than a dictionary? Yes, in this case, that would have been just fine. However, I found it a bit easier to manage and work with as a dict, rather than a list.

### Join these three data frames together into a single data frame with six columns (two from each of the original data frames).

With this dict in place, I wanted to join them together into a single data frame.

As I mentioned above, the easiest way to combine several data frames into a single one is with pd.concat, to which you pass an iterable of data frames as an argument. The data frames are joined, top to bottom, and you end up with one, large data frame.

In our case, though, we don’t want to stack them top to bottom. Rather, we want to stack them side-to-side, such that we get a new data frame with six columns, two from each of the original ones.

Instead of passing the entire “all\_dfs” dictionary to pd.concat, we’ll just pass the values from the dict. When you invoke the “[dict.values](https://docs.python.org/3/library/stdtypes.html?ref=bambooweekly.com#dict.values)” method, you get a special type of object that uniquely used for dict values. But for our purposes, that doesn’t matter, because it’s iterable, and we can thus pass it to pd.concat.

To tell pd.concat that we want to concatenate them sideways, rather than top-to-bottom, we pass the axis=“columns” keyword argument to pd.concat:

```
df = pd.concat(all_dfs.values(), axis='columns')
```

The result of calling pd.concat is a new data frame, which we assign to the variable “df”.

We’re now ready to get started analyzing the data!

### What is the mean number of cargo ships we see in each location each month? Create a line plot graphing it. Do we see any indication of the conflict's influence over the last few months?

To answer this question, we’ll first need to retrieve just the cargo ships from our data frame. Then we’ll need to find how many cargo ships there were in each month.

As things currently stand, our data frame has six columns. Three of those measure cargo ships, and three of them measure oil tankers. I could retrieve them explicitly, putting a list of column names inside of square brackets.

However, we can also use the “[filter](https://www.bambooweekly.com/pandas-filter/)” method on our data frame. This method lets us specify the names of rows or columns we want either via explicit strings or by specifying a regular expression. Given that the three columns I want are easily described with a regexp, I decided to go in that direction:

```
(
    df
    .filter(regex='^cargo')
)
```

What we get back is a subset of df, with all of the rows but only the three columns having to do with cargo ships. The regular expression I used indicated that the word “cargo” should come at the beginning of the string.

Next, I use the “[resample](https://www.bambooweekly.com/pandas-resample/)” method. This only works on data frames whose indexes contain datatime values, aka “time series.” You can think of resampling as a form of grouping. But rather than grouping by a specific value, we’re grouping based on spans of time. For example, we can group by 1 day, 2 weeks, 3 months, or 4 years. We then get at result for each of these chunks.

Specifying the chunk size uses offsets, as described here: [https://pandas.pydata.org/pandas-docs/stable/user\_guide/timeseries.html#dateoffset-objects](https://pandas.pydata.org/pandas-docs/stable/user%5Fguide/timeseries.html?ref=bambooweekly.com#dateoffset-objects)

I was interested in finding out how many cargo ships passed through each recorded area per month. I can thus use a resample code of “1ME”. If you’ve used resample before, then you might be wondering why it’s 1ME and not just 1M. The answer is that the core Pandas developers are tightening things up and making them more consistent. Rather than the “MS” for “month start” and “M” for “month end,” they’re now asking us to use “ME” for “month end.” I end up with the following:

```
(
    df
    .filter(regex='^cargo')
    .resample('1ME')
    .mean()
)
```

The above code returns a data frame whose index contains datetime objects from the end of each month, and whose columns come from our original data frame. The values, though, show the mean number of ships that passed by:

```
            cargo_mandeb  cargo_suez   cargo_gh
date                                           
2019-01-31     34.645161   34.709677  32.161290
2019-02-28     35.857143   35.107143  34.285714
2019-03-31     34.290323   33.354839  29.032258
2019-04-30     37.566667   37.000000  35.466667
2019-05-31     34.741935   36.129032  35.419355
...                  ...         ...        ...
2023-09-30     49.733333   49.300000  46.866667
2023-10-31     51.645161   51.258065  40.387097
2023-11-30     50.733333   51.166667  38.966667
2023-12-31     39.129032   45.064516  40.000000
2024-01-31     25.761905   32.809524  54.095238
```

With this in hand, we can now invoke “plot.line” and get a line plot of the [mean](https://www.bambooweekly.com/pandas-mean/) number of ships in each location, every month:

```
(
    df
    .filter(regex='^cargo')
    .resample('1ME')
    .mean()
    .plot.line()
)
```

The results make the situation very clear:

![](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-2f36103200-ae2b-436b-8b3a-c8138482174b_560x432.jpg)

Look at the rightmost part of the graph: The number of ships going through Bab el Mandeb and the Suez Canal has dropped dramatically, while the number of ships going around the Cape of Good Hope has skyrocketed.

### Create a line plot showing the number of oil tankers in each location during each week, starting the first week of September 2023\. Did the problems start immediately after the Hamas attack of October 7th?

Now let’s create a similar plot, but for the oil tankers (rather than cargo ships). And we’ll start not at the beginning of our data set, but only in September 2023\. Also, we’ll resample by week, rather than by month.

First, let’s keep only those rows starting in September 2023\. It turns out that we can do that using “[.loc](https://www.bambooweekly.com/pandas-loc/)”; because our index contains datetime values, we can pass a string to retrieve rows matching that exactly. Or we can pass a slice of strings, which will be converted into datetime objects.

But it gets better than that: If we pass only part of a date string, then all of the smaller parts are treated as wildcards. That is, if we pass year-month-date-hour-minutes, then all seconds are included. Here, then, I passed “2023-09”, which matches all dates in September 2023\. And then, because it was the first part of an open-ended slice, we got all of the rows starting in September:

```
(
    df
    .loc['2023-09':]
)
```

Then I wanted only those columns having to do with oil tankers. Much as I did before, I invoked filter and grabbed them via a regular expression:

```
(
    df
    .loc['2023-09':]
    .filter(regex='^tanker')
)
```

I then resampled in 1-week chunks, using “1W” as my resampling offset, follwed by a call to mean and plot.line:

```
(
    df
    .loc['2023-09':]
    .filter(regex='^tanker')
    .resample('1W')
    .mean()
    .plot.line()
)
```

This is 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-2f843cc6b7-69f2-4d6e-b845-dc9fbaf83314_543x449.jpg)

We see a similar dive in the number of ships going through the Red Sea locations, and a similar rise in the number of oil tankers going around the Cape of Good Hope. But we also see that the changes didn’t happen (at least, not in a big way) immediately after the October 7th attacks on Israel. Only in early December, from what we see here, did shipping companies start to move things elsewhere — presumably after it became clear that the Houthis are serious about attacking ships passing nearby.

### When was the number of ships (of either type) going through the Suez Canal at a minimum?

Let’s use “filter” again, this time asking only for those columns that have “suez” in their names:

```
(
    df
    .filter(regex='suez$')
)
```

Now, if we wanted to know the minimum number of ships that passed through the Suez Canal, we could call “[min](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.min.html?ref=bambooweekly.com)”. But we don’t want the number; we want to know *when* that number occured. Given that the index contains dates, we can use “[idxmin](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.idxmin.html?ref=bambooweekly.com)”, which returns the index corresponding to a minimum value.

If we invoke it on a data frame, then we get the idxmin for each of the columns in the data frame. Which means, since we’ve narrowed our data frame down to two Suez Canal-related columns, we’ll find out when those were at a minimum:

```
(
    df
    .filter(regex='suez$')
    .idxmin()
)
```

### Those low numbers are from March 2021, when a large tanker (the Ever Given) got stuck in the Suez Canal and blocked traffic. Show, for both the Suez Canal and Bab el Mandeb, the smallest and largest number of ships for each rolling 3-day period from March 15, 2021 through April 1st.

(The original version of this question referenced the “Evergreen” — the ship was actually called the “Ever Given,” and it was the subject of lots of discussion for a few good weeks. You can read more about this incident here: [https://en.wikipedia.org/wiki/Ever\_Given](https://en.wikipedia.org/wiki/Ever%5FGiven?ref=bambooweekly.com) .)

I asked you to find, from March 15th, 2021 through April 1st, 2021, the smallest and largest number of ships going through Suez Canal and Bab el Mandeb.

Let’s start by narrowing the rows to only the Suez Canal and Bab el Mandeb:

```
(
    df
    .filter(regex='(mandeb|suez)$')
)
```

Notice that I again used the filter method with a regular expression, this time using alternation to indicate that I wanted columns ending with “mandeb” or with “suez”.

Now that I’ve selected those columns, I’ll select rows in the time span that was requested. Once again, I’ll use loc in combination with a slice. Remember that when we use loc, the endpoint of a slice is *included* in the output. That’s a stark contrast with just about everywhere else in Python, where the endpoint is *not* included.

I then asked you to find the minimum and maximum number of ships. But I didn’t ask you to find it for each day (i.e., for each index value), or even for each month or week, as we did earlier with resampling. Rather, I asked you to find it for each three-day period.

This is possible with what’s known as a “[window function,](https://pandas.pydata.org/pandas-docs/stable/user%5Fguide/window.html?ref=bambooweekly.com)” and the idea is that you’re performing an aggregation that’s similar to a “[groupby](https://www.bambooweekly.com/pandas-groupby/)”. But whereas groupby runs on each distinct value in a category, window functions run on adjacent rows in a data frame.

The “[rolling](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.rolling.html?ref=bambooweekly.com)” method, for example, takes a numeric argument, indicating how many rows should be grouped together at a time. If I call rolling(3).min(), then it’ll call min on rows 0-2, then on rows 1-3, then on rows 2-4, then on rows 3-5, etc., until it gets through all of the rows in this way.

That’s why it’s called a window function; we have a window of (in this case) 3 rows, and we’ll perform the calculation for each one.

But wait: I didn’t ask you to calculate the min for each row. I asked you to calculate both min and max. Which we can do with the “agg” method; we then pass it a list of strings (‘min’ and ‘max’ in this case) allowing us to choose multiple aggregation methods.

Here’s the final query:

```
(
    df
    .filter(regex='(mandeb|suez)$')
    .loc['2021-03-15':'2021-04-01']
    .rolling(3).agg(['min', 'max'])
)
```

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-2fc03fa358-de76-4134-b8cd-774581cc307f_1690x2032.png)

Notice the NaN values in the two top rows? They’re there because when you have a window size of 3, you can only start to get results on the 3rd row.

And once again, we can see that during late March 2021, there were periods with 0 ships going through the canal, which is kind of bonkers.

### Calculate, for each 3-month period (quarter) in our data set, the mean number of ships of each type, in each location. Now calculate the percentage change in that mean for each quarter. Where is the greatest increase for each location? Where is the greatest decrease?

Finally, I asked you to get the mean number of ships of each type, in each location, for each quarter. This sounds like a job for resampling:

```
(
    df
    .resample('1QE')
    .mean()
)
```

Note that we say “1QE”, which means “quarter end.” Here’s what we get:

![](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-2fb4d3cc56-758e-411d-9e13-4881702c1148_2220x2232.png)

As always when we use resample, the resulting data frame’s index contains the *final* datetime value for the period we specified.

I then asked you to calculate the percentage change in each quarter vs. the previous one. For that, we’ll use a specialized window function, “[pct\_change](https://www.bambooweekly.com/pandas-pct-change/)”, which indicates the percent by which each row’s value differs from the previous one:

```
(
    df
    .resample('1QE')
    .mean()
    .pct_change()
)
```

The result:

![](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-2f248143ce-45ed-48f1-9824-16890c39ac75_2208x2220.png)

We can see that in the last quarter, the numbers for both Bab el Mandeb and Suez have gone done… by a *lot*! And we can see that ships going around the Cape of Good Hope have increased, also by a lot. This, of course, confirms what we saw in the graphs.

But maybe we’re missing something in this sea of numbers. Let’s ask Pandas to find the quarters with the highest and lowest percentage changes. Once again, we can use agg with idxmin and idxmax:

```
(
    df
    .resample('1QE')
    .mean()
    .pct_change()
    .agg(['idxmin', 'idxmax'])
)
```

Here’s the result:

```
       cargo_mandeb tanker_mandeb cargo_suez tanker_suez   cargo_gh  tanker_gh
idxmin   2024-03-31    2024-03-31 2024-03-31  2024-03-31 2020-12-31 2020-12-31
idxmax   2020-09-30    2022-06-30 2021-06-30  2022-06-30 2024-03-31 2024-03-31
```

We’re getting back a date of 2024-03-31 — which hasn’t happened yet. How is that possible? Because the first quarter of 2024 is listed in the result from “resample” as the last day of that quarter, namely March 31st.

In other words, we can confirm that the biggest decreases for Bab el Mandeb and Suez have happened now, along with the greatest increases for the Cape of Good Hope.

Speaking of hope, I hope that you enjoyed this week’s problems. The Jupyter notebook I used in my solutions is at [https://drive.google.com/file/d/1XIhPLwlpbbjwkNLE\_Coq91oJC-bi73iW/view?usp=sharing](https://drive.google.com/file/d/1XIhPLwlpbbjwkNLE%5FCoq91oJC-bi73iW/view?usp=sharing&ref=bambooweekly.com).

I’ll be back on Wednesday with more Pandas puzzles based on current events.

Reuven