Calculates hourly and 2-hour moving averages for different events in the Pandas data frame… here is a solution to the problem.
Calculates hourly and 2-hour moving averages for different events in the Pandas data frame
I have a data frame as shown below:
Event DateTime ModFlow(cfs) ObsFlow(cfs) ModVol(f3) ObsVol(f3)
Event 01 8/15/2016 3:10 0.7309299 0.6408669 219.27897 192.26007
Event 01 8/15/2016 3:15 0.7177225 0.3482972 215.31675 104.48916
Event 01 8/15/2016 3:20 0.7182343 0.5108359 215.47029 153.25077
Event 01 8/15/2016 3:25 0.7078446 0.6315789 212.35338 189.47367
Event 01 8/15/2016 3:30 0.6973493 0.5201238 209.20479 156.03714
Event 01 8/15/2016 3:35 0.6949614 0.5185758 208.48842 155.57274
Event 01 8/15/2016 3:40 0.6951835 0.6517028 208.55505 195.51084
Event 01 8/15/2016 3:45 0.6869527 0.4659443 206.08581 139.78329
Event 01 8/15/2016 3:50 0.6796721 0.4148607 203.90163 124.45821
Event 01 8/15/2016 3:55 0.6901501 0.4071207 207.04503 122.13621
Event 01 8/15/2016 4:00 0.6912107 0.6996904 207.36321 209.90712
Event 01 8/15/2016 4:05 0.6811846 0.6315789 204.35538 189.47367
Event 01 8/15/2016 4:10 0.6783166 0.5650155 203.49498 169.50465
Event 01 8/15/2016 4:15 0.6752744 0.5572755 202.58232 167.18265
Event 01 8/15/2016 4:20 0.6719815 0.6052632 201.59445 181.57896
Event 01 8/15/2016 4:25 0.66859 0.6563467 200.577 196.90401
Event 01 8/15/2016 4:30 0.6641344 0.4721362 199.24032 141.64086
Event 01 8/15/2016 4:35 0.7217036 0.8436533 216.51108 253.09599
Event 01 8/15/2016 4:40 0.7838766 0.8065016 235.16298 241.95048
Event 01 8/15/2016 4:45 0.9858695 0.6424149 295.76085 192.72447
Event 01 8/15/2016 4:50 1.543869 1.071207 463.1607 321.3621
Event 01 8/15/2016 4:55 3.76313 1.555728 1128.939 466.7184
Event 01 8/15/2016 5:00 6.952304 6.942724 2085.6912 2082.8172
Event 01 8/15/2016 5:05 12.44711 8.373065 3734.133 2511.9195
Event 01 8/15/2016 5:10 18.05204 15.53715 5415.612 4661.145
Event 04 4/10/2016 17:25 0.7944226 1.956656 238.32678 586.9968
Event 04 4/10/2016 17:30 0.7899759 1.743034 236.99277 522.9102
Event 04 4/10/2016 17:35 0.7881337 1.580495 236.44011 474.1485
Event 04 4/10/2016 17:40 0.7872726 1.890093 236.18178 567.0279
Event 04 4/10/2016 17:45 0.7867649 1.883901 236.02947 565.1703
Event 04 4/10/2016 17:50 0.7863836 1.50774 235.91508 452.322
Event 04 4/10/2016 17:55 0.7860502 1.46904 235.81506 440.712
Event 04 4/10/2016 18:00 0.7857358 1.660991 235.72074 498.2973
Event 04 4/10/2016 18:05 0.7942941 1.565015 238.28823 469.5045
Event 04 4/10/2016 18:10 0.8083629 1.982972 242.50887 594.8916
Event 04 4/10/2016 18:15 0.8234028 1.674923 247.02084 502.4769
Event 04 4/10/2016 18:20 0.8384557 1.405573 251.53671 421.6719
Event 04 4/10/2016 18:25 0.8464783 1.503096 253.94349 450.9288
Event 04 4/10/2016 18:30 0.8494487 1.599071 254.83461 479.7213
Event 04 4/10/2016 18:35 0.8503551 1.791022 255.10653 537.3066
Event 04 4/10/2016 18:40 0.8505155 1.605263 255.15465 481.5789
Event 04 4/10/2016 18:45 0.850417 1.726006 255.1251 517.8018
Event 04 4/10/2016 18:50 0.8502218 1.602167 255.06654 480.6501
Event 04 4/10/2016 18:55 0.8500343 1.487616 255.01029 446.2848
Event 04 4/10/2016 19:00 0.8498603 1.928793 254.95809 578.6379
Event 04 4/10/2016 19:05 0.8458332 1.716718 253.74996 515.0154
Event 04 4/10/2016 19:10 0.8399328 1.414861 251.97984 424.4583
Event 04 4/10/2016 19:15 0.8337958 1.674923 250.13874 502.4769
Event 04 4/10/2016 19:20 0.8277948 1.447368 248.33844 434.2104
Event 04 4/10/2016 19:25 0.8244936 1.665635 247.34808 499.6905
Values
are obtained every 5 minutes.
I have a lot of such events in the data frame.
I want each event in the new data frame to have 1-hour and 2-hour moving averages.
How could I possibly do that?
Solution
Pandas 0.19
d1 = df.set_index('DateTime').sort_index()
ma_1h = d1.groupby('Event').rolling('H').mean()
ma_2h = d1.groupby('Event').rolling('2H').mean()
ma_1h.head()
ma_2h.head()
Pandas < 0.19
Because timestamps are regularly spaced, we can grab the exact number of rows
ma_1h = d1.groupby('Event').rolling(12).mean().dropna()
ma_2h = d1.groupby('Event').rolling(24).mean().dropna()