How to count the maximum number of simultaneously present entities by column value?

Question

I have a data frame that shows the arrival and departure (type), the time (time) of the vehicles (vehicle_id) on a network of multiple roads (links).

My goal is to see how many vehicles are maximum at the same time present on a link to determine the minimum number of parking lots per link.

For clarification; type=="vehicle enters traffic" means the car leaves the parking lot. type=="vehicle leaves traffic" means it enters the parking lot.

I dont know how to write the code, I assume with dplyr it could be done, but my approach would be like this:

1. group by link
2. sort the subset with increasing time (chronological order of events)
3. If type=="vehicle enters traffic", which means the vehicle was there before, I already know one parking lot is present. So for the first seconds, i know how many stayed overnight (time 0 is midnight).
4. Every time a type=="vehicle leaves traffic" on the link without type=="vehicle enters traffic", the maximum number of parking lot increases.

I hope this is more or less clear.

In the end I desire a simple df with a column for the link number and a column with the number of maximum present vehicles.

I very much appreciate your help!

Data to try:

structure(list(time = c(25466, 29180, 29181, 39304, 40237, 40818, 
42895, 52773, 53829, 59208, 59239, 68389, 24607, 25218, 29947, 
37637, 42623, 43930, 43965, 46223, 51518, 57738, 63527, 65623, 
27505, 28177, 28499, 60605, 63184, 63252, 20959, 26376, 47805, 
49549, 50644, 61197, 78557, 79249, 29169, 31411, 34626, 36747, 
38807, 44618, 58068, 58800, 65431, 69056, 46559, 69920, 20445, 
23764, 24017, 24281, 25301, 27161, 27289, 28783, 28910, 29241, 
29393, 29489, 29549, 29909, 30731, 31092, 31356, 31786, 32829, 
33966, 34094, 34545, 34888, 35775, 35950, 38409, 38636, 39259, 
39527, 40256, 40385, 40564, 40691, 40774, 42271, 42461, 43103, 
43223, 44476, 44903, 44904, 45774, 45834, 45915, 46194, 46626, 
47215, 48784, 50266, 50361, 50763, 52685, 52793, 54688, 55105, 
56310, 57885, 58046, 58118, 59223, 60460, 60597, 60676, 61307, 
61446, 62165, 62347, 62591, 63325, 64155, 65555, 65808, 66038, 
66723, 66729, 66789, 66790, 67502, 67989, 68322, 69318, 69639, 
70450, 70634, 71741, 72121, 72292, 73236, 73775, 74280, 80298, 
80458, 80976, 81035, 84189, 84302, 23333, 32549, 34024, 57385, 
26137, 26163, 27473, 28303, 29206, 31052, 34019, 36023, 36392, 
40717, 43182, 44063, 44064, 44709, 44770, 46863, 48318, 48493, 
51718, 52221, 52599, 52923, 53443, 54494, 55053, 58381, 62610, 
63050, 63050, 63417, 63488, 64067, 65090, 65090, 65185, 66571, 
67715, 68247, 68921, 68955, 70123, 70263, 71359, 77855, 23423, 
23770, 29044, 29053, 32320, 32735, 66884, 67902, 68763, 69206, 
34426, 64393, 32354, 64161, 67761, 25657, 26173, 33923, 35452, 
36914, 38663, 43256, 49564, 50619, 55540, 56403, 73247, 73873, 
77960, 41750, 44879, 47284, 48132, 49418, 50628, 57823, 57824, 
62292, 62293, 66103, 78862, 35841, 42188, 42719, 43543, 23581, 
27573, 45463, 48723, 49182, 52246, 53664, 60310, 61756, 63941, 
64363, 68181, 68182, 80056, 26885, 44885, 45701, 49570, 50407, 
52044, 53566, 55712, 56463, 59266, 63829, 65907, 44795, 52245, 
21619, 21897, 24808, 27283, 28827, 29304, 30437, 31426, 31673, 
32971, 33595, 33962, 39556, 39624, 40347, 41473, 42396, 43000, 
43377, 46795, 47635, 53664, 57927, 57942, 58559, 59187, 59932, 
60068, 61162, 61730, 65144, 65281, 67215), type = structure(c(1L, 
2L, 1L, 2L, 2L, 1L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 1L, 2L, 2L, 1L, 
2L, 1L, 1L, 2L, 1L, 2L, 2L, 2L, 2L, 2L, 1L, 1L, 1L, 2L, 2L, 2L, 
1L, 2L, 1L, 1L, 1L, 2L, 1L, 2L, 2L, 1L, 1L, 2L, 1L, 2L, 1L, 1L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 1L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 2L, 1L, 2L, 2L, 1L, 1L, 2L, 1L, 1L, 1L, 2L, 1L, 1L, 1L, 
2L, 1L, 1L, 1L, 2L, 2L, 1L, 1L, 2L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 
2L, 1L, 2L, 2L, 1L, 2L, 1L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 1L, 
1L, 2L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 1L, 2L, 2L, 2L, 1L, 1L, 1L, 
2L, 1L, 2L, 1L, 2L, 2L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 1L, 1L, 1L, 
1L, 2L, 2L, 1L, 1L, 2L, 1L, 1L, 1L, 2L, 1L, 2L, 2L, 1L, 2L, 2L, 
2L, 1L, 2L, 1L, 1L, 1L, 1L, 2L, 1L, 2L, 2L, 1L, 1L, 2L, 1L, 2L, 
1L, 2L, 2L, 2L, 2L, 1L, 2L, 1L, 1L, 2L, 2L, 1L, 2L, 1L, 1L, 2L, 
1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 2L, 1L, 1L, 1L, 2L, 1L, 1L, 2L, 
1L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 2L, 1L, 2L, 1L, 2L, 2L, 1L, 
2L, 1L, 1L, 2L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 2L, 
1L, 2L, 2L, 2L, 1L, 1L, 2L, 1L, 1L, 2L, 1L, 1L, 2L, 1L, 2L, 2L, 
2L, 1L, 1L, 2L, 1L, 1L, 2L, 1L, 1L, 2L, 1L, 2L, 2L, 2L, 2L, 1L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 1L, 2L, 1L, 1L, 1L, 1L, 1L, 2L, 1L, 
1L, 2L, 1L, 1L, 2L, 1L, 1L, 2L, 1L, 2L, 1L), .Label = c("vehicle enters traffic", 
"vehicle leaves traffic"), class = "factor"), vehicle_id = c(1267068L, 
810534L, 810534L, 532654L, 1111105L, 1111105L, 532654L, 46491L, 
46491L, 4205L, 4205L, 1267068L, 1454119L, 1412246L, 1533113L, 
1430553L, 1430553L, 1430553L, 1533113L, 1430553L, 492533L, 492533L, 
1454119L, 1412246L, 1290940L, 628509L, 558745L, 628509L, 558745L, 
1290940L, 403850L, 774916L, 1397256L, 403850L, 3874L, 774916L, 
1397256L, 3874L, 1043798L, 1043798L, 1221124L, 1881121L, 1881121L, 
1221124L, 12799L, 12799L, 2066556L, 2066556L, 488147L, 488147L, 
1925302L, 2821L, 1919641L, 2147547L, 1785664L, 1869032L, 1812540L, 
1531804L, 1814856L, 1531804L, 2149105L, 1747951L, 1908352L, 1854886L, 
1888344L, 1926462L, 1925659L, 1887358L, 1926462L, 1863281L, 1094609L, 
1888344L, 1925659L, 2148165L, 1863281L, 2148165L, 1814856L, 1885007L, 
1094609L, 1887358L, 1925302L, 1925659L, 1908352L, 1919641L, 1885007L, 
1898426L, 2095866L, 1812540L, 2149105L, 1799420L, 1799420L, 1898426L, 
2095866L, 1905635L, 1859644L, 1187619L, 1794294L, 1908352L, 1187619L, 
2149105L, 1901830L, 1859644L, 1885718L, 1925659L, 4806833L, 1901830L, 
1794294L, 4806833L, 2821L, 1905635L, 1785664L, 1887788L, 2149105L, 
1885718L, 1912658L, 1869032L, 2147547L, 1908352L, 1670344L, 2064554L, 
1902958L, 1888247L, 1888247L, 1898186L, 1840443L, 1378838L, 1378838L, 
1747951L, 1887788L, 5259385L, 2064554L, 42794L, 1912658L, 1887316L, 
1860654L, 1902958L, 1854886L, 5259385L, 1887316L, 1670344L, 42794L, 
1921295L, 1921295L, 1860654L, 1840443L, 1898186L, 80518L, 1131784L, 
1131784L, 80518L, 29916L, 29916L, 519878L, 525457L, 523658L, 
523658L, 526214L, 29916L, 526214L, 479492L, 1856482L, 568524L, 
568524L, 693877L, 479492L, 29916L, 1856482L, 693877L, 520491L, 
520491L, 513349L, 29916L, 513349L, 532803L, 530834L, 526134L, 
398460L, 519877L, 519877L, 533986L, 476491L, 525457L, 519877L, 
519877L, 398460L, 530834L, 519878L, 532803L, 476491L, 537161L, 
533986L, 29916L, 526134L, 537161L, 273572L, 273572L, 861460L, 
861460L, 216294L, 216294L, 1535243L, 230658L, 230658L, 1535243L, 
1428056L, 1428056L, 1140384L, 46083L, 1140384L, 1257243L, 1455977L, 
22182L, 22182L, 1461901L, 1461901L, 1257243L, 1257242L, 1257242L, 
1196859L, 1196859L, 1455977L, 1455977L, 1455977L, 1903824L, 1903824L, 
566328L, 566328L, 1199966L, 1199966L, 1536111L, 1536111L, 2006467L, 
2006467L, 6035493L, 6035493L, 5858613L, 5858613L, 1160146L, 1160146L, 
1747160L, 1811944L, 1811944L, 4682113L, 1811944L, 1058129L, 1058129L, 
4682113L, 1792899L, 1747160L, 1811944L, 1747496L, 1747496L, 1792899L, 
1960095L, 1960095L, 1960095L, 311665L, 1794698L, 1794698L, 311665L, 
1720159L, 1720159L, 1960095L, 1886261L, 1886261L, 1418046L, 1418046L, 
1333818L, 1269837L, 1375263L, 26265L, 1318735L, 1318735L, 1858598L, 
1923074L, 13778L, 1162037L, 1043247L, 1173884L, 1883621L, 13778L, 
11914L, 1375263L, 1209156L, 1883621L, 1858598L, 1923074L, 1360458L, 
1333820L, 26265L, 1333818L, 1162037L, 11914L, 1333820L, 1043247L, 
1269837L, 14229L, 1173884L, 1882652L, 1360458L), link = c(90L, 
90L, 90L, 90L, 90L, 90L, 90L, 90L, 90L, 90L, 90L, 90L, 389L, 
389L, 389L, 389L, 389L, 389L, 389L, 389L, 389L, 389L, 389L, 389L, 
451L, 451L, 451L, 451L, 451L, 451L, 480L, 480L, 480L, 480L, 480L, 
480L, 480L, 480L, 578L, 578L, 578L, 578L, 578L, 578L, 578L, 578L, 
578L, 578L, 662L, 662L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 
723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 
723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 
723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 
723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 
723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 
723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 
723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 
723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 
723L, 774L, 774L, 774L, 774L, 859L, 859L, 859L, 859L, 859L, 859L, 
859L, 859L, 859L, 859L, 859L, 859L, 859L, 859L, 859L, 859L, 859L, 
859L, 859L, 859L, 859L, 859L, 859L, 859L, 859L, 859L, 859L, 859L, 
859L, 859L, 859L, 859L, 859L, 859L, 859L, 859L, 859L, 859L, 859L, 
859L, 859L, 859L, 859L, 859L, 927L, 927L, 927L, 927L, 927L, 927L, 
927L, 927L, 927L, 927L, 987L, 987L, 988L, 988L, 988L, 1277L, 
1277L, 1277L, 1277L, 1277L, 1277L, 1277L, 1277L, 1277L, 1277L, 
1277L, 1277L, 1277L, 1277L, 1476L, 1476L, 1476L, 1476L, 1476L, 
1476L, 1476L, 1476L, 1476L, 1476L, 1476L, 1476L, 1478L, 1478L, 
1478L, 1478L, 1706L, 1706L, 1706L, 1706L, 1706L, 1706L, 1706L, 
1706L, 1706L, 1706L, 1706L, 1706L, 1706L, 1706L, 1713L, 1713L, 
1713L, 1713L, 1713L, 1713L, 1713L, 1713L, 1713L, 1713L, 1713L, 
1713L, 2472L, 2472L, 2482L, 2482L, 2482L, 2482L, 2482L, 2482L, 
2482L, 2482L, 2482L, 2482L, 2482L, 2482L, 2482L, 2482L, 2482L, 
2482L, 2482L, 2482L, 2482L, 2482L, 2482L, 2482L, 2482L, 2482L, 
2482L, 2482L, 2482L, 2482L, 2482L, 2482L, 2482L, 2482L, 2482L
)), row.names = c(23238L, 42556L, 42563L, 81015L, 84161L, 86265L, 
95521L, 137820L, 141613L, 163931L, 164087L, 216307L, 18965L, 
21973L, 46226L, 75475L, 94175L, 100651L, 100815L, 111088L, 133134L, 
156818L, 189497L, 202055L, 33842L, 37370L, 39040L, 171595L, 187438L, 
187844L, 5468L, 27947L, 117806L, 125360L, 129715L, 175097L, 241042L, 
241962L, 42500L, 52783L, 65128L, 72377L, 79465L, 103961L, 158322L, 
161850L, 200947L, 219162L, 112417L, 222619L, 4375L, 15072L, 16200L, 
17395L, 22407L, 32033L, 32706L, 40478L, 41133L, 42874L, 43583L, 
44072L, 44337L, 46039L, 49745L, 51373L, 52526L, 54342L, 58676L, 
62850L, 63317L, 64867L, 66035L, 69170L, 69761L, 78130L, 78885L, 
80865L, 81774L, 84223L, 84679L, 85315L, 85792L, 86117L, 92427L, 
93399L, 96540L, 97130L, 103259L, 105331L, 105336L, 109169L, 109451L, 
109813L, 110965L, 112683L, 115212L, 122086L, 128227L, 128591L, 
130215L, 137505L, 137888L, 144612L, 146164L, 150731L, 157472L, 
158213L, 158602L, 164005L, 170740L, 171536L, 172007L, 175750L, 
176593L, 181026L, 182216L, 183756L, 188258L, 193346L, 201644L, 
203116L, 204405L, 208077L, 208109L, 208419L, 208425L, 211997L, 
214395L, 215965L, 220248L, 221537L, 224574L, 225219L, 228716L, 
229792L, 230292L, 232476L, 233674L, 234734L, 243350L, 243574L, 
244226L, 244294L, 247553L, 247644L, 13209L, 57522L, 63071L, 155252L, 
26694L, 26839L, 33677L, 38038L, 42692L, 51204L, 63048L, 69987L, 
71140L, 85891L, 96919L, 101289L, 101294L, 104414L, 104689L, 113653L, 
120044L, 120801L, 133881L, 135808L, 137165L, 138335L, 140184L, 
143967L, 145971L, 159850L, 183879L, 186653L, 186654L, 188805L, 
189240L, 192828L, 198961L, 198962L, 199507L, 207302L, 213037L, 
215619L, 218593L, 218735L, 223408L, 223918L, 227558L, 240109L, 
13584L, 15098L, 41858L, 41911L, 56556L, 58294L, 208921L, 213974L, 
217961L, 219770L, 64477L, 194837L, 56728L, 193388L, 213272L, 
24235L, 26897L, 62706L, 68026L, 72951L, 78988L, 97296L, 125423L, 
129612L, 147786L, 151099L, 232501L, 233905L, 240245L, 89980L, 
105210L, 115503L, 119239L, 124778L, 129647L, 157204L, 157210L, 
181836L, 181845L, 204756L, 241443L, 69403L, 92041L, 94643L, 98793L, 
14292L, 34219L, 107838L, 121810L, 123716L, 135908L, 141019L, 
169910L, 178466L, 192072L, 194657L, 215315L, 215320L, 243029L, 
30590L, 105241L, 108824L, 125451L, 128782L, 135129L, 140632L, 
148441L, 151345L, 164243L, 191385L, 203666L, 104817L, 135901L, 
7216L, 8092L, 19915L, 32672L, 40686L, 43160L, 48417L, 52850L, 
53867L, 59198L, 61526L, 62837L, 81879L, 82110L, 84554L, 88640L, 
93066L, 96036L, 97944L, 113344L, 117026L, 141017L, 157645L, 157714L, 
160682L, 163809L, 167774L, 168536L, 174921L, 178302L, 199276L, 
200098L, 210574L), class = "data.frame")

