1

I want to reshape my dataset that has missing data from long to wide using two columns' values as the new dataset's columns. Here is an example:

ID  Survey  Question  Response
1        1         1         1
1        1         2         2
1        2         1         3
1        2         2         
1        2         3         4
1        3         1         3
1        3         2         4
2        1         1         2
2        1         2         
2        2         1         6
2        2         2         2
2        2         3         3
2        3         1         
2        3         2         5

I want it to look like this

ID Survey1.Q1 Survey1.Q2 Survey2.Q1 Survey2.Q2 Survey2.Q3 Survey3.Q1 Survey3.Q1
1           1          2          3                     4          3          4
2           2                     6          2          3                     5
  • 1
    Can you add a dput() of your example data? Anything you've already tried? Why did it not work? And is there a specific reason you need this format? It's not very 'tidy data'. – Heroka Nov 04 '15 at 13:09
  • 1
    Try `library(dcast); dcast(df1, ID~Survey+Question, value.var='Response')` I guess for this example, I am getting the expected as you showed. – akrun Nov 04 '15 at 13:12
  • 1
    @akrun Thanks for your help from before! I am still stuck on this problem in that instead of all the columns are of numeric type, Survey and Question are factors and Response is numeric. –  Nov 04 '15 at 13:22
  • Then add a dput of your data... – Heroka Nov 04 '15 at 13:23
  • @heroka the dataset's dim is 31k * 4 –  Nov 04 '15 at 13:27
  • 2
    Already been solved, but for the future: Here's how to create a [reproducible example in R](http://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example). You could do dput(head(data)) for example. – Heroka Nov 04 '15 at 13:29
  • 3
    @akrun [this is of course unprovable] I had already written the syntax when you commented. dcast-syntax can be very uniform. Got distracted with something, added formatting and posted. – Heroka Nov 04 '15 at 14:00

2 Answers2

5

Or you may choose to format your data while casting (thanks @Pierre)

res <- dcast(ID~paste0("Survey",Survey)+paste0("Q",Question),
    value.var="Response",data=dat)
Heroka
  • 12,889
  • 1
  • 28
  • 38
2

with reshape() from {stats}:

d <- read.table(header=TRUE, text=
'ID  Survey  Question  Response
1        1         1         1
1        1         2         2
1        2         1         3
1        2         2         NA
1        2         3         4
1        3         1         3
1        3         2         4
2        1         1         2
2        1         2         NA
2        2         1         6
2        2         2         2
2        2         3         3
2        3         1         NA
2        3         2         5')
d$t <- 10*d$Survey+d$Question
reshape(d[,-(2:3)], dir="wide", timevar="t", idvar="ID")
jogo
  • 12,469
  • 11
  • 37
  • 42