如何按colA和colB多列分组，筛选每组中colC最早的记录

原始数据 #

List[0]: .colA:"Red". colB:"Big".colC:"2020".colD:"info0a".colE:"info02".
List[1]: .colA:"Red". colB:"Big".colC:"2021".colD:"info1a".colE:"info12".
List[2]: .colA:"Red". colB:"Big".colC:"2022".colD:"info2a".colE:"info22".
List[3]: .colA:"Red". colB:"Small".colC:"2021".colD:"info3".colE:"info32".
List[4]: .colA:"Red". colB:"Small".colC:"1999".colD:"info4a".colE:"info42".
List[5]: .colA:"Blue". colB:"Small".colC:"2023".colD:"info5a".colE:"info52".
List[6]: .colA:"Blue". colB:"Small".colC:"2025".colD:"info5a".colE:"info52".

需求 #

按colA和colB分组，筛选每组中colC（年份）最早的记录，期望结果：

colA:"Red".colB:"Big".colC:"2020".colD:"info0a".colE:"info02".(for Red,Big,oldest is 2020)
colA:"Red".colB:"Small".colC:"1999".colD:"info4a".colE:"info42".(Red,Small,oldest 1999)
colA:"Blue".colB:"Small".colC:"2023".colD:"info5a".colE:"info52".(Blue,Small, oldest 2023)

实现方案 #

方案1：使用Python Pandas库（推荐） #

Pandas的分组和索引功能可以快速实现需求：

import pandas as pd

# 将原始数据转为字典列表
data = [
    {"colA": "Red", "colB": "Big", "colC": "2020", "colD": "info0a", "colE": "info02"},
    {"colA": "Red", "colB": "Big", "colC": "2021", "colD": "info1a", "colE": "info12"},
    {"colA": "Red", "colB": "Big", "colC": "2022", "colD": "info2a", "colE": "info22"},
    {"colA": "Red", "colB": "Small", "colC": "2021", "colD": "info3", "colE": "info32"},
    {"colA": "Red", "colB": "Small", "colC": "1999", "colD": "info4a", "colE": "info42"},
    {"colA": "Blue", "colB": "Small", "colC": "2023", "colD": "info5a", "colE": "info52"},
    {"colA": "Blue", "colB": "Small", "colC": "2025", "colD": "info5a", "colE": "info52"}
]

# 转为DataFrame并转换colC为整数类型
df = pd.DataFrame(data)
df["colC"] = df["colC"].astype(int)

# 分组后取每组colC最小的对应行
result = df.loc[df.groupby(["colA", "colB"])["colC"].idxmin()]

# 格式化输出符合期望的格式
for idx, row in result.iterrows():
    print(f'colA:"{row.colA}".colB:"{row.colB}".colC:"{row.colC}".colD:"{row.colD}".colE:"{row.colE}".(for {row.colA},{row.colB},oldest is {row.colC})')

方案2：原生Python实现（无需第三方库） #

如果不想依赖Pandas，可以用原生字典分组+最小值筛选：

data = [
    {"colA": "Red", "colB": "Big", "colC": "2020", "colD": "info0a", "colE": "info02"},
    {"colA": "Red", "colB": "Big", "colC": "2021", "colD": "info1a", "colE": "info12"},
    {"colA": "Red", "colB": "Big", "colC": "2022", "colD": "info2a", "colE": "info22"},
    {"colA": "Red", "colB": "Small", "colC": "2021", "colD": "info3", "colE": "info32"},
    {"colA": "Red", "colB": "Small", "colC": "1999", "colD": "info4a", "colE": "info42"},
    {"colA": "Blue", "colB": "Small", "colC": "2023", "colD": "info5a", "colE": "info52"},
    {"colA": "Blue", "colB": "Small", "colC": "2025", "colD": "info5a", "colE": "info52"}
]

# 按(colA, colB)分组存储数据
groups = {}
for item in data:
    group_key = (item["colA"], item["colB"])
    if group_key not in groups:
        groups[group_key] = []
    groups[group_key].append(item)

# 筛选每组中年份最早的记录
result = []
for key, items in groups.items():
    # 以colC的整数值为依据，找到最小年份的记录
    oldest_item = min(items, key=lambda x: int(x["colC"]))
    result.append(oldest_item)

# 输出符合期望的格式
for item in result:
    print(f'colA:"{item["colA"]}".colB:"{item["colB"]}".colC:"{item["colC"]}".colD:"{item["colD"]}".colE:"{item["colE"]}".(for {item["colA"]},{item["colB"]},oldest is {item["colC"]})')

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