Pandas Set multiple column and row values to nan based on another dataframe

Question

I have two large dataframes, df1:

Col1    Col2    Val1    Val2    Val3
asd     ASYL    4.2             4.2
ppq     CONE            35      35
DA      HU      100     100

And df2 is

Col1    Col2    Val1    Val2  Val3
asd     ASYL    7       12    17
ppq     CONE    17      19    19
DA      HU      5       14    13

Both dataframes have same index columns Col1 and Col2 and same value columns Val1, Val2, Val3.

I want to set the values in df2 to nan where it is Null in df1 to get the following:

Col1    Col2    Val1    Val2  Val3
asd     ASYL    7             17
ppq     CONE            19    19
DA      HU      5       14

I tried the following:

idx = df1.isnull()
df2.loc[idx] = np.nan

But it is not working.

Ch3steR · Accepted Answer · 2020-06-06 10:51:22Z

3

You can use df.where with df.notna

df2.where(df1.notna())

Col1    Col2    Val1   Val2  Val3
asd     ASYL    7      NaN    17
ppq     CONE   NaN      19    19
DA      HU      5       14    NaN

edited Jun 6, 2020 at 10:51

answered Jun 6, 2020 at 10:36

Ch3steR

20.8k4 gold badges34 silver badges66 bronze badges

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

Comments

wwnde · Accepted Answer · 2020-06-06 11:16:35Z

2

Another way

df1.mask(df1.notna(),df2)

Alternatively

m=df1.notna()

pd.DataFrame(np.where(m, df2, np.nan))

answered Jun 6, 2020 at 11:16

wwnde

26.7k6 gold badges22 silver badges38 bronze badges

1 Comment

wwnde Over a year ago

See two alternatives to @Ch3steR solution

Collectives™ on Stack Overflow

Pandas Set multiple column and row values to nan based on another dataframe

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related