3

I would like to perform a rolling max using an (adaptive) non-equi join in data.table. Basing myself on this answer, I think I'm getting close, but not quite there.

For all rows of ex I need the max(value[row:end_window])

> ex
                    time    value end_window row
  1: 2022-03-14 08:20:02 13344.77        540   1
  2: 2022-03-14 08:20:02 13343.52        541   2
  3: 2022-03-14 08:20:03 13342.27        547   3
  4: 2022-03-14 08:20:04 13343.27        541   4
  5: 2022-03-14 08:20:04 13343.02        541   5

I tried calculating rollmax like so:

res <-ex[ex,.(value,row,end_window),on=.(row>=row,row<=end_window)][
  ,.(rollmax = max(value), end_window),by=.(row)]

but it isn't exactly what I expect. For row 1 it is correct because max(ex$value[1:540]) is indeed 13361.77.

res
   row  rollmax end_window
1:   1 13361.77        540
2:   1 13361.77        541
3:   1 13361.77        547
4:   1 13361.77        541

However, for row 596 this is not the case:

> res <- res[order(row)]
> res[596]
   row end_window  rollmax end_window row
1: 596        622 13327.42        622 596
> max(ex$value[596:622])
[1] 13328.67

Any idea?

Data:


> dput(ex)
structure(list(time = structure(c(1647246002.24923, 1647246002.79761, 
1647246003.33618, 1647246004.39149, 1647246004.44229, 1647246005.04222, 
1647246005.61024, 1647246006.19858, 1647246006.74267, 1647246007.28133, 
1647246007.82673, 1647246007.86367, 1647246008.44411, 1647246008.98759, 
1647246009.02739, 1647246010.60053, 1647246011.15027, 1647246011.17698, 
1647246011.72366, 1647246012.79303, 1647246013.33196, 1647246013.87512, 
1647246015.96322, 1647246015.99679, 1647246016.53583, 1647246017.08083, 
1647246017.6256, 1647246018.17829, 1647246018.71606, 1647246019.25767, 
1647246019.79644, 1647246020.33468, 1647246020.8824, 1647246021.94552, 
1647246022.01591, 1647246023.6453, 1647246024.18459, 1647246024.72267, 
1647246025.26541, 1647246025.8108, 1647246026.36594, 1647246027.00413, 
1647246027.5502, 1647246028.08886, 1647246028.12655, 1647246028.19781, 
1647246029.25628, 1647246029.7948, 1647246029.82717, 1647246029.88239, 
1647246030.94222, 1647246030.98792, 1647246031.02387, 1647246031.56251, 
1647246032.10701, 1647246033.68004, 1647246034.21849, 1647246034.75698, 
1647246034.79206, 1647246035.33537, 1647246035.88073, 1647246036.41933, 
1647246037.47445, 1647246038.02743, 1647246038.56629, 1647246039.11172, 
1647246039.69961, 1647246040.24075, 1647246040.78262, 1647246040.81593, 
1647246040.84878, 1647246040.89548, 1647246041.44543, 1647246043.02019, 
1647246043.56719, 1647246044.11148, 1647246045.17907, 1647246045.73629, 
1647246046.2821, 1647246047.33841, 1647246048.92821, 1647246049.47257, 
1647246050.01503, 1647246050.04984, 1647246050.58807, 1647246051.13286, 
1647246051.6825, 1647246052.7956, 1647246053.49162, 1647246054.03274, 
1647246054.57456, 1647246055.632, 1647246055.66697, 1647246055.70239, 
1647246056.25411, 1647246056.79885, 1647246057.87931, 1647246058.42802, 
1647246060.52577, 1647246061.07338, 1647246061.61201, 1647246062.66817, 
1647246063.20656, 1647246063.76296, 1647246064.31383, 1647246064.87371, 
1647246065.41859, 1647246066.47316, 1647246067.52794, 1647246068.11098, 
1647246069.16574, 1647246069.7041, 1647246070.2422, 1647246070.27887, 
1647246070.31238, 1647246070.85178, 1647246071.4025, 1647246071.94053, 
1647246072.47854, 1647246073.02985, 1647246073.58003, 1647246074.63929, 
1647246075.18196, 1647246075.72753, 1647246076.7952, 1647246077.33347, 
1647246077.87198, 1647246078.41077, 1647246078.53224, 1647246079.11493, 
1647246079.14671, 1647246079.69738, 1647246080.23599, 1647246080.77401, 
1647246081.83168, 1647246082.89821, 1647246082.94748, 1647246083.50207, 
1647246084.56697, 1647246085.11225, 1647246085.14328, 1647246085.68186, 
1647246086.22483, 1647246086.76414, 1647246087.30244, 1647246087.84414, 
1647246087.90234, 1647246088.51317, 1647246089.06105, 1647246089.09262, 
1647246089.12905, 1647246089.66859, 1647246091.2575, 1647246091.80716, 
1647246092.34612, 1647246093.40056, 1647246093.43814, 1647246093.49506, 
1647246094.0503, 1647246094.59483, 1647246095.13323, 1647246095.67456, 
1647246096.21326, 1647246097.26845, 1647246097.80666, 1647246098.35339, 
1647246098.38786, 1647246098.92667, 1647246099.46508, 1647246099.49848, 
1647246100.03814, 1647246100.57699, 1647246101.1153, 1647246102.69753, 
1647246102.73281, 1647246103.30528, 1647246103.84384, 1647246104.38213, 
1647246104.92884, 1647246105.46755, 1647246106.53496, 1647246107.08357, 
1647246107.6341, 1647246108.18945, 1647246108.72779, 1647246109.27579, 
1647246109.30699, 1647246109.8461, 1647246111.42362, 1647246112.49641, 
1647246113.04461, 1647246113.08354, 1647246114.14973, 1647246114.68916, 
1647246115.74509, 1647246116.2965, 1647246117.35259, 1647246117.89085, 
1647246118.43221, 1647246118.98784, 1647246120.04347, 1647246120.58226, 
1647246121.11993, 1647246121.15373, 1647246121.69873, 1647246122.24513, 
1647246122.78392, 1647246122.82012, 1647246123.35951, 1647246124.43122, 
1647246124.97043, 1647246125.00463, 1647246126.06283, 1647246126.60107, 
1647246127.139, 1647246128.1969, 1647246128.7363, 1647246129.28432, 
1647246129.83505, 1647246130.38045, 1647246130.91879, 1647246131.45954, 
1647246131.99771, 1647246132.53656, 1647246133.60522, 1647246134.14361, 
1647246134.68677, 1647246135.76064, 1647246135.79411, 1647246136.33365, 
1647246136.87596, 1647246137.41375, 1647246138.46839, 1647246140.04516, 
1647246140.5832, 1647246141.12186, 1647246141.66057, 1647246141.69597, 
1647246142.24788, 1647246142.78726, 1647246143.32551, 1647246143.86371, 
1647246144.40198, 1647246144.9439, 1647246145.99847, 1647246147.05978, 
1647246147.59852, 1647246148.65275, 1647246149.19058, 1647246149.22625, 
1647246149.77296, 1647246150.92239, 1647246151.98347, 1647246152.52875, 
1647246153.59316, 1647246154.1348, 1647246154.69452, 1647246155.74972, 
1647246156.80442, 1647246157.34306, 1647246157.88178, 1647246158.93975, 
1647246158.97796, 1647246159.52411, 1647246160.06207, 1647246160.60031, 
1647246161.14835, 1647246161.737, 1647246162.27981, 1647246163.33446, 
1647246163.87274, 1647246163.90699, 1647246164.4459, 1647246164.984, 
1647246165.02719, 1647246165.08346, 1647246165.6332, 1647246166.68854, 
1647246167.22671, 1647246167.76482, 1647246168.31278, 1647246168.85157, 
1647246169.90642, 1647246169.94019, 1647246170.47815, 1647246171.02075, 
1647246171.12008, 1647246171.19541, 1647246171.73418, 1647246172.27306, 
1647246173.32804, 1647246174.3931, 1647246174.9318, 1647246175.46992, 
1647246176.00905, 1647246176.04586, 1647246176.59036, 1647246177.12817, 
1647246177.6664, 1647246178.20724, 1647246180.29625, 1647246180.84686, 
1647246181.39772, 1647246181.93643, 1647246182.99156, 1647246183.53319, 
1647246184.58817, 1647246185.12657, 1647246186.19437, 1647246186.22632, 
1647246186.79265, 1647246187.84722, 1647246188.38569, 1647246189.45762, 
1647246190.0031, 1647246190.55265, 1647246191.09596, 1647246191.13081, 
1647246191.71897, 1647246192.30443, 1647246192.33833, 1647246192.87677, 
1647246193.41573, 1647246193.95388, 1647246194.49243, 1647246195.03318, 
1647246195.58616, 1647246195.629, 1647246195.68133, 1647246196.7452, 
1647246196.78274, 1647246197.32815, 1647246198.383, 1647246198.92212, 
1647246199.47407, 1647246200.01366, 1647246201.09431, 1647246201.28339, 
1647246201.83275, 1647246201.88873, 1647246202.42749, 1647246202.96512, 
1647246203.50335, 1647246203.53717, 1647246204.60806, 1647246205.66341, 
1647246207.76851, 1647246208.31093, 1647246208.85274, 1647246209.39086, 
1647246209.92895, 1647246210.46752, 1647246211.00958, 1647246211.58626, 
1647246212.13684, 1647246212.6751, 1647246213.23354, 1647246213.77225, 
1647246214.31059, 1647246214.85187, 1647246215.39784, 1647246215.93649, 
1647246216.48652, 1647246216.51868, 1647246217.06782, 1647246217.61658, 
1647246218.15583, 1647246218.69413, 1647246219.2327, 1647246219.78182, 
1647246219.81398, 1647246219.87332, 1647246220.41379, 1647246220.99915, 
1647246221.54392, 1647246221.57975, 1647246222.13378, 1647246222.6731, 
1647246223.2105, 1647246223.74841, 1647246224.28625, 1647246225.34108, 
1647246225.37343, 1647246225.92474, 1647246226.46305, 1647246227.00108, 
1647246228.57557, 1647246230.68348, 1647246232.2717, 1647246232.42394, 
1647246232.48924, 1647246233.5488, 1647246234.60351, 1647246235.65857, 
1647246236.19704, 1647246236.73508, 1647246237.28333, 1647246237.82224, 
1647246238.36069, 1647246239.41507, 1647246239.44798, 1647246241.04188, 
1647246241.08999, 1647246241.63496, 1647246242.17391, 1647246242.71288, 
1647246243.27745, 1647246243.8328, 1647246244.37185, 1647246244.91051, 
1647246246.48473, 1647246247.03573, 1647246247.57391, 1647246248.11399, 
1647246248.65179, 1647246249.18901, 1647246249.72819, 1647246250.26673, 
1647246252.36213, 1647246252.90016, 1647246253.44222, 1647246253.97995, 
1647246254.55197, 1647246255.14833, 1647246255.18025, 1647246256.24064, 
1647246256.77879, 1647246257.3168, 1647246257.8549, 1647246259.42901, 
1647246259.96739, 1647246260.51821, 1647246261.06769, 1647246261.60631, 
1647246262.14409, 1647246262.68199, 1647246263.7364, 1647246264.27817, 
1647246264.8161, 1647246264.8493, 1647246265.38822, 1647246266.02156, 
1647246267.08953, 1647246267.13244, 1647246267.70627, 1647246268.2439, 
1647246269.30283, 1647246269.84823, 1647246270.38616, 1647246270.92416, 
1647246271.46258, 1647246272.01895, 1647246272.05282, 1647246273.11308, 
1647246273.65094, 1647246274.19252, 1647246274.22361, 1647246274.76141, 
1647246275.3023, 1647246275.90684, 1647246276.45273, 1647246276.48599, 
1647246277.0393, 1647246277.5776, 1647246278.64335, 1647246279.18532, 
1647246280.23975, 1647246280.7782, 1647246281.31639, 1647246281.85913, 
1647246282.39697, 1647246282.96604, 1647246282.99761, 1647246283.53741, 
1647246284.08358, 1647246284.62939, 1647246285.17108, 1647246285.70896, 
1647246286.31339, 1647246286.85253, 1647246287.90696, 1647246288.45875, 
1647246289.00068, 1647246290.58259, 1647246291.12135, 1647246291.65992, 
1647246292.20938, 1647246292.7532, 1647246293.29224, 1647246293.31763, 
1647246293.38285, 1647246294.45661, 1647246295.00258, 1647246296.05955, 
1647246296.08681, 1647246296.68747, 1647246297.22569, 1647246297.76365, 
1647246297.79615, 1647246298.33488, 1647246298.33488, 1647246299.26168, 
1647246300.3193, 1647246301.37476, 1647246301.9131, 1647246302.96969, 
1647246304.02832, 1647246304.57973, 1647246305.11874, 1647246305.66082, 
1647246306.19956, 1647246307.78313, 1647246308.39276, 1647246309.46614, 
1647246311.04522, 1647246311.59425, 1647246312.64913, 1647246313.20936, 
1647246313.75224, 1647246314.82892, 1647246315.37281, 1647246315.92568, 
1647246316.48708, 1647246317.03067, 1647246317.58859, 1647246318.64671, 
1647246319.19176, 1647246319.75091, 1647246320.31055, 1647246321.36516, 
1647246322.41996, 1647246322.96025, 1647246324.53452, 1647246325.58951, 
1647246326.13133, 1647246326.68027, 1647246327.22395, 1647246327.76302, 
1647246328.30463, 1647246328.84338, 1647246329.38136, 1647246329.93062, 
1647246329.97819, 1647246330.52056, 1647246331.06359, 1647246332.12599, 
1647246332.66487, 1647246333.20322, 1647246333.75134, 1647246334.30892, 
1647246334.85475, 1647246335.39292, 1647246335.93137, 1647246336.48062, 
1647246337.04778, 1647246337.6012, 1647246338.14782, 1647246338.69871, 
1647246338.738, 1647246339.29972, 1647246339.84796, 1647246340.38704, 
1647246340.93951, 1647246340.97152, 1647246341.5138, 1647246342.59254, 
1647246343.133, 1647246343.67222, 1647246344.72683, 1647246345.2765, 
1647246345.83496, 1647246346.37337, 1647246346.91159, 1647246347.96947, 
1647246348.5073, 1647246349.04528, 1647246349.07814, 1647246349.62146, 
1647246350.68969, 1647246351.35044, 1647246351.88839, 1647246352.44432, 
1647246352.98305, 1647246353.01189, 1647246353.55901, 1647246354.10541, 
1647246354.64892, 1647246355.18692, 1647246355.72845, 1647246356.79225, 
1647246357.84731, 1647246358.38574, 1647246358.94398, 1647246358.99016, 
1647246359.02776, 1647246360.60906, 1647246361.15149, 1647246361.69722, 
1647246362.23563, 1647246363.29111, 1647246363.82956, 1647246364.38134, 
1647246364.91961, 1647246365.4643, 1647246366.00604, 1647246366.54683, 
1647246367.10826, 1647246368.17288, 1647246368.71083, 1647246369.76524, 
1647246370.32348, 1647246370.35208, 1647246370.98812, 1647246371.09929, 
1647246371.13341, 1647246371.71661, 1647246372.25956, 1647246372.80556, 
1647246373.34373, 1647246373.88164, 1647246374.41965, 1647246375.99117
), class = c("POSIXct", "POSIXt"), tzone = "UTC"), value = c(13344.77, 
13343.52, 13342.27, 13343.27, 13343.02, 13343.27, 13343.27, 13342.27, 
13344.77, 13345.02, 13344.52, 13345.52, 13345.02, 13345.05, 13345.52, 
13345.27, 13345.52, 13345.77, 13346.27, 13346.52, 13345.52, 13345.02, 
13345.77, 13346.02, 13345.52, 13346.02, 13347.77, 13347.02, 13345.77, 
13346.77, 13347.27, 13347.52, 13349.02, 13349.02, 13349.27, 13350.77, 
13350.52, 13351.02, 13348.77, 13348.27, 13348.77, 13349.52, 13349.27, 
13348.52, 13347.77, 13348.27, 13348.77, 13350.77, 13350.27, 13351.27, 
13351.52, 13352.02, 13351.77, 13352.02, 13351.52, 13350.52, 13351.02, 
13351.52, 13351.77, 13351.27, 13351.52, 13351.27, 13351.27, 13351.77, 
13350.77, 13351.52, 13351.77, 13352.52, 13353.52, 13353.77, 13354.27, 
13355.77, 13356.27, 13355.52, 13354.02, 13353.77, 13353.52, 13355.27, 
13355.52, 13356.27, 13357.02, 13358.77, 13358.52, 13358.02, 13358.27, 
13358.02, 13357.77, 13355.77, 13356.02, 13356.27, 13356.02, 13356.27, 
13356.52, 13356.05, 13356.52, 13357.02, 13357.52, 13358.77, 13358.52, 
13357.77, 13358.27, 13358.02, 13359.77, 13360.27, 13360.52, 13359.02, 
13359.52, 13360.27, 13360.52, 13360.27, 13360.52, 13361.02, 13361.27, 
13361.52, 13361.77, 13360.27, 13360.02, 13359.52, 13359.27, 13358.52, 
13359.77, 13360.02, 13360.27, 13360.02, 13360.27, 13360.52, 13359.77, 
13360.02, 13359.52, 13359.27, 13358.77, 13359.77, 13359.52, 13359.27, 
13360.02, 13359.02, 13358.77, 13358.27, 13359.02, 13359.27, 13359.77, 
13360.27, 13359.77, 13359.52, 13359.02, 13358.52, 13359.02, 13359.52, 
13359.77, 13359.52, 13359.27, 13359.77, 13359.27, 13358.52, 13358.27, 
13356.02, 13356.02, 13356.02, 13357.27, 13359.27, 13359.02, 13359.52, 
13357.77, 13357.27, 13357.02, 13357.27, 13357.02, 13356.77, 13357.02, 
13356.02, 13355.02, 13355.77, 13356.27, 13357.27, 13357.52, 13357.27, 
13356.27, 13356.02, 13354.77, 13355.02, 13353.77, 13353.27, 13352.02, 
13350.52, 13350.02, 13349.77, 13350.02, 13350.77, 13348.77, 13347.77, 
13348.02, 13347.77, 13347.52, 13347.02, 13344.52, 13343.52, 13344.52, 
13344.27, 13344.02, 13343.52, 13343.77, 13344.02, 13344.52, 13345.02, 
13344.77, 13343.77, 13344.02, 13344.27, 13344.52, 13344.27, 13344.02, 
13343.77, 13344.02, 13344.27, 13343.77, 13344.02, 13343.27, 13343.02, 
13342.77, 13342.52, 13342.77, 13343.02, 13342.77, 13343.27, 13343.77, 
13342.27, 13342.52, 13342.27, 13342.52, 13341.77, 13342.02, 13341.77, 
13339.77, 13340.27, 13340.52, 13340.77, 13341.27, 13340.77, 13341.27, 
13341.02, 13341.52, 13341.77, 13342.27, 13341.77, 13342.27, 13342.52, 
13341.77, 13341.52, 13340.77, 13341.27, 13340.52, 13341.52, 13341.42, 
13343.42, 13343.02, 13341.92, 13340.67, 13341.17, 13342.42, 13343.52, 
13344.77, 13344.27, 13344.52, 13344.67, 13345.27, 13345.17, 13343.27, 
13342.92, 13342.42, 13343.27, 13344.17, 13343.92, 13342.67, 13343.02, 
13343.27, 13343.02, 13343.77, 13344.27, 13344.77, 13345.52, 13345.27, 
13346.02, 13345.92, 13346.02, 13345.17, 13345.42, 13345.17, 13345.27, 
13345.92, 13346.92, 13346.52, 13346.17, 13345.42, 13347.52, 13346.42, 
13346.52, 13347.42, 13346.67, 13346.77, 13347.77, 13349.52, 13350.77, 
13351.17, 13351.27, 13350.92, 13349.42, 13349.17, 13348.77, 13347.77, 
13347.42, 13348.27, 13347.92, 13348.27, 13348.02, 13347.02, 13346.17, 
13346.42, 13346.52, 13347.17, 13348.27, 13347.55, 13348.42, 13348.52, 
13348.92, 13347.77, 13347.67, 13347.92, 13347.17, 13347.02, 13347.17, 
13347.17, 13346.17, 13347.27, 13347.02, 13346.52, 13346.77, 13346.27, 
13345.17, 13344.92, 13345.02, 13343.92, 13344.27, 13344.42, 13344.27, 
13345.27, 13344.17, 13344.92, 13344.42, 13344.27, 13343.42, 13343.92, 
13342.77, 13340.92, 13340.67, 13341.42, 13340.92, 13341.27, 13340.8, 
13341.92, 13342.02, 13341.77, 13341.27, 13342.27, 13342.02, 13341.77, 
13342.52, 13343.02, 13342.52, 13342.27, 13341.77, 13342.02, 13341.77, 
13342.52, 13342.77, 13343.02, 13342.77, 13344.77, 13344.02, 13343.77, 
13345.02, 13345.52, 13344.27, 13344.52, 13345.77, 13346.52, 13346.02, 
13346.52, 13347.02, 13348.27, 13347.55, 13348.02, 13347.52, 13346.77, 
13346.52, 13344.02, 13343.27, 13342.27, 13342.02, 13342.52, 13342.27, 
13342.52, 13343.27, 13343.77, 13344.02, 13344.77, 13344.02, 13344.52, 
13343.02, 13344.02, 13344.27, 13344.27, 13345.27, 13344.52, 13344.77, 
13345.52, 13345.02, 13345.52, 13344.52, 13345.77, 13345.02, 13345.52, 
13345.27, 13345.02, 13345.52, 13345.27, 13345.02, 13344.52, 13344.02, 
13344.27, 13344.02, 13344.02, 13343.77, 13346.52, 13346.02, 13346.77, 
13347.52, 13347.27, 13347.02, 13346.52, 13346.27, 13346.77, 13346.27, 
13346.52, 13346.27, 13345.27, 13346.77, 13347.77, 13347.27, 13346.77, 
13347.27, 13347.02, 13348.52, 13348.67, 13348.17, 13346.67, 13345.42, 
13345.67, 13345.02, 13344.92, 13345.42, 13345.27, 13345.42, 13345.02, 
13344.42, 13344.52, 13344.92, 13345.27, 13344.92, 13344.17, 13343.92, 
13343.92, 13344.17, 13344.52, 13346.02, 13345.92, 13345.67, 13345.52, 
13345.42, 13345.67, 13346.17, 13345.67, 13346.17, 13346.67, 13348.05, 
13348.67, 13347.42, 13347.77, 13347.67, 13348.52, 13348.42, 13348.17, 
13348.27, 13348.92, 13348.67, 13348.52, 13349.27, 13349.17, 13348.77, 
13348.67, 13348.92, 13348.92, 13348.42, 13348.17, 13349.27, 13349.67, 
13348.27, 13347.77, 13347.92, 13348.77, 13350.17, 13351.02, 13350.67, 
13350.77, 13350.42, 13350.27, 13349.92, 13350.02, 13347.67, 13348.17, 
13346.02, 13345.42, 13345.52, 13344.92, 13345.17, 13346.02, 13346.17, 
13346.02, 13344.42, 13344.67, 13344.52, 13343.27, 13344.02, 13343.27, 
13343.02, 13341.77, 13340.02, 13341.02, 13341.27, 13339.77, 13339.27, 
13337.77, 13337.42, 13339.17, 13338.52, 13338.02, 13337.27, 13335.52, 
13334.27, 13334.62, 13334.27, 13334.37, 13333.77, 13333.12, 13332.92, 
13333.42, 13331.42, 13329.92, 13329.92, 13330.27, 13329.67, 13330.27, 
13329.77, 13331.02, 13331.27, 13332.02, 13330.77, 13332.02, 13331.52, 
13331.02, 13332.02, 13332.52, 13331.77, 13332.27, 13329.52, 13329.77, 
13329.52, 13329.27, 13329.02, 13329.52, 13329.77, 13330.02, 13329.77, 
13329.02, 13329.27, 13329.52, 13328.77, 13329.02, 13329.67, 13330.17, 
13329.02, 13329.52, 13329.02, 13328.17, 13329.02, 13328.67, 13327.42, 
13327.77, 13328.67, 13327.42, 13327.17, 13327.42, 13326.77, 13327.92, 
13327.52, 13326.92, 13326.92, 13327.42, 13325.92, 13326.42, 13325.42, 
13323.17, 13323.67, 13324.42, 13324.27, 13323.55, 13324.92, 13324.77, 
13324.02, 13323.17, 13323.02, 13322.52, 13322.27), end_window = c(540L, 
541L, 547L, 541L, 541L, 541L, 541L, 547L, 540L, 233L, 540L, 233L, 
233L, 233L, 233L, 233L, 233L, 233L, 233L, 233L, 233L, 233L, 233L, 
233L, 233L, 233L, 220L, 230L, 233L, 233L, 230L, 226L, 196L, 196L, 
196L, 195L, 195L, 195L, 196L, 218L, 196L, 196L, 196L, 217L, 220L, 
218L, 196L, 195L, 195L, 195L, 195L, 195L, 195L, 195L, 195L, 195L, 
195L, 195L, 195L, 195L, 195L, 195L, 195L, 195L, 195L, 195L, 195L, 
194L, 190L, 190L, 189L, 184L, 184L, 185L, 189L, 190L, 190L, 185L, 
185L, 184L, 184L, 182L, 182L, 183L, 183L, 183L, 183L, 184L, 184L, 
184L, 184L, 184L, 184L, 184L, 184L, 184L, 183L, 182L, 182L, 183L, 
183L, 183L, 181L, 171L, 171L, 181L, 181L, 171L, 171L, 171L, 171L, 
171L, 156L, 156L, 156L, 171L, 179L, 181L, 181L, 182L, 181L, 179L, 
171L, 179L, 171L, 171L, 181L, 179L, 181L, 181L, 182L, 181L, 181L, 
181L, 179L, 181L, 182L, 183L, 181L, 181L, 181L, 171L, 181L, 181L, 
181L, 182L, 181L, 181L, 181L, 181L, 181L, 181L, 181L, 182L, 183L, 
184L, 184L, 184L, 183L, 181L, 181L, 181L, 183L, 183L, 184L, 183L, 
184L, 184L, 184L, 184L, 186L, 184L, 184L, 183L, 183L, 183L, 184L, 
184L, 189L, 186L, 190L, 190L, 195L, 195L, 195L, 195L, 195L, 195L, 
196L, 220L, 219L, 220L, 226L, 230L, 540L, 541L, 540L, 541L, 541L, 
541L, 541L, 541L, 540L, 233L, 540L, 541L, 541L, 541L, 540L, 541L, 
541L, 541L, 541L, 541L, 541L, 541L, 541L, 541L, 542L, 542L, 542L, 
541L, 542L, 541L, 541L, 547L, 542L, 547L, 542L, 547L, 547L, 547L, 
548L, 548L, 548L, 547L, 547L, 547L, 547L, 547L, 547L, 547L, 547L, 
547L, 547L, 542L, 547L, 547L, 547L, 547L, 548L, 547L, 547L, 541L, 
541L, 547L, 547L, 547L, 546L, 541L, 540L, 541L, 540L, 540L, 536L, 
536L, 541L, 541L, 546L, 541L, 541L, 541L, 542L, 541L, 541L, 541L, 
541L, 541L, 540L, 536L, 536L, 353L, 354L, 353L, 536L, 536L, 536L, 
536L, 354L, 353L, 353L, 353L, 536L, 353L, 353L, 353L, 353L, 353L, 
353L, 353L, 341L, 338L, 338L, 316L, 338L, 341L, 341L, 350L, 353L, 
353L, 352L, 352L, 352L, 352L, 353L, 353L, 353L, 353L, 353L, 352L, 
353L, 352L, 350L, 350L, 353L, 353L, 352L, 353L, 353L, 353L, 353L, 
353L, 353L, 353L, 353L, 353L, 353L, 536L, 539L, 539L, 541L, 541L, 
540L, 541L, 536L, 541L, 539L, 540L, 541L, 541L, 541L, 542L, 547L, 
547L, 547L, 547L, 547L, 547L, 547L, 547L, 547L, 547L, 547L, 547L, 
547L, 542L, 541L, 542L, 547L, 547L, 547L, 547L, 542L, 542L, 541L, 
542L, 540L, 541L, 541L, 539L, 536L, 541L, 540L, 536L, 536L, 536L, 
536L, 535L, 397L, 397L, 397L, 397L, 536L, 536L, 541L, 541L, 547L, 
547L, 542L, 547L, 542L, 541L, 541L, 541L, 540L, 541L, 540L, 541L, 
541L, 541L, 541L, 536L, 540L, 540L, 536L, 539L, 536L, 540L, 536L, 
539L, 536L, 536L, 539L, 536L, 536L, 539L, 540L, 541L, 541L, 541L, 
541L, 541L, 536L, 536L, 536L, 535L, 535L, 535L, 536L, 536L, 536L, 
536L, 536L, 536L, 536L, 536L, 535L, 535L, 536L, 535L, 535L, 531L, 
531L, 534L, 536L, 536L, 536L, 539L, 539L, 536L, 536L, 536L, 539L, 
540L, 540L, 539L, 536L, 539L, 541L, 541L, 541L, 541L, 540L, 536L, 
536L, 536L, 536L, 536L, 536L, 536L, 536L, 536L, 536L, 534L, 531L, 
535L, 535L, 535L, 531L, 531L, 534L, 534L, 531L, 531L, 531L, 531L, 
531L, 531L, 531L, 531L, 531L, 531L, 534L, 531L, 528L, 534L, 535L, 
535L, 531L, 523L, 521L, 521L, 521L, 523L, 523L, 528L, 523L, 535L, 
534L, 536L, 536L, 536L, 539L, 536L, 536L, 536L, 536L, 540L, 540L, 
540L, 541L, 541L, 541L, 541L, 547L, 548L, 547L, 547L, 548L, 552L, 
556L, 556L, 552L, 553L, 554L, 556L, 557L, 578L, 574L, 578L, 577L, 
593L, 596L, 596L, 593L, 608L, 611L, 611L, 611L, 611L, 611L, 611L, 
608L, 608L, 602L, 610L, 602L, 608L, 608L, 602L, 596L, 608L, 600L, 
611L, 611L, 611L, 611L, 611L, 611L, 611L, 611L, 611L, 611L, 611L, 
611L, 611L, 611L, 611L, 611L, 611L, 611L, 611L, 620L, 611L, 611L, 
622L, 621L, 611L, 622L, 623L, 622L, 623L, 621L, 622L, 623L, 623L, 
622L, 625L, 623L, 625L, 630L, 630L, 630L, 630L, 630L, 625L, 625L, 
630L, 630L, 630L, 638L, 638L), row = 1:622, rowid = 1:622), row.names = c(NA, 
-622L), class = c("data.table", "data.frame"), .internal.selfref = <pointer: 0x0000026f4d0c1ef0>)

