How can I add up all the events that took place each year for each id

August 20, 2022

I want to know how to add all the counts by id for each year in the data frame below but don’t know how to go about doing so.

ids <- c('A', 'A', 'A',
         'B', 'B', 'B',
         'C', 'C', 'C')

dates <- c('2022-12-21', '2002-03-11', '2002-01-19',
           '2010-11-21', '2002-07-10', '2009-02-19',
           '2022-12-21', '2022-10-11', '2002-01-19')

counts <- rep(1, 9)

df <- data.frame(
  
  ID = ids,
  Event_date = as.Date(dates),
  counts
  
)

My dataframe looks like this..

  ID Event_date counts
1  A 2022-12-21      1
2  A 2002-03-11      1
3  A 2002-01-19      1
4  B 2010-11-21      1
5  B 2002-07-10      1
6  B 2009-02-19      1
7  C 2022-12-21      1
8  C 2022-10-11      1
9  C 2002-01-19      1

But this is what I want…

  ID Event_date counts
1  A 2022       1
2  A 2002       2
3  B 2010       1
4  B 2002       1
5  B 2009       1
6  C 2022       2
7  C 2002       1

>Solution :

library(dplyr)

 df %>% mutate(Event_date= format(Event_date,'%Y')) %>% 
    group_by(ID,Event_date) %>% 
    summarise(counts = sum(counts)) %>% data.frame()

gives,

  ID Event_date counts
1  A       2002      2
2  A       2022      1
3  B       2002      1
4  B       2009      1
5  B       2010      1
6  C       2002      1
7  C       2022      2