R: How to divide several columns into equal and equivalent parts, reduce the number of columns, and reallocate data?

Question

I have a data frame that looks like this:

"ID" "condition" "E_01" "E_02" "E_03" "E_04"...
"ID01" "cond1"    0.1    0.2    0.1    N/A
"ID01" "cond2"    N/A    N/A    N/A    0.1
"ID01" "cond3"    N/A    N/A    N/A    N/A
"ID02" "cond1"    0.1    0.2    0.1    N/A
...

where the E_XY labels continue until N.

If we assume N=9 I would like to first split N into equal and equivalent parts, for example 3, to retrieve "T_01", "T_02", "T_03" three times, and next, reallocate the data to get rid of the N/As as illustrated in the desired output. ("E_01","E_04","E_07" all belong to "T_01"; "E_02","E_05","E_08" all belong to "T_02" etc.)

Desired output:

"ID" "condition" "T_01" "T_02" "T_03"
"ID01" "cond1"    0.1    0.2    0.1
"ID01" "cond2"    0.1    0.2    0.1
"ID01" "cond3"    0.1    0.2    0.1
"ID02" "cond1"    0.1    0.2    0.1
...

Does anyone know a smart way to do this?

Answer 1

Perhaps, try this with split.default -

cols <- grep('E_', names(df))
N <- 3
result <- cbind(df[-cols], sapply(split.default(df[cols], 1:N), rowSums, na.rm = TRUE))