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