Pandas Extract Column Value Based on Another Column

  • Post author:
  • Post category:Pandas
  • Post last modified:December 2, 2024
  • Reading time:15 mins read
You are currently viewing 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.

Advertisements

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.

Key Points –

  • Access and update specific DataFrame rows or columns based on conditions using loc[] for label-based indexing and iloc[] for position-based indexing.
  • Use .query() to write SQL-like queries on DataFrames for conditional extraction of column values.
  • Use logical operators (&, |, ~) to apply multiple conditions when extracting values from a DataFrame.
  • Apply conditional logic to DataFrame columns to extract or assign values based on a condition.
  • Pandas allows combining multiple conditions and columns using chained .loc[] or .apply() methods.
  • Use .apply() along with a lambda function to extract values from one column based on the values of another column.

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.


# Quick examples of extract column value based on another column

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

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

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

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

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

# Example 6: 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.

pandas extract column value

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.

pandas extract column value

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

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)

FAQ on Pandas Extract Column Value Based on Another Column

How can I filter rows in a DataFrame based on the value of another column?

To filter rows in a Pandas DataFrame based on the value of another column, you can use boolean indexing. This method allows you to apply a condition to one or more columns and return only the rows that meet the condition.

How do I select a specific value from one column based on a condition in another column?

You can use the .loc[] method to extract a value from one column based on a condition in another column. For example, to get the value from column_b where column_a is equal to 100.

How can I apply a function to extract values based on conditions in another column?

You can use the apply() function for more complex logic. For example, applying a function to extract values from column_b based on the conditions in column_a.

How do I create a new column based on values from another column?

You can use np.where() or apply() to create a new column based on the condition of another column.

How can I use .query() to extract values from one column based on another column?

You can use the .query() method in Pandas to filter rows based on conditions applied to one or more columns, and then extract values from a specific column.

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.

References

Tags: , , ,