Calculate row means for columns with specific name pattern in R

Question

I've a dataset looking like this:

> head(big_metric[,c(30:37)])
   Recall_41365 Accuracy_41361 Precision_41361 Recall_41361 Accuracy_41365 Precision_41365 Recall_41365 Prob
1:    1.0000000      0.9516129       0.9516129            1      0.8189655       0.8189655            1  0.0
2:    1.0000000      0.9516129       0.9516129            1      0.8189655       0.8189655            1  0.1
3:    1.0000000      0.9516129       0.9516129            1      0.8189655       0.8189655            1  0.2
4:    1.0000000      0.9516129       0.9516129            1      0.8189655       0.8189655            1  0.3
5:    1.0000000      0.9516129       0.9516129            1      0.8189655       0.8189655            1  0.4
6:    0.9936909      0.9516129       0.9516129            1      0.8189655       0.8189655            1  0.5

The dataset is 10 rows with column big_metric$Prob having values from 0 to 0.9 with a 0.1 step.

For each row, I would like to calculate the mean value for all columns except Prob. The mean value will be stored in a new column as follows:

big_metric$Accuracy : mean value for columns having a header pattern Accuracy_413**

big_metric$Precision : mean value for columns having a header pattern Precision_413**

big_metric$Recall : mean value for columns having a header pattern Recall_413**

I'm very new to R, so if at least somebody can set me on the right track, that would be a major help. Any input is appreciated!

Below is a sample of the data:

> dput(big_metric)
structure(list(Accuracy_41365 = c(0.89119171, 0.89119171, 0.89119171, 
0.89119171, 0.89119171, 0.888601036, 0.886010363, 0.883419689, 
0.867875648, 0.870466321), Precision_41365 = c(0.89119171, 0.89119171, 
0.89119171, 0.89119171, 0.89119171, 0.890909091, 0.901069519, 
0.909589041, 0.910364146, 0.92), Recall_41365 = c(1, 1, 1, 1, 
1, 0.997093023, 0.979651163, 0.965116279, 0.944767442, 0.936046512
), Accuracy_41366 = c(0.836898396, 0.836898396, 0.836898396, 
0.836898396, 0.836898396, 0.820855615, 0.839572193, 0.839572193, 
0.831550802, 0.826203209), Precision_41366 = c(0.836898396, 0.836898396, 
0.836898396, 0.836898396, 0.836898396, 0.83423913, 0.856338028, 
0.870967742, 0.87202381, 0.892405063), Recall_41366 = c(1, 1, 
1, 1, 1, 0.980830671, 0.971246006, 0.948881789, 0.936102236, 
0.900958466), Accuracy_41365 = c(0.810344828, 0.810344828, 0.810344828, 
0.810344828, 0.810344828, 0.793103448, 0.801724138, 0.818965517, 
0.801724138, 0.793103448), Precision_41365 = c(0.810344828, 0.810344828, 
0.810344828, 0.810344828, 0.810344828, 0.807017544, 0.825688073, 
0.841121495, 0.838095238, 0.836538462), Recall_41365 = c(1, 1, 
1, 1, 1, 0.978723404, 0.957446809, 0.957446809, 0.936170213, 
0.925531915), Accuracy_41366 = c(0.972477064, 0.972477064, 0.972477064, 
0.972477064, 0.972477064, 0.972477064, 0.926605505, 0.889908257, 
0.844036697, 0.779816514), Precision_41366 = c(0.972477064, 0.972477064, 
0.972477064, 0.972477064, 0.972477064, 0.972477064, 0.99, 1, 
1, 1), Recall_41366 = c(1, 1, 1, 1, 1, 1, 0.933962264, 0.886792453, 
0.839622642, 0.773584906), Accuracy_41361 = c(0.851162791, 0.851162791, 
0.851162791, 0.851162791, 0.851162791, 0.837209302, 0.809302326, 
0.795348837, 0.776744186, 0.795348837), Precision_41361 = c(0.851162791, 
0.851162791, 0.851162791, 0.851162791, 0.851162791, 0.849056604, 
0.858585859, 0.863874346, 0.860962567, 0.879781421), Recall_41361 = c(1, 
1, 1, 1, 1, 0.983606557, 0.928961749, 0.901639344, 0.879781421, 
0.879781421), Accuracy_41366 = c(0.703208556, 0.703208556, 0.703208556, 
0.703208556, 0.703208556, 0.697860963, 0.711229947, 0.703208556, 
0.684491979, 0.687165775), Precision_41366 = c(0.703208556, 0.703208556, 
0.703208556, 0.703208556, 0.703208556, 0.701612903, 0.715877437, 
0.717142857, 0.711370262, 0.715976331), Recall_41366 = c(1, 1, 
1, 1, 1, 0.992395437, 0.977186312, 0.954372624, 0.927756654, 
0.920152091), Accuracy_41361 = c(0.790322581, 0.790322581, 0.790322581, 
0.790322581, 0.790322581, 0.758064516, 0.725806452, 0.661290323, 
0.629032258, 0.612903226), Precision_41361 = c(0.790322581, 0.790322581, 
0.790322581, 0.790322581, 0.790322581, 0.783333333, 0.785714286, 
0.769230769, 0.76, 0.755102041), Recall_41361 = c(1, 1, 1, 1, 
1, 0.959183673, 0.897959184, 0.816326531, 0.775510204, 0.755102041
), Accuracy_41366 = c(0.798165138, 0.798165138, 0.798165138, 
0.798165138, 0.798165138, 0.788990826, 0.76146789, 0.752293578, 
0.724770642, 0.706422018), Precision_41366 = c(0.798165138, 0.798165138, 
0.798165138, 0.798165138, 0.798165138, 0.796296296, 0.796116505, 
0.8, 0.793814433, 0.795698925), Recall_41366 = c(1, 1, 1, 1, 
1, 0.988505747, 0.942528736, 0.91954023, 0.885057471, 0.850574713
), Accuracy_41361 = c(0.874418605, 0.874418605, 0.874418605, 
0.874418605, 0.874418605, 0.860465116, 0.846511628, 0.846511628, 
0.841860465, 0.841860465), Precision_41361 = c(0.874418605, 0.874418605, 
0.874418605, 0.874418605, 0.874418605, 0.872641509, 0.87804878, 
0.885572139, 0.885, 0.896907216), Recall_41361 = c(1, 1, 1, 1, 
1, 0.984042553, 0.957446809, 0.946808511, 0.941489362, 0.925531915
), Accuracy_41365 = c(0.821243523, 0.821243523, 0.821243523, 
0.821243523, 0.821243523, 0.816062176, 0.816062176, 0.810880829, 
0.803108808, 0.792746114), Precision_41365 = c(0.821243523, 0.821243523, 
0.821243523, 0.821243523, 0.821243523, 0.8203125, 0.828877005, 
0.82972973, 0.828337875, 0.831932773), Recall_41365 = c(1, 1, 
1, 1, 1, 0.993690852, 0.977917981, 0.968454259, 0.958990536, 
0.936908517), Accuracy_41361 = c(0.951612903, 0.951612903, 0.951612903, 
0.951612903, 0.951612903, 0.951612903, 0.919354839, 0.919354839, 
0.887096774, 0.870967742), Precision_41361 = c(0.951612903, 0.951612903, 
0.951612903, 0.951612903, 0.951612903, 0.951612903, 0.965517241, 
0.965517241, 0.964285714, 0.981132075), Recall_41361 = c(1, 1, 
1, 1, 1, 1, 0.949152542, 0.949152542, 0.915254237, 0.881355932
), Accuracy_41365 = c(0.818965517, 0.818965517, 0.818965517, 
0.818965517, 0.818965517, 0.818965517, 0.810344828, 0.801724138, 
0.801724138, 0.793103448), Precision_41365 = c(0.818965517, 0.818965517, 
0.818965517, 0.818965517, 0.818965517, 0.818965517, 0.834862385, 
0.839622642, 0.839622642, 0.844660194), Recall_41365 = c(1, 1, 
1, 1, 1, 1, 0.957894737, 0.936842105, 0.936842105, 0.915789474
), Prob = c(0, 0.1, 0.2, 0.3, 0.4, 0.5, 0.6, 0.7, 0.8, 0.9)), row.names = c(NA, 
-10L), class = c("data.table", "data.frame"), .internal.selfref = <pointer: 0x00000000052e1ef0>)

