Reverse Column based on ID

March 7, 2023

I have a dataset with some recurring IDs (there are different frequencies of IDs). There also is a column which shows the nth occurrence of each ID.
I am trying to find a quick way to create a new column which will reverse that time column for each ID.
For example for the following vectors i would expect them to look like this:

(1, 1, 1, 2, 3, 3, 3, 3, 3) = id  
(1, 2, 3, 1, 1, 2, 3, 4, 5) = time  
(3, 2, 1, 1, 5, 4, 3, 2, 1) = time_reverse

I posted a reproducible example below.

df <- data.frame(
  id = rep(c(1, 2, 3, 4, 5), times = c(3, 1, 5, 2, 7)),
  time = c(1, 2, 3, 1, 1, 2,  3, 4, 5, 1, 2, 1, 2, 3, 4, 5, 6, 7), 
  time_reverse = sample(NA, size = 18, replace = TRUE)
)

Thanks in advance 🙂

>Solution :

dplyr

You can use row_number and rev by group:

library(dplyr)
df %>% 
  group_by(id) %>% 
  mutate(time = row_number(), 
         time_reverse = rev(time))

base R

transform(df,
          time = ave(id, id, FUN = seq_along),
          time_reverse = ave(time, id, FUN = rev))

      id  time time_reverse
 1     1     1            3
 2     1     2            2
 3     1     3            1
 4     2     1            1
 5     3     1            5
 6     3     2            4
 7     3     3            3
 8     3     4            2
 9     3     5            1
10     4     1            2
11     4     2            1
12     5     1            7
13     5     2            6
14     5     3            5
15     5     4            4
16     5     5            3
17     5     6            2
18     5     7            1