CODEWITHSUNDEEP
rtidyrfill
Slash
Slash

Reputation: 509

How do I fill data until last non-missing value?

I have some data grouped by let like so:

events <- structure(list(let = c("A", "A", "A", "B", "B", "B"), age = c(0L, 
4L, 16L, 0L, 8L, 7L), value = c(61L, 60L, 13L, 29L, 56L, 99L)),
class = "data.frame", row.names = c("1", 
"2", "3", "4", "5", "6"))

  let age value
1   A   0    61
2   A   4    60
3   A  16    13
4   B   0    29
5   B   8    56
6   B   7    99

How can I cast the data frame so that:

  1. Age is multiple columns grouped into weeks. So for each column, take the value of the largest age that is less than or equal to 0, 7, 14, etc. days
  2. Fill in age UNTIL the last non-missing value by let.

End result would look like this:

    events.cast <- data.frame(
 let = LETTERS[1:2],
  T0_value = c(61,29),
  T1_value = c(60,99),
  T2_value = c(60,56),
  T3_value = c(13,56))

 let T0_value T1_value T2_value T3_value
1   A       61       60       60       13
2   B       29       99       56       NA

Note that this is coming from a previous question I asked.

Upvotes: 1

Views: 209

Answers (1)

akrun
akrun

Reputation: 887851

We could create a column of 'actuals' before the complete and use that to create the NA in 'value' column based on the occurrence of NA in 'actuals'

library(dplyr)
library(tidyr)
library(stringr)
events %>% 
    group_by(grp = cut(age, breaks = c(-Inf,0, 7, 14, 21),
        labels = str_c("T", 0:3, "_value")), let) %>% 
    slice(which.max(value)) %>%
    ungroup %>%
    select(-age) %>% 
    mutate(actuals = TRUE) %>%  
    group_by(let) %>% 
    complete(grp = unique(.$grp)) %>% 
    fill(value) %>%
    ungroup %>%
    mutate(i1 = cumsum(is.na(actuals)), 
           value = replace(value, i1 == max(i1), NA)) %>%
    select(-i1, -actuals) %>%
    pivot_wider(names_from = grp, values_from = value)
# A tibble: 2 x 5
#  let   T0_value T1_value T2_value T3_value
#  <chr>    <int>    <int>    <int>    <int>
#1 A           61       60       60       13
#2 B           29       99       56       NA

Upvotes: 1

Related Questions