How to mutate a new variable that summarizes X number of previous rows based on an if_else statement?

user_15802182 0
Viewed 59

I have a spatial data frame with GPS points every second as well as the distance traveled between points and turning angles. I'd like to create a new column that summarises the amount of time it took to travel 10m and the average turning angle over the last 10 meters.

row x y date dx dy dist dt R2n abs.angle rel.angle
1 580988. 12981141. 2021-07-18 07:00:07 -1.99 -0.0128 1.99 1 0 -3.14 NA
2 580986. 12981141. 2021-07-18 07:00:08 0 0 0 1 3.94 NA NA
3 580986. 12981141. 2021-07-18 07:00:09 2.97 1.13 3.18 1 3.94 0.363 -2.79
4 580989. 12981142. 2021-07-18 07:00:10 0 0 0 1 2.21 NA NA

In other words, I'd like a column "sum.dist" that should always be close to 10 and then two more columns "sum.dt" and average.rel.angle that summarize the time it took the subject to move the previous 10 meters and the average rel.angle over those last 10 meters. I'm aware I'm probably going to need some kind of loop to check to see when the sum of distances is approximately 10 (say the first value to be greater than 10 meters?) and then count the number of rows and use that number of rows to summarize over row n and rows n-x. Unfortunately, I'm having a really hard time even figuring out how to start here. I'm comfortable with basic dplyr functions like mutate and summarise but I am inexperienced with loops.

dplyr if-statement r for-loop loops
1 Answers

Here's one (kinda clunky) way of doing this. I am not sure how you want to group your data by distance. Here, I assumed the first row was 0 and grouped data 0-9, 10-19, 20-29, etc. Is this what you were expecting?

library(dplyr)

# Calculate distance between consecutive rows
df$dist <- c(0, sqrt(diff(df$x)^2 + diff(df$y)^2))
# Calculate a rolling distance
df$roll <- cumsum(df$dist)
# Group rows by every 10 meters
df$grp <- factor(floor(df$roll/10) * 10)
# Calculate summary values by group
summ <- df %>%
  group_by(grp) %>%
  summarize(
    sum.dt = sum(dt),
    average.rel.angle = mean(rel.angle, na.rm = TRUE))
# Combine summary with original data
merge(df, summ, by="grp")

Data:

structure(list(row = c("1", "2", "3", "4", "5", "6", "7"), x = c(580985, 
580990, 580995, 581000, 581005, 581010, 581015), y = c(12981141, 
12981141, 12981141, 12981141, 12981141, 12981141, 12981141), 
    date = structure(list(sec = c(7, 8, 9, 10, 11, 12, 13), min = c(0L, 
    0L, 0L, 0L, 0L, 0L, 0L), hour = c(7L, 7L, 7L, 7L, 7L, 7L, 
    7L), mday = c(18L, 18L, 18L, 18L, 18L, 18L, 18L), mon = c(6L, 
    6L, 6L, 6L, 6L, 6L, 6L), year = c(121L, 121L, 121L, 121L, 
    121L, 121L, 121L), wday = c(0L, 0L, 0L, 0L, 0L, 0L, 0L), 
        yday = c(198L, 198L, 198L, 198L, 198L, 198L, 198L), isdst = c(1L, 
        1L, 1L, 1L, 1L, 1L, 1L), zone = c("EDT", "EDT", "EDT", 
        "EDT", "EDT", "EDT", "EDT"), gmtoff = c(NA_integer_, 
        NA_integer_, NA_integer_, NA_integer_, NA_integer_, NA_integer_, 
        NA_integer_)), class = c("POSIXlt", "POSIXt")), dx = c(-1.99, 
    0, 2.97, 0, 0, 0, 0), dy = c(-0.0128, 0, 1.13, 0, 0, 0, 0
    ), dist = c(1.99, 0, 3.18, 0, 0, 0, 0), dt = c(1, 1, 1, 1, 
    1, 1, 1), R2n = c(0, 3.94, 3.94, 2.21, 2.21, 2.21, 2.21), 
    abs.angle = c(-3.14, NA, 0.363, NA, NA, NA, NA), rel.angle = c(NA, 
    NA, -2.79, NA, NA, NA, NA)), row.names = c("a", "b", "d", 
"e", "f", "g", "h"), class = "data.frame")
user_100218760
Related