Python：含重复ID数据集，如何按ID分组单一行并交替选Region

我需要处理一个包含重复ID且对应不同Region值的数据集，要求为每个ID仅保留一行记录，且Region按顺序交替选取（如ID1选North、ID2选South、ID3选East、ID4选West，依此类推）。尝试多种方法均未成功，现寻求可行解决方案。

原始数据集 #

预期输出 #

解决方案 #

方法1：Python Pandas实现 #

核心逻辑：按ID分组后，根据ID序号匹配目标Region顺序；若目标Region不在当前ID的可选列表中，则按ID序号循环选取该ID的Region。

import pandas as pd

# 加载数据集（实际场景可替换为pd.read_csv等方式读取）
df = pd.DataFrame({
    'ID': [1,1,1,1,2,2,2,2,3,3,3,3,4,4,4,4,5,5,6,6,7,7,7,7,8,8,8,8,9,9,9,9,9,9],
    'Region': ['North','South','East','West','North','South','East','West','North','South','East','West','North','South','East','West','Northwest','South West','Northwest','South West','North','South','East','West','North','South','East','West','North','South','East','West','Northwest','South West']
})

# 定义基础交替选取顺序
target_order = ['North', 'South', 'East', 'West']

# 按ID分组，保留每个ID的唯一Region列表（维持原始出现顺序）
grouped = df.groupby('ID')['Region'].unique().reset_index()

# 计算每个ID对应的目标索引
grouped['target_idx'] = (grouped['ID'] - 1) % len(target_order)

# 自定义选取规则
def pick_region(row):
    target_region = target_order[row['target_idx']]
    # 优先选目标Region
    if target_region in row['Region']:
        return target_region
    # 目标不存在时，按ID序号循环选当前ID的Region
    else:
        alt_idx = (row['ID'] - 1) % len(row['Region'])
        return row['Region'][alt_idx]

# 应用选取规则并整理结果
grouped['Region'] = grouped.apply(pick_region, axis=1)
result = grouped[['ID', 'Region']]

print(result)

方法2：SQL实现（MySQL为例） #

核心逻辑：为每个ID的Region分配行号，根据ID序号计算目标行号；若目标行号不存在，则选取行号最小的记录。

WITH ranked_regions AS (
    SELECT 
        ID,
        Region,
        -- 按Region原始顺序分配行号（若需严格匹配数据加载顺序，可替换为加载时间戳等字段排序）
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Region) AS rn
    FROM your_table_name
),
target_info AS (
    SELECT 
        ID,
        -- 计算目标行号（对应North/South/East/West的顺序）
        (ID - 1) % 4 + 1 AS target_rn,
        MAX(rn) AS max_rn
    FROM ranked_regions
    GROUP BY ID
)
SELECT 
    t.ID,
    COALESCE(
        -- 优先选取目标行号的Region
        (SELECT Region FROM ranked_regions WHERE ID = t.ID AND rn = ti.target_rn),
        -- 目标行号不存在时取第一行
        (SELECT Region FROM ranked_regions WHERE ID = t.ID AND rn = 1)
    ) AS Region
FROM target_info ti
JOIN ranked_regions t ON ti.ID = t.ID
GROUP BY t.ID;

注：需将your_table_name替换为实际表名，若需要严格匹配Region的原始出现顺序，需用数据加载时的顺序字段替代ORDER BY Region。

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