CODEWITHSUNDEEP
r
melbez
melbez

Reputation: 1000

Creating variable with means of multiple other variables in R

How do I combine the means of multiple columns efficiently? I would like to create one object that has a list of means for variables beginning with A and a separate object that has a list of means for variables beginning with C. Ideally, I would be able to use column numbers as opposed to variable names since column numbers are easier to type.

A1U_sweet  A2F_dip  A3U_bbq  C1U_sweet  C2F_dip  C3U_bbq
1          2        1        NA         NA       NA
NA         NA       NA       4          1        2
2          4        7        NA         NA       NA

I have used the following function in the past, but it is inefficient. I have many more columns than depicted here. I am including this to clarify what I am trying to do.

average_A<-data.frame((mean(A1U_sweet, na.rm = TRUE)), (mean(A2F_dip, na.rm = TRUE)), (mean(A3U_bbq, na.rm = TRUE)))
average_C<-data.frame((mean(C1U_sweet, na.rm = TRUE)), (mean(C2F_dip, na.rm = TRUE)), (mean(C3U_bbq, na.rm = TRUE)))

Upvotes: 1

Views: 1325

Answers (1)

akrun
akrun

Reputation: 887241

We could split the data by using the first character of the column names and then do the colMeans on each of the list elements using base R and keep the output in a list

lst <- lapply(split.default(df1, sub("\\d+.*", "", names(df1))), colMeans, na.rm = TRUE)
lst
#$A
#  A1U_sweet   A2F_dip   A3U_bbq 
#      1.5       3.0       4.0 

#$C
#  C1U_sweet   C2F_dip   C3U_bbq 
#      4         1         2

Or with substr and keep it in a single dataset after stripping the prefix part of the column names

res <- t(sapply(split.default(df1, substr(names(df1), 1, 1)), colMeans, na.rm = TRUE))
colnames(res) <- sub("^..", "", colnames(res))
res
#    U_sweet   F_dip   U_bbq
#A       1.5       3       4 
#C       4.0       1       2

Or another option is with tidyverse, where we gather the data into 'long' format and then get the mean by group

library(dplyr)
library(tidyr)
library(stringr)
df1 %>%
   gather(group, value) %>% 
   group_by(grp = str_sub(group, 1, 1), group)  %>% 
   summarise(value = mean(value, na.rm = TRUE)) %>%
   ungroup %>%
   select(-grp)
# A tibble: 6 x 2
#   group     value
#   <chr>     <dbl>
#1 A1U_sweet  1.50
#2 A2F_dip    3.00
#3 A3U_bbq    4.00
#4 C1U_sweet  4.00
#5 C2F_dip    1.00
#6 C3U_bbq    2.00

data

df1 <- structure(list(A1U_sweet = c(1L, NA, 2L), A2F_dip = c(2L, NA, 
4L), A3U_bbq = c(1L, NA, 7L), C1U_sweet = c(NA, 4L, NA), C2F_dip = c(NA, 
1L, NA), C3U_bbq = c(NA, 2L, NA)), .Names = c("A1U_sweet", "A2F_dip", 
 "A3U_bbq", "C1U_sweet", "C2F_dip", "C3U_bbq"), class = "data.frame", 
 row.names = c(NA, -3L))

Upvotes: 1

Related Questions