0

I have a dataframe that looks like this:

ID     Date        result
1      1/1/2010    100
1      1/1/2010    200
2      1/1/2011    300
3      1/1/2011    400
3      1/1/2011    500
3      1/1/2011    600

I would like to include another column on this dataframe as such:

ID     Date        result  Date_num
1      1/1/2010    100     1/1/2010 (1)
1      1/1/2010    200     1/1/2010 (2)
2      1/1/2011    300     1/1/2011 (1)
3      1/1/2011    400     1/1/2011 (1)
3      1/1/2011    500     1/1/2011 (2)
3      1/1/2011    600     1/1/2011 (3)

This seems simple at first since R is quick to group by IDs (using dplyr, table, aggregate, etc), but R is also quick to group everything together and summarize the results.

The first step is to create a vector c(1,2,1,1,2,3) that holds the counts in the correct sequence. Then, I could use dplyr to mutate a new column with it. But the first step has me a little stuck.

I'm thinking: 

  DF <- DF %>% 
    group_by(ID) %>%
    summarize(length(ID)) %>%
    mutate(Date_num = paste(DF$ID, <create a sequence from the previous step?>))

Thanks for you help.

tumultous_rooster
  • 12,150
  • 32
  • 92
  • 149

1 Answers1

3

You could use row_number() from dplyr

 library(dplyr)
 DF %>% 
    group_by(ID) %>%
    mutate(Date_num = row_number())
akrun
  • 874,273
  • 37
  • 540
  • 662