Answer 1

Based on the dput, an easier option would be split (or else may have to do some reshaping with melt

# find the column names that start with 'Recall', 'Accuracy', 'Precision'
nm1 <- grep("^(Recall|Accuracy|Precision)", names(big_metric), value = TRUE)
# split the dataset with the substring of nm1 into a list
# loop through the list and get the rowMeans
out <- sapply(split.default(big_metric[, ..nm1], sub("_.*", "", nm1)), rowMeans)
# assign that columns to create new columns in the original dataset
big_metric[, colnames(out) := as.data.frame(out)]

---

Or we melt into 'long' format and then get the mean of the columns

out1 <- melt(big_metric[, rn := seq_len(.N)], 
      measure = patterns("^Recall", "^Precision", "^Accuracy"))[, 
     lapply(.SD, mean), rn, .SDcols = value1:value3]
big_metric[, c("Recall", "Precision", "Accuracy") := out1[, 2:4]]

Update

If we also need the rowwise standard deviation, make use of the rowSds from matrixStats. As before, we split the big_metric, loop over the list of data.tables, cbind the rowMeans and rowSds to create a list of matrices, then cbind the list elements to create out1. Assign the output columns to be original dataset with a vector of column names prepared

library(matrixStats)
lst1 <- lapply(split.default(big_metric[, ..nm1], sub("_.*", "", nm1)),
   function(x) cbind(Mean = rowMeans(x), SD = rowSds(as.matrix(x))))
out1 <- as.data.frame(do.call(cbind, lst1))
nm2 <- paste0(rep(c("Recall", "Precision", "Accuracy"), each = 2), names(out1))
big_metric[, (nm2) := out1]