How to reshape a data frame with two columns to wide format?

Question

I want to reshape the following data frame

x <- structure(
    list(name = c("HT", "AT", "HG", "AG"),
         conv = c(2L, + 2L, 3L, 4L)),
    .Names = c("name", "conv"), row.names = 1:4, class = "data.frame")

> x
  name conv
1   HT    2
2   AT    2
3   HG    3
4   AG    4

into

  conv  x.1  x.2
1    2   HT   AT
2    3   HG   NA
3    4   AG   NA

In the final data frame there should be a row for every distinct value of conv, and as many x.? columns as there are rows in the original data frame for that particular value of conv, filling with NAs when necessary. I don't care about the column names.

I tried reshape but I can't get it to work, because it seems that it needs a third column that I don't have:

> reshape(x, idvar='conv', direction='wide')
Error in `[.data.frame`(data, , timevar) : undefined columns selected

Answer 1

Something you can try:

xmax <- max(table(x$conv))
xsplit <- split(x, x$conv)
xsplit <- sapply(xsplit, function(tab){c(tab$name, rep(NA, xmax-length(tab$name)))})
x2 <- data.frame(conv=x$conv[!duplicated(x$conv)], t(xsplit), stringsAsFactors=F)
colnames(x2)[-1]<-paste("x",1:xmax,sep=".")
x2
#  conv x.1  x.2 
#2    2  HT   AT
#3    3  HG <NA>
#4    4  AG <NA>

NB: with reshape, you can do what's below but I don't think that's what you want. There may be some parameters to set so you get what you want but I'm really not a reshape expert :-(

reshape(data=x, v.names="name", timevar="name", idvar="conv", direction="wide")
#  conv name.HT name.AT name.HG name.AG
#1    2      HT      AT    <NA>    <NA>
#3    3    <NA>    <NA>      HG    <NA>
#4    4    <NA>    <NA>    <NA>      AG

Answer 2

using data.table v1.9.5:

require(data.table)
dcast(setDT(x), conv ~ paste0("x.", x[, seq_len(.N), by=conv]$V1), value.var="name")
#    conv x.1 x.2
# 1:    2  HT  AT
# 2:    3  HG  NA
# 3:    4  AG  NA

You can install it by following the instructions here.