ValueError: index must be monotonic

Question

dfdata.Speed.rolling('60T', closed='right').sum()

I am trying to apply the rolling sum on this one column and I sorted out the whole data, but still I am getting the same error. Can anyone help me to resolve it ?? The first date and time column in the index column in the data and the second one is normal column. That's why it seems kind of repetitive.

DateTime            DateTime            Speed       distance    IDs totalHours          
2011-01-01 00:19:00 2011-01-01 00:19:00 0.041916    0.000710    19  0.016944
2011-01-01 00:20:00 2011-01-01 00:20:00 0.033719    0.000562    19  0.016667
2011-01-01 00:20:59 2011-01-01 00:20:59 0.153553    0.002517    19  0.016389
2011-01-01 00:21:59 2011-01-01 00:21:59 0.142272    0.002371    19  0.016667
2011-01-01 00:23:00 2011-01-01 00:23:00 0.033166    0.000562    19  0.016944
2011-01-01 00:24:00 2011-01-01 00:24:00 0.037843    0.000631    19  0.016667
2011-01-01 00:26:00 2011-01-01 00:26:00 0.050262    0.001675    19  0.033333
2011-01-01 00:27:00 2011-01-01 00:27:00 0.032249    0.000537    19  0.016667
2011-01-01 00:27:59 2011-01-01 00:27:59 0.180206    0.002953    19  0.016389
2011-01-01 00:29:00 2011-01-01 00:29:00 0.133477    0.002262    19  0.016944
2011-01-01 00:30:00 2011-01-01 00:30:00 0.128053    0.002134    19  0.016667
2011-01-01 00:30:59 2011-01-01 00:30:59 0.041964    0.000688    19  0.016389
2011-01-01 00:32:00 2011-01-01 00:32:00 0.072529    0.001229    19  0.016944
2011-01-01 00:33:00 2011-01-01 00:33:00 0.052437    0.000874    19  0.016667
2011-01-01 00:33:59 2011-01-01 00:33:59 0.033903    0.000556    19  0.016389
2011-01-01 00:35:00 2011-01-01 00:35:00 0.060076    0.001018    19  0.016944
2011-01-01 00:36:00 2011-01-01 00:36:00 0.121709    0.002028    19  0.016667
2011-01-01 00:36:59 2011-01-01 00:36:59 0.090517    0.001483    19  0.016389
2011-01-01 00:37:59 2011-01-01 00:37:59 0.088304    0.001472    19  0.016667
2011-01-01 00:39:00 2011-01-01 00:39:00 0.100654    0.001706    19  0.016944
2011-01-01 00:40:00 2011-01-01 00:40:00 0.034839    0.000581    19  0.016667
2011-01-01 00:40:59 2011-01-01 00:40:59 0.164753    0.002700    19  0.016389
2011-01-01 00:42:00 2011-01-01 00:42:00 0.214163    0.003629    19  0.016944
2011-01-01 00:43:00 2011-01-01 00:43:00 0.283706    0.004728    19  0.016667
2011-01-01 00:45:00 2011-01-01 00:45:00 0.055676    0.001856    19  0.033333
2011-01-01 00:46:00 2011-01-01 00:46:00 0.138059    0.002301    19  0.016667
2011-01-01 00:46:59 2011-01-01 00:46:59 0.339829    0.005569    19  0.016389
2011-01-01 00:48:00 2011-01-01 00:48:00 0.169921    0.002879    19  0.016944
2011-01-01 00:49:00 2011-01-01 00:49:00 0.072382    0.001206    19  0.016667
2011-01-01 00:49:59 2011-01-01 00:49:59 0.029009    0.000475    19  0.016389

This is the sample data.

And here is the error which I am getting.

--------------------------------------------------------------------------
ValueError                                Traceback (most recent call last)
 in ()
      1 # dfdata.Speed.rolling('60T', closed='right').sum()
----> 2 dfdata.Speed.rolling('60T', closed='right').sum()

