Calculate the sum of the first n rows for each group

user_12076197 0
Viewed 1000

What I want to do is group by column A and then take the sum of first two rows, then assign that value as a new column. Example below:

DF:

ColA   ColB
 AA      2
 AA      1
 AA      5
 AA      3
 BB      9
 BB      3
 BB      2
 BB      12
 CC      0
 CC      10
 CC      5
 CC      3

Desired DF:

ColA   ColB    NewCol
 AA      2       3
 AA      1       3
 AA      5       3 
 AA      3       3
 BB      9       12
 BB      3       12
 BB      2       12
 BB      12      12
 CC      0       10
 CC      10      10 
 CC      5       10
 CC      3       10

For AA, it looks at ColB and take the sum of the first two rows and assigns that summed value to newCol. I've tried this by creating a dictionary by looping through the unique ColA values, creating a subset dataframe of the first two rows, summing, then populating the dictionary with values. Then mapping the dictionary back - but my dataframe is VERY big and it takes forever. Any ideas?

Thank you!

pandas dataframe python
1 Answers

You can use transform to get a new value per each row and a lambda function. In lambda you can use head(2) to get first 2 rows for each group and sum() them:

df.groupby('ColA')['ColB'].transform(lambda x: x.head(2).sum())
user_89736200
Related