How to change dataframe row index to datetime.date while reading from csv?

Question

df.index[0] I want to be datetime.date(2006, 8, 27).

While reading from file, df = pd.read_csv(filePath,index_col="Date"), df.index[0] appears as string '2006-08-27'.

I tried:

dateparser = lambda s: datetime.datetime.strptime(s,"%Y-%m-%d").date()
df = pd.read_csv(filePath,parse_dates=["Date"], date_parser=dateparser,index_col="Date")

Now, df.index[0] appears as Timestamp('2006-08-27 00:00:00').

How to make df.index[0] as datetime.date(2006, 8, 27)?

used sample csv:

Date,Symbol,Series,Prev Close,Open,High,Low,Last,Close,VWAP,Volume,Turnover,Trades,Deliverable Volume,%Deliverble
2006-08-27,,,,,,,,,,,,,,
2006-08-28,ATFC,EQ,365.0,521.0,569.0,502.0,553.0,554.25,552.0,15166163,837176013020000.0,,3777529,0.24910000000000002
2006-08-29,ATFC,EQ,554.25,555.0,563.9,535.55,536.1,539.3,547.59,3929113,215153038915000.0,,727534,0.1852
2006-08-30,ATFC,EQ,539.3,537.0,542.9,521.5,529.0,528.1,529.55,2034983,107762957620000.0,,345064,0.1696
2006-08-31,ATFC,EQ,528.1,525.0,544.0,515.0,539.35,538.45,532.89,1670990,89044643830000.0,,286440,0.1714
2006-09-01,ATFC,EQ,538.45,539.0,549.0,535.1,541.35,541.85,542.46,1176195,63803856150000.0,,213842,0.1818

Trenton McKinney · Accepted Answer · 2020-09-15 15:45:37Z

1

As per pandas.read_csv, you can also specify the parse_dates = True and the infer_datetime_format = True arguments to have pandas attempt to parse dates from the index, which you have set to date. As in:

df = pd.read_csv(filePath,index_col="Date",parse_dates=True,infer_datetime_format=True)

edited Sep 15, 2020 at 15:45

Trenton McKinney

63.2k41 gold badges169 silver badges212 bronze badges

answered Sep 15, 2020 at 15:37

brobertsp

716 bronze badges

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

1 Comment

Dr.PB Over a year ago

df.index[0] appeared as Timestamp('2006-08-27 00:00:00')

Abdelrhman Hosny · Accepted Answer · 2020-09-15 18:01:37Z

1

Instead of using a lambda function there is already a function to change the data to datetime pd.to_datetime

so you could do something like this :

df = pd.read_csv(filePath,index_col="Date")

df['Date'] = pd.to_datetime(df['Date'] ,format = '%Y-%m-%d')

df['Date'] = df['Date'].apply(lambda x : x.date())

print(type(df['Date'][0]))

Output

<class 'datetime.date'>

There's also a format argument in the function in order to match your data Format

I think your format is format = '%Y-%m-%d'

edited Sep 15, 2020 at 18:01

answered Sep 15, 2020 at 15:31

Abdelrhman Hosny

1207 bronze badges

3 Comments

Dr.PB Over a year ago

df.index[0] appeared as 0 of class int as row index is not changed to "Date".

Abdelrhman Hosny Over a year ago

I forgot to add the argument index_col="Date" which makes the date the index column which causes the integer output. , Also I tried a mapping which is basically what you did with the parser but in a different way and it worked on my local machine

Abdelrhman Hosny Over a year ago

I edited the original answer with the latest thing I tried along with the output

Dr.PB · Accepted Answer · 2020-09-15 17:36:44Z

0

Not able to get any oneliner.

df = pd.read_csv(filePath)   # load dataframe
df["Date"]=df["Date"].apply(lambda s: datetime.datetime.strptime(s,"%Y-%m-%d").date()) # convert Date column items to datetime.date
df.set_index('Date', inplace=True) # set Date as row index

answered Sep 15, 2020 at 17:36

Dr.PB

1,0771 gold badge19 silver badges45 bronze badges

Collectives™ on Stack Overflow

How to change dataframe row index to datetime.date while reading from csv?

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related