is there a way in R to return ordered values in a mutate statement?-CodePudding

say I have something like:

Date        ID  
2000-01-01  1    
2000-01-02  1    
2000-01-03  1   
2000-01-01  2    
2000-01-02  2    
2000-01-01  3    
2000-01-04  3

is there a way to code for indefinite columns for ordered dates by id? I could do something like:

data %>% group_by(id) %>% mutate(first=min(Date), last=max(Date))

but of course this would only capture the first and last dates as new columns. I would need three new vars for id = 1, and 2 new columns for id =2,3 etc.

Expected output:

Date        ID  need1       need2       need3
2000-01-01  1   2000-01-01  2000-01-02  2000-01-03
2000-01-02  1   2000-01-01  2000-01-02  2000-01-03
2000-01-03  1   2000-01-01  2000-01-02  2000-01-03
2000-01-01  2   2000-01-01  2000-01-02  -
2000-01-02  2   2000-01-01  2000-01-02  -
2000-01-01  3   2000-01-01  2000-01-04  -
2000-01-04  3   2000-01-01  2000-01-04  -

Thanks!

CodePudding user response：

Here is one way -

library(dplyr)
library(tidyr)

data %>% 
  group_by(ID) %>% 
  mutate(need = list(setNames(Date, paste0('need', seq_along(Date))))) %>%
  ungroup %>%
  unnest_wider(need)

#  Date          ID need1      need2      need3     
#  <chr>      <int> <chr>      <chr>      <chr>     
#1 2000-01-01     1 2000-01-01 2000-01-02 2000-01-03
#2 2000-01-02     1 2000-01-01 2000-01-02 2000-01-03
#3 2000-01-03     1 2000-01-01 2000-01-02 2000-01-03
#4 2000-01-01     2 2000-01-01 2000-01-02 NA        
#5 2000-01-02     2 2000-01-01 2000-01-02 NA        
#6 2000-01-01     3 2000-01-01 2000-01-04 NA        
#7 2000-01-04     3 2000-01-01 2000-01-04 NA

For each ID, we save the Date in a list, assign them names using setNames (which is useful later to get column names) and use unnest_wider to separate each date in different column.