Different methods to filter a Pandas DataFrame
Tags: python, datascience, data, dataanalysis
Published: 2023-02-26 22:00:59


Pandas is a popular open-source python library used for data manipulation and analysis. It helps in the data analysis process which involves cleaning, exploring, analyzing, and visualizing data. 

In this article, we will cover the various methods of performing filtering using pandas.

Data Filtering is one of the data manipulation operations we can perform when working with data. Filtering with pandas is similar to a WHERE clause in SQL or a filter in Microsoft Excel.

If you have worked with these tools, you have an idea of how filtering works.
Filters can be used to select a subset of data. It can also be used to exclude or discover null values from a dataset

Here are some examples of filtering

_1. Select all employees who were employed by the organization after 2020_
_2. Select all male employees who work in the finance and marketing department._
_3. Select all traffic accidents that occurred on Wednesday around 12 noon_
_4. Select all students whose registration number is null_

These are some real-world examples of what we can do with filters depending on our dataset and use case for our analysis.

Note: This tutorial assumes that you are already familiar with the usage of pandas for data analysis and you can differentiate between its underlying data structure namely Series and Dataframe.

To keep this tutorial simple, we would be creating our sample dataset. It would be a dataset of employees working for an organization.

```
import pandas as pd
```

```
employees_data = {
    'first_name': ['Jamie', 'Michael', 'James', 'Mark', 'Stones', 'Sharon', 'Jessica', 'Johnson'],
    'last_name': ['Johnson', 'Marley', 'Peterson', 'Henry', 'Walker', 'White', 'Mendy', 'Johnson'],
    'department': ['Accounting', 'Marketing', 'Engineering', 'HR', 'HR', 'Sales', 'Data Analytics', 'Accounting'],
    'date_of_hire': [2011, 2012, 2022, 2000, 2007, 2018, 2020, 2018],
    'gender': ['M','M','M','M','F','F','M','F'],
    'salary': [10000,87500,77000,44500,90000,45000,25000,65000],
    'national_id': [22123,78656,98976,12765, None, None,56432,98744],
    'emp_id': ['Emp22', 'Emp54', 'Emp77', 'Emp99', 'Emp98', 'Emp01', 'Emp36', 'Emp04']
}

employees_df = pd.DataFrame(data=employees_data)
```

![pandas dataframe](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/lfywv5ulubavja7kgm5h.png)

In pandas, there is more than one way to operate. Pandas provide multiple ways to filter data.

**They include:**

**isin()**
This method provides a way to apply single or multiple conditions for filtering.

```
employees_df[employees_df['department'].isin(['Marketing'])]
```
![isin method 1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/fn60bp7ruag21lwegmcb.png)
```
employees_df[employees_df['department'].isin(['Marketing','HR'])]
```
![isin method 2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/1vucts3042cuqpnl9g8f.png)

**Logical operators**
We can use the available logical operators to filter for a subset of data

```
employees_df[employees_df['salary'] > 30000]
```
![Logical operators](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/3wkmmhhgin5fkv9fqten.png)

Here we filter for employees whose salary is above 30000.

Occasions may arise where we have to filter our data for multiple conditions. The logical operator also makes this possible.

```
employees_df[(employees_df['salary'] > 30000) & (employees_df['department'] == 'Marketing')]
```

![multiple logical operators](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/zjwx3ernqk7a62gaz2p5.png)
For all employees with a salary above 30000, who work in the marketing department

You can also use other logical operators such as less than(<), greater than(>), equal to(=), not equal to(!=), etc.

**Query Function**
The query function takes in an expression as an argument which evaluates to a Boolean that is used to filter the dataframe.

```
employees_df.query("department == 'Marketing'")
```
![query function](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ki5eeopfodby76ew1llj.png)

We can also query based on multiple conditions

```
employees_df.query("department == 'Marketing' and date_of_hire > 2006")
```
![multiple query method](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/pnzd1jpsa19o7gkrvyb7.png)

**Str Accessor**
Pandas make it easy to work with string values. Using the str accessor, we can filter for records whose values are strings.

```
employees_df[employees_df['department'].str.contains("M")]
```
![str contains method](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/h8yl89r4s0qimrokyu62.png)
```
employees_df[employees_df['department'].str.startswith("M")]
```

![str startswith method](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ibblasntabbwetagbyk3.png)

**nlargest and nsmallest**
Most times, we just need records of the highest or lowest values in a column. 
These methods make it possible. We could filter for the highest 3 or lowest 3 salaries.

```
employees_df.nlargest(3, 'salary')
```

![nlargest method](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/tk6vibk5b629zsg0fe1w.png)
```
employees_df.nsmallest(3, 'salary')
```

![nsmallest method](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/m7hl53q55vyi21wocsvk.png)

**Tilde sign (~)**
Used to reverse the logic used in filter condition
```
employees_df[~employees_df['department'].str.contains("A")]
```
![Tilde method](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/d8l5bkhpmcjpuszfq1q7.png)
```
employees_df[~employees_df['department'].isin(['Marketing','HR'])]
```
![Tilde multiple parameters](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/vs15mv03m8axlxsqqfhe.png)

**isnull | notnull**
Using the isnull method, we can return records that have NaN values and mark them for deletion. Using the notnull method, we can filter for records that do not contain NaN values.
```
employees_df[employees_df['national_id'].isnull()]
```

![isnull method](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gkn8kdimkrxsa7uuu289.png)
```
employees_df[employees_df['national_id'].notnull()]
```
![notnull method](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/2fb63xnzfukvc7kj54qp.png)

**Filter**
Using this method, we can also filter for a subset of data.
```
employees_df.filter(items=['first_name', 'department'],axis=1)
```
![filter method 1](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6pdsb4vu8imkf1o6y323.png)

Filter where the index begins with 5 or 8 using regex.
```
employees_df.filter(regex='5|8', axis=0)
```
![filter method 2](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/6mmymswil1h2ohvs83yr.png)

filter the dataframe where the index is 6
```
employees_df.filter(like='6', axis=0)
```
![Filter method 3](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/bbt8zrlm757thor1gbsp.png)

In this tutorial, we discussed some of the different ways of filtering rows to return a subset of data from our pandas dataframe. 
The only way to master pandas and be good at data manipulation is through practice. 
I do hope you will make out time to practice.

Connect with me on [Twitter ](https://twitter.com/AviatorIfeanyi)or [LinkedIn ](https://www.linkedin.com/in/aviatorifeanyi/) where I also share some useful data analytics tips