Pandas - Parsing datetime column from sqlite database

Question

I'm currently exploring the python library pandas with some hands-on data, where one of the columns contains a datetime object. However, when a table is parsed using the DataFrame method, the datetime objects in the date column are parsed to an initial value e.g. 1970-01-16 14:12:28.

If I for instance have a np.array with following content:

np.array(result, dtype=my_dtype) =
array([ (datetime.datetime(2012, 9, 9, 0, 0), datetime.datetime(2012, 9, 8, 15, 10)),
dtype=[('Date', ('<M8[us]', {})), ('Forecasting', ('<M8[us]', {})),

when parsed, it will return following:

test = pandas.DataFrame(np.array(result, dtype=my_dtype))
test['Date'] =
1970-01-16 14:12:28.800000

and

test['Forecasting'] =
1970-01-16 14:11:57

Is this a bug, or what am I doing wrong?

FYI: pandas.version = 0.8.1, numpy.version = 1.6.2 and Python 2.7.3

Pierre GM · Accepted Answer · 2012-09-11 13:27:45Z

2

I'm not familiar with panda, but I suspect something is wrong with the dtype you're choosing for your fields. It looks like you're using [us], that is, microseconds from the epoch (1970-01-01), when you probably should be using days from the epoch.

Another possibility is to use basic datetime objects in your ndarray, by using dtype=object.

edited Sep 11, 2012 at 13:27

answered Sep 11, 2012 at 12:22

Pierre GM

20.5k3 gold badges58 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

aagaard Over a year ago

Yes, it is probably that which causes incorrect parsing. I'm parsing the datetime with the np.datetime64 datatype, but how can I do this any different?

Pierre GM Over a year ago

Check this link for NumPy and datetime, in particular for setting your frequency. Note that while it works, it's still not considered as 100% foolproof. An alternative would be to make a ndarray of datetime objects by choosing dtype=object.

aagaard Over a year ago

It actually works if I change the dtype to object instead. But I don't understand how I can change the datetime datatype by changing the epoch? (like datetime['D'])

Pierre GM Over a year ago

'<M8[D]' in your first line ?

aagaard Over a year ago

Sorry, it doesn't work. Tried '<M8[D]' and 'np.datetime64[D]'.

|

Wes McKinney · Accepted Answer · 2012-10-20 20:19:59Z

1

This is a bug. Reported here: http://github.com/pydata/pandas/issues/2095

answered Oct 20, 2012 at 20:19

Wes McKinney

106k32 gold badges146 silver badges109 bronze badges

Collectives™ on Stack Overflow

Pandas - Parsing datetime column from sqlite database

2 Answers 2

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related