# Aggregating and averaging time series data to have same nrow per group

I suspect the solution will be pretty straightforward but I'm struggling to figure it out. The problem: I have an experimental design with several video stimuli of different time lengths (between 90s and 120s), seen by different subjects. Data is in longform and I'm using mixed models/multilevel modeling analysis, with separate random intercepts for stim and subject. Toy data example would look something like this for the first 10 rows:

   Time Raw_EDA   Condition      Video      Subject
1   5.578027907 T       Crystalography  1
2   5.696452252 T       Crystalography  1
3   5.752586797 T       Crystalography  1
4   5.781791269 T       Crystalography  1
5   5.790344838 T       Crystalography  1
6   5.701012485 T       Crystalography  1
7   5.567081224 T       Crystalography  1
8   5.47263799  T       Crystalography  1
9   5.413115417 T       Crystalography  1
10  5.391219614 T       Crystalography  1


I'm trying to figure out the best way to control for the differences in time, because by having different numbers of rows per video I'm systematically introducing missing data, which might skew the results. At first, I tried to standardize within stim, e.g.:

data %>%
group_by(Video) %>%
mutate(Time_sd = scale(Time))


Now I'm thinking testing an alternative solution that is often used for RM ANOVA (which cannot handle differences in number of repetitions).

Basically, I'd like to aggregate my data in such a way that I end up having 90 rows per Video regardless of the length of the video (with the corresponding average for the measure of interest). So if I originally have anything between 90 and 120 rows per video (i.e., one row per second), for videos of max length 120 s, I'd like to average the values every 1.3 rows (120/90), while for videos of max length 90s, I'd like to leave the values as they are.

I can't think of a way to use summarise() for this. Is there some tidy function specialized in this type of aggregating? I saw aggregate() exists but couldn't figure out if or how it can be used in this case.

The main idea in your solution is to add another ID for aggregation.

First, let's create fictitious data:

library(dplyr)
data <- tibble(Time = c(1:90, 1:91, 1:120),
Video = c(rep("V1",90), rep("V2",91), rep("V3",120)))


Second, add the data and Time_agg_ID, for which use the standard rounding operation. To validate and show how it works add the raw_EDA_agg as a column first to the original data. You don't need this actually in your data:

data %<>% mutate(raw_EDA = rnorm(n = nrow(data), mean = 5)) %>%
group_by(Video) %>%
mutate(Time_agg_ID = round(Time*90/max(Time))) %>%
group_by(Video, Time_agg_ID) %>% # to check that it works use mutate
mutate(raw_EDA_agg = mean(raw_EDA))
data %>% tail(10)


Third, now use summary instead and look at the end result:

data %>%
group_by(Video, Time_agg_ID) %>%
summarise(raw_EDA_agg = mean(raw_EDA), .groups = "drop") -> data_agg
data_agg %>% tail(10)


Hope this helps.

• Woho! great solution! it worked, now I have to go through it slowly to make sure I understand what I'm doing. Thanks a thousand. Mar 7, 2022 at 16:02