Get dataframe cell value based on substring

I am trying to search for a substring across my dataframe (no specific column) and get in return the value of the cell.

For example:

I’d like to search the cell which contains “Apple” and i should get in return “Red apple”.

i have been successful returning the row which contains the value as following:

df[df.apply(lambda row: row.astype(str).str.contains(“Apple”, case=false).any, axis=1)]

Or the column name which contains it:

df.apply(lambda x: x.str. contains(“Apple”, case=false).any())

But not exactly what i want, the full value of the cell which contains the substring.

Can I please get help?:)

>Solution :

One option is to stack and use boolean indexing:

s = df.stack()

out = s[s.str.contains('apple')]

output:

0  Column A    Red apple
dtype: object

If you expect a single match:

out = s[s.str.contains('apple')].squeeze()

output: 'Red apple'