CODEWITHSUNDEEP
pythonpandasdataframedatetime
Perdue
Perdue

Reputation: 479

How to convert a column with Excel Serial Dates and regular dates to a pandas datetime?

I have a dataframe where there are birthdays that have regular dates mixed with Excel serial dates like this:

09/01/2020 12:00:00 AM
05/15/1985 12:00:00 AM
06/07/2013 12:00:00 AM
33233
26299
29428

I tried a solution from this answer, and all of the dates that are in the Excel serial format are blanked out, while preserving those that were in a normal date format.

This is my code:

import pandas as pd
import xlrd
import numpy as np
from numpy import *
from numpy.core import *
import os
import datetime
from datetime import datetime, timedelta
import glob

def from_excel_ordinal(ordinal, _epoch0=datetime(1899, 12, 31)):
    if ordinal >= 60:
        ordinal -= 1  # Excel leap year bug, 1900 is not a leap year!
    return (_epoch0 + timedelta(days=ordinal)).replace(microsecond=0)

path = 'C:\\Input'
os.chdir(path)
filelist = glob.glob('*BLAH*.xlsx')  
filename = os.fsdecode(filelist[0])
df = pd.read_excel(filename, sheet_name = 'Blah Blah') 
m = df['Birthday'].astype(str).str.isdigit()
df.loc[m, 'Birthday'] = df.loc[m, 'Birthday'].astype(int).apply(from_excel_ordinal)
df['Birthday'] = pd.to_datetime(df['Birthday'], errors = 'coerce')

I am not sure where I am going wrong with this since the code shouldn't be blanking out the birthdays like it is doing.

Upvotes: 5

Views: 3821

Answers (2)

Trenton McKinney
Trenton McKinney

Reputation: 62453

  • All the dates can't be parsed in the same manner
  • Load the dataframe
  • Cast the dates column as a str if it's not already.
  • Use Boolean Indexing to select different date types
    • Assuming regular dates contain a /
    • Assuming Excel serial dates do not contain a /
  • Fix each dataframe separately based on its datetime type
  • Concat the dataframes back together.
import pandas as pd
from datetime import datetime

# load data
df = pd.DataFrame({'dates': ['09/01/2020', '05/15/1985', '06/07/2013', '33233', '26299', '29428']})

# display(df)

        dates
0  09/01/2020
1  05/15/1985
2  06/07/2013
3       33233
4       26299
5       29428

# set the column type as a str if it isn't already
df.dates = df.dates.astype('str')

# create a date mask based on the string containing a /
date_mask = df.dates.str.contains('/')

# split the dates out for excel
df_excel = df[~date_mask].copy()

# split the regular dates out
df_reg = df[date_mask].copy()

# convert reg dates to datetime
df_reg.dates = pd.to_datetime(df_reg.dates)

# convert excel dates to datetime; the column needs to be cast as ints
df_excel.dates = pd.TimedeltaIndex(df_excel.dates.astype(int), unit='d') + datetime(1900, 1, 1)

# combine the dataframes
df = pd.concat([df_reg, df_excel])

display(df)

       dates
0 2020-09-01
1 1985-05-15
2 2013-06-07
3 1990-12-28
4 1972-01-03
5 1980-07-28

Upvotes: 3

hd1
hd1

Reputation: 34677

pd.TimedeltaIndex(dates_in_excel_serial_format, unit='d') + pd.datetime(1900,1,1)

Demo:

> dates_in_excel_serial_format = [29428]
> pd.TimedeltaIndex(dates_in_excel_serial_format, unit='d') + pd.datetime(1900,1,1)
< DatetimeIndex(['1980-07-28'], dtype='datetime64[ns]', freq=None)

Upvotes: 0

Related Questions