Sum row-wise values that are grouped by column name but keep all columns in R?

user_3447708 0
Viewed 70

Similar questions have been asked here and here, however, my question is slightly different from those questions. In the other questions, after they sum by the groups, they are removing the 'duplicate' columns. I want to keep the duplicate columns. For example, if I have a matrix that looks like this:

myMat <- matrix(NA, 3, 4)
colnames(myMat) <- c('x', 'y', 'x', 'y')
myMat[1,] <- c(1,2,0,1)
myMat[2,] <- c(2,4,3,5)
myMat[3,] <- c(3,6,1,7)

> myMat
     x y x y
[1,] 1 2 0 1
[2,] 2 4 3 5
[3,] 3 6 1 7

I want to row-wise sum, grouped by column name. In the links above, one of the answers suggest this:

> t(rowsum(t(myMat), group = colnames(myMat), na.rm = T))
     x  y
[1,] 1  3
[2,] 5  9
[3,] 4 13

But as you can see, they are combining the columns with the same name together and, as a result, reducing the number of columns. I want to sum the values, but still have the same columns. For example, my desired output would look like this:

     x  y x  y
[1,] 1  3 1  3
[2,] 5  9 5  9
[3,] 4 13 4 13

In the above, the rows (grouped by name) are summed... but I still have each individual column. Any suggestions as to how I could do this?

data-manipulation r matrix group-by
4 Answers

Use ave and apply:

t(apply(myMat, 1, \(x) ave(x, names(x), FUN = sum)))

     x  y x  y
[1,] 1  3 1  3
[2,] 5  9 5  9
[3,] 4 13 4 13
user_134606020

You could also just take your original solution and cbind it to itself

sums =  t(rowsum(t(myMat), group = colnames(myMat), na.rm = T))
cbind(sums, sums)

     x  y x  y
[1,] 1  3 1  3
[2,] 5  9 5  9
[3,] 4 13 4 13
user_44475400

You can try ave like below (with aids of col + row)

> ave(myMat,colnames(myMat)[col(myMat)], row(myMat), FUN = sum)
     x  y x  y
[1,] 1  3 1  3
[2,] 5  9 5  9
[3,] 4 13 4 13
user_121587570

A simple loop would achieve this:

for(i in unique(colnames(myMat))) {
  myMat[,colnames(myMat) == i] <- rowSums(myMat[,colnames(myMat) == i])
}

myMat
#>      x  y x  y
#> [1,] 1  3 1  3
#> [2,] 5  9 5  9
#> [3,] 4 13 4 13
user_125003150
Related