为R数据框添加列：按奇偶连续行比较值标记最小项

我有一个包含value列的R数据框NEWDAT，需要将行按(1,2)、(3,4)……连续两行一组的方式分组，每组内比较value值，为最小值所在行标记TRUE，最大值所在行标记FALSE。

数据构造代码 #

library(reshape2)
set.seed(199)
MB_RMSE_sd1 <-  runif(12, min = 0, max = 2)
TMB_RMSE_sd1 <- runif(12, min = 0, max = 2)
MB_RMSE_sd3 <-  runif(12, min = 2, max = 5)
TMB_RMSE_sd3 <- runif(12, min = 2, max = 5)
MB_RMSE_sd5 <- runif(12, min = 5, max = 10)
TMB_RMSE_sd5 <- runif(12, min = 5, max = 10)
MB_RMSE_sd10 <-  runif(12, min = 7, max = 16)
TMB_RMSE_sd10 <- runif(12, min = 7, max = 16)
MB_MAE_sd1 <-  runif(12, min = 0, max = 2)
TMB_MAE_sd1 <- runif(12, min = 0, max = 2)
MB_MAE_sd3 <-  runif(12, min = 2, max = 5)
TMB_MAE_sd3 <- runif(12, min = 2, max = 5)
MB_MAE_sd5 <-  runif(12, min = 5, max = 10)
TMB_MAE_sd5 <- runif(12, min = 5, max = 10)
MB_MAE_sd10 <-  runif(12, min = 7, max = 16)
TMB_MAE_sd10 <- runif(12, min = 7, max = 16)

ID <- rep(rep(c("N10_AR0.8", "N10_AR0.9", "N10_AR0.95", "N15_AR0.8", "N15_AR0.9", "N15_AR0.95", "N20_AR0.8", "N20_AR0.9", "N20_AR0.95", "N25_AR0.8", "N25_AR0.9", "N25_AR0.95"), 2), 1)
df1 <- data.frame(ID, MB_RMSE_sd1, TMB_MAE_sd1, MB_RMSE_sd3, TMB_MAE_sd3, MB_RMSE_sd5, TMB_MAE_sd5, MB_RMSE_sd10, TMB_MAE_sd10)
reshapp1 <- reshape2::melt(df1, id = "ID")

NEWDAT <- data.frame(value = reshapp1$value, year = reshapp1$ID, n = rep(rep(c("10", "15", "20", "25"), each = 3), 16), Colour = rep(rep(c("RMSE_MB", "RMSE_TMB", "MAE_MB", "MAE_TMB"), each = 12), 4), sd = rep(rep(c(1, 3, 5, 10), each = 48), 1),  phi = rep(rep(c("0.8", "0.9", "0.95"), 16), 4))

NEWDAT$sd <- with(NEWDAT, factor(sd, levels = sd, labels = paste("sd =", sd)))
NEWDAT$year <- factor(NEWDAT$year, levels = NEWDAT$year[1:12])
NEWDAT$n <- with(NEWDAT, factor(n, levels = n, labels = paste("n = ", n)))

head(NEWDAT)

数据预览 #

value       year       n  Colour     sd  phi
1 0.05624369  N10_AR0.8 n =  10 RMSE_MB sd = 1  0.8
2 1.32718190  N10_AR0.9 n =  10 RMSE_MB sd = 1  0.9
3 1.42121934 N10_AR0.95 n =  10 RMSE_MB sd = 1 0.95
4 0.56366171  N15_AR0.8 n =  15 RMSE_MB sd = 1  0.8
5 0.02666847  N15_AR0.9 n =  15 RMSE_MB sd = 1  0.9
6 0.48640038 N15_AR0.95 n =  15 RMSE_MB sd = 1 0.95

解决方案 #

方案1：按连续两行分组 #

使用dplyr生成每2行一组的分组标识，再分组判断最小值：

library(dplyr)

NEWDAT1 <- NEWDAT %>%
  # 生成每2行一组的分组因子
  mutate(group = gl(nrow(.), 2)) %>%
  group_by(group) %>%
  # 最小值标记为TRUE，其余为FALSE（每组仅2行，非最小即最大）
  mutate(Highlight = value == min(value)) %>%
  ungroup() %>%
  # 移除临时分组列
  select(-group)

head(NEWDAT1)

方案2：按业务逻辑分组（匹配你给出的期望输出） #

从数据结构来看，你可能实际需要按n、Colour、sd、phi分组（相同参数组合为一组），代码如下：

NEWDAT1 <- NEWDAT %>%
  group_by(n, Colour, sd, phi) %>%
  mutate(Highlight = value == min(value)) %>%
  ungroup()

head(NEWDAT1)

这个方案的输出会和你给出的示例结果一致：组内所有最小值行标记TRUE，其余标记FALSE。

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