How to speed up Pandas contains when moving rows to another dataframe

user_2650838 0
Viewed 129

I have a small script which checks for a condition and if true moves the pandas dataframe row to a new dataframe and then removes the row from the original dataframe.

Originally, I was doing with this regex but this was slow and after some reading on SO I tried it this way - it's slightly quicker.

The production data I'm using runs this across millions of rows so any time saved will be a big help.

Anything I can do to optimise it further?

import pandas as pd


data = [['thomas cook', 222], ['holidays', 333], ['cheap flights', 444], ['thomascook holidays', 555]]
df1 = pd.DataFrame(data, columns=['query', 'clicks'])
df2 = pd.DataFrame(columns=df1.columns)

print(df1)
                 query  clicks
0          thomas cook     222
1             holidays     333
2        cheap flights     444
3  thomascook holidays     555

brand_terms = ['thomas cook', 'thomascook', 'thomas-cook']
for brand_term in brand_terms:
    condtion = df1[df1["query"].str.contains(brand_term, case=False, regex=False)]
    df2 = df2.append(condtion, ignore_index=True)
    df1.drop(condtion.index, inplace=True)

print(df1)
           query  clicks
1       holidays     333
2  cheap flights     444


print(df2)
                 query clicks
0          thomas cook    222
1  thomascook holidays    555
pandas dataframe python
3 Answers

You can use str.contains() and don't change regex parameter:

df2=(df1.loc[df1["query"].str.contains(pat='|'.join(brand_terms), case=False)]
        .reset_index(drop=True))

output of df2:

    query                   clicks
0   thomas cook             222
1   thomascook holidays     555

Update:

You can use ~(the bitwise negation operator) for that(for example):

df1=(df1.loc[~df1["query"].str.contains(pat='|'.join(brand_terms), case=False)])
        .reset_index(drop=True)

Note:

store your condition in a variable for simplicity and performance:

m=df1["query"].str.contains(pat='|'.join(brand_terms), case=False)
df1=df1.loc[~m].reset_index(drop=True)
df2=df1.loc[m].reset_index(drop=True)
user_142898920
user_88633980

  1. Try Vaex, as an alternative to pandas which the internet says is faster than pandas for string operations.

  2. Other option that you can try is do a set_index on query. I don't know the nature of your query column so can't really tell if this will help. If your query falls in a defined set, setting it as index will reduce rows to check and access that index can get you all rows

  3. Also you can change your regex to $'thomas\s?-?cook'^ and check

user_88020120
Related