如何按id_1和id_2分组并按sequence_id排序转置数据？

核心需求：按id_1和id_2对行分组，按sequence_id排序后将多行数据合并为单行，对应字段依次排列。

示例数据 #

期望输出 #

1. SQL实现（以MySQL 8.0+为例） #

通过窗口函数标记分组内的行顺序，再用条件聚合完成列转行：

WITH ranked_data AS (
    SELECT 
        date, id_1, id_2, sequence_id, data_1, data_2, data_3,
        ROW_NUMBER() OVER (PARTITION BY id_1, id_2 ORDER BY sequence_id) AS row_num
    FROM your_table
)
SELECT 
    date, id_1, id_2, 
    MAX(CASE WHEN row_num = 1 THEN sequence_id END) AS sequence_id,
    MAX(CASE WHEN row_num = 1 THEN data_1 END) AS data_1,
    MAX(CASE WHEN row_num = 1 THEN data_2 END) AS data_2,
    MAX(CASE WHEN row_num = 1 THEN data_3 END) AS data_3,
    MAX(CASE WHEN row_num = 2 THEN data_1 END) AS data_1,
    MAX(CASE WHEN row_num = 2 THEN data_2 END) AS data_2,
    MAX(CASE WHEN row_num = 2 THEN data_3 END) AS data_3,
    MAX(CASE WHEN row_num = 3 THEN data_1 END) AS data_1,
    MAX(CASE WHEN row_num = 3 THEN data_2 END) AS data_2,
    MAX(CASE WHEN row_num = 3 THEN data_3 END) AS data_3
FROM ranked_data
GROUP BY date, id_1, id_2;

注：如果分组内的最大行数超过3，需对应增加row_num = n的条件聚合语句。

2. Python Pandas实现 #

通过分组排序、添加行号、列转行三步完成：

import pandas as pd

# 构造示例数据
df = pd.DataFrame({
    'date': ['2020-01-01', '2020-01-01', '2020-01-01', '2020-02-01', '2020-02-01'],
    'id_1': ['ABC', 'ABC', 'ABC', 'DEF', 'DEF'],
    'id_2': ['123', '123', '123', '456', '456'],
    'sequence_id': [2, 3, 4, 1, 3],
    'data_1': ['hi', 'meet', 'name', 'good', 'how'],
    'data_2': ['nice', 'you', 'is', 'day', 'are'],
    'data_3': ['to', 'my', 'bob', 'sir', 'you']
})

# 分组后按sequence_id排序
grouped = df.groupby(['id_1', 'id_2'], group_keys=False).apply(lambda x: x.sort_values('sequence_id'))

# 标记每组内的行序号
grouped['row_num'] = grouped.groupby(['id_1', 'id_2']).cumcount() + 1

# 列转行展开数据
pivoted = grouped.pivot(index=['date', 'id_1', 'id_2'], columns='row_num', values=['sequence_id', 'data_1', 'data_2', 'data_3'])

# 调整列名格式，匹配期望输出
pivoted.columns = [col[0] for col in pivoted.columns]

# 重置索引得到最终结果
result = pivoted.reset_index()
print(result)

运行后缺失值会自动填充为空，结构与期望输出一致。

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