BUG: DataFrame.at fails when setting a whole row

[Jeaksen]

Pandas version checks

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

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

• I have confirmed this bug exists on the main branch of pandas.

Reproducible Example

import pandas as pd

df = pd.DataFrame(index=['a'], columns=['col1', 'col2'])

df.at['a'] = ['123', '15']
print(df)

Traceback (most recent call last):
  File "/home/*/.local/lib/python3.10/site-packages/pandas/core/indexes/base.py", line 3800, in get_loc
    return self._engine.get_loc(casted_key)
  File "pandas/_libs/index.pyx", line 138, in pandas._libs.index.IndexEngine.get_loc
  File "pandas/_libs/index.pyx", line 144, in pandas._libs.index.IndexEngine.get_loc
TypeError: 'slice(None, None, None)' is an invalid key

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/*/pandas_bug.py", line 6, in <module>
    df.at['a'] = ['123', '15']
  File "/home/*/.local/lib/python3.10/site-packages/pandas/core/indexing.py", line 2438, in __setitem__
    return super().__setitem__(key, value)
  File "/home/*/.local/lib/python3.10/site-packages/pandas/core/indexing.py", line 2393, in __setitem__
    self.obj._set_value(*key, value=value, takeable=self._takeable)
  File "/home/*/.local/lib/python3.10/site-packages/pandas/core/frame.py", line 4208, in _set_value
    icol = self.columns.get_loc(col)
  File "/home/*/.local/lib/python3.10/site-packages/pandas/core/indexes/base.py", line 3807, in get_loc
    self._check_indexing_error(key)
  File "/home/*/.local/lib/python3.10/site-packages/pandas/core/indexes/base.py", line 5963, in _check_indexing_error
    raise InvalidIndexError(key)
pandas.errors.InvalidIndexError: slice(None, None, None)

Issue Description

Trying to set a row in a DataFrame using DataFrame.at fails on pandas 1.5.0. It worked on pandas 1.4.X.

Setting a single value (i.e. df.at['a', 'col1'] = '15') works fine.
Setting the row with loc works as well (i.e. df.loc['a'] = ['123', '15']).

Expected Behavior

The row values should be set:

  col1 col2
a  123   15

Installed Versions

INSTALLED VERSIONS

commit : 87cfe4e
python : 3.10.4.final.0
python-bits : 64
OS : Linux
OS-release : 5.10.16.3-microsoft-standard-WSL2
Version : #1 SMP Fri Apr 2 22:23:49 UTC 2021
machine : x86_64
processor : x86_64
byteorder : little
LC_ALL : None
LANG : en_US.UTF-8
LOCALE : en_US.UTF-8

pandas : 1.5.0
numpy : 1.23.1
pytz : 2022.1
dateutil : 2.8.2
setuptools : 59.6.0
pip : 22.0.2
Cython : 0.29.32
pytest : 7.1.2
hypothesis : None
sphinx : None
blosc : None
feather : None
xlsxwriter : None
lxml.etree : 4.8.0
html5lib : 1.1
pymysql : None
psycopg2 : 2.9.3
jinja2 : 3.0.3
IPython : None
pandas_datareader: None
bs4 : 4.10.0
bottleneck : None
brotli : None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : None
numba : None
numexpr : None
odfpy : None
openpyxl : 3.0.10
pandas_gbq : None
pyarrow : None
pyreadstat : None
pyxlsb : None
s3fs : None
scipy : 1.8.1
snappy : None
sqlalchemy : 1.4.39
tables : None
tabulate : None
xarray : None
xlrd : None
xlwt : None
zstandard : None
tzdata : None

[MarcoGorelli]

Hey @Jeaksen , thanks for the report

From git bisect, looks this was caused by #47074

[MarcoGorelli]

cc @jorisvandenbossche

[phofl]

The new behavior is correct. at should only work on single values, not on a complete row

[Jeaksen]

I would assume from the name, that the function at should support accessing one row using the index, but maybe that's just me. In that case maybe there should be some warning/assert, as it was supported before and the error is really misleading?

From what I can see __setitem__ in _ScalarAccessIndexer is explicitly converting the key, so that it has the right amount of dimensions, only the _set_value in DataFrame stopped supporting the empty col argument. Is the _tuplify in __setitem__ still needed then?

[phofl]

This is explicitly mentioned in the docs

https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.at.html

We have some inconsistencies there, but the expected behavior is that this does not work

[MarcoGorelli]

Thanks

Not a blocker for 1.5.1 then, but I do think a nicer error message would be good

e.g. with __getitem__, it's very clear that you need to set a column as well:

In [4]: df.at['a']
---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
Cell In [4], line 1
----> 1 df.at['a']

File ~/pandas-dev/pandas/core/indexing.py:2436, in _AtIndexer.__getitem__(self, key)
   2433         raise ValueError("Invalid call for scalar access (getting)!")
   2434     return self.obj.loc[key]
-> 2436 return super().__getitem__(key)

File ~/pandas-dev/pandas/core/indexing.py:2387, in _ScalarAccessIndexer.__getitem__(self, key)
   2384         raise ValueError("Invalid call for scalar access (getting)!")
   2386 key = self._convert_key(key)
-> 2387 return self.obj._get_value(*key, takeable=self._takeable)

TypeError: _get_value() missing 1 required positional argument: 'col'

[jorisvandenbossche]

It's indeed documented that .at is only for scalar access, but in practice we are also not very strict about it ..

If we would first want to warn about it before changing, we could quite easily catch that error, and the following rolls back the behaviour for this case:

--- a/pandas/core/frame.py
+++ b/pandas/core/frame.py
@@ -4228,7 +4230,7 @@ class DataFrame(NDFrame, OpsMixin):
             self._mgr.column_setitem(icol, iindex, value)
             self._clear_item_cache()
 
-        except (KeyError, TypeError, ValueError):
+        except (KeyError, TypeError, ValueError, InvalidIndexError):
             # get_loc might raise a KeyError for missing labels (falling back
             #  to (i)loc will do expansion of the index)
             # column_setitem will do validation that may raise TypeError or ValueError

However, I think catching all InvalidIndexErrors might also make .at even more flexible than before .. (in pandas 1.4, we only called get_loc (which raises this error) for the index, and not for the columns; so catching this error generally would also allow a slice for the columns)

A better error message would in any case be useful. Based on the above patch, I think we could catch InvalidIndexError separately, and then reraise it with a better error message.

[ambujpawar]

take