如何在pandas中解决timedelta64列相减出现负天数偏移的问题

问题原因 #

1. 你仅转换了Factor列为timedelta类型，Start_Time、End_Time仍为字符串类型，直接做减法运算会导致类型不匹配的计算异常。
2. 你看到的-1 days +xx:xx:xx格式不是计算错误，是pandas对负timedelta的默认显示逻辑，实际数值等价于对应的负时间差，只是不符合常规阅读习惯。

解决方案 #

第一步：统一转换所有时间列格式 #

先修正原代码的语法错误，同时将所有时间相关列转换为timedelta类型：

import pandas as pd
import numpy as np 

example=[["2","0 days 00:00:57.3","0 days 00:01:12.9","00:00:50.2","pos"],
 ["13","0 days 00:30:08.5","0 days 00:32:14.0", "00:20:28.0","neg"],
 ["6","0 days 00:27:18.7","0 days 00:01:24.2","0 days 00:26:48.4","pos"],
 ["7","0 days 00:01:56.676000","0 days 00:04:56.2","0 days 00:15:33.455000","pos"]]

# 修正原代码语法错误：columns参数漏了等号
example_table = pd.DataFrame(example, columns=["ID","Start_Time","End_Time","Factor","tag"]) 

# 统一转换所有时间列为timedelta类型
time_cols = ["Start_Time", "End_Time", "Factor"]
for col in time_cols:
    example_table[col] = pd.to_timedelta(example_table[col])

# 基础校正计算
example_table["corrected_start"] = example_table["Start_Time"] - example_table["Factor"]

第二步：按需处理负时间差 #

根据业务需求选择对应处理方案：

方案1：业务不允许出现负校正时间，将负值截断为0

example_table["corrected_start_clamped"] = example_table["corrected_start"].where(
    example_table["corrected_start"] >= pd.Timedelta(0), 
    pd.Timedelta(0)
)

方案2：保留负值，仅优化显示格式

可以转为总秒数存储，或者自定义格式化字符串：

# 转为总秒数存储，正负值直接体现
example_table["corrected_start_seconds"] = example_table["corrected_start"].dt.total_seconds()

# 自定义格式化为 ±HH:MM:SS.fff 人类可读格式
def format_timedelta(td):
    total_seconds = td.total_seconds()
    sign = "-" if total_seconds < 0 else ""
    abs_seconds = abs(total_seconds)
    hours = int(abs_seconds // 3600)
    minutes = int((abs_seconds % 3600) // 60)
    seconds = abs_seconds % 60
    return f"{sign}{hours:02d}:{minutes:02d}:{seconds:06.3f}"

example_table["corrected_start_formatted"] = example_table["corrected_start"].apply(format_timedelta)

内容的提问来源于stack exchange，提问作者zob