0

I have two dataframes like below. The first one is a list of ID's, names and values. The second contains all the unique ID and Name combinations, I need the sum of value column to sum up on the condition the first two columns match. A python sumifs basically...

thanks for your help!

allsales = 

Sale ID Name    Value
662165  JC  0.70
662165  LK  0.96
662165  LK  0.53
662165  JC  0.36
662165  LK  0.68
662165  JC  0.91
499741  CWS 0.76
499741  RV  0.97
499741  RV  0.43
499741  RV  0.14
499741  RV  0.11
499741  CWS 0.18
996276  WG  0.82
996276  WG  0.38
996276  WG  0.78
996276  WG  0.64
996276  RG  0.62
996276  RG  0.14
996276  RG  0.10
996276  WG  0.37

sales = 

Sale ID Name    Sum of Value
662165  JC  1.96
662165  LK  ?
499741  CWS ?
499741  RV  ?
996276  WG  ?
996276  RG  ?

I had tried the following to get one condition working as a start but its saying 'can only compare identically labelled series objects.

sales['Sum of Value'] = allsales.loc[allsales['Sales ID'] == sales['Sales ID'], allsales['Value']].sum()
Eric Broda
  • 6,701
  • 6
  • 48
  • 72
Jordan Cullen
  • 11
  • 3
  • Possible duplicate of [Pandas groupby multiple columns, list of multiple columns](https://stackoverflow.com/questions/51584363/pandas-groupby-multiple-columns-list-of-multiple-columns) – KenHBS Jul 24 '19 at 21:07

1 Answers1

0

I think that groupby is your friend here.

df = allsales.groupby(['Sale ID', 'Name'],
                  as_index = False).sum()
Marcus Högenå Bohman
  • 473
  • 1
  • 6
  • 13