Groupby sum total in pandas

user_7801236 0
Viewed 38

Let's say I have a dataframe that looks like this:

Input

item_code    qty

 10034        5
              4
              3

 10035        2
              1

I would like to add a new column name total after the qty column and put sum total qty according to item_code column.

output

    item_code    qty   total

     10034        5      12
                  4
                  3

     10035        2       3
                  1
pandas-groupby pandas
1 Answers

Use if item_code is column:

df['total'] = df.groupby(df['item_code'].replace('',np.nan).ffill())['qty'].transform('sum')

Use if item_code is level of MultiIndex:

df['total'] = df.groupby(level=0)['qty'].transform('sum')

If need remove duplicated values (repalce to empty string) it is possible, but get mixed values - strings with numbers, so next processing should be problematic:

df['total'] = df['total'].mask(df.index.get_level_values('item_code').duplicated(), '')
user_29010020
Related