ParserError: hour must be in 0..23: 09/05/2019 24:00

user_17209170 0
Viewed 408

I have a dataset from a external source and I'm trying to get an index of a Pandas dataframe to datetime using the to_datetime() method but I keep getting the error message.

ValueError: hour must be in 0..23

and

ParserError: hour must be in 0..23: 09/05/2019 24:00

I have read in my csv as follows:

dataframe = pandas.read_csv("file_name.csv", 
                              parse_dates = ['date'], 
                              dayfirst = True, 
                              index_col = 'date', 
                              header = 2, 
                              na_values = ['-99.0'])

My output looks like this:

                    co   no   no2   nox    o3  PM10  PM2.5  Unnamed: 8
datetime                                                              
09/05/2019 01:00  0.23  0.5   2.8   3.2  16.0   9.9    6.2         NaN
09/05/2019 02:00  0.23  0.2   1.9   2.1  18.1  10.3    5.7         NaN
09/05/2019 03:00  0.21  0.5   0.7   1.2  19.7  13.3    7.5         NaN
09/05/2019 04:00  0.21  0.5   1.5   1.9  18.2  13.0    5.5         NaN
09/05/2019 05:00  0.23  0.5   3.0   3.5  16.0  13.9    6.7         NaN
...                ...  ...   ...   ...   ...   ...    ...         ...
16/05/2019 20:00  0.70  8.2  20.3  28.1   1.4  30.3   25.4         NaN
16/05/2019 21:00  0.61  7.1  19.1  26.2   1.7  20.7   19.5         NaN
16/05/2019 22:00  0.44  1.8  11.7  13.5   8.1   7.7   11.1         NaN
16/05/2019 23:00  0.41  0.8   8.4   9.2  10.5   8.8    9.5         NaN
16/05/2019 24:00  0.46  0.8   8.5   9.4   9.3  12.4   10.2         NaN

For some reason when I try and convert to datetime with the following code it comes up with the above stated error messages

df.index = pd.to_datetime(df.index) 
print(df)
pandas dataframe datetime python
1 Answers

do not set parse_dates upon import - instead, parse to datetime afterwards, by splitting the string into date and time, parse the date part to datetime and add the time part as a timedelta.

Ex:

df = pd.DataFrame({'date': ["16/05/2019 23:00", "16/05/2019 24:00"]})

df['datetime'] = (pd.to_datetime(df['date'].str.split(' ').str[0], dayfirst=True) 
                  + pd.to_timedelta(df['date'].str.split(' ').str[1] + ':00'))

Note: to_timedelta expects HH:MM:SS format, so I added ':00' to the time string.

print(df['datetime'])
0   2019-05-16 23:00:00
1   2019-05-17 00:00:00
Name: datetime, dtype: datetime64[ns]
user_101974180
Related