Data sampling before and after a timestamp-CodePudding

I have a data set from a sailing boat and I want to analyse some of the manoeuvres. Below is a data set with timestamps every second, a grouping column that groups by time of tack/manoeuvre and the other two (BSP,TWA) are variables.

What I would like it a summary table that tells me TackTime, (Min, Max, Mean) of the variables for the 10 seconds before TackTime and the same for the 60 seconds after the manoeuvres.

I have tried group_by and mutate but it is not working and very clunky,

Any input would be greatly appreciated.

Here is my sample data

dput (df)
structure(list(Time = structure(c(1650888776.16, 1650888777.888, 
1650888778.752, 1650888779.616, 1650888780.48, 1650888781.344, 
1650888783.072, 1650888783.936, 1650888784.8, 1650888785.664, 
1650888786.528, 1650888787.392, 1650888789.12, 1650888789.984, 
1650888790.848, 1650888791.712, 1650888792.576, 1650888794.304, 
1650888795.168, 1650888796.032, 1650888796.896, 1650888797.76, 
1650888798.624, 1650888800.352, 1650888801.216, 1650888802.08, 
1650888802.944, 1650888803.808, 1650888805.536, 1650888806.4, 
1650888807.264, 1650888808.128, 1650888808.992, 1650888809.856, 
1650888811.584, 1650888812.448, 1650888813.312, 1650888814.176, 
1650888815.04, 1650888816.768, 1650888817.632, 1650888818.496, 
1650888819.36, 1650888820.224, 1650888821.088, 1650888822.816, 
1650888823.68, 1650888824.544, 1650888825.408, 1650888826.272, 
1650888828, 1650888828.864, 1650888829.728, 1650888830.592, 1650888831.456, 
1650888832.32, 1650888834.048, 1650888834.912, 1650888835.776, 
1650888836.64, 1650888837.504, 1650888839.232, 1650888840.096, 
1650888840.96, 1650888841.824, 1650888842.688, 1650888843.552, 
1650888845.28, 1650888846.144, 1650888847.008, 1650888847.872, 
1650888848.736, 1650888849.6, 1650888851.328, 1650888852.192, 
1650888853.056, 1650888853.92, 1650888854.784, 1650888856.512, 
1650888857.376, 1650888858.24, 1650888859.104, 1650888859.968, 
1650888860.832, 1650888862.56, 1650888863.424, 1650888864.288, 
1650888865.152, 1650888866.016, 1650888867.744, 1650888868.608, 
1650888869.472, 1650888870.336, 1650888871.2, 1650888872.064, 
1650888873.792, 1650888874.656, 1650888875.52, 1650888876.384, 
1650888877.248, 1650888878.976, 1650888879.84, 1650888880.704, 
1650888881.568, 1650888882.432, 1650888883.296, 1650888885.024, 
1650888885.888, 1650888886.752, 1650888887.616, 1650888888.48, 
1650888890.208, 1650888891.072, 1650888891.936, 1650888892.8, 
1650888893.664, 1650888895.392, 1650888968.832, 1650888969.696, 
1650888970.56, 1650888971.424, 1650888972.288, 1650888973.152, 
1650888974.88, 1650888975.744, 1650888976.608, 1650888977.472, 
1650888978.336, 1650888980.064, 1650888980.928, 1650888981.792, 
1650888982.656, 1650888983.52, 1650888984.384, 1650888986.112, 
1650888986.976, 1650888987.84, 1650888988.704, 1650888989.568, 
1650888991.296, 1650888992.16, 1650888993.024, 1650888993.888, 
1650888994.752, 1650888995.616, 1650888997.344, 1650888998.208, 
1650888999.072, 1650888999.936, 1650889000.8, 1650889001.664, 
1650889003.392, 1650889004.256, 1650889005.12, 1650889005.984, 
1650889006.848, 1650889007.712, 1650889009.44, 1650889010.304, 
1650889011.168, 1650889012.032, 1650889012.896, 1650889014.624, 
1650889015.488, 1650889016.352, 1650889017.216, 1650889018.08, 
1650889019.808, 1650889020.672, 1650889021.536, 1650889022.4, 
1650889023.264, 1650889024.128, 1650889025.856, 1650889026.72, 
1650889027.584, 1650889028.448, 1650889029.312, 1650889031.04, 
1650889031.904, 1650889032.768, 1650889033.632, 1650889034.496, 
1650889035.36, 1650889037.088, 1650889037.952, 1650889038.816, 
1650889039.68, 1650889040.544, 1650889041.408, 1650889043.136, 
1650889044, 1650889044.864, 1650889045.728, 1650889046.592, 1650889048.32, 
1650889049.184, 1650889050.048, 1650889050.912, 1650889051.776, 
1650889053.504, 1650889054.368, 1650889055.232, 1650889056.096, 
1650889056.96, 1650889057.824, 1650889059.552, 1650889060.416, 
1650889061.28, 1650889062.144, 1650889063.008, 1650889064.736, 
1650889065.6, 1650889066.464, 1650889067.328, 1650889068.192, 
1650889069.056, 1650889070.784, 1650889071.648, 1650889072.512, 
1650889073.376, 1650889074.24, 1650889075.968, 1650889076.832, 
1650889077.696, 1650889078.56, 1650889079.424, 1650889080.288, 
1650889082.016, 1650889082.88, 1650889083.744, 1650889084.608, 
1650889085.472, 1650889086.336), tzone = "", class = c("POSIXct", 
"POSIXt")), TackTime = structure(c(1650888836, 1650888836, 1650888836, 
1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 
1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 
1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 
1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 
1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 
1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 
1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 
1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 
1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 
1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 
1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 
1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 
1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 
1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 
1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 
1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 
1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 
1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 
1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 1650888836, 
1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 
1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 
1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 
1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 
1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 
1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 
1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 
1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 
1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 
1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 
1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 
1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 
1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 
1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 
1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 
1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 
1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 
1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 
1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 1650889028, 
1650889028, 1650889028, 1650889028), class = c("POSIXct", "POSIXt"
), tzone = ""), BSP = c(7.76, 7.908, 8.106, 7.95, 7.876, 7.577, 
7.465, 7.205, 7.056, 7.076, 7.11, 7.21, 7.309, 7.328, 7.319, 
7.251, 7.222, 7.153, 6.978, 6.859, 6.628, 6.49, 6.201, 5.994, 
5.87, 5.858, 5.715, 5.62, 5.193, 5.04, 4.957, 4.864, 4.844, 4.747, 
4.636, 4.678, 4.688, 4.682, 4.685, 4.626, 4.471, 4.424, 4.218, 
3.752, 3.605, 3.557, 3.466, 3.518, 3.596, 3.688, 4.07, 4.312, 
4.471, 4.587, 4.607, 4.592, 4.412, 4.108, 3.596, 3.337, 3.285, 
3.165, 3.009, 3.446, 3.982, 4.401, 5.093, 5.515, 5.849, 6.438, 
7.281, 7.542, 7.881, 7.992, 8.098, 7.988, 8.163, 7.507, 7.387, 
7.289, 7.127, 7.095, 6.94, 7.183, 7.375, 7.445, 7.548, 7.518, 
7.336, 7.329, 7.37, 7.343, 7.413, 7.62, 7.741, 7.83, 7.892, 7.737, 
7.8, 7.715, 7.751, 7.698, 7.42, 7.387, 7.364, 7.351, 7.441, 7.503, 
7.607, 7.532, 7.523, 7.595, 7.636, 7.707, 7.562, 7.528, 7.635, 
6.779, 6.881, 6.94, 7.028, 6.972, 6.816, 6.86, 7.208, 7.173, 
7.088, 6.662, 6.512, 6.593, 6.728, 6.745, 7.017, 6.858, 7.042, 
7.52, 7.632, 7.756, 7.814, 7.989, 8.018, 7.97, 7.909, 7.768, 
7.931, 8.048, 8.133, 8.132, 8.141, 8.708, 8.669, 8.686, 8.826, 
8.67, 8.281, 8.24, 8.151, 8.281, 8.378, 8.47, 8.592, 8.864, 8.891, 
8.65, 8.228, 7.778, 7.699, 7.581, 7.523, 7.425, 7.411, 7.181, 
6.415, 6.108, 4.971, 4.566, 4.329, 4.257, 4.51, 5.049, 5.362, 
5.841, 5.921, 5.957, 5.914, 5.957, 6.056, 6.181, 6.423, 6.485, 
6.478, 6.447, 6.462, 6.416, 6.415, 6.492, 6.667, 6.661, 6.356, 
5.734, 5.365, 5.214, 4.646, 4.218, 3.933, 3.714, 3.402, 3.073, 
2.689, 2.511, 2.517, 2.505, 2.876, 3.246, 3.441, 3.72, 3.772, 
3.823, 3.865, 3.908, 4.015, 4.152, 4.238, 4.222, 4.257, 4.238, 
4.238, 4.219, 4.094, 4.121, 4.354, 4.715, 5.35, 5.733), TWA = c(102.5, 
103, 100.7, 99.4, 96.1, 97.6, 97.1, 95.9, 97.5, 98, 99.6, 100.2, 
104.2, 105.1, 111.1, 114.6, 114.9, 114.1, 111.4, 110.1, 108.3, 
107.5, 106.8, 106.3, 104.4, 102.4, 99.2, 93.2, 91.7, 90.6, 90.3, 
91.5, 90.1, 87.8, 85.9, 83.9, 81.2, 78.5, 71.2, 69, 51.7, 43.4, 
45, 32.3, 36.5, 40.2, 42.7, 44.5, 46.7, 48.1, 48.4, 48.4, 46.7, 
41, 28.8, 25.9, 13.8, 0.2, -15.5, -28.1, -42.4, -58.5, -52.5, 
-59.7, -72.4, -76.3, -82.3, -91.8, -111.3, -116.1, -118.8, -123.7, 
-128.3, -130.9, -132.2, -134.8, -136.2, -137.5, -136, -132.2, 
-128.6, -126, -125.5, -124.3, -124, -124.2, -124.2, -123.9, -122.6, 
-119.9, -118.2, -117.4, -117.9, -120.6, -119.6, -119.5, -121, 
-122.3, -122.8, -125, -124.6, -124.8, -123.4, -123, -124, -125.8, 
-126.1, -127.5, -127.3, -126.9, -126.2, -124.7, -124.7, -123.9, 
-126.8, -126.5, -130, -131.3, -127.4, -122.5, -117.8, -116.9, 
-119.7, -120.2, -117.9, -110.9, -99.9, -91.3, -93.7, -89.6, -89.1, 
-89.6, -90.8, -89.2, -95.6, -91.4, -92.4, -93.5, -91.3, -89, 
-87, -90.4, -91.7, -95.8, -97.5, -94.2, -92.3, -96.9, -93.7, 
-92.3, -93.2, -96.1, -95.5, -97.8, -105.4, -113.3, -120.2, -128.9, 
-134.8, -138, -138.5, -130.7, -120.9, -111, -100.7, -92, -87.6, 
-86.2, -81.6, -74.9, -62.5, -53.7, -40.8, -28.4, -6.1, 16.4, 
29.2, 37.6, 42.5, 40.6, 41.7, 42, 40.9, 41.2, 41.5, 41.6, 43.8, 
42.5, 39.3, 38.9, 41.8, 38.7, 40.7, 42.5, 47.4, 42.8, 34.5, 21.6, 
7.4, 1.5, -1.8, -3.7, -6.3, -6.8, 4.3, 4.7, 4.8, 14.8, 17.4, 
23.4, 33.1, 44, 46.8, 51.2, 40.1, 35.9, 33.1, 31.7, 32, 34.7, 
36.7, 35.9, 35, 28.8, 24, 22.9, 24.1, 24, 33.3, 49, 71.3, 78.5, 
89.4, 101.3)), row.names = c(NA, -234L), class = c("data.table", 
"data.frame"), .internal.selfref = <pointer: 0x7ff01480d4e0>)

