Transpose data with reshape2 with data airquality

Question

I want to reshape the data airquality. I want "Month" on the row and all other as columns. I made the columns unique by adding the month and day number. It works to transform but nearly all the values get NA. Does anyone know why this is and how to fix it?

airquality
library(reshape2)
aql <- melt(airquality, id.vars=c("Month", "Day"))
aql$variable <- paste0(airquality$Month, airquality$Day, aql$variable)
aql <- aql[,-2]
aqw <- dcast(aql, Month~variable

Answer 1

The solution by @Chris is neat, but as pivot_wider is a new function from the development version, it might be subject to breaking changes in the future and is not well suited to produce code, that is supposed to stay replicable. Using the CRAN version you can do the same with just a little more code:

library(tidyr)

airquality %>% 
  gather("var", "value", Ozone:Temp) %>% 
  unite("name", var, Day) %>% 
  spread(name, value)

Answer 2

I would recommend using the new pivot_wider function from the development version of tidyr which is quite intuitive:

#devtools::install_github("tidyverse/tidyr")
library(tidyr)


airquality %>% 
  pivot_wider(id_cols = Month,
              names_from = Day,
              values_from = c(Ozone, Solar.R, Wind, Temp)) 


## A tibble: 5 x 125
#  Month Ozone_1 Ozone_2 Ozone_3 Ozone_4 Ozone_5 Ozone_6 Ozone_7 Ozone_8 Ozone_9 Ozone_10
#  <int>   <int>   <int>   <int>   <int>   <int>   <int>   <int>   <int>   <int>    <int>
#1     5      41      36      12      18      NA      28      23      19       8       NA
#2     6      NA      NA      NA      NA      NA      NA      29      NA      71       39
#3     7     135      49      32      NA      64      40      77      97      97       85
#4     8      39       9      16      78      35      66     122      89     110       NA
#5     9      96      78      73      91      47      32      20      23      21       24
## … with 114 more variables

This creates a column for each air quality variable and day, and a row for each month.

Note that reshape2 (2010-2014) is retired and replaced by tidyr so it's worth looking into.