Pandas: Converting Timestamp and Milliseconds to DatetimeIndex

Question

With

df = read_csv('data\query.csv')

I'am getting:

    TIMESTAMP   MILLISECONDS    PRICE
0   15.10.2012 08:00:06     350     24.6
1   15.10.2012 08:00:06     630     24.7
2   15.10.2012 08:00:06     640     24.9
3   15.10.2012 08:00:06     650     24.5
4   15.10.2012 08:00:06     710     24.3

I figured out that this one

df = read_csv('data\query.csv', parse_dates=[[0, 1]], index_col=0)

is concatenating the first two columns to a string but still not recognizing the index as a DatetimeIndex

Additionally this one

Import datetime
datetime.datetime.strptime("15.10.2012 15:30:00 890", "%d.%m.%Y %H:%M:%S %f")

is doing the conversion job.

QUESTION: how to do the conversion and DatetimeIndex in one rush at read_csv?

CAn you show a snippet of the csv file? Maybe you need a converter. — Wouter Overmeire, Oct 23 '12 at 06:42
My csv contains a header row, three columns, columns are semicolon delmited: TIMESTAMP;MILLISECONDS;PRICE 15.10.2012 08:00:06;350;24.6 — user1766682, Oct 28 '12 at 11:18

score 1 · Answer 1 · answered Oct 30 '12 at 20:07

In [188]: from dateutil import parser

In [189]: from StringIO import StringIO

In [190]: data = """\
TIMESTAMP;MILLISECONDS;PRICE
15.10.2012 08:00:06;350;24.6
"""

In [191]: def date_parser(s):
    return parser.parse(s[:-4]).replace(microsecond=int(s[-3:])*1000)
   .....:

In [192]: df = pd.read_csv(StringIO(data), sep=';', parse_dates=[[0, 1]], date_parser=date_parser)

In [193]: df
Out[193]:
       TIMESTAMP_MILLISECONDS  PRICE
0  2012-10-15 08:00:06.350000   24.6

In [194]: df.set_index('TIMESTAMP_MILLISECONDS', inplace=True)
Out[194]:
                            PRICE
TIMESTAMP_MILLISECONDS
2012-10-15 08:00:06.350000   24.6

In [195]: df.index
Out[195]:
<class 'pandas.tseries.index.DatetimeIndex'>
[2012-10-15 08:00:06.350000]
Length: 1, Freq: None, Timezone: None

score 0 · Answer 2 · answered Oct 31 '12 at 20:02

0

please consider adding the converter to pandas code: https://github.com/pydata/pandas/blob/master/pandas/tseries/converter.py

See also: https://github.com/pydata/pandas/issues/1180

answered Oct 31 '12 at 20:02

Timmie

15,995
3
14
7

score 0 · Answer 3 · answered Mar 20 '17 at 03:55

0

Have you tried .to_timestamp()?

df_trend = pd.read_csv('googletrends.csv',parse_dates=True, index_col=0)
ts_iphone = df_trend.ix['2007':'2013','iphone'].to_timestamp()

You can find the documentation here.

answered Mar 20 '17 at 03:55

1man

5,216
7
42
56

Pandas: Converting Timestamp and Milliseconds to DatetimeIndex

3 Answers3