Search for a value anywhere in a pandas DataFrame

Question

This seems like a simple question, but I couldn't find it asked before (this and this are close but the answers aren't great).

The question is: if I want to search for a value somewhere in my df (I don't know which column it's in) and return all rows with a match.

What's the most Pandaic way to do it? Is there anything better than:

for col in list(df):
    try:    
        df[col] == var
        return df[df[col] == var]
    except TypeError:
        continue

?

Well, "pandaic" is what I've known the pandas devs to call it (eg Andy Hayden), but you are free to call it as you please, I'm sure synonyms are allowed :D — cs95, Dec 30 '18 at 16:49
From 2020, this should now henceforth be called `Pandemic` over "Pandaic" or "Pandorable". — shivams, Sep 29 '20 at 09:04

cs95 · Accepted Answer · 2021-01-04T00:35:04.253

65

You can perform equality comparison on the entire DataFrame:

df[df.eq(var1).any(1)]

edited Jan 04 '21 at 00:35

answered Dec 30 '18 at 16:36

cs95

379,657
97
704
746

BENY · Answer 2 · 2018-12-30T16:37:24.307

33

You should using isin , this is return the column , is want row check cold' answer :-)

df.isin(['bal1']).any()
A        False
B         True
C        False
CLASS    False
dtype: bool

Or

df[df.isin(['bal1'])].stack() # level 0 index is row index , level 1 index is columns which contain that value 
0  B    bal1
1  B    bal1
dtype: object

edited Dec 30 '18 at 16:37

answered Dec 30 '18 at 16:35

BENY

317,841
20
164
234

6

Guess this will need to be `df[df.isin(['bal1']).any(1)]` – cs95 Dec 30 '18 at 16:37
This should be the correct answer, really...is the other one faster or something? – Jossie Calderon Feb 26 '20 at 19:03
the `.stack()` is quite useful and a LOT more informative too – Wingmore Mar 07 '23 at 01:10

score 2 · Answer 3 · edited Oct 18 '19 at 08:50

You can try the code below:

import pandas as pd
x = pd.read_csv(r"filePath")
x.columns = x.columns.str.lower().str.replace(' ', '_')
y = x.columns.values
z = y.tolist()
print("Note: It take Case Sensitive Values.")
keyWord = input("Type a Keyword to Search: ")
try:
    for k in range(len(z)-1):
        l = x[x[z[k]].str.match(keyWord)]
        print(l.head(10))
        k = k+1
except:
    print("")

score 0 · Answer 4 · answered Nov 28 '22 at 12:09

0

This is a solution which will return the actual column you need.

df.columns[df.isin(['Yes']).any()]

answered Nov 28 '22 at 12:09

anudeep99

1

score 0 · Answer 5 · answered Jan 19 '23 at 17:16

0

Minimal solution:

import pandas as pd
import numpy as np

def locate_in_df(df, value):
    a = df.to_numpy()
    row = np.where(a == value)[0][0]
    col = np.where(a == value)[1][0]
    return row, col

answered Jan 19 '23 at 17:16

Rafael Fraga

1

Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 21 '23 at 01:33

Search for a value anywhere in a pandas DataFrame

5 Answers5

Linked