r - Combine Multiple Columns Into Tidy Data

Question

This question already has answers here:

Answer 1

Almost every data tidying problem can be solved in three steps:

1. Gather all non-variable columns
2. Separate "colname" column into multiple variables
3. Re-spread the data

(often you'll only need one or two of these, but I think they're almost always in this order).

For your data:

1. The only column that's already a variable is unique.id
2. You need to split current column names into variable and number
3. Then you need to put the "variable" variable back into columns

This looks like:

library(tidyr)
library(dplyr)

df3 %>%
  gather(col, value, -unique.id, -intervention) %>%
  separate(col, c("variable", "number")) %>%
  spread(variable, value, convert = TRUE) %>%
  mutate(start = as.Date(start, "1970-01-01"), stop = as.Date(stop, "1970-01-01"))

Your case is a bit more complicated because you have two types of variables, so you need to restore the types at the end.