Annual mean using common months over the year for different plots

Viewed 89

data

data Hi, I have a large dataset like this. I want to calculate the annual mean that I can do by dplyr:

    annual_mean<-
              df %>%
              group_by(year) %>% 
              summarise(across(Plot1:Plot3, ~ mean(.x, na.rm = TRUE)))

But I need to calculate the annual mean using the months commonly present over the years excluding the NA. For example, in Plot1 may be 2,4,5 and 7 months are common over the years. So, I have to calculate the annual mean only using these months. And then different common months for Plot2, Plot3.

New member! Any help is highly appreciated!

Reproducible Sample Data

> dput(head(df, 100))
structure(list(Plot1 = c(0.77, 0.75, 0.75, 0.77, 0.79, 0.8, 0.8, 
0.83, 0.81, 0.85, 0.85, NA, NA, 0.69, 0.66, 0.74, 0.63, 0.65, 
0.65, 0.7, 0.7, 0.7, 0.79, 0.8, 0.75, 0.82, 0.8, 0.83, 0.8, 0.82, 
NA, 0.86, 0.8, 0.81, 0.78, 0.78, 0.73, 0.79, 0.74, 0.72, 0.76, 
0.71, 0.71, 0.72, 0.73, 0.71, 0.73, 0.74, 0.79, 0.78, 0.78, 0.83, 
0.8, 0.85, 0.86, 0.78, 0.76, 0.73, 0.72, 0.69, 0.73, 0.65, 0.63, 
0.63, 0.65, 0.69, 0.76, 0.7, 0.7, 0.71, 0.72, 0.77, 0.71, 0.76, 
0.77, 0.78, 0.8, 0.76, 0.76, 0.51, 0.74, 0.71, 0.73, 0.64, 0.65, 
0.63, 0.61, 0.64, 0.71, 0.68, 0.74, 0.7, 0.76, 0.74, 0.71, 0.74, 
0.8, 0.78, 0.79, 0.69), Plot2 = c(NA, 0.38, 0.45, 0.48, 0.76, 
0.88, 0.91, 0.9, 0.89, 0.89, 0.89, 0.84, 0.82, 0.83, 0.67, 0.62, 
0.58, 0.6, 0.57, NA, NA, NA, NA, 0.42, 0.48, 0.53, 0.49, 0.51, 
0.66, 0.89, NA, 0.87, 0.88, 0.85, 0.85, 0.85, 0.81, 0.69, 0.67, 
0.57, 0.54, NA, NA, NA, NA, NA, 0.44, 0.51, 0.48, 0.38, 0.51, 
0.83, 0.91, 0.91, 0.87, 0.89, 0.84, 0.87, 0.58, 0.75, 0.61, 0.55, 
0.55, 0.55, NA, 0.61, 0.6, NA, NA, NA, NA, NA, NA, 0.78, 0.87, 
0.91, 0.92, 0.9, 0.9, 0.9, 0.89, 0.88, 0.88, 0.82, 0.56, 0.54, 
0.54, 0.57, NA, NA, NA, NA, NA, NA, NA, 0.28, NA, 0.69, 0.8, 
0.91), Plot3 = c(NA, 0.77, 0.65, 0.7, 0.61, 0.63, 0.6, 0.59, 
0.55, 0.53, 0.49, 0.44, 0.45, 0.35, 0.48, 0.51, 0.7, 0.63, 0.7, 
0.7, NA, NA, NA, NA, 0.38, NA, 0.67, 0.59, 0.59, 0.61, 0.56, 
0.53, 0.54, 0.51, 0.32, 0.37, 0.43, 0.4, 0.67, 0.6, 0.65, 0.67, 
0.72, NA, 0.72, NA, 0.75, 0.7, 0.69, 0.66, 0.62, 0.6, 0.57, 0.54, 
0.54, 0.53, 0.49, 0.33, 0.31, 0.41, 0.46, 0.47, 0.52, 0.69, 0.74, 
0.7, NA, NA, 0.79, NA, 0.72, 0.72, 0.64, 0.6, 0.6, 0.58, 0.58, 
0.54, 0.53, 0.51, 0.51, 0.49, 0.37, 0.51, 0.48, 0.5, 0.64, 0.77, 
NA, NA, 0.78, 0.77, 0.72, NA, 0.68, 0.61, 0.59, 0.62, 0.59, 0.59
), Day = c(18, 5, 21, 6, 22, 8, 24, 9, 25, 11, 27, 12, 28, 13, 
29, 15, 31, 16, 2, 18, 1, 17, 2, 18, 6, 22, 7, 23, 9, 25, 10, 
26, 12, 28, 13, 29, 14, 30, 16, 1, 17, 3, 19, 1, 17, 2, 18, 6, 
22, 7, 23, 9, 25, 10, 26, 12, 28, 13, 29, 14, 30, 16, 1, 17, 
3, 19, 1, 17, 2, 18, 6, 22, 7, 23, 9, 25, 10, 26, 12, 28, 13, 
29, 14, 30, 16, 1, 17, 3, 19, 1, 17, 2, 18, 5, 21, 6, 22, 8, 
24, 9), year = c(2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 
2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 2000, 
2000, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 
2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 2001, 
2001, 2001, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 
2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 2002, 
2002, 2002, 2002, 2003, 2003, 2003, 2003, 2003, 2003, 2003, 2003, 
2003, 2003, 2003, 2003, 2003, 2003, 2003, 2003, 2003, 2003, 2003, 
2003, 2003, 2003, 2003, 2004, 2004, 2004, 2004, 2004, 2004, 2004, 
2004, 2004, 2004, 2004), months = c(2, 3, 3, 4, 4, 5, 5, 6, 6, 
7, 7, 8, 8, 9, 9, 10, 10, 11, 12, 12, 1, 1, 2, 2, 3, 3, 4, 4, 
5, 5, 6, 6, 7, 7, 8, 8, 9, 9, 10, 11, 11, 12, 12, 1, 1, 2, 2, 
3, 3, 4, 4, 5, 5, 6, 6, 7, 7, 8, 8, 9, 9, 10, 11, 11, 12, 12, 
1, 1, 2, 2, 3, 3, 4, 4, 5, 5, 6, 6, 7, 7, 8, 8, 9, 9, 10, 11, 
11, 12, 12, 1, 1, 2, 2, 3, 3, 4, 4, 5, 5, 6)), row.names = c(NA, 
-100L), class = c("tbl_df", "tbl", "data.frame"))
2 Answers

