Carry / use value from previous group

Question

data:

structure(list(id = c(1, 1, 2, 2, 2, 2, 2, 2, 3, 3, 4, 4, 5), 
               ax = c("a", "a", "b", "b", "b", "b", "b", "b", "c", "c", 
                      "d", "d", "e"), time = c(1, 3, 0, 2, 4, 5, 6, 8, 7, 9, 10, 
                                               11, 12)), .Names = c("id", "ax", "time"), class = c("data.table", 
                                                                                                   "data.frame"), row.names = c(NA, -13L))

looks like:

    id ax time
 1:  1  a    1
 2:  1  a    3
 3:  2  b    0
 4:  2  b    2
 5:  2  b    4
 6:  2  b    5
 7:  2  b    6
 8:  2  b    8
 9:  3  c    7
10:  3  c    9
11:  4  d   10
12:  4  d   11
13:  5  e   12

I want to have the max of the previous group next to the actual group:

desired output:

    id ax time newCol
 1:  1  a    1     NA
 2:  1  a    3     NA
 3:  2  b    0      3
 4:  2  b    2      3
 5:  2  b    4      3
 6:  2  b    5      3
 7:  2  b    6      3
 8:  2  b    8      3
 9:  3  c    7      8
10:  3  c    9      8
11:  4  d   10      9
12:  4  d   11      9
13:  5  e   12     11

Is it also possible to have the value of the "previous-previous" grp?

Interessted in baseR, data.table and tidyverse solutions

note:

Can be grouped by EITHER id or ax. The example is a little redundant here.

Answer 1

A data.table solution:

dtt.max <- dtt[, .(max = max(time)), by = ax]
dtt.max[, max.prev := shift(max)]

dtt[dtt.max, newCol := i.max.prev, on = 'ax']
# > dtt
#     id ax time newCol
#  1:  1  a    1     NA
#  2:  1  a    3     NA
#  3:  2  b    0      3
#  4:  2  b    2      3
#  5:  2  b    4      3
#  6:  2  b    5      3
#  7:  2  b    6      3
#  8:  2  b    8      3
#  9:  3  c    7      8
# 10:  3  c    9      8
# 11:  4  d   10      9
# 12:  4  d   11      9
# 13:  5  e   12     11

Answer 2

data.table solution using id + 1

library(data.table)
merge(d, setDT(d)[, max(time), id + 1], all.x = TRUE)

Answer 3

Here is a dplyr approach. The key here is to group and ungroup when necessary:

df %>% 
  group_by(ax) %>% 
  mutate(new = time[n()]) %>%
  ungroup() %>%
  mutate(new = lag(new)) %>%
  group_by(ax) %>%
  mutate(new = new[1])

# A tibble: 13 x 4
# Groups:   ax [5]
      id ax     time   new
   <dbl> <chr> <dbl> <dbl>
 1    1. a        1.   NA 
 2    1. a        3.   NA 
 3    2. b        0.    3.
 4    2. b        2.    3.
 5    2. b        4.    3.
 6    2. b        5.    3.
 7    2. b        6.    3.
 8    2. b        8.    3.
 9    3. c        7.    8.
10    3. c        9.    8.
11    4. d       10.    9.
12    4. d       11.    9.
13    5. e       12.   11.