Python: Groupby and sum respective rows and update dataframe column-CodePudding

Input df:

Store   Category    Item    tot_table
11      AA          Apple   13.5
11      AA          Orange  13.5
11      BB          Potato  11.5
11      BB          Carrot  11.5
12      AA          Apple   10  
12      BB          Potato  9
12      BB          Carrot  9

Need to perform df.groupby('Store')['tot_table'].unique().sum() , but this line of code doesn't work out.

Expected output df:

Store   Category    Item    split_table     tot_table
11      AA          Apple   13.5                25
11      AA          Orange  13.5                25
11      BB          Potato  11.5                25
11      BB          Carrot  11.5                25
12      AA          Apple   10                  19
12      BB          Potato  9                   19
12      BB          Carrot  9                   19

CodePudding user response：

You can use groupby.transform with unique/sum:

df['tot_table'] = (df.groupby('Store')['tot_table']
                     .transform(lambda s: s.unique().sum())
                  )

output:

   Store Category    Item  tot_table
0     11       AA   Apple       25.0
1     11       AA  Orange       25.0
2     11       BB  Potato       25.0
3     11       BB  Carrot       25.0
4     12       AA   Apple       19.0
5     12       BB  Potato       19.0
6     12       BB  Carrot       19.0