不同长度DataFrame条件匹配：按双字段匹配填充percCor

现有两个行数不同的DataFrame：df1（147200行）、df2（510000行）。需按Listener和Trial两个字段精准匹配，将df2中的percCor值填充至df1对应的percCor列。

示例数据集 #

df1 <- data.frame(Listener = c("01JA","01JA","02JK","02JK", "03GL", "03GL"), 
                  Trial = c("Trial 1", "Trial 2", "Trial 1", "Trial 2", "Trial 1", "Trial 2"),
                  Pupil = c(1,2,4,3,2,6), 
                  Time = c(20, 40, 20, 40, 20, 40), 
                  percCor = c(NA, NA, NA, NA, NA, NA))

df2 <- data.frame(Listener = c("01JA","01JA","01JA","01JA", "01JA", "02JK","02JK","02JK","02JK", "02JK", "03GL", "03GL","03GL", "03GL", "03GL"), 
                  Trial = c("Trial 1", "Trial 1", "Trial 2", "Trial 2", "Trial 3","Trial 1", "Trial 1", "Trial 2", "Trial 2", "Trial 3","Trial 1", "Trial 1", "Trial 2", "Trial 2", "Trial 3"),
                  Pupil = c(1,1,1,2,4,3,2,6,2,5,6,2,4,5,6), 
                  Time = c(50, 100, 150, 50, 100, 150,50, 100, 150, 50, 100, 150, 50, 100, 150), 
                  percCor = c(100, 100, 20, 20, 60, 60, 60, 30, 30, 40, 60, 60, 40, 40, 55))

期望结果 #

dfans <- data.frame(Listener = c("01JA","01JA","02JK","02JK", "03GL", "03GL"), 
                    Trial = c("Trial 1", "Trial 2", "Trial 1", "Trial 2", "Trial 1", "Trial 2"),
                    Pupil = c(1,2,4,3,2,6), 
                    Time = c(20, 40, 20, 40, 20, 40), 
                    percCor = c(100, 20, 60, 30, 60, 40))

已尝试的问题方法 #

# 方法1：merge连接，内存不足
mdf <- merge(x=df1,y=df2, by.x=c("Listener","Trial"), by.y=c("Listener","Trial"))

# 方法2：dplyr左连接，内存不足
mdf<-left_join(df1, df2, by= c('Listener','Trial'))%>%
   mutate(percCor=percCor.y)%>%
   select(-c(percCor.x,percCor.y))

# 方法3：无连接键的左连接，无法正确匹配
mdf <-left_join(df1, df2)

# 方法4：右连接，保留了不必要的试验数据
mdf <-right_join(df1, df2)

# 方法5：循环匹配Listener，未同时匹配Trial且速度极慢
for(id in 1:nrow(df2)){
  df1$percCor[df1$Listener %in% df2$Listener[id]] <- df2$percCor[id]
}

# 方法6：循环匹配Listener和Trial，逻辑错误且速度极慢
for(id in 1:nrow(df2)){
  df1$percCor[df1$Listener %in% df2$Listener[id] & df1$Trial %in% df2$Trial] <- df2$percCor[id]
}

高效解决方案 #

方案1：dplyr去重后连接 #

问题核心是df2中同一Listener+Trial组合存在重复行，直接连接会导致数据膨胀、内存溢出。先对df2去重，保留每个组合的唯一percCor值（示例中同一组合的percCor值一致），再进行左连接：

library(dplyr)

# 对df2去重，仅保留Listener、Trial、percCor的唯一组合
df2_clean <- df2 %>% 
  distinct(Listener, Trial, percCor)

# 左连接并替换percCor列
df_result <- df1 %>%
  left_join(df2_clean, by = c("Listener", "Trial")) %>%
  # 替换原NA的percCor为匹配到的值
  mutate(percCor = percCor.y) %>%
  select(-percCor.y)

方案2：data.table高效匹配（适合大数据） #

data.table在处理几十万行数据时，内存效率和速度远高于基础dplyr：

library(data.table)

# 转换为data.table格式
setDT(df1)
setDT(df2)

# 对df2去重，保留每个Listener+Trial的唯一percCor
df2_clean <- unique(df2, by = c("Listener", "Trial"))

# 按匹配条件直接填充df1的percCor列
df1[df2_clean, on = .(Listener, Trial), percCor := i.percCor]

# 转换回data.frame（可选）
df_result <- as.data.frame(df1)

两种方案都能避免数据膨胀，解决内存不足问题，同时保证匹配逻辑准确，运行效率远高于循环方法。

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