按标识将行数据转换为列结构的技术实现求助

原始数据 #

目标结构 #

技术实现方案 #

方案1：SQL实现 #

通过窗口函数给用户记录排序编号，再用条件聚合完成横向展开。

MySQL版本

WITH ranked_data AS (
    SELECT 
        ID,
        Name,
        TopNotch,
        Date,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date) AS row_num
    FROM original_table
)
SELECT
    DENSE_RANK() OVER (ORDER BY ID) AS ID,
    Name,
    MAX(CASE WHEN row_num = 1 THEN TopNotch END) AS TopNotch1,
    MAX(CASE WHEN row_num = 1 THEN Date END) AS Date1,
    MAX(CASE WHEN row_num = 2 THEN TopNotch END) AS TopNotch2,
    MAX(CASE WHEN row_num = 2 THEN Date END) AS Date2,
    MAX(CASE WHEN row_num = 3 THEN TopNotch END) AS TopNotch3,
    MAX(CASE WHEN row_num = 3 THEN Date END) AS Date3
FROM ranked_data
GROUP BY ID, Name
ORDER BY ID;

SQL Server版本

WITH ranked_data AS (
    SELECT 
        ID,
        Name,
        TopNotch,
        Date,
        'TopNotch' + CAST(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date) AS VARCHAR) AS tn_col,
        'Date' + CAST(ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Date) AS VARCHAR) AS date_col,
        DENSE_RANK() OVER (ORDER BY ID) AS new_id
    FROM original_table
)
SELECT
    new_id AS ID,
    Name,
    TopNotch1,
    Date1,
    TopNotch2,
    Date2,
    TopNotch3,
    Date3
FROM ranked_data
PIVOT (MAX(TopNotch) FOR tn_col IN (TopNotch1, TopNotch2, TopNotch3)) AS pvt_tn
PIVOT (MAX(Date) FOR date_col IN (Date1, Date2, Date3)) AS pvt_date
ORDER BY new_id;

方案2：Python Pandas实现 #

通过分组排序、透视表展开完成转换。

import pandas as pd

# 加载原始数据
df = pd.DataFrame({
    'ID': [100, 100, 102, 102, 102, 103, 103, 103],
    'Name': ['Dewey', 'Dewey', 'Huey', 'Huey', 'Huey', 'Louie', 'Louie', 'Louie'],
    'TopNotch': [10,9,8,6,4,11,12,13],
    'Date': ['2011-01-01','2011-01-02','2022-01-13','2022-02-13','2022-03-13','2012-08-10','2012-09-10','2012-10-10']
})

# 给每组用户的记录按日期排序编号
df['row_num'] = df.groupby('ID')['Date'].rank(method='first', ascending=True).astype(int)

# 分别透视TopNotch和Date列
pivot_tn = df.pivot(index=['ID', 'Name'], columns='row_num', values='TopNotch').add_prefix('TopNotch')
pivot_date = df.pivot(index=['ID', 'Name'], columns='row_num', values='Date').add_prefix('Date')

# 合并结果并生成连续ID
result = pd.concat([pivot_tn, pivot_date], axis=1).reset_index()
result['ID'] = result.groupby('ID').ngroup() + 1

# 调整列顺序并填充空值
result = result[['ID', 'Name', 'TopNotch1', 'Date1', 'TopNotch2', 'Date2', 'TopNotch3', 'Date3']]
result = result.fillna('')

print(result)

方案3：Excel实现 #

1. 给记录编号：在原始数据新增列（如E列），E2输入=COUNTIF($A$2:A2,A2)，下拉填充，得到每个用户的记录序号。
2. 生成目标表头：在新区域输入目标结构的所有表头。
3. 提取唯一用户：复制原始数据A、B列，去重后粘贴到目标区域ID、Name列，给ID列重新编号为1、2、3。
4. 匹配数据：
   • 目标区域C2（TopNotch1）输入公式：=XLOOKUP($A2&$B2&C$1,原始数据!$A:$A&原始数据!$B:$B&"TopNotch"&原始数据!$E:$E,原始数据!$C:$C,"")，回车后向右向下填充。
   • Date列替换公式中的原始数据!$C:$C为原始数据!$D:$D，重复填充操作。

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