Assign an ID based on two columns R

Question

I have some data that looks like this. I want to assign an 'ID' by email and wk_id.

row_num    email    wk_id
    1       aaaa    1/4/15
    2       aaaa    1/11/15
    3       aaaa    1/25/15
    4       bbbb    6/29/14
    5       bbbb    9/7/14
    6       cccc    11/16/14
    7       cccc    11/30/14
    8       cccc    12/7/14
    9       cccc    12/14/14
    10      cccc    12/21/14
    11      cccc    12/28/14
    12      cccc    1/4/15
    13      cccc    1/25/15

I want the data to look like this.

row_num email   wk_id       ID
1       aaaa    1/4/15      1
2       aaaa    1/11/15     2
3       aaaa    1/25/15     3
4       bbbb    6/29/14     1
5       bbbb    9/7/14      2
6       cccc    11/16/14    1
7       cccc    11/30/14    2
8       cccc    12/7/14     3
9       cccc    12/14/14    4
10      cccc    12/21/14    5
11      cccc    12/28/14    6
12      cccc    1/4/15      7
13      cccc    1/25/15     8

I can't figure out how to get the "counter" to reset everytime it hits a new email address. I've tried data.table and ddply but still can't quite get it.

Use the hadley method... Split Apply Combine. Split the data by email / wk_id, apply: add a 1:nrow(small_chunk) column to each chunk, Combine back into a dataframe. — cory, Jun 05 '15 at 16:56

score 3 · Accepted Answer · edited Jun 05 '15 at 18:19

You could try:

library(dplyr)
df %>%
   group_by(email) %>% 
   mutate(ID = row_number())

Which gives:

#Source: local data frame [13 x 4]
#Groups: email
#
#   row_num email    wk_id ID
#1        1  aaaa   1/4/15  1
#2        2  aaaa  1/11/15  2
#3        3  aaaa  1/25/15  3
#4        4  bbbb  6/29/14  1
#5        5  bbbb   9/7/14  2
#6        6  cccc 11/16/14  1
#7        7  cccc 11/30/14  2
#8        8  cccc  12/7/14  3
#9        9  cccc 12/14/14  4
#10      10  cccc 12/21/14  5
#11      11  cccc 12/28/14  6
#12      12  cccc   1/4/15  7
#13      13  cccc  1/25/15  8

Or using data.table

library(data.table)
setDT(df)[, ID:= 1:.N, email]

Or ave from base R

df$ID <- with(df, ave(row_num, email, FUN=seq_along))

Assign an ID based on two columns R

1 Answers1

Linked