在R语言中依据常模数据新增cutoff分类列的技术咨询

我有一个长格式纵向数据集，包含约2800行、400名参与者，样本数据如下：

ID wave score sex age edu
1001 1 28 1 69 12
1001 2 27 1 70 12
1001 3 28 1 71 12
1001 4 26 1 72 12
1002 1 30 2 78 9
1002 3 30 2 80 9
1003 1 30 2 65 16
1003 2 30 2 66 16
1003 3 29 2 67 16
1003 4 28 2 68 16
1004 1 22 2 85 4
1005 1 20 2 60 9
1005 2 18 1 61 9
1006 1 22 1 74 9
1006 2 23 1 75 9
1006 3 25 1 76 9
1006 4 19 1 77 9

我想要新增一列cutoff，取值为Normal或Impaired，判断依据是结局变量score是否低于基于性别（Sex）、教育年限（Edu）、年龄（Age）划分的-1SD临界值。

现有代码的疑问 #

目前我手动对照Excel常模表写了以下代码，但不确定是否正确：

data$cutoff <- ifelse(data$sex==1 & data$age<70 & data$edu<3 & data$score<19.91, "Impaired", "Normal")
data$cutoff <- ifelse(data$sex==2 & data$age<70 & data$edu<3 & data$score<18.39, "Impaired", "Normal")

进阶需求：导入Excel常模表实现自动匹配 #

我还想知道是否可以直接导入存储常模的Excel文件，依据其中的临界值自动生成cutoff列，这样代码可以复用在后续研究中。

该Excel的结构如下（以60-69岁组为例）：

我已经生成了agecat和educat列，将每个参与者分配到了常模对应的年龄分组和教育分组中，希望能利用这两列匹配Excel中的临界值，自动生成cutoff列。

解答 #

1. 现有手动代码的问题

你的现有代码存在覆盖问题：第二行的ifelse会完全覆盖第一行的结果——也就是说只有符合sex==2 & age<70 & edu<3条件的行才会被正确分类，其他行（包括第一行中符合男性条件的行）都会被设为Normal。

要解决这个问题，你可以用嵌套ifelse，更推荐用dplyr包的case_when，可读性更强：

# 方案1：嵌套ifelse
data$cutoff <- ifelse(data$sex==1 & data$age<70 & data$edu<3 & data$score<19.91, 
                      "Impaired",
                      ifelse(data$sex==2 & data$age<70 & data$edu<3 & data$score<18.39, 
                             "Impaired", 
                             "Normal"))

# 方案2：用dplyr的case_when（更推荐）
library(dplyr)
data <- data %>%
  mutate(cutoff = case_when(
    sex==1 & age<70 & edu<3 & score<19.91 ~ "Impaired",
    sex==2 & age<70 & edu<3 & score<18.39 ~ "Impaired",
    TRUE ~ "Normal"  # 所有不满足上述条件的情况都归为Normal
  ))

2. 导入Excel常模表实现自动匹配

这是更高效且可复用的方案，步骤如下：

步骤1：整理Excel常模表为tidy格式

首先需要把Excel中的宽表转换为适合合并的长表（tidy data）。假设你的Excel文件名为norms.xlsx，可以用readxl包读取，再用tidyr和dplyr整理：

library(readxl)
library(tidyr)
library(dplyr)

# 读取Excel表（假设数据在第一个工作表）
norms_raw <- read_excel("norms.xlsx")

# 整理为tidy格式：每行对应一个（年龄组、性别、教育组）的临界值
norms_tidy <- norms_raw %>%
  # 填充年龄组列，让每个分组行都有对应的年龄组标签
  fill(`Age组`, .direction = "down") %>%
  # 只保留我们需要的-1.5SD临界值行
  filter(维度 == "-1.5SD") %>%
  # 把宽表转长表，拆分性别和教育组信息
  pivot_longer(cols = -c(`Age组`, 维度), 
               names_to = c("sex", "educat"), 
               names_sep = "_",  # 这里需要根据你Excel列名的实际分隔符调整，比如列名是"男性_0-3"就用"_"
               values_to = "cutoff_score") %>%
  # 把性别转换为和原数据集一致的数字编码（1=男性，2=女性）
  mutate(sex = case_when(
    sex == "男性" ~ 1,
    sex == "女性" ~ 2
  )) %>%
  # 保留需要的匹配列
  select(agecat = `Age组`, sex, educat, cutoff_score)

注意：如果Excel中列名的格式不同（比如男性的教育组列直接叫"0-3"），可能需要用pivot_longer的names_pattern参数来匹配，或者先手动调整Excel列名后再处理。

步骤2：合并数据集并生成cutoff列

现在你可以把整理好的常模表和原数据集合并，然后自动生成cutoff列：

# 根据agecat、sex、educat三个字段匹配常模临界值
data_merged <- data %>%
  left_join(norms_tidy, by = c("agecat", "sex", "educat")) %>%
  # 判断score是否低于临界值，生成cutoff列
  mutate(cutoff = case_when(
    score < cutoff_score ~ "Impaired",
    TRUE ~ "Normal"
  ))

这样一来，后续如果常模表更新或者有新的分组，你只需要更新Excel文件，代码不需要大改，完全可以复用。

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