6.6 Indexing in Pandas

Way back in the introduction to data frames, we covered indexing into data frames with Boolean vectors, such as Pandas Series or Numpy arrays, and indexing into data frames with column names.

This page covers more sophisticated indexing in Pandas.

To understand this indexing, we first need to cover the index of data frames and Series.

We start with our usual imports:

# Load the Numpy library, rename to "np"
import numpy as np

# Load the Pandas data science library, rename to "pd"
import pandas as pd

The index

We return to our first data frame, containing statistics on development and gender from the World Bank.

This is the gender_stats.csv file. See the gender stats data page for some more detail.

Download that file to the same directory as this notebook, if you are running on your own computer.

# Load the data file as a data frame.
gender_data = pd.read_csv('gender_stats.csv')

# Show the first 10 rows
gender_data.head(10)

As you know, the names at the top, in bold typeface, are the names of the columns. We can see these names using the columns attribute of the data frame:

gender_data.columns

Index(['country_name', 'country_code', 'fert_rate', 'gdp_us_billion',
       'health_exp_per_cap', 'health_exp_pub', 'prim_ed_girls',
       'mat_mort_ratio', 'population'],
      dtype='object')

These can also be called the column labels.

We have not paid attention so far to the numbers at the left of the table display, also in bold typeface.

In this case, they are ascending integers, starting at 0, with value 0 for the first row, 1 for the second row, and so on.

These numbers are the index of the data frame. The index contains the row labels.

We can see the row labels / index with the index attribute of the data frame.

gender_data.index

RangeIndex(start=0, stop=216, step=1)

This is saying we have a simple index, that is like a np.arange, starting at 0, and continuing in steps of 1 up to, but not including 216.

So far, the index looks like row numbers, with 0 for the first row, 1 for the second row. We will soon see that the index is more than that.

For example, let us look at the last 15 rows. We can get these with the tail method of the data frame.

last_15 = gender_data.tail(15)
last_15

Here is the index of this new data frame.

last_15.index

RangeIndex(start=201, stop=216, step=1)

Notice that the first row of this new data frame no longer has the label 0 - it has label 201. These numbers are the labels from the original data frame, from which we selected the rows.

Notice too that the second row in this table is the USA, and it has the label \202.

Now imagine that I sort the original data frame by Gross Domestic Product, and select the top 15 rows. By doing this, I select the top 15 richest countries.

# Sort by GDP.
df_by_gdp = gender_data.sort_values('gdp_us_billion', ascending=False)
# Take the top 15 rows.
richest_15 = df_by_gdp.head(15)
richest_15

Now notice that the index values have followed their respective rows. The row for the USA is now first, but it has kept its original index value (row label) - 202. The index value identifies the row.

Here is the index for this small table:

richest_15.index

Int64Index([202, 35, 97, 49, 67, 63, 26, 88, 94, 164, 32, 10, 104, 58, 124], dtype='int64')

Now let us say that I wanted to get the eleventh row of this table.

I would be tempted to do something like richest_15[10] - but then - what would Pandas give me? Does 10 mean the row label for “Australia”? Or does it mean the eleventh row of the table, as it would for an array, and therefore the row for “Canada”? That is, will the indexing using the labels (index values)? Or will it use the positions (as for indexing in Numpy arrays)?

The answer is, it is ambiguous, and when things are ambiguous, Pandas has to guess. That’s a bad idea, because then we have to guess what Pandas has guessed, and it is starting to get really confusing.

For that reason, we careful Pandas users do not index rows with code like richest_15[10]. When we are not using Booleans to select rows, we always use one of the two indexing attributes that Pandas provides. Each does a different type of indexing.

• The loc (label locate) attribute indexes using the labels (row index).
• The iloc (position locate) attribute indexes using the positions.

Here they are in action:

# Get the row with label 10
richest_15.loc[10]

country_name          Australia
country_code                AUS
fert_rate                1.8615
gdp_us_billion          1422.99
health_exp_per_cap      4256.06
health_exp_pub          6.29238
prim_ed_girls           48.5767
mat_mort_ratio                6
population              23.4446
Name: 10, dtype: object

# Get the row at position / offset 10
richest_15.iloc[10]

country_name           Canada
country_code              CAN
fert_rate              1.6003
gdp_us_billion        1708.47
health_exp_per_cap    4616.54
health_exp_pub        7.54625
prim_ed_girls         48.8089
mat_mort_ratio           7.25
population            35.5171
Name: 32, dtype: object

In more details, what is happening in the first cell above is that richest_15.loc gives us a special value (AKA object) that:

• knows about the richest_15 data frame;
• will give us rows and columns, if we use [ something ] on it.

So, read richest_15.loc[10] as:

• Get the richest_15 value (the data frame);
• Get the loc attribute of this data frame. This evaluates to a special object (value) that can give us rows and columns from the data frame, by looking at labels;
• Use this object to fetch the row labeled 10, by using indexing ([10])

Likewise, read richest_15.iloc[10] as:

• Get the richest_15 value (the data frame);
• Get the iloc attribute of this data frame. This evaluates to a special object (value) that can give us rows and columns from the data frame, by looking at positions (offsets);
• Use this object to fetch the row at offset 10, by using indexing ([10]).

When you select rows and columns from a data frame, consider carefully what you want.

Do you want to select rows and columns by label (by index, in the rows). Then you want .loc.

Do you want to select rows and columns by position (offset). Then you want .iloc.

Loc and iloc indexing work on columns too

Consider this. We use .loc and the label for the row, followed by the label for the column.

richest_15.loc[10, 'gdp_us_billion']

1422.994116449394

This selects the row labeled 10 (the USA row) and returns the value from the gdp_us_billion column.

Conversely, iloc only understands positions, and so, integers. It does not understand or use labels:

