Equivalent of max and over(partition by) in R for flattening-CodePudding

I have data as:

ID  time0       obs_num recorded_dt day0    day1    day2    day3    day4    day5    ... day31
1   2009-01-01  A       2009-01-01  A       NULL    NULL    NULL    NULL    NULL    ... NULL
1   2009-01-01  D       2009-01-31  NULL    NULL    NULL    NULL    NULL    NULL    ... D
1   2009-01-01  B       2009-01-05  NULL    NULL    NULL    NULL    NULL    B       ... NULL
2   2005-02-02  B       2005-02-03  NULL    B       NULL    NULL    NULL    NULL    ... NULL

Data can be reproduced as:

example = data.frame(
  ID = c(1,1,1,2),
  time0 = c('2009-01-01','2009-01-01','2009-01-01','2005-02-02'),
  obs_num = c('A','D','B','B'),
  recorded_dt = c('2009-01-01','2009-01-31','2009-01-05','2005-02-03')
)

library(tidyverse)
df <- example %>% 
  mutate(difs_days = floor(difftime(recorded_dt, time0, units="days"))) %>% 
  arrange(difs_days) %>% 
  pivot_wider(names_from = difs_days, values_from = obs_num, names_prefix = 'day') %>% 
  arrange(ID, recorded_dt)

df
# # A tibble: 4 × 7
#      ID time0      recorded_dt day0  day1  day4  day30
#   <dbl> <chr>      <chr>       <chr> <chr> <chr> <chr>
# 1     1 2009-01-01 2009-01-01  A     NA    NA    NA   
# 2     1 2009-01-01 2009-01-05  NA    NA    B     NA   
# 3     1 2009-01-01 2009-01-31  NA    NA    NA    D    
# 4     2 2005-02-02 2005-02-03  NA    B     NA    NA

I want to flatten the data into:

ID  time0       day0    day1    day2    day3    day4    day5    ... day31
1   2009-01-01  A       NULL    NULL    NULL    NULL    B       ... D
2   2005-02-02  NULL    B       NULL    NULL    NULL    NULL    ... NULL

In SQL, I would use max(dayX) over(partition by ID) as XYZ and then keep distinct values. I think there must be an efficient way in R. Can you please help.

CodePudding user response：

You can summarise multiple columns with across():

df %>%
  group_by(ID, time0) %>%
  summarise(across(day0:day30, ~ if(all(is.na(.x))) NA else max(.x, na.rm = TRUE))) %>%
  ungroup()

# # A tibble: 2 × 6
#      ID time0      day0  day1  day4  day30
#   <dbl> <chr>      <chr> <chr> <chr> <chr>
# 1     1 2009-01-01 A     NA    B     D    
# 2     2 2005-02-02 NA    B     NA    NA

CodePudding user response：

Update:

For the given example we could use: summarise(across(everything(), ~trimws(paste(., collapse = ''))))

To replace "" by NA just add na_if("")at the end of the code:

library(dplyr)

example %>%
  select(-recorded_dt) %>% 
  mutate(across(everything(), ~ifelse(is.na(.), "", .))) %>% 
  group_by(ID, time0) %>% 
  summarise(across(everything(), ~trimws(paste(., collapse = '')))) %>% 
  na_if("")

     ID time0      day0  day1  day4  day30
  <dbl> <chr>      <chr> <chr> <chr> <chr>
1     1 2009-01-01 "A"   ""    "B"   "D"  
2     2 2005-02-02 ""    "B"   ""    ""