0

I have a dataframe df, where I want to set the column 'Time' to a datetimeindex. The column before converting looks like this:

01-10-19    09:05
01-10-19    10:04
01-10-19    11:05
01-10-19    12:04
01-10-19    13:04
            ...  
31-05-20    22:05
31-05-20    23:05
01-06-20    00:05
01-06-20    01:05
01-06-20    02:05

So I tried the following line of code:

df['Time'] = pd.to_datetime(df['Time'], format='%d-%m-%Y    %H:%M', errors='coerce')

Which lead to only NaT 'values' in the column, without datetimeindex being installed. I've also tried to change the format in multiple ways such as: '%%dd-%%mm-%%YY %%HH:%%MM' or '%d%d-%m%m-%Y%Y %H%H:%M%M', but it resulted in the same error. When I remove the errors='coerce', I got the message: ValueError: time data '09:05' does not match format '%d-%m-%Y %H:%M' (match). What am I missing? Why is it the wrong format and how do I fix it? Thanks very much in advance!

Tijmen Stronks
  • 49
  • 5
  • 1
    Are you sure that is all one column? The error you are getting suggests that the date and time are in separate columns. – user1558604 Mar 17 '21 at 23:24
  • You're right, apparently the dataframe already has an index which is indeed the date column. Rookie mistake... But fixed it, thanks! – Tijmen Stronks Mar 18 '21 at 14:55

1 Answers1

1

Try this:

df['Time'] = pd.to_datetime(df['Time'], infer_datetime_format= True)
print(df)

#output:
                 Time
0 2019-01-10 09:05:00
1 2019-01-10 10:04:00
2 2019-01-10 11:05:00
3 2019-01-10 12:04:00
4 2019-01-10 13:04:00
5 2020-05-31 22:05:00
6 2020-05-31 23:05:00
7 2020-01-06 00:05:00
8 2020-01-06 01:05:00
9 2020-01-06 02:05:00
pakpe
  • 5,391
  • 2
  • 8
  • 23