Home » Python » Python Programs

Pandas text matching like SQL's LIKE?

Let's understand is there any method in Pandas which resembles SQL's LIKE? By Pranit Sharma Last updated : October 06, 2023

Pandas is a special tool that allows us to perform complex manipulations of data effectively and efficiently. Inside pandas, we mostly deal with a dataset in the form of DataFrame. DataFrames are 2-dimensional data structures in pandas. DataFrames consist of rows, columns, and data.

Problem statement

Suppose we are given the dataframe with multiple columns of string type we need to find a way to do something similar to SQL LIKE syntax on this data frame column so that it returns a list of indexes or a list of Booleans that can be used for indexing the dataframe.

Text matching like SQL's LIKE

With SQL like we can match any number of characters and even zero characters by using '%' and also, we can match exactly 1 character by using '_'.

Similarly, we can use the series method string.startswith and pass any of the required strings inside it as a parameter so that it returns a list of Booleans.

Pandas also offer a series method string.contains() which works similarly to the startswith method but it takes a regular expression as a parameter.

Let us understand with the help of an example,

Python program for pandas text matching like SQL's LIKE

# Importing pandas package
import pandas as pd

# Importing numpy package
import numpy as np

# Creating a dictionary
d = {'a':['abba','ammi','bbaabbaa','foo','bar']}

# Creating a DataFrame
df = pd.DataFrame(d)

# Display Original df
print("Original DataFrame:\n",df,"\n")

# Using startswith method
res = df['a'].str.startswith('a', na=False)

# Display result
print("Result of startswith:\n",res,"\n")

# Using contains
res = df['a'].str.contains('^a', na=False)

# Display result
print("Result of contains:\n",res)

Output

The output of the above program is:

Python Pandas Programs »