Search code examples
rdata-cleaning

Colapse rows wit same date and keep value

I have a Dataset with the following structure :

Date AA BB CC DD EE
1/03/2014 0.2 NA NA NA NA
1/03/2014 NA 0.3 NA NA NA
1/03/2014 NA NA 1.2 NA NA
2/03/2014 NA NA NA 3.4 NA
2/03/2014 NA NA NA NA 5.6
3/03/2014 NA 0.5 NA NA NA
3/03/2014 NA NA 1.6 NA NA

And I want to create something like this :

Date AA BB CC DD EE
1/03/2014 0.2 0.3 1.2 NA NA
2/03/2014 NA NA NA 3.4 5.6
3/03/2014 NA 0.5 1.6 NA NA

How can I do it?

Solution

  • A third option that will preserve multiple rows per Date if that might exist (though the use of sort can rearrange them ...):

    library(dplyr)
dat %>%
  group_by(Date) %>%
  mutate(across(everything(), ~ sort(., na.last = TRUE))) %>%
  filter(if_any(everything(), ~ !is.na(.))) %>%
  ungroup()
# # A tibble: 3 x 6
#   Date         AA    BB    CC    DD    EE
#   <chr>     <dbl> <dbl> <dbl> <dbl> <dbl>
# 1 1/03/2014   0.2   0.3   1.2  NA    NA  
# 2 2/03/2014  NA    NA    NA     3.4   5.6
# 3 3/03/2014  NA     0.5   1.6  NA    NA