read excel and convert index to datatimeindex pandas

Question

I read an excel in pandas like this

df = pd.read_excel("Test.xlsx", index_col=[0])

The dataframe look like this with the index containing a date and time and one column:

01.01.2015 00:15:00     47.2
01.01.2015 00:30:00     46.6
01.01.2015 00:45:00     19.4
01.01.2015 01:00:00     14.8
01.01.2015 01:15:00     14.8
01.01.2015 01:30:00     16.4
01.01.2015 01:45:00     16.2
...

I want to convert the index to a datatimeindex, I tried

df.index = pd.to_datetime(df.index)

and got: "ValueError: Unknown string format"

What is here the best way to convert the index to a datatime format containing date and tiem to use datetime based functions

jezrael · Accepted Answer · 2016-10-21 10:52:20Z

1

I think you need add parameter format - see http://strftime.org/:

df.index = pd.to_datetime(df.index, format='%d.%m.%Y %H:%M:%S')

print (df)
                        a
2015-01-01 00:15:00  47.2
2015-01-01 00:30:00  46.6
2015-01-01 00:45:00  19.4
2015-01-01 01:00:00  14.8
2015-01-01 01:15:00  14.8
2015-01-01 01:30:00  16.4
2015-01-01 01:45:00  16.2

answered Oct 21, 2016 at 10:52

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

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

5 Comments

Jollle Over a year ago

I tried that also, but got: "ValueError: unconverted data remains: b" ???

jezrael Over a year ago

It seems you have bad data - you can check all rows which cannot be converted to datetimeindex by print (df[pd.to_datetime(df.index, format='%d.%m.%Y %H:%M:%S', errors='coerce').to_series().isnull().values]). What return it?

jezrael Over a year ago

Thgen you can replace all bad values by NaT by df.index = pd.to_datetime(df.index, format='%d.%m.%Y %H:%M:%S', errors='coerce')

Jollle Over a year ago

ok, you´re right, bad data, didnt recognize that. I deleted that data, now it works.

Jollle Over a year ago

Thanks a lot for help!

Collectives™ on Stack Overflow

read excel and convert index to datatimeindex pandas

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