Home > Net >  How can I group by two variable in R taking mean of all other variables?
How can I group by two variable in R taking mean of all other variables?

Time:11-10

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
  • Related