Filtering DataFrame Columns

Table of Contents

• Introduction
• Filtering Columns Based on Row Values
  • isin() Method
• Filtering Based on Column Labels
  • startswith() Method
  • endswith() Method
  • contains() Method
  • len() Method

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 columns 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

Let’s create a more elaborate dataframe with 10 columns.

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

Filtering Columns Based on Row Values

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

Example

1df.loc[:, df.loc['Weight']<60]

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

1df.loc['Weight']<60

Tom        False
Jack       False
Gary       False
Lois        True
Jacobs      True
Donald     False
Yasmine    False
Cindy      False
Name: Weight, dtype: bool

Or persons who come from ‘Germany’.

Example

1df.loc[:,df.loc['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.loc['Weight']>60) & (df.loc['Height']<1.7)
2df.loc[:,mask]

isin() Method

To select only columns such that the ‘Country’ is either ‘USA’ or ‘Germany’.

Example

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

Filtering Based on Column Labels

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

1df.columns

Index(['Tom', 'Jack', 'Gary', 'Lois', 'Jacobs', 'Donald', 'Yasmine', 'Cindy'], dtype='object')

startswith() Method

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

Example

1df.columns.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.columns.str.startswith(('A','B'))]

endswith() Method

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

Example

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

contains() Method

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

Example

1df.loc[:,df.columns.str.contains('on')]

Column labels containing one of multiple string patterns.

Example

1df.loc[:,df.columns.str.contains('on|as')]

Example

1df.loc[:,df.columns.str.contains('on|as|et')]

To grep column labels that contain BOTH “o” and “i”.

Example

1df.loc[:,(df.columns.str.contains('o')) & (df.columns.str.contains('i'))]

len() Method

To filter columns such that the label has more than a certain number of characters.

Example

1df.loc[:,df.columns.str.len()>5]