~/anaconda3/lib/python3.6/site-packages/pandas/core/generic.py in rolling(self, window, min_periods, freq, center, win_type, on, axis, closed)
   6193                                    min_periods=min_periods, freq=freq,
   6194                                    center=center, win_type=win_type,
-> 6195                                    on=on, axis=axis, closed=closed)
   6196 
   6197         cls.rolling = rolling

~/anaconda3/lib/python3.6/site-packages/pandas/core/window.py in rolling(obj, win_type, **kwds)
   2050         return Window(obj, win_type=win_type, **kwds)
   2051 
-> 2052     return Rolling(obj, **kwds)
   2053 
   2054 

~/anaconda3/lib/python3.6/site-packages/pandas/core/window.py in __init__(self, obj, window, min_periods, freq, center, win_type, axis, on, closed, **kwargs)
     84         self.win_freq = None
     85         self.axis = obj._get_axis_number(axis) if axis is not None else None
---> 86         self.validate()
     87 
     88     @property

~/anaconda3/lib/python3.6/site-packages/pandas/core/window.py in validate(self)
   1085                                          timedelta))):
   1086 
-> 1087             self._validate_monotonic()
   1088             freq = self._validate_freq()
   1089 

~/anaconda3/lib/python3.6/site-packages/pandas/core/window.py in _validate_monotonic(self)
   1117             formatted = self.on or 'index'
   1118             raise ValueError("{0} must be "
-> 1119                              "monotonic".format(formatted))
   1120 
   1121     def _validate_freq(self):

ValueError: index must be monotonic

user_6615163 · Answer

I just copied your example, created a new CSV (data.csv) file to try. Finally I created new DataFrame (df) and executed your statement, it worked. Have a look and comment if you face any issues.

Note: Please check your pandas version, mine is 0.23.4 (with Python 3.6.5) and this is the link for documentation.

data.csv

Datetime,Speed,distance,IDs,totalHours
2011-01-01 00:19:00,0.041916,0.000710,19,0.016944
2011-01-01 00:20:00,0.033719,0.000562,19,0.016667
2011-01-01 00:20:59,0.153553,0.002517,19,0.016389
2011-01-01 00:21:59,0.142272,0.002371,19,0.016667
2011-01-01 00:23:00,0.033166,0.000562,19,0.016944
2011-01-01 00:24:00,0.037843,0.000631,19,0.016667
2011-01-01 00:26:00,0.050262,0.001675,19,0.033333
2011-01-01 00:27:00,0.032249,0.000537,19,0.016667
2011-01-01 00:27:59,0.180206,0.002953,19,0.016389
2011-01-01 00:29:00,0.133477,0.002262,19,0.016944
2011-01-01 00:30:00,0.128053,0.002134,19,0.016667
2011-01-01 00:30:59,0.041964,0.000688,19,0.016389
2011-01-01 00:32:00,0.072529,0.001229,19,0.016944
2011-01-01 00:33:00,0.052437,0.000874,19,0.016667
2011-01-01 00:33:59,0.033903,0.000556,19,0.016389
2011-01-01 00:35:00,0.060076,0.001018,19,0.016944
2011-01-01 00:36:00,0.121709,0.002028,19,0.016667
2011-01-01 00:36:59,0.090517,0.001483,19,0.016389
2011-01-01 00:37:59,0.088304,0.001472,19,0.016667
2011-01-01 00:39:00,0.100654,0.001706,19,0.016944
2011-01-01 00:40:00,0.034839,0.000581,19,0.016667
2011-01-01 00:40:59,0.164753,0.002700,19,0.016389
2011-01-01 00:42:00,0.214163,0.003629,19,0.016944
2011-01-01 00:43:00,0.283706,0.004728,19,0.016667
2011-01-01 00:45:00,0.055676,0.001856,19,0.033333
2011-01-01 00:46:00,0.138059,0.002301,19,0.016667
2011-01-01 00:46:59,0.339829,0.005569,19,0.016389
2011-01-01 00:48:00,0.169921,0.002879,19,0.016944
2011-01-01 00:49:00,0.072382,0.001206,19,0.016667
2011-01-01 00:49:59,0.029009,0.000475,19,0.016389

