在R中基于规则将随机数转换为有序序列SequenceNum

原始数据集 #

ID  Date        Col1     Score
  13  2002-08-01  18221    60.75
  13  2010-08-12  18448    65.33
  11  2009-11-06  -65145   61.13
  11  2009-11-06  -65145   59.91
  12  2011-05-10   93910   62.10
  14  2009-05-29   13000   70.28
  15  2008-12-03   19423   39.72

目标结果 #

ID  Date        Col1     Score    SequenceNum
  13  2002-08-01  18221    60.75    1 
  13  2010-08-12  18448    65.33    2
  11  2009-11-06  -65145   61.13    2
  11  2009-11-06  -65145   59.91    1
  12  2011-05-10   93910   62.10    1
  14  2008-12-03   19423   39.72    2
  14  2009-05-29   13000   70.28    1

规则说明 #

• 按ID分组处理
• 同一ID内Col1值不同时，按Col1升序生成序列
• 同一ID内Col1值相同时，按Score升序生成序列

Python Pandas 实现 #

通过分组+排序后分配序号的方式实现：

import pandas as pd

# 构造数据集
data = [
    [13, '2002-08-01', 18221, 60.75],
    [13, '2010-08-12', 18448, 65.33],
    [11, '2009-11-06', -65145, 61.13],
    [11, '2009-11-06', -65145, 59.91],
    [12, '2011-05-10', 93910, 62.10],
    [14, '2009-05-29', 13000, 70.28],
    [15, '2008-12-03', 19423, 39.72]
]
df = pd.DataFrame(data, columns=['ID', 'Date', 'Col1', 'Score'])

# 生成SequenceNum
df['SequenceNum'] = df.groupby('ID').apply(
    lambda x: x.sort_values(['Col1', 'Score']).reset_index(drop=True).index + 1
).reset_index(drop=True)

# 输出结果
print(df.sort_values('ID'))

逻辑：按ID分组后，每组内先按Col1升序、再按Score升序排序，然后给排序后的行分配从1开始的连续序号。

SQL 实现 #

使用窗口函数直接生成序列：

SELECT 
    ID,
    Date,
    Col1,
    Score,
    ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Col1 ASC, Score ASC) AS SequenceNum
FROM 
    your_table_name
ORDER BY 
    ID;

逻辑：PARTITION BY ID实现按ID分组，ORDER BY Col1 ASC, Score ASC指定排序规则，ROW_NUMBER()为每组内的行生成符合要求的序号。

内容的提问来源于stack exchange，提问作者Ahir Bhairav Orai