1

I am trying to build a summary table for my homework. The variable I am using to aggregate is a binary variable (0/1). 

total <-aggregate(result ~sex, data=x,sum)

However, I want aggregate every few 0s and every few 1s into different groups. For example:

Sex= 1 1 1 1 0 0 1 1
result = 5 1 10 6 7 8 3 2

The table I want to get will be Sex 1 result 22, Sex 0 result 15, sex 1 result 5. How can I do this? Any ideas? Thanks!

Mike
  • 11
  • 1

1 Answers1

2

We can use rleid from data.table to do the grouping and then get the sum of 'result'

library(data.table)
setDT(x)[, .(Sex = Sex[1L],result = sum(result)), by = .(grp= rleid(Sex))][, grp := NULL][]
#    Sex result
#1:   1     22
#2:   0     15
#3:   1      5

Or using dplyr

library(dplyr)
x %>% 
  group_by(grp = cumsum(Sex!= lag(Sex, default = Sex[1]))+1) %>% 
  summarise(Sex = first(Sex), result = sum(result)) %>%
  select(-grp)
#   Sex result
#   <dbl>  <dbl>
#1     1     22
#2     0     15 
#3     1      5

This can also be used with aggregate

aggregate(result ~cbind(Sex = cumsum(c(1, diff(Sex) != 0))), x, sum)

EDIT: Changed the grouping variable in aggregate from rleid(Sex) to one of the options showed in the link provided by @Sotos

data

x <- structure(list(Sex = c(1, 1, 1, 1, 0, 0, 1, 1), result = c(5, 
1, 10, 6, 7, 8, 3, 2)), .Names = c("Sex", "result"), row.names = c(NA, 
-8L), class = "data.frame")
Community
  • 1
  • 1
akrun
  • 874,273
  • 37
  • 540
  • 662
  • 1
    Some cool function [here](http://stackoverflow.com/questions/37809094/create-group-names-for-consecutive-values) if you don't want to load data.table just for the `rleid` – Sotos Sep 08 '16 at 07:51
  • @Sotos I also had it without `rleid` for `dplyr` – akrun Sep 08 '16 at 07:52
  • Well If you are going to load any package might as well go with `rleid` (with `dtplyr` and all). I was talking about `aggregate`. But yeah, I didn't notice your `dplyr` method – Sotos Sep 08 '16 at 07:57
  • 1
    @Sotos Thank you for the suggestion. I pray somebody won't accuse me for plagiarism :-) – akrun Sep 08 '16 at 09:02
  • 1
    heh...naahh... I think you are safe :) – Sotos Sep 08 '16 at 09:07