Suppose I have a dataframe(the time column has 3 min-windows) like this-
ID-A ID-B time sum flag
A 1 09:30:00 5 Y
09:33:00 8 N
09:36:00 5 Y
2 09:36:00 10 Y
09:39:00 15 Y
09:42:00 2 Y
B 1 09:30:00 10 Y
09:33:00 12 N
09:36:00 5 Y
I want to make a new dataframe grouped by ID-A and ID-B. It should have a 12min average and a 15min average of 'sum' divided by the number of 'Y' flags corresponding to them. If there are not enough time values to calculate a 12min average or a 15 min, then the 'sum' value should be extrapolated, assuming all of those values have Y flags.
Desired Dataframe-
ID-A ID-B sum-12min sum-15min
A 1 8 7.5
2 9 15
B 1 12 45/4
for ex- for A-1 i took 3 available values and their sum was 18 for 9 minutes with total 2 Y flags. I extrapolated their sum value for sum-12 min i.e. added 6 to the sum and assumed Y flag. Therefore, 18+6 = 24 / (total Y flags) = 24/3 = 8