Convert R dataframe from long to wide format

Question

I have the following data:

 df <- data.frame(left=letters[1:4], right=replicate(1,sample(0:100,12,rep=TRUE)))

I looks like this (numbers might be different because on the random function)

   left  right
1     a     6
2     b    97
3     c    78
4     d    28
5     a    61
6     b    58
7     c    56
8     d    28
9     a    90
10    b    83
11    c    72
12    d    17

The first column is headers for a table, repeating 3 times. The new dataframe should look like this:

a  b  c  d
6  97 78 28
61 58 56 28
90 83 72 17

When I use

spread(df,left, right)

it looks like this:

    a  b  c  d
1   6 NA NA NA
2  NA 97 NA NA
3  NA NA 78 NA
4  NA NA NA 28
5  61 NA NA NA
6  NA 58 NA NA
7  NA NA 56 NA
8  NA NA NA 28
9  90 NA NA NA
10 NA 83 NA NA
11 NA NA 72 NA
12 NA NA NA 17

Any idea?

Perhaps this would work: `data.frame(a = df[which(df$left == 'a'),2], b = df[which(df$left == 'b'),2], c = df[which(df$left == 'c'),2], d = df[which(df$left == 'd'), 2])` — bouncyball, Jan 25 '16 at 20:33
@Alex couldn't find a good one soon enough; if you have a good one: vote to close as duplicate & I will marked it immediately as a duplicate — Jaap, Jan 25 '16 at 20:51
`df$group <- rep(1:3, each=4); xtabs(right~group+left, data=df)` — jogo, Jan 25 '16 at 20:53
@Jaap Does this do the trick? http://stackoverflow.com/questions/8192136/long-to-wide-w-two-repeated-measures — Richard Erickson, Jan 25 '16 at 20:59
`unstack(df, form = right ~ left)` is in the classic long to wide dupe. It is the last answer — Pierre L, Jan 25 '16 at 21:10

Jaap · Answer 1 · 2018-06-02T08:58:35.207

2

The shortest solution is probably using the rowid function from the data.table package:

library(data.table)
dt <- dcast(setDT(df), rowid(left) ~ left, value.var = "right")
dt[, names(dt)[1] := NULL]

which gives:

> dt
    a  b  c  d
1: 18 52 67 12
2: 36 74 61 86
3: 81 41 82  3

edited Jun 02 '18 at 08:58

answered Jan 25 '16 at 20:41

Jaap

81,064
34
182
193

get error message when using rowID > dcast(setDT(df), rowid(left) ~ left, value.var = "right") Error in eval(expr, envir, enclos) : could not find function "rowid" – user3482393 Jan 25 '16 at 20:59
@user3482393 you have to install the [development version of `data.table`](https://github.com/Rdatatable/data.table/wiki/Installation) for access to the `rowid` function – Jaap Jan 25 '16 at 21:05

score 0 · Answer 2 · answered Jan 25 '16 at 21:03

0

Sticking with dplyr and tidyr (appreciate it's not as neat as other answers!):

df %>% 
mutate(rowId = 1:n()) %>%
group_by(rowId) %>% 
spread(left, right) %>% #gives you wide df with NAs
ungroup() %>% 
select(-rowId) %>% 
mutate(temp = rep(1:3, each = 4)) %>% 
group_by(temp) %>% 
summarise_each(funs(mean(., na.rm = TRUE))) %>% 
ungroup() %>% 
select(-temp)

answered Jan 25 '16 at 21:03

Nick F

311
2
8

it's a little nicer like this: `df %>% group_by(left) %>% mutate(b = row_number()) %>% spread(left, right)` – jeremycg Jan 25 '16 at 21:14
both of these works great, Thanks. – user3482393 Jan 25 '16 at 21:26
Thanks @jeremycg, much neater. – Nick F Jan 25 '16 at 21:37

Convert R dataframe from long to wide format

2 Answers2

Linked

Related