Get rows from a column per group based on a condition

user_11397513 0
Viewed 366

I have a data.frame as shown below:

enter image description here

Basic requirement is to find average of "n" number of "value" after certain date per group.

For ex:, user provides:

Certain Date = Failure Date

n = 4

Hence, for A, the average would be (60+70+80+100)/4 ; ignoring NAs

and for B, the average would be (80+90+100)/3. Note for B, n=4 does not happen as there are only 3 values after the satisfied condition failuredate = valuedate.

Here is the dput:

structure(list(Name = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 
1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L), .Label = c("A", 
"B"), class = "factor"), FailureDate = structure(c(1L, 1L, 1L, 
1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L), .Label = c("1/5/2020", "1/7/2020"), class = "factor"), ValueDate = structure(c(1L, 
3L, 4L, 5L, 6L, 7L, 8L, 9L, 10L, 2L, 1L, 3L, 4L, 5L, 6L, 7L, 
8L, 9L, 10L, 2L), .Label = c("1/1/2020", "1/10/2020", "1/2/2020", 
"1/3/2020", "1/4/2020", "1/5/2020", "1/6/2020", "1/7/2020", "1/8/2020", 
"1/9/2020"), class = "factor"), Value = c(10L, 20L, 30L, 40L, 
NA, 60L, 70L, 80L, NA, 100L, 10L, 20L, 30L, 40L, 50L, 60L, 70L, 
80L, 90L, 100L)), class = "data.frame", row.names = c(NA, -20L
))
tidyverse dataframe r
3 Answers

We could create an index with cumsum after grouping by 'Name', extract the 'Value' elements and get the mean

library(dplyr)
n <- 4
df1 %>%
   type.convert(as.is = TRUE) %>% 
   group_by(Name) %>% 
   summarise(Ave = mean(head(na.omit(Value[lag(cumsum(FailureDate == ValueDate),
        default = 0) > 0]), n), na.rm = TRUE))
# A tibble: 2 x 2
#  Name    Ave
#  <chr> <dbl>
#1 A      77.5
#2 B      90
user_37322710

You can convert factor dates to the Date object and then compute averages of "n" numbers after FailureDate per group. Note that "n" numbers should exclude NA, so tidyr::drop_na() is used here.

library(dplyr)

df %>%
  mutate(across(contains("Date"), as.Date, "%m/%d/%Y")) %>%
  tidyr::drop_na(Value) %>% 
  group_by(Name) %>%
  summarise(mean = mean(Value[ValueDate > FailureDate][1:4], na.rm = T))

# # A tibble: 2 x 2
#   Name   mean
#   <fct> <dbl>
# 1 A      77.5
# 2 B      90
user_100689850

You can try this:

library(dplyr)

n <- 4

df %>%
  mutate(condition = as.character(FailureDate) == as.character(ValueDate))
  group_by(Name) %>%
  mutate(condition = cumsum(condition)) %>%
  filter(condition == 1) %>%
  slice(-1) %>%
  filter(!is.na(Value)) %>%
  slice(1:n) %>%
  summarise(mean_col = mean(Value))

> df

# A tibble: 2 x 2
  Name  mean_col
  <fct>    <dbl>
1 A         77.5
2 B         90
user_115874890
Related