BUG: DataFrame.at setter of categorical DF overwrites entire row

[treszkai]

• I have checked that this issue has not already been reported.

• I have confirmed this bug exists on the latest version of pandas.

• (optional) I have confirmed this bug exists on the master branch of pandas.

---

On a DataFrame with categorical dtype, df.at[x,y] = v sets all non-initialized values in row x.

Code Sample, a copy-pastable example

$ python
Python 3.8.0 (default, Oct 28 2019, 16:14:01) 
[GCC 8.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.

>>> import pandas as pd
>>> pd.__version__
'1.2.0.dev0+1137.g50b34a4a8'
>>> df = pd.DataFrame(index=range(3), columns=range(3), dtype=pd.CategoricalDtype(['foo', 'bar']))
>>> df.at[1,2] = 'foo'
>>> df
     0    1    2
0  NaN  NaN  NaN
1  foo  foo  foo
2  NaN  NaN  NaN

It doesn't overwrite values that have been set with df.loc:

>>> df = pd.DataFrame(index=range(3), columns=range(3), dtype=pd.CategoricalDtype(['foo', 'bar']))
>>> df.loc[1,1] = 'bar'  # not necessary, just for demo
>>> df.at[1,2] = 'foo'
>>> df
     0    1    2
0  NaN  NaN  NaN
1  foo  bar  foo
2  NaN  NaN  NaN

Problem description

df.at[x, y] = v on a categorical dtype should behave as with other dtypes, and the same as df.loc[x, y] = v.

Expected Output

The same as what happens with a DF initialized with Nones:

>>> df = pd.DataFrame([[None] * 3] * 3, index=range(3), columns=range(3), dtype=pd.CategoricalDtype(['foo', 'bar']))
>>> df.loc[1,1] = 'bar'
>>> df.at[1,2] = 'foo'
>>> df
     0    1    2
0  NaN  NaN  NaN
1  NaN  bar  foo
2  NaN  NaN  NaN

Or as with dtype=float:

>>> df = pd.DataFrame(index=range(3), columns=range(3), dtype=float)
>>> df.loc[1,1] = 1
>>> df.at[1,2] = 27
>>> df
    0    1     2
0 NaN  NaN   NaN
1 NaN  1.0  27.0
2 NaN  NaN   NaN

Output of pd.show_versions()

INSTALLED VERSIONS

• ------------------
• commit : 50b34a4
• python : 3.8.0.final.0
• python-bits : 64
• OS : Linux
• OS-release : 5.4.0-52-generic
• Version : fillna bug #57~18.04.1-Ubuntu SMP Thu Oct 15 14:04:49 UTC 2020
• machine : x86_64
• processor : x86_64
• byteorder : little
• LC_ALL : None
• LANG : en_US.UTF-8
• LOCALE : en_US.UTF-8
• pandas : 1.2.0.dev0+1137.g50b34a4a8
• numpy : 1.19.4
• pytz : 2020.4
• dateutil : 2.8.1
• pip : 20.2.4
• setuptools : 50.3.2
• Cython : None
• pytest : None
• hypothesis : None
• sphinx : None
• blosc : None
• feather : None
• xlsxwriter : None
• lxml.etree : None
• html5lib : None
• pymysql : None
• psycopg2 : None
• jinja2 : None
• IPython : None
• pandas_datareader: None
• bs4 : None
• bottleneck : None
• fsspec : None
• fastparquet : None
• gcsfs : None
• matplotlib : None
• numexpr : None
• odfpy : None
• openpyxl : None
• pandas_gbq : None
• pyarrow : None
• pyxlsb : None
• s3fs : None
• scipy : None
• sqlalchemy : None
• tables : None
• tabulate : None
• xarray : None
• xlrd : None
• xlwt : None
• numba : None

[GYHHAHA]

What's the expected output with dtype=float? I think your example returns the right result. @treszkai

[treszkai]

Yes, the problem is only with categorical dtype. The Expected Output section compares it to two scenarios that work well, i.e. pre-intialized or non-dtype. Also could've said that "Expected Output: DataFrame.at setter sets a single item, not the entire row."

[treszkai]

Setting with df.at will call DataFrame._set_value, which on the series = self._get_item_cache(col) line Series with the exact same _values regardless if col is 0 or 2:

>>> df = pd.DataFrame(index=range(3), columns=range(3), dtype=pd.CategoricalDtype(['foo', 'bar']))
>>> df.at[0,0] = 'foo'
>>> df[0]._values is df[2]._values
True

When setting with df.loc, in the _iLocIndexer._setitem_with_indexer method take_split_path will be true (with categorical dtype), which copies the entire column on every setitem, that's why loc isn't affected.

# set the item, possibly having a dtype change
ser = ser.copy()
ser._mgr = ser._mgr.setitem(indexer=pi, value=v)
ser._maybe_update_cacher(clear=True)

[ma3da]

Yes, I'd say this follows from two things:

• when your DF is instantiated, only one "nan array" is created, which will be associated with all its columns, as they were provided no data (here),
• in the case of categorical columns, the block manager keeps one block per column, which will result in underlying values being the same object (the unique "nan array") (here).

I'm tempted to say the way to correct this would be to create one "nan array" per empty column, but there may be a memory-efficient solution.

[treszkai]

to create one "nan array" per empty column, but there may be a

memory-efficient solution. In what situation is it necessary to spare memory temporarily, until the elements of the DataFrame are set? When the user asked for a categorical dtype for the whole DF, it's reasonable to assume that all columns will be used as such, no? Luis Pinto <[email protected]> schrieb am Mo., 16. Nov. 2020, 10:29:

…

Yes, I'd say this follows from two things: - when your DF is instantiated, only one "nan array" is created, which will be associated with all its columns, as they were provided no data ( here <https://github.com/pandas-dev/pandas/blob/c77fc357efbe9443ad8e32b9ce34599e61bcd3f5/pandas/core/internals/construction.py#L271-L272> ), - in the case of categorical columns, the block manager keeps one block per column, which will result in underlying values being the same object (the unique "nan array") (here <https://github.com/pandas-dev/pandas/blob/c77fc357efbe9443ad8e32b9ce34599e61bcd3f5/pandas/core/internals/managers.py#L1771-L1776> ). I'm tempted to say the way to correct this would be to create one "nan array" per empty column, but there may be a memory-efficient solution. — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#37763 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AHJJPFQQQH3P62WX5KU4VALSQDWIZANCNFSM4TSMNBLQ> .

[ma3da]

@treszkai I do agree with you. But I'm not versed enough in the library's internals to feel confident there are no subtleties I'm missing :)

[ma3da]

This issue seems to have been solved by #37355.
On master:

In [1]: import pandas as pd

In [2]: pd.__version__
Out[2]: '1.2.0.dev0+1320.g28634289c'

In [3]: df = pd.DataFrame(index=range(3), columns=range(3), dtype=pd.CategoricalDtype(['foo', 'bar']))

In [4]: df.at[1,2] = 'foo'

In [5]: df
Out[5]: 
     0    1    2
0  NaN  NaN  NaN
1  NaN  NaN  foo
2  NaN  NaN  NaN

[jreback]

would take a validation test to close this issue