Average a multiple number of rows for every column, multiple times-CodePudding

Here I have a snippet of my dataset. The rows indicate different days of the year.

The Substations represent individuals, there are over 500 individuals. The 10 minute time periods run all the way through 24 hours.

I need to find an average value for each 10 minute interval for each individual in this dataset. This should result in single row for each individual substation, with the respective average value for each time interval.

I have tried:

meanbygroup <- stationgroup %>%
  group_by(Substation) %>%
  summarise(means = colMeans(tenminintervals[sapply(tenminintervals, is.numeric)]))

But this averages the entire column and I am left with the same average values for each individual substation.

So for each individual substation, I need an average for each individual time interval.

Please help!

CodePudding user response：

Try using summarize(across()), like this:

df %>% 
  group_by(Substation) %>% 
  summarize(across(everything(), ~mean(.x, na.rm=T)))

Output:

   Substation `00:00` `00:10` `00:20`
   <chr>        <dbl>   <dbl>   <dbl>
 1 A          -0.233   0.110  -0.106 
 2 B           0.203  -0.0997 -0.128 
 3 C          -0.0733  0.196  -0.0205
 4 D           0.0905 -0.0449 -0.0529
 5 E           0.401   0.152  -0.0957
 6 F           0.0368  0.120  -0.0787
 7 G           0.0323 -0.0792 -0.278 
 8 H           0.132  -0.0766  0.157 
 9 I          -0.0693  0.0578  0.0732
10 J           0.0776 -0.176  -0.0192
# … with 16 more rows

Input:

set.seed(123)

df = bind_cols(
  tibble(Substation = sample(LETTERS,size = 1000, replace=T)),
  as_tibble(setNames(lapply(1:3, function(x) rnorm(1000)),c("00:00", "00:10", "00:20")))
) %>% arrange(Substation)


# A tibble: 1,000 × 4
   Substation `00:00` `00:10` `00:20`
   <chr>        <dbl>   <dbl>   <dbl>
 1 A            0.121  -1.94   0.137 
 2 A           -0.322   1.05   0.416 
 3 A           -0.158  -1.40   0.192 
 4 A           -1.85    1.69  -0.0922
 5 A           -1.16   -0.455  0.754 
 6 A            1.95    1.06   0.732 
 7 A           -0.132   0.655 -1.84  
 8 A            1.08   -0.329 -0.130 
 9 A           -1.21    2.82  -0.0571
10 A           -1.04    0.237 -0.328 
# … with 990 more rows