Assist me in Joining These Data Frames in Python

Question

Here's my scenario. Say I have these two data sets-

dic = {'firstname':['John','John','John','John','John','Susan','Susan',
                    'Susan','Susan','Susan','Mike','Mike','Mike','Mike',
                    'Mike'],
       'lastname':['Smith','Smith','Smith','Smith','Smith','Wilson',
                   'Wilson','Wilson','Wilson','Wilson','Jones','Jones',
                   'Jones','Jones','Jones'],
       'company':['KFC','BK','KFC','KFC','KFC','BK','BK','WND','WND',
                  'WND','TB','CHP','TB','CHP','TB'],
       'paid':[200,300,250,100,900,650,430,218,946,789,305,750,140,860,310]}
df1 = pd.DataFrame(dic)
print(df1)

and

dic = {'firstname':['John','John','Susan','Susan','Mike','Mike'],
       'lastname':['Smith','Smith','Wilson','Wilson','Jones','Jones'],
       'company':['KFC','BK','BK','WND','TB','CHP'],
       'paid':[1450,300,1080,1953,755,1610]}
df2 = pd.DataFrame(dic)
print(df2)

with output 1 being-

   firstname lastname company  paid
0       John    Smith     KFC   200
1       John    Smith      BK   300
2       John    Smith     KFC   250
3       John    Smith     KFC   100
4       John    Smith     KFC   900
5      Susan   Wilson      BK   650
6      Susan   Wilson      BK   430
7      Susan   Wilson     WND   218
8      Susan   Wilson     WND   946
9      Susan   Wilson     WND   789
10      Mike    Jones      TB   305
11      Mike    Jones     CHP   750
12      Mike    Jones      TB   140
13      Mike    Jones     CHP   860
14      Mike    Jones      TB   310

and output 2 being-

  firstname lastname company  paid
0      John    Smith     KFC  1450
1      John    Smith      BK   300
2     Susan   Wilson      BK  1080
3     Susan   Wilson     WND  1953
4      Mike    Jones      TB   755
5      Mike    Jones     CHP  1610

What I would like to do is add the df2 paid column to each portion of the detailed view of df1

I'm assuming there would be a merge function that could help me, but I would need some help writing the code to do that.

So my ideal output would be-

   firstname lastname company  paid sum_paid
0       John    Smith     KFC   200     1450
1       John    Smith      BK   300      300
2       John    Smith     KFC   250     1450
3       John    Smith     KFC   100     1450
4       John    Smith     KFC   900     1450
5      Susan   Wilson      BK   650     1080
6      Susan   Wilson      BK   430     1080
7      Susan   Wilson     WND   218     1953
8      Susan   Wilson     WND   946     1953
9      Susan   Wilson     WND   789     1953
10      Mike    Jones      TB   305      755
11      Mike    Jones     CHP   750     1610
12      Mike    Jones      TB   140      755
13      Mike    Jones     CHP   860     1610
14      Mike    Jones      TB   310      755

your column sum_paid looks like the result of the `df1.groupby(['firstname', 'lastname', 'company'])['paid'].transform('sum')`? no need of df2 — Ben.T, Jun 30 '20 at 14:32
@YOBEN_S, after trying merge again, I figured it out. Don't know why it didn't work the first time — Delto, Jun 30 '20 at 14:34
Does this answer your question? [Pandas Merging 101](https://stackoverflow.com/questions/53645882/pandas-merging-101) — Ben.T, Jun 30 '20 at 14:34

score 2 · Accepted Answer · answered Jun 30 '20 at 14:30

Just do this:

df = df1.merge(df2, on=['firstname', 'lastname', 'company']).rename(columns={'paid_y': 'sum_paid', 'paid_x': 'paid'})
print(df)

   firstname lastname company    paid  sum_paid
0       John    Smith     KFC     200    1450
1       John    Smith     KFC     250    1450
2       John    Smith     KFC     100    1450
3       John    Smith     KFC     900    1450
4       John    Smith      BK     300     300
5      Susan   Wilson      BK     650    1080
6      Susan   Wilson      BK     430    1080
7      Susan   Wilson     WND     218    1953
8      Susan   Wilson     WND     946    1953
9      Susan   Wilson     WND     789    1953
10      Mike    Jones      TB     305     755
11      Mike    Jones      TB     140     755
12      Mike    Jones      TB     310     755
13      Mike    Jones     CHP     750    1610
14      Mike    Jones     CHP     860    1610

Assist me in Joining These Data Frames in Python

1 Answers1