Search code examples
rdataframecsvtranspose

How to turn the values in a column into the number of rows?

How do I turn this data frame:

enter image description here

into one that looks like this table:

enter image description here

The goal is that the number of columns is equal to the available values in the month column, and the number of rows is equal to the years available. I do not have a reproducible example.

Solution

  • The base R way is less elegant, but here is an approach. 

    Result <- reshape(NYHSpot,direction = "wide",idvar="Year", timevar = "Month")
colnames(Result)[2:13] <- month.abb
Result
   Year   Jan   Feb   Mar   Apr   May   Jun   Jul   Aug   Sep   Oct   Nov   Dec
1  1986 0.420 0.340 0.460 0.420 0.410 0.411 0.434    NA    NA    NA    NA    NA
8  1987 0.557 0.556 0.523 0.518 0.541 0.516 0.454 0.489 0.474 0.509 0.504 0.542
20 1988    NA    NA    NA    NA    NA    NA    NA 0.449 0.461 0.452    NA    NA

    Data

    NYHSpot <- structure(list(i..Spot.Price = c(0.42, 0.34, 0.46, 0.42, 0.41, 
0.411, 0.434, 0.489, 0.474, 0.509, 0.504, 0.542, 0.557, 0.556, 
0.523, 0.518, 0.541, 0.516, 0.454, 0.449, 0.461, 0.452), Month = c(6L, 
7L, 8L, 9L, 10L, 11L, 12L, 1L, 2L, 3L, 4L, 5L, 6L, 7L, 8L, 9L, 
10L, 11L, 12L, 1L, 2L, 3L), Year = c(1986, 1986, 1986, 1986, 
1986, 1986, 1986, 1987, 1987, 1987, 1987, 1987, 1987, 1987, 1987, 
1987, 1987, 1987, 1987, 1988, 1988, 1988)), class = "data.frame", row.names = c(NA, 
-22L))