I have a dataset that looks like this:
library(dplyr)
Data <- tibble(Code = letters[1:6],
Min_0 = c(12.3, NA, 1.1, NA, 0, NA),
Min_1 = c(3.6, NA, 12.4, 1.7, 15.6, NA),
Min_2 = c(45.6, NA, 2.4, 28.9, 32.7, NA),
Min_3 = c(2.3, NA, NA, NA, NA, NA),
Min_4 = c(52.4, NA, 23.6, 4.6, 0, NA),
Min_5 = c(0, NA, 5.7, NA, 0.1, NA))
For every row I want to add a column with the minimum value from columns Min_1 to Min_5 or NA if that row only contains NAs. I would also like to add another column that contains the last digit of the column name that has the minimum value for that row.
I think I have a solution to my first question by adapting an answer from this question (in my case I only want to ignore NAs not NAs and zeros).
Min_Function <- function(x, f, ...) ifelse(all(is.na(x)), NA,
ifelse(all(is.na(x)),
0, f(x[x >= 0], na.rm = TRUE, ...)))
Data %>%
rowwise() %>%
mutate(Min_Time = Min_Function(c_across(starts_with("Min_") & -ends_with("0")), min))
My real data has many more columns which is why I need to use the starts_with and ends_with to select the columns I am interested in.
However, I don't know how I get an output like this for the second part of my question:
Data %>% mutate(Min_ID = c(5, NA, 2, 1, 4, NA))
Data %>%
mutate(Min_Time = max.col(-across(starts_with("Min_") & -ends_with("0"),
~replace_na(.x, Inf)), 'first')*
NA^if_all(starts_with("Min_") & -ends_with("0"), is.na))
# A tibble: 6 × 8
Code Min_0 Min_1 Min_2 Min_3 Min_4 Min_5 Min_Time
<chr> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 a 12.3 3.6 45.6 2.3 52.4 0 5
2 b NA NA NA NA NA NA NA
3 c 1.1 12.4 2.4 NA 23.6 5.7 2
4 d NA 1.7 28.9 NA 4.6 NA 1
5 e 0 15.6 32.7 NA 0 0.1 4
6 f NA NA NA NA NA NA NA