transform data from rows to column in R

Question

I am facing problem in transforming data from columns to row or wide format. My dataframe look like as below

   V1   V2  V3    V4
   1    1  2.041  NA
   1  123  5.190  NA
   1  366  9.500  NA
   1 1462 16.800  NA
   1 2558 23.500  88

   2    1  2.466  NA
   2  123  5.440  NA
   2  366  9.000  NA
   2 2558 18.600  91
   ....

I would like to transform it to as below

1 1 2.041 123 5.190 366 9.500 1462 16.800 2558 23.500 88
2 1 2.466 123 5.440 366 9.000 NA   NA     2558 18.600 91

Or

1 1 2.041 NA 123 5.190 NA 366 9.500 NA 1462 16.800 NA 2558 23.500 88
2 1 2.466 NA 123 5.440 NA 366 9.000 NA NA   NA     NA 2558 18.600 91

I have tried couple of option with dcast like below but its not working out well.

dcast(data,V1~.) 
dcast(data,V1~V2+V3+V4)

I don't think your desired output is correct for the second line. — David Arenburg, Feb 25 '15 at 15:26
It's still wrong, it should be `2 1 2.466 NA 123 5.44 NA 366 9.0 NA 2558 18.6 91 NA NA NA` — David Arenburg, Feb 25 '15 at 15:47

David Arenburg · Accepted Answer · 2015-02-25T15:26:45.093

3

With base R

t(matrix(c(t(df[-1])), ncol = length(unique(df$V1))))
#      [,1]  [,2] [,3] [,4] [,5] [,6] [,7] [,8] [,9] [,10] [,11] [,12] [,13] [,14] [,15]
# [1,]    1 2.041   NA  123 5.19   NA  366  9.5   NA  1462  16.8    NA  2558  23.5    88
# [2,]    1 2.466   NA  123 5.44   NA  366  9.0   NA  1462  13.7    NA  2558  18.6    91

Or try this

library(data.table)
setDT(df)[, as.list(c(t(.SD))), by = V1]
#    V1 V1    V2 V3  V4   V5 V6  V7  V8 V9  V10  V11 V12  V13  V14 V15
# 1:  1  1 2.041 NA 123 5.19 NA 366 9.5 NA 1462 16.8  NA 2558 23.5  88
# 2:  2  1 2.466 NA 123 5.44 NA 366 9.0 NA 1462 13.7  NA 2558 18.6  91

Or without NAs

setDT(df)[, as.list(na.omit(c(t(.SD)))), by = V1]
#    V1 V1    V2  V3   V4  V5  V6   V7   V8   V9  V10 V11
# 1:  1  1 2.041 123 5.19 366 9.5 1462 16.8 2558 23.5  88
# 2:  2  1 2.466 123 5.44 366 9.0 1462 13.7 2558 18.6  91

Per your edit, try this:

Res <- setDT(df)[, .(Value = c(t(.SD))), by = V1][, indx := seq_len(.N), by = V1]
dcast(Res, V1 ~ indx, value.var = "Value")
#    V1 1     2  3   4    5  6   7   8  9   10   11 12   13   14 15
# 1:  1 1 2.041 NA 123 5.19 NA 366 9.5 NA 1462 16.8 NA 2558 23.5 88
# 2:  2 1 2.466 NA 123 5.44 NA 366 9.0 NA 2558 18.6 91   NA   NA NA

edited Feb 25 '15 at 15:26

answered Feb 25 '15 at 14:48

David Arenburg

91,361
17
137
196

Thx, What is .SD when you matrix transpose with `t()`? – Jigar Parekh Feb 25 '15 at 15:05
`.SD` stands for **S**ub **D**ata, it is part of `data.table` syntax. Take a look at `?data.table` – David Arenburg Feb 25 '15 at 15:06
getting error like `j doesn't evaluate to the same number of columns for each group`, I think due to data where few V1 might have 4 rows instead of 5. – Jigar Parekh Feb 25 '15 at 15:10
yes both solution worked if no. of rows per V1 is same. – Jigar Parekh Feb 25 '15 at 15:19
Ok try this, for not the same size data set `Res <- setDT(df)[, list(Value = c(t(.SD))), by = V1] ; dcast(Res[, indx := seq_len(.N), by = V1], V1 ~ indx, value.var = "Value")`. See my edit. – David Arenburg Feb 25 '15 at 15:24
problem i see with last solution is if there data after missing row then it takes over from next row and instead of having NA – Jigar Parekh Feb 25 '15 at 15:47

transform data from rows to column in R

1 Answers1