Is there a quick method for calculating the result of multiple column multiplication, but with different proportion according to NA values?

user_12924821 0
Viewed 57

I am trying to get the result of the multiplication of columns A, B and C with different proportions according to NA values. Here is a reproducible example :

data <- data.frame(A = c(NA, 3, NA, 5),
               B = c(NA, NA, 10, 2),
               C = c(12, 14, 5, 18))

With the proportions :

proportion <- data.frame(id = c("A", "B", "C"),
                     proportion1 = c(1, NA, NA),
                     proportion2 = c(NA, 1, NA),
                     proportion3 = c(NA, NA, 1),
                     proportion4 = c(0.25, 0.75, NA),
                     proportion5 = c(0.4, NA, 0.6),
                     proportion6 = c(NA, 0.33, 0.67),
                     proportion7 = c(0.35, 0.2, 0.45))

I can get the result that I want with the following code. However, I can't find how to do it for a larger dataset (e.g. 5 or more initial groups).

library(dplyr)
data_results <- data %>% mutate(result = case_when(is.na(B) & is.na(C) ~ A,
                                               is.na(A) & is.na(C) ~ B,
                                               is.na(A) & is.na(B) ~ C,
                                               is.na(C) ~ 0.25 * A + 0.75 * B,
                                               is.na(B) ~ 0.4 * A + 0.6 * C,
                                               is.na(A) ~ 0.33 * B + 0.67 * C,
                                               TRUE ~ 0.35 * A + 0.2 * B + 0.45 * C))

Example : in data_results, line 3 corresponds to proportion6. So we get

0.33 * 10 + 0.67 * 5 = 6.65

Any help would be greatly appreciated. Thanks.

dplyr dataframe r
1 Answers

Here is a base R option

x <- apply(!is.na(data),1,function(v) toString(which(v,arr.ind = TRUE)))
y <- apply(!is.na(proportion[-1]),2,function(v) toString(which(v,arr.ind = TRUE)))
data$results <- colSums(t(data)*proportion[1+match(x,y)],na.rm = TRUE)

which gives

> data
   A  B  C results
1 NA NA 12   12.00
2  3 NA 14    9.60
3 NA 10  5    6.65
4  5  2 18   10.25
user_121587570
Related