2

So I had everything working with my data until I added milliseconds to the date/time field.

selected = selected['2017-02-20 16:10:05':'2017-02-20 16:20:06'] is my statement to filter my data_frame**

The below data works when milliseconds are 0000

enter image description here

The below data errors when milliseconds are properly populated

enter image description here

Update: I have tried 

selected.sort_index()
selected.ix['2017-02-20 16:10:05':'2017-02-20 16:20:06']
selected.ix[datetime.date(year=2017,month=2,day=20):datetime.date(year=2017,month=2,day=21)]

TypeError: '>' not supported between instances of 'str' and 'datetime.date'

Then I get the following

ValueError                                Traceback (most recent call last)
/usr/local/lib/python3.6/site-packages/pandas/indexes/base.py in get_slice_bound(self, label, side, kind)
   3121             try:
-> 3122                 return self._searchsorted_monotonic(label, side)
   3123             except ValueError:

/usr/local/lib/python3.6/site-packages/pandas/indexes/base.py in _searchsorted_monotonic(self, label, side)
   3087 
-> 3088         raise ValueError('index must be monotonic increasing or decreasing')
   3089 

ValueError: index must be monotonic increasing or decreasing

During handling of the above exception, another exception occurred:

KeyError                                  Traceback (most recent call last)
<ipython-input-43-a7bf11322082> in <module>()
     11 selected = selected.query('y_acc < 0.4 and z_acc > -0.4')
     12 selected = selected.query('z_acc < 0.4 and z_acc > -0.4')
---> 13 selected = selected['2017-02-20 16:10:05':'2017-02-20 16:20:06']
     14 
     15 #%pylab inline

/usr/local/lib/python3.6/site-packages/pandas/core/frame.py in __getitem__(self, key)
   2045 
   2046         # see if we can slice the rows
-> 2047         indexer = convert_to_index_sliceable(self, key)
   2048         if indexer is not None:
   2049             return self._getitem_slice(indexer)

/usr/local/lib/python3.6/site-packages/pandas/core/indexing.py in convert_to_index_sliceable(obj, key)
   1772     idx = obj.index
   1773     if isinstance(key, slice):
-> 1774         return idx._convert_slice_indexer(key, kind='getitem')
   1775 
   1776     elif isinstance(key, compat.string_types):

/usr/local/lib/python3.6/site-packages/pandas/indexes/base.py in _convert_slice_indexer(self, key, kind)
   1236         else:
   1237             try:
-> 1238                 indexer = self.slice_indexer(start, stop, step, kind=kind)
   1239             except Exception:
   1240                 if is_index_slice:

/usr/local/lib/python3.6/site-packages/pandas/indexes/base.py in slice_indexer(self, start, end, step, kind)
   2995         """
   2996         start_slice, end_slice = self.slice_locs(start, end, step=step,
-> 2997                                                  kind=kind)
   2998 
   2999         # return a slice

/usr/local/lib/python3.6/site-packages/pandas/indexes/base.py in slice_locs(self, start, end, step, kind)
   3174         start_slice = None
   3175         if start is not None:
-> 3176             start_slice = self.get_slice_bound(start, 'left', kind)
   3177         if start_slice is None:
   3178             start_slice = 0

/usr/local/lib/python3.6/site-packages/pandas/indexes/base.py in get_slice_bound(self, label, side, kind)
   3123             except ValueError:
   3124                 # raise the original KeyError
-> 3125                 raise err
   3126 
   3127         if isinstance(slc, np.ndarray):

/usr/local/lib/python3.6/site-packages/pandas/indexes/base.py in get_slice_bound(self, label, side, kind)
   3117         # we need to look up the label
   3118         try:
-> 3119             slc = self.get_loc(label)
   3120         except KeyError as err:
   3121             try:

/usr/local/lib/python3.6/site-packages/pandas/indexes/base.py in get_loc(self, key, method, tolerance)
   2134                 return self._engine.get_loc(key)
   2135             except KeyError:
-> 2136                 return self._engine.get_loc(self._maybe_cast_indexer(key))
   2137 
   2138         indexer = self.get_indexer([key], method=method, tolerance=tolerance)

pandas/index.pyx in pandas.index.IndexEngine.get_loc (pandas/index.c:4433)()

pandas/index.pyx in pandas.index.IndexEngine.get_loc (pandas/index.c:4279)()

pandas/src/hashtable_class_helper.pxi in pandas.hashtable.PyObjectHashTable.get_item (pandas/hashtable.c:13742)()

pandas/src/hashtable_class_helper.pxi in pandas.hashtable.PyObjectHashTable.get_item (pandas/hashtable.c:13696)()

KeyError: '2017-02-20 16:10:05'
Burf2000
  • 5,001
  • 14
  • 58
  • 117

2 Answers2

5

To filter by datetime index

selected_subset = selected.ix['2017-02-20 16:10:05':'2017-02-20 16:20:06']

reference: filtering pandas dataframes on dates

I tried this and it worked for me:

import pandas as pd
import numpy as np
from datetime import datetime

date_time_1 = datetime.now()

date_time_2 = datetime.now()

data = [{'time': date_time_1, 'val': 1}, {'time': date_time_2, 'val':2}]

df = pd.DataFrame.from_dict(data, orient='columns')

df.set_index(['time'], inplace=True)

df = df.sort_index() # This is an important step if 'time' field is not sorted
df


                            val
time    
2017-02-28 19:19:00.777225  1
2017-02-28 19:19:04.605302  2

df.ix['2017-02-28 19:19:00':'2017-02-28 19:19:03']


                            val
time    
2017-02-28 19:19:00.777225  1
Community
  • 1
  • 1
Vikash Singh
  • 13,213
  • 8
  • 40
  • 70
1

The issue turned out to be it thought the date index was a string. I found this out using:

selected.ix[datetime.date(year=2017,month=2,day=20):datetime.date(year=2017,month=2,day=21)]

Which returned the error: TypeError: '>' not supported between instances of 'str' and 'datetime.date'

I then added parse_dates=True ro my read_csv commandand that fixed the issue.

I could then just use 

selected = selected['2017-02-20 16:10:05':'2017-02-20 16:11:06']
Burf2000
  • 5,001
  • 14
  • 58
  • 117