Python实现按日期分组字典并合并metric与horizon为新键

需求说明 #

先将每个字典中的metric与horizon值合并为新键（如rmse-1、rmse-2），对应值为原value字段内容，再按date字段分组，最终合并为指定格式的字典列表。

输入数据集 #

import pandas as pd 
import datetime
dataset = [{"date": datetime.datetime(2022, 9, 10, tzinfo=datetime.timezone.utc), 
            "extra": 0, 
            "horizon": 1, 
            "metric": "rmse", 
            "value": 1
           },
           {"date": datetime.datetime(2022, 9, 11, tzinfo=datetime.timezone.utc), 
            "extra": 0, 
            "horizon": 1, 
            "metric": "rmse", 
            "value": 2
           },
           {"date": datetime.datetime(2022, 9, 10, tzinfo=datetime.timezone.utc), 
            "extra": 0, 
            "horizon": 2, 
            "metric": "rmse", 
            "value": 3
           },
           {"date": datetime.datetime(2021, 9, 11, tzinfo=datetime.timezone.utc), 
            "extra": 0, 
            "horizon": 2, 
            "metric": "rmse", 
            "value": 4
           }     
    ]

期望输出 #

desired_output= [{"date": datetime.datetime(2022, 9, 10, tzinfo=datetime.timezone.utc), 
             "rmse-1": 1,  
             "rmse-2": 3, 
           },
           {"date": datetime.datetime(2022, 9, 11, tzinfo=datetime.timezone.utc), 
             "rmse-1": 2,  
             "rmse-2": 4, 
           },
           {"date": datetime.datetime(2021, 9, 11, tzinfo=datetime.timezone.utc), 
             "rmse-2": 4, 
           }
    ]

现有代码及输出 #

现有实现代码 #

def group_and_merge_dataset(dataset, group_by_key, merge_value_keys):

    new_dataset = []
    for item in dataset:
        item.update({"metric": "{}-{}".format(item['metric'],item['horizon'])})
        d = {"date": item[group_by_key], item['metric']: item['value']}
        new_dataset.append(d)
    
    for item in new_dataset:
        print(item)

print(group_and_merge_dataset(dataset, 'date', ['metric', 'horizon']))

现有代码输出 #

{'date': datetime.datetime(2022, 9, 10, 0, 0, tzinfo=datetime.timezone.utc), 'rmse-1': 1}
{'date': datetime.datetime(2022, 9, 11, 0, 0, tzinfo=datetime.timezone.utc), 'rmse-1': 2}
{'date': datetime.datetime(2022, 9, 10, 0, 0, tzinfo=datetime.timezone.utc), 'rmse-2': 3}
{'date': datetime.datetime(2021, 9, 11, 0, 0, tzinfo=datetime.timezone.utc), 'rmse-2': 4}
None

完善方案 #

提供两种实现方式，按需选择：

方法一：原生Python实现 #

通过字典按日期分组，逐步合并字段：

def group_and_merge_dataset(dataset, group_by_key):
    grouped = {}
    for item in dataset:
        date = item[group_by_key]
        metric_key = f"{item['metric']}-{item['horizon']}"
        # 初始化日期对应的字典
        if date not in grouped:
            grouped[date] = {"date": date}
        # 添加合并后的指标键值对
        grouped[date][metric_key] = item['value']
    # 转换为列表返回
    return list(grouped.values())

# 调用并打印结果
result = group_and_merge_dataset(dataset, 'date')
for item in result:
    print(item)

方法二：Pandas实现（适合大数据量） #

利用透视表快速完成分组与列转换：

import pandas as pd

# 转换为DataFrame
df = pd.DataFrame(dataset)
# 生成合并后的指标列
df['metric_horizon'] = df['metric'] + '-' + df['horizon'].astype(str)
# 透视表分组
pivot_df = df.pivot(index='date', columns='metric_horizon', values='value').reset_index()
# 转换为字典列表
result = pivot_df.to_dict('records')

# 打印结果
for item in result:
    print(item)

输出结果说明 #

两种方法都会得到如下结构的结果（Pandas方法中缺失值会显示nan，可通过pivot_df.fillna(0)替换为0，或根据需求处理）：

{'date': datetime.datetime(2022, 9, 10, 0, 0, tzinfo=datetime.timezone.utc), 'rmse-1': 1, 'rmse-2': 3}
{'date': datetime.datetime(2022, 9, 11, 0, 0, tzinfo=datetime.timezone.utc), 'rmse-1': 2, 'rmse-2': nan}
{'date': datetime.datetime(2021, 9, 11, 0, 0, tzinfo=datetime.timezone.utc), 'rmse-1': nan, 'rmse-2': 4}

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