QST: how to perform group by rolling efficiently based on datetime index?
- [ y] I have searched the [[pandas] tag](https://stackoverflow.com/questions/tagged/pandas) on StackOverflow for similar questions.

- [ y] I have asked my usage related question on [StackOverflow](https://stackoverflow.com/questions/68446123/how-to-perform-group-by-rolling-efficiently-based-on-datetime-index?noredirect=1#comment120965466_68446123).

---

#### Question about pandas: how to perform group by rolling efficiently based on datetime index?

let's firstly create a medium size random df:

    size_now = 1e5
    
    df = pd.DataFrame({"time":np.random.randint(1000,size=int(size_now)),
                 "id":np.random.randint(1000,size=int(size_now)),
                 "data":np.random.randint(1000,size=int(size_now))})
    
    df.time =  pd.to_datetime(df.time,unit="s")

df looks like this:

    print(df)
    executed in 9ms, finished 15:16:05 2021-07-19
                         time   id  data
    0     1970-01-01 00:15:01  915   299
    1     1970-01-01 00:08:30   31   940
    2     1970-01-01 00:02:55  925   600
    3     1970-01-01 00:12:48  554   935
    4     1970-01-01 00:07:43  565   638
    ...                   ...  ...   ...
    99995 1970-01-01 00:16:27  707   615
    99996 1970-01-01 00:01:06  298    38
    99997 1970-01-01 00:14:26   37    43
    99998 1970-01-01 00:16:09   66   791
    99999 1970-01-01 00:01:45  376   854

I realized rolling sum can be pretty fast: 

    df.set_index("time").sort_index().rolling("2500ms")["data"].sum()

    time
    1970-01-01 00:00:00        66.0
    1970-01-01 00:00:00       809.0
    1970-01-01 00:00:00       879.0
    1970-01-01 00:00:00      1329.0
    1970-01-01 00:00:00      1729.0
                             ...   
    1970-01-01 00:16:39    124998.0
    1970-01-01 00:16:39    125265.0
    1970-01-01 00:16:39    126050.0
    1970-01-01 00:16:39    126782.0
    1970-01-01 00:16:39    127766.0
    Name: data, Length: 100000, dtype: float64

It only took 0.31s to complete the task. However, what I need is group by id and perform rolling on time. So here is my goal:

    df.groupby("id").rolling("2500ms",on="time")["data"].sum()

    id   time               
    0    1970-01-01 00:05:43      161.0
         1970-01-01 00:12:05      288.0
         1970-01-01 00:00:35      981.0
         1970-01-01 00:00:22      479.0
         1970-01-01 00:09:33      834.0
                                 ...   
    999  1970-01-01 00:15:07    28159.0
         1970-01-01 00:06:39    29035.0
         1970-01-01 00:06:15    29538.0
         1970-01-01 00:09:07    29788.0
         1970-01-01 00:00:48    30736.0
    Name: data, Length: 100000, dtype: float64

But it took 2s to complete the task. I wanted to know if there is much more efficient to do this.

I thought presorting may be helpful, so I tried:

    df = df.set_index(["id","time"]).sort_index()

This is very fast too. After this, we just need to cut our dataframe into multiple small frame and do rolling sum and concat them together... so I tried

    df.groupby(level=0).rolling("2500ms")

but it gives me this error

> ValueError: window must be an integer

so i tried:

    df.groupby(level=0).rolling("2500ms",on=df.index.levels[1]).sum()


this breaks my Jupiter notebook, giving me kernel dead error.

So I tried

    df_tmp_list = []
    
    for j in df.index.unique(level=0):
        df_tmp = df.loc[j]
        df_tmp = df_tmp.rolling("2500ms").sum()
        df_tmp["id"] = j
        df_tmp_list.append(df_tmp)
    #     break
    
    pd.concat(df_tmp_list).reset_index().set_index(["id","time"])

This also took 2.6s... What would be the most efficient way for me to achieve?

    df.groupby("id").rolling("2500ms",on="time")["data"].sum()



**Edit**
Thanks everyone for answering. It seems everyone runs it much faster than I do. I need 2s, others need 50ms... to be exactly clear, the complete code is:

    import pandas as pd
    import numpy as np
    
    size_now = 1e5
    
    df = pd.DataFrame({"time":np.random.randint(1000,size=int(size_now)),
                 "id":np.random.randint(1000,size=int(size_now)),
                 "data":np.random.randint(1000,size=int(size_now))})
    
    df.time =  pd.to_datetime(df.time,unit="s")
    
    %time df.groupby("id").rolling("2500ms",on="time")["data"].sum()

my output is

    CPU times: user 2.3 s, sys: 24 ms, total: 2.32 s
    Wall time: 2.32 s

I am actually running on AWS-EC2 with 4 cpus and 16GB RAM.I have tried to update packages:

      510  conda update pandas
      511  conda update numpy

so speed didn't get improved... Any help?I'm very frustrated...

I tried on my MacBook Pro, with 2.8 GHz Quad-Core Intel Core i7 and 16 GB 1600 MHz DDR3. It took 450ms... still much slower than all of you guys.... Please help me...
```
