7 ways to filter pandas DataFrame by column value

In the vast world of data manipulation and analysis, the ability to selectively sift through vast amounts of information is pivotal. Enter the concept of filtering data, a fundamental operation that data professionals often grapple with. Among the tools at their disposal, the Python library pandas stands out as a robust and versatile solution. Specifically, the operation to "pandas filter by column value" has gained traction for its efficiency and ease of use. This action, succinctly described as "filter dataframe by column value," empowers analysts to streamline dataframes, shedding irrelevant data and honing in on what truly matters. As we delve into this article, we'll unravel the nuances of this operation, ensuring that by the end, you'll be adept at harnessing the power of pandas to filter data seamlessly.

 

Different methods to filter pandas DataFrame by column value

In this tutorial we will discuss how to filter pandas DataFrame by column value  using the following methods:

• Boolean Indexing: Directly filter rows based on column conditions using boolean expressions.
• Using loc: Access a group of rows and columns by labels or boolean arrays.
• Using query Method : Filter DataFrame rows using a query string, especially useful for complex conditions.
• isin Method : Filter DataFrame rows based on whether the column value is in a set of values.
• String Methods (str attribute) : Use string functions to filter columns that contain text.
• Using lambda Functions : Apply a custom function for more complex or unique filtering conditions.
• Handling Missing Values (isna and notna methods) : Filter rows based on whether a column value is missing or present.

 

Setting Up the Environment

Before diving into the techniques of filter pandas DataFrame by column value, it's essential to establish a proper environment to work in. Here's how you can set up your environment:

Installing pandas

If you haven't already installed pandas, it's the primary library we'll be using. Installation is straightforward using pip, which is a package management system used to install and manage software packages written in Python.

Command to install pandas:

pip install pandas

Importing Necessary Libraries

After installation, before using pandas in your code, you must import it. The common convention is to import pandas as pd, which is just an alias. It's a shorthand that you'll find in most pandas tutorials and documentation.

Python code to import pandas:

import pandas as pd

Creating a Sample DataFrame for Demonstration

A DataFrame is a 2-dimensional labeled data structure with columns that can be of different types, similar to a spreadsheet or SQL table, or a dict of Series objects. To ensure you can follow along with filtering techniques, let's create a sample DataFrame:

data = {
    'Name': ['John', 'Anna', 'Mike', 'Ella'],
    'Age': [28, 22, 32, 25],
    'City': ['New York', 'London', 'San Francisco', 'Los Angeles']
}

df = pd.DataFrame(data)

 

1. Boolean Indexing

In the context of pandas, Boolean indexing is a powerful and intuitive technique to filter pandas dataframe by column based on the value of those columns. By applying conditions directly to a pandas Series, a resultant series of True or False values is generated. This series can then be used to filter rows of the dataframe, selecting only those where the condition holds true.

Single Condition

If we want to filter this DataFrame to show only individuals older than 25, we'd apply the condition directly to the 'Age' column:

df[df['Age'] > 25]

Here, df['Age'] > 25 returns a Boolean Series where rows corresponding to individuals older than 25 are marked as True. This Series then filters df to show only the rows where the condition is met.

Multiple Conditions

Filtering based on multiple conditions requires the use of logical operators: & (and), | (or), and ~ (not). It's essential to wrap each condition in parentheses to ensure the desired evaluation order.

For instance, to extract rows corresponding to individuals older than 25 and living in New York:

df[(df['Age'] > 25) & (df['City'] == 'New York')]

Note the use of parentheses around each condition.

Negating Conditions

To filter out certain rows, the ~ operator can be used. For example, to get rows of individuals who don't live in New York:

df[~(df['City'] == 'New York')]

 

2. Using the loc Method

The loc method is one of the primary tools in pandas, specifically designed to filter pandas dataframe by column and row labels. Whether you're targeting specific rows based on single conditions or combining multiple conditions, loc ensures direct access to the desired subset of data, making it indispensable for precise data selection.

