pandas dataframe group by uneven timestamp

bbc Published at Dev

bbc

I have a dataframe of market prices, timestamps are in microsecond, eg.

Time       Bid
0  2014-03-03 23:30:30.224323  0.892500
1  2014-03-03 23:30:30.224390  0.892525
2  2014-03-03 23:30:30.224408  0.892525
3  2014-03-03 23:30:30.364299  0.892525
4  2014-03-03 23:30:31.022652  0.892500
5  2014-03-03 23:30:31.022702  0.892525
6  2014-03-03 23:30:31.866949  0.892525
7  2014-03-03 23:30:33.366843  0.892525
8  2014-03-03 23:30:33.858239  0.892525
9  2014-03-03 23:30:34.360997  0.892525
10 2014-03-03 23:30:35.034307  0.892525
11 2014-03-03 23:30:36.110848  0.892525
12 2014-03-03 23:30:36.359973  0.892525
13 2014-03-03 23:30:38.111191  0.892525
14 2014-03-03 23:30:41.599924  0.892525
15 2014-03-03 23:30:41.599972  0.892500

How do I group by Time by strip away microsecond, for example, how to convert a OHLC(open, high, low, close) structure of evenly time slot(1min, 5mins, 1hour etc), and also counts for each time slot? I tried to add another column with

e['Time2'] = pd.to_datetime(e.Time, format='%Y/%m/%d %H:%M:%S')

dropping %f, but Time2 column looks identical to Time column.

Many thanks,

how to convert it into something like

Andy Hayden

You need to make the datetime the index so that you can resample:

In [11]: df = df.set_index('Time')

In [12]: df.resample('5min', how='ohlc')
Out[12]:
                        Bid
                       open      high     low   close
Time
2014-03-03 23:30:00  0.8925  0.892525  0.8925  0.8925

Note: Unfortunately to_datetime doesn't raise if it fails (at least by default) so it often a good idea to check the dtype of the column is datetime64...

Collected from the Internet

Please contact [email protected] to delete if infringement.