Averaging my data into quarterly means (nfrequency error message)-CodePudding

I'm trying to average my data into quarterly means but when i use the following code i get this error

code

quarterly = aggregate(overturning_ts, nfrequency = 4, mean)

error message

Error in aggregate.ts(overturning_ts, nfrequency = 4, mean) : 
cannot change frequency from 1 to 4

date snippet

overturning_ts
    year month day hour Quarter Days_since_start Overturning_Strength
[1,] 2004     4   2    0       2              1.0             9.689933
[2,] 2004     4   2   12       2              1.5            10.193495
[3,] 2004     4   3    0       2              2.0            10.660849
[4,] 2004     4   3   12       2              2.5            11.077229
[5,] 2004     4   4    0       2              3.0            11.432414
[6,] 2004     4   4   12       2              3.5            11.721769

all data available here, after downloading i just converted it to a time series to get overturned_ts: https://drive.google.com/file/d/1NV3aKsvpPkGatLnuUMbvLpxhcYs_gdM-/view?usp=sharing

outcome i am looking for here;

   Qtr1   Qtr2   Qtr3   Qtr4
1960  160.1  129.7   84.8  120.1
1961  160.1  124.9   84.8  116.9
1962  169.7  140.9   89.7  123.3

CodePudding user response：

Like this?

library(tidyverse)

df %>%  
  group_by(year, Quarter) %>% 
  summarise(avg_overturning = mean(Overturning_Strength, na.rm = TRUE)) %>%  
  pivot_wider(names_from = Quarter, 
              values_from = avg_overturning, names_sort = TRUE)
    
# A tibble: 11 x 5
# Groups:   year [11]
    year   `1`   `2`   `3`   `4`
   <dbl> <dbl> <dbl> <dbl> <dbl>
 1  2004 NA     15.3  23.7  17.7
 2  2005 14.0   18.7  21.7  22.5
 3  2006 17.1   17.7  20.5  20.8
 4  2007 18.9   15.5  17.9  20.0
 5  2008 18.5   15.5  16.1  20.2
 6  2009 16.3   14.9  15.3  12.2
 7  2010  8.89  16.2  19.7  15.1
 8  2011 15.1   16.0  17.8  18.4
 9  2012 15.8   11.9  16.4  16.5
10  2013 11.9   17.1  17.6  18.8
11  2014 15.1   NA    NA    NA

CodePudding user response：

We can use base R

with(df1, tapply(Overturning_Strength, list(year, Quarter),
     FUN = mean, na.rm = TRUE))
             1        2        3        4
2004        NA 15.34713 23.74958 17.65220
2005 13.950342 18.66797 21.73983 22.49755
2006 17.116492 17.71430 20.50190 20.84159
2007 18.918347 15.46002 17.87220 20.01701
2008 18.508666 15.53064 16.06696 20.21658
2009 16.255357 14.85671 15.28269 12.16084
2010  8.889602 16.18042 19.74318 15.05649
2011 15.130970 15.96652 17.79070 18.35192
2012 15.793286 11.90334 16.37805 16.45706
2013 11.867353 17.07688 17.60640 18.81432
2014 15.119643       NA       NA       NA

Or with xtabs from base R

xtabs(Overturning_Strength ~ year   Quarter, 
        df1)/table(df1[c("year", "Quarter")])
      Quarter
year           1         2         3         4
  2004           15.347126 23.749583 17.652204
  2005 13.950342 18.667970 21.739828 22.497550
  2006 17.116492 17.714298 20.501897 20.841587
  2007 18.918347 15.460020 17.872199 20.017007
  2008 18.508666 15.530639 16.066960 20.216581
  2009 16.255357 14.856708 15.282690 12.160845
  2010  8.889602 16.180422 19.743183 15.056486
  2011 15.130970 15.966518 17.790699 18.351916
  2012 15.793286 11.903337 16.378045 16.457062
  2013 11.867353 17.076883 17.606403 18.814323
  2014 15.119643

CodePudding user response：

As (it seems like) your data is already structured with quarters as a column a possible solution could be to use dplyr directly without making it a timeseries object with ts(). We would group_by every year-quarter pair, summarise the strength-value, and change to a wide format for the desired output with pivot_wider.

library(dplyr)  

overturning |>
  select(year, Quarter, Overturning_Strength) |>
  group_by(year, Quarter) |>
    summarise(value = mean(Overturning_Strength)) |>
  ungroup() |>
  pivot_wider(year, names_from = Quarter, names_prefix = "Qtr", names_sort = TRUE)