> ## 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 #22: Banana index (solutions)
- URL: https://www.bambooweekly.com/bw-22-banana-index-solution/
- Published: 2023-06-29T15:01:03.000Z
- Updated: 2026-08-23T09:37:12.000Z
- Description: Get practice working with CSV files, cleaning, filtering, sorting, correlations, and styling.
- Author: Reuven M. Lerner
- Tags: csv, cleaning, filtering, sorting, correlations, styling

This week, we looked at the “Banana index” from the Economist, downloading its first-ever release of data, and then finding foods which have a bigger carbon footprint than a banana.

Along the way, we looked at ways to filter columns and rows, to employ broadcasting, to look into correlations, and even to decorate our data frame in different colors depending on the values inside.

### Data

The repo for the banana index is at:

[https://github.com/TheEconomist/banana-index-data/](https://github.com/TheEconomist/banana-index-data/?ref=bambooweekly.com)

We're going to download the data from version 1.0 of the banana index, in this CSV file:

[https://github.com/TheEconomist/banana-index-data/releases/download/1.0/bananaindex.csv](https://github.com/TheEconomist/banana-index-data/releases/download/1.0/bananaindex.csv?ref=bambooweekly.com)

### Questions

This week, I asked you seven questions:

### 1\. Download the data into a data frame. Set the index to be the “entity” column. Remove the “year”, “Banana values”, “type”, and “Chart?” columns.

Let’s start by loading up Pandas:

```
import pandas as pd
```

With that in place, we need to load the CSV file. In theory, we could download the file to the local filesystem and read with it “[pd.read\_csv](https://www.bambooweekly.com/pandas-read-csv/)”. However, read\_csv, like most of the “read” methods in Pandas, accepts a filename, a file-like object, or a URL as its first argument. We can thus read the file directly from its URL, straight into a data frame:

```
url = 'https://github.com/TheEconomist/banana-index-data/releases/download/1.0/bananaindex.csv'

df = pd.read_csv(url)
```

Now, I asked that the “entity” column be set to the index. We could, in theory, first read the CSV file and then use “[set\_index](https://www.bambooweekly.com/pandas-set-index/)” to modify it. But it’s easier and faster for us to simply pass the “index\_col” keyword argument to read\_csv:

```
df = pd.read_csv(url, index_col='entity')
```

In this particular case, we’re only going to use one column as an index. However, as is often the case in Pandas, you can pass a list of strings, rather than a single string, if you want to create a multi-index based on more than one of the columns in the downloaded file.

I then asked you to remove several of the columns, which we aren’t going to be using. The easiest way to do this is with the “[drop](https://www.bambooweekly.com/pandas-drop/)” method. You can call “drop” with a single string value or with a list of strings. However, by default, “drop” works on the index (i.e., rows), rather than the columns. We can change this by passing “axis='columns'”:

```
df = df.drop(['year', 'Banana values', 'Chart?', 'type', 'Unnamed: 16'], axis='columns')
```

Note that instead of dropping these columns, I could have passed the “usecols” keyword argument to “read\_csv”, indicating which columns I wanted to keep. The only issue there is that I wanted to keep most of them and drop only a handful, so I did it this way.

I now have a data frame with all of the data I want, with 160 rows and 111 columns.

Note that when I was putting together this week’s issue, I found that “read\_csv” sometimes gave me an error when trying to retrieve the document from GitHub. I’m not sure what the cause of the problem was, but I found that waiting a few seconds and then retrying solved the issue. My best guess is that GitHub is trying to stop programs from retrieving data, and thus throttles the frequency with which they can make requests, but I’m really not sure.

### 2\. Three of the columns contain pre-computed banana scores for kg, calories, and protein. For each of these columns, show the 10 highest-scoring food products.

In order to answer this question, we’ll need to do a few different things:

1. Find the columns with the pre-computed banana scores
2. Find the 10 highest-scoring food products for each of these columns
3. Display these 10 highest-scoring products in each column

First: How can we find the columns with pre-computed banana scores? We can get a full list of columns with the “[columns](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.columns.html?highlight=columns&ref=bambooweekly.com#pandas.DataFrame.columns)” attribute:

```
df.columns
```

Notice that it’s an attribute, not a method, so you don’t want to use parentheses.

The result that I get is an index object, which you can think of as a list of strings with some additional capabilities:

```
Index(['emissions_kg', 'emissions_1000kcal', 'emissions_100g_protein',
       'emissions_100g_fat', 'land_use_kg', 'land_use_1000kcal',
       'Land use per 100 grams of protein', 'Land use per 100 grams of fat',
       'Bananas index (kg)', 'Bananas index (1000 kcalories)',
       'Bananas index (100g protein)'],
      dtype='object')
```

There are three columns that clearly contain banana index data. I can always retrieve columns from a data frame by passing a list of columns inside of square brackets — that is, nested square brackets — as follows:

```
df[['Bananas index (kg)', 'Bananas index (1000 kcalories)',        'Bananas index (100g protein)']
```

And yes, this will work! But… yuck, right?

I would greatly prefer to have Pandas give me all of the columns whose names start with the string “Bananas”. Or even those whose names contain the substring “Bana” in them.

Fortunately, Pandas provides us with such a method, called “[filter](https://www.bambooweekly.com/pandas-filter/)”. Now, the “filter” method is confusing — to me, at least — because it doesn’t filter the values. Rather, it filters the columns by their names. It lets you get a subset of the columns by giving it a substring, and passing that as a value to the “like” keyword argument:

```
df.filter(like='Bana')
```

This returns a new data frame, based on df, in which we have all of the rows but only those columns containing “Bana” in their names.

I should add that if you prefer, you can pass a different keyword argument, namely “regex”, along with a string containing a regular expression. So we could also have said:

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

(I love regular expressions, but I realize that they can be a bit surprising and difficult for people to understand. If that describes you, check out my free, 14-part “regexp crash course” at [https://RegexpCrashCourse.com/](https://lerner.co.il/e-mail-courses/regular-expressions-crash-course/?ref=bambooweekly.com). You’ll be using regular expressions in no time!)

Anyway, now that we’ve narrowed the data frame down to the columns that we want, we’ll want to get the 10 top items for each of these columns. This means sorting our data frame by the values in each of these columns, and taking the top 10 values. Looking at the index after each sort will let us see which food items are associated with these values.

If we were only interested in sorting by one value, then it would be obvious how to handle it. How can we sort by three different columns?

I normally tell people that when you use a “for” loop in Pandas, you’re almost certainly doing something wrong. But that’s because you should use the built-in vectorization, rather than the (typical) Python way of doing things with iterations.

In this case, though, we need to iterate, using a “for” loop not over the values in our data frame, but rather on the columns. We can then print each column name, then the result of sorting that column by values, grabbing the top 10 values:

```
for one_column in df.filter(like='Bana'):
    print(one_column)
    print(df[one_column].sort_values(ascending=False).head(10))
    print()
```

The result of iterating over the result of “filter” is to get each column name, one at a time. We print the column name, then use it to retrieve that column from df. Note that this will return a series, rather than a data frame.

Then we invoke “[sort\_values](https://www.bambooweekly.com/pandas-sort-values/)” on that series, which returns a new series, sorted in ascending order. Actually, it would *normally* be in ascending order, but here we pass the keyword argument ascending=False, which gives us results in descending order. We can then invoke “[head(10)](https://www.bambooweekly.com/pandas-head/)” to get the 10 top values for each one. Finally, we invoke “print” with no arguments to give us a blank line.

The result looks like this:

```
Bananas index (kg)
entity
Beef steak         148.563324
Beef mince         108.816189
Beef meatballs      81.052853
Beef burger         61.803856
Lamb chops          35.383303
Lamb (leg)          35.198904
Lamb burgers        30.833345
Cottage cheese      28.944313
Parmesan cheese     27.499275
Prawns              23.943772
Name: Bananas index (kg), dtype: float64

Bananas index (1000 kcalories)
entity
Beef steak        77.752629
Beef mince        54.049393
Beef meatballs    35.782826
Cottage cheese    33.508657
Prawns            30.063042
Lettuce           28.915534
Raspberries       28.105624
Beef burger       25.011498
Lamb (leg)        17.335509
Coconut milk      14.670763
Name: Bananas index (1000 kcalories), dtype: float64

Bananas index (100g protein)
entity
Coconut milk      33.320155
Sugar             20.685802
Grapes            15.128521
Beef steak         8.312805
Raspberries        7.878815
Beef mince         6.878002
Marmalade          6.495684
Butter             6.192303
Lettuce            5.173570
Beef meatballs     4.612929
Name: Bananas index (100g protein), dtype: float64
```

If you’re interested in finding the *least* carbon-emitting foods on the banana scale, you could just invoke “[tail](https://www.bambooweekly.com/pandas-tail/)”.

### 3\. Which foods, if any, have the highest banana score on all three lists?

In the previous question, we printed out the 10 top-ranking foods on each of the three banana scales. I was curious to know which foods, if any, had high scores on all three of these scales. Lettuce, for example, might be 5x worse than bananas when it comes to carbon emissions, but only when you compare their protein content; when you compare caloric content, they’ll obviously be quite far apart.

How can we find the values that are on all three lists? In our “for” loop above, we iterated over each column, sorting it by value. The result was a new series whose index contained the names of the foods.

It turns out that index objects in Pandas are much smarter than people give them credit for, with a bunch of methods that can be quite useful. Among other things, index objects have an “[intersection](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Index.intersection.html?ref=bambooweekly.com)” method; pass a second index as an argument, and you’ll get back the items that are common to both of them.

So here’s my strategy: I’ll create a list of index objects. Then I’ll go through them one by one, running “intersection” on each index, and storing the result. After going through all of the indexes, any values that remain are common to all three top-10 lists.

I’ll first use a list comprehension to create a list of the indexes from the top-10 lists:

```
high_scoring_foods = [
    df[one_column].sort_values(ascending=False).head(10).index
    for one_column in df.filter(like='Bana')
]
```

Next, I’ll start with the first index:

```
base_index = high_scoring_foods[0]
```

I’ll now iterate over each of the remaining indexes, invoking “intersection” on base\_index while passing the current index. I’ll then assign the result back to “base\_index”:

```
for one_index in high_scoring_foods[1:]:
    base_index = base_index.intersection(one_index)
```

When I’m done, base\_index will contain only those elements that are common to all three banana-related measures. I can print them out using a \* to unpack the elements of base\_index as positional arguments, then adding the keyword argument “sep=', '” so that the different elements will be separated by a comma and space:

print(\*base\_index, sep=', ')

The result, on my system:

```
Beef steak, Beef mince, Beef meatballs
```

In other words: No matter how you slice, mince, dice, or chop it — beef products are among the top-10 carbon-emission problems among common foods we eat.

### 4\. Create a new column, named \`Bananas index (land use 1000 kcal)\`, calculating that food item's use of land for every 1,000 kcal. Which 10 foods have the highest score? Do any appear on the three previous lists?

While we have a column for each food’s use of land per 1,000 kcal, there is no official index for that measure, ranking it relative to the banana. Land use seems like an important thing to measure, so let’s calculate it ourselves!

First, I’ll need to get the banana value for that measurement. We can do that with “[loc](https://www.bambooweekly.com/pandas-loc/)”. Remember that when we call “loc” with two arguments, the first is a row selector and the second is a column selector. In this case, the selectors will actually be simple strings:

```
df.loc['Bananas', 'land_use_1000kcal']
```

Now that we have the measurement of bananas for 1,000 kcal, we want to divide each of the other values in that column by that number. In normal Python, we would use a “for” loop here — but as I indicated above, we almost never want to use a “for” loop in Pandas. Rather, we want to use a vectorized operation. In this case, that’ll be broadcasting, dividing a series by a float. That’ll return a new series, the result of dividing each of the series elements by that float. We can then assign the new series back to the data frame, in the form of a new column:

```
df['Bananas index (land use 1000 kcal)'] = df['land_use_1000kcal'] / df.loc['Bananas', 'land_use_1000kcal'] 
```

Remember that assigning to a column in a data frame either creates a new column (if the column name is new) or replaces an existing one (if the column name already exists).

Now that we have this new measure, let’s find out which 10 foods have the highest scores. I’ll use the same code as before:

```
for one_column in df.filter(like='Bana'):
    print(one_column)
    print(df[one_column].sort_values(ascending=False).head(10))
    print()
```

Because our new column starts with the word “Bananas”, it is picked up by our call to “filter”, and is thus displayed alongside of the other measurements. The land-use banana index produces the following output:

```
Bananas index (land use 1000 kcal)
entity
Beef steak          82.303271
Beef mince          54.147705
Beef meatballs      34.756181
Beef burger         20.074816
Cottage cheese      12.765236
Lettuce             12.760535
Lamb (leg)           7.890345
Lentils              7.555601
Lamb chops           6.148642
Chilli con carne     6.033678
Name: Bananas index (land use 1000 kcal), dtype: float64
```

Beef is still at the top, but we have a few other contenders now, including cottage cheese (!), lentils, and chilli con carne, which I never thought of as a standalone agricultural product… but hey, you learn something new every day, right?

What products appear in all four of these banana indexes? We can re-run our code, and find out:

```
high_scoring_foods = [
    df[one_column].sort_values(ascending=False).head(10).index
    for one_column in df.filter(like='Bana')
]

base_index = high_scoring_foods[0]
for one_index in high_scoring_foods[1:]:
    base_index = base_index.intersection(one_index)
print(*base_index, sep=', ')
```

Because we didn’t hard-code the column names before, and because our new column starts with “Bana”, we didn’t need to change our code at all. And the result?

```
Beef steak, Beef mince, Beef meatballs
```

Yup, the same as before.

### 5\. What kind of cheese has the highest banana score per 1,000 kcal?

I’m a big fan of cheese, and I saw a lot of types of cheese in the database assembled by the Economist. So I was curious to see which type of cheese might cause the greatest carbon emissions.

First, I’ll need to find all of the rows whose index mentions “cheese”. If we were talking about a non-index column, then we could use all sorts of string methods, such as “[str.contains](https://www.bambooweekly.com/pandas-str-contains/)”. But we have an index, so perhaps there’s another way?

We actually saw a different way to do this just before, in the “filter” method. But that works on columns, right? Yes, except that’s just the default: You can pass “axis='rows'” as a keyword argument, and have it return only those rows whose indexes contain the string you’ve passed. For example:

```
df.filter(like='heese', axis='rows')
```

This actually returns a data frame, albeit one with a smaller set of rows than we had before. We can then sort the data frame by the 1,000 kcal banana index, asking for the results in descending order:

```
df.filter(like='heese', axis='rows').sort_values('Bananas index (1000 kcalories)', ascending=False)
```

Since we only want the most carbon-emitting cheese, we’ll run “head(1)”:

df.filter(like='heese', axis='rows').sort\_values('Bananas index (1000 kcalories)', ascending=False).head(1)

And the winner? (Or loser?) Cottage cheese! I never would have guessed, actually.

### 6\. Show the correlations among the four computed banana scores. Which values seem to correlate most highly?

The banana index measures carbon emissions along several different dimensions. But perhaps some of these are closely related to one another — that is, when one goes up, the other goes up, as well. As statisticians love to point out, correlation is not the same as causation, but it can be interesting to see if there are similarities among these measures. (Here’s the obligatory XKCD reference on the subject: [https://xkcd.com/552/](https://xkcd.com/552/?ref=bambooweekly.com))

When we invoke the “[corr](https://www.bambooweekly.com/pandas-corr/)” method on a data frame, we get a new data frame back, one in which the columns of our data frame appear as *both* the columns and the index. The idea is to show how closely correlated each of the columns is with one another. The correlation score can be read as:

- +1: 100% positive correlation
- 0: No correlation at all
- \-1: 100% negative correlation

When I run “corr” on our data frame, I get the following:

![](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-2fc6c1ae2a-732f-4a77-8481-30e600f8fb15_2104x316.png)

The diagonal will always be 1.0, since every column is 100% positively correlated with itself.

We can see that there is a very strong correlation (0.88) between the land use index and the 1,000 kcal index. There’s an even stronger correlation (0.92) between the land use index and the kg of bananas index. That’s probably why the Economist decided not to include the land-use index in its final publication, even though it had that data around. After all, there isn’t really much use in publishing an index if it’s pretty much the same as another one.

We can also see that there is also a high positive correlation between the kg of bananas and the 1000 kcal of bananas (0.88).

### 7\. Highlight any correlations in the range 0.8 to 0.99 using a different color.

It’s nice to be able to look at our correlations and figure out which are high, and which are low. But every Pandas data frame has a “style” attribute that allows us to change its foreground and background colors based on the value in each cell.

You can set the styling in a variety of ways, and it’s worth reading about them at

[https://pandas.pydata.org/pandas-docs/stable/reference/style.html](https://pandas.pydata.org/pandas-docs/stable/reference/style.html?ref=bambooweekly.com)

Here, I just wanted to highlight those correlations that were between 0.8 and 0.99\. True, we could have asked for everything 0.8 and above, but that would have included the diagonals with 1.0 correlations, which is just distracting.

Fortunately, there’s a method that we can run on the “style” property called “[highlight\_between](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.io.formats.style.Styler.highlight%5Fbetween.html?highlight=highlight%5Fbetween&ref=bambooweekly.com#pandas.io.formats.style.Styler.highlight%5Fbetween)”, which takes two keyword arguments, “left” and “right”. We can thus say that if the value in the cell is between two particular values, then we want it to be colored in a particular way. My favorite highlight color is “#abcdef”, which works out to be a nice light blue. And thus, my code is:

```
df.filter(like='Bana').corr().style.highlight_between(color='#abcdef', left=0.8, right=0.99)
```

Notice how I’m running highlight\_between on the style property of the data frame we got back from invoking corr() — showing, once again, the power of method chaining, and how much Pandas wants us to use it.

The result is our correlation matrix, with all of the highly correlated measures in light blue:

![](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-2f0e4cd72f-a4ef-4119-9598-27cb3b32ec50_2120x298.png)

So, what do you think?

The Jupyter notebook that I used in putting together this week’s question is here: [https://drive.google.com/file/d/1RvzcMW77LwgcoJ-e8\_pw3PtXxYhApvX9/view?usp=sharing](https://drive.google.com/file/d/1RvzcMW77LwgcoJ-e8%5Fpw3PtXxYhApvX9/view?usp=sharing&ref=bambooweekly.com)

Questions or comments? Please share!

I’ll be back on Wednesday of next week with a new set of questions.

Reuven