Find substring in pandas dataframe and save in new column

Question

I have a dataframe with approx. 10,000 rows and 10 columns. And I have a string, which I want to search for in the dataframe, called 'atmosphere'. This string can only be found once in a row. I want to keep only the cells that contain this string, but with their whole content, and save them in a new column. I already found the following solution, but it only gives me back "True" (when cell contains string) or "False" (when it does not).:

df.apply(lambda col: col.str.contains('atmosphere', case=False), axis=1)
Output:
  col_1  col_2  col_3  col_4 ...
1 True   False  False  False
2 False  True   False  False
3 True   False  False  False 
...

How can I get from this, to this?:

   new_col
1 today**atmosphere**is
2 **atmosphere**humid
3 the**atmosphere**now

Tip: Have a look into ‘regex’ and the .extract() method. — s3dev
– s3dev, Commented Mar 23, 2022 at 8:27
It is not clear what is the input and the output. Please provide the output of df.head().to_dict() in your question — mozway
– mozway, Commented Mar 23, 2022 at 8:34

Henry Yik · Accepted Answer · 2022-03-23 08:35:38Z

1

If you already have your result, you can simply stack it:

df = pd.DataFrame({"a":["apple", "orange", "today atmosphere"],
                   "b":["pineapple", "atmosphere humid", "kiwi"],
                   "c":["the atmosphere now", "watermelon", "grapes"]})

                  a                 b                   c
0             apple         pineapple  the atmosphere now
1            orange  atmosphere humid          watermelon
2  today atmosphere              kiwi              grapes


print (df[df.apply(lambda col: col.str.contains('atmosphere', case=False), axis=1)].stack())

0  c    the atmosphere now
1  b      atmosphere humid
2  a      today atmosphere
dtype: object

answered Mar 23, 2022 at 8:35

Henry Yik

22.6k5 gold badges21 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Freyana Over a year ago

thanks, this seems to have worked! @Henry Yik - But the output of the stack function is not saved as a datframe, is it?

Henry Yik Over a year ago

You can always assign it to a variable. Add to_frame() if you want it as a df.

Collectives™ on Stack Overflow

Find substring in pandas dataframe and save in new column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related