Zanam Zanam - 1 month ago 19
Python Question

Pandas: reading data from PyODBC and parsing datetime from different columns

I am trying the following:

import pyodbc
from datetime import timedelta
import pandas as pd

query = "SELECT dateCol, hourCol, loadCol FROM myTable"
cursor = conn.cursor()
cursor.execute(query)
zload = pd.DataFrame({'DateTime':[], 'Load':[]})
for row in cursor:
dateTime = pd.to_datetime(row[0], format='%Y-%m-%d') + td(hours=int(row[1]))
load = float(row[2])
zload.append({dateTime: load})


I am not able to get the following line of code right:
zload.append({dateTime: load})


I am trying to create a dataframe with
datatime
as index and
load
as value.

A sample row looks as:
row

(u'2002-01-01', '1 ', Decimal('8331.46'))


where
row[0]
is date;
row[1]
is hour and
row[2]
is load

Or is there an alternate way to achieve the above where I read the table from database and create pandas dataframe out of it using logic above.

Answer

I think you can simply read your data directly to DataFrame using read_sql() method.

Demo:

In [29]: df = pd.read_sql(query, conn, parse_dates=['dateCol'])

In [30]: df
Out[30]:
     dateCol  hourCol  loadCol
0 2002-01-01        1  8331.46
1 2002-02-28       13   331.22

In [31]: df.dtypes
Out[31]:
dateCol    datetime64[ns]
hourCol             int64
loadCol           float64
dtype: object

In [32]: df.index = df.pop('dateCol') + pd.to_timedelta(df.pop('hourCol').astype(str) + ' hours')

In [33]: df
Out[33]:
                     loadCol
2002-01-01 01:00:00  8331.46
2002-02-28 13:00:00   331.22

In [35]: df.index.dtype
Out[35]: dtype('<M8[ns]')

In [36]: df.index.dtype_str
Out[36]: 'datetime64[ns]'
Comments