如何在时间序列DataFrame中添加计算值？含跨年补值场景

原始数据 #

先构造对应结构的DataFrame：

import pandas as pd

df = pd.DataFrame({
    'id': [2, 5, 8, 11, 14],
    'meter': [2, 2, 2, 2, 2],
    'reading': [8232, 8267, 8472, 9354, 9417],
    'date': pd.to_datetime(['2023-09-01', '2023-11-01', '2023-12-01', '2024-02-17', '2024-03-04'])
})

问题背景 #

直接按年份取首尾读数计算消费会出现极大误差：2023年首尾差为240，2024年为63，但实际总消费是1185。核心原因是2023-12-01到2024-02-17的跨年间隔过长，需补全年末和年初的读数来拆分该区间的消费。

实现步骤 #

1. 识别跨年时间区间 #

先对数据按日期排序，再定位跨年度的相邻行：

# 按日期排序并重置索引
df_sorted = df.sort_values('date').reset_index(drop=True)

# 标记跨年区间：前一行年份小于后一行年份
df_sorted['next_year'] = df_sorted['date'].dt.year.shift(-1)
cross_year_mask = df_sorted['date'].dt.year < df_sorted['next_year']
cross_year_rows = df_sorted[cross_year_mask]

2-4. 计算区间时长、总消费和日均消费 #

针对找到的跨年区间（2023-12-01到2024-02-17）计算关键指标：

# 获取跨年区间的起始与结束行
start_row = cross_year_rows.iloc[0]
end_row = df_sorted.iloc[cross_year_rows.index[0]+1]

# 计算区间总天数
total_days = (end_row['date'] - start_row['date']).days
# 区间总消费
total_consumption = end_row['reading'] - start_row['reading']
# 日均消费
daily_consumption = total_consumption / total_days

5-6. 添加年末和年初的补全记录 #

构造2023-12-31和2024-01-01的读数记录，合并到原数据：

# 计算2023-12-31的读数
dec_31_date = pd.to_datetime('2023-12-31')
days_to_dec31 = (dec_31_date - start_row['date']).days
dec_31_reading = start_row['reading'] + days_to_dec31 * daily_consumption

# 计算2024-01-01的读数
jan_1_date = pd.to_datetime('2024-01-01')
days_from_jan1 = (end_row['date'] - jan_1_date).days
jan_1_reading = end_row['reading'] - days_from_jan1 * daily_consumption

# 构造补全记录的DataFrame
补全记录 = pd.DataFrame({
    'id': [max(df['id'])+1, max(df['id'])+2],
    'meter': [start_row['meter'], start_row['meter']],
    'reading': [round(dec_31_reading), round(jan_1_reading)],  # 电表读数取整
    'date': [dec_31_date, jan_1_date]
})

# 合并数据并重新排序
final_df = pd.concat([df_sorted, 补全记录], ignore_index=True).sort_values('date').reset_index(drop=True)

验证计算结果 #

按年份分组计算消费，结果与预期一致：

yearly_consumption = final_df.groupby(final_df['date'].dt.year)['reading'].agg(lambda x: x.iloc[-1] - x.iloc[0])
print(yearly_consumption)
# 输出：
# date
# 2023    570
# 2024    591
# Name: reading, dtype: int64

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