如何提取字段重复值对应数据?以重复passageId的score转多列为例
重复键值场景下的关联值拆分提取方法
针对同一userId对应passageId存在重复取值时,把关联score值拆分到独立列的需求,是完全可以实现的,这类操作属于数据清洗中常见的行转列/宽表透视场景。
参考样例
原始输入数据
userId passageId score 1 1 2 1 2 3 1 1 4 2 1 3 2 3 3 2 3 4
目标输出格式
仅保留存在重复记录的userId+passageId组合,将同组内的score按出现顺序拆分到独立列:
userId passageId score_1 score_2 1 1 2 4 2 3 3 4
通用实现逻辑
所有工具的实现都围绕3个核心步骤展开:
- 以
userId和passageId的组合为分组键,圈定所有存在重复记录的分组范围 - 在每个分组内部,对关联的score值按原始出现顺序排序,从1开始给每个值分配顺序编号
- 把每个分组内不同编号的score值横向展开,以编号为后缀生成
score_1、score_2这类独立列
常用工具实现参考
Python Pandas 实现
核心是利用分组累计计数生成顺序序号,再通过透视完成行转列,核心代码片段:
import pandas as pd # 读取原始结构化数据 df = pd.read_clipboard(sep='\s+') # 分组内生成从1开始的顺序编号 df['seq'] = df.groupby(['userId', 'passageId']).cumcount() + 1 # 行转列透视 result = df.pivot( index=['userId', 'passageId'], columns='seq', values='score' ) # 按规则重命名列 result.columns = [f'score_{col}' for col in result.columns] # 过滤出仅存在重复记录的分组(即有2个及以上score值的行) result = result.dropna(subset=['score_2']).reset_index()
SQL 实现
利用窗口函数生成分组内的顺序编号,再通过条件聚合把值提取到独立列,核心逻辑片段:
WITH ranked_record AS ( SELECT userId, passageId, score, -- 按原始记录顺序给同组的score编号 ROW_NUMBER() OVER ( PARTITION BY userId, passageId ORDER BY score ) AS seq FROM 原始数据表 ) SELECT userId, passageId, -- 提取对应编号的score到独立列 MAX(CASE WHEN seq = 1 THEN score END) AS score_1, MAX(CASE WHEN seq = 2 THEN score END) AS score_2 FROM ranked_record GROUP BY userId, passageId -- 仅保留存在2条及以上重复记录的分组 HAVING COUNT(*) > 1
内容的提问来源于stack exchange,提问作者SimonRH
相关产品推荐
相关产品推荐

