SQL合并Name列并按Category分组求和的实现问题

现有数据 #

需求 #

合并Name1和Name2列，按Category分组，同一名称属于同一分类时，对Total列求和。

尝试过的代码 #

SQL代码（结果不符合预期） #

pysqldf = lambda q1: sqldf(q1, globals())
q1 = """SELECT Name, SUM(Total) AS Total
FROM
(
    SELECT Name1 AS Name, SUM(Total) AS Total
    FROM df
    WHERE Name1 IS NOT NULL
    GROUP BY Name1
    UNION ALL
    SELECT Name2, SUM(Total)
    FROM df
    WHERE Name2 IS NOT NULL
    GROUP BY Name2
) t
GROUP BY Name
ORDER BY Name"""
df2 = pysqldf(q1)

Pandas代码（结果不符合预期） #

df2 = df.groupby(['investigator','second_investigator','requested_test']).agg({'Count': 'sum'})

期望输出 #

正确解法 #

SQL实现 #

原SQL未关联Category字段，导致无法按分类分组求和。正确逻辑是先拆分Name1和Name2的记录并保留对应分类，再按名称+分类分组：

pysqldf = lambda q1: sqldf(q1, globals())
q1 = """SELECT Name, Category, SUM(Total) AS Total
FROM (
    -- 提取Name1的有效记录
    SELECT Name1 AS Name, Category, Total
    FROM df
    WHERE Name1 IS NOT NULL
    UNION ALL
    -- 提取Name2的有效记录
    SELECT Name2 AS Name, Category, Total
    FROM df
    WHERE Name2 IS NOT NULL
) t
GROUP BY Name, Category
ORDER BY Name, Category"""
df2 = pysqldf(q1)

逻辑说明 #

1. 用UNION ALL将Name1和Name2拆分为独立行，同时保留对应的Category和Total，过滤空名称；
2. 按Name和Category双重分组，对Total求和，得到同一名称同一分类下的总和；
3. 按名称和分类排序，与期望输出一致。

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