CODEWITHSUNDEEP
pythonpandasdataframegroup-bysum
Yan
Yan

Reputation: 105

How to sum by agrouping a specific column using Python?

I`m not able to sum by each group/column. The idea is to create a new column on this data set with the sum by "store":

PNO store   ForecastSUM
17  20054706    WITZ    0.0
8   8007536 WITZ    0.0
2   8007205 WITZ    0.0
12  8601965 WITZ    0.0
5   8007239 WITZ    0.0
14  20054706    ROT 1.0
1   8007205 ROT 7.0
9   8601965 ROT 2.0
6   8007536 ROT 3.0
3   8007239 ROT 2.0
15  20054706    MAR 1.0
7   8007536 MAEG    6.0
10  8601965 MAEG    4.0
4   8007239 MAEG    3.0
0   8007205 MAEG    6.0
13  20054706    BUD 1.0
11  8601965 AYC 0.0
16  20054706    AYC 0.0

I am trying to apply this code:

copiedDataWHSE['sumWHSE'] = copiedDataWHSE.groupby(['ForecastSUM']).agg({'ForecastSUM': "sum"})

and the result I am getting is:

PNO store   ForecastSUM sumWHSE
17  20054706    WITZ    0.0 NaN
8   8007536 WITZ    0.0 NaN
2   8007205 WITZ    0.0 4.0
12  8601965 WITZ    0.0 NaN
5   8007239 WITZ    0.0 NaN
14  20054706    ROT 1.0 NaN
1   8007205 ROT 7.0 3.0
9   8601965 ROT 2.0 NaN
6   8007536 ROT 3.0 12.0
3   8007239 ROT 2.0 6.0
15  20054706    MAR 1.0 NaN
7   8007536 MAEG    6.0 7.0
10  8601965 MAEG    4.0 NaN
4   8007239 MAEG    3.0 4.0
0   8007205 MAEG    6.0 0.0
13  20054706    BUD 1.0 NaN
11  8601965 AYC 0.0 NaN
16  20054706    AYC 0.0 NaN

Which is wrong, since I would like to have as example, once the store is ROT, the sumWHSE column should receive 19.

Upvotes: 1

Views: 122

Answers (1)

semblable
semblable

Reputation: 793

As @sammywemmy mentions, you need to group on store, not on ForecastSUM:

store_groupby = df.groupby(['store']).agg({'ForecastSUM': "sum"})

However, since it's a groupby of length 6, you can't assign it back to the dataframe as a new column.

What I would do is turn the groupby into a dictionary, then assign() it to a new column with a lambda function.

store_groupby_dict = store_groupby.to_dict()

df = df.assign(store_total = lambda x: store_groupby_dict[x.store])

Doing the same thing with apply() makes it a little more readable:

df['store_total'] = df.store.apply(lambda x: store_groupby_dict[x])

Upvotes: 1

Related Questions