DataFrame.fillna() fails with categorical columns present when trying to fill missing values in numeric columns with NaNs

[dragoljub]

Code Sample, a copy-pastable example if possible

import numpy as np
import pandas as pd
df = pd.DataFrame(np.random.randint(0, 3, (3, 3)), columns=list('ABC')).astype(np.float32)
df.B.iloc[1] = None
df.A = df.A.astype('category')

df.fillna(-9999)

---------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
<ipython-input-8-dcdff14fcba0> in <module>
----> 1 df.fillna(-9999)

D:\Python37\lib\site-packages\pandas\core\frame.py in fillna(self, value, method, axis, inplace, limit, downcast, **kwargs)
   3788                      self).fillna(value=value, method=method, axis=axis,
   3789                                   inplace=inplace, limit=limit,
-> 3790                                   downcast=downcast, **kwargs)
   3791 
   3792     @Appender(_shared_docs['replace'] % _shared_doc_kwargs)

D:\Python37\lib\site-packages\pandas\core\generic.py in fillna(self, value, method, axis, inplace, limit, downcast)
   5425                 new_data = self._data.fillna(value=value, limit=limit,
   5426                                              inplace=inplace,
-> 5427                                              downcast=downcast)
   5428             elif isinstance(value, DataFrame) and self.ndim == 2:
   5429                 new_data = self.where(self.notna(), value)

D:\Python37\lib\site-packages\pandas\core\internals.py in fillna(self, **kwargs)
   3706 
   3707     def fillna(self, **kwargs):
-> 3708         return self.apply('fillna', **kwargs)
   3709 
   3710     def downcast(self, **kwargs):

D:\Python37\lib\site-packages\pandas\core\internals.py in apply(self, f, axes, filter, do_integrity_check, consolidate, **kwargs)
   3579 
   3580             kwargs['mgr'] = self
-> 3581             applied = getattr(b, f)(**kwargs)
   3582             result_blocks = _extend_blocks(applied, result_blocks)
   3583 

D:\Python37\lib\site-packages\pandas\core\internals.py in fillna(self, value, limit, inplace, downcast, mgr)
   2004                mgr=None):
   2005         values = self.values if inplace else self.values.copy()
