Python how to clean dirty date time strings

user_7687256 0
Viewed 1736

I have a dataframe data = pd.DataFrame({'date':['25 ugust 2014','14 Auust 2014','27 ugust 2014','18 Marc 2015','03 Jue 2014']})

It is currently an object dtype. I'd like to convert it to datetime format but I'm not able to do so as the strings in the months are dirty.

data['date'] = pd.to_datetime(data['date'], format = '%d %B %Y')

What will be the best and fastest way to clean up this data so that I can change it to date format? Will re.sub be appropriate?

python-3.x date python
2 Answers

Here Is an approach using SequenceMatcher from difflib:

import pandas as pd
from difflib import SequenceMatcher

MONTHS = ["January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"]

def clean_date_time(date_time: str) -> str:
    day, month, year = date_time.split(" ")

    def get_similarity(other_month):
        return SequenceMatcher(None, month, other_month).ratio()

    return " ".join((day, max(MONTHS, key=get_similarity), year))


data["date"] = data["date"].apply(clean_date_time)

Resulting data:

             date
0  25 August 2014
1  14 August 2014
2  27 August 2014
3   18 March 2015
4    03 June 2014
user_53192670

Here is a rough sketch of an idea using difflib:

import difflib

months = {'jan', 'january', 'feb', 'february', 'mar', 'march', 'august'}   #TODO: add the rest of the months

def cleanup_dmy_date(d):
  d, m, y = d.split()
  m = difflib.get_close_matches(m.lower(), months, 1) or [m]
  return f'{d} {m[0]} {y}'

print(cleanup_dmy_date('25 ugust 2014'))
print(cleanup_dmy_date('14 Auust 2014'))
print(cleanup_dmy_date('18 Marc 2015'))

It cleans up the date as a string, but it might as well construct a datetime directly from d, m and y. Applying this is a column of a dataframe is straightforward and is left as an exercise for the reader.

user_3672730
Related