R: Using doBy with Dates

Question

I am doing some coding in R. I am trying to use the doBy package to get a sum total score for a variable (x) by both date (date) and by id (id). The doBy command works fine and I get this output.

data
id date       x
1  01/01/2021 1
1  01/02/2021 2
1  01/03/2021 3
2  02/01/2021 2
2  02/02/2021 3
2  02/02/2021 4
3  03/11/2021 3
3  03/12/2021 3
3  03/13/2021 2

I want to recode the date so that everyone's first chronological date 1 is 1, the chronological second date is 2, the chronological 3rd date is 3, etc. I want my date to look something like this.

data2
id daycount x
1  1        1
1  2        2
1  3        3
2  1        2
2  2        3
2  3        4
3  1        3
3  2        3
3  3        2

I was able to order the days using order() but I am not sure how to get the dates to match up. I think I need some kind of sequence or autonumber. Also, some participants may have different number of days. Some participants may have 1 day and other participants may have 10 days.

score 1 · Accepted Answer · answered Jun 15 '21 at 19:55

1) doBy Assuming that the dates are already sorted within id:

library(doBy)
transform_by(data, ~ id, countdays = seq_along(id))

giving:

  id       date x countdays
1  1 01/01/2021 1         1
2  1 01/02/2021 2         2
3  1 01/03/2021 3         3
4  2 02/01/2021 2         1
5  2 02/02/2021 3         2
6  2 02/02/2021 4         3
7  3 03/11/2021 3         1
8  3 03/12/2021 3         2
9  3 03/13/2021 2         3

2) Base R It could also be done using transform and ave in base R.

transform(data, daycount = ave(id, id, FUN = seq_along))

giving:

  id       date x daycount
1  1 01/01/2021 1        1
2  1 01/02/2021 2        2
3  1 01/03/2021 3        3
4  2 02/01/2021 2        1
5  2 02/02/2021 3        2
6  2 02/02/2021 4        3
7  3 03/11/2021 3        1
8  3 03/12/2021 3        2
9  3 03/13/2021 2        3

Note

data in reproducible form:

Lines <- "id date       x
1  01/01/2021 1
1  01/02/2021 2
1  01/03/2021 3
2  02/01/2021 2
2  02/02/2021 3
2  02/02/2021 4
3  03/11/2021 3
3  03/12/2021 3
3  03/13/2021 2"
data <- read.table(text = Lines, header = TRUE)

Thank you. I will be sure to include sample code to reproduce my data for future questions. — Eric Boorman, Dec 07 '21 at 18:22

Marta Karas · Answer 2 · 2021-06-15T20:00:21.360

You may want to use group_by by id and then create a new column using rank or dense_rank (note the difference with them with handling the duplicates).

To recreate your data, I used:

# recreate data frame
id_vec <- rep(c(1,2,3), each = 3)
date_vec <- c(
  '01/01/2021',
  '01/02/2021',
  '01/03/2021',
  '02/01/2021',
  '02/02/2021',
  '02/02/2021',
  '03/11/2021',
  '03/12/2021',
  '03/13/2021' 
)
x_vec <- rep(c(1,2,3), times = 3)
data <- data.frame(id = id_vec, date = date_vec, x = x_vec)

I also converted the data column to an actual date format for your convenience:

# convert string to date object
library(lubridate)
library(dplyr)
data <- data %>% mutate(date_formatted = mdy(date))

Creating a column with rank:

data %>%
  group_by(id) %>%
  mutate(day_count = rank(date_formatted, ties.method = "first")) %>%
  ungroup()
# # A tibble: 9 x 5
# id date           x date_formatted day_count
# <dbl> <chr>      <dbl> <date>             <int>
# 1     1 01/01/2021     1 2021-01-01             1
# 2     1 01/02/2021     2 2021-01-02             2
# 3     1 01/03/2021     3 2021-01-03             3
# 4     2 02/01/2021     1 2021-02-01             1
# 5     2 02/02/2021     2 2021-02-02             2
# 6     2 02/02/2021     3 2021-02-02             3
# 7     3 03/11/2021     1 2021-03-11             1
# 8     3 03/12/2021     2 2021-03-12             2
# 9     3 03/13/2021     3 2021-03-13             3

Creating new column with dense_rank:

data %>%
  group_by(id) %>%
  mutate(day_count = dense_rank(date_formatted)) %>%
  ungroup()
# # A tibble: 9 x 5
# id date           x date_formatted day_count
# <dbl> <chr>      <dbl> <date>             <int>
# 1     1 01/01/2021     1 2021-01-01             1
# 2     1 01/02/2021     2 2021-01-02             2
# 3     1 01/03/2021     3 2021-01-03             3
# 4     2 02/01/2021     1 2021-02-01             1
# 5     2 02/02/2021     2 2021-02-02             2
# 6     2 02/02/2021     3 2021-02-02             2
# 7     3 03/11/2021     1 2021-03-11             1
# 8     3 03/12/2021     2 2021-03-12             2
# 9     3 03/13/2021     3 2021-03-13             3

R: Using doBy with Dates

2 Answers2

Note