Pandas will not recognize datetimeindex from csv file

Question

I am trying to read this file of two columns into a dataframe. It doesn't read as datetime.

INPUT :
DATE    DTB3
8/4/2014    0.0004
8/5/2014    0.0003
8/6/2014    0.0003
........

Using this code :

T1 = pd.read_csv(FRED_file,sep=',',header=None,names=['DATE','DTB3'],dtype= {'DATE':'str','DTB3':'str'},parse_dates=['DATE'],index_col='DATE')
T1.drop(T1.index[0],inplace=True)
T1.index = pd.to_datetime(T1.index, format = '%m/%d%Y')
T1.index = T1.index.strftime('%Y-%m-%d')
T1.index

Output :

Index(['2014-08-04', '2014-08-05', '2014-08-06', '2014-08-07', '2014-08-08',
       '2014-08-11', '2014-08-12', '2014-08-13', '2014-08-14', '2014-08-15',
           ...
       '2018-09-07', '2018-09-10', '2018-09-11', '2018-09-12', '2018-09-13',
       '2018-09-14', '2018-09-17', '2018-09-18', '2018-09-19', '2018-09-20'],
       dtype='object', length=1079)

Sven Harris · Accepted Answer · 2018-09-25 07:11:02Z

2

Looks like you are converting the index to a datetime in the line to_datetime but then converting it back to a string using strftime (string format time). I think if you remove that strftime line you should have a datetime index

answered Sep 25, 2018 at 7:11

Sven Harris

2,9491 gold badge13 silver badges21 bronze badges

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

1 Comment

576i Over a year ago

This is the correct answer. Remove the T1.index = T1.index.strftime('%Y-%m-%d') line.

jezrael · Accepted Answer · 2018-09-25 07:24:04Z

Reason why it not recognise datetimes function read_csv is parameter header=None - it convert first line of csv to first line of data (and index) and converting to datetimes silently failed:

temp=u"""DATE,DTB3
8/4/2014,0.0004
8/5/2014,0.0003
8/6/2014,0.0003"""
#after testing replace 'pd.compat.StringIO(temp)' to FRED_file
T1 = pd.read_csv(pd.compat.StringIO(temp), 
                 header=None,
                 names=['DATE','DTB3'],
                 dtype= {'DTB3':'str'},
                 parse_dates=['DATE'],
                 index_col='DATE'
                 )

print (T1)
            DTB3
DATE            
DATE        DTB3
8/4/2014  0.0004
8/5/2014  0.0003
8/6/2014  0.0003

print (T1.index)

Index(['DATE', '8/4/2014', '8/5/2014', '8/6/2014'], dtype='object', name='DATE')

You can omit parameter header , names - because file contains csv header sep - because sep=',' is default parameter, 'DATE':'str' from dtype - if / is in values it is converted to strings:

import pandas as pd

temp=u"""DATE,DTB3
8/4/2014,0.0004
8/5/2014,0.0003
8/6/2014,0.0003"""
#after testing replace 'pd.compat.StringIO(temp)' to FRED_file
T1 = pd.read_csv(pd.compat.StringIO(temp), 
                 dtype= {'DTB3':'str'},
                 parse_dates=['DATE'],
                 index_col='DATE'
                 )

print (T1)
              DTB3
DATE              
2014-08-04  0.0004
2014-08-05  0.0003
2014-08-06  0.0003

print (T1.index)
DatetimeIndex(['2014-08-04', '2014-08-05', '2014-08-06'], 
               dtype='datetime64[ns]', name='DATE', freq=None)

Collectives™ on Stack Overflow

Pandas will not recognize datetimeindex from csv file

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related