Querying data

Querying data from a DataFrame

Let’s now review how we can access data contained in the DataFrame. This process, known as indexing, consists in specifying a row or a column (or ranges of rows and columns) where the data is stored. In pandas, there are two different ways to do that:

By label: data is queried using the actual index/column name (e.g., the VEI column in the DataFrame above)
By location: data is queried using the column location (e.g., the 3rd row)

Label-based indexing

Figure 1: Label-based queries using `.loc`. (doc)

Querying rows

When we know the exact label of the row or the column, we can use the .loc (doc) function to query the DataFrame (Figure 1). Let’s start by querying specific rows. We have previously defined the Name column as the index (i.e., row label), which means that we can simply pass the name of the volcano.

Function reference: loc (doc).

df.loc['Calbuco']

1: Get the row for “Calbuco” volcano

Country                    Chile
Date         2015-04-22 00:00:00
VEI                            4
Latitude                -41.2972
Longitude               -72.6097
Name: Calbuco, dtype: object

Note that the result is a Series (i.e., a 1-dimensional DataFrame where the columns become the index), not a DataFrame. If we want to keep it as a DataFrame, we can use double brackets. Double brackets can also be used to query multiple rows.

Function reference: loc (doc).

df.loc[['Calbuco']]
df.loc[['Calbuco', 'Taal']]

1: Query one row and return it as a DataFrame
2: Query multiple rows

	Country	Date	VEI	Latitude	Longitude
Name
Calbuco	Chile	2015-04-22	4	-41.2972	-72.6097
Taal	Philippines	2020-01-12	4	14.0020	120.9934

Question

What is the VEI recorded for Etna volcano?

What is the eruption date for Taal volcano?

Double brackets

In general, double brackets [[ ]] will return a DataFrame and not a Series. We will dominantly use this in the following examples.

Querying columns

Let’s now query specific columns. For example, querying the VEI column can be achieved in different ways. The simplest is to use the column name directly. We can also query multiple columns using double brackets

df[['VEI']]
df[['Country', 'VEI']]

1: Get the VEI column
2: Query multiple columns

	Country	VEI
Name
St. Helens	USA	5
Pinatubo	Philippines	6
El Chichón	Mexico	5
Galunggung	Indonesia	4
Nevado del Ruiz	Colombia	3
Merapi	Indonesia	2
Ontake	Japan	2
Soufrière Hills	Montserrat	3
Etna	Italy	2
Nyiragongo	DR Congo	1
Kīlauea	USA	2
Agung	Indonesia	3
Tavurvur	Papua New Guinea	3
Sinabung	Indonesia	3
Taal	Philippines	4
La Soufrière	Saint Vincent	4
Calbuco	Chile	4
St. Augustine	USA	3
Eyjafjallajökull	Iceland	4
Cleveland	USA	3

Until now, we have selected either rows or columns. With .loc (doc), we can select both in one expression:

Function reference: loc (doc).

df.loc[['Calbuco', 'Taal'], ['Country', 'VEI']]

	Country	VEI
Name
Calbuco	Chile	4
Taal	Philippines	4

The general pattern is:

Function reference: loc (doc).

df.loc[row_selection, column_selection]

The selection before the comma specifies the rows; the selection after the comma specifies the columns.

To keep all rows and select only two columns, use : for the rows:

Function reference: loc (doc).

df.loc[:, ['Country', 'VEI']]

	Country	VEI
Name
St. Helens	USA	5
Pinatubo	Philippines	6
El Chichón	Mexico	5
Galunggung	Indonesia	4
Nevado del Ruiz	Colombia	3
Merapi	Indonesia	2
Ontake	Japan	2
Soufrière Hills	Montserrat	3
Etna	Italy	2
Nyiragongo	DR Congo	1
Kīlauea	USA	2
Agung	Indonesia	3
Tavurvur	Papua New Guinea	3
Sinabung	Indonesia	3
Taal	Philippines	4
La Soufrière	Saint Vincent	4
Calbuco	Chile	4
St. Augustine	USA	3
Eyjafjallajökull	Iceland	4
Cleveland	USA	3

For the selections above, this produces the same result as:

df[['Country', 'VEI']]

	Country	VEI
Name
St. Helens	USA	5
Pinatubo	Philippines	6
El Chichón	Mexico	5
Galunggung	Indonesia	4
Nevado del Ruiz	Colombia	3
Merapi	Indonesia	2
Ontake	Japan	2
Soufrière Hills	Montserrat	3
Etna	Italy	2
Nyiragongo	DR Congo	1
Kīlauea	USA	2
Agung	Indonesia	3
Tavurvur	Papua New Guinea	3
Sinabung	Indonesia	3
Taal	Philippines	4
La Soufrière	Saint Vincent	4
Calbuco	Chile	4
St. Augustine	USA	3
Eyjafjallajökull	Iceland	4
Cleveland	USA	3

Position-based indexing

Caution 1: Indexing in Python

Remember that in Python, indexing starts from 0 - so the first row or column has an index of 0.

Figure 2: Position-based queries using `.iloc`. (doc)

Some situations require querying data by location instead of label - let’s say for instance we need to retrieve rows 10-20. This is done using the .iloc (doc) function (instead of the .loc (doc) function previously used; Figure 2). Remember that Python uses zero-based indexing (Caution 1), meaning that the first element is at position 0, the second at position 1, and so on.

The next example queries the first row - using again double brackets to return a DataFrame.