Parse multiple date formats into a single format-further question

Question

Update: The question is now solved :)

I have asked one similar question before. That answer was great. But now, the dataset gets more complicated.

Based on the original answer, I tried like this:

 fmt=['%Y-%m-%d', '%d-%m-%Y', '%d/%m/%Y','%Y-%d-%m', '%Y-%d-%b', '%d-%b-%Y', 
  '%d/%b/%Y','Year: %d; month','month: %d;Year','%Y','%b %d %Y','%b %Y %d',
     '%Y/%m/%d','%Y/%d/%m','%Y%m']

 dfs = [pd.to_datetime(df['Date'], format=f, errors='coerce') for f in fmt]

 df['Date']= pd.concat(dfs, axis=1).bfill(axis=1).iloc[:, 0]

And it gave me more than 4000 NaN values in df.date column. And if I delete all those NaN values, that's too much. When I tracked down the date column, those NaN values were caused by these rows:

Any ideas about how should I deal with them? I 'm thinking to replace those month/date with zero to one like this:

 20500           =====> 2005-01-01
 2014/17/0       =====> 2004-01-17
 2006/0/5        =====> 2006-01-05
 2005-3-0        =====> 2005-03-01
 2006-18-0       =====> 2006-01-18

Note: Some rows the date was in Year/month/date format whereas the other rows could be Year/date/month or simply YearDate

Thank you for your help!

[![enter image description here][2]][2]

Here are some printed out dateformat:

With jezrael's solution, I have successfully reduced NaT values from more than 4000 rows to only around 300 rows. The data format in those 300 rows were like the follows:

        Date        Date1
238     2001-01-0     NaT
249     2001-1-0      NaT
277     2001-2-0      NaT
506     2002-1-0      NaT
535     2002-4-0      NaT    
58510   2001-1-0      NaT
58516   2001-1-0      NaT
58779   2002-3-0      NaT
58888   2001-3-0      NaT
58889   2002-8-0      NaT

user_4238408 · Answer

You are better off using fillna:

# initialize with all `nan`
out = pd.Series(pd.NaT, df.index)

# list of formats
fmt=['%Y-%m-%d', '%d-%m-%Y', '%d/%m/%Y','%Y-%d-%m', '%Y-%d-%b', '%d-%b-%Y', 
'%d/%b/%Y','Year: %d; month','month: %d;Year','%Y','%b %d %Y','%b %Y %d',
 '%Y/%m/%d','%Y/%d/%m','%Y%m']

# loop through format,
# update the series with valid dates for that format
for f in fmt:
    out = out.fillna(pd.to_datetime(df['Date'], format=f, errors='coerce'))

df['Date'] = out

user_2901002 · Answer

There is problem not valid dates, mainly because 0 are processing like 1. So solution is repalce this values, $ is for match end of string:

d = {'/0$':'/01','/0/':'/01/','-0-':'-01-'}
df['Date'] = df['Date'].replace(d, regex=True)

#for replace formats like 20050 to 200501
m = df['Date'].str.len().eq(5) & df['Date'].str.endswith('0')

df['Date'] = df['Date'].mask(m, df['Date'] + '1')

You can still looking for no matched values like used for check data in original solution:

fmt=['%Y-%m-%d', '%d-%m-%Y', '%d/%m/%Y','%Y-%d-%m', '%Y-%d-%b', '%d-%b-%Y', 
  '%d/%b/%Y','Year: %d; month','month: %d;Year','%Y','%b %d %Y','%b %Y %d',
     '%Y/%m/%d','%Y/%d/%m','%Y%m']

dfs = [pd.to_datetime(df['Date'], format=f, errors='coerce') for f in fmt]

df['Date1']= pd.concat(dfs, axis=1).bfill(axis=1).iloc[:, 0]
 
df = df[df['Date1'].isna()]
print (df)
         Date Date1
15      20050   NaT
17  2004/17/0   NaT
27   2003/0/1   NaT
32  2006-0-01   NaT
37  2006-0-01   NaT

Parse multiple date formats into a single format-further question

2 Answers