Map index of one dataframe to column of another dataframe

Question

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

I'd suggest using pandas.merge to join the two DataFrames on the ID column. This would give you a new DataFrame with all three columns. — Bashton
– Bashton, Commented Dec 23, 2021 at 9:25
Is there a particular advantage of using merge instead of map function? — Gee
– Gee, Commented Dec 23, 2021 at 9:46

sophocles · Accepted Answer · 2021-12-23 09:30:11Z

1

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

answered Dec 23, 2021 at 9:30

sophocles

13.9k3 gold badges18 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Corralien · Accepted Answer · 2021-12-23 09:34:13Z

1

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

edited Dec 23, 2021 at 9:34

answered Dec 23, 2021 at 9:27

Corralien

121k8 gold badges44 silver badges69 bronze badges

Collectives™ on Stack Overflow

Map index of one dataframe to column of another dataframe

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related