如果您在Excel中有DateTime/Time对象,最简单的方法可能是将其作为字符串导入,在缺少时添加引用日期,转换为to_datetime
并减go 引用:
df = pd.read_excel('data.xlsx', dtype={'Total time': 'str'})
ref = '1900-01-01 '
df['timedelta'] = pd.to_datetime(df['Total time']
.where(df['Total time'].str.match('\d{4}-\d{2}-\d{2}'),
ref+df['Total time'])
).sub(pd.Timestamp(ref))
另一种 Select 是保留原始类型,并使用Python+Pandas进行转换:
from datetime import datetime
def to_timedelta(x):
ref = datetime(1900, 1, 1)
if isinstance(x, datetime):
return x - ref
return str(x)
df = pd.read_excel('data.xlsx')
df['timedelta'] = pd.to_timedelta(df['Total time'].apply(to_timedelta))
输出:
Total time timedelta
0 06:27:53 0 days 06:27:53
1 1900-01-02 20:22:26 1 days 20:22:26
2 1900-01-07 03:58:44 6 days 03:58:44