replace all column values not in a list python

Question

I have a df that looks like below:

             Animal               Color                Note
         0    Cat                 Brown               Friendly
         1    Dog                 White               Furry
         2    Rabbit              Brown               Furry

Now I want the code to check values in all columns and comparing against a list, replace the values that are not in the list with "NA". So the list is

  my_list = ['Dog', 'White', 'Friendly']

And the desired output is:

             Animal               Color                Note
         0    NA                  NA                  Friendly
         1    Dog                 White               NA
         2    NA                  NA                  Furry

I found a similar question in below link How to replace all values in a Pandas Dataframe not in a list?

So as suggested there, I tried the below

     df_new = df[~df.isin(my_list)] = "NA"

But it gives me "NA" as a result, not the desired df. Could someone please help me with how to fix this? Much appreciated.

What is the name of your input DataFrame? `df` or `df_in`? – mozway Jul 03 '22 at 15:28 — mozway, Jul 03 '22 at 15:28

mozway · Accepted Answer · 2022-07-03T15:30:52.067

You can use where:

df_new = df.where(df.isin(my_list))

output:

  Animal  Color      Note
0    NaN    NaN  Friendly
1    Dog  White       NaN
2    NaN    NaN       NaN

That said, df[~df.isin(my_list)] = 'NA' should also work for in place modification, but replace with a 'NA' string, not a real NaN.

Be careful, df_new = df[~df_in.isin(my_list)] = "NA" doesn't make sense as you have a double assignment (and it is unclear what is df and what is df_in)!

replace all column values not in a list python

1 Answers1