Cumulative count by group over time in r

user_10927267 0
Viewed 312

I know this might be a simple operation but I can't find a solution. I know it should be some form of group_by and sum or cumsum, but I cant figure out how. I want to plot a cumulative count of something by group over time. I have multiple rows per group and time that need to be counted (and some missing data). My dataset looks somewhat like this

df <- data.frame(group = c("A","A","A","A","B","B","B","C","C","C","C","C"),
                    time = c(1,1,2,3,1,2,2,1,2,2,3,3))

and I want this result:

group time count
    A    1     2
    A    2     3
    A    3     4
    B    1     1
    B    2     3
    C    1     1
    C    2     3
    C    3     5

I am usually use dplyr, but I am also happy with base R. How do I do that?

dplyr r
3 Answers

You can use the following solution:

library(dplyr)

df %>%
  group_by(group, time) %>%
  add_count() %>%
  distinct() %>%
  group_by(group) %>%
  mutate(n = cumsum(n))

# A tibble: 8 x 3
# Groups:   group [3]
  group  time     n
  <chr> <dbl> <int>
1 A         1     2
2 A         2     3
3 A         3     4
4 B         1     1
5 B         2     3
6 C         1     1
7 C         2     3
8 C         3     5
user_143145200

We can use summarise with group_by

library(dplyr)
df %>% 
    group_by(group, time) %>% 
    summarise(count = n()) %>% 
    group_by(group) %>% 
    mutate(count = cumsum(count)) %>% 
    ungroup

-output

# A tibble: 8 x 3
  group  time count
  <chr> <dbl> <int>
1 A         1     2
2 A         2     3
3 A         3     4
4 B         1     1
5 B         2     3
6 C         1     1
7 C         2     3
8 C         3     5
user_37322710

You can use count and cumsum -

library(dplyr)

df %>%
  count(group, time, name = 'count') %>%
  group_by(group) %>%
  mutate(count = cumsum(count)) %>%
  ungroup

#   group  time count
#  <chr> <dbl> <int>
#1 A         1     2
#2 A         2     3
#3 A         3     4
#4 B         1     1
#5 B         2     3
#6 C         1     1
#7 C         2     3
#8 C         3     5
user_39629140
Related