Is there a more elegant way to catch multiple date parsing formats in python

Question

I'm working on learning to clean datasets and am encountering multiple date formats. There appears to be only two formats datetimes were stored as. The below code works fine, but feels clunky. Is there a better (or faster) way to handle multiple formats in a DataFrame? If it were only one format, it would be doable in just one line and I like avoiding try/except statements if I can.

For reference, the data goes from 1963 to 2010, so declaring the date_parsed column as 2023 seemed safe to me. Thanks!

data['date_parsed'] = pd.to_datetime('01/01/2023', format='%m/%d/%Y')

for i in range(len(data.Date)):

try:
    data['date_parsed'][i] = pd.to_datetime(data.Date[i], format='%m/%d/%Y')

except:
    data['date_parsed'][i] = pd.to_datetime(data.Date[i], format='%Y-%m-%dT%H:%M:%S.%fZ')

what do you mean by "*way to handle multiple formats*"? do you want to exclude certain formats, or only parse specific ones? — FObersteiner, Jan 17 '23 at 13:31
see also [Handling multiple datetime formats with pd.to_datetime](https://stackoverflow.com/q/47256212/10197418) or [parse multiple date format pandas](https://stackoverflow.com/q/62211539/10197418) — FObersteiner, Jan 17 '23 at 13:38

jezrael · Accepted Answer · 2023-01-17T12:55:37.013

2

Use Series.fillna for replace missing values from non matched values in to_datetime by parameter errors='coerce':

d1 = pd.to_datetime(data.Date, format='%m/%d/%Y', errors='coerce')
d2 = pd.to_datetime(data.Date, format='%Y-%m-%dT%H:%M:%S.%fZ', errors='coerce')

data['date_parsed'] = d1.fillna(d2)

edited Jan 17 '23 at 12:55

answered Jan 17 '23 at 12:49

jezrael

822,522
95
1,334
1,252

mozway · Answer 2 · 2023-01-17T12:56:32.010

You can use functools.reduce with a list comprehension of pandas.to_datetime and your various formats, to handle any number of formats:

from functools import reduce

formats = ['%m/%d/%Y', '%Y-%m-%dT%H:%M:%S.%fZ']

data['date_parsed'] = reduce(lambda a,b: a.fillna(b),
                             [pd.to_datetime(data['Date'],
                                             format=fmt, errors='coerce',
                                             utc=fmt.endswith('Z'))
                              for fmt in formats])

Note that if you have a Z in the format, you need utc=True.

Example:

                          Date                       date_parsed
0                   01/01/2023               2023-01-01 00:00:00
1  2023-01-01T12:00:00.123456Z  2023-01-01 12:00:00.123456+00:00

score 0 · Answer 3 · answered Jan 17 '23 at 13:06

Basically, you can rely on infer_datetime_format parameter:

infer_datetime_format: bool, default False

If True and no format is given, attempt to infer the format of the datetime strings based on the first non-NaN element, and if it can be inferred, switch to a faster method of parsing them. In some cases this can increase the parsing speed by ~5-10x.

df = pd.DataFrame({'date_parsed': ['01/01/2023', '2023-01-01T14:53:30.200Z']})
pd.to_datetime(df['date_parsed'], infer_datetime_format=True)

0                 2023-01-01 00:00:00
1    2023-01-01 14:53:30.200000+00:00

the point of `infer_datetime_format` is to infer the datetime format for a whole column (series), assuming it is the same for all elements. if you want the parser to infer the format for each element in the series, simply do not provide a `format`. — FObersteiner, Jan 17 '23 at 13:29

Is there a more elegant way to catch multiple date parsing formats in python

3 Answers3