Pandas Dataframe: Replace values in a dataframe with values from corresponding column vector of a different dataframe

Question

I have two data frames

DF_1:

Item number   Price   Location
33              $4     Boston
29              $2     NYC

DF_2:

Item number    29    33
Property 1     2.4   4.2
Property 2     5.2   1.9
Property 3     8.9   3.8

I want to replace the Item number in DF_1 with the 3 corresponding properties for it from DF_2, so that the new DF looks like:

New_DF:

Property 1   Property 2    Property 3   Price   Location
4.2           1.9          3.8          $4      Boston
2.4           5.2          8.9          $2      NYC

I've gone through hours of trying to do it and also thoroughly looked through StackOverFlow but couldn't find anything similar. Please help.

jpp · Accepted Answer · 2018-06-11 19:06:39Z

1

One way is to transpose and merge. The only complication is that you need to ensure your merge column / index are both of the same type. Here we cast the index of the transposed dataframe explicitly to int.

df2_t = df2.set_index('Item Number').T
df2_t.index = df2_t.index.astype(int)

res = df1.merge(df2_t, left_on='Item Number', right_index=True)

print(res)

   Item Number Price Location  Property1  Property2  Property3
0           33    $4   Boston        4.2        1.9        3.8
1           29    $2      NYC        2.4        5.2        8.9

answered Jun 11, 2018 at 19:06

jpp

166k37 gold badges301 silver badges363 bronze badges

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

Collectives™ on Stack Overflow

Pandas Dataframe: Replace values in a dataframe with values from corresponding column vector of a different dataframe

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related