Search code examples
rcbind

Match Columns with rows and subtract from columns

my data looks like:

     >dput(mea)
     >structure(list(mea = c(NA, NA, NA, NA, 7.5, 19.5)), .Names = "mea",
     row.names = c(NA,-6L), class = "data.frame")

and

     >dput(m1)
     >structure(list(t = c(5e-04, 0.001, 0.0015, 0.002, 0.0025, 0.003, 
     0.0035, 0.004, 0.0045, 0.005, 0.0055, 0.006, 0.0065, 0.007, 0.0075, 
     0.008, 0.0085, 0.009, 0.0095, 0.01, 0.0105, 0.011, 0.0115, 0.012, 
     0.0125, 0.013, 0.0135, 0.014, 0.0145, 0.015), a = c(21L, 13L, 
     17L, 20L, 19L, 9L, 11L, 16L, 28L, 10L, 14L, 12L, 15L, 27L, 30L, 
     6L, 29L, 22L, 24L, 5L, 25L, 18L, 26L, 1L, 3L, 4L, 23L, 8L, 7L, 
     2L), b = c(1, 1, 1, 1, 1, 4, 4, 4, 4, 1, 1, 1, 1, 1, 1, 1, 1, 
     1, 4, 4, 4, 4, 1, 1, 1, 1, 1, 1, 1, 1), c = c(19L, 25L, 14L, 
     18L, 3L, 4L, 5L, 6L, 8L, 20L, 17L, 13L, 28L, 2L, 29L, 15L, 26L, 
     12L, 24L, 16L, 9L, 1L, 22L, 27L, 21L, 23L, 11L, 10L, 7L, 30L), 
     `4` = c(NA, NA, NA, 18L, 3L, 4L, 5L, 6L, 8L, 20L, 17L, 13L, 
     NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
     NA, NA, NA), `17` = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, 
     NA, NA, NA, NA, NA, NA, NA, 26L, 12L, 24L, 16L, 9L, 1L, 22L, 
     27L, 21L, NA, NA, NA, NA, NA), `<NA>` = c(NA_integer_, NA_integer_, 
     NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
     NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
     NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
     NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
     NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
     NA_integer_, NA_integer_, NA_integer_)), class = "data.frame",
     row.names = c(NA,30L), .Names = c("t", "a", "b", "c", "4", "17", "<NA>"))

What I'm trying to do is to match the rownames of mea with the columnnames of m1 and then subtract the appropriate value of mea from the whole column of m1 (which matches the rowname of mea).

What I did is:

    mea1<-na.omit(mea)
    idx <- match(rownames(mea1) , 1:ncol(m1)) 
    m5 <- cbind((m1[,idx] - mea[idx,]), m1[,-idx])

I got this from the question asked here: https://stackoverflow.com/a/18077661/5681674

It does the trick, but the wrong way. It subtracts the second (last value) of mea from the first matched column of m1 and the first value of mea from the second matched column of m1. But it should be in the correct order. I read somewhere that cbind works from end to front, is that the problem here? And how can I solve it?

Thank you in advance!

Best regards, Chris

Solution

  • The issue is that you are trying to subtract a vector from a data.frame so the vector elements are getting recycled. It's not actually subtracting the elements in the "wrong" direction (i.e. 19.5 from column 1, and 7.5 from column 2). Instead, what's happening is 19.5 and 7.5 are being subtracted from each column in alternating order (because the elements are being recycled to fit the length of the column.

    For example, check out head((m1[,idx] - mea[idx,])) compared to head(m1[,idx]). You'll see that row 4 is -1.5 or 18 - 19.5 and row 5 is -4.5 or 3 - 7.5.

    To fix this you can use mapply instead:

    m5 <- cbind(mapply("-", m1[,idx], mea[idx,]), m1[,-idx])

    Now if we look at head(m5[,c(1,2)]) we see:

        4 17
1   NA NA
2   NA NA
3   NA NA
4 10.5 NA
5 -4.5 NA
6 -3.5 NA

    Or that 7.5 is being correctly subtracted from the entire first column. If you check the second column you can also see that 19.5 is being correctly subtracted from the entire column