1

This probably has been asked already, but I could not find it. I have a data set, where column names are numbers, and row names are sample names (see below). 

"599.773" "599.781" "599.789" "599.797" "599.804" "599.812" "599.82" "599.828" 
"A" 0 0 0 0 0 2 1 4  
"B" 0 0 0 0 0 1 0 3  
"C" 0 0 0 0 2 1 0 1  
"D" 3 0 0 0 3 1 0 0

I want to bin the columns, say every 4 columns, by summation, and then name the new columns with the mean of the binned columns. For the above table I would end up with:

"599.785" "599.816" 
"A" 0 7 
"B" 0 4  
"C" 0 4  
"D" 3 4

The new column names, 599.785 and 599.816, are average of the column names that were binned. I think something like cut would work for a vector of numbers, but I am not sure how to implement it for large data frames. Thanks for any help!

user2554798
  • 29
  • 3

2 Answers2

0
colnames <- c("599.773", "599.781", "599.789", "599.797", 
              "599.804", "599.812" ,"599.82" ,"599.828" )
mat <- matrix(scan(), nrow=4, byrow=TRUE)
 0 0 0 0 0 2 1 4  
  0 0 0 0 0 1 0 3  
  0 0 0 0 2 1 0 1  
  3 0 0 0 3 1 0 0 

 colnames(mat)=colnames
 rownames(mat) = LETTERS[1:4]

 sRows <- function(mat, cols) rowSums(mat[, cols])
 sapply(1:(dim(mat)[2]/4), function(base) sRows(mat, base:(base+4)) )

  [,1] [,2]
A    0    2
B    0    1
C    2    3
D    6    4

accum <- sapply(1:(dim(mat)[2]/4), function(base) 
                      sRows(mat, base:(base+4)) )
colnames(accum) <- sapply(1:(dim(mat)[2]/4), 
                          function(base)   
                      mean(as.numeric(colnames(mat)[ base:(base+4)] )) )
accum
#-------
  599.7888 599.7966
A        0        2
B        0        1
C        2        3
D        6        4
IRTFM
  • 258,963
  • 21
  • 364
  • 487
0

First of all Using numeric values as columns names is not a good/standard habit.

Even I am here giving a solution as the desired OP.

## read data without checking names 
dt <- read.table(text='
"599.773" "599.781" "599.789" "599.797" "599.804" "599.812" "599.82" "599.828" 
"A" 0 0 0 0 0 2 1 4  
"B" 0 0 0 0 0 1 0 3  
"C" 0 0 0 0 2 1 0 1  
"D" 3 0 0 0 3 1 0 0',header=TRUE, check.names =FALSE)

cols <- as.numeric(colnames(dt))
## create a factor to groups columns
ff   <- rep(c(TRUE,FALSE),each=length(cols)/2)
## using tapply to group operations by ff 
vals <- do.call(cbind,tapply(cols,ff,
       function(x)
         rowSums(dt[,paste0(x)])))
nn <- tapply(cols,ff,mean)
## names columns with means
colnames(vals) <- nn[colnames(vals)]

vals
  599.816 599.785
A       7       0
B       4       0
C       4       0
D       4       3
agstudy
  • 119,832
  • 17
  • 199
  • 261