我有一個數據框，其中的生日具有與 Excel 序列日期混合的常規日期，如下所示：09/01/2020 12:00:00 AM05/15/1985 12:00:00 AM06/07/2013 12:00:00 AM332332629929428我嘗試了此答案中的解決方案，所有 Excel 串行格式的日期都被清空，同時保留正常日期格式的日期。這是我的代碼：import pandas as pdimport xlrdimport numpy as npfrom numpy import *from numpy.core import *import osimport datetimefrom datetime import datetime, timedeltaimport globdef 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')我不確定我在哪里出了問題，因為代碼不應該像現在這樣清空生日。
2 回答
嚕嚕噠
TA貢獻1784條經驗 獲得超7個贊
無法以相同的方式解析所有日期
加載數據框
如果尚未將該
dates列轉換為 a，則將其轉換為 a。str使用布爾索引選擇不同的日期類型
假設常規日期包含
/假設 Excel 序列日期不包含
/
根據日期時間類型分別修復每個數據幀
將數據幀重新連接在一起。
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])
顯示(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
侃侃無極
TA貢獻2051條經驗 獲得超10個贊
pd.TimedeltaIndex(dates_in_excel_serial_format, 單位='d') + pd.datetime(1900,1,1)
演示：
> 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)
添加回答
舉報
0/150
提交
取消