1

I have the following tibble:

my_tbl <- tribble(
  ~year, ~event_id, ~winner_id,
  2011,      "A",     4322,
  2011,      "A",     9604,
  2011,      "A",     1180,
  2013,      "A",     4322,
  2013,      "A",     9604,
  2013,      "A",     1663,
  2014,      "A",     4322,
  2016,      "A",     5478,
  2017,      "A",     4322,
  2017,      "A",     1663,
  2011,      "B",     4322,
  2013,      "B",     7893,
  2013,      "B",     1188,
  2014,      "B",     7893,
  2016,      "B",     2365,
  2017,      "B",     3407,
  2011,      "C",     5556,
  2013,      "C",     5556,
  2014,      "C",     1238,
  2016,      "C",     2391,
  2017,      "C",     2391,
  2011,      "D",     4219,
  2013,      "D",     7623,
  2014,      "D",     8003,
  2016,      "D",     2851,
  2017,      "D",     0418
)

For each event, I would like to find the longuest streak using either the lag() and/or lead() functions. The expected output should look like this:

results_summary_tbl <- tribble(
  ~event_id, ~most_wins_in_a_row, ~number_of_winners, ~winners,                              ~years,
   "A",       3,                  1,                   "4322",                               "4322 = (2011, 2013, 2014)",
   "C",       2,                  2,                   "5556 , 2391",                        "5556 = (2011, 2013), 2391 = (2015, 2016)",
   "B",       2,                  1,                   "7893",                               "7893 = (2013, 2014)",
   "D",       1,                  5,                   "4219 , 7623 , 8003 , 2851 , 0418",   "4219 = (2011), 7623 = (2013), 8003 = (2014), 2851 = (2016), 0418 = (2017)"
)

Thanks

lmcjfal
  • 85
  • 5
  • How are you getting 3 wins in a row for "4332"? None of them look consecutive? – Chris Nov 21 '19 at 17:20
  • 9
    So this is just [this question](https://stackoverflow.com/questions/58799951/how-to-find-the-longest-duplicate-sequence-in-a-tibble-column-follow-up-questio) again but you're asking to do it with just `lag` and `lead`? Not clear why you'd want to do it that way, computing run-lengths is a problem basically tailor-made for the `rle` (run-length encoding) function. – IceCreamToucan Nov 21 '19 at 17:22
  • The event "A" did not take place in 2012. Winner 4322 still wont it 3 times in a row. – lmcjfal Nov 21 '19 at 17:23
  • Yes, lead or lag are the preferred option – lmcjfal Nov 21 '19 at 17:24

0 Answers0