-1

My question is as follows:

I want to transform my data from:

          ID1     ID1     ID2.    ID2 
Time.     Price   Vol.    Price   Vol
01/2/17.  5       12     3         9 
02/2/17.  6       8      4        10

To:

Time.   Symbol   Price   Vol.    
01/2/17.  ID1      5      12  
01/2/17.  ID2      3      9
02/2/17.  ID1      6     8  
02/2/17.  ID2      4     10

Any ideas? This would be much appreciated.

Rnovice
  • 119
  • 7
  • We could use `tidyr::pivot_longer` for that. Depending on how the *multiple headings* are implemented we have to change them a bit so we can tell `pivot_longer` how to build the new names .. could you provide a minimal reproducible example? – dario Mar 12 '20 at 13:13
  • Here is a useful link if you're not sure how to create a [minimal reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example/5963610#5963610) The MRE will make it easier for others to find and test a answer to your question. – dario Mar 12 '20 at 13:16
  • Sorry I am not quite sure what you mean. At the moment the data is just in a csv file and I am wondering what is the best way to convert it to the format I provided above. At the moment, I have not tried to code anything. – Rnovice Mar 12 '20 at 13:30
  • The idea behind a MRE is to provide a way to for others to reproduce the problem you want help with, with as **little code as possible** (therefor *minimal*): In your case this might mean provide an example of text that has the same structure as your csv (if you haven't it yet loaded into R). The link I gave you should explain what the goal of a MRE is. – dario Mar 12 '20 at 13:35

1 Answers1

2

Does this work for you? Here is a solution with the melt() function from the data.table package that kinda does what you want.

data <- data.table(time = c("01/02/17", "02/02/17"),
                   price1 = c(5, 6),
                   vol1 = c(12, 8),
                   price2 = c(3, 4),
                   vol2 = c(9, 10))

melted_data <- melt(data, id.vars = c("time"), measure.vars = patterns("^price", "^vol") , variable.name = "symbol", value.name = c("price", "vol"), variable.factor = FALSE)
melted_data$symbol[melted_data$symbol==1] <- "ID1"
melted_data$symbol[melted_data$symbol==2] <- "ID2"


       time symbol price vol
1: 01/02/17    ID1     5  12
2: 02/02/17    ID1     6   8
3: 01/02/17    ID2     3   9
4: 02/02/17    ID2     4  10
Gainz
  • 1,721
  • 9
  • 24