Python dataframe matching strings in a list

Question

I need to search a dataframe column for matching strings within a list and return the match into a new column in the dataframe. The below code works but it is horribly inefficient and I have millions of rows in my dataframe.

import pandas as pd 
Cars = {'MakeModel': ['HondaCivic','Toyota_Corolla','FordFocus','Audi--A4']}  
df = pd.DataFrame(data=Cars) 

mlist = ['Honda','Toyota','Ford','Audi'] 

for i in df.index:  
    for x in mlist:     
        if x in df.get_value(i,'MakeModel'): 
            df.set_value(i,'Make', x)

cs95 · Accepted Answer · 2019-07-05 14:33:29Z

1

Let's use str.extract with a capture group here. This extracts the "make" from each cell if it exists, or inserts NaNs in that row.

import re

df['Make'] = df['MakeModel'].str.extract(
    r'({})'.format('|'.join(map(re.escape, mlist))), expand=False)
df
        MakeModel    Make
0      HondaCivic   Honda
1  Toyota_Corolla  Toyota
2       FordFocus    Ford
3        Audi--A4    Audi

map(re.escape, mlist) can be replaced with mlist if you're sure your mlist strings do not contain any regex meta-characters which require escaping.

answered Jul 5, 2019 at 14:33

cs95

406k106 gold badges744 silver badges797 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bikerider Over a year ago

Is there a way to use "Unknown" in place of NaNs in the this same line of code? This worked perfectly - thanks! It processed 30M rows in a less than a minute!

cs95 Over a year ago

@bikerider somewhat, you can always use .fillna('Unknown') on the result. extract cannot do it on its own.

Collectives™ on Stack Overflow

Python dataframe matching strings in a list

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related