search dataframe for a keyword in any column and get the rows

11,009

Solution 1

Flying blind without an example here, but how about:

df.apply(lambda row: row.astype(str).str.contains('TEST').any(), axis=1)

So, for example:

import numpy as np
import pandas as pd

np.random.seed(0)
df = pd.DataFrame(np.random.choice(['0.0', 'Hello', 'Goodbye'], (12, 3)))
df.apply(lambda row: row.astype(str).str.contains('Hello').any(), axis=1)

Solution 2

Without data it is complicated, but I try use numpy function numpy.column_stack and list comprehension:

print df
       A      B          D   E
0      A  TEST1 2014-04-08   8
1      B  TEST2 2014-05-08   7
2      B      C 2014-05-08  15
3      B  TEST3 2014-05-08   1
4  TESTA      A 2014-04-08   6
5      A  TEST5 2014-04-08   1

Mask subset with columns with string data:

 mask = np.column_stack([df[col].str.contains("TEST") for col in ['A', 'B']])
 print mask
 [[False  True]
 [False  True]
 [False False]
 [False  True]
 [ True False]
 [False  True]]
print df.loc[mask.any(axis=1)]
       A      B          D  E
0      A  TEST1 2014-04-08  8
1      B  TEST2 2014-05-08  7
3      B  TEST3 2014-05-08  1
4  TESTA      A 2014-04-08  6
5      A  TEST5 2014-04-08  1

Mask subset with excluded columns with not string data:

mask = np.column_stack([df[col].str.contains("TEST") for col in df if col not in ['D', 'E']])
print mask
[[False  True]
 [False  True]
 [False False]
 [False  True]
 [ True False]
 [False  True]]
print df.loc[mask.any(axis=1)]
       A      B          D  E
0      A  TEST1 2014-04-08  8
1      B  TEST2 2014-05-08  7
3      B  TEST3 2014-05-08  1
4  TESTA      A 2014-04-08  6
5      A  TEST5 2014-04-08  1
Share:
11,009
Admin
Author by

Admin

Updated on June 07, 2022

Comments

  • Admin
    Admin almost 2 years

    I have a dataframe which I wish to get a subset by checking for the presence of a keyword across all columns in all rows one by one. Here is the snippet:

    df.apply(lambda x: x.str.contains('TEST')).any()
    

    but because not all column values are of string type and so it throws error:

    AttributeError: ('Can only use .str accessor with string values

    Any help is appreciated.