How do I compute the average per row of multiple numeric columns

Question

I have 8 age categories as 8 separate columns. Each column has a value between 1 and 3. I want to compute a new column that holds the average age per row.

This is my data:

structure(list(`2.5` = c(0, 0, 0, 1, 1, 2, 1, 2, 0, 0, 1, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 2, 0, 
0, 2, 0, 1, 0, 1, 0, 1, 0, 0, 0, 1, 0, 0, 0, 1, 2, 0, 0, 0, 0, 
0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1, 2, 1, 0, 0, 2, 0, 0, 0, 
0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 2, 0, 2, 2, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 2, 0, 2, 0, 1, 0, 0, 0, 2, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
1, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 1, 0, 1, 0, 0, 
0, 1, 0, 0, 0, 0, 2, 1, 0, 0, 0, 0, 1, 0, 0, 0, 0, 2, 0, 0, 0, 
0, 0, 1, 0, 1, 0, 0, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0, 3, 
0, 0, 1), `9` = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 1, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 2, 0, 0, 0, 
1, 0, 0, 0, 0, 2, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 1, 0, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 2, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 1, 0, 0, 0, 0, 2, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 2, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 3, 0, 1, 
0, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 3, 0, 0, 0, 0, 0, 0, 2, 0, 
2, 0, 3, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 
1, 0, 0, 0, 1, 0, 0, 0, 0, 2, 0, 1, 1, 2, 0, 0, 0, 0, 0, 0, 1
), `15.5` = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 
0, 0, 0, 0, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2, 0, 0, 
0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 3, 0, 2, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 1, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 
0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0), 
    `21.5` = c(0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 3, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0), `29.5` = c(0, 
    1, 2, 0, 1, 0, 0, 0, 0, 2, 0, 0, 0, 2, 0, 0, 0, 0, 1, 0, 
    0, 0, 0, 0, 0, 0, 1, 2, 1, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 2, 0, 0, 1, 0, 1, 0, 2, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 2, 0, 0, 0, 0, 
    0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 1, 0, 0, 0, 
    0, 0, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2, 0, 0, 0, 0, 0, 
    0, 0, 0, 1, 0, 0, 2, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 1, 2, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 2, 
    0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 0, 0, 
    0, 1, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0), `42` = c(0, 0, 0, 
    2, 1, 2, 2, 2, 0, 0, 2, 0, 0, 0, 2, 0, 0, 0, 1, 1, 0, 2, 
    0, 0, 1, 0, 1, 0, 1, 1, 2, 1, 2, 0, 2, 0, 1, 1, 2, 0, 2, 
    1, 0, 0, 0, 0, 2, 1, 2, 1, 0, 0, 0, 1, 0, 0, 0, 0, 2, 0, 
    2, 0, 1, 0, 0, 0, 2, 2, 2, 1, 0, 2, 0, 0, 1, 0, 0, 2, 0, 
    2, 1, 1, 0, 0, 2, 0, 0, 0, 2, 1, 1, 1, 1, 0, 1, 2, 2, 0, 
    0, 0, 0, 2, 0, 2, 0, 0, 2, 2, 2, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 1, 0, 2, 0, 2, 1, 0, 1, 1, 2, 0, 0, 2, 1, 2, 2, 2, 0, 
    1, 0, 1, 0, 2, 2, 2, 1, 0, 0, 2, 0, 0, 0, 0, 2, 0, 2, 2, 
    2, 2, 1, 2, 0, 2, 0, 2, 0, 2, 2, 1, 0, 0, 0, 2, 2, 0, 2, 
    0, 0, 2, 2, 0, 0, 0, 0, 2, 1, 2, 0, 0, 1, 2, 0, 0, 0, 1, 
    1, 2, 2, 1, 0, 0, 0, 2, 1, 1, 2), `57` = c(0, 1, 0, 0, 0, 
    0, 0, 0, 2, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 1, 0, 0, 2, 1, 
    0, 0, 0, 0, 0, 0, 0, 1, 0, 1, 0, 1, 0, 0, 0, 2, 0, 0, 2, 
    0, 0, 2, 0, 0, 0, 0, 1, 0, 2, 0, 1, 0, 2, 2, 0, 0, 0, 0, 
    0, 0, 2, 0, 0, 0, 0, 1, 2, 0, 0, 1, 0, 2, 0, 0, 1, 0, 0, 
    0, 0, 2, 0, 0, 2, 0, 0, 0, 1, 1, 0, 2, 0, 0, 0, 2, 0, 1, 
    2, 0, 2, 0, 1, 1, 0, 0, 0, 2, 0, 0, 1, 2, 2, 2, 0, 2, 0, 
    0, 0, 0, 0, 1, 2, 0, 0, 0, 1, 1, 0, 1, 0, 0, 0, 2, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 2, 0, 1, 2, 2, 2, 0, 0, 0, 0, 0, 0, 
    0, 0, 2, 0, 1, 0, 2, 0, 0, 0, 2, 2, 0, 0, 0, 1, 0, 0, 1, 
    0, 0, 1, 1, 2, 0, 0, 1, 0, 0, 2, 0, 0, 0, 0, 0, 1, 0, 0, 
    0, 1, 2, 0, 1, 0, 1, 0, 0), `72` = c(2, 0, 0, 0, 1, 0, 0, 
    0, 0, 0, 0, 2, 0, 0, 0, 1, 1, 1, 0, 0, 1, 0, 0, 0, 0, 2, 
    0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 2, 0, 1, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 2, 0, 2, 0, 0, 0, 2, 
    0, 0, 2, 0, 2, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 1, 1, 0, 0, 0, 0, 0, 0, 0, 2, 0, 
    0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 2, 0, 0, 
    0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 
    0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 1, 0, 2, 1, 0, 0, 
    0, 1, 0, 2, 0, 0, 0, 1, 0, 0, 0, 2, 1, 0, 0, 0, 0, 1, 0, 
    0, 2, 1, 0, 0, 0, 0)), row.names = c(NA, -204L), class = c("data.table", 
"data.frame"))