# We can't specify columns by label, using iloc.
richest_15.iloc[10, 'gdp_us_billion']

ValueError                                Traceback (most recent call last)
   ...
ValueError: Location based indexing can only have [integer, integer slice (START point is INCLUDED, END point is EXCLUDED), listlike of integers, boolean array] types

Notice the somewhat informative error right at the bottom of the error message.

iloc can get columns by their position. For example, fertility_rate is the third column, and so we can get the fertility rate by asking iloc for the eleventh row, third column, like so:

richest_15.iloc[10, 2]

1.6002999999999998

This is the value from the row at offset 10 and the column at offset 2.

Series have an index too

Data frames have columns (and column labels), and rows (with row labels — the index).

Series also have element labels — they also have an index.

Consider this column, of the fertility rates of the richest countries:

# Get the fert_rate column from the data frame
rich_ferts = richest_15['fert_rate']
rich_ferts.head()

202    1.860875
35     1.558750
97     1.430000
49     1.450000
67     1.842500
Name: fert_rate, dtype: float64

As y’all know, this is a Series:

type(rich_ferts)

pandas.core.series.Series

You probably also spotted the numbers to the left of the values. Yes, the row labels have come across from the data frame into the Series. We can see that the first element has row label 202, and we remember that this is the row label (index value) for the USA.

As for the data frames, here is the index for the series:

rich_ferts.index

Int64Index([202, 35, 97, 49, 67, 63, 26, 88, 94, 164, 32, 10, 104, 58, 124], dtype='int64')

Like data frames, we can index by label, with loc, and by position, with iloc:

# The value for row labeled 10 - Australia.
rich_ferts.loc[10]

1.8615

# The value for the row at position 10 - Canada
rich_ferts.iloc[10]

1.6002999999999998

Rows from data frames are Series

Here we select the row at position 0 (the first row):

first_row = richest_15.iloc[0]
first_row

country_name          United States
country_code                    USA
fert_rate                   1.86088
gdp_us_billion              17369.1
health_exp_per_cap          9060.07
health_exp_pub              8.12196
prim_ed_girls               48.7588
mat_mort_ratio                   14
population                  318.558
Name: 202, dtype: object

This is a Series:

type(first_row)

pandas.core.series.Series

Notice that the Series has 9 elements, one for each column in the data frame.

As you remember, we can see the number of rows and columns in a data frame with:

richest_15.shape

(15, 9)

Remember, the columns have names, which we can also call labels.

When we select a row out of a data frame, we have one element per column, and the labels for the columns now become the labels for the rows.

first_row.index

Index(['country_name', 'country_code', 'fert_rate', 'gdp_us_billion',
       'health_exp_per_cap', 'health_exp_pub', 'prim_ed_girls',
       'mat_mort_ratio', 'population'],
      dtype='object')

As for any other Series, we can get elements by label, with loc:

first_row.loc['fert_rate']

1.860875

We can also get elements by position (offset), with iloc:

first_row.iloc[0]

'United States'

You can index without loc and iloc, but please don’t, except …

In the data frame introduction, you saw that we can index into data frame directly, using [ something ] after the data frame value, without using loc or iloc. Call this direct indexing. This is a very reasonable thing to do if:

• the something is a Boolean sequence. This is not ambiguous.
• the something is a column name. This isn’t ambiguous either.

We used both types of direct indexing in the data frame introduction page.

For example, both of these are entirely reasonable uses of direct indexing:

# Direct Boolean indexing:
# Select rows where 'health_exp_per_cap' is greater than 3000 dollars.
is_big_spender = richest_15['health_exp_per_cap'] > 3000  # Boolean Series
big_health_spenders = richest_15[is_big_spender]
big_health_spenders

# Direct indexing with column label:
# Select the 'country_code' column.
country_code = richest_15['country_code']
country_code

202    USA
35     CHN
97     JPN
49     DEU
67     GBR
63     FRA
26     BRA
88     IND
94     ITA
164    RUS
32     CAN
10     AUS
104    KOR
58     ESP
124    MEX
Name: country_code, dtype: object

Those uses of direct indexing are entirely sensible. Other uses of direct indexing are not sensible, and we strongly encourage you not to use them.

For example, Pandas will also allow you to this, with the Series we just made:

# Nooooooo !
country_code[10]

'AUS'

Please don’t do that. This is ambiguous. Did you really mean the country with label 10? Or did you mean the country at position 10? Pandas will guess for you. Don’t let it. If you are not selecting rows / elements with Booleans, always do this:

# By label
country_code.loc[10]

'AUS'

or this:

# By position
country_code.iloc[10]

'CAN'

You can use Boolean indexing with loc

It is often convenient to use direct indexing with Booleans, as we have just seen:

# Create a Boolean series with True for big spender rows, False otherwise.
is_big_spender = richest_15['health_exp_per_cap'] > 3000
is_big_spender

202     True
35     False
97      True
49      True
67      True
63      True
26     False
88     False
94      True
164    False
32      True
10      True
104    False
58     False
124    False
Name: health_exp_per_cap, dtype: bool

# Direct Boolean indexing:
# Select rows where 'health_exp_per_cap' is greater than 3000 dollars.
big_health_spenders = richest_15[is_big_spender]
big_health_spenders

We can also use Booleans with loc indexing. This allows us to do things such as selecting rows with Booleans, and columns with labels, in one shot, like this:

# Boolean indexing for rows, with "loc"
# Get "country_name" column values for big health spenders:
big_spender_names = richest_15.loc[is_big_spender, 'country_name']
big_spender_names

202     United States
97              Japan
49            Germany
67     United Kingdom
63             France
94              Italy
32             Canada
10          Australia
Name: country_name, dtype: object

In fact you can use some Boolean sequences with iloc as well, but that needs a little more explanation.