BUG: setting item to iterable with .at fails when column doesn't exist or has wrong dtype

[jbogar]

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(data=[[1,2],[3,4]],index=["a","b"],columns=["A","B"])
df.at["a","C"]=[1,2,3]

Issue Description

When using .at to set cell value to an iterable, it fails if it has to create a new column.

It works fine if setting the cell value to a scalar (like df.at["a","C"]=1).

It also fails if the column exists but is of the wrong dtype.

This fails:
df.at["a","A"]=[1,2,3]

But this works:

df.loc[:,"A"]=df.A.astype(object)
df.at["a","A"]=[1,2,3]

The error trace:

KeyError                                  Traceback (most recent call last)
File ~/miniconda3/lib/python3.12/site-packages/pandas/core/indexes/base.py:3805, in Index.get_loc(self, key)
   3804 try:
-> 3805     return self._engine.get_loc(casted_key)
   3806 except KeyError as err:

File index.pyx:167, in pandas._libs.index.IndexEngine.get_loc()

File index.pyx:196, in pandas._libs.index.IndexEngine.get_loc()

File pandas/_libs/hashtable_class_helper.pxi:7081, in pandas._libs.hashtable.PyObjectHashTable.get_item()

File pandas/_libs/hashtable_class_helper.pxi:7089, in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: 'C'

The above exception was the direct cause of the following exception:

KeyError                                  Traceback (most recent call last)
File ~/miniconda3/lib/python3.12/site-packages/pandas/core/frame.py:4561, in DataFrame._set_value(self, index, col, value, takeable)
   4560 else:
-> 4561     icol = self.columns.get_loc(col)
   4562     iindex = self.index.get_loc(index)

File ~/miniconda3/lib/python3.12/site-packages/pandas/core/indexes/base.py:3812, in Index.get_loc(self, key)
   3811         raise InvalidIndexError(key)
-> 3812     raise KeyError(key) from err
   3813 except TypeError:
   3814     # If we have a listlike key, _check_indexing_error will raise
   3815     #  InvalidIndexError. Otherwise we fall through and re-raise
   3816     #  the TypeError.

KeyError: 'C'

During handling of the above exception, another exception occurred:

ValueError                                Traceback (most recent call last)
Cell In[1], line 4
      1 import pandas as pd
      3 df=pd.DataFrame(data=[[1,2],[3,4]],index=["a","b"],columns=["A","B"])
----> 4 df.at["a","C"]=[1,2,3]

File ~/miniconda3/lib/python3.12/site-packages/pandas/core/indexing.py:2586, in _AtIndexer.__setitem__(self, key, value)
   2583     self.obj.loc[key] = value
   2584     return
-> 2586 return super().__setitem__(key, value)

File ~/miniconda3/lib/python3.12/site-packages/pandas/core/indexing.py:2542, in _ScalarAccessIndexer.__setitem__(self, key, value)
   2539 if len(key) != self.ndim:
   2540     raise ValueError("Not enough indexers for scalar access (setting)!")
-> 2542 self.obj._set_value(*key, value=value, takeable=self._takeable)

File ~/miniconda3/lib/python3.12/site-packages/pandas/core/frame.py:4575, in DataFrame._set_value(self, index, col, value, takeable)
   4573         self.iloc[index, col] = value
   4574     else:
-> 4575         self.loc[index, col] = value
   4576     self._item_cache.pop(col, None)
   4578 except InvalidIndexError as ii_err:
   4579     # GH48729: Seems like you are trying to assign a value to a
   4580     # row when only scalar options are permitted

File ~/miniconda3/lib/python3.12/site-packages/pandas/core/indexing.py:911, in _LocationIndexer.__setitem__(self, key, value)
    908 self._has_valid_setitem_indexer(key)
    910 iloc = self if self.name == "iloc" else self.obj.iloc
--> 911 iloc._setitem_with_indexer(indexer, value, self.name)

File ~/miniconda3/lib/python3.12/site-packages/pandas/core/indexing.py:1890, in _iLocIndexer._setitem_with_indexer(self, indexer, value, name)
   1885         self.obj[key] = infer_fill_value(value)
   1887     new_indexer = convert_from_missing_indexer_tuple(
   1888         indexer, self.obj.axes
   1889     )
-> 1890     self._setitem_with_indexer(new_indexer, value, name)
   1892     return
   1894 # reindex the axis
   1895 # make sure to clear the cache because we are
   1896 # just replacing the block manager here
   1897 # so the object is the same

File ~/miniconda3/lib/python3.12/site-packages/pandas/core/indexing.py:1942, in _iLocIndexer._setitem_with_indexer(self, indexer, value, name)
   1939 # align and set the values
   1940 if take_split_path:
   1941     # We have to operate column-wise
-> 1942     self._setitem_with_indexer_split_path(indexer, value, name)
   1943 else:
   1944     self._setitem_single_block(indexer, value, name)

File ~/miniconda3/lib/python3.12/site-packages/pandas/core/indexing.py:1998, in _iLocIndexer._setitem_with_indexer_split_path(self, indexer, value, name)
   1993     if len(value) == 1 and not is_integer(info_axis):
   1994         # This is a case like df.iloc[:3, [1]] = [0]
   1995         #  where we treat as df.iloc[:3, 1] = 0
   1996         return self._setitem_with_indexer((pi, info_axis[0]), value[0])