Sorry for the large dput but it requires a big sample.

CodePudding user response：

How about this:

dat %>% 
  group_by(TackTime) %>% 
  mutate(bef_aft = case_when(TackTime - Time > 0 & TackTime-Time <=10 ~ "before",
                            Time-TackTime > 0 & Time-TackTime <= 60 ~ "after", 
                            TRUE ~ NA_character_), 
         bef_aft = factor(bef_aft, levels=c("before", "after"))) %>% 
  group_by(TackTime, bef_aft) %>% 
  summarise( across(c(BSP, TWA), ~min(.x, na.rm = TRUE), .names = "min_{.col}"),
             across(c(BSP, TWA), ~max(.x, na.rm = TRUE), .names = "max_{.col}"), 
             across(c(BSP, TWA), ~mean(.x, na.rm = TRUE), .names = "mean_{.col}")) %>% 
  filter(!is.na(bef_aft))

#>   TackTime            bef_aft min_BSP min_TWA max_BSP max_TWA mean_BSP mean_TWA
#>   <dttm>              <fct>     <dbl>   <dbl>   <dbl>   <dbl>    <dbl>    <dbl>
#> 1 2022-04-25 08:13:56 before     3.60   -15.5    4.61    48.4     4.24     28.6
#> 2 2022-04-25 08:13:56 after      3.01  -138.     8.16   -28.1     6.94   -115. 
#> 3 2022-04-25 08:17:08 before     4.57   -87.6    7.70    16.4     6.69    -50.5
#> 4 2022-04-25 08:17:08 after      2.50    -6.8    6.67   101.      4.77     34.0