> ## 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 #53: Airport animals (solutions)
- URL: https://www.bambooweekly.com/bw-53-airport-animals-solution/
- Published: 2024-02-15T16:01:29.000Z
- Updated: 2026-08-23T09:36:55.000Z
- Description: Get better at: PDF extraction, string operations, cleaning, multi-index, and window functions.
- Author: Reuven M. Lerner
- Tags: pdf-extraction, strings, cleaning, multi-index, window-functions

This week, we’re looking at data from the City of London’s [Heathrow Animal Reception Centre](https://www.cityoflondon.gov.uk/services/animal-health-welfare/heathrow-animal-reception-centre?ref=bambooweekly.com). If an animal (legally) enters the UK by air, then it almost certainly passes through HARC, which classifies and checks it. The center (er, “centre”) also takes care of animals that are hungry or injured — sort of an airport VIP lounge for non-human passengers. I read about HARC in a recent Economist story ("How to transport a rhino," [https://www.economist.com/britain/2024/01/25/how-to-transport-a-rhino](https://www.economist.com/britain/2024/01/25/how-to-transport-a-rhino?ref=bambooweekly.com)), which I recommend as being short, amusing, and informative.

I found a document, dated November 1st, 2023, describing HARC’s activities over a five-year period. This week, we extracted the data from that document, and then looked at the numbers and types of animals that HARC has dealt with. Along the way, we got some practice retrieving data from PDF files and cleaning the data. Of course, tracking animals isn’t anything new — in fact, you could argue that Noah was the first person in history to keep track of animals in binary.

### Data and seven questions

The data is, as I mentioned, buried inside of a letter in PDF format. The letter can be downloaded from here:

[https://committees.parliament.uk/writtenevidence/126507/default/](https://committees.parliament.uk/writtenevidence/126507/default/?ref=bambooweekly.com)

And no, there is no filename or extension on that URL. Going to that link should force the download of the data, at least from a normal browser. Using \`wget\` doesn't seem to work, however. Moreover, the link seems to produce a randomly named file with each download, so the filename I use here almost certainly won’t match the one that you get on your system.

I didn’t see a data dictionary for this information, but I think that it’s mostly self-explanatory. I did look up some of the animal-related terms, and will happily bore you with the details, if you like.

This week, I have seven tasks and questions for you to answer based on the data.

### Turn the table on page 3 of the PDF into a data frame. I used \`tabula-py\` (a wrapper around the \`tabula-java\` package written in Java), available on PyPI ([https://pypi.org/project/tabula-py/](https://pypi.org/project/tabula-py/?ref=bambooweekly.com)). I also used JPype1 ([https://pypi.org/project/JPype1/](https://pypi.org/project/JPype1/?ref=bambooweekly.com)), which improved the Python-to-Java communication.

First, let’s load Pandas:

```
import pandas as pd
```

With that in place, we can read the table from the PDF file into a Pandas data frame. We can load the “tabula” package and then use its “read\_pdf” function to grab the tables in the PDF file. I indicated that I wanted to read tables from page 3 of the PDF. However, “read\_pdf” returns a list of data frames, even if there is only one on the page. For this reason, we’ll need to retrieve the first element of that list, asking for index 0:

```
import tabula

filename = '/Users/reuven/Downloads/RG0OM-p-.pdf'
df = tabula.read_pdf(filename, pages=3)[0]
```

I found, however, that reading things in this way created a huge number of warnings. Some of them came from the Java system that tabula uses in order to read from PDF files, which disappeared when I installed the “JPype1” package. But others were pretty persistent, and seemed to be the result of some soon-to-be deprecated function calls within tabula. To quiet down those warnings, I loaded the “[warnings](https://docs.python.org/3/library/warnings.html?ref=bambooweekly.com#module-warnings)” module from the standard library, and told it that I wanted to ignore “FutureWarning”, the category that we were getting.

The final code that I used thus looked like this:

```
import tabula
import warnings

warnings.filterwarnings("ignore", category=FutureWarning)  

filename = '/Users/reuven/Downloads/RG0OM-p-.pdf'
df = tabula.read_pdf(filename, pages=3)[0]
```

The result? We now have a data frame with the same data (pretty much) as was in the PDF file.

By the way, if you want to learn more about Python’s “warnings” module, you can watch my talk from PyCon US 2019:

### The final column was mis-parsed, at least on my system, such that it contains information for both consignments and animals from 2023, separated by spaces. Replace this one column with two columns.

I was quite pleasantly surprised to see how well Tabula read data from the PDF file into Pandas. But I soon realized that the final two columns, containing data from the first 2/3 of 2023, was squished together into a single column.

The columns contained integers, and that the integers were separated by spaces. (If there had been words, then separating them would have been significantly harder.)

I started by using “[str.split](https://www.bambooweekly.com/pandas-str-split/)”, a Pandas method that we can apply to our series:

```
df['2023 Jan - Nov'].str.split()
```

Fortunately, this did the trick, giving me a 2-element list of strings in place of the original single string. But now what? How can I take that list and turn it into two separate Pandas series?

One option that I considered would be to grab the first element from each row, and then the second element from each row. I could do that, but it seemed pretty messy to me.

But an even better option is to use the “expand=True” keyword argument to “str.split”. This argument tells Pandas not to return a single series with a list of strings in each element, but rather to return a data frame, with each element in its own column:

```
df['2023 Jan - Nov'].str.split(expand=True)
```

This returns a two-column data frame, split in precisely the way that I wanted. However, its first row contained the original columns. I removed them by invoking “[drop](https://www.bambooweekly.com/pandas-drop/)”, passing 0 (i.e., the first row in the data frame):

```
df['2023 Jan - Nov'].str.split(expand=True).drop(0)
```

I then assigned these two columns to our data frame by just assigning to two new column names:

```
df[['2023 Consignments', '2023 Animals']] = df['2023 Jan - Nov'].str.split(expand=True).drop(0)

```

The only remaining problem with the mis-parsed column is that the old one is still around. We can remedy that by invoking “drop” again, this time telling it to remove a column:

```
df = df.drop('2023 Jan - Nov', axis='columns')
```

### Remove the first row. Set the index to be the (first) TAXA column. Drop the (final) "total" row. Drop the columns containing only NaN values (i.e., Unnamed 1, 3, 5, and 7). Turn all values into integers.

There’s still a bit of fixing and cleaning to do, as I outlined in this question.

I removed the first row by invoking “drop”, passing the integer 0, since that’s the index of the

```
df = (df
      .drop(0)
     )
```

Then I changed the index to use the “TAXA” column, using “[set\_index](https://www.bambooweekly.com/pandas-set-index/)”:

```
df = (df
      .drop(0)
      .set_index('TAXA')
     )
```

Next, I removed the final line, containing the totals for all animals and years. Once again, I used “drop” to remove it:

```
df = (df
      .drop(0)
      .set_index('TAXA')
      .drop('Total')
     )
```

Next, I wanted to remove the columns that contained only NaN values. I thought about a few different ways to do it, and finally decided that we’re only talking about four columns, and I know their names — so I can just use “drop” again, passing it a list of the columns. I did decide, however, to use a list comprehension to do it in a slightly fancier (if overkill) way:

```
df = (df
      .drop(0)
      .set_index('TAXA')
      .drop('Total')
      .drop([f'Unnamed: {n}'
              for n in range(1, 8, 2)], axis='columns')
     )
```

Finally, I invoked “[astype](https://www.bambooweekly.com/pandas-astype/)” on the data frame. Honestly, I hadn’t ever done this before; I often use “astype” on a series, but doing it to an entire data frame seemed like a bit much. And yes, you can pass “astype” a dict that specifies different types for different columns — but here, we’ll just use regular integers for all of the columns, so I passed the Python “int” type:

```
df = (df
      .drop(0)
      .set_index('TAXA')
      .drop('Total')
      .drop([f'Unnamed: {n}'
              for n in range(1, 8, 2)], axis='columns')
      .astype(int)
     )
```

The result is a data frame that we can finally (well, almost) start to use.

### Replace the original index with a two-level multi-index. The outer level will be the years 2019-2023, and the inner level will be "Consignments" and "Animals", repeated for each year, for a total of 10 columns.

The only remaining problem is that the column names are all messed up. We want a two-level, multi-index for our columns, matching the one that we see in the table in the original PDF. We should have four top-level columns (2019-2023), and under each we should have two low-level columns, “Consignments” and “Animals.”

In order to do this, we’ll need to create a multi-index object, and assign it to “df.columns”.

How can we create a multi-index? Pandas actually provides a few different functions that do this, such as “[MultiIndex.from\_arrays](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.MultiIndex.from%5Farrays.html?ref=bambooweekly.com#pandas.MultiIndex.from%5Farrays)” and “[MultiIndex.from\_tuples](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.MultiIndex.from%5Ftuples.html?ref=bambooweekly.com#pandas.MultiIndex.from%5Ftuples)”. But the one that I think fits the bill here is “[MultiIndex.from\_product](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.MultiIndex.from%5Fproduct.html?ref=bambooweekly.com#pandas.MultiIndex.from%5Fproduct)”, which takes two or more iterables (e.g., lists, tuples, or range) and produces a multi-index from the cartesian product of all these iterables.

Here, we can use Python’s “range” builtin for the years, and then a simple list of two strings for the second level:

```
df.columns = pd.MultiIndex.from_product([range(2019, 2024),
                            ['Consignments', 'Animals']])
```

And that’s it! Our data frame is now ready for us to perform some analysis.

![](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-2ff32d5e78-a36d-4799-9cce-c78b4a4b6c34_3204x818.png)

### What are the five most common types of animals that passed through Heathrow in 2023? Format the numbers with commas every three digits. What are the five most common types for which the total number was less than 1,000?

Let’s now dig into the numbers a bit: In 2023 (at least, in the part of 2023 for which we have data), what were the five most common animal types to enter Heathrow?

Normally, we could just select a column. But our columns are a multi-index, which means that we need to select both the outer and inner parts. If we know what we want, then we can use a tuple to indicate the two levels, putting that inside of square brackets:

```
(
    df
    [(2023, 'Animals')]
)
```

The above returns the column containing the number of animals that entered in 2023\. I asked you to find the five most commonly found animals. We could use “[sort\_values](https://www.bambooweekly.com/pandas-sort-values/)” and then “[head](https://www.bambooweekly.com/pandas-head/)” to grab them, but I’ve recently started to use “[nlargest](https://www.bambooweekly.com/pandas-nlargest/)” more and more, which combines those actions into a single method:

```
(
    df
    [(2023, 'Animals')]
    .nlargest(5)
)
```

We get the following:

```
TAXA
Invertebrate       3992517770
Butterfly Pupae     330684416
Fish                 16843687
Fish Eggs            11256545
Eggs (SPF)            5994799
Name: (2023, Animals), dtype: int64
```

That’s a lot of animals! But it’s also a bit hard to read. So I asked you to add commas to the output. There are a few ways to do that, but the easiest is to use “[apply](https://www.bambooweekly.com/pandas-apply/)”, passing lambda that does nothing more than put the number into an f-string, where the format string (after the colon) is a single comma:

```
(
    df
    [(2023, 'Animals')]
    .nlargest(5)
    .apply(lambda s: f'{s:,}')
)
```

Here’s the result we get:

```
TAXA
Invertebrate       3,992,517,770
Butterfly Pupae      330,684,416
Fish                  16,843,687
Fish Eggs             11,256,545
Eggs (SPF)             5,994,799
Name: (2023, Animals), dtype: object
```

I can’t stop imagining some poor government inspector at Heathrow trying to count the billions of invertebrates that people are trying to import.

![](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-2f7630d05a-4762-46cc-b858-b4d3dab29a20_1024x1024.jpg)

Anyway, given the ridiculously large number of small animals in this report, I asked what the 5 most common animals in 2023 were, where the number was less than 1,000\. The query is almost identical, except that I used “[loc](https://www.bambooweekly.com/pandas-loc/)” along with lambda to keep the number below 1,000, and then invoke “nlargest”:

```
(
    df
    [(2023, 'Animals')]
    .loc[lambda s_: s_ < 1000]
    .nlargest(5)
)
```

Here’s what I got:

```
TAXA
Horse            389
Lagomorph SPF    318
Bird of prey     232
Bird             213
Alpaca            48
Name: (2023, Animals), dtype: int64
```

In case you’re wondering (because I was!), a lagomorph is a rabbit-like animal ([https://en.wikipedia.org/wiki/Lagomorpha](https://en.wikipedia.org/wiki/Lagomorpha?ref=bambooweekly.com)). The SPF designation means that it was raised in a particularly pure environment that’s appropriate for scientific testing. In other words, these animals are headed for a laboratory experiment of some sort.

### Which animals had the greatest percentage growth from 2022 to 2023? Which had the greatest percentage drop? What does it mean when we see \`inf\` or \`NaN\`? Display the results as percentages, rather than floats.

Here, I wanted to know which animals had the greatest increase from 2022 to 2023\. Given that some of the animals number in the billions (!), while others are in the single or double digits, a comparison of raw numbers seemed less appropriate than a percentage change. Fortunately, the Pandas window function “[pct\_change](https://www.bambooweekly.com/pandas-pct-change/)” can measure the difference from one row to the next.

However, we don’t want to compare rows. Rather, we want to compare across columns. We can do this by passing the “axis” keyword argument, indicating that we want to do it across the columns.

But wait: The data frame contains alternating columns for consignments and animals. If we were to run “pct\_change” across all of the columns, we wouldn’t get accurate results. Before running our comparison, we thus need to retrieve only the columns for animal numbers. We can do that with “[xs](https://www.bambooweekly.com/pandas-xs/)”, a method that lets us select particular rows or columns from a multi-index:

```
(
    df
    .xs('Animals', level=1, axis='columns')
    .pct_change(axis='columns')
)
```

Notice what I did here: I told “xs” to retrieve all columns in which “Animals” is the value at level 1 (i.e., the inner level of our two-level multi-index). I then asked it to run “pct\_change” across the columns, giving me the following data frame:

![](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-2fd1cd3361-b23f-4f62-a14d-8d2b0a02fc00_1810x1626.png)

The first column is NaN, because it’s the basis for our comparisons. Each subsequent column shows the percentage change from the previous column. If we’re only interested in 2023, then we can select that column:

```
(
    df
    .xs('Animals', level=1, axis='columns')
    .pct_change(axis='columns')
    [2023]
)
```

I asked you to find the five biggest and smallest changes, which we can calculate with “nlargest” and “nsmallest”. It might seem like I need to calculate these in two separate queries. But I can use “[agg](https://www.bambooweekly.com/pandas-agg/)” to run both aggregation methods at the same time, getting the results together:

```
(
    df
    .xs('Animals', level=1, axis='columns')
    .pct_change(axis='columns')
    [2023]
    .agg(['nlargest', 'nsmallest'])
)
```

Here’s what I get:

```
                      nlargest  nsmallest
TAXA                                     
Sheep                      inf        NaN
Other ( E )        2324.000000        NaN
Lagomorph SPF        18.875000        NaN
Alpaca                3.800000        NaN
Ferret                2.444444        NaN
Donkey                     NaN  -1.000000
Goat                       NaN  -1.000000
Pig                        NaN  -1.000000
Invertebrate (AQ)          NaN  -0.995021
Butterfly Pupae            NaN  -0.980402
```

Because I used “agg”, I get a data frame back. And because there is no overlap between the biggest and smallest, we get NaNs in half of the rows for each column. We also have an “inf” result, which is what you would expect when calculating the percentage difference based on 0.

The only issue here is that the numbers are a bit hard to read. I thus asked you to display them as percentages (i.e., multiplied by 100 and with a “%” sign). We can do that, as before, using “apply” with lambda and an f-string:

```
(
    df
    .xs('Animals', level=1, axis='columns')
    .pct_change(axis='columns')
    [2023]
    .agg(['nlargest', 'nsmallest'])
    .style.format('{:,.2%}'.format)
)
```

Notice that the format code I used here was “,.2%”, which means that we want commas every three digits, and we want to display the number as a percentage with two digits after the decimal point. 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-2f5a69c70f-9a36-468b-a659-603310a053fb_1064x1170.png)

It’s sad that the number of invertebrates dropped by 99.5% from 2022 to 2023\. But that just means we went from 26.7 billion to 3.9 billion.

### Produce a line plot showing, over the years, the number of consignments of dogs, cats, fish, and horses that entered Heathrow. The x axis should represent years, and the y axis should show the number of consignments.

In order to get only the consignment-related columns, we can again use “xs”:

```
(
    df
    .xs('Consignments', level=1, axis='columns')
)
```

This returns a data frame in which the animals form the index, and the years form the columns. To create the kind of graph I asked for, we need to turn the data frame around, swapping rows and columns. We can do this with the “[transpose](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.transpose.html?ref=bambooweekly.com)” method, but that is normally abbreviated as “T”, which does *not* need parentheses:

```
(
    df
    .xs('Consignments', level=1, axis='columns')
    .T
)
```

We can now retrieve a subset of the columns, using double square brackets, and then create the plot with “[plot.line](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.plot.line.html?ref=bambooweekly.com)”:

```
(
    df
    .xs('Consignments', level=1, axis='columns')
    .T
    [['Dog', 'Cat', 'Fish', 'Horse']]
    .plot.line()
)
```

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-2f0e8a4b5a-ab3a-44f5-9605-8db667915767_562x413.jpg)

And that’s it for this week!

Here is my Jupyter notebook: [https://drive.google.com/file/d/1ifq9\_aDODT3PxYRrRoY3y0iiJtn094iP/view?usp=sharing](https://drive.google.com/file/d/1ifq9%5FaDODT3PxYRrRoY3y0iiJtn094iP/view?usp=sharing&ref=bambooweekly.com)

I’ll be back next with with more Pandas questions based on current events.

Reuven