BUG: merge drops index components sometimes, when join is empty

[phofl]

• 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.

---

Note: Please read this guide detailing how to provide the necessary information for us to reproduce your bug.

Code Sample, a copy-pastable example

import pandas as pd

left = pd.DataFrame({'a': [1], 'i': [1]}).set_index(['a', 'i'])
right = pd.DataFrame({'i': [1]}).set_index(['i'])

df = pd.merge(
    left,
    right, 
    left_on="i",
    right_on="i",
    how="left")
print(df)
print(df.reset_index())

df = pd.merge(
    left,
    right,    
    right_index=True,
    left_on="i",
    how="left")
print(df)
print(df.reset_index())

Problem description

The first merge changes the index of the left DataFrame in a way, that the index component a is dropped.

Empty DataFrame
Columns: []
Index: [1]


   i
0  1

The second merge returns the original index respectively DataFrame after resetting the index. I think this is the expected output.

Expected Output

Empty DataFrame
Columns: []
Index: [(1, 1)]


   a  i
0  1  1

Output of pd.show_versions()

Was tested on master.

[vampypandya]

@phofl
I am not sure of the merging method is right.
Lets see,

left = pd.DataFrame([[1,1,2]],columns=['a','i','j'])
right = pd.DataFrame([[1,3]],columns=['a','p'])
x = left.merge(right,left_on='a',right_on='a',how='right')

**Output**
   a  i  j  p
0  1  1  2  3

Try using this.
I hope so this clears the issue.

[phofl]

You are right, the merge method should be left. That was a copy and paste bug. If you change the DataFrames left and right, the issue also occurs if you perform a right join (this is of course equivalent to the left join noted above).

Your example is uncorrelated with mine. If you perform your merge over the index, the inconsistent behavior occurs. I know that you could your transform your data in a way that it works, but the inconsistent behavior is still there.

[TomAugspurger]

@phofl is your issue the same as #34292?

[phofl]

@TomAugspurger

No, looks alike, but is different.

#34292 runs into

        if self.left_index and self.right_index and self.how != "asof":
            join_index, left_indexer, right_indexer = left_ax.join(
                right_ax, how=self.how, return_indexers=True, sort=self.sort
            )

This is in merge.py line 870

The error occurs somewhere in there. Mine runs into the else block.