Statements executed on Python's interactive terminal

>>> import pandas as pd
>>>
>>> df = pd.read_csv("data.csv")
>>> df
               Datetime     Speed  distance  IDs  totalHours
0   2011-01-01 00:19:00  0.041916  0.000710   19    0.016944
1   2011-01-01 00:20:00  0.033719  0.000562   19    0.016667
2   2011-01-01 00:20:59  0.153553  0.002517   19    0.016389
3   2011-01-01 00:21:59  0.142272  0.002371   19    0.016667
4   2011-01-01 00:23:00  0.033166  0.000562   19    0.016944
5   2011-01-01 00:24:00  0.037843  0.000631   19    0.016667
6   2011-01-01 00:26:00  0.050262  0.001675   19    0.033333
7   2011-01-01 00:27:00  0.032249  0.000537   19    0.016667
8   2011-01-01 00:27:59  0.180206  0.002953   19    0.016389
9   2011-01-01 00:29:00  0.133477  0.002262   19    0.016944
10  2011-01-01 00:30:00  0.128053  0.002134   19    0.016667
11  2011-01-01 00:30:59  0.041964  0.000688   19    0.016389
12  2011-01-01 00:32:00  0.072529  0.001229   19    0.016944
13  2011-01-01 00:33:00  0.052437  0.000874   19    0.016667
14  2011-01-01 00:33:59  0.033903  0.000556   19    0.016389
15  2011-01-01 00:35:00  0.060076  0.001018   19    0.016944
16  2011-01-01 00:36:00  0.121709  0.002028   19    0.016667
17  2011-01-01 00:36:59  0.090517  0.001483   19    0.016389
18  2011-01-01 00:37:59  0.088304  0.001472   19    0.016667
19  2011-01-01 00:39:00  0.100654  0.001706   19    0.016944
20  2011-01-01 00:40:00  0.034839  0.000581   19    0.016667
21  2011-01-01 00:40:59  0.164753  0.002700   19    0.016389
22  2011-01-01 00:42:00  0.214163  0.003629   19    0.016944
23  2011-01-01 00:43:00  0.283706  0.004728   19    0.016667
24  2011-01-01 00:45:00  0.055676  0.001856   19    0.033333
25  2011-01-01 00:46:00  0.138059  0.002301   19    0.016667
26  2011-01-01 00:46:59  0.339829  0.005569   19    0.016389
27  2011-01-01 00:48:00  0.169921  0.002879   19    0.016944
28  2011-01-01 00:49:00  0.072382  0.001206   19    0.016667
29  2011-01-01 00:49:59  0.029009  0.000475   19    0.016389
>>>
>>> df.index = pd.to_datetime(df.Datetime)
>>> df
                                Datetime     Speed  distance  IDs  totalHours
