1

I have a dataframe with a column of dates, unfortunately my import (using read_excel) brought in format of dates as datetime and also excel dates as integers.

What I am seeking is a column with dates only in format %Y-%m-%d

From research, excel starts at 1900-01-00, so I could add these integers. I have tried to use str.extract and a regex in order to separate the columns into two, one of datetimes, the other as integers. However the result is NaN.

Here is an input code example


df = pd.DataFrame({'date_from': [pd.Timestamp('2022-09-10 00:00:00'),44476, pd.Timestamp('2021-02-16 00:00:00')], 'date_to': [pd.Timestamp('2022-12-11 00:00:00'),44455, pd.Timestamp('2021-12-16 00:00:00')]})

Output looks like below

Attempt to first separate the columns by extracting the integers( dates imported from MS excel)

df.date_from.str.extract(r'(\d\d\d\d\d)')

however this gives NaN.

The reason I have tried to separate integers out of the column, is that I get an error when trying to act on the excel dates within the mixed column (in other words and error using the following code:)

def convert_excel_time(excel_time):

    return pd.to_datetime('1900-01-01') + pd.to_timedelta(excel_time,'D')

Any guidance on how I might get a column of dates only? I find the datetime modules and aspects of pandas and python the most frustrating of all to get to grips with!

thanks

Prolle
  • 358
  • 1
  • 10

1 Answers1

1

You can convert values to timedeltas by to_timedelta with errors='coerce' for NaT if not integers add Timestamp called d, then convert datetimes with errors='coerce' and last pass to Series.fillna in custom function:

def f(x):
    #https://stackoverflow.com/a/9574948/2901002
    d = pd.Timestamp(1899, 12, 30)
    timedeltas = pd.to_timedelta(x, unit='d', errors='coerce')
    dates = pd.to_datetime(x, errors='coerce')
    return (timedeltas + d).fillna(dates)

cols = ['date_from','date_to']
df[cols] = df[cols].apply(f)
print (df)
   date_from    date_to
0 2022-09-10 2022-12-11
1 2021-10-07 2021-09-16
2 2021-02-16 2021-12-16
jezrael
  • 822,522
  • 95
  • 1,334
  • 1,252