Compare 2 dataframes, find matching rows, then sum a column

user_270043 0
Viewed 74

I have 2 dataframes.

df1:
a   b   c   d   e   f
a0  b0  c0  d0  e0  1
a1  b1  c1  d1  e1  5
a2  b2  c2  d2  e2  3
a1  b1  c1  d1  e1  5
a2  b2  c2  d2  e2  4
a3  b3  c3  d3  e3  2
a4  b4  c4  d4  e4  4

df2:
a   b   c   d   e   g
a0  b0  c0  d0  e0  g0
a1  b1  c1  d1  e1  g1
a2  b2  c2  d2  e2  g2

I would like to compare columns a, b, c, d of the 2 dataframes, and if they match, sum the corresponding values in column f of df1 and add that column to df2. So in this case, I would get the resulting dataframe, where h is the sum of the appropriate f values:

a   b   c   d   e   g   h
a0  b0  c0  d0  e0  g0  1
a1  b1  c1  d1  e1  g1  10
a2  b2  c2  d2  e2  g2  7

df1 is much bigger than df2, so I was thinking I could first get the matching rows from df1, then sum column f of this smaller dataframe, but I'm not sure how to do that.

pandas dataframe python
1 Answers

Use DataFrame.merge with aggregate sum, I think you need also merge by e column:

df = (df1.merge(df2, on=['a','b','c','d','e'])
         .groupby(['a','b','c','d','e','g'], as_index=False)
         .sum())
print (df)
    a   b   c   d   e   g   f
0  a0  b0  c0  d0  e0  g0   1
1  a1  b1  c1  d1  e1  g1  10
2  a2  b2  c2  d2  e2  g2   7
user_29010020
Related