Take the minimum date/value of a each row after the first NA and before the last NA?

user_6357649 0
Viewed 63

I am attempting to take the minimum date in a sequence of dates that is after all NAs before that sequence, and the only thing after that sequence is either NAs, or, that date sequence is the last columns.

This is better explained through example:

sample <- data.frame(subject = c("A","B","C"),Date1 = c("1-2-19","1-2-19",NA),Date2 = c("1-3-19",NA,"1-3-19"),Date3 = c("1-4-19","1-4-19",NA)
           ,Date4 = c(NA,"1-5-19",NA),Date5 = c("1-6-19",NA,NA),Date6 = c("1-7-19",NA,"1-7-19"))

Ouput:

subject  Date1  Date2  Date3  Date4  Date5  Date6
1       A 1-2-19 1-3-19 1-4-19   <NA> 1-6-19 1-7-19
2       B 1-2-19   <NA> 1-4-19 1-5-19   <NA>   <NA>
3       C   <NA> 1-3-19   <NA>   <NA>   <NA> 1-7-19

The hoped for result is to have an additional colum called Minimum_Date, where the expected result for each row is entered.

So Subject A would return '1-6-19'

Subject B would return '1-4-19'

Subject C would return '1-7-19'

r
2 Answers

Here is an option in base R

sample$minDate <- apply(sample[-1], 1, function(x) {
       i1 <- which(!is.na(x))
       mx <- cumsum(c(TRUE, diff(i1) != 1))
       x1 <- x[i1[mx == max(mx)]]
       x1[which.min(as.Date(x1, "%m-%d-%y"))]})
sample$minDate
#[1] "1-6-19" "1-4-19" "1-7-19"
user_37322710

Here is a tidyverse approach that transforms to long format to achieve the desired output.

library(tidyverse)
sample <- data.frame(subject = c("A","B","C"),Date1 = c("1-2-19","1-2-19",NA),Date2 = c("1-3-19",NA,"1-3-19"),Date3 = c("1-4-19","1-4-19",NA)
                     ,Date4 = c(NA,"1-5-19",NA),Date5 = c("1-6-19",NA,NA),Date6 = c("1-7-19",NA,"1-7-19"))

sample %>%
  gather(date_num, date, -subject) %>% #reshape longer
  mutate(date = lubridate::mdy(date)) %>% # convert to date so min works
  arrange(subject, desc(date_num)) %>% # sort in reverse order
  group_by(subject) %>%
  mutate(after_na = cumsum(is.na(date))) %>% # create indicator for how many NAs have appeared
  filter(!is.na(date)) %>% # deal with rows that end in NA
  filter(after_na == min(after_na)) %>% # restrict to the last sequence
  summarise(Minimum_Date = min(date)) %>% # get min of those dates in last sequence
  inner_join(sample) # join onto original table
#> # A tibble: 3 x 8
#>   subject Minimum_Date Date1  Date2  Date3  Date4  Date5  Date6 
#>   <fct>   <date>       <fct>  <fct>  <fct>  <fct>  <fct>  <fct> 
#> 1 A       2019-01-06   1-2-19 1-3-19 1-4-19 <NA>   1-6-19 1-7-19
#> 2 B       2019-01-04   1-2-19 <NA>   1-4-19 1-5-19 <NA>   <NA>  
#> 3 C       2019-01-07   <NA>   1-3-19 <NA>   <NA>   <NA>   1-7-19

Created on 2019-06-18 by the reprex package (v0.3.0)

user_70284590
Related