Pandas中Timedelta类型列用idxmin取最小列名报错排查

问题概述 #

当DataFrame中包含pd.Timedelta标量与pd.NaT值时，执行以下代码为每行添加记录最小Timedelta值列名的新列：

incomplete_df['reason'] = "Reason is " + incomplete_df.idxmin(axis="columns")

会触发错误：ValueError: attempt to get argmin of an empty sequence。但将数据转换为整数+np.NAN时可正常运行，此不一致性已在GitHub提交编号为48123的issue，测试环境为Python 3.9.10。

可复现代码 #

import pandas as pd
import numpy as np

incomplete_df = pd.DataFrame({
    'event1': [pd.Timedelta("1 days"), pd.Timedelta("2 days"), pd.Timedelta("nat"), pd.Timedelta("5 days"), pd.Timedelta("6 days"), pd.Timedelta("nat"), pd.Timedelta("nat"), pd.Timedelta("11 days"), pd.Timedelta("nat"), pd.Timedelta("15 days")],
    'event2': [pd.Timedelta("nat"), pd.Timedelta("1 days"), pd.Timedelta("nat"), pd.Timedelta("3 days"), pd.Timedelta("4 days"), pd.Timedelta("7 days"), pd.Timedelta("nat"), pd.Timedelta("12 days"), pd.Timedelta("nat"), pd.Timedelta("17 days")],
    'event3': [pd.Timedelta("nat"), pd.Timedelta("nat"), pd.Timedelta("nat"), pd.Timedelta("nat"), pd.Timedelta("6 days"), pd.Timedelta("4 days"), pd.Timedelta("9 days"), pd.Timedelta("nat"), pd.Timedelta("3 days"), pd.Timedelta("nat")]
})

问题原因 #

这是pandas在处理Timedelta类型与数值类型时的行为不一致：

• 当某一行全部为pd.NaT时，idxmin方法处理Timedelta类型时无法识别该场景，会尝试对空序列求argmin，从而抛出错误；
• 而处理数值类型（如整数+np.NAN）的全NaN行时，idxmin会返回NaN，不会触发异常。

临时解决方案 #

方案1：先过滤全NaT行再处理 #

先标记出所有值均为NaT的行，对这类行单独处理，其余行正常调用idxmin：

import pandas as pd
import numpy as np

# 标记全NaT的行
all_nat_rows = incomplete_df.isna().all(axis=1)
# 生成结果列，全NaT行显示提示文本，其余行拼接idxmin结果
incomplete_df['reason'] = np.where(
    all_nat_rows,
    "No valid event available",
    "Reason is " + incomplete_df[~all_nat_rows].idxmin(axis="columns")
)

方案2：转换为数值类型处理 #

将Timedelta转换为总秒数（数值类型），利用数值类型的idxmin行为避免错误，之后再转换回所需格式：

import pandas as pd

# 将Timedelta转为总秒数，NaT自动转为NaN
td_seconds = incomplete_df.apply(lambda col: col.dt.total_seconds())
# 获取每行最小值得列名并拼接
incomplete_df['reason'] = "Reason is " + td_seconds.idxmin(axis="columns")
# 替换全NaN行的结果文本
incomplete_df['reason'] = incomplete_df['reason'].replace("Reason is nan", "No valid event available")

内容的提问来源于stack exchange，提问作者FluidMechanics Potential Flows