R: how do you make row sums based on grouping from other variables?

Question

Here is an example of data:

df <- data.frame("ID1" = c("A","A","B","C"), 
            "Wt1" = c(0.8,0.6,0.4,0.5),
            "ID2" = c("B","A","C","B"),
            "Wt2" = c(0.1,0.4,0.5,0.5),
            "ID3" = c("C",NA,"C",NA), 
            "Wt3" = c(0.1,NA,0.1,NA))

And I would like to create columns (vote) in a dataframe, which is based on argmax wt from groups ID1, ID2, ID3. For example, in row 3 of the example data, the sum wt for "B" is 0.4 and the sum wt for "C" is 0.6, so vote = "C".

So, the result will be similar to

  ID1 Wt1 ID2 Wt2  ID3 Wt3 vote
1   A 0.8   B 0.1    C 0.1    A
2   A 0.6   A 0.4 <NA>  NA    A
3   B 0.4   C 0.5    C 0.1    C
4   C 0.5   B 0.5 <NA>  NA    C

In the case of binding (line 4 in the example), simply select any of the identifier values. Can anyone suggest a solution?

Answer 1

Firstly, it is very difficult to manipulate tables formatted in this way. This is not your desired result, but I am afraid that you may go further down the road.

, .

df$obs <- 1:nrow(df)

  df1 <- do.call("rbind",lapply(seq(1,6,2),function(x) {df <- df[,c(x: (x+1),7)]; 
colnames(df) <- c("ID","Wt","obs"); df}))

data.frame , data.table.

dt <- as.data.table(df1)

obs ID

dt[,total:=sum(Wt,na.rm=TRUE),.(obs,ID)]

.

dt[,vote:=.SD[which.max(total)],obs]

#dt
#    ID  Wt obs total vote
# 1:  A 0.8   1   0.8    A
# 2:  A 0.6   2   1.0    A
# 3:  B 0.4   3   0.4    C
# 4:  C 0.5   4   0.5    C
# 5:  B 0.1   1   0.1    A
# 6:  A 0.4   2   1.0    A
# 7:  C 0.5   3   0.6    C
# 8:  B 0.5   4   0.5    C
# 9:  C 0.1   1   0.1    A
# 10: NA  NA   2   0.0    A
# 11:  C 0.1   3   0.6    C
# 12: NA  NA   4   0.0    C