I have a csv file with 24:00 hour instead of 00:00 and try to read it with pandas. I found solution and try to adopt it. The problem is, I get an error and don’t know how to fix it. Can someone help me?
My csv:
Datetime Value 45 01.01.2021 23:00 2.7 46 01.01.2021 23:30 2.9 47 01.01.2021 24:00 1.5 48 02.01.2021 00:30 1.2 49 02.01.2021 01:00 1.9 50 02.01.2021 01:30 1.9
The code I got from the link above adopted to my case:
df['Datetime'] = (pd.to_datetime(df['Datetime'].str[:10], format='%d.%m.%Y') + pd.to_timedelta(df['Datetime'].str[10:12]+':'+df['Datetime'].str[12:14]+':00'))
The error I get:
expecting hh:mm:ss format, received: 0:0::00
The link where I got the function to transform it:
Pandas: parsing 24:00 instead of 00:00
Advertisement
Answer
You can use str.split()
+pd.to_datetime()
+pd.to_timedelta()
:
s=df['Datetime'].str.replace('.','-').str.split(expand=True) df['Datetime']=pd.to_datetime(s[0])+pd.to_timedelta(s[1]+':00')
OR
df['Datetime']=pd.to_datetime(df['Datetime'].str[:10], format='%d.%m.%Y')+pd.to_timedelta(df['Datetime'].str[10:]+':00')
output of df
:
Datetime Value 45 2021-01-01 23:00:00 2.7 46 2021-01-01 23:30:00 2.9 47 2021-01-02 00:00:00 1.5 48 2021-02-01 00:30:00 1.2 49 2021-02-01 01:00:00 1.9 50 2021-02-01 01:30:00 1.9