-> 2006         values = values.fillna(value=value, limit=limit)
   2007         return [self.make_block_same_class(values=values,
   2008                                            placement=self.mgr_locs,

D:\Python37\lib\site-packages\pandas\util\_decorators.py in wrapper(*args, **kwargs)
    176                 else:
    177                     kwargs[new_arg_name] = new_arg_value
--> 178             return func(*args, **kwargs)
    179         return wrapper
    180     return _deprecate_kwarg

D:\Python37\lib\site-packages\pandas\core\arrays\categorical.py in fillna(self, value, method, limit)
   1754             elif is_hashable(value):
   1755                 if not isna(value) and value not in self.categories:
-> 1756                     raise ValueError("fill value must be in categories")
   1757 
   1758                 mask = values == -1

ValueError: fill value must be in categories

Problem description

fillna() fails on a data frame that has categorical columns without any missing values and numeric columns that have some missing values. It appears that fillna is attempting to fill categorical columns even though they have no missing values.

Expected Output

I expected the missing value in the numeric column to be filled but the categorical columns without any missing values to be untouched.

Output of pd.show_versions()

INSTALLED VERSIONS

commit: None
python: 3.7.1.final.0
python-bits: 64
OS: Windows
OS-release: 2008ServerR2
machine: AMD64
processor: Intel64 Family 6 Model 63 Stepping 2, GenuineIntel
byteorder: little
LC_ALL: None
LANG: None
LOCALE: None.None

pandas: 0.23.4
pytest: 4.0.0
pip: 18.1
setuptools: 40.6.2
Cython: 0.29
numpy: 1.15.4
scipy: 1.1.0
pyarrow: 0.11.1
xarray: 0.11.0
IPython: 7.1.1
sphinx: 1.8.2
patsy: 0.5.1
dateutil: 2.7.5
pytz: 2018.7
blosc: 1.6.2
bottleneck: 1.2.1
tables: 3.4.4
numexpr: 2.6.8
feather: None
matplotlib: 3.0.2
openpyxl: 2.5.10
xlrd: 1.1.0
xlwt: 1.3.0
xlsxwriter: None
lxml: 4.2.5
bs4: 4.6.3
html5lib: 1.0.1
sqlalchemy: 1.2.14
pymysql: None
psycopg2: None
jinja2: 2.10
s3fs: None
fastparquet: 0.1.6
pandas_gbq: None
pandas_datareader: None

[TomAugspurger]

I think we would like to have the invariant that

df.fillna(scalar)

is equivalent to df.fillna({col: scalar for co in df.columns}).

It appears that fillna is attempting to fill categorical columns even though they have no missing values.

We try to avoid value-dependent behavior wherever possible. We've found that raising exceptions or not, depending on the values, can be surprising and often requires expensive checks.

[dragoljub]

OK. So then the question becomes should pd.Categorical([1,2,3]).fillna(0) rais a ValueError even if there are no missing values to fill, regardless if we are trying to fill a non-existing category?

[TomAugspurger]

I think it should raise since we don’t want that do depend on the values, just the type.

…

________________________________ From: Gagi <notifications@github.com> Sent: Monday, December 3, 2018 17:09 To: pandas-dev/pandas Cc: Tom Augspurger; Comment Subject: Re: [pandas-dev/pandas] DataFrame.fillna() fails with categorical columns present when trying to fill missing values in numeric columns with NaNs (#24079) OK. So then the question becomes should pd.Categorical([1,2,3]).fillna(0) rais a ValueError even if there are no missing values, regardless if we are trying to fill a non-existing category? — You are receiving this because you commented. Reply to this email directly, view it on GitHub<#24079 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/ABQHIs_ZhtAKYc4aLXr2pudl_V9sQPxHks5u1a8jgaJpZM4Y_kg2>.

[dragoljub]

I agree it should not depend on values, but if the sate of the categorical columns is col.isnull().all() == False should we just pass back the untouched categorical series? Maybe I'm missing how the underlying fillna() is implemented in Categorical, but I would assume if there is nothing to fill it should return the existing/untouched Catagorical series. That should play nice with the higher-level DataFrame.isnull() call.

[TomAugspurger]

You need to look at the values to see if there are any missing though. I’d recommend adding the fill value as a valid category early on in your pipeline.

…

________________________________ From: Gagi <notifications@github.com> Sent: Monday, December 3, 2018 18:04 To: pandas-dev/pandas Cc: Tom Augspurger; Comment Subject: Re: [pandas-dev/pandas] DataFrame.fillna() fails with categorical columns present when trying to fill missing values in numeric columns with NaNs (#24079) I agree it should not depend on values, but if the sate of the categorical columns is col.isnull().all() == False should we just pass back the untouched categorical series? Maybe I'm missing how the underlying fillna() is implemented in Categorical, but I would assume if there is nothing to fill it should return the existing/untouched Catagorical series. That should play nice with the higher-level DataFrame.isnull() call. — You are receiving this because you commented. Reply to this email directly, view it on GitHub<#24079 (comment)>, or mute the thread<https://github.com/notifications/unsubscribe-auth/ABQHIkqYvF6U1sEJ2qfZPnV5-AvUGXNZks5u1bv8gaJpZM4Y_kg2>.

[dragoljub]

Actually, the issue is I do not want to add a not-needed 0 category fill-value to the categorical columns with no missing values, but want to have the ability to fill the numeric column nulls with zeros (or something else). Maybe its too much/specific to ask this of a mixed-type DF. 😄

I thought pd.Categorical had a 'missing' category that is known to exist if some values are missing and it is part of the valid category levels. So you would not have to check all values just levels.

I see what you are saying, but with DataFrames with 3-5 context columns (with no missings), and 100s of numeric columns that you may want to fillna() with a shorthand it becomes impossible without sub selecting/looping through column types. Even if your Categorical columns have no missing at all it will fail. If there are missings in the categorical columns, sure then the fill level category must be added before fillna. 🤔

[TomAugspurger]

I think we're stuck here, between the following to desirable properties

1. df.fillna(scalar) should be equivalent to df.fillna({col: scalar for col in df.columns})
2. The success of Series[categorical].fillna(value) should depend only on the type, not the values.

I'd recommend something like

In [5]: df.fillna(pd.Series(9999, index=df.select_dtypes(exclude='category').columns))
Out[5]:
     A       B    C
0  2.0     2.0  1.0
1  1.0  9999.0  0.0
2  1.0     1.0  0.0

[dragoljub]

Does select_drypes create a copy?

Any way, there are several workarounds. If collectively we don’t think it’s a bug I’m fine with closing this. Thanks.

[rpeys]

Hi, I'm having this same issue. I want to color-annotate the rows in my clustermap using a column of categorical data, which has no missing values, but I get "ValueError: fill value must be in categories." Is there a recommended workaround?

[cmcouto-silva]

There's a recent asked question about it in StackOverflow with some workarounds. I, personally, would be glad with a fixing about this bug.