如何在R中按ID分组替换指定变量的多时间点数据？

需求说明 #

按unqid分组，将ceramides对象指定的变量，用time=1（第1时间点）的数据替换time=2、3、4（其余时间点）的数据，使这些变量在每个分组内所有时间点数据重复；同时保留非ceramides变量的原始数据不变。

示例数据 #

structure(list(unqid = c(248, 248, 248, 248, 260, 260, 260, 260, 
3245, 3245, 3245, 3245, 3356, 3356, 3356, 3356, 5777, 5777, 5777, 
5777, 6670, 6670, 6670, 6670), time = c(1, 2, 3, 4, 1, 2, 3, 
4, 1, 2, 3, 4, 1, 2, 3, 4, 1, 2, 3, 4, 1, 2, 3, 4), risk_period = c("baseline", 
"0 to 2", "2 to 6", "6 to 12", "baseline", "0 to 2", "2 to 6", 
"6 to 12", "baseline", "0 to 2", "2 to 6", "6 to 12", "baseline", 
"0 to 2", "2 to 6", "6 to 12", "baseline", "0 to 2", "2 to 6", 
"6 to 12", "baseline", "0 to 2", "2 to 6", "6 to 12"), log_Cer.d18.0.24.1. = 
c(1.75591357030424, 
1.78808074202933, 1.78808074202933, 2.12541503739623, 1.82304843124874, 
1.84784683417316, 1.84784683417316, 1.660523253172, 1.71686160700412, 
1.53021484400339, 1.53021484400339, NA, 1.89959518683134, 2.13535376890766, 
2.13535376890766, 1.85969746880244, 1.79719659193748, NA, NA, 
1.58721201454406, 1.58269404870719, NA, NA, NA), log_Cer.d18.1.20.0. = 
c(2.0779936380825, 
1.91571413701583, 1.91571413701583, 2.37155913815626, 1.90173659734545, 
2.17760248999473, 2.17760248999473, 2.25151395301426, 1.92254402612409, 
2.13414350086059, 2.13414350086059, NA, 2.06112457583167, 2.11854093530707, 
2.11854093530707, 2.16685493654321, 1.78492915001842, NA, NA, 
1.88865763010095, 1.90477840908931, NA, NA, NA), log_GlcCer..d18.1.18.0. = 
c(1.37530467341568, 
1.26149055730786, 1.26149055730786, 2.20534847316661, 1.37179016097532, 
1.94465372915655, 1.94465372915655, 1.86255558272858, 1.39432569370228, 
1.80008523512444, 1.80008523512444, NA, 1.59814912304543, 1.65836069384085, 
1.65836069384085, 1.80053131813665, 1.30323667707535, NA, NA, 
1.66851539788016, 1.79824483492343, NA, NA, NA), log_GlcCer..d18.1.20.0. = 
c(1.68455467959852, 
1.68084892938971, 1.68084892938971, 2.83694873855279, 1.98660174822612, 
2.11687085647631, 2.11687085647631, 1.94521751150187, 1.94408113760572, 
2.3144321228024, 2.3144321228024, NA, 1.86220914942921, 2.08792541332488, 
2.08792541332488, 2.14127700040429, 1.25556058999258, NA, NA, 
1.75839585512294, 1.85392719235767, NA, NA, NA), log_SM.d18.0.22.0. = 
c(3.16608443039769, 
2.81581789967824, 2.81581789967824, 3.57758543823053, 3.05566675337641, 
2.71402626524511, 2.71402626524511, 2.74435051671931, 3.18268277797341, 
3.06067406280674, 3.06067406280674, NA, 3.00579040289776, 3.43150364762063, 
3.43150364762063, 2.92413057011273, 2.89178396996734, NA, NA, 
2.65395668019336, 2.61724748884637, NA, NA, NA), log_SM.d18.1.18.0. = 
c(4.12097226184649, 
3.97496147376645, 3.97496147376645, 4.39933846293122, 3.95478500357647, 
4.04453196517474, 4.04453196517474, 4.14121011613781, 3.89734856154778, 
4.00561959288859, 4.00561959288859, NA, 4.01511036918758, 4.00986157943819, 
4.00986157943819, 3.97023826969138, 3.86134148535091, NA, NA, 
3.77303147344872, 4.06523949171878, NA, NA, NA), log_SM.d18.1.24.1. = 
c(4.80118834282449, 
4.58079754854406, 4.58079754854406, 5.29872341013633, 4.89353600842266, 
5.01659126290913, 5.01659126290913, 5.01117232938486, 4.87122149340715, 
4.81332745585807, 4.81332745585807, NA, 4.88235204875765, 4.92826803352429, 
4.92826803352429, 4.78283431218245, 4.3613226254187, NA, NA, 
4.58555011488179, 4.63520556565684, NA, NA, NA), cancer = c(0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 
0, 0)), row.names = c(917L, 918L, 919L, 920L, 3458L, 3459L, 3460L, 
3461L, 4286L, 4287L, 4288L, 4289L, 4290L, 4291L, 4292L, 4293L, 
4462L, 4463L, 4464L, 4465L, 4506L, 4507L, 4508L, 4509L), class = "data.frame")

已筛选目标变量的代码 #

## 找到第一个神经酰胺列的位置
cer.start <- which(colnames(long_data) == "log_Cer.d18.0.24.1."); cer.start # 注意：R中用两个"=="表示相等判断
## 找到最后一个神经酰胺列的位置
cer.stop <- which(colnames(long_data) == "log_SM.d18.1.24.1."); cer.stop
# 选取第一个到最后一个神经酰胺列之间的所有列名
ceramides <- colnames(long_data)[cer.start:cer.stop]

尝试过的错误写法 #

以下两种写法均无法实现需求：

long_data_cancer_ceramide_test<-
for (i in colnames(ceramides)) {
long_data_cancer_ceramide %>% 
group_by(unqid) %>% 
mutate("i" = i [time==1])
}

long_data_cancer_ceramide_test<-long_data_cancer_ceramide%>%
group_by(unqid)%>%
lapply(ceramides, function(x) mutate(x = x [time==1]))

可行解决方案 #

使用dplyr包的across()函数可以高效批量处理指定列，代码如下：

library(dplyr)

long_data_cancer_ceramide_test <- long_data_cancer_ceramide %>%
  group_by(unqid) %>%
  # 批量处理ceramides指定的所有列，用每个分组内time=1的值替换所有行
  mutate(across(all_of(ceramides), ~ .x[time == 1])) %>%
  ungroup() # 取消分组，恢复普通数据框格式

代码说明 #

• group_by(unqid)：按unqid分组，确保每个分组内的操作独立进行
• across(all_of(ceramides), ~ .x[time == 1])：
  • all_of(ceramides)：指定要处理的目标列（即之前筛选出的神经酰胺变量）
  • ~ .x[time == 1]：在每个分组中，取当前列time=1对应的数值，由于每个分组只有一个time=1的行，该数值会自动填充到分组内所有行，替换掉time=2、3、4的原始数据
• ungroup()：处理完成后取消分组，避免后续操作受分组状态影响

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