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

Question

How do I turn this data frame into one that looks like this table. Where 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. Thank you.

Please review how to post a [reproduicble example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). You can make a copy of your data we can use with `dput`. Do not post images - we cant copy paste from them and they dont work with screen readers — Conor Neilson, Mar 26 '20 at 00:50

score 0 · Answer 1 · answered Mar 25 '20 at 23:16

Try this:

If you don't have tidyr package installed, please install it first

install.packages("tidyr")

Then following codes will do what you want

library(tidyr)

# sample data
DF <- data.frame(Price = c(42,34,42,45,54,60), 
                 Month = c(1,2,3,1,2,3), 
                 Year = c(2000,2000,2000,2001,2001,2001))

DF %>%
  mutate(Month = month.abb[Month]) %>%
  pivot_wider(names_from = Month, values_from = Price)

Result:

   Year   Jan   Feb   Mar
  <dbl> <dbl> <dbl> <dbl>
1  2000    42    34    42
2  2001    45    54    60

PS: for next time, please ask question in reproductible example. This link for inspiration

score 0 · Accepted Answer · answered Mar 25 '20 at 23:31

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))

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

2 Answers2