adele adele - 2 months ago 14
Python Question

Dataframe column showing half hour intervals

I can create an hour of day column in pandas as follows:

data['hod'] = [r.hour for r in data.index]


This allows me to easily check stats on my data based on the time of day. How can I create a similar column showing every half hour?

Example data:

Low High Open hod
Timestamp
2014-03-04 09:30:00 1783.50 1786.75 1783.50 9
2014-03-04 09:45:00 1784.50 1788.75 1784.50 9
2014-03-04 10:00:00 1785.75 1789.50 1788.25 10
2014-03-04 10:15:00 1787.75 1789.50 1788.50 10
2014-03-04 10:30:00 1788.25 1791.25 1789.00 10
... ... ... ... ...
2016-06-10 15:00:00 2079.50 2082.00 2082.00 15
2016-06-10 15:15:00 2079.50 2083.00 2079.75 15
2016-06-10 15:30:00 2082.50 2084.25 2082.75 15
2016-06-10 15:45:00 2083.50 2088.25 2083.50 15
2016-06-10 16:00:00 2085.75 2088.25 2086.25 16


Desired output.

I would like a new column 'hod2' showing times every half hour as follows:

Low High Open hod2
Timestamp
2014-03-04 09:30:00 1783.50 1786.75 1783.50 9:30
2014-03-04 09:45:00 1784.50 1788.75 1784.50 9:30
2014-03-04 10:00:00 1785.75 1789.50 1788.25 10:00
2014-03-04 10:15:00 1787.75 1789.50 1788.50 10:00
2014-03-04 10:30:00 1788.25 1791.25 1789.00 10:30

Answer

Since your index is a DatetimeIndex, it has certain attributes that we can access like hour. Another attribute you may find useful for your task is minute. Something like this should work for displaying half-hour increments.

data['hod2'] = ['{0}:{1}'.format(r.hour, '30' if round(float(r.minute)/60) == 1 else '00') for r in data.index]

EDIT

A much cleaner version of this was suggested by Alberto Garcia-Raboso in the comments below:

data['hod2'] = ['{}:{:02d}'.format(r.hour, (r.minute//30)*30) for r in data.index]