divide columns using pattern in colnames

Question

Assuming I have the following simplified table which has dynamic columns a_x (where x is an index e.g 0, 1, 2, 3, 4...) and b_x respectively. The number of a columns is always equal to the number of b columns but the total number of columns can be dynamic (not always 3 a and 3 b). To make it clearer the following example depicts the structure of my data:

> d <- read.table(text = "10 20 25 0.3 0.23 0.34 
                          40 20 30 0.25 0.4 0.45")
> names(d) <- c("a_0", "a_1", "a_2", "b_0", "b_1", "b_2")
> d
   a_0 a_1 a_2  b_0  b_1  b_2
1  10  20  25   0.30 0.23 0.34
2  40  20  30   0.25 0.40 0.45

I would like to divide a columns with the corresponding b columns and save the results in new c columns. In order to do the divisions I use the transform() function (with hard-coded colnames) like this:

transform(d, c_0 = as.numeric(as.character(a_0)) / as.numeric(as.character(b_0)))

How can I do this step automatically using (probably) a pattern in colnames given the fact the number of columns of my input data is not always the same.

Any help would be appreciated

Answer 1

Here are several approaches. (1) and (1a) seem best but the others do show different approaches. They have the same column names and order as in the question except for (1a) and (2) but those could easily be fixed up if that were a problem. No packages are used except for (4a).

1) transform

ix <- grep("a", names(d))
cbind(d, setNames(d[ix] / d[-ix], sub("a", "c", names(d)[ix])))
##   a_0 a_1 a_2  b_0  b_1  b_2       c_0      c_1      c_2
## 1  10  20  25 0.30 0.23 0.34  33.33333 86.95652 73.52941
## 2  40  20  30 0.25 0.40 0.45 160.00000 50.00000 66.66667

1a) This is a variation of (1);

transform(d, c = setNames(d[ix], ix-1) / d[-ix])  # ix is from above
##   a_0 a_1 a_2  b_0  b_1  b_2       c.0      c.1      c.2
## 1  10  20  25 0.30 0.23 0.34  33.33333 86.95652 73.52941
## 2  40  20  30 0.25 0.40 0.45 160.00000 50.00000 66.66667

2) reshape Convert to long form, perform the division and convert back to wide form.

varying <- split(names(d), sub("_.*", "", names(d)))
long <- reshape(d, dir = "long", varying = varying, v.names = names(varying))
reshape(transform(long, c = a / b), dir = "wide", idvar = "id")[-1]
##     a.1  b.1       c.1 a.2  b.2      c.2 a.3  b.3      c.3
## 1.1  10 0.30  33.33333  20 0.23 86.95652  25 0.34 73.52941
## 2.1  40 0.25 160.00000  20 0.40 50.00000  30 0.45 66.66667

3) apply We can convert to a 3d array and then use apply.

nr <- nrow(d)
nc <- ncol(d)
cc <- apply(array(as.matrix(d), c(nr, nc / 2, 2)), 1:2, function(x) x[1] / x[2])
colnames(cc) <- paste("c", seq(0, length = ncol(cc)), sep = "_")
cbind(d, cc)
##   a_0 a_1 a_2  b_0  b_1  b_2       c_0      c_1      c_2
## 1  10  20  25 0.30 0.23 0.34  33.33333 86.95652 73.52941
## 2  40  20  30 0.25 0.40 0.45 160.00000 50.00000 66.66667

4) diff Transpose the log of d, take diffs and reverse the log transpose by taking exp transpose. Then cbind it to d. This solution assumes that all entries are strictly positive (which is the case in the question) so that we can take logs.

nc <- ncol(d)
cc <- t(exp(-diff(t(log(d)), nc/2)))
colnames(cc) <- paste("c", seq(0, length = ncol(cc)), sep = "_")
cbind(d, cc)
##   a_0 a_1 a_2  b_0  b_1  b_2       c_0      c_1      c_2
## 1  10  20  25 0.30 0.23 0.34  33.33333 86.95652 73.52941
## 2  40  20  30 0.25 0.40 0.45 160.00000 50.00000 66.66667

(4a) diff.zoo supports a geometric diff which performs a division rather than subtraction. (In the current version of zoo diff.zoo requires that the elements of the input be strictly positive but this restriction is lifted in the development version of zoo.)

library(zoo)

nc <- ncol(d)
cc <- 1 / t(diff(zoo(t(d)), nc/2, arith = FALSE))
colnames(cc) <- paste("c", seq(0, length = ncol(cc)), sep = "_")
cbind(d, cc)
##     a_0 a_1 a_2  b_0  b_1  b_2       c_0      c_1      c_2
## x.1  10  20  25 0.30 0.23 0.34  33.33333 86.95652 73.52941
## x.2  40  20  30 0.25 0.40 0.45 160.00000 50.00000 66.66667

Answer 2

We can remove everything after underscore from the names, split them and divide one-by-one, i.e.

Reduce(`/`, split.default(d, gsub('_.*', '', names(d))))
#        a_0      a_1      a_2
#1  33.33333 86.95652 73.52941
#2 160.00000 50.00000 66.66667