QST: group by rolling slow on one instance but faster on another, is it possible to optimize
- [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/68456672/group-by-rolling-slow-on-one-instance-but-faster-on-another-is-it-possible-to-o).

---

#### Question about pandas

I wanted to perform group by rolling sum, but I see quite different performance in two types of cases. Consider this two case:

Case 1, "time" and "id" has roughly the same size of 3000.

    import pandas as pd
    import numpy as np
    
    size_now = 1e6
    
    df = pd.DataFrame({"time":np.random.randint(3000,size=int(size_now)),
                 "id":np.random.randint(3000,size=int(size_now)),
                 "data":np.random.randint(1000,size=int(size_now))})
    
    df.time =  pd.to_datetime(df.time,unit="s")
    
    df = df.groupby(["id","time"]).sum().reset_index()

then


    %time df.sort_values(["id","time"]).groupby("id").rolling("1001ms",on="time")[["data"]].sum().sort_index()
    
    executed in 692ms, finished 10:25:20 2021-07-20
    CPU times: user 568 ms, sys: 41.6 ms, total: 610 ms
    Wall time: 671 ms

It took 700ms to complete the calculations.

Case 2, where `id` varies significantly more than `time`:

    import pandas as pd
    import numpy as np
    
    size_now = 1e6
    
    df = pd.DataFrame({"time":np.random.randint(162,size=int(size_now)),
                 "id":np.random.randint(70000,size=int(size_now)),
                 "data":np.random.randint(1000,size=int(size_now))})
    
    df.time =  pd.to_datetime(df.time,unit="s")
    
    df = df.groupby(["id","time"]).sum().reset_index()

Then 

    %time df.sort_values(["id","time"]).groupby("id").rolling("1001ms",on="time")[["data"]].sum().sort_index()
    
    executed in 2.90s, finished 10:28:39 2021-07-20
    CPU times: user 2.84 s, sys: 45.7 ms, total: 2.89 s
    Wall time: 2.88 s

speed is much slower now... is it possible to optimize Case 2 to have similar performance as Case 1?