Cleaning up pandas column of datetime strings

Question

I currently have some data in the form of datestrings that I would like to standardize into a zero-padded %H:%M:%S string. In its original form, the data deviates from the standard format in the following ways:

The time is not zero padded (e.g. '2:05:00')
There can be trailing whitespaces (e.g., ' 2:05:00')
There can be times over 24H displayed (e.g., '25:00:00')

Currently, this is what I have:

df['arrival_time'] = pd.to_datetime(df['arrival_time'].map(lambda x: x.strip()), format='%H:%M:%S').dt.strftime('%H:%M:%S')

But I get an error on the times that are over 24H. Is there a good way to transform this dataframe column into the proper format?

To clarify, I'm doing this with GTFS data, which specifies times over 24H if a trip runs overnight. — user1834200
– user1834200, Commented Mar 12, 2018 at 8:16

jezrael · Accepted Answer · 2018-03-12 06:37:35Z

2

I believe you need:

df = pd.DataFrame({'arrival_time':['2:05:00','2:05:00','25:00:00'],})

df['arrival_time'] = df['arrival_time'].str.strip().str.zfill(8)
print (df)
  arrival_time
0     02:05:00
1     02:05:00
2     25:00:00

Or:

df['arrival_time'] = pd.to_datetime(df['arrival_time'].str.strip(), errors='coerce')
                       .dt.strftime('%H:%M:%S')
print (df)
  arrival_time
0     02:05:00
1     02:05:00
2          NaT

Or:

df['arrival_time'] = (pd.to_timedelta(df['arrival_time'].str.strip())
                        .astype(str)
                        .str.extract('\s.*\s(.*)\.', expand=False))
print (df)
  arrival_time
0     02:05:00
1     02:05:00
2     01:00:00

edited Mar 12, 2018 at 6:37

answered Mar 12, 2018 at 6:24

jezrael

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

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

1 Comment

user1834200 Over a year ago

Thank you! The top answer is what ended up working for me.

Collectives™ on Stack Overflow

Cleaning up pandas column of datetime strings

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related