纵向队列数据：将SurveyYear转换为个人SurveyTime调查次数序号

我有一组队列形式招募的受访者纵向数据，包含受访者ID（PersonID）、调查年份（SurveyYear）及其他调查数据。现在需要新增一列SurveyTime，标记每位受访者是第1次、第2次……第N次参与调查（按调查年份从早到晚排序）。

原始数据 #

目标数据（新增SurveyTime列） #

解决方案 #

1. Python (Pandas) #

通过groupby按受访者ID分组，对调查年份排序后生成连续序号：

import pandas as pd

# 假设数据已加载至DataFrame df
df['SurveyTime'] = df.groupby('PersonID')['SurveyYear'].rank(method='first', ascending=True).astype(int)

# 若需保留原始行顺序，可先保存索引再恢复
df['orig_idx'] = df.index
df_sorted = df.sort_values(['PersonID', 'SurveyYear'])
df_sorted['SurveyTime'] = df_sorted.groupby('PersonID').cumcount() + 1
df = df_sorted.sort_values('orig_idx').drop('orig_idx', axis=1)

2. SQL #

利用窗口函数ROW_NUMBER()实现分组排序计数：

SELECT 
    PersonID,
    SurveyYear,
    ROW_NUMBER() OVER (PARTITION BY PersonID ORDER BY SurveyYear ASC) AS SurveyTime,
    SurveyQ1Rating,
    SurveyQ2Rating,
    Gender
FROM your_table_name;

3. R (dplyr) #

通过分组后调用row_number()生成序号：

library(dplyr)

df <- df %>%
    group_by(PersonID) %>%
    mutate(SurveyTime = row_number(SurveyYear)) %>%
    ungroup()

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