CODEWITHSUNDEEP
rdplyr
A1976
A1976

Reputation: 39

Find the name of the column with the minimum date using dplyr

I have the following data frame, I want to find the name of the column with the minimum date for each Id.

id   a_date       b_date         c_date        d_date     
1    2014-01-01   2014-01-05     2014-01-15    NA                   
2    2017-02-01   NA             NA            2015-03-01

Which is:

df <- tibble(id = c(1, 2),
             a_date = c("01/01/14", "01/02/17"),
             b_date = c("05/01/14", NA),
             c_date = c("15/01/14", NA),
             d_date = c(NA, "01/03/15"))

I tried this, but did not work

df  %>% group_by(id) %>% 
  mutate( min = pmin(a_date, b_date, c_date, d_date ,na.rm=TRUE) %>% 
  mutate(col_name = which(colnames(df)== min)

Expected output will be as below

id   col_name    
1    a_date                 
2    d_date

Upvotes: 1

Views: 71

Answers (1)

william3031
william3031

Reputation: 1708

How about this?

df %>% 
  gather(key, date, -id) %>% 
  filter(!is.na(date)) %>% 
  mutate(date = dmy(date)) %>% 
  group_by(id) %>% 
  arrange(date) %>% 
  slice(1)

It produces this output:

# A tibble: 2 x 3
# Groups:   id [2]
     id key    date      
  <dbl> <chr>  <date>    
1     1 a_date 2014-01-01
2     2 d_date 2015-03-01

Upvotes: 1

Related Questions