如何用Pandas按格式优先级筛选并聚合CSV行数据

步骤1：读取CSV数据 #

首先用Pandas读取目标CSV文件：

import pandas as pd

df = pd.read_csv("your_file.csv")

步骤2：按格式优先级筛选行 #

根据[KFX, AZW3, PDF]的优先级，先为每个格式分配权重，再筛选出每个id下优先级最高的格式对应的所有行：

# 定义格式优先级权重：权重越小优先级越高
format_priority = {"KFX": 0, "AZW3": 1, "PDF": 2}
df["priority"] = df["Format"].map(format_priority)

# 找出每个id对应的最高优先级格式的权重
id_top_priority = df.groupby("id")["priority"].min().reset_index()

# 筛选出每个id中优先级最高的格式的所有行
filtered_df = df.merge(id_top_priority, on=["id", "priority"], how="inner")
filtered_df = filtered_df.drop("priority", axis=1)  # 移除临时权重列

步骤3：分组聚合数据 #

针对不同列设置对应的聚合规则，避免重复内容并合并目标字段：

# 定义各列的聚合逻辑
agg_rules = {
    "Title": "first",          # 同一id下Title唯一，取第一个值即可
    "Author(s)": lambda x: ", ".join(sorted(set(x))),  # 去重后排序拼接
    "Format": "first",         # 筛选后同一id下Format唯一
    "Size": "first",           # 同一id下Size唯一
    "Tags": lambda x: ", ".join(sorted(set(x)))        # 去重后排序拼接
}

# 按id分组聚合
result_df = filtered_df.groupby("id").agg(agg_rules).reset_index()

输出结果 #

执行以下代码即可得到目标格式的CSV内容：

print(result_df.to_csv(index=False, quotechar='"'))

输出内容：

"id","Title","Author(s)","Format","Size","Tags"
"1","Horse","John","KFX","122","Classic, Drama, Horror"
"2","Banana","Anna, Julia","AZW3","312","SciFi"

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