CODEWITHSUNDEEP
pythonpandaspandas-groupby
Daneil
Daneil

Reputation: 11

How to combine two groups by Index

I need to combine two groups together. How should I do?

For example:

Table1 

Index|Column1|Column2|Values
0    |      A|      B|    10
1    |      A|      B|    20
2    |      B|      B|    10
3    |      B|      B|    10
4    |      B|      A|    30
5    |      C|      C|    30
6    |      C|      C|    10
7    |      C|      D|    10
8    |      C|      D|    20

Group1 of Column1 sum

Index|Values(sum)
A    |         30
B    |         50
C    |         70

Group2 of Column2 sum

Index|Values(sum)
A    |         30
B    |         50
C    |         40
D    |         30

Now I want to combine Group1 and Group2 together and add one column back to Table1 like this below.

Combine

Index|Values
A    |    60
B    |   100
C    |   110
D    |    30

Table1

Index|Column1|Column2|Values|Combine
0    |      A|      B|    10|     60
1    |      A|      B|    20|     60
2    |      B|      B|    10|    100
3    |      B|      B|    10|    100
4    |      B|      A|    30|    100
5    |      C|      C|    30|    110
6    |      C|      C|    10|    110
7    |      C|      D|    10|    110
8    |      C|      D|    20|    110

Upvotes: 0

Views: 183

Answers (3)

Rajat Jain
Rajat Jain

Reputation: 2032

df['Values1'] = df.groupby('Column1')['Values'].transform('sum')

This would do grouping on Column1.

df['Values2'] = df.groupby('Column2')['Values'].transform('sum')

This would do it on Column2. Next we can combine the results like below:

df['Combine'] = df['Values1'] + df['Values2']

Upvotes: 0

Vaishali
Vaishali

Reputation: 38415

You can melt the two columns and then aggregate. Map the values to Column1 in the nest step

mapper = df.melt('Values',['Column1', 'Column2']).groupby('value')['Values'].sum().to_dict()
df['Combine'] = df['Column1'].map(mapper)

    Column1 Column2 Values  Combine
index               
0   A       B       10      60
1   A       B       20      60
2   B       B       10      100
3   B       B       10      100
4   B       A       30      100
5   C       C       30      110
6   C       C       10      110
7   C       D       10      110
8   C       D       20      110

Upvotes: 3

fuglede
fuglede

Reputation: 18201

If your data frame is called df, you could let

v = df.groupby('Column1').Values.sum().add(df.groupby('Column2').Values.sum(), fill_value=0)
df['Combine'] = v.loc[df['Column1']].values

Upvotes: 1

Related Questions