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

Question

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.

MaxU - stand with Ukraine · Accepted Answer · 2016-11-01 20:15:17Z

2

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]'

edited Nov 1, 2016 at 20:15

answered Nov 1, 2016 at 19:53

MaxU - stand with Ukraine

212k37 gold badges402 silver badges436 bronze badges

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

5 Comments

Zanam Over a year ago

This creates three columns: date, hour and load. I need to create a datetime index and then load as value

Zanam Over a year ago

loadCol is the third column in 'df'. I want the 'dateCol' and 'hourCol' combined as 'DateTime'

Zanam Over a year ago

I get the following error: File "pandas\tslib.pyx", line 2933, in pandas.tslib.array_to_timedelta64 (pandas\tslib.c:52763) File "pandas\tslib.pyx", line 3256, in pandas.tslib.convert_to_timedelta64 (pandas\tslib.c:56387) File "pandas\tslib.pyx", line 3169, in pandas.tslib.parse_timedelta_string (pandas\tslib.c:54962) ValueError: expected hh:mm:ss format

MaxU - stand with Ukraine Over a year ago

@Zanam, what is your Pandas version? I'm using Pandas 0.19.0

Zanam Over a year ago

My db was giving out corrupt data. I use 0.18 but your code is brilliant.

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related