Python Pandas : how to add values in column group by a particular row and put the result in a new co-CodePudding

I have a dataframe

p_id    b_id      val
101     201       99
101     202       2 
101     203       11
102     201       1
102     202       50
102     203       23

I need to add the values in the column val, based on p_id and b_id, to get this result dataframe:

ie, for each p_id:

sum1 = val(201) val(202) = 99 2 = 101

sum2 = val(201) val(203) = 99 11 = 110

p_id   sum_1     sum_2
101    101        110
102     51         24

The resultant dataframe should look like this I tried running this

df2 = df.groupby(['p_id', 'b_id'])['val'].sum().reset_index()

but not being able to get all the rows for each p_id

CodePudding user response：

So we are doing sum first with transform, then cumcount create the key, and pivot

df.val  = df.groupby('p_id')['val'].transform('first')
df['key'] = df.groupby(['p_id']).cumcount().astype('str').radd('Sum_')
df = df.pivot(index= 'p_id', columns = 'key', values = 'val').drop(['Sum_0'],axis=1).reset_index()
Out[228]: 
key  p_id  Sum_1  Sum_2
0     101    101    110
1     102     51     24