How can I group by two variable in R taking mean of all other variables?-CodePudding

my data frame is:

M1T1	M1T2	M1T3	M2T1	M2T2	M2T3	M3T1	M3T2	M3T3	cntry_lan	admdw
NA	NA	NA	1	2	2	1	1	2	ATGER	group1
7	6	5	NA	NA	NA	6	6	5	ATGER	group3
7	5	5	NA	NA	NA	7	4	4	ATGER	group2

My code is :

mtmm_data1  %>%
  group_by(cntry_lan) %>% group_by(admdw)
  summarise_at(vars(M1MT1, M1T2, M1T3, M2T1, M2T2, M2T3, M3T1, M3T2, M3T3), list(name = mean))

The error I get:

Error in UseMethod("tbl_vars") : no applicable method for 'tbl_vars' applied to an object of class "c('quosures', 'list')"

Each countr_lan has three groups that are group1, group2 and group3. I would like to have one row for each country_lan and then have three groups for each country_lan having all the columns stable. Instead of creating new columns, I want to have the mean of the same columns M1T1-M3T3..

CodePudding user response：

The function you need is aggregate, you can use it this way :

aggregate(x=df[,c("M1T1","M1T2","M1T3","M2T1","M2T2","M2T3","M3T1","M3T2","M3T3")], #column on which you want to apply a function
          by=list(country=df$country,grp=df$grp),#grouping variables
          FUN=mean)#function to apply

df being your data frame

CodePudding user response：

df<-data.frame(cntry_lan = c("ATGER"),
               admdw = c("group1", "group2", "group3"),
               M1T1 = c(NA,1,2),
               M1T2 = c(1,10,3),
               M1T3 = c(10,NA,1))

library(tidyverse)

df %>% 
  rowwise() %>% 
  mutate(mean = mean(c_across(starts_with("M")), na.rm=T), .keep="unused") %>% 
  pivot_wider(names_from = admdw, values_from = mean)

#  cntry_lan group1 group2 group3
#  <chr>      <dbl>  <dbl>  <dbl>
#1 ATGER        5.5    5.5      2