Calculate density of a column using input from other columns-CodePudding

I have a table as below:

Rate     Distance    
Start           4                   
Coupon          7                   
Coupon          8                   
End             10                 
Start           13                  
Coupon          14                  
End             18

And I want to calculate the another column as Density that will take the value at df["Rate"] == "End" and subtract with the value at df["Rate"] == "Start" and then divide the count of coupons between Start and End with the subtracted value as output below:

Rate     Distance     Density
Start          4          0.33
Coupon         7          0.33
Coupon         8          0.33
End            10         0.33
Start          13         5
Coupon         14         5
End            18         5

CodePudding user response：

Assumes, per your description, that the last group should have density of 0.2.

g = df['Rate'].eq('Start').cumsum()
df['Density'] = df['Distance'].groupby(g).transform(lambda x: (len(x)-2)/(x.iat[-1]-x.iat[0]))
print(df)

Result

     Rate  Distance   Density
0   Start         4  0.333333
1  Coupon         7  0.333333
2  Coupon         8  0.333333
3     End        10  0.333333
4   Start        13  0.200000
5  Coupon        14  0.200000
6     End        18  0.200000

Assumptions

Each group always starts with Start and ends with End.
Each group always contains at least one coupon