In pandas, the loc method allows you to select rows and columns by their labels. Labels can be explicit row/column names, or boolean arrays. This method is versatile as it helps in accessing a group of rows and columns by their labels or conditions.

Filtering with loc Using a Single Condition

The basic syntax for filtering with loc is:

dataframe.loc[condition]

For instance, if you want to filter rows where the age is greater than 25, you'd use:

df.loc[df['Age'] > 25]

Filtering with loc Using Multiple Conditions

To filter using multiple conditions, you'd employ logical operators like & (and), | (or), and ~ (not). Remember, each condition must be wrapped in parentheses to ensure proper evaluation order.

For example, to find individuals older than 25 who live in New York:

df.loc[(df['Age'] > 25) & (df['City'] == 'New York')]

As you can observe, combining conditions provides a powerful way to refine your data filtering to be as specific or as broad as necessary.

 

3. Using the query Method

The query method offers a dynamic approach to filter pandas dataframe by column using a string-based expression. Especially beneficial for complex conditions or when working with large dataframes, this method provides a more structured and readable means to sift through data, compared to traditional Boolean indexing.

Filtering DataFrame Rows Using a Query String

The query method in pandas provides a more readable way to filter rows, especially when dealing with multiple conditions. Instead of explicitly referencing the dataframe in each condition, we can specify conditions as a string.

Single Condition Filtering

Imagine a scenario where you want to filter out individuals older than 25. Instead of using the usual Boolean indexing, the same can be achieved with:

df.query("Age > 25")

Multiple Conditions

For complex queries, you can chain conditions together using logical operators within the query string. For instance, to filter for individuals older than 25 and living in New York:

df.query("Age > 25 and City == 'New York'")

The query method naturally supports the logical operators: and, or, and not.

Using External Variables in Queries

One of the advantages of query is its ability to incorporate external variables using the @ symbol. If you have a variable age_limit that specifies a particular age, and you want to filter based on this:

age_limit = 25
df.query("Age > @age_limit")

 

4. Using the isin Method

When you're looking to filter pandas dataframe by column based on a predefined set of values, the isin method is a remarkable tool. It streamlines the process by checking for membership, eliminating the need to write verbose conditions using multiple OR operators.

When you have a set of specific values and wish to determine whether each row's value in a DataFrame column matches one of these values, the isin method is your go-to solution. It returns a Boolean Series that can be used directly to filter rows in the DataFrame.

Filtering with a Single List of Values

Consider a scenario where you have a DataFrame df and you want to filter rows where the "City" column has either "New York", "London", or "Tokyo". Instead of writing out three separate conditions:

df[(df['City'] == 'New York') | (df['City'] == 'London') | (df['City'] == 'Tokyo')]

You can simplify with the isin method:

cities = ['New York', 'London', 'Tokyo']
df[df['City'].isin(cities)]

Using isin with Multiple Columns

The power of the isin method isn't just limited to one column. If you have criteria for multiple columns, you can combine the results. For instance, to filter rows with specific names and cities:

names = ['John', 'Anna']
cities = ['New York', 'London']
df[df['Name'].isin(names) & df['City'].isin(cities)]

names = ['John', 'Anna']
cities = ['New York', 'London']
df[df['Name'].isin(names) & df['City'].isin(cities)]

Negating the isin Result

There may be times when you want to filter rows where the column value is not in a set of values. This can be achieved by prefixing the condition with the ~ operator:

cities = ['New York', 'London']
df[~df['City'].isin(cities)]

 

5. Using String Methods (str Attribute)

When dealing with textual data within pandas, the str attribute becomes an invaluable resource to filter pandas dataframe by column. Offering a comprehensive suite of string functions, this attribute allows for seamless manipulation and filtering of text-based columns.

The str attribute allows for vectorized operations on string columns. Just like Python's native string methods (like split(), replace(), etc.), pandas' str methods work similarly but can be applied to entire Series or columns.

Checking for Substrings with contains