EDIT: the below method performs the correct operation but blithely fills up 64gb of RAM in a few seconds when applied to 1M rows.

idxlist_ <- list()
for(i in 1:nrow(ex)) {
  if(!is.na(ex$end_window[i])) {
    idxlist_[[i]] <- 0:(ex$end_window[i]-i)
  } else(
    idxlist_[[i]] <- 0
  )
}
res <- rollapplyr(ex$value, width=idxlist_, max, align = 'left', fill=NA)
> res[596]
[1] 13328.67
> max(ex$value[596:622])
[1] 13328.67

enter image description here

gaut
  • 5,771
  • 1
  • 14
  • 45
  • frollmax is not yet implemented, if it would be as mean and sum, then adaptive argument will work. frollapply unfortunately does not support adaptive argument. If you don't mind, could you fill and issue in DT requesting support for your problem? I would like to know more to weight if frollmax or frollapply adaptive is better to prioritize. – jangorecki Aug 18 '22 at 14:07
  • Also my answer here may give some inspiration for faster approaches https://stackoverflow.com/questions/21368245/adaptive-moving-average-top-performance-in-r – jangorecki Aug 18 '22 at 14:16
  • indeed. `frollapply` doesn't support align=`left`. Guess I could try by `rev`ing the data however. but I was just hoping for a `join` solution tbh. Will try with .EACHI as you suggested – gaut Aug 18 '22 at 14:30
  • it doesn't support `align=left` for adaptive cases* – gaut Aug 18 '22 at 14:38
  • 1
    issue opened at https://github.com/Rdatatable/data.table/issues/5438 – gaut Aug 18 '22 at 14:44

