Converting pandas strings column to datetime with ocassionally missing hour part

Question

I have duration string noted as '48m 37s' and sometimes as '1h 38m 29s' and a collection of string that holds this data in pandas dataframe's column

I'm trying to convert datatype of duration column to datetime as follows

pd.to_datetime(usg['duration'], format='%Hh %Mm %Ss')

but failing with the following error

ValueError: time data '1h 38m 29s' does not match format '%Mm %Ss' (match)

I understand that sometimes the hour part is missing in the list entries of duration column and wondering if there is a way to specify multiple formats if in case one fails to match other succeeds.

Doing so shall yield the output as below preserving the order of entries in the column

     00:39:40
     01:38:29
     07:39:40

Albert Villanova del Moral · Accepted Answer · 2017-03-31 23:15:54Z

1

Better use: pd.to_timedelta(usg['duration'])

usg = pd.DataFrame({'duration': ['48m 37s', '1h 38m 29s']})

pd.to_timedelta(usg['duration'])

gives the output:

0   00:48:37
1   01:38:29
Name: duration, dtype: timedelta64[ns]

edited Mar 31, 2017 at 23:15

answered Mar 31, 2017 at 13:20

Albert Villanova del Moral

861 gold badge1 silver badge7 bronze badges

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

3 Comments

EdChum Over a year ago

This will fail on the OP's sample data, also don't post a code snippet as this isn't useful for others. You should post a complete code example that demonstrates that this works for the OP

Albert Villanova del Moral Over a year ago

This does not fail.

EdChum Over a year ago

Sorry I had missing s in my data, the point remains, please don't post short code snippets, answers should be complete with sample data, code and produced output as currently it looks like a comment

jezrael · Accepted Answer · 2017-03-31 13:25:42Z

You need:

usg = pd.DataFrame({'duration':['7h 39m 40s','15h 39m 40s','39m 40s']})
print (usg)


usg['duration'] = np.where(usg.duration.str.contains('h'), 
                pd.to_datetime(usg['duration'], format='%Hh %Mm %Ss', errors='coerce'),
                pd.to_datetime(usg['duration'], format='%Mm %Ss',errors='coerce'))
print (usg)
             duration
0 1900-01-01 07:39:40
1 1900-01-01 15:39:40
2 1900-01-01 00:39:40

Another solution:

usg['duration'] = pd.to_datetime(usg['duration'].where(usg.duration.str.contains('h'), 
                                 '0h '+ usg['duration']),format='%Hh %Mm %Ss')
print (usg)
             duration
0 1900-01-01 07:39:40
1 1900-01-01 15:39:40
2 1900-01-01 00:39:40

usg.loc[~usg.duration.str.contains('h'), 'duration'] = '0h '+ usg['duration']
usg['duration'] = pd.to_datetime(usg['duration'], format='%Hh %Mm %Ss')
print (usg)
             duration
0 1900-01-01 07:39:40
1 1900-01-01 15:39:40
2 1900-01-01 00:39:40

Collectives™ on Stack Overflow

Converting pandas strings column to datetime with ocassionally missing hour part

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related