I have a Dataset with the following structure :

And I want to create something like this :

How can I do it?

CodePudding user response：

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  

CodePudding user response：

With tidyverse you can try this:

Data

df <- read.table( text= "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", header = T)

Code

df %>% 
  group_by(Date) %>% 
  fill(AA:EE) %>% 
  slice_tail()

Output

# A tibble: 3 x 6
# Groups:   Date [3]
  Date         AA    BB    CC    DD    EE
  <fct>     <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  

CodePudding user response：

df %>%
  pivot_longer(-Date)%>%
  na.omit()%>%
  pivot_wider(Date)

# 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