Group By Ignoring NA

user_7577404 0
Viewed 36

I am trying to get the following data to get the data in the following format into grouped by day and aggregating the shared variable with the mean function. Any assistance would be great!

dateA = c("5/8/2020 01:00:00","5/8/2020 02:00:00", "5/8/2020 03:00:00", "5/8/2020 04:00:00", NA, NA, NA, "5/12/2020 01:00:00", "5/12/2020 05:00:00", "5/12/2020 09:00:00", NA, NA, NA, NA, NA)
dateB = c(NA, NA, NA, NA, "5/10/2020 01:00:00", "5/10/2020 05:00:00", "5/10/2020 09:00:00", NA, NA, NA, "5/15/2020 02:00:00", "5/15/2020 06:00:00", "5/15/2020 12:00:00", "5/15/2020 14:00:00", "5/15/2020 19:00:00")
varA = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15)
data = data.frame(dateA,dateB,varA)

Original Data format:

dateA               dateB               varA
5/8/2020 01:00:00   NA                    1  
5/8/2020 02:00:00   NA                    2 
5/8/2020 03:00:00   NA                    3
5/8/2020 04:00:00   NA                    4
NA                  5/10/2020 01:00:00    5
NA                  5/10/2020 05:00:00    6
NA                  5/10/2020 06:00:00    7
5/12/2020 01:00:00  NA                    8
5/12/2020 05:00:00  NA                    9 
5/12/2020 09:00:00  NA                    10
NA                  5/15/2020 02:00:00    11
NA                  5/15/2020 06:00:00    12
NA                  5/15/2020 12:00:00    13
NA                  5/15/2020 14:00:00    14
NA                  5/15/2020 19:00:00    15

Wanted Data Format:

dateA       dateB       meanA 
5/8/2020    NA          2.5
NA          5/10/2020   6
5/12/2020   NA          9
NA          5/15/2020   13
dplyr dataframe r
1 Answers

We can do a grouping based on the rleid of non-NA elements in 'dateA', 'dateB', then convert the 'date' columns to Datetime class and change it to Date class (with as.Date), while getting the mean of 'varA')

library(dplyr)
library(lubridate)
library(data.table)
data %>%
    group_by(grp = rleid(!is.na(dateA), !is.na(dateB))) %>% 
    summarise(across(starts_with('date'), ~as.Date(first(mdy_hms(.)))),
                meanA = mean(varA)) %>%
    select(-grp)
# A tibble: 4 x 3
#  dateA      dateB      meanA
#  <date>     <date>     <dbl>
#1 2020-05-08 NA           2.5
#2 NA         2020-05-10   6  
#3 2020-05-12 NA           9  
#4 NA         2020-05-15  13

Or using base R with aggregate

aggregate(varA ~ date, transform(data,
  date = sub(' .*', '', pmax(dateA, dateB, na.rm = TRUE))), FUN = mean)
user_37322710
Related