2 Answers2

4

Do you need to do it with canonical data.table? This seems reasonable given your data sample:

ex[, value2 := mapply(function(a, b) max(value[a:b], na.rm = TRUE), row, end_window)]
ex
#                     time    value end_window   row rowid   value2
#                   <POSc>    <num>      <int> <int> <int>    <num>
#   1: 2022-03-14 08:20:02 13344.77        540     1     1 13361.77
#   2: 2022-03-14 08:20:02 13343.52        541     2     2 13361.77
#   3: 2022-03-14 08:20:03 13342.27        547     3     3 13361.77
#   4: 2022-03-14 08:20:04 13343.27        541     4     4 13361.77
#   5: 2022-03-14 08:20:04 13343.02        541     5     5 13361.77
#   6: 2022-03-14 08:20:05 13343.27        541     6     6 13361.77
#   7: 2022-03-14 08:20:05 13343.27        541     7     7 13361.77
#   8: 2022-03-14 08:20:06 13342.27        547     8     8 13361.77
#   9: 2022-03-14 08:20:06 13344.77        540     9     9 13361.77
#  10: 2022-03-14 08:20:07 13345.02        233    10    10 13361.77
#  ---                                                             
# 613: 2022-03-14 08:26:10 13324.42        630   613   613 13324.92
# 614: 2022-03-14 08:26:11 13324.27        630   614   614 13324.92
# 615: 2022-03-14 08:26:11 13323.55        630   615   615 13324.92
# 616: 2022-03-14 08:26:11 13324.92        625   616   616 13324.92
# 617: 2022-03-14 08:26:12 13324.77        625   617   617 13324.77
# 618: 2022-03-14 08:26:12 13324.02        630   618   618 13324.02
# 619: 2022-03-14 08:26:13 13323.17        630   619   619 13323.17
# 620: 2022-03-14 08:26:13 13323.02        630   620   620 13323.02
# 621: 2022-03-14 08:26:14 13322.52        638   621   621 13322.52
# 622: 2022-03-14 08:26:15 13322.27        638   622   622 13322.27
r2evans
  • 141,215
  • 6
  • 77
  • 149
  • I welcome any other additional syntax, although I'd like to keep the canonical expression as well. The thing is, I have over 8 million rows, so I need the `data.table` optimization – gaut Aug 18 '22 at 13:30
  • Doing a rolling join is still going to be rather expensive, even with `data.table`-canonical. From my understanding of the canonical rolling join, it tends to be only LOCF, NOCB, or `"nearest"` logic, with no (afaict) ability to provide a user-defined function for the roll. I hope somebody with more experience with `data.table` can make a suggestion that doesn't first execute a cartesian expansion of your data (which I think you cannot afford). Good luck. (Curious, though: have you tried this on your data?) – r2evans Aug 18 '22 at 13:41
  • I have, and unfortunately it isn't done yet. For info, `data.table` joins have shown significant improvements over even Rcpp in [this related question](https://stackoverflow.com/questions/73378024/r-efficient-iterative-subsetting-and-filtering-of-large-vector) – gaut Aug 18 '22 at 13:46
  • 1
    AFAIU that wouldn't be rolling but a non-equi using max and by=.EACHI, then you avoid cartesian as each group is computed sequentially, so small cartesians within a group. – jangorecki Aug 18 '22 at 14:09
  • I've not internalized `by=.EACHI` yet in my `data.table`-foo; I know there are several good docs on it, but I haven't yet worked on it yet. Thanks @jangorecki. (I agree about not being rolling, that was something I latched onto from the OP for comments, and didn't think to correct; I think this answer, even if not canonical, does reflect the fact that it's not rolling, just variable-window.) – r2evans Aug 18 '22 at 14:33