-> 1998     raise ValueError(
   1999         "Must have equal len keys and value "
   2000         "when setting with an iterable"
   2001     )
   2003 elif lplane_indexer == 0 and len(value) == len(self.obj.index):
   2004     # We get here in one case via .loc with a all-False mask
   2005     pass

ValueError: Must have equal len keys and value when setting with an iterable

Expected Behavior

The cell value is set without errors.

Installed Versions

INSTALLED VERSIONS

commit : 0691c5c
python : 3.9.21
python-bits : 64
OS : Darwin
OS-release : 24.3.0
Version : Darwin Kernel Version 24.3.0: Thu Jan 2 20:24:23 PST 2025; root:xnu-11215.81.4~3/RELEASE_ARM64_T6020
machine : arm64
processor : arm
byteorder : little
LC_ALL : None
LANG : None
LOCALE : None.UTF-8

pandas : 2.2.3
numpy : 2.0.2
pytz : 2025.1
dateutil : 2.8.2
pip : 25.0.1
Cython : None
sphinx : None
IPython : 8.18.0
adbc-driver-postgresql: None
adbc-driver-sqlite : None
bs4 : 4.13.3
blosc : None
bottleneck : None
dataframe-api-compat : None
fastparquet : None
fsspec : 2023.6.0
html5lib : None
hypothesis : None
gcsfs : None
jinja2 : 3.1.6
lxml.etree : 5.3.1
matplotlib : 3.9.4
numba : None
numexpr : 2.10.2
odfpy : None
openpyxl : None
pandas_gbq : None
psycopg2 : None
pymysql : None
pyarrow : 18.1.0
pyreadstat : None
pytest : None
python-calamine : None
pyxlsb : None
s3fs : 2023.6.0
scipy : 1.13.1
sqlalchemy : None
tables : N/A
tabulate : 0.9.0
xarray : None
xlrd : None
xlsxwriter : None
zstandard : None
tzdata : 2023.3
qtpy : None
pyqt5 : None

[yuanx749]

The same errors happen using .loc.

A workaround is specifying the column first:
df["C"] = pd.Series()

[ShayanG9]

Looking at the documentation it seems like this example does not show a bug, since it should throw a KeyError given there is no key "C" already in the dataframe. Moreover, the .at function is specifically used "if you only need to get or set a single value in a DataFrame or Series." This is why it works when we convert the dataframe to a object dtype. The real bug here seems to be that it works when providing a scalar: it should throw a KeyError.

If there is a need to fix .at to throw an error I would be happy to work on it as my first issue.

[jbogar]

@ShayanG9 The documentation says it should throw KeyError when getting, not setting. Userguide specificaly states that it will inflate the dataframe inplace if the key does not exist.

All documentation says it should work the same as .loc, just access only one cell of the dataframe.

@yuanx749 That's the issue, if you look at the trace, it falls back to .loc, which throws this error. But it shouldn't fall back to .loc, it should access a single cell and put a list to it.

[ShayanG9]

It seems like you are right about the KeyError. However, given it should change only one cell then it doesn't make sense to exchange a cell populated by an integer type with a list, like you mention in the line df.at["a","A"]=[1,2,3] Especially when the series is of type int64.

import pandas as pd

df=pd.DataFrame(data=[[1,2],[3,4]],index=["a","b"],columns=["A","B"])
print(df.dtypes)

returns

A    int64
B    int64
dtype: object

However, if we do

import pandas as pd

df=pd.DataFrame(data=[[1,2],[3,4]],index=["a","b"],columns=["A","B"])
df.loc[:,"A"]=df.A.astype(object)
print(df.dtypes)

We would get

FutureWarning: Setting an item of incompatible dtype is deprecated and will raise in a future error of pandas. Value '[1 3]' has dtype incompatible with int64, please explicitly cast to a compatible dtype first.
  df.loc[:,"A"]=df.A.astype(object)
A    object
B     int64
dtype: object

Now that the series is of dtype object we can replace the int64 object with a list object, since they are interchangeable. I'm not sure if there is something I might be missing, but the implementation seems to make sense. Perhaps a note about this behavior in the documentation would be good?

[jbogar]

What you propose would be incompatible with .loc

When you assign incompatible value with .loc, it will throw a future warning, but it will change the dtype to the compatible one. .loc and .at should have consistent behavior.

In [16]: df=pd.DataFrame(data=[[1]], columns=["A"])

In [17]: print(df.dtypes)
A    int64
dtype: object

In [18]: df.loc[0,"A"]="this is string"
<ipython-input-18-dcd67bec8a93>:1: FutureWarning: Setting an item of incompatible dtype is deprecated and will raise an error in a future version of pandas. Value 'this is string' has dtype incompatible with int64, please explicitly cast to a compatible dtype first.
  df.loc[0,"A"]="this is string"

In [19]: df.dtypes
Out[19]: 
A    object
dtype: object

Btw. if you do this example with .at it will also work.

[ShayanG9]

Thanks for clarifying, and sorry for the misunderstanding. I see what you mean now. Looking at this pull request it seems like this behavior is intentional. If you look it seems like it was previously in the code that a more descriptive error would be thrown: "Must have equal len keys and value when setting with an iterable". However, if this should be the behavior I do not know. I did some digging and it seems to be about compatibility with numpy something about this pull request. It might be good to have someone from the pandas team look over this?