if anyone has a more elegant way to do the descending sort at the end, please share !
library(tidyverse)
library(lubridate)
(df<- tribble(~Date, ~A.B, ~A.C, ~A.D, ~B.A, ~B.C, ~B.D,
"1/1/11" ,NA , NA , 0.01 , NA ,0.05, NA,
"1/2/11" ,0.25, NA , NA , 0.45 , NA , 0.12) %>% mutate(Date=
dmy(Date)))
(oldnames<-names(df %>% select(-Date)))
(newnames <- stringr::str_sub(oldnames,1,1))
(dflong <- pivot_longer(df,
cols = -Date,
names_to="name",
values_to="val") %>% mutate(short_name=stringr::str_sub(name,1,1),
populated=!is.na(val)))
(df2 <- group_by(dflong,
Date,short_name) %>% summarise(count=sum(populated)) %>% ungroup %>% pivot_wider(
id_cols=Date,
names_from=short_name,
values_from=count
))
(df_res1 <- left_join(df,
df2))
(max_df<- select(df2,-Date) %>% summarise_all(max) %>% pivot_longer(
cols=everything(),
names_to="name",
values_to="maxval"
) %>% arrange(desc(maxval)))
sort_order = pull(max_df,name) %>% paste0("desc(",.,")",collapse=',')
constructed_arr <- paste0("arrange(df_res1,",sort_order,")")
(sorted_result <- eval(parse_expr(constructed_arr)))
# A tibble: 2 x 9
# Date A.B A.C A.D B.A B.C B.D A B
# <date> <dbl> <lgl> <dbl> <dbl> <dbl> <dbl> <int> <int>
#1 2011-02-01 0.25 NA NA 0.45 NA 0.12 1 2
#2 2011-01-01 NA NA 0.01 NA 0.05 NA 1 1