如何按小时对嵌套字典中的pw与datetime数据分组求和

我有一组嵌套字典格式的数据，每个子字典包含pw（功率值）和datetime.datetime类型的时间戳数据，具体如下：

[
    {"pw": 0.12345, "timestamp": datetime.datetime(2022, 6, 14, 13, 10, 27)},
    {"pw": 0.12345, "timestamp": datetime.datetime(2022, 6, 14, 13, 20, 27)},
    {"pw": 0.12345, "timestamp": datetime.datetime(2022, 6, 14, 13, 43, 27)},
    {"pw": 0.12345, "timestamp": datetime.datetime(2022, 6, 14, 14, 11, 27)},
    {"pw": 0.12345, "timestamp": datetime.datetime(2022, 6, 14, 14, 14, 27)},
    {"pw": 0.12345, "timestamp": datetime.datetime(2022, 6, 14, 14, 21, 27)},
    {"pw": 0.12345, "timestamp": datetime.datetime(2022, 6, 14, 15, 09, 27)},
    {"pw": 0.12345, "timestamp": datetime.datetime(2022, 6, 14, 15, 12, 27)},
    {"pw": 0.12345, "timestamp": datetime.datetime(2022, 6, 14, 15, 40, 27)}
]

需按小时对数据分组，完成以下操作：

• 对每组内的pw值求和
• 保留组内所有datetime.datetime时间戳的集合
• 输出每组的起始与结束时间戳

方法一：使用Python标准库itertools.groupby #

import datetime
from itertools import groupby

# 原始数据
data = [
    {"pw": 0.12345, "timestamp": datetime.datetime(2022, 6, 14, 13, 10, 27)},
    {"pw": 0.12345, "timestamp": datetime.datetime(2022, 6, 14, 13, 20, 27)},
    {"pw": 0.12345, "timestamp": datetime.datetime(2022, 6, 14, 13, 43, 27)},
    {"pw": 0.12345, "timestamp": datetime.datetime(2022, 6, 14, 14, 11, 27)},
    {"pw": 0.12345, "timestamp": datetime.datetime(2022, 6, 14, 14, 14, 27)},
    {"pw": 0.12345, "timestamp": datetime.datetime(2022, 6, 14, 14, 21, 27)},
    {"pw": 0.12345, "timestamp": datetime.datetime(2022, 6, 14, 15, 09, 27)},
    {"pw": 0.12345, "timestamp": datetime.datetime(2022, 6, 14, 15, 12, 27)},
    {"pw": 0.12345, "timestamp": datetime.datetime(2022, 6, 14, 15, 40, 27)}
]

# 定义分组键：提取日期+小时作为分组依据
def group_key(item):
    ts = item["timestamp"]
    return (ts.year, ts.month, ts.day, ts.hour)

# 先按分组键排序（groupby要求数据已排序）
sorted_data = sorted(data, key=group_key)

# 分组处理
grouped_result = []
for key, group in groupby(sorted_data, key=group_key):
    items = list(group)
    total_pw = sum(item["pw"] for item in items)
    timestamps = [item["timestamp"] for item in items]
    grouped_result.append({
        "total_pw": round(total_pw, 4),
        "timestamps": timestamps,
        "start": min(timestamps),
        "end": max(timestamps)
    })

# 输出求和结果
print("**求和结果**")
for res in grouped_result:
    ts_strs = " + ".join(str(ts) for ts in res["timestamps"])
    print(f"{res['total_pw']} , {ts_strs}")

# 输出起止时间
print("\n**起止时间**")
print(f"{'Start':<40} {'End'}")
for res in grouped_result:
    print(f"{str(res['start']):<40} , {str(res['end'])}")

方法二：使用Pandas（更简洁） #

import datetime
import pandas as pd

data = [
    {"pw": 0.12345, "timestamp": datetime.datetime(2022, 6, 14, 13, 10, 27)},
    {"pw": 0.12345, "timestamp": datetime.datetime(2022, 6, 14, 13, 20, 27)},
    {"pw": 0.12345, "timestamp": datetime.datetime(2022, 6, 14, 13, 43, 27)},
    {"pw": 0.12345, "timestamp": datetime.datetime(2022, 6, 14, 14, 11, 27)},
    {"pw": 0.12345, "timestamp": datetime.datetime(2022, 6, 14, 14, 14, 27)},
    {"pw": 0.12345, "timestamp": datetime.datetime(2022, 6, 14, 14, 21, 27)},
    {"pw": 0.12345, "timestamp": datetime.datetime(2022, 6, 14, 15, 09, 27)},
    {"pw": 0.12345, "timestamp": datetime.datetime(2022, 6, 14, 15, 12, 27)},
    {"pw": 0.12345, "timestamp": datetime.datetime(2022, 6, 14, 15, 40, 27)}
]

df = pd.DataFrame(data)
# 按小时生成分组标识
df["hour_group"] = df["timestamp"].dt.floor("H")

# 聚合计算
grouped_df = df.groupby("hour_group").agg(
    total_pw=("pw", "sum"),
    timestamps=("timestamp", list),
    start=("timestamp", "min"),
    end=("timestamp", "max")
).reset_index()

# 输出求和结果
print("**求和结果**")
for _, row in grouped_df.iterrows():
    ts_strs = " + ".join(str(ts) for ts in row["timestamps"])
    print(f"{round(row['total_pw'],4)} , {ts_strs}")

# 输出起止时间
print("\n**起止时间**")
print(f"{'Start':<40} {'End'}")
for _, row in grouped_df.iterrows():
    print(f"{str(row['start']):<40} , {str(row['end'])}")

求和结果 #

0.3704 , 2022-06-14 13:10:27 + 2022-06-14 13:20:27 + 2022-06-14 13:43:27
0.3704 , 2022-06-14 14:11:27 + 2022-06-14 14:14:27 + 2022-06-14 14:21:27
0.3704 , 2022-06-14 15:09:27 + 2022-06-14 15:12:27 + 2022-06-14 15:40:27

注：原示例中的求和数值存在误差，实际3个0.12345求和应为0.37035，四舍五入后为0.3704

起止时间 #

Start                                     End 
2022-06-14 13:10:27                       , 2022-06-14 13:43:27
    
2022-06-14 14:11:27                       , 2022-06-14 14:21:27
    
2022-06-14 15:09:27                       , 2022-06-14 15:40:27

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