如何用Python实现Excel列的回溯计算（多日期行场景）

首先先明确我们的需求和数据集：

原始数据集 #

计算规则 #

我们需要新增new_Eff和new_time两列，规则分两种情况：

• 单条记录的分组（同一ID+Date仅一行）：直接映射原字段，new_Eff = 1.Eff，new_time = Time
• 多条记录的分组（同一ID+Date有多行）：
  • 分组内第一条记录：new_Eff取同一ID前一日的2.Eff，new_time = Time / Qty
  • 分组内后续记录：new_time等于当日总Time减去前面所有记录的new_time之和，new_Eff = new_time / Qty

（注：你给出的示例中第3行new_Eff写的41.48应该是笔误，按规则应该取1/3/2019的2.Eff即41.18）

Python实现代码（基于Pandas） #

用Pandas来处理这种结构化数据的分组计算是最方便的，下面是完整实现：

import pandas as pd

# 1. 构造原始数据集（实际场景中可以用pd.read_csv读取）
data = {
    'ID': [3, 3, 3, 3],
    'Date': ['1/2/2019', '1/3/2019', '1/4/2019', '1/4/2019'],
    'Input1': ['A', 'A', 'A', 'A'],
    'Input2': ['A', 'A', 'A', 'B'],
    '1.Eff': [32.08, 55.95, 56.61, 56.61],
    '2.Eff': [76.64, 41.18, 50, 35.67],
    'Qty': [5, 10, 5, 10],
    'Time': [200, 100, 300, 300]
}

df = pd.DataFrame(data)

# 2. 转换日期格式并排序，确保时间顺序正确（关键步骤）
df['Date'] = pd.to_datetime(df['Date'])
df = df.sort_values(['ID', 'Date'])

# 3. 标记每个ID+Date分组的大小，区分单/多记录分组
df['group_size'] = df.groupby(['ID', 'Date'])['ID'].transform('count')

# 4. 获取同一ID前一日的2.Eff值，用shift(1)实现回溯
df['prev_day_2Eff'] = df.groupby('ID')['2.Eff'].shift(1)

# 5. 初始化结果列
df['new_time'] = df['Time']
df['new_Eff'] = df['1.Eff']

# 6. 自定义分组处理函数，按规则计算多记录分组的结果
def process_group(group):
    if len(group) == 1:
        # 单记录分组直接返回
        return group
    else:
        # 提取当日总Time（示例中所有行Time相同，取第一行即可；如果是总和就用group['Time'].sum()）
        total_daily_time = group['Time'].iloc[0]
        
        # 处理分组内第一条记录
        first_idx = group.index[0]
        group.loc[first_idx, 'new_time'] = group['Time'].loc[first_idx] / group['Qty'].loc[first_idx]
        group.loc[first_idx, 'new_Eff'] = group['prev_day_2Eff'].loc[first_idx]
        
        # 处理分组内后续记录
        for i in range(1, len(group)):
            current_idx = group.index[i]
            # 计算前面所有new_time的累计和
            prev_time_sum = group['new_time'].iloc[:i].sum()
            # 计算当前new_time和new_Eff
            group.loc[current_idx, 'new_time'] = total_daily_time - prev_time_sum
            group.loc[current_idx, 'new_Eff'] = group.loc[current_idx, 'new_time'] / group['Qty'].loc[current_idx]
        
        return group

# 7. 应用分组处理函数
df = df.groupby(['ID', 'Date'], group_keys=False).apply(process_group)

# 8. 清理辅助列，恢复原始顺序并输出
df = df.drop(['group_size', 'prev_day_2Eff'], axis=1)
df = df.sort_index()

# 打印结果，保留两位小数
print(df.round(2))

运行结果 #

执行代码后会得到如下结果，和需求一致：

关键细节说明 #

1. 日期排序：一定要把Date转为日期类型并排序，否则shift(1)无法正确获取前一日的数据。
2. 分组回溯：用groupby('ID')['2.Eff'].shift(1)实现同一ID下的前一日数据提取，非常高效。
3. 多记录处理：自定义函数里通过循环计算累计和，确保后续记录的new_time是当日剩余时间。
4. 灵活性：如果当日总Time是分组内的总和，只需要把total_daily_time = group['Time'].iloc[0]改成total_daily_time = group['Time'].sum()即可。

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