Pandas: Extract Column Value Based on Another Column

You can use the query() function in pandas to extract the value in one column based on the value in another column.

This function uses the following basic syntax:

df.query("team=='A'")["points"]

This particular example will extract each value in the points column where the team column is equal to A.

The following examples show how to use this syntax in practice with the following pandas DataFrame:

import pandas as pd

#create DataFrame
df = pd.DataFrame({'team': ['A', 'A', 'A', 'A', 'B', 'B', 'B', 'B'],
                   'position': ['G', 'G', 'F', 'F', 'G', 'G', 'F', 'F'],
                   'points': [11, 28, 10, 26, 6, 25, 29, 12]})

#view DataFrame
print(df)

  team position  points
0    A        G      11
1    A        G      28
2    A        F      10
3    A        F      26
4    B        G       6
5    B        G      25
6    B        F      29
7    B        F      12

Example 1: Extract Column Values Based on One Condition Being Met

The following code shows how to extract each value in the points column where the value in the team column is equal to ‘A’:

#extract each value in points column where team is equal to 'A'
df.query("team=='A'")["points"]

0    11
1    28
2    10
3    26
Name: points, dtype: int64

This function returns all four values in the points column where the corresponding value in the team column is equal to ‘A’.

Example 2: Extract Column Values Based on One of Several Conditions Being Met

The following code shows how to extract each value in the points column where the value in the team column is equal to ‘A’ or the value in the position column is equal to ‘G’:

#extract each value in points column where team is 'A' or position is 'G'
df.query("team=='A' | position=='G'")["points"]

0    11
1    28
2    10
3    26
4     6
5    25
Name: points, dtype: int64

This function returns all six values in the points column where the corresponding value in the team column is equal to ‘A’ or the value in the position column is equal to ‘G’.

Example 3: Extract Column Values Based on Several Conditions Being Met

The following code shows how to extract each value in the points column where the value in the team column is equal to ‘A’ and the value in the position column is equal to ‘G’:

#extract each value in points column where team is 'A' and position is 'G'
df.query("team=='A' & position=='G'")["points"]

0    11
1    28
Name: points, dtype: int64

This function returns the two values in the points column where the corresponding value in the team column is equal to ‘A’ and the value in the position column is equal to ‘G’.

Additional Resources

The following tutorials explain how to perform other common tasks in pandas:

Pandas: How to Select Columns Based on Condition
Pandas: Drop Rows Based on Multiple Conditions
Pandas: Update Column Values Based on Another DataFrame