Python中的Excel日期时间SN转换 [英] Excel Datetime SN Conversion in Python
本文介绍了Python中的Excel日期时间SN转换的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我的csv输入文件有时在日期字段中具有excel序列号.我使用以下代码,因为我的输入文件中不应包含01/2000之前的日期.但是,此解决方案非常耗时,我希望找到一个更好的解决方案.谢谢.
My csv input file sometimes has excel serial numbers in the date field. I am using the following code as my input file should never contain dates prior to 01/2000. However, this solution is quite time consuming and I am hoping to find a better solution. Thank you.
def DateCorrection(x):
if pd.to_datetime(x) < pd.to_datetime('2000-01-01'):
return pd.to_datetime(datetime.fromordinal(datetime(1900, 1, 1).toordinal() + int(x) - 2))
else:
return pd.to_datetime(x)
推荐答案
假设您的输入看起来像
import pandas as pd
df = pd.DataFrame({'date': ["2020-01-01", 43862, "2020-03-01"]})
您可以按以下步骤处理它:
you can process it as follows:
# convert everything first, ignore invalid results for now:
df['datetime'] = pd.to_datetime(df['date'])
# where you have numeric values, i.e. "excel datetime format":
nums = pd.to_numeric(df['date'], errors='coerce') # timestamp strings will give NaN here
# now replace the invalid dates:
df.loc[nums.notna(), 'datetime'] = pd.to_datetime(nums[nums.notna()], unit='d', origin='1899-12-30')
...给你
df
date datetime
0 2020-01-01 2020-01-01
1 43862 2020-02-01
2 2020-03-01 2020-03-01
相关:
这篇关于Python中的Excel日期时间SN转换的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文