I have data that looks like the example below.
A given row stores two values, but only one date. It is true for the first value in the row.
df1 <- read.table(text = "ad en.1 heat.1 time.1 time.2
R6A 44680 38560 '2025-03-31 07:27' '2025-03-01 00:01'
R6A 44890 44800 '2025-04-01 11:46' '2025-04-01 00:01'
R8B 47390 40980 '2025-03-31 07:17' '2025-03-01 00:01'
R8B 47620 47520 '2025-04-01 11:46' '2025-04-01 00:01'
", header = TRUE)
I want to transform the data to the output format:
ad en.1 time
1 R6A 38560 2025-03-01 00:01
2 R6A 44680 2025-04-01 07:27
3 R6A 44890 2025-04-01 11:46
4 R6A 44800 2025-04-01 00:01
5 R8B 40980 2025-03-01 00:01
6 R8B 47390 2025-03-31 07:17
7 R8B 47620 2025-04-01 11:46
8 R8B 47520 2025-04-01 00:01
Do
rbind(subset(df1, select=-c(heat.1, time.2)) |> transform(time=time.1, time.1=NULL),
subset(df1, select=-c(en.1, time.1)) |> transform(time=time.2, time.2=NULL, en.1=heat.1, heat.1=NULL)) |>
sort_by(~ad+time)
ad en.1 time
5 R6A 38560 2025-03-01 00:01
1 R6A 44680 2025-03-31 07:27
6 R6A 44800 2025-04-01 00:01
2 R6A 44890 2025-04-01 11:46
7 R8B 40980 2025-03-01 00:01
3 R8B 47390 2025-03-31 07:17
8 R8B 47520 2025-04-01 00:01
4 R8B 47620 2025-04-01 11:46
which is lengthy but easy to read. Or reshape/merge long.