Copy value from one dataframe to another based on multiple column index

Question

I have 2 dataframes:

df1 = 
  item  shop revenue
0   1   0   0.0
1   2   1   0.0
2   3   2   0.0

df2 =
  item  shop revenue
0   1   0   33
1   2   1   244
2   3   2   124
3   4   3   26

I want to map the value of revenue from df2 based on the item and shop equality. I do this in a painful way first by combining two columns and using them as index. Then I map the value and finally I drop the extra columns.

df1['new_id']=df1["shop"].astype(str) +"_"+ df1["item"].astype(str)
df2['new_id']=df2["shop"].astype(str) +"_"+ df2["item"].astype(str)
df1 = df1.set_index("new_id")
df1.update(df2.set_index("new_id"))
df1 = df1.reset_index()
df1 = df1.drop(['new_id'],axis=1)
df2 = df2.drop(['new_id'],axis=1)

df1 =
   item shop revenue
0   1   0   33.0
1   2   1   244.0
2   3   2   124.0

There must a better and more concise way of doing this with a simpler code. Could you please advise me on a better approach?

Are always in df1.revenue only 0 values?

jezrael
– jezrael

2020-06-07 06:28:37 +00:00
Commented Jun 7, 2020 at 6:28 — jezrael
– jezrael, Commented Jun 7, 2020 at 6:28
any value. I used it just for illustration.

user3141181
– user3141181

2020-06-07 09:24:03 +00:00
Commented Jun 7, 2020 at 9:24 — user3141181
– user3141181, Commented Jun 7, 2020 at 9:24

jezrael · Accepted Answer · 2020-06-07 06:30:43Z

2

You can use DataFrame.merge by select 2 columns in df1 and no on parameter for merge by intersection of columns:

df = df1[['item','shop']].merge(df2)

So it working same like:

df = df1[['item','shop']].merge(df2, on=['item','shop'])

Your solution should be changed with DataFrame.set_index by 2 columns for MultiIndex:

df11 = df1.set_index(['item','shop'])
df11.update(df2.set_index(['item','shop']))
df = df11.reset_index()

edited Jun 7, 2020 at 6:30

answered Jun 7, 2020 at 6:25

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

1 Comment

user3141181 Over a year ago

df = df1[['item','shop']].merge(df2) will merge only the common rows. Other rowss will be left out. How do I add back the left out rows. The changed solution is good. Thanks.

Collectives™ on Stack Overflow

Copy value from one dataframe to another based on multiple column index

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related