Use values from groups before and after certain focal groups

user_6203884 0
Viewed 156

I have a value column 'v', grouped by an 'id' column which indicates if values are standards ('s') or collected in field (1.1 - 1.5; 2.1 - 2.5), like this:

    id  v
1    s  8
2    s  3
3    s  3
4  1.1  7
5  1.2  3
6  1.3  6
7  1.4  5
8  1.5 10
9    s  3
10   s  8
11   s  6
12 2.1  2
13 2.2  8
14 2.3  1
15 2.4  4
16 2.5  3
17   s  5
18   s  2
19   s  5

For each sequence of field measurements, I need to calculate the mean and standard deviation of all standard values immediately before and after the focal group of field values. Thus, for first group of field samples 1.1-1.5, I need to calculate mean and sd of 'v' for the previous (rows 1-3) and following (rows 9-11) chunks of standard values. Similarly for the second group of field samples (2.1-2.5), I need the mean and sd of standard values in rows 9-11 and 17-19.

Note that number of the samples and standards vary across datasets. They are not always in a 3-5-3-5 configuration as in this simplified example.

The desired result is:

    id  v v_mean v_sd
1    s  8     NA   NA
2    s  3     NA   NA
3    s  3     NA   NA
4  1.1  7   5.17 2.48 # 5.17 = mean of previous and following run of standard values                        
5  1.2  3   5.17 2.48 # mean(d$v[c(1:3, 9:11)])
6  1.3  6   5.17 2.48 # 2.48 = sd of previous and following run of standard values 
7  1.4  5   5.17 2.48 # sd(d$v[c(1:3, 9:11)]) 
8  1.5 10   5.17 2.48
9    s  3     NA   NA
10   s  8     NA   NA
11   s  6     NA   NA
12 2.1  2   4.83 2.14 # 4.83 = mean(d$v[c(9:11, 17:19)])
13 2.2  8   4.83 2.14 # 2.14 = sd(d$v[c(9:11, 17:19)])
14 2.3  1   4.83 2.14
15 2.4  4   4.83 2.14
16 2.5  3   4.83 2.14
17   s  5     NA   NA
18   s  2     NA   NA
19   s  5     NA   NA

These questions were helpful: How to take the mean of values between a range of rows in R? and How to take the mean of last 10 values in a column before a missing value using R?, but my problem is the continual shifting of the averages.

Data:

d = structure(list(id = c("s", "s", "s", "1.1", "1.2", "1.3","1.4", "1.5", "s", "s", "s",
                          "2.1", "2.2", "2.3", "2.4", "2.5", "s", "s", "s"),
                   v = c(8, 3, 3, 7, 3, 6, 5, 10, 3,
                         8, 6, 2, 8, 1, 4, 3, 5, 2, 5)),
              row.names = c(NA,  -19L), class = "data.frame", index = integer(0))
mean r group-by average
2 Answers

If you want to maintain the output in the same format of the original data here is one way :

library(dplyr)
#Create group column
gc1 <- gc %>% mutate(grp = data.table::rleid(Sample_ID == 'Standard'))

gc1 %>%
  #Keep only "Standard" rows
  filter(Sample_ID == 'Standard') %>%
  #For each group
  group_by(grp) %>%
  #Get mean of Area
  summarise(std_avg = mean(Area)) %>%
  #Get rolling mean of every 2 groups
  mutate(std_avg = zoo::rollmeanr(std_avg, 2, fill = NA), 
         #Adjust group value for output
         grp = grp - 1) %>%
  #Join with original data
  right_join(gc1, by = 'grp') %>%
  #Arrange data
  arrange(grp)

this returns :

#    grp std_avg Sample_ID Date       Time     Component Retention_Time  Area
#   <dbl>   <dbl> <chr>     <chr>      <chr>    <chr>              <dbl> <dbl>
# 1     1     NA  Standard  2019-01-16 15:34:05 CO2                 3.61 2824.
# 2     1     NA  Standard  2019-01-16 15:39:03 CO2                 3.62 2837.
# 3     1     NA  Standard  2019-01-16 15:44:02 CO2                 3.62 2876.
# 4     2   2828. 1.1       2019-01-16 15:49:00 CO2                 3.62 3904.
# 5     2   2828. 1.2       2019-01-16 15:53:58 CO2                 3.61 3482.
# 6     2   2828. 1.3       2019-01-16 15:58:56 CO2                 3.61 3452.
# 7     2   2828. 1.4       2019-01-16 16:03:55 CO2                 3.61 3278.
# 8     2   2828. 1.5       2019-01-16 16:08:53 CO2                 3.62 3687.
# 9     3     NA  Standard  2019-01-16 16:13:51 CO2                 3.62 2815.
#10     3     NA  Standard  2019-01-16 16:18:50 CO2                 3.61 2812.
#11     3     NA  Standard  2019-01-16 16:23:48 CO2                 3.62 2805.
#12     4   2821. 2.1       2019-01-16 16:28:46 CO2                 3.62 3996.
#13     4   2821. 2.2       2019-01-16 16:33:44 CO2                 3.61 3317.
#14     4   2821. 2.3       2019-01-16 16:38:42 CO2                 3.61 3216.
#15     4   2821. 2.4       2019-01-16 16:43:41 CO2                 3.62 3978.
#16     4   2821. 2.5       2019-01-16 16:48:39 CO2                 3.62 3727.
#17     5     NA  Standard  2019-01-16 16:53:37 CO2                 3.62 2841.
#18     5     NA  Standard  2019-01-16 16:58:36 CO2                 3.62 2823.
#19     5     NA  Standard  2019-01-16 17:03:34 CO2                 3.61 2829.
user_39629140

A data.table alternative:

library(data.table)
setDT(d)
 
# create a grouping variable based on runs of "s" or not "s" 
d[ , g := rleid(id == "s")]

# group data by the run id
# use .GRP to refer to the current group
# subtract and add 1 to refer to previous and following groups
# calculate mean and sd and add new columns by reference 
d[ , c("v_mean", "v_sd") := {
  d[g %in% (.GRP + c(-1, 1)), .(mean(v), sd(v))]
}, by = g]

# if desired, set mean and sd for standards to NA
d[id == "s", `:=`(v_mean = NA, v_sd = NA)] 
#      id  v g   v_mean     v_sd
#  1:   s  8 1       NA       NA
#  2:   s  3 1       NA       NA
#  3:   s  3 1       NA       NA
#  4: 1.1  7 2 5.166667 2.483277
#  5: 1.2  3 2 5.166667 2.483277
#  6: 1.3  6 2 5.166667 2.483277
#  7: 1.4  5 2 5.166667 2.483277
#  8: 1.5 10 2 5.166667 2.483277
#  9:   s  3 3       NA       NA
# 10:   s  8 3       NA       NA
# 11:   s  6 3       NA       NA
# 12: 2.1  2 4 4.833333 2.136976
# 13: 2.2  8 4 4.833333 2.136976
# 14: 2.3  1 4 4.833333 2.136976
# 15: 2.4  4 4 4.833333 2.136976
# 16: 2.5  3 4 4.833333 2.136976
# 17:   s  5 5       NA       NA
# 18:   s  2 5       NA       NA
# 19:   s  5 5       NA       NA
user_18517120
Related