CODEWITHSUNDEEP
r
Edwin
Edwin

Reputation: 961

Using dplyr to summarize by multiple groups

I'm trying to use dplyr to summarize a dataset based on 2 groups: "year" and "area". This is how the dataset looks like:

  Year   Area Num
1 2000 Area 1  99
2 2001 Area 3  85
3 2000 Area 1  60
4 2003 Area 2  90
5 2002 Area 1  40
6 2002 Area 3  30
7 2004 Area 4  10
...

The end result should look something like this:

  Year    Area Mean
1 2000 Area 1  100
2 2000 Area 2   80
3 2000 Area 3   89
4 2001 Area 1   80
5 2001 Area 2   85
6 2001 Area 3   59
7 2002 Area 1   90
8 2002 Area 2   88
...

Excuse the values for "mean", they're made up.

The code for the example dataset:

df <- structure(list(
   Year = c(2000, 2001, 2000, 2003, 2002, 2002, 2004), 
   Area = structure(c(1L, 3L, 1L, 2L, 1L, 3L, 4L), 
   .Label = c("Area 1", "Area 2", "Area 3", "Area 4"), 
   class = "factor"), 
   Num = structure(c(7L, 5L, 4L, 6L, 3L, 2L, 1L), 
   .Label = c("10", "30", "40", "60", "85", "90", "99"), 
   class = "factor")), 
   .Names = c("Year", "Area", "Num"), 
   class = "data.frame", row.names = c(NA, -7L))

df$Num <- as.numeric(df$Num)

Things I've tried:

df.meanYear <- df %>%
  group_by(Year) %>%
  group_by(Area) %>%
  summarize_each(funs(mean(Num)))

But it just replaces every value with the mean, instead of the intended result.

If possible please do provide alternate means (i.e. non-dplyr) methods, because I'm still new with R.

Upvotes: 6

Views: 16165

Answers (3)

arielhasidim
arielhasidim

Reputation: 834

I had a similar problem in my code, I fixed it with the .groups attribute:

df %>%
  group_by(Year,Area) %>%
  summarise(avg = mean(Num), .groups="keep")

Also verified with the added example (as.numeric corrupted Num values, so I used as.numeric(as.character(df$Num)) to fix it):

   Year Area     avg
  <dbl> <fct>  <dbl>
1  2000 Area 1  79.5
2  2001 Area 3  85  
3  2002 Area 1  40  
4  2002 Area 3  30  
5  2003 Area 2  90  
6  2004 Area 4  10

Upvotes: 3

akrun
akrun

Reputation: 887108

We can use data.table

library(data.table)
setDT(df)[, .(avg = mean(Num)) , by = .(Year, Area)]

Upvotes: 0

Raphael K
Raphael K

Reputation: 2353

Is this what you are looking for?

 library(dplyr)
 df <- group_by(df, Year, Area)
 df <- summarise(df, avg = mean(Num))

Upvotes: 6

Related Questions