How to monthly summarise daily data, using dplyr and lubridate, only if less than 10 days per month are NAs?

user_7188346 0
Viewed 409

I have daily meteorological data (temperature and precipitation) from 1955 to 2017 from different locations and I want to summarize each variable into monthly averages but only if the number of NAs in each month is less than 10.

I put four months of temperature data with different amounts of NAs as example (1st month: 1 NA, 2nd month(31days): 30 NA, 3rd month: 0 NA, 4th month: all data as NA):

library(dplyr)
library(lubridate)    
exmpldf <- data.frame(DATE = c("1955-06-01", "1955-06-02", "1955-06-03", "1955-06-04", "1955-06-05", "1955-06-06", "1955-06-07", "1955-06-08", "1955-06-09", "1955-06-10", 
                                    "1955-06-11", "1955-06-12", "1955-06-13", "1955-06-14", "1955-06-15", "1955-06-16", "1955-06-17", "1955-06-18", "1955-06-19", "1955-06-20", 
                                    "1955-06-21", "1955-06-22", "1955-06-23", "1955-06-24", "1955-06-25", "1955-06-26", "1955-06-27", "1955-06-28", "1955-06-29", "1955-06-30", 
                                    "1955-07-01", "1955-07-02", "1955-07-03", "1955-07-04", "1955-07-05", "1955-07-06", "1955-07-07", "1955-07-08", "1955-07-09", "1955-07-10", 
                                    "1955-07-11", "1955-07-12", "1955-07-13", "1955-07-14", "1955-07-15", "1955-07-16", "1955-07-17", "1955-07-18", "1955-07-19", "1955-07-20", 
                                    "1955-07-21", "1955-07-22", "1955-07-23", "1955-07-24", "1955-07-25", "1955-07-26", "1955-07-27", "1955-07-28", "1955-07-29", "1955-07-30", 
                                    "1955-07-31", "1955-08-01", "1955-08-02", "1955-08-03", "1955-08-04", "1955-08-05", "1955-08-06", "1955-08-07", "1955-08-08", "1955-08-09", 
                                    "1955-08-10", "1955-08-11", "1955-08-12", "1955-08-13", "1955-08-14", "1955-08-15", "1955-08-16", "1955-08-17", "1955-08-18", "1955-08-19", 
                                    "1955-08-20", "1955-08-21", "1955-08-22", "1955-08-23", "1955-08-24", "1955-08-25", "1955-08-26", "1955-08-27", "1955-08-28", "1955-08-29", 
                                    "1955-08-30", "1955-08-31", "1955-09-01", "1955-09-02", "1955-09-03", "1955-09-04", "1955-09-05", "1955-09-06", "1955-09-07", "1955-09-08", 
                                    "1955-09-09", "1955-09-10", "1955-09-11", "1955-09-12", "1955-09-13", "1955-09-14", "1955-09-15", "1955-09-16", "1955-09-17", "1955-09-18", 
                                    "1955-09-19", "1955-09-20", "1955-09-21", "1955-09-22", "1955-09-23", "1955-09-24", "1955-09-25", "1955-09-26", "1955-09-27", "1955-09-28", 
                                    "1955-09-29", "1955-09-30"), 
                          TMAX = c(NA, 20, 27, 17,  26.5, 27, 17, 26.5, 20, 23, 23, 21.5, 24, 26.5, 27, 27, 26.5, 24.5, 23, 22.5, 24, 23, 21.5, 25, 26.5, 23, 
                           24, 23.5, 23, 23, 23, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
                           NA, 24, 22, 21, 17, 17, 17, 21.5, 22, 22, 22.5, 22.5, 16.5, 20.5, 17.5, 23, 17, 21, 21.5, 21, 21, 20, 22, 22, 22, 21.5, 21.5, 21.5, 22.5, 20, 
                           21, 20, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA))

For the monthly aggregation I used mutate to create a column "MONTH" and a column "YEAR"

exmpldf <- exmpldf %>%
  mutate(month(DATE), year(DATE))
names(exmpldf) <- c("DATE", "TMAX", "MONTH", "YEAR")

To create the monthly average I used summarize:

exmpldfmeanMonth <- exmpldf %>%
  group_by(MONTH, YEAR) %>%
  summarise(TMAX = mean(TMAX))

The problem is, in my time series (1955-2017) there are many months that have at least 1 daily data as NA and other months have all or almost all daily data as NA, in any case, the monthly mean is NA:

> exmpldfmeanMonth
# A tibble: 4 x 3
# Groups:   MONTH [4]
  MONTH  YEAR  TMAX
  <dbl> <dbl> <dbl>
1     6  1955  NA   (1 day is NA)
2     7  1955  NA   (all days but 1, are NA)
3     8  1955  20.7 (no NAs)
4     9  1955  NA   (all days are NA)

You could add na.rm = T but then it calculates the mean even if there is only one data per month:

exmpldfmeanMonth <- exmpldf %>%
  group_by(MONTH, YEAR) %>%
  summarise(TMAX = mean(TMAX, na.rm = T))

> exmpldfmeanMonth
# A tibble: 4 x 3
# Groups:   MONTH [4]
  MONTH  YEAR  TMAX
  <dbl> <dbl> <dbl>
