1

I was wondering if anyone can help me with reshaping the following dataset. I'm wanting to reshape the dataset and also obtain new columns based on substrings of the column name. Please see my example:

Code to make the dataset:

id <- c(500,600,700)
r1_2017 <- c(1,4,5)
r1_2018 <- c(6,4,3)
r2_2017 <- c(4,3,4)
r2_2018 <- c(3,5,6)
test <- data.frame(id, r1_2017, r1_2018, r2_2017, r2_2018)

Which produces a table:

id  r1_2017 r1_2018 r2_2017 r2_2018
500 1       6       4       3
600 4       4       3       5
700 5       3       4       6

Desired outcome:

id   type  year  reading
500  r1    2017  1
500  r1    2018  6
500  r2    2017  4
500  r2    2018  3
600  r1    2017  4
600  r1    2018  4
600  r2    2017  3
600  r2    2018  5
700  r1    2017  5
700  r1    2018  3
700  r2    2017  4
700  r2    2018  6

Any help on this would be much appreciated - even if it is suggesting an approach to take or pointing to to certain libraries/functions etc.

Many thanks

MGJ-123
  • 614
  • 4
  • 19
  • In the link, please see e.g. the [`data.table` answer](https://stackoverflow.com/questions/12466493/reshaping-multiple-sets-of-measurement-columns-wide-format-into-single-columns/28786520#28786520), where you can use `measure=patterns`. Or good'ol `base::reshape` with its `sep` argument. Cheers – Henrik Jun 08 '20 at 13:28
  • 2
    with `tidyr`: `pivot_longer(test, cols = -id, names_to = c("type", "year"), values_to = "reading", names_sep = "_")` – Ben Jun 08 '20 at 13:28

0 Answers0