Understanding behavior of pandas join operator with how="right"

user_17644392 0
Viewed 41

I need some help understanding the behavior of pandas "join" operator. When running the code below, I would expect a different output.

What bothers me is that the index column of the left dataframe gets turned into a column, which includes a value that was not there in the original left dataframe. Also, the new index has no name although it contains the same values as the previous "index_left". Why does pandas behave like this? Is my expectation wrong?

Code:

import pandas as pd

df_left = pd.DataFrame(data=["value_left_1"], index=(["index_1"]), columns=["col_left_1"])
df_left.index.name = "index_left"
    
df_right = pd.DataFrame(data=[["value_right_1"],["value_right_2"]], index=(["index_1","index_2"]), columns=["col_right1"])
df_right.index.name = "index_right"
    
print(df_left.join(df_right, on="index_left", how="right"))

Actual output:

        index_left    col_left_1     col_right1
index_1    index_1  value_left_1  value_right_1
NaN        index_2           NaN  value_right_2

Expectation:

           col_left_1     col_right1
index_left
index_1    value_left_1  value_right_1
NaN        NaN           value_right_2
indices pandas dataframe join
1 Answers

To obtain the desired output, you may need to explicitly set the index to index_left:

import pandas as pd

df_left = pd.DataFrame(data=["value_left_1"], index=(["index_1"]), columns=["col_left_1"])
df_left.index.name = "index_left"
    
df_right = pd.DataFrame(data=[["value_right_1"],["value_right_2"]], index=(["index_1","index_2"]), columns=["col_right1"])
df_right.index.name = "index_right"
    
print(df_left.join(df_right, on="index_left", how="right").set_index('index_left'))

output:

             col_left_1     col_right1
index_left                             
index_1     value_left_1  value_right_1
index_2              NaN  value_right_2
user_29649630
Related