BUG: Setting values on DataFrame with multi-index .loc(axis=0) access adds columns

[btschroer]

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

dates = pd.date_range("2001-01-01", freq="D", periods=2)
ids = ["i1", "i2"]
index = pd.MultiIndex.from_product([dates, ids], names=["date", "identifier"])
frame = pd.DataFrame(0.0, index=index, columns=["A", "B"])
frame.loc(axis=0)["2001-01-01", ["i1", "i2"]] = None
frame
>>>                         A    B i1 i2
date       identifier                      
2001-01-01 i1           NaN  NaN  None  None
           i2           NaN  NaN  None  None
2001-01-02 i1           0.0  0.0   NaN   NaN
           i2           0.0  0.0   NaN   NaN

Issue Description

Setting a multi-indexed DataFrame adds columns.

Expected Behavior

No columns added, i.e. behaviour as outlined in https://pandas.pydata.org/docs/user_guide/advanced.html#advanced-indexing-with-hierarchical-index.

Installed Versions

NSTALLED VERSIONS

commit : bdc79c1
python : 3.10.14.final.0
python-bits : 64
OS : Darwin
OS-release : 23.4.0
Version : Darwin Kernel Version 23.4.0: Wed Feb 21 21:44:43 PST 2024; root:xnu-10063.101.15~2/RELEASE_ARM64_T6000
machine : arm64
processor : arm
byteorder : little
LC_ALL : None
LANG : None
LOCALE : None.UTF-8

pandas : 2.2.1
numpy : 1.26.4
pytz : 2024.1
dateutil : 2.9.0
setuptools : 69.2.0
pip : 24.0
Cython : None
pytest : 7.4.4
hypothesis : None
sphinx : 6.2.1
blosc : None
feather : None
xlsxwriter : None
lxml.etree : None
html5lib : None
pymysql : None
psycopg2 : None
jinja2 : 3.1.3
IPython : 8.22.2
pandas_datareader : None
adbc-driver-postgresql: None
adbc-driver-sqlite : None
bs4 : 4.12.3
bottleneck : None
dataframe-api-compat : None
fastparquet : None
fsspec : None
gcsfs : None
matplotlib : 3.7.3
numba : 0.59.0
numexpr : 2.9.0
odfpy : None
openpyxl : None
pandas_gbq : None
pyarrow : 14.0.2
pyreadstat : None
python-calamine : None
pyxlsb : None
s3fs : None
scipy : 1.12.0
sqlalchemy : None
tables : 3.9.2
tabulate : 0.9.0
xarray : None
xlrd : None
zstandard : None
tzdata : 2024.1
qtpy : None
pyqt5 : None

[eshaready]

take

[btschroer]

take

@eshaready What do you mean, by 'take'?

[eshaready]

It means I assigned myself to the issue!

[btschroer]

Great thanks a lot for looking into this.

[eshaready]

Hey! It looks like for the behavior you want, you should be using the syntax
frame.loc[("2001-01-01", "i1"):("2001-01-01", "i2")] = None
I think this produces the behavior you're looking for, slicing multiple rows and assigning them to None. The syntax you used checks for i1 and i2 in columns, and since they aren't columns it adds them.

[btschroer]

Thanks a lot @eshaready for looking into this and suggesting a fix. I see a couple of caveats with the approach you suggested, maybe my example was a bit too oversimplified. Let me try to extend it a bit to maybe better bring over the point:

import pandas as pd

dates = pd.date_range("2001-01-01", freq="D", periods=2)
ids = ["i1", "i2", "i3"]
index = pd.MultiIndex.from_product([dates, ids], names=["date", "identifier"])
frame = pd.DataFrame(0.0, index=index, columns=["A", "B"])
frame.loc(axis=0)["2001-01-01", ["i1", "i3"]] = None
frame
>>>                          A    B    i1    i3
date       identifier                      
2001-01-01 i1          NaN  NaN  None  None
           i2          0.0  0.0   NaN   NaN
           i3          NaN  NaN  None  None
2001-01-02 i1          0.0  0.0   NaN   NaN
           i2          0.0  0.0   NaN   NaN
           i3          0.0  0.0   NaN   NaN

In this example I cannot 'slice' the second multilevel, since I do not want to modify rows where identifier is "i2".

Also I am using .loc(axis=0) which clearly indicates that I am selecting based on the index alone, so there must be no change in the original frame's columns effected by this call.

The documentation, https://pandas.pydata.org/docs/user_guide/advanced.html#advanced-indexing-with-hierarchical-index, also suggest that the syntax I am using is valid and should not modify the column schema of the frame:

Importantly, a list of tuples indexes several complete MultiIndex keys, whereas a tuple of lists refer to several values within a level:

In [48]: s = pd.Series(
   ....:     [1, 2, 3, 4, 5, 6],
   ....:     index=pd.MultiIndex.from_product([["A", "B"], ["c", "d", "e"]]),
   ....: )
   ....: 

In [49]: s.loc[[("A", "c"), ("B", "d")]]  # list of tuples
Out[49]: 
A  c    1
B  d    5
dtype: int64

In [50]: s.loc[(["A", "B"], ["c", "d"])]  # tuple of lists
Out[50]: 
A  c    1
   d    2
B  c    4
   d    5
dtype: int64