[ How to combine a hierarchical date and time index into a single datetime index in Pandas? ]
Suppose I have the following dataframe:
temp
2014-11-20 12:45:00 51
2014-11-20 12:46:00 43
2014-11-20 12:47:00 44
2014-11-21 12:45:00 44
2014-11-21 12:46:00 46
2014-11-21 12:47:00 48
2014-11-22 12:45:00 38
2014-11-22 12:46:00 32
2014-11-22 12:47:00 37
If you copy the above and use pd.read_clipboard()
I believe you should get a hierarchical index (or MultiIndex)
The first index is the date and the second index is the time as strings. How can I combine the two indexes into a single datetime index?
(Note this is not about using pd.read_csv()
parse_dates
option because the dataframe is not coming from a csv file, the question is how to do it with an existing dataframe.)
Answer 1
You can use the to_datetime
method to parse strings to datetime values. To use this, I think the easiest is to first just concatenate the date and time strings leading to a single level string index:
In [184]: s
Out[184]:
temp
2014-11-20 12:45:00 51
12:46:00 43
12:47:00 44
2014-11-21 12:45:00 44
12:46:00 46
12:47:00 48
2014-11-22 12:45:00 38
12:46:00 32
12:47:00 37
In [187]: s.index = s.index.get_level_values(0).values + ' ' + s.index.get_level_values(1)
In [188]: s.index
Out[188]: Index([u'2014-11-20 12:45:00', u'2014-11-20 12:46:00', ... u'2014-11-22 12:47:00'], dtype='object')
And then you can use to_datetime
to convert the strings to a DatetimeIndex:
In [189]: s.index = pd.to_datetime(s.index)
In [190]: s.index
Out[190]:
<class 'pandas.tseries.index.DatetimeIndex'>
[2014-11-20 12:45:00, ..., 2014-11-22 12:47:00]
Length: 9, Freq: None, Timezone: None