How to add 6 columns after every 12 columns in r?

user_6123824 0
Viewed 100

I am having dataset 756 columns and 68 rows. I want to insert 6 columns after every 12 columns and fill the 1st empty columns with mean of 1st and 2nd column; 2nd empty column with mean of 3rd, 4th and 5th column; 3rd empty column with mean of 6th, 7th, 8th and 9th column; 4th empty column with mean of 10th, 11th and 12th column; 5th empty column with mean of all the 12 columns; 6th empty column with mean of 10th, 11th, 12th of 1st row and 1st, 2nd and 3rd column from 2nd row

Existing DataFrame

Col_1   Col_2   Col_3   Col_4   Col_5   Col_6   Col_7   Col_8   Col_9   Col_10  Col_11  Col_12  Col_1   Col_2   Col_3   Col_4   Col_5   Col_6   Col_7   Col_8   Col_9   Col_10  Col_11  Col_12
22.43   24.53   27.61   29.31   31.35   30.27   28.40   27.67   28.22   28.10   25.52   22.97   23.55   25.43   28.60   30.26   32.93   31.27   29.35   28.81   28.89   28.93   26.63   23.60
22.89   25.00   27.32   30.12   31.82   31.71   28.92   28.00   27.98   26.83   24.02   23.54   23.75   26.12   28.19   31.31   32.99   32.44   30.05   28.82   28.63   27.58   24.57   24.30
23.11   25.23   28.47   30.24   32.52   30.65   28.53   27.26   27.65   27.02   24.40   22.35   23.83   25.98   29.27   31.19   34.15   31.88   29.57   28.23   28.03   27.38   25.03   22.80

Desired DataFrame

Col_1   Col_2   Col_3   Col_4   Col_5   Col_6   Col_7   Col_8   Col_9   Col_10  Col_11  Col_12  Empty_1 Empty_2 Empty_3 Empty_4 Empty_5 Empty_6 Col_1   Col_2   Col_3   Col_4   Col_5   Col_6   Col_7   Col_8   Col_9   Col_10  Col_11  Col_12  Empty_1 Empty_2 Empty_3 Empty_4 Empty_5 Empty_6
22.43   24.53   27.61   29.31   31.35   30.27   28.40   27.67   28.22   28.10   25.52   22.97   =AVERAGE(A2:B2) =AVERAGE(C2:E2) =AVERAGE(F2:I2) =AVERAGE(J2:L2) =AVERAGE(J2:L2) =AVERAGE(J2:L2,A3:C3)   23.55   25.43   28.6    30.26   32.93   31.27   29.35   28.81   28.89   28.93   26.63   23.6    =AVERAGE(S2:T2) =AVERAGE(U2:W2) =AVERAGE(X2:AA2)    =AVERAGE(AB2:AD2)   =AVERAGE(AB2:AD2)   =AVERAGE(AB2:AD2,S3:U3)
22.89   25.00   27.32   30.12   31.82   31.71   28.92   28.00   27.98   26.83   24.02   23.54   =AVERAGE(A3:B3) =AVERAGE(C3:E3) =AVERAGE(F3:I3) =AVERAGE(J3:L3) =AVERAGE(J3:L3) =AVERAGE(J3:L3,A4:C4)   23.75   26.12   28.19   31.31   32.99   32.44   30.05   28.82   28.63   27.58   24.57   24.30   =AVERAGE(S3:T3) =AVERAGE(U3:W3) =AVERAGE(X3:AA3)    =AVERAGE(AB3:AD3)   =AVERAGE(AB3:AD3)   =AVERAGE(AB3:AD3,S4:U4)
23.11   25.23   28.47   30.24   32.52   30.65   28.53   27.26   27.65   27.02   24.4    22.35   =AVERAGE(A4:B4) =AVERAGE(C4:E4) =AVERAGE(F4:I4) =AVERAGE(J4:L4) =AVERAGE(J4:L4) =AVERAGE(J4:L4,A5:C5)   23.83   25.98   29.27   31.19   34.15   31.88   29.57   28.23   28.03   27.38   25.03   22.8    =AVERAGE(S4:T4) =AVERAGE(U4:W4) =AVERAGE(X4:AA4)    =AVERAGE(AB4:AD4)   =AVERAGE(AB4:AD4)   =AVERAGE(AB4:AD4,S5:U5)

How to implement it in R?

dplyr dataframe r
1 Answers

Data:

df <- read.table(text = 'Col_1   Col_2   Col_3   Col_4   Col_5   Col_6   Col_7   Col_8   Col_9   Col_10  Col_11  Col_12  Col_1   Col_2   Col_3   Col_4   Col_5   Col_6   Col_7   Col_8   Col_9   Col_10  Col_11  Col_12
22.43   24.53   27.61   29.31   31.35   30.27   28.40   27.67   28.22   28.10   25.52   22.97   23.55   25.43   28.60   30.26   32.93   31.27   29.35   28.81   28.89   28.93   26.63   23.60
                 22.89   25.00   27.32   30.12   31.82   31.71   28.92   28.00   27.98   26.83   24.02   23.54   23.75   26.12   28.19   31.31   32.99   32.44   30.05   28.82   28.63   27.58   24.57   24.30
                 23.11   25.23   28.47   30.24   32.52   30.65   28.53   27.26   27.65   27.02   24.40   22.35   23.83   25.98   29.27   31.19   34.15   31.88   29.57   28.23   28.03   27.38   25.03   22.80', header = TRUE, stringsAsFactors = FALSE)

Code:

library('data.table')
# split data by 12 columns
setDT(df)
split_cols <- split(seq_len(ncol(df)), ceiling(seq_along(df)/12))
df1 <- lapply(split_cols, function(x) df[, .SD, .SDcols = x ][, id := .I])

# compute row means
df2 <- lapply(df1, function(x){
  x[, .(Col1_2 = rowMeans(x[, 1:2]),
        Col3_5 = rowMeans(x[, 3:5]),
        Col6_9 = rowMeans(x[, 6:9]),
        Col10_12 = rowMeans(x[, 10:12]),
        Col1_12 = rowMeans(x[, .SD, .SDcols = -'id']),
        Col10_12_1_3 = rowMeans(do.call('cbind', list(x[, 10:12], 
                                                      rbindlist(list(x[-1, 1:3], 
                                                                     data.frame(NA, NA, NA))))), 
                                na.rm = TRUE),
        id)]
})
# check for the length of df1 and df2 to be equal. If not, stop proceeding further.
stopifnot(length(df1) == length(df2))

# join df1 and df2 by id
df3 <- lapply(seq_along(df2), function(x) df1[[x]][df2[[x]], on = "id"])

# remove id column
df3 <- lapply(df3, function(x) x[, id := NULL])

# column bind all data
do.call('cbind', df3)
user_16917230
Related