CODEWITHSUNDEEP
pythonpandas
kroonike
kroonike

Reputation: 1129

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.

Upvotes: 0

Views: 277

Answers (2)

shivsn
shivsn

Reputation: 7838

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

Upvotes: 2

piRSquared
piRSquared

Reputation: 294338

Using date objects as opposed to parsing strings

df = pd.DataFrame([
        ['06/17/2016', '03:00:00 PM', 19.13],
        ['06/17/2016', '02:00:00 PM', 19.13],
        ['06/17/2016', '01:00:00 PM', 19.26],
        ['06/17/2016', '12:00:00 AM', 19.28],
        ['06/17/2016', '11:00:00 AM', 19.28],
    ],
    columns=['Date', 'Time', 'AAA'],
)
df.Date = pd.to_datetime(df.Date)
df.Time = pd.to_datetime(df.Time) - pd.DatetimeIndex(df.Time).date

df.set_index(df.Date + df.Time)[['AAA']]

                       AAA
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

Upvotes: 2

Related Questions