Return efficiently all occurrences for substring in Pandas Python DataFrame (large tables)

matt.aurelio 0
Viewed 25

How can I obtain the values that I am searching for in a dataframe using str.contains?

import pandas as pd
import numpy as np
import re

df = pd.DataFrame({"Name": ['Philip', 'Jana', 'Kate', 'John K.', 'Jonhatan'],
                   "City": ['NewYork', 'New jearsey', 'Mexico City', 'Lisbon', 'Bahia'],
                   "Language": ['English', 'english', 'Spanish, Dutch, German', 'Spanish and English', 'Portuguese, English'],
                    "Years": [24, 27, 29, 40, 61] })

search = 'english'
mask = np.column_stack([df[col].astype(str).str.contains(search, flags=re.I) for col in df.columns]).nonzero()

df.where(mask)

Returns:

enter image description here

Ideally I would like to have the following inside a single series:


pd.Series(['English', 'english', 'Spanish and English', 'Portuguese, English'])

EDIT: The assumption is that I don't know where is the value located within the dataframe. Also I want to use str.contains because of ability to use regex.

dataframe pandas python
1 Answers

Make simple things simple. Iterate over the 'Language' column items and filter out all which contain the word 'english' as follows:

import pandas as pd
df = pd.DataFrame({"Name": ['Philip', 'Jana', 'Kate', 'John K.', 'Jonhatan'],
                   "City": ['NewYork', 'New jearsey', 'Mexico City', 'Lisbon', 'Bahia'],
                   "Language": ['English', 'english', 'Spanish, Dutch, German', 'Spanish and English', 'Portuguese, English'],
                    "Years": [24, 27, 29, 40, 61] })
print(df)
print(' --- ')
ds = pd.Series( [ entry for entry in df['Language'] if 'english' in  entry.lower() ] )
print(ds)

In case the column name is not known you have to iterate over all items in the pandas dataframe and filter out all strings you can find. I have modified the df so that you can see it will find also 'Jana English' from the "Name" column:

import pandas as pd
df = pd.DataFrame({"Name":     ['Philip', 'Jana English', 'Kate', 'John K.', 'Jonhatan'],
                   "City":     ['NewYork', 'New jearsey', 'Mexico City', 'Lisbon', 'Bahia'],
                   "Language": ['English', 'english', 'Spanish, Dutch, German', 'Spanish and English', 'Portuguese, English'],
                    "Years":   [24, 27, 29, 40, 61] })
print(df)
print(' --- ')
ds = []
for column in df.columns:
   ds.extend([ entry for entry in df[column] 
       if isinstance(entry, str) and 'english' in  entry.lower()])
ds = pd.Series(ds)
print(ds)

Here the output of above code:

           Name         City                Language  Years
0        Philip      NewYork                 English     24
1  Jana English  New jearsey                 english     27
2          Kate  Mexico City  Spanish, Dutch, German     29
3       John K.       Lisbon     Spanish and English     40
4      Jonhatan        Bahia     Portuguese, English     61
 --- 
0           Jana English
1                English
2                english
3    Spanish and English
4    Portuguese, English
dtype: object

If speed is an issue list comprehension could be faster than Python loop so check it out:

Rows, Cols = df.shape
ds = pd.Series( [item for item in [ 
        df.iloc[row,col] for col in range(Cols) for row in range(Rows) ] 
        if isinstance(item, str) and 'english' in item.lower()] )

Probably even faster then this above should be be:

dsl = []
def f(e):
    global dsl
    if isinstance(e, str) and 'english' in e.lower(): dsl.append(e) 
df.applymap(f)
ds = pd.Series(dsl)
print(ds)

and if this is still not fast enough ... why not use directly the CSV text and search in the text using regular expressions?

user95mw0
Related