Dubraven93 Dubraven93 - 1 month ago 12
Python Question

python pandas, trying to find unique combinations of two columns and merging while summing a third column

Hi I will show what im trying to do through examples:
I start with a dataframe like this:

> pd.DataFrame({'A':['a','a','a','c'],'B':[1,1,2,3], 'count':[5,6,1,7]})
A B count
0 a 1 5
1 a 1 6
2 a 2 1
3 c 3 7


I need to find a way to get all the unique combinations between column A and B, and merge them. The count column should be added together between the merged columns, the result should be like the following:

A B count
0 a 1 11
1 a 2 1
2 c 3 7


Thans for any help.

Answer

Use groupby with aggregating sum:

print (df.groupby(['A','B'], as_index=False)['count'].sum())
   A  B  count
0  a  1     11
1  a  2      1
2  c  3      7

print (df.groupby(['A','B'])['count'].sum().reset_index())
   A  B  count
0  a  1     11
1  a  2      1
2  c  3      7