How to compute a weighted loop?

Question

This is a portion of my data frame:

df = pd.DataFrame({
    'IBI_msec': [652, 618, 654],
    'rate': [92.02, 97.09, 91.74]
})

I need to compute a loop that enables me to compute this logic:

• if 652 > 500, then bin = 92.02, and the remaining (652 - 500) goes to the next bin;
• because the bin2 contains the 152, remaining from the previous row, only 348 msec from 618 are needed. In that case, a weighted average is needed: ((152*92.02) + (348*97.09)) / 500 which gives 95.55.
• (I don't expect to have IBI values under 500 in the data).

The final result should be something like this:

  IBI_msec    rate   bins
0      652   92.02  92.02
1      618   97.09  95.55
2      654   91.74  94.63

Answer 1

This does what you want: that said, there will be a problem if (when) remainder goes over 500, so you should complete your rules to deal with that case.

import pandas as pd

df = pd.DataFrame({
    'IBI_msec': [652, 618, 654],
    'rate': [92.02, 97.09, 91.74]
})

bins = []
remainder = 0
for i in range(len(df)):
    if i == 0:
        bins.append(df['rate'][0])
        fill = 500
    else:
        fill = 500 - remainder
        bins.append(round((remainder*df['rate'][i-1]+fill*df['rate'][i])/500,2))
    remainder = df['IBI_msec'][i] - fill
    
print(bins)
# [92.02, 95.55, 94.63]