如何按指定优先级筛选DataFrame中同一id的ethnicity记录？

输入数据 #

需求说明 #

同一个id对应多个ethnicity时，按 South Asian > White > Other 的优先级保留行：

• 若id同时有South Asian和White，只留South Asian
• 若id有White/South Asian和Other，只留White/South Asian
• 仅当id只有Other时，保留该行

预期输出 #

解决方案 #

方法一：SQL实现（数据库场景适用） #

核心逻辑是给不同ethnicity分配优先级权重，通过窗口函数筛选每个id下权重最高的行：

WITH ranked_data AS (
    SELECT 
        id,
        ethnicity,
        -- 按优先级赋值：South Asian权重最高，其次White，最后Other
        CASE ethnicity
            WHEN 'south asian' THEN 3
            WHEN 'white' THEN 2
            WHEN 'other' THEN 1
        END AS priority_score,
        -- 给每个id内的行按优先级降序排序，最高分排第1
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY priority_score DESC) AS rn
    FROM your_table_name
)
SELECT id, ethnicity
FROM ranked_data
WHERE rn = 1;

如果你的数据库支持QUALIFY语法（如BigQuery、Snowflake），可以简化写法：

SELECT id, ethnicity
FROM your_table_name
QUALIFY ROW_NUMBER() OVER (PARTITION BY id ORDER BY 
    CASE ethnicity
        WHEN 'south asian' THEN 3
        WHEN 'white' THEN 2
        WHEN 'other' THEN 1
    END DESC) = 1;

方法二：Python Pandas实现（本地数据处理适用） #

通过优先级映射、排序、去重三步完成筛选：

import pandas as pd

# 构造示例数据（实际可从文件/数据库读取）
data = pd.DataFrame({
    'id': [1,2,2,3,4,4],
    'ethnicity': ['white', 'south asian', 'other', 'other', 'white', 'south asian']
})

# 定义优先级映射，值越大优先级越高
priority_map = {'south asian': 3, 'white': 2, 'other': 1}
# 添加优先级列
data['priority'] = data['ethnicity'].map(priority_map)
# 按id分组、优先级降序排序，保留每个id的第一行
result = data.sort_values(['id', 'priority'], ascending=[True, False]) \
             .drop_duplicates(subset='id', keep='first') \
             .drop('priority', axis=1)

# 输出结果
print(result)

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