在R中基于多条件匹配并填充数据框Speed列的技术问题

模拟数据生成 #

set.seed(123)
# 生成DF11
DF11 <- data.frame(
  Lat = sample(30:35, 20, replace = TRUE),
  Long = sample(110:115, 20, replace = TRUE),
  Day = sample(1:5, 20, replace = TRUE),
  Month = 10,
  Hour_Minute = sample(c("08:00", "12:00", "18:00"), 20, replace = TRUE),
  Speed = sample(c(0, 30:60), 20, replace = TRUE)
)

# 生成DF12
DF12 <- data.frame(
  Lat = sample(30:35, 15, replace = TRUE),
  Long = sample(110:115, 15, replace = TRUE),
  Day = sample(1:5, 15, replace = TRUE),
  Month = 10,
  Hour_Minute = sample(c("08:00", "12:00", "18:00"), 15, replace = TRUE),
  Real_Speed = sample(30:60, 15, replace = TRUE)
)

步骤1：多条件匹配替换Speed的0值 #

先确保匹配列类型一致（比如Hour_Minute如果是因子转成字符），再用左连接匹配后针对性替换0值，避免NA覆盖有效数据：

library(dplyr)

# 统一匹配列类型（按需执行）
DF11$Hour_Minute <- as.character(DF11$Hour_Minute)
DF12$Hour_Minute <- as.character(DF12$Hour_Minute)

# 左连接+条件替换
DF11_updated <- DF11 %>%
  left_join(DF12, by = c("Lat", "Long", "Day", "Month", "Hour_Minute")) %>%
  mutate(
    Speed = case_when(
      Speed == 0 & !is.na(Real_Speed) ~ Real_Speed,
      TRUE ~ Speed
    )
  ) %>%
  select(-Real_Speed)

步骤2：填充剩余0值 #

按Day和Hour_Minute分组，从同组非0的Speed值中随机抽样填充：

DF11_final <- DF11_updated %>%
  group_by(Day, Hour_Minute) %>%
  mutate(
    # 收集当前组所有非0 Speed值
    non_zero_speeds = list(Speed[Speed != 0]),
    # 对0值行随机抽样填充，组内无有效数据则保留0
    Speed = ifelse(
      Speed == 0,
      if(length(non_zero_speeds[[1]]) > 0) sample(non_zero_speeds[[1]], size = n(), replace = TRUE) else Speed,
      Speed
    )
  ) %>%
  ungroup() %>%
  select(-non_zero_speeds)

常见问题说明 #

之前用merge出错并引入NA，大概率是以下原因：

• 匹配列类型不一致（比如因子 vs 字符），导致匹配失败生成NA
• 直接用merge后未做条件判断，直接覆盖了原有非0的Speed值
• 未处理匹配后Real_Speed为NA的情况，误把NA代入替换

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