How to convert Pandas Series of strings to Pandas datetime with non-standard formats that contain dates before 1970

user_6824915 0
Viewed 306

I have a column of dates in the following format:

Jan-85
Apr-99
Nov-01
Feb-65
Apr-57
Dec-19

I want to convert this to a pandas datetime object.

The following syntax works to convert them:

pd.to_datetime(temp, format='%b-%y')

where temp is the pd.Series object of dates. The glaring issue here of course is that dates that are prior to 1970 are being wrongly converted to 20xx.

I tried updating the function call with the following parameter:

pd.to_datetime(temp, format='%b-%y', origin='1950-01-01')

However, I am getting the error:

Name: temp, Length: 42537, dtype: object' is not compatible with origin='1950-01-01'; it must be numeric with a unit specified

I tried specifying a unit as it said, but I got a different error citing that the unit cannot be specified alongside a format.

Any ideas how to fix this?

pandas datetime python
2 Answers

Just @DudeWah's logic, but improving upon the code:

def days_of_future_past(date,chk_y=pd.Timestamp.today().year):
    return date.replace(year=date.year-100) if date.year > chk_y else date
 

temp = pd.to_datetime(temp,format='%b-%y').map(days_of_future_past)

Output:

>>> temp

0   1985-01-01
1   1999-04-01
2   2001-11-01
3   1965-02-01
4   1957-04-01
5   2019-12-01
6   1965-05-01
Name: date, dtype: datetime64[ns]
user_147849120

Gonna go ahead and answer my own question so others can use this solution if they come across this same issue. Not the greatest, but it gets the job done. It should work until 2069, so hopefully pandas will have a better solution to this by then lol

Perhaps someone else will post a better solution.

def wrong_date_preprocess(data):
    """Correct date issues with pre-1970 dates with whacky mon-yy format."""
    df1 = data.copy()
    dates = df1['date_column_of_interest']
    # use particular datetime format with data; ex: jan-91
    dates = pd.to_datetime(dates, format='%b-%y')

    # look at wrongly defined python dates (pre 1970) and get indices
    date_dummy = dates[dates > pd.Timestamp.today().floor('D')]
    idx = list(date_dummy.index)
    # fix wrong dates by offsetting 100 years back dates that defaulted to > 2069
    dummy2 = date_dummy.apply(lambda x: x.replace(year=x.year - 100)).to_list()
    dates.loc[idx] = dummy2

    df1['date_column_of_interest'] = dates
    return(df1)
user_68249150
Related