If you need to filter rows where a string column contains a particular substring:

df[df['Name'].str.contains('Jo')]

This would return rows where the 'Name' column has names like 'John', 'Johan', 'Jodie', etc.

Matching Patterns with match

While contains checks if a substring is present anywhere in the string, match checks if a string starts with a given pattern (often used with regular expressions):

df[df['Name'].str.match('Jo')]

This will filter for names that start with "Jo" like 'John' but not 'Mary Jo'.

Changing Case for Comparison

Sometimes, it's essential to ensure a case-insensitive comparison. You can change the case of the entire column using lower() or upper() and then apply your condition:

df[df['City'].str.lower() == 'new york']

Filtering Using String Length

At times, you might need to filter based on the length of strings:

df[df['Name'].str.len() > 5]

Advanced Filtering with Regular Expressions

The str attribute supports regex-based methods too. For instance, to filter rows with names that have two consecutive vowels:

df[df['Name'].str.contains(r'[aeiou]{2}', regex=True)]

 

6. Using lambda Functions

Lambda functions, also known as anonymous functions, are one of the most flexible tools in the Python ecosystem, allowing for the creation of small, one-off functions without the need for a formal function definition. For situations that demand a custom touch or when standard methods fall short, lambda functions are your ally. They enable you to craft specific logic on-the-fly to filter pandas dataframe by column, ensuring that even the most intricate requirements can be met efficiently.

A lambda function is a way to create small, anonymous functions on-the-fly. Its concise syntax can streamline your code, especially when the function's logic is short and specific to a particular operation.

Basic Filtering with Lambda

Let's say you want to filter rows based on the length of a string in a column. While this could be achieved using the str attribute, it can also be done using a lambda function:

df[df['Name'].apply(lambda x: len(x) > 5)]

Complex Conditions with Lambda

Lambda functions shine when dealing with more intricate conditions that may not be directly achievable with standard pandas methods. For instance, filtering rows where a column's value is a palindrome:

df[df['Word'].apply(lambda x: x == x[::-1])]

Using Lambda with Multiple Columns

Lambda functions can also consider values from multiple columns. Imagine you want to filter rows where the sum of values in two columns exceeds a certain limit:

limit = 100
df[df.apply(lambda row: row['Value1'] + row['Value2'] > limit, axis=1)]

 

7. Handling Missing Values (isna and notna Methods)

In real-world datasets, missing values are a common occurrence. Before delving into analysis or further processing, identifying and managing these missing values is crucial. Thankfully, pandas provides built-in methods, notably isna and notna are tailored to filter pandas dataframe by column based on the presence or absence of data.

In pandas, missing values are usually represented using NaN (Not a Number) for numerical data and NaT (Not a Timestamp) for datetime data. Both are specific instances of the numpy.nan object.

Using the isna Method

The isna method returns a DataFrame (or Series) of the same shape as the original but with True where values are missing and False otherwise.

To filter rows where a specific column has missing values:

df[df['Age'].isna()]

Using the notna Method

The counterpart to isna, the notna method flags non-missing values with True and missing values with False.

To filter rows where a particular column has a non-missing value:

df[df['Age'].notna()]

Filtering Based on Multiple Columns

You can combine the results from isna and notna for multiple columns using logical operators. For instance, to find rows where 'Age' is missing but 'Name' is present:

df[df['Age'].isna() & df['Name'].notna()]

 

Summary

Filtering data is a fundamental operation in data analysis and manipulation. Pandas, a powerful data manipulation library in Python, provides a plethora of methods to filter dataframes based on column values. From direct Boolean indexing and the versatile query method to handling textual data with the str attribute and managing missing values with isna and notna, pandas equips users with diverse tools to tailor datasets to their specific needs. Whether you're dealing with large datasets or specific filtering requirements, understanding how to effectively filter pandas dataframe by column is crucial for insightful data analysis.

 

References

Python - DataFrame
How do I select rows from a DataFrame based on column values?