Group_by / summarize by two variables within a function

Question

I would like to write a function that summarize the provided data by some specified criteria, in this case by age

The example data is a table of users' age and their stats.

df <- data.frame('Age'=rep(18:25,2), 'X1'=10:17, 'X2'=28:35,'X4'=22:29)

Next I define the output columns that are relevant for the analysis

output_columns <- c('Age', 'X1', 'X2', 'X3')

This function computes the basic the sum of X1. X2 and X3 grouped by age.

aggr <- function(data, criteria, output_columns){
  k <- data %>% .[, colnames(.) %in% output_columns] %>%
    group_by_(.dots = criteria) %>%
    #summarise_each(funs(count), age) %>%
    summarize_if(is.numeric, sum)
  return (k)
}

When I call it like this

> e <- aggr(df, "Age", output_columns)
> e
# A tibble: 8 x 3
    Age    X1    X2
  <int> <int> <int>
1    18    20    56
2    19    22    58
3    20    24    60
4    21    26    62
5    22    28    64
6    23    30    66
7    24    32    68
8    25    34    70

I want to have another column called count which shows the number of observations in each age group. Desired output is

> desired
  Age X1 X2 count
1  18 20 56     2
2  19 22 58     2
3  20 24 60     2
4  21 26 62     2
5  22 28 64     2
6  23 30 66     2
7  24 32 68     2
8  25 34 70     2

I have tried different ways to do that, e.g. tally(), summarize_each etc. They all deliver wrong results.

I believe their should be an easy and simple way to do that. Any help is appreciated.

I think you need %>% group_by_at(criteria) %>% mutate(count = n()) added into your function — akrun
– akrun, Commented May 24, 2018 at 14:22
The last line in the pipeline could be replaced with: { cbind(summarize_if(., is.numeric, sum), summarize(., n = n())) } — G. Grothendieck
– G. Grothendieck, Commented May 24, 2018 at 14:55

IceCreamToucan · Accepted Answer · 2018-05-24 14:43:15Z

4

Since you're already summing all variables, you can just add a column of all 1s before the summary function

aggr <- function(data, criteria, output_columns){ 
    data %>% 
      .[, colnames(.) %in% output_columns] %>%
      group_by_(.dots = criteria) %>%
      mutate(n = 1L) %>%
      summarize_if(is.numeric, sum)
}

# A tibble: 8 x 4
    Age    X1    X2     n
  <int> <int> <int> <int>
1    18    20    56     2
2    19    22    58     2
3    20    24    60     2
4    21    26    62     2
5    22    28    64     2
6    23    30    66     2
7    24    32    68     2
8    25    34    70     2

edited May 24, 2018 at 14:43

answered May 24, 2018 at 14:31

IceCreamToucan

28.8k2 gold badges27 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

akrun Over a year ago

Is it better than my answer

denis Over a year ago

that's clever, like it

akrun · Accepted Answer · 2018-05-24 14:33:39Z

1

We could create the 'count' column before summarise_if

aggr<- function(data, criteria, output_columns){
                data %>% 
                   select(intersect(names(.), output_columns))%>%
                   group_by_at(criteria)%>%   
                   group_by(count = n(), add= TRUE) %>%                                
                   summarize_if(is.numeric,sum) %>%
                   select(setdiff(names(.), 'count'), count)                                     

    }




aggr(df,"Age",output_columns)
# A tibble: 8 x 4
# Groups:   Age [8]
#    Age    X1    X2 count
#  <int> <int> <int> <int>
#1    18    20    56     2
#2    19    22    58     2
#3    20    24    60     2
#4    21    26    62     2
#5    22    28    64     2
#6    23    30    66     2
#7    24    32    68     2
#8    25    34    70     2

edited May 24, 2018 at 14:33

answered May 24, 2018 at 14:28

akrun

891k38 gold badges590 silver badges700 bronze badges

3 Comments

akrun Over a year ago

@Ryan That is a good way. I find that you posted an answer. I like it

esem Over a year ago

adding this line group_by(count = n(), add= TRUE) to my original function works just as fine. Can that lead to any mistake?

akrun Over a year ago

@esem After the summarise step, you can ungroup if you need any more transformations

jay.sf · Accepted Answer · 2018-05-24 15:32:02Z

1

In base R you could do

aggr <- function(data, criteria, output_columns){
  ds <- data[, colnames(data) %in% output_columns]
  d <- aggregate(ds, by=list(criteria), function(x) c(sum(x), length(x)))
  "names<-"(do.call(data.frame, d)[, -c(2:3, 5)], c(names(ds), "n"))
}

> with(df, aggr(df, Age, output_columns))
  Age X1 X2 n
1  18 20 56 2
2  19 22 58 2
3  20 24 60 2
4  21 26 62 2
5  22 28 64 2
6  23 30 66 2
7  24 32 68 2
8  25 34 70 2

answered May 24, 2018 at 15:32

jay.sf

76.3k8 gold badges66 silver badges132 bronze badges

Collectives™ on Stack Overflow

Group_by / summarize by two variables within a function

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related