I have this simple data frame,
var_1 <- c(10, 5, 6, 0)
var_2 <- c(0, 0, 3, 0)
var_3 <- c(2, 0, 9, 0)
df <- data.frame(var_1, var_2, var_3)
df
> df
var_1 var_2 var_3
1 10 0 2
2 5 0 0
3 6 3 9
4 0 0 0
I want to create a column gathering information from the columns named with var_. In the first row, I have 10 under var_1 and 2 under var_3. So the entry in the resulting column should be `var_1 (10), var_3(2) and so on. The rows with all zeros should be recorded as NA. The resulting data frame should look like this following:
var_1 var_2 var_3 res
1 10 0 2 var_1 (10), var_3 (2)
2 5 0 0 var_1 (5)
3 6 3 9 Var_2 (3)
4 0 0 0 <NA>
I can somehow gather the variable names but cannot get the values with it. Any idea how to achieve that res column?
res <- mapply(sprintf, names(df), df, fmt = "%s (%d)")
res[df == 0] <- NA
res <- apply(res, 1, \(x) paste(na.omit(x), collapse = ", "))
res[res == ""] <- NA
df$res <- res
df
# var_1 var_2 var_3 res
#1 10 0 2 var_1 (10), var_3 (2)
#2 5 0 0 var_1 (5)
#3 6 3 9 var_1 (6), var_2 (3), var_3 (9)
#4 0 0 0 <NA>