4

Taking only the first element of the last instance of end_window seems to take care of the immediate problem, but the suggestion to use .EACHI is spot on. For a large data set, a join will be faster than a simple group operation.

ex[, end_window := pmin(end_window, .N)] # don't search beyond the extent of ex

# join solution
f1 <- function(ex) ex[ex, .(rollmax = value, row, end_window), on = .(row >= row, row <= end_window)][, .(rollmax = max(rollmax), end_window = end_window[1]), row]
# grouping operation
f2 <- function(ex) ex[, .(rollmax = max(ex$value[row:end_window]), end_window), row]
# join by .EACHI from jangorecki's suggestion
f3 <- function(ex) setnames(setcolorder(ex[ex, .(max(value)), on = .(row >= row, row <= end_window), .EACHI], c(1,3,2)), c("row", "rollmax", "end_window"))

res1 <- f1(ex)
res2 <- f2(ex)
res3 <- f3(ex)
f1(ex)[596]
#>    row  rollmax end_window
#> 1: 596 13328.67        622
f2(ex)[596]
#>    row  rollmax end_window
#> 1: 596 13328.67        622
f3(ex)[596]
#>    row  rollmax end_window
#> 1: 596 13328.67        622
max(ex$value[596:622])
#> [1] 13328.67

microbenchmark::microbenchmark(f1 = f1(ex),
                               f2 = f2(ex),
                               f3 = f3(ex),
                               check = "identical")
