如何在Pandas中计算时间差并跳过空值（保留有效计算）

首先先统一处理时间列的类型与时区（避免因时区不匹配导致计算错误），再针对空值场景计算时间差：

步骤1：构造并预处理DataFrame #

import pandas as pd
from io import StringIO

# 构造原始数据
df_str = """
case_id    first_created  last_paid       submitted_time
   3456    2021-01-27     2021-01-29      2021-01-26 21:34:36.566023+00:00
   7891    2021-08-02     2021-09-16      2022-10-26 19:49:14.135585+00:00
   1245    2021-09-13     None            2022-10-31 02:03:59.620348+00:00
   9073    None           None            2021-09-12 10:25:30.845687+00:00
 """
df = pd.read_csv(StringIO(df_str.strip()), sep='\s\s+', engine='python')

# 转换所有时间列为UTC时区的datetime类型，统一格式
df['first_created'] = pd.to_datetime(df['first_created'], utc=True)
df['last_paid'] = pd.to_datetime(df['last_paid'], utc=True)
df['submitted_time'] = pd.to_datetime(df['submitted_time'], utc=True)

步骤2：按需求计算时间差 #

利用where方法过滤空值场景，仅当对应时间列非空时计算天数差，否则设为'N/A'：

# 计算create_duration：first_created非空时计算天数差，否则为N/A
df['create_duration'] = (df['submitted_time'] - df['first_created']).dt.days.where(df['first_created'].notna(), 'N/A')

# 计算paid_duration：last_paid非空时计算天数差，否则为N/A
df['paid_duration'] = (df['submitted_time'] - df['last_paid']).dt.days.where(df['last_paid'].notna(), 'N/A')

最终结果 #

执行后得到的DataFrame如下：

case_id           first_created              last_paid               submitted_time create_duration paid_duration
0     3456 2021-01-27 00:00:00+00:00 2021-01-29 00:00:00+00:00 2021-01-26 21:34:36.566023+00:00              -1            -3
1     7891 2021-08-02 00:00:00+00:00 2021-09-16 00:00:00+00:00 2022-10-26 19:49:14.135585+00:00             450            405
2     1245 2021-09-13 00:00:00+00:00                       NaT 2022-10-31 02:03:59.620348+00:00             413           N/A
3     9073                       NaT                       NaT 2021-09-12 10:25:30.845687+00:00            N/A           N/A

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