ParserError: Unknown string format: EM:AIL-CodePudding

I have a df with a column - queue time. It has time values. But the type of these values are str. It might also contain incorrect values like 'abc' or 'email' in it as well. The time values are of the form '10:23', '22:22', '1:1', etc.

I want to convert these values to '10:23:00', '22:22:00', '01:01:00' and so on. If it's not a time value and is a string, I want to ignore them.

I tried to apply this :

df['queue time'] = pd.to_datetime(df['queue time'].str.split(':', expand=True)
                                              .apply(lambda col: col.str.zfill(2))
                                              .fillna('00')
                                              .agg(':'.join, axis=1)).dt.time

But it gives an error when it encounters values like 'abc' or 'email' and gives the following error : ParserError: Unknown string format: EM:AIL

How do I tweak my code. Need some expert help. thanks!

CodePudding user response：

Just addition @Mozway solution just pass parameter errors='coerce'

df['queue time'] = pd.to_datetime(df['queue time'], format='%H:%M',errors='coerce').dt.time

input

 queue time
0      10:23
1      22:22
2     EM:AIL
3    abv:ess

output

  queue time
0   10:23:00
1   22:22:00
2        NaT
3        NaT

CodePudding user response：

IIUC, just use:

df['queue time'] = pd.to_datetime(df['queue time'], format='%H:%M', errors='coerce').dt.time

output:

  queue time
0   10:23:00
1   22:22:00
2   01:01:00
3        NaT

Used input:

df = pd.DataFrame({'queue time': ['10:23', '22:22', '1:1', 'EM:AIL']})