使用Pandas实现PROD字段80%-90%文本匹配并删除高库存重复产品

首先安装依赖库：

pip install pandas fuzzywuzzy python-Levenshtein

完整代码实现 #

import pandas as pd
from fuzzywuzzy import fuzz

# 1. 加载数据，可替换为pd.read_csv/ pd.read_excel读取本地文件
data = {
    "PROD": ["product abcdefg (white)", "product abcdefg blue color", "product abcdefg - pink", "product zy"],
    "QUAN": [20, 30, 20, 80]
}
df = pd.DataFrame(data)

# 2. 配置相似度阈值，可在80-90区间自定义调整
SIMILARITY_THRESHOLD = 85
used_index = set()
group_list = []

# 3. 模糊匹配分组
for idx in df.index:
    if idx in used_index:
        continue
    current_prod = df.loc[idx, "PROD"]
    current_group = [idx]
    used_index.add(idx)
    # 遍历未分组行做匹配
    for other_idx in df.index:
        if other_idx in used_index:
            continue
        sim_score = fuzz.ratio(current_prod, df.loc[other_idx, "PROD"])
        if sim_score >= SIMILARITY_THRESHOLD:
            current_group.append(other_idx)
            used_index.add(other_idx)
    group_list.append(current_group)

# 4. 给每行打分组标签
df["group_id"] = -1
for group_id, index_list in enumerate(group_list):
    df.loc[index_list, "group_id"] = group_id

# 5. 每个分组保留库存最低的1条
result = df.sort_values("QUAN", ascending=True).groupby("group_id", as_index=False).first().drop("group_id", axis=1)

# 输出结果
print(result)

运行结果 #

PROD  QUAN
0    product abcdefg (white)    20
1                 product zy    80

调整说明 #

• 若需要更宽松的匹配，可将SIMILARITY_THRESHOLD调低到80，更严格的话调高到90
• 10万行以上的大数据量场景，可先对PROD列做预处理（去掉括号、颜色关键词等冗余后缀），减少匹配计算耗时

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