CODEWITHSUNDEEP
pythonpandasdate
sagarpavan
sagarpavan

Reputation: 95

How to convert string of different format(YYYY-MM-DD, DD-MM-YYYY) to date object of one format in pandas?

I have a column of string object where it contains different format(YYYY-MM-DD, DD-MM-YYYY). How to convert to DD-MM-YYYY of date object.

I tried with, df['accepted_date'] = pd.to_datetime(df['accepted_date'], format='%d-%m-%Y')

I got error as time data '1899-12-31' does not match format '%d-%m-%Y' (match)

Thanks,

Upvotes: 1

Views: 11693

Answers (1)

jezrael
jezrael

Reputation: 862591

Let pandas to parse dates, but then some days with months should be swapped:

df['accepted_date'] = pd.to_datetime(df['accepted_date'])

So better is use to_datetime with format and parameter errors='coerce', what return only matched datetimes with NaT for non matched. Last use combine_first for join all Series - NaT are replaced by values from another Series:

df = pd.DataFrame({'accepted_date':['2017-01-02','07-08-2017','20-03-2017','2017-01-04']})

d1 = pd.to_datetime(df['accepted_date'], format='%d-%m-%Y', errors='coerce')
d2 = pd.to_datetime(df['accepted_date'], format='%Y-%m-%d', errors='coerce')

df['accepted_date1'] = d1.combine_first(d2)
df['accepted_date2'] = pd.to_datetime(df['accepted_date'])
print (df)
  accepted_date accepted_date1 accepted_date2
0    2017-01-02     2017-01-02     2017-01-02
1    07-08-2017     2017-08-07     2017-07-08 <-swapped dd-mm
2    20-03-2017     2017-03-20     2017-03-20
3    2017-01-04     2017-01-04     2017-01-04

Detail:

print (d1)
0          NaT
1   2017-08-07
2   2017-03-20
3          NaT
Name: accepted_date, dtype: datetime64[ns]

print (d2)
0   2017-01-02
1          NaT
2          NaT
3   2017-01-04
Name: accepted_date, dtype: datetime64[ns]

EDIT:

Another solution is use parameter dayfirst=True:

df['accepted_date3'] = pd.to_datetime(df['accepted_date'], dayfirst=True)
print (df)
  accepted_date accepted_date3
0    2017-01-02     2017-01-02
1    07-08-2017     2017-08-07
2    20-03-2017     2017-03-20
3    2017-01-04     2017-01-04

Upvotes: 6

Related Questions