Datetime
2011-01-01 00:19:00  2011-01-01 00:19:00  0.041916  0.000710   19    0.016944
2011-01-01 00:20:00  2011-01-01 00:20:00  0.033719  0.000562   19    0.016667
2011-01-01 00:20:59  2011-01-01 00:20:59  0.153553  0.002517   19    0.016389
2011-01-01 00:21:59  2011-01-01 00:21:59  0.142272  0.002371   19    0.016667
2011-01-01 00:23:00  2011-01-01 00:23:00  0.033166  0.000562   19    0.016944
2011-01-01 00:24:00  2011-01-01 00:24:00  0.037843  0.000631   19    0.016667
2011-01-01 00:26:00  2011-01-01 00:26:00  0.050262  0.001675   19    0.033333
2011-01-01 00:27:00  2011-01-01 00:27:00  0.032249  0.000537   19    0.016667
2011-01-01 00:27:59  2011-01-01 00:27:59  0.180206  0.002953   19    0.016389
2011-01-01 00:29:00  2011-01-01 00:29:00  0.133477  0.002262   19    0.016944
2011-01-01 00:30:00  2011-01-01 00:30:00  0.128053  0.002134   19    0.016667
2011-01-01 00:30:59  2011-01-01 00:30:59  0.041964  0.000688   19    0.016389
2011-01-01 00:32:00  2011-01-01 00:32:00  0.072529  0.001229   19    0.016944
2011-01-01 00:33:00  2011-01-01 00:33:00  0.052437  0.000874   19    0.016667
2011-01-01 00:33:59  2011-01-01 00:33:59  0.033903  0.000556   19    0.016389
2011-01-01 00:35:00  2011-01-01 00:35:00  0.060076  0.001018   19    0.016944
2011-01-01 00:36:00  2011-01-01 00:36:00  0.121709  0.002028   19    0.016667
2011-01-01 00:36:59  2011-01-01 00:36:59  0.090517  0.001483   19    0.016389
2011-01-01 00:37:59  2011-01-01 00:37:59  0.088304  0.001472   19    0.016667
2011-01-01 00:39:00  2011-01-01 00:39:00  0.100654  0.001706   19    0.016944
2011-01-01 00:40:00  2011-01-01 00:40:00  0.034839  0.000581   19    0.016667
2011-01-01 00:40:59  2011-01-01 00:40:59  0.164753  0.002700   19    0.016389
2011-01-01 00:42:00  2011-01-01 00:42:00  0.214163  0.003629   19    0.016944
2011-01-01 00:43:00  2011-01-01 00:43:00  0.283706  0.004728   19    0.016667
2011-01-01 00:45:00  2011-01-01 00:45:00  0.055676  0.001856   19    0.033333
2011-01-01 00:46:00  2011-01-01 00:46:00  0.138059  0.002301   19    0.016667
2011-01-01 00:46:59  2011-01-01 00:46:59  0.339829  0.005569   19    0.016389
2011-01-01 00:48:00  2011-01-01 00:48:00  0.169921  0.002879   19    0.016944
2011-01-01 00:49:00  2011-01-01 00:49:00  0.072382  0.001206   19    0.016667
2011-01-01 00:49:59  2011-01-01 00:49:59  0.029009  0.000475   19    0.016389
>>>
>>>
>>> df.Speed.rolling('60T', closed='right').sum()
Datetime
2011-01-01 00:19:00    0.041916
2011-01-01 00:20:00    0.075635
2011-01-01 00:20:59    0.229188
2011-01-01 00:21:59    0.371460
2011-01-01 00:23:00    0.404626
2011-01-01 00:24:00    0.442469
2011-01-01 00:26:00    0.492731
2011-01-01 00:27:00    0.524980
2011-01-01 00:27:59    0.705186
2011-01-01 00:29:00    0.838663
2011-01-01 00:30:00    0.966716
2011-01-01 00:30:59    1.008680
2011-01-01 00:32:00    1.081209
2011-01-01 00:33:00    1.133646
2011-01-01 00:33:59    1.167549
2011-01-01 00:35:00    1.227625
2011-01-01 00:36:00    1.349334
2011-01-01 00:36:59    1.439851
2011-01-01 00:37:59    1.528155
2011-01-01 00:39:00    1.628809
2011-01-01 00:40:00    1.663648
2011-01-01 00:40:59    1.828401
2011-01-01 00:42:00    2.042564
2011-01-01 00:43:00    2.326270
2011-01-01 00:45:00    2.381946
2011-01-01 00:46:00    2.520005
2011-01-01 00:46:59    2.859834
2011-01-01 00:48:00    3.029755
2011-01-01 00:49:00    3.102137
2011-01-01 00:49:59    3.131146
Name: Speed, dtype: float64
>>>

ValueError: index must be monotonic

1 Answers