You can do something along the lines of this:
library("tidyverse")
d <- tribble(
~Date, ~A, ~B, ~C, ~D, ~E,
"210915", 1, 5, "", "", "",
"210915", "", "", 2, 4, "",
"210915", "", "", "", "", 7,
"220915", 7, 6, 2, "", "",
"220915", "", "", "", 9, 1
)
d_clean <- d %>%
pivot_longer(cols = -Date,
names_to = "cat",
values_to = "value") %>%
filter(value != "") %>%
pivot_wider(id_cols = Date,
names_from = cat,
values_from = value)
Yielding:
> d
# A tibble: 5 x 6
Date A B C D E
<chr> <chr> <chr> <chr> <chr> <chr>
1 210915 1 5 "" "" ""
2 210915 "" "" 2 4 ""
3 210915 "" "" "" "" 7
4 220915 7 6 2 "" ""
5 220915 "" "" "" 9 1
> d_clean
# A tibble: 2 x 6
Date A B C D E
<chr> <chr> <chr> <chr> <chr> <chr>
1 210915 1 5 2 4 7
2 220915 7 6 2 9 1
Hope it helps 