#> Unit: milliseconds
#>  expr    min      lq     mean  median      uq     max neval
#>    f1 7.5080 8.66995 9.886949 9.18510 9.73970 21.3667   100
#>    f2 3.2476 3.70190 4.641003 4.19860 5.07995 14.8099   100
#>    f3 3.9210 4.73330 5.323673 5.14675 5.73335 13.9769   100

Trying f2 and f3 on a larger data set. Surprisingly, the join is now faster.

ex <- data.table(value = cumsum(rnorm(1e6, 0.1)), end_window = 1:1e6 + sample(50:500, 1e6, TRUE), row = 1:1e6)[, end_window := pmin(end_window, .N)]
microbenchmark::microbenchmark(f2 = f2(ex),
                               f3 = f3(ex),
                               times = 10,
                               check = "identical")
#> Unit: seconds
#>  expr      min       lq     mean   median       uq      max neval
#>    f2 5.730260 5.802243 7.053432 7.080656 8.146056 8.395844    10
#>    f3 2.607298 2.998779 3.302622 3.396466 3.695056 3.745173    10
jblood94
  • 10,340
  • 1
  • 10
  • 15
  • just for info, 8M rows are processed in 6.64 mins using the grouping method. The join method results in `Join results in more than 2^31 rows (internal vecseq reached physical limit). Very likely misspecified join` – gaut Aug 18 '22 at 23:27
  • AFAIU this join is suboptimal as it does not use by=.EACHI – jangorecki Aug 19 '22 at 11:03
  • Good size for going parallel. I'd guess the timings wouldn't be too far from linear for rows per thread. – jblood94 Aug 19 '22 at 11:39
  • @jangorecki see the updated answer that includes an `.EACHI` join. – jblood94 Aug 20 '22 at 02:09
  • Selecting columns in f3 makes extra in-memory copy, better to remove (with :=) columns from the result. – jangorecki Aug 20 '22 at 07:23
  • And we are still looking at milliseconds. If you scale to seconds you may end up with completely different rank of answers. – jangorecki Aug 20 '22 at 07:23
  • 1
    I was being sloppy. Replaced the column select with `setcolorder`. Didn't make much difference for the smaller data set, but you were right about timings on a bigger data set. I'm surprised. – jblood94 Aug 20 '22 at 17:11
  • 1
    @gaut, I suggest taking a look at the updated answer. A join with `.EACHI` performs better on a larger data set. – jblood94 Aug 20 '22 at 17:11
  • 2
    On my machine f3() was 3.648s. Freshly developed optimized frollmax was 0.115s, x30 times speed up. Available in frollmax branch for that moment. Probably won't make it to coming CRAN release, but the one after. – jangorecki Aug 21 '22 at 17:09
  • 1
    Using extra flag hasNA=FALSE makes it 0.07s, roughly 50 times faster – jangorecki Aug 21 '22 at 17:17
  • `frollmax` performs best over all the proposed methods, see https://github.com/Rdatatable/data.table/issues/5438 – gaut Aug 21 '22 at 20:07