facing error while parsing date column with different formats

Question

my dataframe includes date in dd/mm/yy & dd/mm/yyyy format but while converting them into pandas datetime it gives below error :

ValueError: time data "02/11/21" doesn't match format "%m/%d/%Y", at position 129. You might want to try: - passing format if your strings have a consistent format; - passing format='ISO8601' if your strings are all ISO8601 but not necessarily in exactly the same format; - passing format='mixed', and the format will be inferred for each element individually. You might want to use dayfirst alongside this.

with this code df['date_column'] = pd.to_datetime(df['date_column'], format='%m/%d/%Y', errors='coerce') it converts most of the values into NaT

Answer 1

Use %y for YY format of year:

df['date_column'] = pd.to_datetime(df['date_column'], format='%m/%d/%y', errors='coerce')

If there are both format of years YY and YYYY use:

df['date_column'] = (pd.to_datetime(df['date_column'], format='%m/%d/%y', errors='coerce') 
                       .fillna(pd.to_datetime(df['date_column'], 
                                              format='%m/%d/%Y', errors='coerce'))