Add a unqiue ID by name and date in R

Question

I am doing some data cleaning/ formatting, and I would like to add a unique identifier to each record by name and then by date. For example, "Bob" may have four check-in dates, two of which are the same. For a case like this, I want to give him three different (sequential) ID numbers.

Here is the closest I have gotten to my desired result:

---

An example data set I created:

tst <- data_frame(
  name = c("Bob", "Sam", "Roger", "Stacy", "Roger", "Roger", "Sam", "Bob", "Sam", "Stacy", "Bob", "Stacy", "Roger", "Bob"),
  date = as.Date(c("2009-07-03", "2010-08-12", "2009-07-03", "2016-04-01", "2002-01-03", "2019-02-10", "2005-04-17", "2009-07-03", "2010-09-21", "2012-11-12", "2015-12-31", "2014-10-10", "2015-06-02", "2003-08-21")),
  amount = round(runif(14, 0, 100), 2)
)

Generating a check_in_number variable...

tst2 <- tst %>%
  arrange(date) %>%
  group_by(name, date) %>%
  mutate(check_in_number = row_number())

The line above will generate check_in_number for Bob as 1, 1, 2, 1 in that order. I would instead like the output to be 1, 2, 2, 3. In other words. I would like check-in instances on the same date to be considered a single check-in.

Is this possible with tidyverse? Am I overlooking a simple method for this?

---

There is a similar question here, but I am leaving this up because the problem I had involved an ordered date variable on which I was arranging the data. In other words, my data required my new variable to be consecutive.

How to number/label data-table by group-number from group_by?

Answer 1

You need group_indices:

library(tidyverse)

tst <- tibble(
  name = c("Bob", "Sam", "Roger", "Stacy", "Roger", "Roger", "Sam", "Bob", "Sam", "Stacy", "Bob", "Stacy", "Roger", "Bob"),
  date = as.Date(c("2009-07-03", "2010-08-12", "2009-07-03", "2016-04-01", "2002-01-03", "2019-02-10", "2005-04-17", "2009-07-03", "2010-09-21", "2012-11-12", "2015-12-31", "2014-10-10", "2015-06-02", "2003-08-21")),
  amount = round(runif(14, 0, 100), 2)
)

tst %>%
  arrange(name, date) %>%
  mutate(check_in_number = group_indices(., name, date))
#> # A tibble: 14 x 4
#>    name  date       amount check_in_number
#>    <chr> <date>      <dbl>           <int>
#>  1 Bob   2003-08-21  91.1                1
#>  2 Bob   2009-07-03  38.1                2
#>  3 Bob   2009-07-03  28.3                2
#>  4 Bob   2015-12-31  22.3                3
#>  5 Roger 2002-01-03  68.3                4
#>  6 Roger 2009-07-03  83.8                5
#>  7 Roger 2015-06-02  94.2                6
#>  8 Roger 2019-02-10  48.8                7
#>  9 Sam   2005-04-17  16.6                8
#> 10 Sam   2010-08-12  93.2                9
#> 11 Sam   2010-09-21  65.5               10
#> 12 Stacy 2012-11-12  92.6               11
#> 13 Stacy 2014-10-10  84.4               12
#> 14 Stacy 2016-04-01   7.43              13

If you need the numbering to restart on each name, you can rescale based on the first value in each name:

tst %>%
  arrange(name, date) %>%
  mutate(check_in_number = group_indices(., name, date)) %>%
  group_by(name) %>%
  mutate(check_in_number = check_in_number - first(check_in_number) + 1)
#> # A tibble: 14 x 4
#> # Groups:   name [4]
#>    name  date       amount check_in_number
#>    <chr> <date>      <dbl>           <dbl>
#>  1 Bob   2003-08-21  91.1                1
#>  2 Bob   2009-07-03  38.1                2
#>  3 Bob   2009-07-03  28.3                2
#>  4 Bob   2015-12-31  22.3                3
#>  5 Roger 2002-01-03  68.3                1
#>  6 Roger 2009-07-03  83.8                2
#>  7 Roger 2015-06-02  94.2                3
#>  8 Roger 2019-02-10  48.8                4
#>  9 Sam   2005-04-17  16.6                1
#> 10 Sam   2010-08-12  93.2                2
#> 11 Sam   2010-09-21  65.5                3
#> 12 Stacy 2012-11-12  92.6                1
#> 13 Stacy 2014-10-10  84.4                2
#> 14 Stacy 2016-04-01   7.43               3

^{Created on 2019-06-18 by the reprex package (v0.3.0)}

Answer 2

An option with data.table

library(data.table)
setDT(tst)[order(name, date)][, check_in_number := .GRP, .(name, date)][]
#      name       date amount check_in_number
# 1:   Bob 2003-08-21  66.36               1
# 2:   Bob 2009-07-03  22.18               2
# 3:   Bob 2009-07-03  96.15               2
# 4:   Bob 2015-12-31  31.64               3
# 5: Roger 2002-01-03  92.32               4
# 6: Roger 2009-07-03  41.85               5
# 7: Roger 2015-06-02  15.46               6
# 8: Roger 2019-02-10  80.38               7
# 9:   Sam 2005-04-17  49.18               8
#10:   Sam 2010-08-12  73.57               9
#11:   Sam 2010-09-21  49.37              10
#12: Stacy 2012-11-12  24.82              11
#13: Stacy 2014-10-10  23.31              12
#14: Stacy 2016-04-01  80.12              13

---

If we need to restart the numbering

setDT(tst)[order(name, date)][, check_in_number := .GRP, 
   .(name, date)][,  check_in_number := match(check_in_number, 
          unique(check_in_number)), .(name)][]
#      name       date amount check_in_number
# 1:   Bob 2003-08-21  66.36               1
# 2:   Bob 2009-07-03  22.18               2
# 3:   Bob 2009-07-03  96.15               2
# 4:   Bob 2015-12-31  31.64               3
# 5: Roger 2002-01-03  92.32               1
# 6: Roger 2009-07-03  41.85               2
# 7: Roger 2015-06-02  15.46               3
# 8: Roger 2019-02-10  80.38               4
# 9:   Sam 2005-04-17  49.18               1
#10:   Sam 2010-08-12  73.57               2
#11:   Sam 2010-09-21  49.37               3
#12: Stacy 2012-11-12  24.82               1
#13: Stacy 2014-10-10  23.31               2
#14: Stacy 2016-04-01  80.12               3

data

tst <- data_frame(
  name = c("Bob", "Sam", "Roger", "Stacy", "Roger", "Roger", "Sam", "Bob", "Sam", "Stacy", "Bob", "Stacy", "Roger", "Bob"),
  date = as.Date(c("2009-07-03", "2010-08-12", "2009-07-03", "2016-04-01", "2002-01-03", "2019-02-10", "2005-04-17", "2009-07-03", "2010-09-21", "2012-11-12", "2015-12-31", "2014-10-10", "2015-06-02", 
    "2003-08-21")),
  amount = round(runif(14, 0, 100), 2)
)