Vitali Halapjan Vitali Halapjan - 5 months ago 20
Python Question

Create pandas DateTimeIndex from 12-hour-clock format

I'm looking for a way to create a datetimeindex in pandas. My data looks as follows:

Date Time AAA
0 06/17/2016 03:00:00 PM 19.13
1 06/17/2016 02:00:00 PM 19.13
2 06/17/2016 01:00:00 PM 19.26
3 06/17/2016 12:00:00 AM 19.28
4 06/17/2016 11:00:00 AM 19.28


The result I want to obtain is:

AAA
Date
2016-06-17 15:00:00 19.16
2016-06-17 14:00:00 19.14
2016-06-17 13:00:00 19.18
2016-06-17 12:00:00 19.27
2016-06-17 11:00:00 19.27


I am note sure how to efficiently do this since my Time column uses 12-hour clock format.

Answer

you can do it using to_datetime as:

df
Out[38]: 
         Date         Time    AAA
0  06/17/2016  03:00:00 PM  19.13
1  06/17/2016  02:00:00 PM  19.13
2  06/17/2016  01:00:00 PM  19.26
3  06/17/2016  12:00:00 AM  19.28
4  06/17/2016  11:00:00 AM  19.28

In [39]: df['Date']=pd.to_datetime(df['Date']+ ' '+df['Time'])

In [40]: df
Out[40]: 
                 Date         Time    AAA
0 2016-06-17 15:00:00  03:00:00 PM  19.13
1 2016-06-17 14:00:00  02:00:00 PM  19.13
2 2016-06-17 13:00:00  01:00:00 PM  19.26
3 2016-06-17 00:00:00  12:00:00 AM  19.28
4 2016-06-17 11:00:00  11:00:00 AM  19.28

In [40]: df=df.drop(['Time','Date'],axis=1).set_index(df['Date'])

In [41]: df
Out[41]: 
                       AAA
Date                      
2016-06-17 15:00:00  19.13
2016-06-17 14:00:00  19.13
2016-06-17 13:00:00  19.26
2016-06-17 00:00:00  19.28
2016-06-17 11:00:00  19.28