Updated I have modified my solutions based on what your have requested in comments. This time I first found the common months for each Plot and then went on to calculate the mean for every year.

library(dplyr)
library(purrr)

# I created a character vector of Plot names in order to ease iteration
Plots <- paste("Plot", c(1:3), sep = "")

# Then we iterate over each name selecting only the corresponding column 
# from the original data set and find the common months for each iteration.
Plots %>%
  map(~ df %>% 
        select(.x, months, year) %>%
        drop_na() %>%
        group_split(year) %>%
        map(~ .x %>% 
              select(months) %>%
              distinct()) %>% 
        reduce(~ intersect(.x, .y))) %>%
  set_names(nm = Plots) -> commons

Then we use the commons' months to apply a filter on the entire data set:

# This time we iterate over both commons and plot names at the same time

map2(Plots, commons, ~ 
       df %>% 
       select(.x, months, year) %>%
       filter(months %in% .y[["months"]]) %>%
       group_by(year) %>%
       summarise(across(starts_with("Plot"), ~ mean(.x, na.rm = TRUE)))) %>%
  exec(cbind, !!!.) %>%
  select(-c(3, 5))


    year Plot1 Plot2 Plot3
   <dbl> <dbl> <dbl> <dbl>
 1  2000 0.749 0.771 0.566
 2  2001 0.78  0.724 0.512
 3  2002 0.741 0.707 0.528
 4  2003 0.703 0.806 0.559
 5  2004 0.734 0.757 0.549
 6  2005 0.725 0.758 0.547
 7  2006 0.732 0.796 0.542
 8  2007 0.725 0.763 0.585
 9  2008 0.761 0.736 0.569
10  2009 0.766 0.776 0.575
11  2010 0.768 0.78  0.622
12  2011 0.781 0.787 0.549
13  2012 0.776 0.784 0.553
14  2013 0.774 0.787 0.598
15  2014 0.748 0.809 0.577
16  2015 0.731 0.797 0.587
17  2016 0.758 0.67  0.653
18  2017 0.799 0.76  0.599
19  2018 0.794 0.81  0.575

You have three Plot columns and you want to do the same thing with each one. In cases like this you can make your life easier by 'pivoting' the data into 'long' (i.e. 'tidy') format. Here is an approach using tidyr and dplyr:

library(dplyr)
library(tidyr)

tidied_df <- df %>%
  
  # First pivot into 'long' or 'tidy' format
  pivot_longer(Plot1:Plot3, names_to = "Plot", 
               values_to = "Value", names_prefix = "Plot") %>% 
  
  # First remove all missing values of 'Value'
  filter(!is.na(Value)) %>% 
  
  # Now turn 'implicit' missing months to explicit
  complete(Plot, year, months) %>% 
  
  # Can now add a column indicating whether the whole month is 'missing'
  # for a given year
  group_by(Plot, year, months) %>% 
  mutate(month_missing = all(is.na(Value))) %>% 
  
  # Now use the helper column to remove a month from all years if any
  # one year has only missing values for that month
  group_by(Plot, months) %>% 
  filter(!any(month_missing)) %>% 
  ungroup()
  
# We can take a look to see which months are present across all years
# for each plot like so:
tidied_df %>% 
  group_by(Plot) %>% 
  summarise(
    months = paste(sort(unique(months)), collapse = ", "), 
    .groups = "drop"
  )
#> # A tibble: 3 x 2
#>   Plot  months                         
#>   <chr> <chr>                          
#> 1 1     2, 3, 4, 5, 6, 7, 9, 10, 11, 12
#> 2 2     4, 5, 6, 7, 8, 9, 10, 11       
#> 3 3     3, 4, 5, 6, 7, 8, 9, 10
  

# Finally, we can compute the mean across each plot:
tidied_df %>% 
  group_by(Plot, year) %>% 
  summarise(Value = mean(Value, na.rn = TRUE), .groups = "drop")
#> # A tibble: 57 x 3
#>    Plot   year Value
#>    <chr> <dbl> <dbl>
#>  1 1      2000 0.749
#>  2 1      2001 0.78 
#>  3 1      2002 0.741
#>  4 1      2003 0.703
#>  5 1      2004 0.734
#>  6 1      2005 0.725
#>  7 1      2006 0.732
#>  8 1      2007 0.725
#>  9 1      2008 0.761
#> 10 1      2009 0.766
#> # … with 47 more rows

Edit

As requested, here is a method to filter for values of Plot where all years have at least 4 months in common:

tidied_df %>% 
  group_by(Plot) %>% 
  filter(n_distinct(months) > 4) %>% 
  ungroup()
Related