Check For a Substring in a Pandas DataFrame Column

Looking for strings to cut down your dataset for analysis and machine learning

Byron Dolon

The Pandas library is a comprehensive tool not only for crunching numbers but also for working with text data.

For many data analysis applications and machine learning exploration/pre-processing, you’ll want to either filter out or extract information from text data. To do so, Pandas offers a wide range of in-built methods that you can use to add, remove, and edit text columns in your DataFrames.

In this piece, let’s take a look specifically at searching for substrings in a DataFrame column. This may come in handy when you need to create a new category based on existing data (for example during feature engineering before training a machine learning model).

If you want to follow along, download the dataset here.

import pandas as pd

df = pd.read_csv('vgsales.csv')

Now let’s get started!

NOTE: we’ll be using a lot of loc in this piece, so if you’re unfamiliar with that method, check out the first article linked at the very bottom of this piece.

Using “contains” to Find a Substring in a Pandas DataFrame

The contains method in Pandas allows you to search a column for a specific substring. The contains method returns boolean values for the Series with True for if the original Series value contains the substring and False if not. A basic application of contains should look like Series.str.contains("substring"). However, we can immediately take this to the next level with two additions:

1. Using the case argument to specify whether to match on string case;
2. Using the returned Series of boolean values as a mask to get a subset of the DataFrame.

Applying these two should look like this:

pokemon_games = df.loc[df['Name'].str.contains("pokemon", case=False)] 

#if contains special characters:

df.loc[df['ITEM'].str.contains(r'[?]')] 

Using the loc method allows us to get only the values in the DataFrame that contain the string “pokemon”. We’ve simply used the contains method to acquire True and False values based on whether the “Name” column includes our substring and then returned only the True values.

Using regex with the “contains” method in Pandas

In addition to just matching on a regular substring, we can also use contains to match on regular expressions. We’ll use the exact same format as before, except this time let’s use a bit of regex to only find the story-based Pokemon games (i.e. excluding Pokemon Pinball and the like).

pokemon_og_games = df.loc[df['Name'].str.contains("pokemon \w{1,}/", case=False)]

Above, I just used some simple regex to find strings that matched the pattern of “pokemon” + “one character or more” + “/”. The result of the new mask returned rows including “Pokemon Red/Pokemon Blue”, “Pokemon Gold/Pokemon Silver”, and more.

Next, let’s do another quick example of using regex to find all Sports games with “football” or “soccer” in its name. First, we’ll use a simple conditional statement to filter out all rows with the a genre of “sports”:

sports_games = df.loc[df['Genre'] == 'Sports']

You’ll notice that above there was no real need to match on a substring or use regex, because we were simply selecting rows based on a category. However, when matching on the row name, we’ll need to be searching different types of strings for a substring, which is where regex comes in handy. To do so, we’ll do the following:

football_soccer_games = sports_games.loc[df['Name'].str.contains("soccer|football", case=False)]

Now we’ve gotten a DataFrame with just the games that have a name including “soccer” or “football”. We simply made use of the “|” regex “or” operator that allows you to match on a string that contains one or another substring.

So we’ve successfully gotten a DataFrame with only names that contain either “football” or “soccer”, but we don’t actually know which of those two strings it contains. If we wanted to know which of the two it contained, we could use the findall method on the name column and assign the returned values to a new column in the DataFrame.

The findall method returns matches of the pattern of regular expression you specify in each string of the Series you call it on. The format is largely the same as the contains method, except you’ll need to import re to not match on string case.

import re
football_soccer_games['Football/Soccer'] = football_soccer_games['Name'].str.findall('football|soccer', flags=re.IGNORECASE)

You’ll see at the end of the returned DataFrame a new column that contains either “Soccer” or “Football”, depending on which of the two the videogame name contains. This can be helpful if you need to create new columns based on the existing columns and using values from those columns.

Finally, for a quick trick to exclude strings with just one additional operator on top of the basic contains method, let’s try to get all the football and soccer games that don’t include “FIFA” in the name.

not_fifa = football_soccer_games.loc[~football_soccer_games['Name'].str.contains('FIFA')]

As you can see, we’ve simply made use of the ~ operator that allows us to take all the False values of the mask inside the loc method.

And that’s all!

Working with strings can be a little tricky, but the in-built Pandas methods are versatile and allow you to slice and dice your data in pretty much whatever way you need. The contains and findall methods allow you to do a lot, especially when you’re able to write some regular expressions to really find specific substrings.

Good luck with your strings!