1     6  1955  23.7  (1 day is NA)
2     7  1955  23    (all days but 1, are NA)
3     8  1955  20.7  (no NAs)
4     9  1955 NaN    (all days are NA)

So I would like to generate a conditional that calculates the monthly average only if there are 10 or less NAs per month, otherwise it should be considered as NA:

> exmpldfmeanMonth
# A tibble: 4 x 3
# Groups:   MONTH [4]
  MONTH  YEAR  TMAX
  <dbl> <dbl> <dbl>
1     6  1955  23.7  (1 day is NA)
2     7  1955 NAN    (all days but 1, are NA)
3     8  1955  20.7  (no NAs)
4     9  1955 NaN    (all days are NA)

Could you please guide me on how to solve this? Thank you very much in advance!

dplyr lubridate time-series dataframe r
3 Answers
library(dplyr)
library(lubridate)

df %>% 
  mutate(month = month(DATE),
         year = year(DATE)) %>% 
  group_by(month, year) %>% 
  summarize(prcp = if (sum(is.na(TMAX)) <= 10) mean(TMAX, na.rm = T) else NA,
            .groups = "drop")

Alternatively, when you summarize you could count the number of NA and then add a mutate statement to conditionally change prcp:

df %>% 
  mutate(month = month(DATE),
         year = year(DATE)) %>% 
  group_by(month, year) %>% 
  summarize(prcp = mean(TMAX, na.rm = T),
            numna = sum(is.na(TMAX)), # count number of NA
            .groups = "drop") %>% 
  mutate(prcp = ifelse(numna > 10, NA, prcp)) %>% 
  select(-numna)

Output

In the data you've shown there is only one month and year combination and that grouping has more than 10 NA:

  month  year prcp 
1     6  1955 NA

Update

Given you've updated the reprex with new data this solution still works:

str(exmpldf)
'data.frame':   122 obs. of  2 variables:
 $ DATE: chr  "1955-06-01" "1955-06-02" "1955-06-03" "1955-06-04" ...
 $ TMAX: num  NA 20 27 17 26.5 27 17 26.5 20 23 ...

exmpldf %>% 
  mutate(month = month(DATE),
         year = year(DATE)) %>% 
  group_by(month, year) %>% 
  summarize(prcp = if (sum(is.na(TMAX)) <= 10) mean(TMAX, na.rm = T) else NA,
            .groups = "drop")

  month  year  prcp
  <dbl> <dbl> <dbl>
1     6  1955  23.7
2     7  1955  NA  
3     8  1955  20.7
4     9  1955  NA
user_63824340

Please, find one alternative based on your approach using the packages data.table and lubridate:

Reprex

  • Code
library(data.table)
library(lubridate)

setDT(df1)[, DATE := ymd(DATE)
           ][, `:=` (month = month(DATE), year = year(DATE))
             ][, .(PRCP = fifelse(sum(is.na(TMAX)) <= 10, mean(TMAX, na.rm = TRUE), NA_real_)), by = .(month, year)][]
  • Case 1: NA <= 10

1.1 Your data:

df1 <- data.frame(DATE = c("1955-06-01", "1955-06-02", "1955-06-03", "1955-06-04",
                           "1955-06-05", "1955-06-06", "1955-06-07", "1955-06-08",
                           "1955-06-09", "1955-06-10", "1955-06-11", "1955-06-12",
                           "1955-06-13", "1955-06-14", "1955-06-15", "1955-06-16"),
                  TMAX = c(NA, NA, NA, NA, NA, NA, NA, NA, 20, 23, 23, 21.5, 24, 26.5,
                           27, 27))

2.2 Output:

#>    month year PRCP
#> 1:     6 1955   24
  • Case 2: NA > 10

2.1 Your data:

df1 <- data.frame(DATE = c("1955-06-01", "1955-06-02", "1955-06-03", "1955-06-04",
                           "1955-06-05", "1955-06-06", "1955-06-07", "1955-06-08",
                           "1955-06-09", "1955-06-10", "1955-06-11", "1955-06-12",
                           "1955-06-13", "1955-06-14", "1955-06-15", "1955-06-16"),
                  TMAX = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 21.5, 24, 26.5,
                           27, 27))

2.2 Output:

#>    month year PRCP
#> 1:     6 1955   NA

Created on 2021-10-28 by the reprex package (v0.3.0)

user_136610610

Consider creating a help function, which you can customise based on the need. Furthermore, you can specify whether you want to use mean, sum, or any other aggregation.

agg_data<- function(x, n=10, f = 'avg'){
#' @param x a vector of values
#' @param n a minimum number of observations
#' @param f which function to apply (e.g. `avg`, `sum`)
  
  # return NA if there are more than 10 NA
  if( sum(is.na(x)) > n ) return( NA_real_ )
  
  x <- dplyr::case_when(
    f %in% 'avg' ~ mean(x, na.rm = TRUE),
    f %in% 'sum' ~ sum(x, na.rm = TRUE),
    TRUE ~ NA_real_
  )
  
  return( x )
}

Then you can use this function in your summarise script, e.g

exmpldf %>% 
  mutate(month = month(DATE),
         year = year(DATE)) %>% 
  group_by(month, year) %>% 
  summarise(prcp = agg_data(TMAX, n = 10, f = 'avg'),
            .groups = "drop")
user_55164570
Related