CODEWITHSUNDEEP
pythondatetimepandasdataframe
Paradigm
Paradigm

Reputation: 149

Pandas: Parsing / converting a column as date after importing from read_sql

I have the following code: 

from pandas.io import sql  
import sqlite3  
conn = sqlite3.connect('breakDownRecs.sqlite')  
query = "SELECT * FROM indRecs"  
df = sql.read_sql(query, con=conn)  #parse_dates=['DateDayId'])  
print(df)

I get (correctly) something like: 

               DateID           Usr EventTypeM  DateDayID  DateMonthID
0      20151007221243        Noelle        LVL   20151007       201510
1      20151007225607         Morak        QST   20151007       201510
2      20151007225621   Austinpower        QST   20151007       201510
...

The question is how can I parse it so to become DateDayID column as a date, so to be indexed later? If I provide: 

df = sql.read_sql(query, con=conn, parse_dates=['DateDayID'])

It gives me 

               DateID           Usr EventTypeM DateDayID  DateMonthID
0      20151007221243        Noelle        LVL       NaT       201510
1      20151007225607         Morak        QST       NaT       201510
...

Thank you!

Upvotes: 4

Views: 12630

Answers (3)

Vlad Bezden
Vlad Bezden

Reputation: 89677

df = sql.read_sql(query, con=conn, parse_dates=['DateID'])

or 

df = sql.read_sql(query, con=conn, parse_dates={'DateID': {'format': '%Y-%m-%d'}})

Upvotes: 4

jezrael
jezrael

Reputation: 863146

I think you need to_datetime with parameter format:

df['DateID'] = pd.to_datetime(df.DateID, format='%Y%m%d%H%M%S')
print df
               DateID     Usr EventTypeM DateDayID  DateMonthID
0 2015-10-07 22:12:43  Noelle        LVL       NaT       201510
1 2015-10-07 22:56:07   Morak        QST       NaT       201510

Upvotes: 1

piRSquared
piRSquared

Reputation: 294488

Try:

import pandas as pd

df.loc[:, 'DateID'] = pd.to_datetime(df.DateID)

Upvotes: 0

Related Questions