Substitution of the date in an additional column-CodePudding

Sample dataset

df <- data.frame(
  x1 = c("17.10.2022", "Godzina", "00:00 - 01:00", "01:00 - 02:00", "02:00 - 03:00", "03:00 - 04:00", "04:00 - 05:00", "05:00 - 06:00", "06:00 - 07:00"),
  x2 = c(NA, NA, 17.09497, 17.29869, 17.34341, 17.10647, 17.10647, 17.01680, 17.18461)
  )

I would like the end result to be as follows

             x1       x2         x3
1 00:00 - 01:00 17.09497 17.10.2022
2 01:00 - 02:00 17.29869 17.10.2022
3 02:00 - 03:00 17.34341 17.10.2022
4 03:00 - 04:00 17.10647 17.10.2022
5 04:00 - 05:00 17.10647 17.10.2022
6 05:00 - 06:00 17.01680 17.10.2022
7 06:00 - 07:00 17.18461 17.10.2022

There are about a thousand such tables, and I would like to do it elegantly and efficiently.

CodePudding user response：

If all tables are shaped like the example this might help

library(dplyr)

df %>% 
  mutate(x3 = x1[1]) %>% 
  rowwise() %>% 
  summarize(x1, x2 = x2[!is.na(x2)], x3)
# A tibble: 7 × 3
  x1               x2 x3
  <chr>         <dbl> <chr>
1 00:00 - 01:00  17.1 17.10.2022
2 01:00 - 02:00  17.3 17.10.2022
3 02:00 - 03:00  17.3 17.10.2022
4 03:00 - 04:00  17.1 17.10.2022
5 04:00 - 05:00  17.1 17.10.2022
6 05:00 - 06:00  17.0 17.10.2022
7 06:00 - 07:00  17.2 17.10.2022