> ## 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 #72: City travel (solutions)
- URL: https://www.bambooweekly.com/bw-72-city-travel-solution/
- Published: 2024-06-27T15:00:25.000Z
- Updated: 2026-08-23T09:36:45.000Z
- Description: Get better at: CSV files, PyArrow, multi-indexes, index operations, plotting, grouping, sorting, correlations, and joins.
- Author: Reuven M. Lerner
- Tags: csv, pyarrow, multi-index, index-operations, plotting, grouping, sorting, correlations, joins

This week, we looked at the rich data set collected by Rafael Prieto-Curiel and Juan P. Ospina in their recent paper, "The ABC of mobility" ([https://www.sciencedirect.com/science/article/pii/S0160412024001272](https://www.sciencedirect.com/science/article/pii/S0160412024001272?ref=bambooweekly.com)). Their research looked at a large number of cities around the world, and how people travel to work within them. They broadly classified travel methods as A, B, or C:

- `A` stands for "active," and includes walking and biking
- `B` stands for "bus," and is the overall category for public transportation
- `C` stands for "car," and describes people who drive to work

They found, not surprisingly, that cities in North America rely on cars more than in the rest of the world.

A recent article in the Economist ([https://www.economist.com/interactive/2024-walkable-cities](https://www.economist.com/interactive/2024-walkable-cities?ref=bambooweekly.com)) summarized the paper. This seemed relevant not only because of when the paper was published, but also because of the recent cancellation of congestion pricing in Manhattan, which was about to go into effect later this month ([https://www.nytimes.com/2024/06/05/nyregion/congestion-pricing-pause-hochul.html?unlocked\_article\_code=1.2k0.xMmn.a7Q2Lxo3J885&smid=url-share](https://www.nytimes.com/2024/06/05/nyregion/congestion-pricing-pause-hochul.html?unlocked%5Farticle%5Fcode=1.2k0.xMmn.a7Q2Lxo3J885&smid=url-share&ref=bambooweekly.com)).

### Data and seven questions

The researchers behind the ABC paper have a Web site ([https://citiesmoving.com/](https://citiesmoving.com/?ref=bambooweekly.com)) with interactive visualizations. And, fortunately for us, they have also made their data available at

[https://github.com/rafaelprietocuriel/ModalShare/blob/main/ModalShare.csv](https://github.com/rafaelprietocuriel/ModalShare/blob/main/ModalShare.csv?ref=bambooweekly.com)

Here are the seven tasks and questions that I gave you, along with my detailed explanations. As always, a link to the Jupyter notebook that I used to solve these problems is at the bottom of the page.

### Import the CSV file into a data frame. We'll want a three-part multi-index from the `region`, `Country`, and `City` columns.

Before doing anything else, we'll load up Pandas:

```python
import pandas as pd
```

With that in place, we can load up the CSV file. Because it wasn't too large, I decided to load the entire thing all at once, using [read\_csv](https://www.bambooweekly.com/pandas-read-csv/). However, it took a bit longer than I would have expected, so I used the PyArrow engine, by giving the `engine='pyarrow'` keyword argument:

```python
filename = '/Users/reuven/Downloads/ModalShare.csv'

df = pd.read_csv(filename, engine='pyarrow')
```

(In order to use the `pyarrow` engine, you'll need to install the package with `pip install pyarrow`.)

However, I also asked you to create a three-part multi-index. We could, of course, use [set\_index](https://www.bambooweekly.com/pandas-set-index/) after creating the data frame. But it's easier to just pass the `index_col` keyword argument to `read_csv`. As is almost always the case in Pandas, wherever we can pass a single string value to that keyword argument, we can instead pass a list of strings. We can thus specify our multi-index in this way:

```python
filename = '/Users/reuven/Downloads/ModalShare.csv'

df = pd.read_csv(filename, engine='pyarrow',
                index_col=['region', 'Country', 'City'])
```

The resulting data frame has 996 rows and 22 columns.

### How many distinct cities were represented in this research? Which 10 cities were surveyed the most times?

On the face of it, this doesn't seem like a particularly challenging question. But when you consider that city names repeat themselves, and that some cities were surveyed multiple times, it becomes a bit trickier.

Fortunately, the survey gives each city a unique ID number, which we can grab from the `CityID` column in the data frame. Counting the number of unique values of `CityID` will tell us how many distinct cities were included.

One option would be to take the `CityID` column, run [drop\_duplicates](https://www.bambooweekly.com/pandas-drop-duplicates/) on its elements, and then run the [count](https://www.bambooweekly.com/pandas-count/) method on the results:

```python
df['CityID'].drop_duplicates().count()
```

This gives us a result of 794 different cities. I worried that running two methods, `drop_duplicates` and then `count`, would take a while. (And yes, I realize that with such a small data set, nothing will really take "a while.") So I also considered using [value\_counts](https://www.bambooweekly.com/pandas-value-counts/) to find out how often each element appeared, then grabbing the length of the index with `len`:

```python
len(df['CityID'].value_counts().index)
```

Both techniques gave me the same result. But which one really ran faster? Or was there no real difference?

I used the [%timeit](https://ipython.readthedocs.io/en/stable/interactive/magics.html?ref=bambooweekly.com#magic-timeit) magic command in Jupyter to time both of them, and was quite surprised by the results:

- Using `drop_duplicates().count()` took an average of 153 µs
- Using `value_counts().index` and then running `len` took an average of 320 µs

So it turns out that my instinct for what would run faster was completely wrong; my initial preference took twice as long!

Next, I asked you which 10 cities were surveyed the most times. Here, it was pretty clear that I would want to use [value\_counts](https://www.bambooweekly.com/pandas-value-counts/). But what would I run `value_counts` on? And how would I then use the results?

I actually was able to run `value_counts` on the index itself. Index objects aren't exactly Pandas series objects, but they're not exactly *not* series objects, either. We can often run series methods on them, including (as is the case here) where we have a multi-index.

I thus ran `value_counts` on `df.index`. The result of `value_counts` is always a series in which the index contains the unique values, sorted (by default) in descending order of frequency. I can thus grab the index of that set of results to find which cities were most (and least) popular. By running [head](https://www.bambooweekly.com/pandas-head/) on the series, I can even get the 10 most commonly referred to cities:

```python
(
    df
    .index
    .value_counts()
    .head(10)
    .index
)
```

Now, the output isn't beautiful; we get back a `MultiIndex` object. But we can read it pretty easily, and if you squint just right, you can pretend that it's a list of tuples. Which, of course, it basically is:

```python
MultiIndex([('Europe',        'Austria',        'Graz'),
            ('Europe',        'Austria',      'Vienna'),
            ('Europe',        'Germany',     'Leipzig'),
            ('Europe',        'Germany',      'Erfurt'),
            ('Europe',        'Germany',  'Dusseldorf'),
            ('Europe',         'Norway',        'Oslo'),
            ('Europe',        'Germany',    'Hannover'),
            ('Europe',         'Norway', 'Fredrikstad'),
            ('Europe', 'United Kingdom',      'London'),
            ('Europe',        'Germany',   'Karlsruhe')],
           names=['region', 'Country', 'City'])
```

We can see that cities in Austria and Germany were surveyed more often than in other countries, and that Graz and Vienna (both in Austria) were the most-surveyed cities. I should note that the authors of the ABC paper didn't conduct these studies themselves; rather, they collected a huge number of other studies, conducted by other people. So we can't really blame (or praise) them for the selection of cities; they cast a very wide net, and it turns out that there was just more data for these cities (and countries) than others.

### Show the values for ABC (Active, Bus, and Car) for all cities in G7 countries (US, UK, France, Germany, Canada, Italy, and Japan). Retain the full three-part index (region, country, city).

If our data frame's index only consisted of the `Country` column, then it wouldn't be that hard to extract the rows containing certain country names. But here, things are a bit trickier.

One option would be to use [reset\_index](https://www.bambooweekly.com/pandas-reset-index/) to move the `region` and `city` columns back to the data frame, and out of the index. But that seems a bit clunky, especially since I asked you to keep the multi-index as is.

What we would basically want is to say:

- We want all regions
- We only want countries in the G7
- We want all cities

When we're using a multi-index, this kind of thing is most easily accomplished with an [IndexSlice](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.IndexSlice.html?ref=bambooweekly.com) object. We invoke `pd.IndexSlice`, specifying what value or values we want from each level of our index. We then pass that to `df.loc`, and get only those rows.

But wait: How can we indicate that we want *all* of the values from `region` and `city`? If we were using a regular Python slice, we could use `:` to do so. But that's only possible, according to Python syntax, if we use `[]` rather than `()`. Well, that's why `.loc` uses `[]` rather than `()` – and `IndexSlice` follows in that tradition, using `[]` so that we can use one or more `:` characters.

I first defined a list of G7 countries, since we'll be using it later:

```python
g7_countries = ['France', 'Germany', 'United States', 
                'United Kingdom', 'Canada', 'Japan', 'Italy']

```

Then I used `pd.IndexSlice` to ask for all regions, G7 countries, and all cities. Notice how I pass these three arguments inside of the `[]` for `pd.IndexSlice`. I then use the resulting `IndexSlice` object as the argument to [df.loc](https://www.bambooweekly.com/pandas-loc/):

```python
df.loc[pd.IndexSlice[:, g7_countries, :]]
```

However, the above gives us all of the columns, and I asked you to provide only the `Active`, `Bus`, and `Car` columns. Fortunately, `df.loc` takes two arguments – the first is the row selector, and the second is the column selector.

If we pass a list of column names as the second argument, our result will contain only those rows we asked for (i.e., in G7 countries) and only those columns we specify:

```python
df.loc[pd.IndexSlice[:, g7_countries, :],
    ['Active', 'Bus', 'Car']]
```

The resulting data frame has 630 rows and 3 columns. Given that the overall data frame has 996 rows, we can see that G7 countries were a pretty large proportion of those included in the survey and research.

### Calculate the mean ABC values for each G7 country. Create a stacked bar plot for each country, showing the relative makeup of each kind of travel, sorted by the proportion of car usage.

To calculate the mean ABC value for each G7 country, we'll first need to (again) keep only those rows from the original data frame for G7 countries:

```python
(
    df
    .loc[pd.IndexSlice[:, g7_countries], :]
)
```

With that data frame in hand, we can now run a [groupby](https://www.bambooweekly.com/pandas-groupby/) operation. Basically:

- We want one result row for each unique value of `Country`
- We want one result column for each of `Active`, `Bus`, and `Car`
- We want to run the [mean](https://www.bambooweekly.com/pandas-mean/) method on each of the three columns, for each unique value of `Country`.

We can do this by running `groupby`, then by specifying multiple columns in the square brackets, and then invoking `mean` on them:

```python
(
    df
    .loc[pd.IndexSlice[:, g7_countries], :]
    .groupby('Country')[['Active', 'Bus', 'Car']].mean()
)
```

The result is a data frame with seven rows (one for each G7 country) and three columns (one for each we asked for). The values indicate what percentage, on average, each country's surveyed citizens use each modality to get to work:

```
                  Active       Bus       Car
Country                                     
Canada          0.081678  0.153980  0.764342
France          0.297288  0.106188  0.596524
Germany         0.376980  0.156931  0.466088
Italy           0.245816  0.157314  0.596870
Japan           0.315000  0.560000  0.125000
United Kingdom  0.225335  0.212506  0.562158
United States   0.031399  0.010876  0.957731
```

We can see that the `Active` numbers are very low in the US and Canada, but pretty high in a number of other countries. Car usage, by contrast, is very high in the US and Canada, but relatively low in European cities, and extremely low in Japan.

I asked you to visualize this, creating a stacked bar plot. First, I asked you to sort the data frame by `Car` values. We can do that by invoking [sort\_values](https://www.bambooweekly.com/pandas-sort-values/), specifying that I want to sort by the `Car` column:

```python
(
    df
    .loc[pd.IndexSlice[:, g7_countries], :]
    .groupby('Country')[['Active', 'Bus', 'Car']].mean()
    .sort_values('Car')
)
```

I then invoked [plot.bar](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.plot.bar.html?ref=bambooweekly.com), which would normally give us one cluster of bars per row, with each bar in the cluster representing one column. But here, I passed `stacked=True`, which ensures that each cluster is combined together:

```python
(
    df
    .loc[pd.IndexSlice[:, g7_countries], :]
    .groupby('Country')[['Active', 'Bus', 'Car']].mean()
    .sort_values('Car')
    .plot.bar(stacked=True)
)
```

Here's the result of my plot:

![](https://storage.ghost.io/c/06/ba/06ba0cc0-be6f-4de7-af2f-5c20165279b9/content/images/2024/06/data-src-image-3dcebfed-7815-4697-aab1-8707e9db2284.png)

You can see that the plot is definitely sorted, from smallest to largest, by car usage. And when we compare what proportion of Japanese citizens commute to work by car vs. the US, the difference is rather stark. We see that every country (and again, especially Japan) has many more users of public transportation than the United States, with a surprising (to me, at least) number of people in cities outside of North America walking or biking to work.

### Create a data frame with the countries in the rows and both mean and median A, B, and C score for each country in the columns. Sort this data frame by median car use. What are the 10 top car-using countries?

Now, we want to do something a bit different, having countries in the index (i.e., just a regular, one-dimensional index) but have the columns contain *both* median and mean usage for the A, B, and C scores. How can we do that?

The first thing to realize is that both the index and columns in a data frame are defined by "index" objects. As such, both can be single-value (which is the norm) or multi-valued. That's right: The index (rows) can be a multi-index, with more than one dimension, and the columns can *also* be a multi-index, with more than one dimension.

That's fine, at least theoretically, but how can we get to that?

The answer is the [agg](https://www.bambooweekly.com/pandas-agg/) method, which allows us to pass a list of strings, with each string naming an aggregation method we wish to run. So we can run `mean` or `median`, or we can run `agg(['mean', 'median']`:

```python
(
    df
    .groupby('Country')[['Active', 'Bus', 'Car']]
    .agg(['mean', 'median'])
)
```

This is great, and gives us a new data frame with a multi-index on our columns. The outer level has names `Active`, `Bus`, and `Car`, as we would expect. But then there's an inner layer, which alternates between `mean` and `median`. We thus have `Active`+`mean`, `Active`+`median`, `Bus`+`mean`, `Bus`+`median`, and `Car`+`mean` and `Car`+`median`. We have a total of six columns, but we can retrieve any two by specifying the outer layer with `df['Active']`, `df['Bus']`, or `df['Car']`.

But I asked you to sort by median car use, which means the single column `median` within the `df['Car']` column. How can we specify that?

The answer is: A Python tuple. We can use tuples to specify and work with a multi-index, and that's true for rows and also for columns. If I pass the tuple `('Car', 'median')` as the column on which I want to sort, it'll work just fine. I can then sort in descending order (`ascending=False`) and use `head(10)` to get the first 10 values:

```python
(
    df
    .groupby('Country')[['Active', 'Bus', 'Car']]
    .agg(['mean', 'median'])
    .sort_values(('Car', 'median'), ascending=False)
    .head(10)
)  
```

The results:

```
                 Active                 Bus                 Car          
                   mean    median      mean    median      mean    median
Country                                                                  
United States  0.031399  0.025442  0.010876  0.006486  0.957731  0.966667
New Zealand    0.139783  0.101010  0.136277  0.121212  0.723941  0.818182
Australia      0.086549  0.059406  0.172051  0.140000  0.741400  0.810000
Canada         0.081678  0.070707  0.153980  0.140000  0.764342  0.787879
Ireland        0.205943  0.220000  0.115471  0.070000  0.678586  0.710000
South Africa   0.090000  0.090000  0.260000  0.260000  0.650000  0.650000
Norway         0.277962  0.280000  0.100437  0.080000  0.621601  0.650000
Portugal       0.197203  0.190960  0.198552  0.175000  0.604245  0.650000
Taiwan         0.134505  0.134505  0.249655  0.249655  0.615840  0.615840
Italy          0.245816  0.207920  0.157314  0.131310  0.596870  0.613860
```

Median car use in the United States is 96.6 percent. The #10 country, Italy, only has a median car rate of 61 percent. That's quite a gap, but it fits with my experience in the US.

### Calculate the correlation between population and each of the A, B, and C scores. Do we see anything large or dramatic? (To be fair, the researchers on this paper has a more nuanced and complex set of calculations for this sort of correlation.)

In Pandas, we can find the correlation among columns by invoking the [corr](https://www.bambooweekly.com/pandas-corr/) method. This returns a data frame containing floats, where -1 indicates 100% negative correlation, 0 indicates no correlation, and +1 indicates 100% positive correlation.

The rows and the columns of the returned data frame are identical, allowing us to see each correlation twice. Along the diagonal, all of the correlations are 1.0, because by definition, a column is 100% positively correlated with itself.

However, we aren't interested in finding correlations among all of the columns; we only need to look at the ABC columns, plus `population`. We can thus start by choosing only those columns with double square brackets, and only then invoking `corr`:

```python
(
    df
    [['Active', 'Bus', 'Car', 'population']]
    .corr()
)
```

Since we want to find out how each column correlates with `population`, we can then just select that column:

```python
(
    df
    [['Active', 'Bus', 'Car', 'population']]
    .corr()
    ['population']
)
```

Here's what I got:

```
Active        0.046704
Bus           0.364077
Car          -0.220949
population    1.000000
Name: population, dtype: float64
```

What this means is:

- As the population rises, there's a very small positive correlation with active (walking and biking) commutes. In other words, people are pretty much equally active in areas with large and small populations alike.
- As the population rises, there's a semi-strong positive correlation with use of public transportation. Which makes sense; only in areas with large populations are we going to see public transportation
- Finally, car use is *negatively* correlated with population growth, albeit not super strongly. So as the population rises, we'll see less car usage. Clearly, that depends to a great degree on the country and city; that's very true for New York and Paris, but not so much for Houston and Phoenix.

### Create a scatter plot showing the ABC scores (on the y axis) vs. population (on the x axis). Have each of A, B, and C be displayed in a different color. Do we see any large, obvious correlations in this plot?

In the previous question, we looked at the correlation numbers. But we can visualize a correlation (or lack thereof) with a scatter plot. Here, I asked you to create a scatter plot that has the ABC scores (of all types) on the y axis, but to separate A, B, and C by color so that we can distinguish them. The x axis will be the population, such that smaller cities will be on the left and larger ones will be on the right.

To do this, we'll first need to get all three A, B, and C scores into a single column. In other words, we'll want three rows for each country, one with the A score, one with the B score, and one with the C score. We can do that by running a `groupby` on the `Country` column, getting the mean for all three ABC columns:

```python
(
    df
    .groupby('Country')[['Active', 'Bus', 'Car']].mean()
)
```

This gives us a row for each country, but three columns. How can we make them into a single column? We can do some Pandas magic, first turning those columns into part of the index with [stack](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.stack.html?ref=bambooweekly.com), then turning them into a regular column with `reset_index`, specifying that we only want to reset the inner index: 

```python
(
    df
    .groupby('Country')[['Active', 'Bus', 'Car']].mean()
    .stack()
    .reset_index(level=1)
)
```

It's annoying that our columns are now named `level_1` and `0`, but I decided not to do anything about that for now, since we'll be messing with the names soon enough.

Indeed: Because we need numbers for a scatter plot, I asked Pandas to create a new column, `abc`, turning the `level_1` column into categories. We normally think of category dtypes as being for more efficient string usage, but they're really like enums, meaning strings that represent integers. We can thus use a category column in our scatter plot. I do this with a combination of [assign](https://www.bambooweekly.com/pandas-assign/) and [lambda](https://docs.python.org/3/tutorial/controlflow.html?highlight=lambda&ref=bambooweekly.com):

```python

(
    df
    .groupby('Country')[['Active', 'Bus', 'Car']].mean()
    .stack()
    .reset_index(level=1)
    .assign(abc=lambda df_: df_['level_1'].astype('category'))
)    
```

Next, I get rid of the `level_1` column, which we no longer need:

```python
(
    df
    .groupby('Country')[['Active', 'Bus', 'Car']].mean()
    .stack()
    .reset_index(level=1)
    .assign(abc=lambda df_: df_['level_1'].astype('category'))
    .drop('level_1', axis='columns')
)    
```

It's great that we have the ABC data in a single column. But what about population? For that, we'll need to [join](https://www.bambooweekly.com/pandas-join/) with... the original data frame, but with (a) `country` as its index and (b) just the `Country` column. This is not exactly a "self-join", but it's not too far away from one:

```python3
(
    df
    .groupby('Country')[['Active', 'Bus', 'Car']].mean()
    .stack()
    .reset_index(level=1)
    .assign(abc=lambda df_: df_['level_1'].astype('category'))
    .drop('level_1', axis='columns')
    .join(df
          .reset_index()
          .set_index('Country')
          ['population'])
)
```

The result is a data frame in which each country (in the index) appears three times, as does the population (as a column), But column `0` contains the number for A, B, or C, and the `abc` column tells us which is which.

All that's left is to create the plot:

```python3
(
    df
    .groupby('Country')[['Active', 'Bus', 'Car']].mean()
    .stack()
    .reset_index(level=1)
    .assign(abc=lambda df_: df_['level_1'].astype('category'))
    .drop('level_1', axis='columns')
    .join(df
          .reset_index()
          .set_index('Country')
          ['population'])
    .plot.scatter(x='population', y=0, c='abc', colormap='Spectral')
)
```

I told [plot.scatter](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.plot.scatter.html?ref=bambooweekly.com) that it should use the `Spectral` colormap, plotting the `0` column vs. the `population` column, and that it should use the value in `abc` to determine the color. The result looks like this:

![](https://storage.ghost.io/c/06/ba/06ba0cc0-be6f-4de7-af2f-5c20165279b9/content/images/2024/06/data-src-image-ae0634df-5c28-4186-95c0-cd56fbce10ec.png)

It's a bit ugly, and I can't say that it gives me any tremendous insights. We see the many purple (car) lines for low-population areas in the top left. And we see fewer purple dots up high (i.e., high usage) as populations get larger. The visualizations on the researchers' site are far nicer and more useful than this one, in no small part because they're using better correlations than just a straight comparison with population.

That's it for this week! A link to my Jupyter notebook is here: [https://drive.google.com/file/d/1hjSKo788BdhZB0s3MTZjZ37XfJ4CdsSH/view?usp=sharing](https://drive.google.com/file/d/1hjSKo788BdhZB0s3MTZjZ37XfJ4CdsSH/view?usp=sharing&ref=bambooweekly.com)

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

Reuven