0

Below, a sample of my data, I have more Rs and Os.

A   R1  O1  R2  O2  R3  O3
1   3   3   5   3   6   4
2   3   3   5   4   7   4
3   4   4   5   5   6   5

I want to get the following data

A   R   O   Value
1   3   1   3
1   5   2   3
1   6   3   4
2   3   1   3
2   5   2   4
2   7   3   4
3   4   1   4
3   5   2   5
3   6   3   5

I try the melt function, but I was unsuccessful. Any help would be very much appreciated.

www
  • 38,575
  • 12
  • 48
  • 84
  • Possible duplicate of [Reshaping multiple sets of measurement columns (wide format) into single columns (long format)](https://stackoverflow.com/questions/12466493/reshaping-multiple-sets-of-measurement-columns-wide-format-into-single-columns) – kath Dec 09 '17 at 20:10
  • Using the first answer of the linked post: `library(reshape2)` `reshape(df, idvar = "A", direction = "long", varying = list(R = c(2, 4, 6), O = c(3, 5, 7)), v.names = c("R", "O"))` You get a dataframe with the colnames "A", "time", "R", "O". To match it exactly with your desired outcome rename "O" to "Value", and "time" to "O" and sort the data by column "A". – kath Dec 09 '17 at 20:36

1 Answers1

1

A solution using dplyr and tidyr. The key is to use gather to collect all the columns other than A, and the use extract to split the column, and then use spread to convert the data frame back to wide format.

library(dplyr)
library(tidyr)

dt2 <- dt %>%
  gather(Column, Number, -A) %>%
  extract(Column, into = c("Column", "ID"), regex = "([A-Z]+)([0-9]+)") %>%
  spread(Column, Number) %>%
  select(A, R, O = ID, Value = O)
dt2
#   A R O Value
# 1 1 3 1     3
# 2 1 5 2     3
# 3 1 6 3     4
# 4 2 3 1     3
# 5 2 5 2     4
# 6 2 7 3     4
# 7 3 4 1     4
# 8 3 5 2     5
# 9 3 6 3     5

DATA

dt <- read.table(text = "A   R1  O1  R2  O2  R3  O3
1   3   3   5   3   6   4
                 2   3   3   5   4   7   4
                 3   4   4   5   5   6   5",
                 header = TRUE)
www
  • 38,575
  • 12
  • 48
  • 84
  • Sure. After the `gather` function, the elements in `Column` have two parts. The first part is an uppercase letter (`R`, `O`, ...). while the second part is a number (`1`, `2`, `3`). We can use the `extract` function to specify the two new columns (`Column`, `ID`) we are going to create. The regex also has two parts. `([A-Z]+)` captures the uppercase letter, while `(0-9)+` captures the numbers. – www Dec 09 '17 at 21:10