R - Reducing a matrix-CodePudding

I have a square matrix that is like:

A <- c("111","111","111","112","112","113")
B <- c(100,10,20,NA,NA,10)
C <- c(10,20,40,NA,10,20)
D <- c(10,20,NA,NA,40,200)
E <- c(20,20,40,10,10,20)
F <- c(NA,NA,40,100,10,20)
G <- c(10,20,NA,30,10,20)

df <- data.frame(A,B,C,D,E,F,G)
names(df) <- c("Codes","111","111","111","112","112","113")

#   Codes 111 111 111 112 112 113
# 1   111 100  10  10  20  NA  10
# 2   111  10  20  20  20  NA  20
# 3   111  20  40  NA  40  40  NA
# 4   112  NA  NA  NA  10 100  30
# 5   112  NA  10  40  10  10  10
# 6   113  10  20 200  20  20  20

I want to reduce it so that observations with the same row and column names are summed up. So I want to end up with:

#   Codes 111 112 113
# 1   111 230 120  30
# 2   112  50 130  40
# 3   113 230  40  20

I tried to first combine the rows with the same "Codes" number, but I was having a lot of trouble.

CodePudding user response：

In tidyverse

library(tidyverse)

df %>%
   pivot_longer(-Codes, values_drop_na = TRUE) %>%
   group_by(Codes, name) %>%
   summarise(value = sum(value), .groups = 'drop')%>%
   pivot_wider()
# A tibble: 3 x 4
  Codes `111` `112` `113`
  <chr> <dbl> <dbl> <dbl>
1 111     230   120    30
2 112      50   130    40
3 113     230    40    20

One way in base R:

tapply(unlist(df[-1]), list(names(df)[-1][col(df[-1])], df[,1][row(df[-1])]), sum, na.rm = TRUE)
    111 112 113
111 230  50 230
112 120 130  40
113  30  40  20

Note that this can be simplified as denoted by @thelatemail to

 grp <- expand.grid(df$Codes, names(df)[-1])
 tapply(unlist(df[-1]), grp, FUN=sum, na.rm=TRUE)

You can also use `xtabs:

xtabs(vals~., na.omit(cbind(grp, vals = unlist(df[-1]))))
     Var2
Var1  111 112 113
  111 230 120  30
  112  50 130  40
  113 230  40  20