I have the following dataset:crab_traps <- tibble(trap_id = 1:5, logger_1 = c(2, 3, 4, 5, 6), logger_2 = c(9, 7, 5, 3, 1), logger_3 = c(6, 5, 1, 7, 8)trap_id identifies a specific crab trap and each logger_* column is the distance to a water quality logger.
I want to create a new column (preferably using dplyr::mutate) to figure out which logger is closest to each trap.
Hopefully the new column would be something like closest_logger <- c("logger_1", "logger_1", "logger_3", "logger_2", "logger_2") (using this example data)
I have tried crab_traps <- crab_traps %>% rowwise() %>% mutate(closest_logger = min(logger_1, logger_2, logger_3)) which gives me the smallest measurement, but I want to get the name of the column that the smallest measurement came from.
Any help would be wonderful!
The most efficient solution would be using max.col :
crab_traps$closest_logger <- names(crab_traps)[max.col(-crab_traps[-1]) + 1]
If you want a dplyr solution you can try c_across with rowwise.
library(dplyr)
crab_traps %>%
rowwise() %>%
mutate(closest_logger = names(.)[which.min(c_across(starts_with('logger')))+1])
# trap_id logger_1 logger_2 logger_3 closest_logger
# <int> <dbl> <dbl> <dbl> <chr>
#1 1 2 9 6 logger_1
#2 2 3 7 5 logger_1
#3 3 4 5 1 logger_3
#4 4 5 3 7 logger_2
#5 5 6 1 8 logger_2
In both the cases, we add +1 because we are ignoring the first column while calculating minimum.