Converting multiple date formats into one format python

Question

The date variable in my data is in multiple formats like DD/MM/YYYY D/MM/YY DD/M/YYYY 12/8/2017 27/08/17 8/9/2017 10/9/2017 15/09/17..

I need to change these multiple formats into one single format like DD/MM/YYYY

Tried to create a parsing function

def parse_date(date):
if date == '':
    return None
else:
    return dt.strptime(date, '%d/%m/%y').date()

and when I apply this function to my dataset, it throws me the following error..

"ValueError Traceback (most recent call last) in () ----> 1 data.Date = data.Date.apply(parse_date)

Unconverted Data Remains Error ValueError: unconverted data remains: 17"

How can I solve the unconverted data remains error?

What is the date that causes that error? It could be the %y doesn't like the 2 digit year. — Bailey Parker
– Bailey Parker, Commented Jan 22, 2018 at 14:58
I would split the string on the slashes, and construct another string with the correct number of digits. — Andy G
– Andy G, Commented Jan 22, 2018 at 14:59
When I changed %y to %Y, I get the following error : ValueError: time data '13/08/17' does not match format '%d/%m/%Y' — vishnu prashanth
– vishnu prashanth, Commented Jan 22, 2018 at 15:00
@AndyG there are around 10000 records with dates ranging from 01/01/2000 - 12/01/2018 in multiple date formats.. — vishnu prashanth
– vishnu prashanth, Commented Jan 22, 2018 at 15:03

Rakesh · Accepted Answer · 2018-01-22 15:03:38Z

5

You can use the dateutil module to do this

import dateutil.parser as dparser
a = ["12/8/2017", "27/08/17", "8/9/2017", "10/9/2017", "15/09/17"]

for i in a:
    print dparser.parse(i,fuzzy=True).date()

Result:

answered Jan 22, 2018 at 15:03

Rakesh

82.9k17 gold badges85 silver badges122 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ali Rasim Kocal · Accepted Answer · 2018-01-22 15:22:10Z

1

This is because %y expects a 4 digit number.

In order to cover multiple date formats, you can have a look at the dateparser library. (Docs)

Otherwise you will have to manually go through possible types or extend the dates yourself. If you are sure you only need to extend the year part you can do something like this before feeding the string to the parser:

date_parts = date.split('/')
if len(date_parts[2]) == 2:
    date_parts[2] = "20" + date_parts[2]
date = '/'.join(date_parts)

I think using the dateparser library is the way to go, as it is more extendible.

edited Jan 22, 2018 at 15:22

answered Jan 22, 2018 at 15:05

Ali Rasim Kocal

5403 silver badges14 bronze badges

3 Comments

vishnu prashanth Over a year ago

when I tried for the entire date values from the dataset, it shows an error. 'Series' object has no attribute 'split'

vishnu prashanth Over a year ago

I tried as date= Data.Date.Split('/') where Data is my actual dataset..

Ali Rasim Kocal Over a year ago

You are getting this error because your Date is not a string as implied in the question but a series object. You will need to iterate over the Series and apply this element-wise.

Andy G · Accepted Answer · 2018-01-22 15:19:02Z

0

A basic approach is to split the strings on the slashes, and then re-join them with the correct number of digits. A simple approach:

date = "12/8/2017"

parts = date.split("/")

print(parts) # ['12', '8', '2017']

if len(parts[0]) == 1:
    parts[0] = "0" + parts[0]
if len(parts[1]) == 1:
    parts[1] = "0" + parts[1]
if len(parts[2]) == 2:
    parts[2] = "20" + parts[2]
newDate = "/".join(parts)
# or 
newDate = parts[0] + "/" + parts[1] + "/" + parts[2]

print(newDate) # 12/08/2017

Then you have a consistent date format throughout. (An additional check is required if your dates extend into the last century.)

I would test this first, and consider the other answers' approaches if this is not performant.

edited Jan 22, 2018 at 15:19

answered Jan 22, 2018 at 15:12

Andy G

19.4k5 gold badges49 silver badges70 bronze badges

1 Comment

vishnu prashanth Over a year ago

This works perfectly for a single value. But when I tried for a series of date values from the dataset, it shows an error when I add entire dates records.. will work on it and will update! Thanks

Collectives™ on Stack Overflow

Converting multiple date formats into one format python

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related