pandas实现分组匹配BLU/RED标识并统计Grade组合频次

待处理原始数据表Table1如下：

Fuse    Ident   Grade
A1  BLU123  skyline
A1  RED235  blue
A1  RED345  ortho
B1  RED160  linx
B1  BLU760  milli
B2  BLU222  moli
B2  RED201  straw
C1  RED201  straw
C2  BLU222  moli
D1  RED235  blue
D1  BLU123  skyline
E1  NA  NA  NA
E2  NA  NA  NA
F1  BLU999  monte
F1  BLU23  nome   

目标输出 #

提供两种可选格式的统计结果表Table2：

Option1（两两配对计数） #

Grade1  Grade2  row#
blue    skyline 2
ortho   skyline 1
linx    milli   1
straw   moli    2

Option2（同组同Grade2的Grade1拼接后计数） #

Grade1  Grade2  row#
blue;ortho  skyline 1
linx    milli   1
straw   moli    2

计算规则 #

• 按Fuse字段拆分数据子集，仅当子集内Ident列同时存在BLU、RED前缀的条目时，该子集为有效集合
• 有效集合中，RED前缀Ident对应的Grade记为Grade1，BLU前缀Ident对应的Grade记为Grade2，两两组队生成完整配对；例：A1子集可生成2组有效配对（RED235&BLU123、RED345&BLU123）
• 最终结果需统计不同Grade1、Grade2组合的出现次数，填入row#列
• 过滤规则：Fuse、Ident为空（含NA）的行直接忽略；子集内不存在BLU、RED前缀互补配对的直接忽略

原有代码问题 #

原代码按["Fuse","Ident"]双字段分组，分组粒度为单条Ident记录，无法在子集内同时拿到BLU和RED两类数据，自然无法完成配对和统计。 #

基于pandas实现，先做数据清洗，再按规则生成配对、统计计数，代码如下：

import pandas as pd
from itertools import product

# 1. 加载并清洗基础数据
# 自动过滤原始数据中E1/E2行多出来的全空列
df = pd.read_csv("你的数据文件路径", sep="\s+")
df = df.dropna(axis=1, how="all")
# 丢弃Fuse/Ident为空的无效行
df = df.dropna(subset=["Fuse", "Ident"])
# 提取Ident前缀，仅保留BLU/RED开头的有效记录
df["prefix"] = df["Ident"].str[:3]
df = df[df["prefix"].isin(["BLU", "RED"])]

# 2. 按Fuse分组，筛选有效集合并生成对应配对
pairs_opt1 = []
pairs_opt2 = []
for fuse, group in df.groupby("Fuse"):
    # 子集不同时包含BLU和RED前缀则直接跳过
    if not {"BLU", "RED"}.issubset(set(group["prefix"])):
        continue
    # 提取当前子集的Grade1（RED对应）、Grade2（BLU对应）列表
    g1_list = group[group["prefix"] == "RED"]["Grade"].unique().tolist()
    g2_list = group[group["prefix"] == "BLU"]["Grade"].unique().tolist()
    
    # 生成Option1需要的两两笛卡尔积配对
    for g1, g2 in product(g1_list, g2_list):
        pairs_opt1.append({"Grade1": g1, "Grade2": g2})
    
    # 生成Option2需要的同Grade2拼接Grade1的配对
    for g2 in g2_list:
        g1_concat = ";".join(sorted(g1_list))  # 排序保证拼接顺序一致
        pairs_opt2.append({"Grade1": g1_concat, "Grade2": g2})

# 3. 分组计数生成最终结果
## Option1结果
res1 = pd.DataFrame(pairs_opt1)
res1 = res1.groupby(["Grade1", "Grade2"]).size().reset_index(name="row#")
print("=====Option1结果=====")
print(res1)

## Option2结果
res2 = pd.DataFrame(pairs_opt2)
res2 = res2.groupby(["Grade1", "Grade2"]).size().reset_index(name="row#")
print("=====Option2结果=====")
print(res2)

逻辑说明 #

• 预处理阶段提前过滤空值、非BLU/RED前缀的无效行，减少后续计算量
• 分组后先校验子集有效性，无效子集直接跳过，避免冗余计算
• Option1用itertools.product生成两个Grade列表的笛卡尔积，完全匹配两两配对的要求
• Option2按单个Fuse内的Grade2维度，将对应所有Grade1用分号拼接后再做计数
• 两类结果最后都按Grade1、Grade2维度分组统计行数，直接得到row#列

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