Filtering DataFrame Rows

Table of Contents

• Introduction
• Filtering Based on Column Values
  • isin() Method
• Filtering Based on Index
  • startswith() Method
  • endswith() Method
  • contains() Method
  • len() Method
  • Filtering Integer Index

Introduction

Boolean masking refers to selecting subsets of a DataFrame with the help of Boolean expressions. In particular, it can be applied to filtering the rows of a DataFrame based on certain specified Boolean expressions.

In DataFrames, however, we cannot use the Python logical operators and, or and not. Instead, we need to use the symbols as shown in the table below.

We first import the required libraries and modules.

1import numpy as np
2import pandas as pd
3import random as rd

Let’s create a more elaborate dataframe for this section.

1a='Tom Gary Lois Wendy Betty Jacobs Donald Yasmine Nick Andrew'.split()
2b=rd.choices(range(50,85),k=10)
3c=np.random.uniform(1.6, 1.9, 10).round(2)
4d = rd.choices(['Germany', 'UK', 'USA', 'France', 'Canada'], k=10)
5
6df = pd.DataFrame({'Weight':b,
7                   'Height':c,
8                   'Country':d}, index=a)
9df

Filtering Based on Column Values

Suppose we want to select persons whose weight is below 60.

Example

1df[df['Weight']<60]

The expression within the square brackets is a Boolean expression and it is actually a Series of Boolean values.

1df['Weight']<60

Tom         True
Gary       False
Lois       False
Wendy       True
Betty      False
Jacobs      True
Donald     False
Yasmine     True
Nick       False
Andrew     False
Name: Weight, dtype: bool

We can select only persons (rows) who come from ‘Germany’.

Example

1df[df['Country']=='Germany']

Suppose we want to select persons whose weight is above 60 and height is below 1.70.

We first create a mask which is a Boolean expression containing the two conditions. Thereafter, we apply the mask to the DataFrame.

Example

1mask = (df['Weight']>60) & (df['Height']<1.7)
2df[mask]

isin() Method

To filter the DataFrame to include only participants from USA or Germany.

Example

1df.loc[df["Country"].isin(["USA", "Germany"]), :]

Filtering Based on Index

It is also possible to filter the rows of a DataFrame based on the index column. Recall that df.index allows us to see all the row labels.

1df.index

Index(['Tom', 'Gary', 'Lois', 'Wendy', 'Betty', 'Jacobs', 'Donald', 'Yasmine',
       'Nick', 'Andrew'],
      dtype='object')

startswith() Method

To filter rows such that the index starts with a certain character, we use the str attribute followed by the startswith() method. The Boolean mask looks like this.

Example

1df.index.str.startswith(('A','B'))

array([False, False, False, False,  True, False, False, False, False,
        True])

We then feed the above mask into df.loc.

1df.loc[df.index.str.startswith(('A','B'))]

endswith() Method

To filter rows such that the index ends with certain characters, we use the str attribute followed by the endswith() method.

Example

1df.loc[df.index.str.endswith(('y','s'))]

contains() Method

To filter rows such that the index contains certain characters, we use the str attribute followed by the contains() method.

Example

1df.loc[df.index.str.contains('on')]

We can also filter the rows such that the index contains one of multiple specified strings.

Example

1df.loc[df.index.str.contains('on|as')]

Example

1df.loc[df.index.str.contains('on|as|et')]

We can also grep index labels that contain all the specified strings.

Example

1df.loc[(df.index.str.contains('o')) & (df.index.str.contains('i'))]

len() Method

To filter rows such that the index has more than a certain number of characters.

Example

1df.loc[df.index.str.len()>5]

Filtering Integer Index

We first convert the current name index into an integer range index.

Example

1df.reset_index(inplace=True)

The inplace=True argument indicates that the DataFrame is modified in place which means that original df is modified without making a further assignment.

1df

We can select all rows whose indices lie between 3 and 8, exclusive.

Example

1df.loc[(df.index > 3) & (df.index < 8)]