How to sum by agrouping a specific column using Python?

Question

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.

why are you grouping on ForecastSum, if you are going to aggregate on it? — sammywemmy
– sammywemmy, Commented Apr 5, 2021 at 22:39

semblable · Accepted Answer · 2021-04-05 23:11:44Z

1

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])

answered Apr 5, 2021 at 23:11

semblable

8032 gold badges11 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Yan Over a year ago

Once I apply the df['store_total'] = df.store.apply(lambda x: store_groupby_dict[x]), the follow erroe happens:

Yan Over a year ago

KeyError Traceback (most recent call last) <ipython-input-130-a19458ebf745> in <module>() 1 ----> 2 copiedDataWHSE['store_total'] = copiedDataWHSE['store'].apply(lambda x: store_groupby_dict[x]) C:\ProgramData\Anaconda3\lib\site-packages\pandas\core\series.py in apply(self, func, convert_dtype, args, **kwds) 3192 else: 3193 values = self.astype(object).values -> 3194 mapped = lib.map_infer(values, f, convert=convert_dtype)

Yan Over a year ago

3195 3196 if len(mapped) and isinstance(mapped[0], Series): pandas/_libs/src\inference.pyx in pandas._libs.lib.map_infer() <ipython-input-130-a19458ebf745> in <lambda>(x) 1 ----> 2 copiedDataWHSE['store_total'] = copiedDataWHSE['store'].apply(lambda x: store_groupby_dict[x]) KeyError: 'WITZ'

Yan Over a year ago

Is is not recognizing the WITZ as a key. What should I do ?

Yan Over a year ago

stackoverflow.com/users/12399409/k-n-c could you help me here ?

|

Collectives™ on Stack Overflow

How to sum by agrouping a specific column using Python?

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related