1

Coming from Sum the values according to labels in R.

I've been notified that working with 2 dimensional tables is rather significantly different with 1 dimensional ones, like:

     a    a,b    a,b,c    c
 d   5     2       1      2
d,e  2     1       1      1

And we want to achieve:

     a    b    c
d    12   5    5
e    4    2    2

So how can this be achieved using R?

Community
  • 1
  • 1
lkn2993
  • 566
  • 7
  • 26

1 Answers1

1

A little bit convoluted, but it should work :

m <- as.matrix(data.frame('a'=c(5,2),'a,b'=c(2,1),
                          'a,b,c'=c(1:1),'c'=c(2,1),
                          check.names = FALSE,row.names=c('d','d,e')))
colNamesSplits <- strsplit(colnames(m),',')
rowNamesSplits <- strsplit(rownames(m),',')

colNms <- unique(unlist(colNamesSplits))
rowNms <- unique(unlist(rowNamesSplits))

colIdxs <- unlist(sapply(1:length(colNamesSplits),
                         function(i) rep.int(i,length(colNamesSplits[[i]]))))
rowIdxs <- unlist(sapply(1:length(rowNamesSplits),
                         function(i) rep.int(i,length(rowNamesSplits[[i]]))))
colIdxsMapped <- unlist(sapply(colNamesSplits, function(n) match(n,colNms)))
rowIdxsMapped <- unlist(sapply(rowNamesSplits, function(n) match(n,rowNms)))

# let's create the fully expanded matrix
expanded <- as.matrix(m[rowIdxs,colIdxs])
rownames(expanded) <- rowNms[rowIdxsMapped]
colnames(expanded) <- colNms[colIdxsMapped]

# aggregate expanded by cols :
expanded <- do.call(cbind,lapply(split(1:ncol(expanded),colnames(expanded)),
                 function(ii) rowSums(expanded[,ii,drop=FALSE])))
# aggregate expanded by rows :
expanded <- do.call(rbind,lapply(split(1:nrow(expanded),rownames(expanded)),
                 function(ii) colSums(expanded[ii,,drop=FALSE])))

> expanded
   a b c
d 12 5 5
e  4 2 2
digEmAll
  • 56,430
  • 9
  • 115
  • 140