Map index of one dataframe to column of another dataframe-CodePudding

I know there are questions similar to this but they do not answer my question, so kindly bear with me.

I have 2 data frames df1 and df2.

In df1, the Order ID1 is the index and the Payment column is empty.

print(df1)

Order ID1    Sale Price     Payment
OD1             45            
OD2             55
OD3             56

In df2, the Order ID2 is the index.

print(df2)

Order ID2     paid value
OD1             44
OD3             41
OD2             33

I am trying to map the index of df1 (Order ID1) to the index of df2 (Order ID2) and return the corresponding paid value in the empty payment column. Basically I am performing a look up procedure.

I tried using the map function as follows:-

df2['Payment'] = df2.index.map(df1.index)

but I am getting the following error:

TypeError: 'Index' object is not callable

CodePudding user response：

That's because you are trying to map the index. You first need to create a dictionary with keys being the index of df2 and values being the paid value using dict(zip()). Then you can map that on df1.index and return it into your Payment column:

df1['payment'] = df1.index.map(dict(zip(df2.index,df2['paid value'])))

           Sale Price  payment
Order ID1                     
OD1                45       44
OD2                55       33
OD3                56       41

CodePudding user response：

Update: if Order IDX is already the index of your 2 dataframes, use simply assignment:

df1['Payment'] = df2['paid value']
print(df1)

# Output:
           Sale Price  Payment
Order ID1                     
OD1                45       44
OD2                55       33
OD3                56       41

Old answer

df1['Payment'] = df1['Order ID1'].map(df2.set_index('Order ID2').squeeze())
print(df1)

# Output:
  Order ID1  Sale Price  Payment
0       OD1          45       44
1       OD2          55       33
2       OD3          56       41