# Issue

This Content is from Stack Overflow. Question asked by MemeFast King

Suppose I have a dataframe(the time column has 3 min-windows and grouped by ID-A and ID-B) like this-

``````ID-A  ID-B    time     sum   num
A       1   09:30:00    5     2
1   09:33:00    8     2
1   09:36:00    5     2
2   09:36:00    10    3
2   09:39:00    15    3
2   09:42:00    2     3
B       1   09:30:00    10    2
1   09:33:00    12    2
1   09:36:00    5     2
``````

I am trying to calculate 15min and 30min average of the sum divided by num.

Note:- For 1 pair of ID-A and ID-B, the num is always the same.

Desired Dataframe(grouped by ID-A and ID-B)-
ID-A ID-B time sum num 15min 30min
A 1 09:30:00 5 2 15 30
09:33:00 8 2 15 30
09:36:00 5 2 15 30
2 09:36:00 10 3 15 30
09:39:00 15 3 15 30
09:42:00 2 3 15 30
B 1 09:30:00 10 2 22.5 45
09:33:00 12 2 22.5 45
09:36:00 5 2 22.5 45

For Example –
For ID-A -> A and ID-B -> 1, the total time data was available for only 9 minutes. So I did, (5+8+5)/9 = 18/9 = 2 for 1 minute. It also has to be divided by num, so 2/2=1. Therefore, for 15 minutes, it will be 15 and 30 for 30 minutes. There could be an instance where the time data is available for 15 or 30 minutes. Then obviously, extrapolation is not required only normal calculations should happen.

My approach-
Since the maximum average I need is 30 minutes, I thought of extrapolating all the values first to 30 minutes so I don’t have to care about whether I have all values present.
Eventually I just want ID-A, ID-B, 15min and 30min columns only in my df but this will also work.

# Solution

Looks like this would work?

``````# cast 'num' to float
df['num'] = df['num'].astype(float)

def add_cols(grp):
# divide sum by 3xnum of rows, and then divide by 'num'
multiple = grp['sum'].sum() / (3*len(grp)) / grp.iloc[0, -1]
return grp.assign(**{'15min': 15 * multiple, '30min': 30 * multiple})

df.groupby(['ID-A', 'ID-B']).apply(add_cols)
``````

Output:

``````                       time   sum   num     15min   30min
ID-A ID-B
A   1   2022-09-18 09:30:00     5   2.0     15.0    30.0
1       2022-09-18 09:33:00     8   2.0     15.0    30.0
1       2022-09-18 09:36:00     5   2.0     15.0    30.0
2       2022-09-18 09:36:00     10  3.0     15.0    30.0
2       2022-09-18 09:39:00     15  3.0     15.0    30.0
2       2022-09-18 09:42:00     2   3.0     15.0    30.0
B   1   2022-09-18 09:30:00     10  2.0     22.5    45.0
1       2022-09-18 09:33:00     12  2.0     22.5    45.0
1       2022-09-18 09:36:00     5   2.0     22.5    45.0
``````

``` This Question was asked in  StackOverflow by  MemeFast King and Answered by Josh Friedlander It is licensed under the terms of
CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.```
people found this article helpful. What about you?