In R, assign row sums of multiple columns to other columns

user_3542212 0
Viewed 59

Hoping someone can tell me how to simplify my code with a more elegant way to do what I am trying to do in R.

Existing data.frame:

names <- c("ADD1_T1", "ADD2_T1", "ADD3_T1", "ADD4_T1", "ADD5_T1", "ADD6_T1", "ADD7_T1", "ADD8_T1", "ADD9_T1", "SS_ADD1_T1", "SS_ADD2_T1", "SS_ADD3_T1", "SS_ADD4_T1", "SS_ADD5_T1", "SS_ADD6_T1", "SS_ADD7_T1", "SS_ADD8_T1", "SS_ADD9_T1", "TT_ADD1_T1", "TT_ADD2_T1", "TT_ADD3_T1", "TT_ADD4_T1", "TT_ADD5_T1", "TT_ADD6_T1", "TT_ADD7_T1", "TT_ADD8_T1", "TT_ADD9_T1", "XX_ADD1_T1", "XX_ADD2_T1", "XX_ADD3_T1", "XX_ADD4_T1", "XX_ADD5_T1", "XX_ADD6_T1", "XX_ADD7_T1", "XX_ADD8_T1", "XX_ADD9_T1", "GG_ADD1_T1", "GG_ADD2_T1", "GG_ADD3_T1", "GG_ADD4_T1", "GG_ADD5_T1", "GG_ADD6_T1", "GG_ADD7_T1", "GG_ADD8_T1", "GG_ADD9_T1", "ADD1_T2", "ADD2_T2", "ADD3_T2", "ADD4_T2", "ADD5_T2", "ADD6_T2", "ADD7_T2", "ADD8_T2", "ADD9_T2", "SS_ADD1_T2", "SS_ADD2_T2", "SS_ADD3_T2", "SS_ADD4_T2", "SS_ADD5_T2", "SS_ADD6_T2", "SS_ADD7_T2", "SS_ADD8_T2", "SS_ADD9_T2", "TT_ADD1_T2", "TT_ADD2_T2", "TT_ADD3_T2", "TT_ADD4_T2", "TT_ADD5_T2", "TT_ADD6_T2", "TT_ADD7_T2", "TT_ADD8_T2", "TT_ADD9_T2", "XX_ADD1_T2", "XX_ADD2_T2", "XX_ADD3_T2", "XX_ADD4_T2", "XX_ADD5_T2", "XX_ADD6_T2", "XX_ADD7_T2", "XX_ADD8_T2", "XX_ADD9_T2", "GG_ADD1_T2", "GG_ADD2_T2", "GG_ADD3_T2", "GG_ADD4_T2", "GG_ADD5_T2", "GG_ADD6_T2", "GG_ADD7_T2", "GG_ADD8_T2", "GG_ADD9_T2")
df <- data.frame()
for (k in names) df[[k]] <- as.character()
df[nrow(df)+20,] <- NA
df[10:ncol(df)] <- sample(0:1, size = 20, replace = TRUE)

Looking to do the below in R with as few lines of code as possible. Is it possible to do with just a couple of line of code (as opposed to 18)?

ADD1_T1  = as.numeric(rowSums(df[, c("SS_ADD1_T1" , "TT_ADD1_T1" , "XX_ADD1_T1" , "GG_ADD1_T1") ], na.rm=TRUE)>0)
ADD2_T1  = as.numeric(rowSums(df[, c("SS_ADD2_T1" , "TT_ADD2_T1" , "XX_ADD2_T1" , "GG_ADD2_T1") ], na.rm=TRUE)>0)
...
ADD9_T1  = as.numeric(rowSums(df[, c("SS_ADD9_T1" , "TT_ADD9_T1" , "XX_ADD9_T1" , "GG_ADD9_T1") ], na.rm=TRUE)>0)

...

ADD1_T2  = as.numeric(rowSums(df[, c("SS_ADD1_T2" , "TT_ADD1_T2" , "XX_ADD1_T2" , "GG_ADD1_T2") ], na.rm=TRUE)>0)
ADD2_T2  = as.numeric(rowSums(df[, c("SS_ADD2_T2" , "TT_ADD2_T2" , "XX_ADD2_T2" , "GG_ADD2_T2") ], na.rm=TRUE)>0)
...
ADD9_T2  = as.numeric(rowSums(df[, c("SS_ADD9_T2" , "TT_ADD9_T2" , "XX_ADD9_T2" , "GG_ADD9_T2") ], na.rm=TRUE)>0)
r
3 Answers

Consider actually defining all columns as numeric

for (k in names) df[[k]] <- as.numeric()

From there you can create a matrix of results by creating a vector of stem names with outer + paste0 and iterate with sapply + grep:

ADD_nms <- as.vector(outer(1:9, 1:2, function(x,y) paste0("ADD", x, "_T", y)))
ADD_nms
# [1] "ADD1_T1" "ADD2_T1" "ADD3_T1" "ADD4_T1" "ADD5_T1" "ADD6_T1" "ADD7_T1" 
# [8] "ADD8_T1" "ADD9_T1" "ADD1_T2" "ADD2_T2" "ADD3_T2" "ADD4_T2" "ADD5_T2" 
# [15] "ADD6_T2" "ADD7_T2" "ADD8_T2" "ADD9_T2"

ADD_matrix <- sapply(ADD_nms, function(x) 
    as.numeric(rowSums(df[, grep(x, names(df))], na.rm=TRUE)>0))

ADD_matrix

Online Demo

user_14224510

You could reshape your dataset: since your data has the same values everywhere, it is good to have some randomization ie:

df[10:ncol(df)] <-  sample(0:1,20 * 81, replace = TRUE)

Now what you can do:

nms <- names(df)
index<- grep("_ADD", nms)
A <- matrix(names(df)[index], 9)
B <- rbind(A[,1:4],A[,5:8])
df1 <- reshape(df[c(B)], t(B), dir="long", times = sub(".._","",B[,1]))
s <- grep("^(id|time)$",names(df1))
D <- by(df1[-s], df1$time, function(x) as.integer(rowSums(x, na.rm = TRUE) > 0))
df[match(names(D), nms[-index])] <- D

Now you could compute any values and compare with the column in df

user_83802720

In base R you can use the apply() funcition. See This

An interesting (and recommended alternative) is to use the group_by function from the tydyverse. See This

user_120349720
Related