Fill NaN values on all columns except one (without dropping it) in dataframe

user_16039969 0
Viewed 976

I want to fill NaN values with "Not Available" string for all columns in my dataframe except for one column (that is named "lag")

I've tried this:

#fill NaN values with "Not Available" except in "lag" column
mydataset_df[mydataset_df.columns.difference(['lag'])].fillna("Not Available", inplace = True)

And also this:

mydataset_df.loc[ : , mydataset_df.columns != 'lag'].fillna("Not Available", inplace = True)

What I get is the same result: no columns are filled with "Not Available" when blank. But if I execute:

mydataset.fillna("Not Available", inplace = True)

I get ALL the blank values in the df correctly filled with "Not Available" (so I guess the issue here is the first part in the code string when I select the columns on which the command needs to be applied)

Can somebody help me on this? I don't want to drop "lag" column, I just want to leave it and fill all the other columns if there are blanks.

Thanks in advance

Stefano

pandas nan python
3 Answers

You can try this

for col in mydataset_df.columns:
    if col != 'lag':
        mydataset_df[col].fillna("Not Available", inplace=True)
user_111452610

Use pop and pd.concat:

pop the lag column and fill NAN in other columns with Not Available. Finally, merge the 2.

df = pd.concat([df.pop('lag'), df.fillna('Not Available')], axis=1)
user_154380330

The first attempt shows a hint: the famous "A value is trying to be set on a copy of a slice from a DataFrame". It's a tricky part of Pandas, which tries to tell you that you're not working with the dataframe itself.

In this case, it becomes more clear when you omit inplace:

mydataset_df = pd.DataFrame({'lag': [1, 2, 3, pd.NA], 'foo': [2, 4, pd.NA, 8]})
print(mydataset_df[mydataset_df.columns.difference(['lag'])].fillna("Not Available"))

             foo
0              2
1              4
2  Not Available
3              8

fillna was not passed the entire dataframe, only a reference to the columns. That's explained in details here: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy . In short, the result is "unpredictable"... Confusing for sure.

Using the same idea, but making it safe, you can just replace the columns in the dataframe with:

mydataset_df[mydataset_df.columns.difference(['lag'])] = mydataset_df[mydataset_df.columns.difference(['lag'])].fillna("Not Available")
user_29791150
Related