Desired result based on the first 3 links:

link    max_vehicles
90      2
389     2
451     3
...

Answer 1

This looks a bit cumbersome but it does what you need. It basically groups by link and type BUT in a sense where everytime the type changes, it is a new group (hence the rleid() from data.table - We can avoid loading an extra package and implement it ourselves with a combination of cumsum and diff, or use the base R rle with some modifications, but I went with the fast option). We then sort on time, filter out the rows where cars are leaving the parking lots and count the number of rows in each group. We then re-group by link in order to remove the grouping of rleid() and filter to get the one with the maximum value (slice(which.max())). Finally we remove the extra column created for the rleid() grouping.

library(dplyr)

df %>% 
 group_by(link, grp = data.table::rleid(type)) %>% 
 arrange(time) %>% 
 filter(type == 'vehicle leaves traffic') %>% 
 count() %>% 
 group_by(link) %>% 
 slice(which.max(n)) %>% 
 select(-grp)

# A tibble: 19 x 2
# Groups:   link [19]
#    link     n
#   <int> <int>
# 1    90     2
# 2   389     2
# 3   451     3
# 4   480     3
# 5   578     2
# 6   662     1
# 7   723     9
# 8   774     2
# 9   859     4
#10   927     2
#11   987     1
#12   988     1
#13  1277     2
#14  1476     2
#15  1478     1
#16  1706     3
#17  1713     3
#18  2472     1
#19  2482     7