0

I have a table in a SQL Server which contains, among other things, a DateTime column From_UTC and a DateTime NULL column To_UTC. I try to read it into a pandas DataFrame.

The non-NULL column comes in fine as a datetime64, but the other gets datatype Object.

import pandas.io.sql as psql
import adodbapi
conn = adodbapi.connect(...)
df = psql.read_frame("SELECT Unit, To_UTC, From_UTC, CategoryID FROM T_Alarm WHERE ...", con=conn)
df.dtypes

Unit                  object
To_UTC                object
From_UTC      datetime64[ns]
CategoryID             int64
dtype: object

df.To_UTC[0]
==> pywintypes.datetime(2011, 9, 6, 13, 51, 56)

If I include a WHERE To_UTC IS NOT NULL in my query, everything works fine.

I guess that a combination of adodbapi, the pywintypes.datetime objects, and the NULLs in the result prevents pandas from coercing into the right type?

I tried df.convert_objects(), but it didn't help.

UPDATE:

If I do df.To_UTC = pandas.Series(df.To_UTC, dtype='datetime64[ns]') then I get the DataFrame I want. But I would still like a more general solution so that I don't have to make this hack everywhere I do frame queries with nullable datetime results.

Bjarke Ebert
  • 1,920
  • 18
  • 26
  • Can you show us how your data looks like? Especially the values of the DateTime Null Column? – dorvak Nov 20 '13 at 13:01
  • The Datetime NULL column (To_UTC) is just a bunch of datetimes with a few NULLs mixed in. It is just the NULLs that cause the column to get dtype Object, since when I filter them out (in the SQL 'where' clause) I get the expected result. – Bjarke Ebert Nov 26 '13 at 11:41
  • What will a NULL in the data "mean" semantically for your code? Could you possible replace the NULLs with a minimum date value or similar? – Allan S. Hansen Nov 26 '13 at 12:15
  • No, this table is used by a lot of other code (mostly C#), I am just doing some data analysis on it from Python, so I'm not in a position to change the semantics / data structure. The rows of the table denote a kind of alarms with begin and end times. Alarms that are still open will have only From_UTC set, while To_UTC will be NULL. When the alarm closes, the To_UTC will be set. – Bjarke Ebert Nov 26 '13 at 13:41
  • The NULL values should translate to a NaT value in Pandas. This is what I get with my workaround seen under the "UPDATE:" heading – Bjarke Ebert Nov 26 '13 at 13:44

0 Answers0