Using fillna method on multiple columns of a Pandas DataFrame failed

Question

Why would this operation fail? For example:

a = pd.DataFrame({'a': [1,2,np.nan, np.nan],
                 'b': [5,np.nan,6, np.nan],
                 'c': [5, 1, 5, 2]})


a[['a', 'b']].fillna(0, inplace=True)

and gave me this warning:

SettingWithCopyWarning: 
A value is trying to be set on a copy of a slice from a DataFrame

But a was still Filled with NAs as before. However, if I call .fillna() on each column separately, there'd be no issue. How can I fill NA values on multiple columns in one shot?

Indiraindirect

Just a.fillna(0, inplace = True) – Fug

Answer 1

These answers are guided by the fact that OP wanted an in place edit of an existing dataframe. Usually, I overwrite the existing dataframe with a new one.

---

Use pandas.DataFrame.fillna with a dict

Pandas fillna allows us to pass a dictionary that specifies which columns will be filled in and with what.

So this will work

a.fillna({'a': 0, 'b': 0})

     a    b  c
0  1.0  5.0  5
1  2.0  0.0  1
2  0.0  6.0  5
3  0.0  0.0  2

With an in place edit made possible with:

a.fillna({'a': 0, 'b': 0}, inplace=True)

NOTE: I would've just done this a = a.fillna({'a': 0, 'b': 0})

We don't save text length but we could get cute using dict.fromkeys

a.fillna(dict.fromkeys(['a', 'b'], 0), inplace=True)

---

loc

We can use the same format as the OP but place it in the correct columns using loc

a.loc[:, ['a', 'b']] = a[['a', 'b']].fillna(0)

a

     a    b  c
0  1.0  5.0  5
1  2.0  0.0  1
2  0.0  6.0  5
3  0.0  0.0  2

---

pandas.DataFrame.update

Explicitly made to make in place edits with the non-null values of another dataframe

a.update(a[['a', 'b']].fillna(0))

a

     a    b  c
0  1.0  5.0  5
1  2.0  0.0  1
2  0.0  6.0  5
3  0.0  0.0  2

---

Iterate column by column

I really don't like this approach because it is unnecessarily verbose

for col in ['a', 'b']:
    a[col].fillna(0, inplace=True)

a

     a    b  c
0  1.0  5.0  5
1  2.0  0.0  1
2  0.0  6.0  5
3  0.0  0.0  2

---

fillna with a dataframe

Use the result of a[['a', 'b']].fillna(0) as the input for another fillna. In my opinion, this is silly. Just use the first option.

a.fillna(a[['a', 'b']].fillna(0), inplace=True)

a

     a    b  c
0  1.0  5.0  5
1  2.0  0.0  1
2  0.0  6.0  5
3  0.0  0.0  2

Deutzia

You beat me by 25 seconds:) – Fug

Thanks a lot! Why is there such a difference between fillna on multiple columns and a single column, even with flagging both inplace=True? – Indiraindirect

Because a[['a', 'b']] is a view/slice and will produce the warning when you use inplace=True. a.loc[: ['a', 'b']] won't give a warning with inplace=True, but it's doing it on a copy which isn't helpful. My Option 4 shows it with an inplace=True because I'm operating on a itself but filling with another dataframe in which I've filled already... btw Option 4 is absurd, please don't actually use it. – Deutzia

Does this mean that the best way would be option 3? Because my gut instinct told me to go with no 4 which you've now discouraged me from doing :) – Ounce

@Ounce I've updated the answer to include a new and preferred way. – Deutzia

Thanks - if I wanted to map changes instead as a['c'].map({4: 5, 2: 4, 1: 3}) and with several columns, could I use update then - or would another command be better? – Ounce

I'd recommend asking a new question. – Deutzia

@Ounce also, your example dictionary precludes itself from being used in pandas.DataFrame.replace but you could indeed use update. But as I said, far better to ask a new question. – Deutzia

Answer 2

EDIT: As @piRSquared pointed out, the first solution should be

a.loc[:, ['a', 'b']] = a[['a', 'b']].fillna(0)

to fillna in selected columns

or

a.fillna(0, inplace = True)

to fillna in all the columns

Fug

a.loc[:, ['a', 'b']].fillna(0, inplace=True) fills the copy. The second works, but will fill 'c' if it had nulls – Deutzia

Oh ok, a bad day i guess:( – Fug

You obviously see how many answers MaxU, jezrael, and I put up... trust me, we've corrected each other many many times. Get used to it. I'm happy to have another person actively answering pandas questions. I'm just looking out for you :-) – Deutzia