Pandas version checks
-
[x] I have checked that this issue has not already been reported.
-
[x] 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
df = pd.DataFrame(data={("column_1", ""): [1,1], ("column_2", ""): [2,2]})
df.index = pd.MultiIndex.from_arrays([[1,1], ["metadata_1", "metadata_2"]], names=["index", "metadata"])
df2 = pd.DataFrame(data=[1,1], index=[1,1]).rename_axis("index", axis=0)
df2.columns = pd.MultiIndex.from_product([["new_data"], [""]])
df.reset_index().merge(df2.reset_index(), on="index")
# IndexError: Requested axis not found in manager
df.reset_index().merge(df2.reset_index(), on=[("index", "")])
# IndexError: Requested axis not found in manager
df.reset_index().columns
# MultiIndex([( 'index', ''),
# ('metadata', ''),
# ('column_1', ''),
# ('column_2', '')],
# )
df2.reset_index().columns
# MultiIndex([( 'index', ''),
# ('new_data', '')],
# )
## We have a workaround if we force df2 to start as a multi-index, then it works
df2 = pd.DataFrame(data=[1,1], index=[1,1]).rename_axis("index", axis=0)
df2.columns = pd.MultiIndex.from_product([["new_data"], [""]])
df2.index = pd.MultiIndex.from_arrays(
[df2.index, ["dummy"] * len(df2)],
names=["index", "dummy_index"],
)
# Merge works
df.reset_index().merge(df2.reset_index(), on=[("index", "")])
# Extra empty multi-index column
df2.reset_index().columns
# MultiIndex([( 'index', ''),
# ('dummy_index', ''),
# ('new_data', '')],
# )
Issue Description
I have a dataframe (main) with multi-indexed index and columns. I would like to do a merge with a second dataframe (side) that is single-index and single column.
I coerced (side) to give it the same levels in the columns, and do a pd.merge on (main).reset_index and side.reset_index.
The example works if you make (side) also a multi-index index.
Additionally, the first example above is fine on 2.2.3, but is broken on 2.3.1
Expected Behavior
Usual merge behaviour. Not sure why the initial dataframe must both have multi-index indexes when we use .reset_index() on both and merge over a single column