如何为多ID季度数据集生成新医生标识变量（未出现于前4季度）

问题描述 #

现有一份覆盖三年的个体级季度数据集，包含ID、Quarter、Doctor_Name字段，多数个体每季度对应多位医生。需生成New_Doctor变量：

• 前4个Quarter的New_Doctor字段为空
• 从第5个Quarter开始，若某Doctor_Name未出现在该ID对应的前4个Quarter中，则标记为1，否则为0

输入样例（单个ID） #

| ID | Quarter | Doctor_Name |
|----|---------|-------------|
| 1  |    1    | Dr. Smith   |
| 1  |    1    | Dr. Brown   |
| 1  |    1    | Dr. Smith   |
| 1  |    2    | Dr. Patel   |
| 1  |    2    | Dr. Garcia  |
| 1  |    2    | Dr. Garcia  |
| 1  |    3    | Dr. Kim     |
| 1  |    3    | Dr. Brown   |
| 1  |    3    | Dr. Patel   |
| 1  |    4    | Dr. Patel   |
| 1  |    4    | Dr. Craig   |
| 1  |    4    | Dr. Craig   |
| 1  |    5    | Dr. Brown   |
| 1  |    5    | Dr. Lee     |
| 1  |    5    | Dr. Kim     |
| 1  |    6    | Dr. Patel   |
| 1  |    6    | Dr. Smith   |
| 1  |    6    | Dr. Smith   |
| 1  |    7    | Dr. Garcia  |
| 1  |    7    | Dr. Smith   |
| 1  |    7    | Dr. Kim     |
| 1  |    8    | Dr. Lee     |
| 1  |    8    | Dr. Brown   |
| 1  |    8    | Dr. Smith   |

期望输出样例 #

| ID | Quarter | Doctor_Name | New_Doctor    |
|----|---------|-------------|---------------|
| 1  |    1    | Dr. Smith   |               |
| 1  |    1    | Dr. Brown   |               |
| 1  |    1    | Dr. Smith   |               |
| 1  |    2    | Dr. Patel   |               |
| 1  |    2    | Dr. Garcia  |               |
| 1  |    2    | Dr. Garcia  |               |
| 1  |    3    | Dr. Kim     |               |
| 1  |    3    | Dr. Brown   |               |
| 1  |    3    | Dr. Patel   |               |
| 1  |    4    | Dr. Patel   |               |
| 1  |    4    | Dr. Craig   |               |
| 1  |    4    | Dr. Craig   |               |
| 1  |    5    | Dr. Brown   |       0       |
| 1  |    5    | Dr. Lee     |       1       |
| 1  |    5    | Dr. Kim     |       0       |
| 1  |    6    | Dr. Patel   |       0       |
| 1  |    6    | Dr. Smith   |       1       |
| 1  |    6    | Dr. Smith   |       1       |
| 1  |    7    | Dr. Garcia  |       1       |
| 1  |    7    | Dr. Smith   |       0       |
| 1  |    7    | Dr. Kim     |       0       |
| 1  |    8    | Dr. Lee     |       0       |
| 1  |    8    | Dr. Brown   |       0       |
| 1  |    8    | Dr. Smith   |       0       |

当数据集包含多个ID时，可通过以下几种主流工具实现需求：

解决方案 #

1. SQL实现 #

核心思路是先按ID分组提取前4季度的医生名单，再关联回原表做存在性判断：

WITH doctor_history AS (
    SELECT 
        ID,
        DISTINCT Doctor_Name AS past_doctor
    FROM 
        your_table
    WHERE 
        Quarter <= 4
)
SELECT 
    t.ID,
    t.Quarter,
    t.Doctor_Name,
    CASE 
        WHEN t.Quarter <= 4 THEN NULL
        WHEN dh.past_doctor IS NULL THEN 1
        ELSE 0
    END AS New_Doctor
FROM 
    your_table t
LEFT JOIN 
    doctor_history dh ON t.ID = dh.ID AND t.Doctor_Name = dh.past_doctor
ORDER BY 
    t.ID, t.Quarter, t.Doctor_Name;

2. Python Pandas实现 #

通过分组获取每个ID的历史医生集合，再逐行应用判断逻辑：

import pandas as pd

# 读取数据
df = pd.read_csv("your_data.csv")

# 按ID分组，提取每个ID前4季度的唯一医生列表
past_doctors = df[df['Quarter'] <=4].groupby('ID')['Doctor_Name'].unique().to_dict()

# 定义判断函数
def mark_new_doctor(row):
    if row['Quarter'] <=4:
        return None
    return 1 if row['Doctor_Name'] not in past_doctors[row['ID']] else 0

# 生成新列并排序
df['New_Doctor'] = df.apply(mark_new_doctor, axis=1)
df = df.sort_values(by=['ID', 'Quarter', 'Doctor_Name'])

# 输出结果
print(df.to_markdown(index=False))

3. R语言实现 #

使用dplyr分组构建历史医生列表，再通过条件判断生成目标字段：

library(dplyr)

# 读取数据
df <- read.csv("your_data.csv")

# 生成每个ID的历史医生集合
past_doctors <- df %>%
  filter(Quarter <=4) %>%
  group_by(ID) %>%
  summarize(past_doctors = list(unique(Doctor_Name))) %>%
  ungroup()

# 关联数据并生成New_Doctor列
df <- df %>%
  left_join(past_doctors, by = "ID") %>%
  mutate(
    New_Doctor = case_when(
      Quarter <=4 ~ NA_integer_,
      !Doctor_Name %in% past_doctors ~ 1L,
      TRUE ~ 0L
    )
  ) %>%
  select(-past_doctors) %>%
  arrange(ID, Quarter, Doctor_Name)

# 输出结果
print(df)

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