How to loop over each dataset column to summarize data by group

Question

I have this dataset

id = c(1,1,1,2,2,3)
v1 = c(3,4,5,2,4,5)
v2 = c(3,1,2,1,4,5)
v3 = c(2,1,2,3,3,4)
mydata <- data.frame(id ,v1, v2, v3)

> mydata
  id v1 v2 v3
1  1  3  3  2
2  1  4  1  1
3  1  5  2  2
4  2  2  1  3
5  2  4  4  3
6  3  5  5  4

and grouped data by id

groupdata <- group_by(mydata, id)

using summarize function can get a specific column mean value by id:

summarize(groupdata, mean = mean(v1))
# A tibble: 3 × 2
     id  mean
  <dbl> <dbl>
1     1     4
2     2     3
3     3     5

what i am tring to do is loop over each column and summarize them

colnames <- names(mydata)

for(i in colnames){
  assign(paste(i,"mean", sep = "_"), summarize(groupdata, mean = mean(i))) 
}

but i got this

> v1_mean
# A tibble: 3 × 2
     id  mean
  <dbl> <lgl>
1     1    NA
2     2    NA
3     3    NA

I found that you can't pass column names into summarize function as the parameter, is there any suggestions to improve the loop function?

You can try `apply` if you data isn't too huge. `apply(mydata, 1, mean)` — Nick Criswell, Jan 05 '17 at 16:40
`dplyr`'s `summarize_all` function might be what you're looking for. — jdobres, Jan 05 '17 at 16:54

Nick Criswell · Accepted Answer · 2017-01-05T17:00:12.400

Sorry, I misunderstood. Give this a shot.

library(dplyr)
grouped_mean <- mydata %>%
  group_by(id) %>%
  mutate_all(.funs = mean) %>%
  distinct(.keep_all = TRUE)

> grouped_mean
Source: local data frame [3 x 4]
Groups: id [3]

     id    v1    v2       v3
  <dbl> <dbl> <dbl>    <dbl>
1     1     4   2.0 1.666667
2     2     3   2.5 3.000000
3     3     5   5.0 4.000000

Per @jdobres comment, you can skip a step with summarise_all

grouped_mean <- mydata %>%
  group_by(id) %>%
  summarise_all(.funs = mean)
> grouped_mean
# A tibble: 3 × 4
     id    v1    v2       v3
  <dbl> <dbl> <dbl>    <dbl>
1     1     4   2.0 1.666667
2     2     3   2.5 3.000000
3     3     5   5.0 4.000000

Thank you for your help, this is what I am looking for! – freefrog Jan 06 '17 at 01:17 — freefrog, Jan 06 '17 at 01:17

score 0 · Answer 2 · answered Jan 05 '17 at 16:51

0

I think @Nick meant apply(mydata, 2, mean), which results in:

      id       v1       v2       v3 
1.666667 3.833333 2.666667 2.500000

answered Jan 05 '17 at 16:51

MCornejo

327
1
12

when operation on columns, it is better to use `sapply` or `lapply` like this: `sapply(mydata, mean)` – Jaap Jan 05 '17 at 17:08

How to loop over each dataset column to summarize data by group

2 Answers2