Yves
Reputation: 556
How to manipulate the value of a row based on the condition and the row above?
I have a df and I want to group it by links and sort it by time. Then, everytime type == 'vehicle leaves traffic' the cell in the column count should add +1 of the cell value in the preceding row. If type == 'vehicle enters traffic' it should deduct 1 from the preceding row.
For clarification, not the preceding row's value should be changed, but the value of the row should be changed based on the preceding value.
This is my approach, but all I get is 0, 1 and 2. I'm expecting much higher values for some links.
parking_min <- cars %>%
group_by(link)%>%
dplyr::mutate(count = if_else(type == 'vehicle leaves traffic', lag(count, n=1,order_by=time)+1,lag(count))) %>%
dplyr::mutate(count = if_else(type == 'vehicle enters traffic',lag(count, n=1, order_by=time)-1,lag(count)))
This is my data:
structure(list(time = c("23707.0", "31209.0", "31210.0", "36230.0",
"36231.0", "38925.0", "39583.0", "40198.0", "40818.0", "41974.0",
"42895.0", "43099.0", "43683.0", "44645.0", "45730.0", "46785.0",
"48846.0", "48905.0", "52790.0", "53829.0", "55021.0", "58240.0",
"58635.0", "59682.0", "59683.0", "63740.0", "63776.0", "68607.0",
"24607.0", "25218.0", "26442.0", "28004.0", "29884.0", "37750.0",
"42623.0", "43965.0", "49426.0", "54925.0", "56688.0", "56689.0",
"57738.0", "61900.0", "64221.0", "67065.0", "69404.0", "77454.0",
"83588.0", "89601.0", "27452.0", "27743.0", "33598.0", "36297.0",
"60604.0", "62940.0", "63184.0", "63250.0", "20911.0", "27013.0",
"29815.0", "49550.0", "53991.0", "55620.0", "61200.0", "67672.0",
"78558.0", "79245.0", "27006.0", "29085.0", "31411.0", "36747.0",
"36877.0", "38434.0", "38807.0", "44607.0", "58068.0", "58800.0",
"65236.0", "65431.0", "69013.0", "69072.0", "25609.0", "29520.0",
"46559.0", "66916.0", "74904.0", "78407.0", "20445.0", "23938.0",
"24017.0", "24281.0", "25283.0", "25873.0", "27137.0", "27342.0",
"28790.0", "28910.0", "29241.0", "29345.0", "29465.0", "29525.0",
"29909.0", "30719.0", "31092.0", "31356.0", "31786.0", "32829.0",
"33966.0", "34175.0", "34545.0", "34888.0", "34977.0", "35775.0",
"35950.0", "38409.0", "38636.0", "39259.0", "39527.0", "40256.0",
"40385.0", "40564.0", "40691.0", "40774.0", "42271.0", "42469.0",
"42895.0", "43103.0", "43223.0", "43599.0", "44476.0", "44903.0",
"44904.0", "45774.0", "45834.0", "45915.0", "46230.0", "46287.0",
"46626.0", "47215.0", "48784.0", "50266.0", "50361.0", "50763.0",
"52685.0", "52793.0", "54688.0", "55105.0", "56310.0", "57885.0",
"58093.0", "58153.0", "59223.0", "60460.0", "60597.0", "60676.0",
"61307.0", "61457.0", "61974.0", "62141.0", "62165.0", "62347.0",
"62591.0", "64175.0", "64280.0", "65555.0", "65808.0", "66038.0",
"66391.0", "66723.0", "66735.0", "66736.0", "66933.0", "67502.0",
"67989.0", "68322.0", "68785.0", "69318.0", "70450.0", "70634.0",
"71069.0", "71741.0", "72121.0", "72292.0", "73236.0", "73775.0",
"74280.0", "80298.0", "80458.0", "80976.0", "81035.0", "84189.0",
"84302.0", "85602.0", "23296.0", "34106.0", "34107.0", "55975.0",
"55976.0", "57434.0", "60561.0", "70091.0", "26085.0", "26163.0",
"26654.0", "27473.0", "28303.0", "29212.0", "29380.0", "29581.0",
"29707.0", "30802.0", "31052.0", "33174.0", "34020.0", "36031.0",
"36392.0", "38037.0", "40717.0", "42099.0", "43154.0", "44413.0",
"44414.0", "44730.0", "44770.0", "46863.0", "46876.0", "48318.0",
"48435.0", "48493.0", "48700.0", "51736.0", "51747.0", "51748.0",
"52221.0", "52302.0", "52599.0", "52921.0", "53104.0", "53230.0",
"53443.0", "54494.0", "55053.0", "56555.0", "56717.0", "58381.0",
"62245.0", "62554.0", "63050.0", "63050.0", "63447.0", "63507.0",
"64054.0", "65090.0", "65090.0", "65217.0", "65218.0", "66571.0",
"66945.0", "67715.0", "68169.0", "68921.0", "68955.0", "69081.0",
"70123.0", "70263.0", "71413.0", "74609.0", "75930.0", "75931.0",
"76676.0", "77855.0", "88490.0", "92653.0", "23458.0", "23770.0",
"29531.0", "29532.0", "32320.0", "32735.0", "47644.0", "50879.0",
"50971.0", "51427.0", "55554.0", "57334.0", "57971.0", "59064.0",
"66852.0", "68689.0", "69206.0", "72502.0", "84592.0", "84593.0",
"90207.0", "90208.0", "34426.0", "74433.0", "32354.0", "64161.0",
"67914.0", "21864.0"), type = c("vehicle enters traffic", "vehicle leaves traffic",
"vehicle enters traffic", "vehicle leaves traffic", "vehicle enters traffic",
"vehicle leaves traffic", "vehicle leaves traffic", "vehicle leaves traffic",
"vehicle enters traffic", "vehicle leaves traffic", "vehicle enters traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle leaves traffic",
"vehicle enters traffic", "vehicle leaves traffic", "vehicle enters traffic",
"vehicle enters traffic", "vehicle leaves traffic", "vehicle enters traffic",
"vehicle enters traffic", "vehicle leaves traffic", "vehicle enters traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle leaves traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle enters traffic",
"vehicle enters traffic", "vehicle enters traffic", "vehicle enters traffic",
"vehicle leaves traffic", "vehicle leaves traffic", "vehicle enters traffic",
"vehicle enters traffic", "vehicle leaves traffic", "vehicle leaves traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle enters traffic",
"vehicle leaves traffic", "vehicle leaves traffic", "vehicle enters traffic",
"vehicle enters traffic", "vehicle leaves traffic", "vehicle leaves traffic",
"vehicle leaves traffic", "vehicle leaves traffic", "vehicle leaves traffic",
"vehicle leaves traffic", "vehicle leaves traffic", "vehicle enters traffic",
"vehicle enters traffic", "vehicle enters traffic", "vehicle enters traffic",
"vehicle leaves traffic", "vehicle leaves traffic", "vehicle leaves traffic",
"vehicle enters traffic", "vehicle leaves traffic", "vehicle leaves traffic",
"vehicle enters traffic", "vehicle enters traffic", "vehicle enters traffic",
"vehicle enters traffic", "vehicle leaves traffic", "vehicle leaves traffic",
"vehicle enters traffic", "vehicle leaves traffic", "vehicle enters traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle enters traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle leaves traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle enters traffic",
"vehicle enters traffic", "vehicle enters traffic", "vehicle enters traffic",
"vehicle leaves traffic", "vehicle leaves traffic", "vehicle leaves traffic",
"vehicle leaves traffic", "vehicle leaves traffic", "vehicle leaves traffic",
"vehicle leaves traffic", "vehicle leaves traffic", "vehicle leaves traffic",
"vehicle leaves traffic", "vehicle leaves traffic", "vehicle leaves traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle leaves traffic",
"vehicle leaves traffic", "vehicle leaves traffic", "vehicle leaves traffic",
"vehicle leaves traffic", "vehicle leaves traffic", "vehicle leaves traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle leaves traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle enters traffic",
"vehicle leaves traffic", "vehicle leaves traffic", "vehicle enters traffic",
"vehicle enters traffic", "vehicle enters traffic", "vehicle leaves traffic",
"vehicle enters traffic", "vehicle enters traffic", "vehicle enters traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle enters traffic",
"vehicle enters traffic", "vehicle leaves traffic", "vehicle enters traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle leaves traffic",
"vehicle enters traffic", "vehicle leaves traffic", "vehicle enters traffic",
"vehicle enters traffic", "vehicle enters traffic", "vehicle leaves traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle leaves traffic",
"vehicle leaves traffic", "vehicle leaves traffic", "vehicle enters traffic",
"vehicle leaves traffic", "vehicle leaves traffic", "vehicle enters traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle leaves traffic",
"vehicle enters traffic", "vehicle enters traffic", "vehicle enters traffic",
"vehicle enters traffic", "vehicle enters traffic", "vehicle enters traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle enters traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle leaves traffic",
"vehicle enters traffic", "vehicle enters traffic", "vehicle enters traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle leaves traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle leaves traffic",
"vehicle leaves traffic", "vehicle leaves traffic", "vehicle enters traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle enters traffic",
"vehicle leaves traffic", "vehicle leaves traffic", "vehicle enters traffic",
"vehicle enters traffic", "vehicle leaves traffic", "vehicle leaves traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle enters traffic",
"vehicle enters traffic", "vehicle enters traffic", "vehicle enters traffic",
"vehicle enters traffic", "vehicle leaves traffic", "vehicle enters traffic",
"vehicle enters traffic", "vehicle enters traffic", "vehicle enters traffic",
"vehicle enters traffic", "vehicle leaves traffic", "vehicle leaves traffic",
"vehicle enters traffic", "vehicle leaves traffic", "vehicle enters traffic",
"vehicle enters traffic", "vehicle enters traffic", "vehicle leaves traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle enters traffic",
"vehicle enters traffic", "vehicle enters traffic", "vehicle leaves traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle enters traffic",
"vehicle enters traffic", "vehicle enters traffic", "vehicle leaves traffic",
"vehicle leaves traffic", "vehicle leaves traffic", "vehicle enters traffic",
"vehicle enters traffic", "vehicle leaves traffic", "vehicle enters traffic",
"vehicle leaves traffic", "vehicle leaves traffic", "vehicle enters traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle enters traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle leaves traffic",
"vehicle enters traffic", "vehicle leaves traffic", "vehicle leaves traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle enters traffic",
"vehicle leaves traffic", "vehicle leaves traffic", "vehicle leaves traffic",
"vehicle enters traffic", "vehicle enters traffic", "vehicle enters traffic",
"vehicle enters traffic", "vehicle leaves traffic", "vehicle enters traffic",
"vehicle enters traffic", "vehicle enters traffic", "vehicle leaves traffic",
"vehicle enters traffic", "vehicle enters traffic", "vehicle leaves traffic",
"vehicle leaves traffic", "vehicle leaves traffic", "vehicle leaves traffic",
"vehicle enters traffic", "vehicle leaves traffic", "vehicle leaves traffic",
"vehicle enters traffic", "vehicle enters traffic", "vehicle leaves traffic",
"vehicle leaves traffic", "vehicle leaves traffic", "vehicle enters traffic",
"vehicle leaves traffic", "vehicle leaves traffic", "vehicle enters traffic",
"vehicle enters traffic", "vehicle leaves traffic", "vehicle leaves traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle enters traffic",
"vehicle enters traffic", "vehicle leaves traffic", "vehicle leaves traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle leaves traffic",
"vehicle enters traffic", "vehicle leaves traffic", "vehicle enters traffic",
"vehicle leaves traffic", "vehicle leaves traffic", "vehicle enters traffic",
"vehicle leaves traffic", "vehicle leaves traffic", "vehicle enters traffic",
"vehicle enters traffic", "vehicle enters traffic", "vehicle leaves traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle enters traffic",
"vehicle leaves traffic", "vehicle enters traffic", "vehicle leaves traffic",
"vehicle enters traffic", "vehicle enters traffic", "vehicle leaves traffic",
"vehicle enters traffic", "vehicle enters traffic", "vehicle leaves traffic",
"vehicle enters traffic"), vehicle_id = c(1267069L, 810534L,
810534L, 51825L, 51825L, 1326473L, 1199672L, 1111105L, 1111105L,
532654L, 532654L, 1267069L, 1199672L, 1398907L, 1398907L, 1239391L,
1239391L, 1326473L, 46491L, 46491L, 1179923L, 434774L, 434774L,
4205L, 4205L, 1269433L, 1179923L, 1269433L, 1454119L, 1412246L,
1310775L, 1278645L, 1533113L, 1430553L, 1430553L, 1533113L, 1533113L,
492533L, 1430553L, 1430553L, 492533L, 1278645L, 1454119L, 1533113L,
1278645L, 1310775L, 1412246L, 1278645L, 1161080L, 1290940L, 558745L,
628509L, 628509L, 1161080L, 558745L, 1290940L, 403850L, 774916L,
1530598L, 403850L, 1397256L, 3874L, 774916L, 1530598L, 1397256L,
3874L, 193835L, 1043798L, 1043798L, 1881121L, 193835L, 1221124L,
1881121L, 1221124L, 12799L, 12799L, 526654L, 2066556L, 526654L,
2066556L, 485689L, 486288L, 488147L, 486288L, 485689L, 488147L,
1925302L, 2821L, 1919641L, 2147547L, 1785664L, 1394390L, 1869032L,
1812540L, 1531804L, 1814856L, 1531804L, 2149105L, 1747951L, 1908352L,
1854886L, 1888344L, 1926462L, 1925659L, 1887358L, 1926462L, 1863281L,
1094609L, 1888344L, 1925659L, 1222534L, 2148165L, 1863281L, 2148165L,
1814856L, 1885007L, 1094609L, 1887358L, 1925302L, 1925659L, 1908352L,
1919641L, 1885007L, 1898426L, 1222534L, 2095866L, 1812540L, 1528492L,
2149105L, 1799420L, 1799420L, 1898426L, 2095866L, 1905635L, 1859644L,
1528492L, 1187619L, 1794294L, 1908352L, 1187619L, 2149105L, 1901830L,
1859644L, 1885718L, 1925659L, 4806833L, 1901830L, 1794294L, 4806833L,
2821L, 1905635L, 1785664L, 1887788L, 2149105L, 1885718L, 1912658L,
1394390L, 1457624L, 1869032L, 2147547L, 1908352L, 2064554L, 1457624L,
1902958L, 1888247L, 1888247L, 1670344L, 1898186L, 1378838L, 1378838L,
1840443L, 1747951L, 1887788L, 5259385L, 1215125L, 2064554L, 1912658L,
1887316L, 42794L, 1860654L, 1902958L, 1854886L, 5259385L, 1887316L,
1670344L, 42794L, 1921295L, 1921295L, 1860654L, 1840443L, 1898186L,
1215125L, 80518L, 1131784L, 1131784L, 1060825L, 1060825L, 80518L,
1345214L, 1345214L, 29916L, 29916L, 534393L, 519878L, 525457L,
523658L, 529842L, 526134L, 534394L, 529842L, 523658L, 529554L,
526214L, 29916L, 526214L, 529554L, 479492L, 530841L, 1856482L,
514510L, 514510L, 693877L, 479492L, 29916L, 1270690L, 1856482L,
526134L, 693877L, 790707L, 520491L, 568524L, 568524L, 520491L,
1455918L, 513349L, 29916L, 534585L, 790707L, 513349L, 532803L,
530834L, 1270690L, 1455918L, 526134L, 1309724L, 1309724L, 519877L,
519877L, 533986L, 476491L, 525457L, 519877L, 519877L, 398460L,
398460L, 530834L, 534393L, 519878L, 1145089L, 476491L, 537161L,
530841L, 533986L, 29916L, 526134L, 534585L, 513028L, 513028L,
1145089L, 537161L, 532803L, 534394L, 273572L, 273572L, 861460L,
861460L, 216294L, 216294L, 230683L, 1365760L, 230683L, 310759L,
1521962L, 1521962L, 310759L, 1365760L, 1535243L, 1247561L, 1535243L,
1247561L, 230658L, 230658L, 1277451L, 1277451L, 1428056L, 1428056L,
1140384L, 46083L, 1140384L, 1343106L), link = c(90L, 90L, 90L,
90L, 90L, 90L, 90L, 90L, 90L, 90L, 90L, 90L, 90L, 90L, 90L, 90L,
90L, 90L, 90L, 90L, 90L, 90L, 90L, 90L, 90L, 90L, 90L, 90L, 389L,
389L, 389L, 389L, 389L, 389L, 389L, 389L, 389L, 389L, 389L, 389L,
389L, 389L, 389L, 389L, 389L, 389L, 389L, 389L, 451L, 451L, 451L,
451L, 451L, 451L, 451L, 451L, 480L, 480L, 480L, 480L, 480L, 480L,
480L, 480L, 480L, 480L, 578L, 578L, 578L, 578L, 578L, 578L, 578L,
578L, 578L, 578L, 578L, 578L, 578L, 578L, 662L, 662L, 662L, 662L,
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, 723L,
723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 723L, 774L, 774L,
774L, 774L, 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, 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,
927L, 927L, 927L, 927L, 927L, 927L, 927L, 927L, 927L, 927L, 927L,
927L, 987L, 987L, 988L, 988L, 988L, 1277L), count = c(1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1,
1, 1, 1)), row.names = c(18798L, 64777L, 64783L, 90025L, 90030L,
102868L, 105834L, 108690L, 111727L, 118283L, 124349L, 125700L,
129642L, 135888L, 142577L, 148772L, 161264L, 161590L, 182778L,
187946L, 193712L, 211483L, 213953L, 220930L, 220932L, 252098L,
252362L, 284709L, 24007L, 27759L, 35618L, 45777L, 57274L, 97498L,
122488L, 131443L, 164666L, 193274L, 202313L, 202319L, 208399L,
237611L, 255705L, 275635L, 288938L, 316125L, 328641L, 334960L,
42227L, 44153L, 77374L, 90336L, 227678L, 245970L, 247907L, 248400L,
6850L, 39423L, 56892L, 165451L, 188728L, 196734L, 232209L, 279294L,
318618L, 320203L, 39374L, 52544L, 65925L, 92485L, 93136L, 100641L,
102349L, 135630L, 210380L, 215004L, 263248L, 264622L, 286884L,
287214L, 30294L, 55175L, 147465L, 274669L, 309732L, 318235L,
5646L, 20075L, 20529L, 22040L, 28176L, 31997L, 40252L, 41570L,
50757L, 51465L, 53497L, 54091L, 54830L, 55211L, 57389L, 61972L,
64148L, 65630L, 67926L, 73590L, 79237L, 80209L, 81961L, 83619L,
84034L, 87938L, 88723L, 100516L, 101524L, 104339L, 105567L, 108951L,
109580L, 110436L, 111056L, 111525L, 120134L, 121493L, 124350L,
125721L, 126499L, 129085L, 134769L, 137553L, 137562L, 142843L,
143218L, 143719L, 145566L, 145897L, 147872L, 151431L, 160891L,
169439L, 169951L, 172179L, 182269L, 182796L, 192084L, 194128L,
200320L, 209292L, 210580L, 210957L, 217800L, 226596L, 227622L,
228226L, 233050L, 234180L, 238184L, 239554L, 239746L, 241169L,
243114L, 255350L, 256150L, 265478L, 267279L, 268878L, 271245L,
273444L, 273513L, 273515L, 274788L, 278257L, 281213L, 283103L,
285741L, 288499L, 293964L, 294807L, 296677L, 299364L, 300811L,
301489L, 304733L, 306468L, 307953L, 322532L, 322917L, 324007L,
324134L, 329621L, 329788L, 331476L, 16560L, 79874L, 79878L, 198570L,
198576L, 206594L, 227352L, 292291L, 33304L, 33809L, 37031L, 42365L,
47741L, 53329L, 54310L, 55545L, 56255L, 62460L, 63930L, 75298L,
79478L, 89112L, 90807L, 98845L, 111205L, 119068L, 126025L, 134327L,
134337L, 136437L, 136711L, 149242L, 149320L, 158130L, 158789L,
159131L, 160368L, 177328L, 177392L, 177394L, 179845L, 180260L,
181812L, 183400L, 184268L, 184922L, 185978L, 191177L, 193860L,
201573L, 202492L, 212365L, 240333L, 242817L, 246833L, 246834L,
249842L, 250280L, 254468L, 262163L, 262164L, 263107L, 263117L,
272463L, 274866L, 279549L, 282270L, 286442L, 286606L, 287265L,
292441L, 293100L, 298081L, 308952L, 312468L, 312471L, 314309L,
317028L, 334227L, 336208L, 17419L, 19144L, 55246L, 55251L, 70799L,
73085L, 154041L, 172793L, 173296L, 175721L, 196355L, 206048L,
209794L, 216742L, 274263L, 285199L, 287908L, 302276L, 330230L,
330233L, 335274L, 335275L, 81402L, 308426L, 71000L, 255248L,
280759L, 10158L), class = "data.frame")
Possible output:
time type vehicle_id link count
18798 23707.0 vehicle enters traffic 1267069 90 0 #start point
64777 31209.0 vehicle leaves traffic 810534 90 1 #+1
64783 31210.0 vehicle enters traffic 810534 90 0 #-1
90025 36230.0 vehicle leaves traffic 51825 90 1
90030 36231.0 vehicle enters traffic 51825 90 0
102868 38925.0 vehicle leaves traffic 1326473 90 1
105834 39583.0 vehicle leaves traffic 1199672 90 2 #here as well 1+1 =2
108690 40198.0 vehicle leaves traffic 1111105 90 3 #2+1 =3
111727 40818.0 vehicle enters traffic 1111105 90 2 #3-1 =2
118283 41974.0 vehicle leaves traffic 532654 90 3
124349 42895.0 vehicle enters traffic 532654 90 2
125700 43099.0 vehicle leaves traffic 1267069 90 3
129642 43683.0 vehicle enters traffic 1199672 90 2
135888 44645.0 vehicle leaves traffic 1398907 90 3
142577 45730.0 vehicle enters traffic 1398907 90 2
148772 46785.0 vehicle leaves traffic 1239391 90 3
161264 48846.0 vehicle enters traffic 1239391 90 2
161590 48905.0 vehicle enters traffic 1326473 90 1
182778 52790.0 vehicle leaves traffic 46491 90 2
Eventually I want to find the maximum count for each link. But that can be done in another step and does not need to be part of the solution, perhaps it helps to clarify the question.
Upvotes: 2
Views: 59
Answers (2)
Bas
Reputation: 4658
I think this does what you want:
df %>%
group_by(link) %>%
arrange(time) %>%
mutate(
adder = case_when(
type == "vehicle leaves traffic" ~ 1,
type == "vehicle enters traffic" ~ -1,
TRUE ~ 0),
count = count + cumsum(adder)
) %>%
select(-adder)
which gives
time type vehicle_id link count
1 23707.0 vehicle enters traffic 1267069 90 0
2 31209.0 vehicle leaves traffic 810534 90 1
3 31210.0 vehicle enters traffic 810534 90 0
4 36230.0 vehicle leaves traffic 51825 90 1
5 36231.0 vehicle enters traffic 51825 90 0
6 38925.0 vehicle leaves traffic 1326473 90 1
7 39583.0 vehicle leaves traffic 1199672 90 2
8 40198.0 vehicle leaves traffic 1111105 90 3
9 40818.0 vehicle enters traffic 1111105 90 2
10 41974.0 vehicle leaves traffic 532654 90 3
Upvotes: 2
Georgery
Reputation: 8117
I think this is what you want:
df %>%
arrange(link, time) %>%
group_by(link) %>%
mutate(vehicles_entered_traffic = cumsum(type == "vehicle enters traffic")
, vehicles_left_traffic = cumsum(type == "vehicle leaves traffic")
, count = count[1] + vehicles_left_traffic - vehicles_entered_traffic)
Upvotes: 3
Related Questions
- change repeating row value based on values on another column
- Modified value of a row based on a given condition in R
- How to change column value to match row based on column condition
- R - change value to value from preceding row if conditions are true
- Changing value of row based on condition in other condition
- Using dplyr to mutate the following rows after meeting condition
- Mutate value based on first row above that satisfies a condition
- How to change the value in a row if condition met in the previous row under dplyr
- Row value manipulation in R based on condition
- How to replace a value in first column onwards with a new value based on a condition