Answer 1

Here is a base R one-liner where we multiply values in the columns of dataframe by its names, calculate the sum of column values and divide by its rowSums.

df$result <- colSums(t(df) * as.numeric(names(df)))/rowSums(df)

head(df)
#  2.5 9 15.5 21.5 29.5 42 57 72   result
#1   0 0    0    0    0  0  0  2 72.00000
#2   0 0    0    0    1  0  1  0 43.25000
#3   0 0    0    0    2  0  0  0 29.50000
#4   1 0    0    0    0  2  0  0 28.83333
#5   1 0    0    0    1  1  0  1 36.50000
#6   2 0    0    0    0  2  0  0 22.25000

Answer 2

apply is a useful option, where 1 tells it to compute by row. It also seems to play nicely with data.tables:

df$means <- apply(df, 1, function(r) sum(r * as.double(names(df))) / sum(r))

#### OUTPUT ####

     2.5 9 15.5 21.5 29.5 42 57 72    means
  1:   0 0    0    0    0  0  0  2 72.00000
  2:   0 0    0    0    1  0  1  0 43.25000
  3:   0 0    0    0    2  0  0  0 29.50000
  4:   1 0    0    0    0  2  0  0 28.83333
  5:   1 0    0    0    1  1  0  1 36.50000
 ---                                       
200:   0 0    0    0    0  0  1  1 64.50000
201:   3 0    0    0    0  2  0  0 18.30000
202:   0 0    1    0    0  1  1  0 38.16667
203:   0 0    0    0    1  1  0  0 35.75000
204:   1 1    0    0    0  2  0  0 23.87500

Answer 3

First, create a temporary data frame where you calculate the ages from column names. Then, with rowSums calculate average ages. (Supposed your data frame is called d.)

tmp <- do.call(cbind, lapply(seq(d), function(x) d[x] * as.numeric(colnames(d)[x])))
d$mu <- rowSums(tmp) / rowSums(d)

head(d)
#   2.5 9 15.5 21.5 29.5 42 57 72       mu
# 1   0 0    0    0    0  0  0  2 72.00000
# 2   0 0    0    0    1  0  1  0 43.25000
# 3   0 0    0    0    2  0  0  0 29.50000
# 4   1 0    0    0    0  2  0  0 28.83333
# 5   1 0    0    0    1  1  0  1 36.50000
# 6   2 0    0    0    0  2  0  0 22.25000