Pandas Extract Column Value Based on Another Column

Use pandas.DataFrame.query() to get a column value based on another column. Besides this method, you can also use DataFrame.loc[], DataFrame.iloc[], and DataFrame.values[] methods to select column values based on another column of pandas DataFrame.

In this article, I will explain how to extract column values based on another column of pandas DataFrame using different ways, these can be used to create conditional columns on padas DataFrame.

1. Quick Examples To Extract Column Value Based on Another Column

If you are in a hurry, below are some quick examples of how to extract column values based on another column of pandas DataFrame.

# Example 1: Below are some quick examples.

# Example 2: Extract column values by using DataFrame.loc[] property.
df2=df.loc[df['Fee'] == 30000, 'Courses']

# Example 3: To get First Element by using .iloc[] method.
df2=df.loc[df['Fee'] == 30000, 'Courses'].iloc[0]

# Example 4: Extract column values by DataFrame.item() method
df2=df.loc[df['Fee'] == 30000, 'Courses'].item()

# Example 5: Using DataFrame.query() method extract column values.
df2=df.query('Fee==25000')['Courses']

# Example 6: Using DataFrame.values() property.
df2=df[df['Fee']==22000]['Courses'].values[0]

# Example 7: Other example.
df2=df[df['Fee']==22000]['Courses']

Now, let’s create a Pandas DataFrame with a few rows and columns and execute the above examples. Our DataFrame contains column names Courses, Fee, Duration, and Discount.

# Create Pandas DataFrame.
import pandas as pd
import numpy as np
technologies = {
    'Courses':["Spark","PySpark","Python","pandas"],
    'Fee' :[20000,25000,22000,30000],
    'Duration':['30days','40days','35days','50days'],
    'Discount':[1000,2300,1200,2000]
              }
index_labels=['r1','r2','r3','r4']
df = pd.DataFrame(technologies,index=index_labels)
print("Create DataFrame:\n", df)

Yields below output.

2. Using DataFrame.query() – Extract Column Value of Pandas

You can extract a column of pandas DataFrame based on another value by using the DataFrame.query() method. The query() is used to query the columns of a DataFrame with a boolean expression. The blow example returns a Courses column where the Fee column value matches with 25000.

# Using DataFrame.query() method extract column values.
df2=df.query('Fee == 25000')['Courses']
print("Extract column value based on another column:\n", df2)

Yields below output.

3. Extract Column Values by Using DataFrame.loc[] Property

You can also select column values based on another DataFrame column value by using DataFrame.loc[] property. The .loc[] property explains how to access a group of rows and columns by label(s) or a boolean array. Here, the condition can just be selecting rows and columns, but it can also be used to filter DataFrames. These filtered DataFrame can have values applied to them.

# Extract column values by using DataFrame.loc[] property.
df2=df.loc[df['Fee'] == 30000, 'Courses']
print("Extract column value based on another column:\n", df2)

Yields below output.

# Output:
# Extract column value based on another column:
r4    pandas
Name: Courses, dtype: object

Alternatively, you can use .loc[] method to get a series that satisfies your condition and the .iloc[] method to get the first element.

# To get First Element by using .iloc[] method.
df2=df.loc[df['Fee'] == 30000, 'Courses'].iloc[0]
print("Extract column value based on another column:\n", df2)

# Output:
# Extract column value based on another column:
# pandas

Another method is to extract columns of pandas DataFrame based on another column by using DataFrame.item() method.

# Extract column values by DataFrame.item() method
df2=df.loc[df['Fee'] == 30000, 'Courses'].item()
print("Extract column value based on another column:\n", df2)

# Output:
# Extract column value based on another column:
# pandas

4. Using DataFrame.Values()

In this section, with the help of DataFrame.value() property, you can extract column values of pandas DataFrame based on another column. The value() property is used to get a Numpy representation of the DataFrame. Only the values in the DataFrame will be returned, the axes labels will be removed. You can put [0] at the end to access the value.

# Using DataFrame.values() property.
df2=df[df['Fee']==22000]['Courses'].values[0]
print("Extract column value based on another column:\n", df2)

# Output:
# Extract column value based on another column:
# Python

5. Other Example

Another simple method to extract values of pandas DataFrame based on another value.

# Other example.
df2=df[df['Fee']==22000]['Courses']
print("Extract column value based on another column:\n", df2)

# Output:
# Extract column value based on another column:
# r3    Python
# Name: Courses, dtype: object

6. Complete Example – Extract Column Value Based on Another Column

# Complete examples to extract column values based another column.
# Create Pandas DataFrame.
import pandas as pd
import numpy as np
technologies = {
    'Courses':["Spark","PySpark","Python","pandas"],
    'Fee' :[20000,25000,22000,30000],
    'Duration':['30days','40days','35days','50days'],
    'Discount':[1000,2300,1200,2000]
              }
index_labels=['r1','r2','r3','r4']
df = pd.DataFrame(technologies,index=index_labels)
print(df)

# Extract column values by using DataFrame.loc[] property.
df2=df.loc[df['Fee'] == 30000, 'Courses']
print(df2)

# To get First Element by using .iloc[] method.
df2=df.loc[df['Fee'] == 30000, 'Courses'].iloc[0]
print(df2)

# Extract column values by DataFrame.item() method
df2=df.loc[df['Fee'] == 30000, 'Courses'].item()
print(df2)

# Using DataFrame.query() method extract column values.
df2=df.query('Fee==25000')['Courses']
print(df2)

# Using DataFrame.values() property.
df2=df[df['Fee']==22000]['Courses'].values[0]
print(df2)

# Other example.
df2=df[df['Fee']==22000]['Courses']
print(df2)

Conclusion

In this article, you have learned how to extract column values of pandas DataFrame based on another column by using DataFrame.loc[], DataFrame.iloc[], DataFrame.query(), DataFrame.values[] methods with simple examples.

Related Articles

• Select Pandas DataFrame Columns by Label or Index
• How to Merge Series into Pandas DataFrame
• Create Pandas DataFrame From Multiple Series
• Drop Infinite Values From Pandas DataFrame
• Drop Rows From Pandas DataFrame Examples
• Pandas Extract Year from Datetime
• Pandas Extract Month and Year from Datetime
• Get First N Rows of Pandas DataFrame
• Add a column based on another column

References

• https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.values.html