Converting rows to columns

Question

I have a data frame which looks like this

 a    b    c   d
 ------------------
 1    1    1   0
 1    1    1   200
 1    1    1   300
 1    1    2   0
 1    1    2   600
 1    2    3   0
 1    2    3   100
 1    2    3   200
 1    3    1   0

I want it to look like this.

 a    b    c   d1    d2    d3
 ------------------------------
 1    1    1   0   200   300
 1    1    2   0     600   (NA)
 1    2    3   0     100   200
 1    3    1   0

If the value is missing then NA filled in that field is fine, but I should be able to say that the value is missing. For example line 2 & 4.

I am new to R and I have no idea how to proceed.

You want to look for reshaping data from long to wide. There are lots of questions like this on SO, e.g.: http://stackoverflow.com/questions/5890584/reshape-data-from-long-to-wide-format-r — Thomas, Apr 28 '14 at 07:18
Can you tell me what exactly are the values that you need in the columns d1, d2, d3? — RHelp, Apr 28 '14 at 07:23
@RHelp, the value that are in the column 'd' I want them converted into rows. The second table in my question. — user2575429, Apr 28 '14 at 11:56

shadow · Accepted Answer · 2014-04-28T07:58:29.293

You should first calculate the numbers of times each (a,b,c)-combination exists. I would do this with data.table:

require(data.table)
dt <- data.table(df)
dt[, time:=paste("d", 1:.N, sep="."), by=list(a, b, c)]

Then you can use dcast to change to wide format.

dcast.data.table(dt, a+b+c~time, value.var="d", fun=sum, fill=NA_integer_)
##    a b c d.1 d.2 d.3
## 1: 1 1 1   0 200 300
## 2: 1 1 2   0 600  NA
## 3: 1 2 3   0 100 200
## 4: 1 3 1   0  NA  NA

Converting rows to columns

1 Answers1