Pandas Assign The Groupby Sum Value To The Last Row In The Original Table

For example, I have a table A id price sum 1 2 0 1 6 0 1 4 0 2 2 0 2 10 0 2 1 0 2 5 0 3 1 0 3 5 0 What I want is li

Solution 1:

Use last_valid_index to locate rows to fill

g = df.groupby('id')
l = pd.DataFrame.last_valid_index
df.loc[g.apply(l), 'sum'] = g.price.sum().values
dfid  price  sum
0   1      2    0
1   1      6    0
2   1      4   12
3   2      2    0
4   2     10    0
5   2      1    0
6   2      5   18
7   3      1    0
8   3      5    6

Solution 2:

You could do this:

df.assign(sum=df.groupby('id')['price'].transform('sum').drop_duplicates(keep='last')).fillna(0)

OR

df['sum'] = (df.groupby('id')['price']
               .transform('sum')
               .mask(df.id.duplicated(keep='last'), 0))

Output:

id  price   sum
0   1      2   0.0
1   1      6   0.0
2   1      4  12.0
3   2      2   0.0
4   2     10   0.0
5   2      1   0.0
6   2      5  18.0
7   3      1   0.0
8   3      5   6.0