在Pandas中基于另一列条件将Unix时间戳转换为相对日期

Python Pandas 实现 #

以下是用Pandas处理该需求的具体步骤：

• 导入依赖并加载数据

import pandas as pd

# 示例数据
data = {
    'A': ['activity1', 'activity2', 'activity3', 'activity4', 'activity5'],
    'index': [1, 2, 1, 2, 3],
    'timestamp': [1.612946e+09, 1.614255e+09, 1.612181e+09, 1.613045e+09, 1.637668e+09]
}
df = pd.DataFrame(data)

• 划分分组
  通过index == 1的累积求和，为每个以index=1起始的序列分配唯一组ID：

df['group'] = (df['index'] == 1).cumsum()

• 计算相对时间（转换为小时）
  提取每组内index=1的timestamp作为基准，计算当前行与基准的时间差并转为小时：

# 获取各组的基准时间
base_timestamps = df[df['index'] == 1].set_index('group')['timestamp']
df['base_timestamp'] = df['group'].map(base_timestamps)
# 秒转小时
df['relative_hours'] = (df['timestamp'] - df['base_timestamp']) / 3600

• 生成目标D列
  格式化输出结果，index=1的行直接显示0，其余行显示小时数加单位：

df['D'] = df.apply(
    lambda row: '0' if row['index'] == 1 else f"{int(row['relative_hours'])} hours",
    axis=1
)
# 清理辅助列（可选）
df = df.drop(['group', 'base_timestamp', 'relative_hours'], axis=1)

运行后输出结果与示例完全一致：

A  index     timestamp          D
0  activity1      1  1.612946e+09          0
1  activity2      2  1.614255e+09  80 hours
2  activity3      1  1.612181e+09          0
3  activity4      2  1.613045e+09  50 hours
4  activity5      3  1.637668e+09  430 hours

Excel 实现 #

如果用Excel处理，可按以下步骤操作：

1. 划分分组：在空白列（比如E列）的E2单元格输入公式=IF(B2=1,E1+1,E1)，E1单元格初始值设为1，下拉填充整列，完成组号标记。
2. 获取基准时间：在另一空白列（比如F列）的F2单元格输入公式=XLOOKUP(E2,$E$2:$E$6,$C$2:$C$6,,0,1)，下拉填充，得到每行对应组的基准timestamp。
3. 生成D列：在D2单元格输入公式=IF(B2=1,"0",INT((C2-F2)/3600)&" hours")，下拉填充即可得到目标列。

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