Pandas won't fillna() inplace

Question

I'm trying to fill NAs with "" on 4 specific columns in a data frame that are string/object types. I can assign these columns to a new variable as I fillna(), but when I fillna() inplace the underlying data doesn't change.

a_n6 = a_n6[["PROV LAST", "PROV FIRST", "PROV MID", "SPEC NM"]].fillna("")
a_n6

gives me:

<class 'pandas.core.frame.DataFrame'>
Int64Index: 1542 entries, 0 to 3611
Data columns (total 4 columns):
PROV LAST     1542  non-null values
PROV FIRST    1542  non-null values
PROV MID      1542  non-null values
SPEC NM       1542  non-null values
dtypes: object(4)

but

a_n6[["PROV LAST", "PROV FIRST", "PROV MID", "SPEC NM"]].fillna("", inplace=True)
a_n6

gives me:

<class 'pandas.core.frame.DataFrame'>
Int64Index: 1542 entries, 0 to 3611
Data columns (total 7 columns):
NPI           1103  non-null values
PIN           1542  non-null values
PROV FIRST    1541  non-null values
PROV LAST     1542  non-null values
PROV MID      1316  non-null values
SPEC NM       1541  non-null values
flag          439  non-null values
dtypes: float64(2), int64(1), object(4)

It's just one row, but still frustrating. What am I doing wrong?

Answer 1

Use a dict as the value argument to fillna()

As mentioned in the comment by @rhkarls on @Jeff's answer, using .loc indexed to a list of columns won't support inplace operations, which I too find frustrating. Here's a workaround.

Example:

import pandas as pd
import numpy as np

df = pd.DataFrame({'a':[1,2,3,4,np.nan],
                   'b':[6,7,8,np.nan,np.nan],
                   'x':[11,12,13,np.nan,np.nan],
                   'y':[16,np.nan,np.nan,19,np.nan]})
print(df)
#     a    b     x     y
#0  1.0  6.0  11.0  16.0
#1  2.0  7.0  12.0   NaN
#2  3.0  8.0  13.0   NaN
#3  4.0  NaN   NaN  19.0
#4  NaN  NaN   NaN   NaN

Let's say we want to fillna for x and y only, not a and b.

I would expect using .loc to work (as in an assignment), but it doesn't, as mentioned earlier:

# doesn't work
df.loc[:,['x','y']].fillna(0, inplace=True)
print(df) # nothing changed

However, the documentation says that the value argument to fillna() can be:

alternately a dict/Series/DataFrame of values specifying which value to use for each index (for a Series) or column (for a DataFrame). (values not in the dict/Series/DataFrame will not be filled).

It turns out that using a dict of values will work:

# works
df.fillna({'x':0, 'y':0}, inplace=True)
print(df)
#     a    b     x     y
#0  1.0  6.0  11.0  16.0
#1  2.0  7.0  12.0   0.0
#2  3.0  8.0  13.0   0.0
#3  4.0  NaN   0.0  19.0
#4  NaN  NaN   0.0   0.0

Also, if you have a lot of columns in your subset, you could use a dict comprehension, as in:

df.fillna(dict.fromkeys(['x', 'y'], 0), inplace=True) # also works

Answer 2

you are filling a copy (which you then can't see)

either:

• don't fillna inplace (there is no performance gain from doing something inplace)

for example

a_n6[["PROV LAST", "PROV FIRST", "PROV MID", "SPEC NM"]] = a_n6[["PROV LAST", "PROV FIRST", "PROV MID", "SPEC NM"]].fillna("")

or preferably

a_n6.fillna({'PROV LAST': '', 'PROV FIRST': '',
            'PROV MID': '', 'SPEC NM': ''}, inplace=True)

• assign the copy to a new variable first (the a_n6[[list_of_fileds]] is a copy in a multi-dtype object), see here: http://pandas.pydata.org/pandas-docs/stable/indexing.html#returning-a-view-versus-a-copy

here's a more in-depth explanation Pandas: Chained assignments

Answer 3

a workaround is to save fillna results in another variable and assign it back like this:

na_values_filled = X.fillna(0)
X = na_values_filled

My exact example (which I couldn't get to work otherwise) was a case where I wanted to fillna in only the first line of every group. Like this:

groups = one_train.groupby("installation_id")
first_indexes_filled = one_train.loc[groups.apply(pd.DataFrame.first_valid_index), 'clicks'].fillna(0)
one_train.loc[groups.apply(pd.DataFrame.first_valid_index), 'clicks'] =  first_indexes_filled

My case may be unnecessarily complicated but i think the general "save results, then assign back" method should work as a workaround for the failing inplace=True

Answer 4

The top answer gave me SettingWithCopyWarning: A value is trying to be set on a copy of a slice from a DataFrame, so this is what I ended up with. It works and doesn't give any warnings:

fill_dict = {x: 0 for x in columns_of_interest}
df.loc[:, columns_of_interest].fillna(fill_dict, inplace=True)

Answer 5

The "Use a dict as the value argument" answer doesn't work for me, but an easy enough workaround is to use:

for n in ["PROV LAST", "PROV FIRST", "PROV MID", "SPEC NM"]:    
    a_n6[n].fillna("", inplace=True)
a_n6