使用Pandas合并含重复项的CSV文件时首行重复问题的解决

问题背景 #

有两个CSV文件需要条件连接：

• 第一个文件包含文件路径，但存在多个Unknown字段，共17543行+表头：

File path;Song title;Artist names;Album title;Track number;Duration seconds
"Inverno.mp3";"Inverno";"Unknown";"Unknown";"0";280
"Inverno(1).mp3";"Inverno(1)";"Unknown";"Unknown";"0";280
"Soldier, Soldier.mp3";"Soldier, Soldier";"Natalie Merchant";"The House Carpenter's Daughter";"06";225
"Surely.mp3";"Surely";"Unknown";"Unknown";"0";31
"Intro.mp3";"Intro";"intro";"TEJO Beat";"01";30

• 第二个文件无文件路径，但包含第一个文件缺失的标签信息，共17560行+表头：

Song title,Album title,Artist names,Duration seconds
Inverno,Perfil,Adriana Calcanhotto,280
Inverno,Fabrica Do Poema,Adriana Calcanhotto,280
"Soldier, Soldier",The House Carpenter's Daughter,Natalie Merchant,225
Surely,Supertramp,Supertramp,31
Intro,TEJO Beat,intro,30

唯一可靠的匹配键是Song title和Duration seconds的组合，但存在少量重复项。目标是生成包含所有Unknown字段文件完整信息的CSV，但当前合并后出现问题：像Inverno.mp3和Inverno(1).mp3这类歌名相似、时长相同但对应不同专辑的文件，合并结果中文件路径重复，本该是两行不同路径对应不同专辑，实际却是同一路径对应两个不同专辑。

当前代码 #

import pandas as pd  # 补充缺失的导入语句
keycol = ["Song title", "Duration seconds"]

include_values = ["Unknown"]

first = pd.read_csv(firstfile, sep=None, engine='python')

second = pd.read_csv(secondfile, sep=None, engine='python')
merged = first.merge(second, on=keycol)

mergedfiltered = merged[merged[['Album title_x', 'Artist names_x']].isin(include_values).all(axis=1)]
header = ["File path","Song title","Artist names","Album title","Track number","Duration seconds"]
column_list = ["File path", "Song title", "Artist names_y","Album title_y","Track number", "Duration seconds"]
mergedfiltered.to_csv(destination, index=False, sep=";", columns=column_list, header=header)

问题核心 #

默认的merge会生成笛卡尔积匹配：当第一个文件中存在多个匹配同一键组合的行，第二个文件中也有多个同键组合的行时，会把第一个的每一行和第二个的每一行都配对，导致重复的文件路径。

解决方案 #

给每个重复的键组合添加组内序号，将序号加入匹配键，确保每行一一对应：

修改后的代码 #

import pandas as pd

keycol = ["Song title", "Duration seconds"]
include_values = ["Unknown"]

# 读取两个文件
first = pd.read_csv(firstfile, sep=None, engine='python')
second = pd.read_csv(secondfile, sep=None, engine='python')

# 给重复的键组合添加组内序号，从0开始计数
first['match_idx'] = first.groupby(keycol).cumcount()
second['match_idx'] = second.groupby(keycol).cumcount()

# 使用包含序号的新键组合进行合并
merged = first.merge(second, on=keycol + ['match_idx'])

# 过滤出原文件中Artist或Album为Unknown的行
mergedfiltered = merged[merged[['Album title_x', 'Artist names_x']].isin(include_values).all(axis=1)]

# 准备输出列和表头
header = ["File path","Song title","Artist names","Album title","Track number","Duration seconds"]
column_list = ["File path", "Song title", "Artist names_y","Album title_y","Track number", "Duration seconds"]
mergedfiltered.to_csv(destination, index=False, sep=";", columns=column_list, header=header)

代码说明 #

1. 添加组内序号：groupby(keycol).cumcount()会对每个键组合下的行按顺序计数，即使键组合重复，加上序号后就能唯一标识每一行。
2. 扩展匹配键：合并时用keycol + ['match_idx']作为新的匹配键，确保第一个文件的第n个重复行只和第二个文件的第n个重复行配对，避免笛卡尔积。
3. 保留原有逻辑：原有的过滤和输出逻辑不变，仅修改合并前的预处理步骤。

预期输出 #

修改后，合并结果会正确对应不同的文件路径和专辑：

File path;Song title;Artist names;Album title;Track number;Duration seconds
"Inverno.mp3";"Inverno";"Adriana Calcanhotto";"Perfil";"0";280
"Inverno(1).mp3";"Inverno(1)";"Adriana Calcanhotto";"Fabrica Do Poema";"0";280
"Soldier, Soldier.mp3";"Soldier, Soldier";"Natalie Merchant";"The House Carpenter's Daughter";"06";225
"Surely.mp3";"Surely";"Supertramp";"Supertramp";"0";31
"Intro.mp3";"Intro";"intro";"TEJO Beat";"01";30

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