Convert data frame common rows to columns

Question

Say I have a data frame where one column is some repeating value (dates, IDs, etc). Is there a way to convert a data frame into a now data frame with columns instead of replicating rows? Basically I want to transpose something like this:

col1 col2 col3
1    aa   30
2    aa   40
3    aa   10
1    bb   20
2    bb   12
3    bb   15
1    cc   40
2    cc   31
3    cc   12

Into this:

    aa   bb   cc
1   30   20   40
2   40   12   31
3   10   15   12

Here is some code that makes a sample of the first data frame:

a <- c(rep(1:10, 3))
b <- c(rep("aa", 10), rep("bb", 10), rep("cc", 10))
set.seed(123)
c <- sample(seq(from = 20, to = 50, by = 5), size = 30, replace = TRUE)
d <- data.frame(a,b, c)

I am unsure how to transpose it.

e.g. `tidyr::spread(d, key = b, value = c)` will give what you want. — Alex, Oct 22 '14 at 00:03
Also `reshape` will get you close: `reshape(d , v.names="c", idvar="a", timevar="b", direction="wide" )` — user20650, Oct 22 '14 at 00:03

score 6 · Accepted Answer · answered Oct 22 '14 at 00:09

6

a <- c(rep(1:10, 3))
b <- c(rep("aa", 10), rep("bb", 10), rep("cc", 10))
set.seed(123)
c <- sample(seq(from = 20, to = 50, by = 5), size = 30, replace = TRUE)
d <- data.frame(a,b, c)
#how to transpose it#
e<-reshape(d,idvar='a',timevar='b',direction='wide')
e

answered Oct 22 '14 at 00:09

Jason

1,559
1
9
14

Bingo. This is a textbook, simple example of `reshape` – thelatemail Oct 22 '14 at 00:21
Thank you, I'm going to accept this since it allows for unequal numbers of items in each sample. These are all great though. – Jeffrey Kramer Oct 22 '14 at 13:33

score 6 · Answer 2 · answered Oct 22 '14 at 03:31

6

This is also a case in which you can use unstack:

unstack(d, c ~ b)
#    aa bb cc
# 1  30 50 50
# 2  45 35 40
# 3  30 40 40
# 4  50 40 50
# 5  50 20 40
# 6  20 50 40
# 7  35 25 35
# 8  50 20 40
# 9  35 30 30
# 10 35 50 25

answered Oct 22 '14 at 03:31

A5C1D2H2I1M1N2O1R2T1

190,393
28
405
485

1

The code keeps getting shorter for every new answer – Rich Scriven Oct 22 '14 at 03:32
This is also pretty helpful but I'm going to use one of the earlier solutions just in case my data frame does not contain the same number of observations for each grouping. – Jeffrey Kramer Oct 22 '14 at 13:32

Alex · Answer 3 · 2014-10-22T00:14:25.127

Using your data frame d,

library(tidyr)
> spread(d, key = b, value = c)
    a aa bb cc
1   1 30 50 50
2   2 45 35 40
3   3 30 40 40
4   4 50 40 50
5   5 50 20 40
6   6 20 50 40
7   7 35 25 35
8   8 50 20 40
9   9 35 30 30
10 10 35 50 25

Explanation, the argument key = b lets you specify a column in your data frame. spread will create a new column for each unique entry in the key column b. The argument value = c tells spread to retrieve the value in column c and write it in the corresponding new key column.

Rich Scriven · Answer 4 · 2014-10-22T00:46:23.570

4

If there are always equal numbers of observations in each group, this would be very easy with split then as.data.frame

as.data.frame(split(d$c, d$b))
#    aa bb cc
# 1  30 50 50
# 2  45 35 40
# 3  30 40 40
# 4  50 40 50
# 5  50 20 40
# 6  20 50 40
# 7  35 25 35
# 8  50 20 40
# 9  35 30 30
# 10 35 50 25

edited Oct 22 '14 at 00:46

answered Oct 22 '14 at 00:37

Rich Scriven

97,041
11
181
245

This is neat, so long as there are equal numbers in each group. `reshape` will be preferable when this can't be guaranteed. – thelatemail Oct 22 '14 at 00:43
Agreed. But it looks like it is, according to both the given data sets. Added that to the answer for clarity – Rich Scriven Oct 22 '14 at 00:44
This is cool, thanks! However, it is possible to have different numbers of observations. – Jeffrey Kramer Oct 22 '14 at 13:29

score 0 · Answer 5 · answered Nov 18 '14 at 11:11

With split and cbind:

> ll = lapply(split(d, d$b), function(x) x[3])
> dd = do.call(cbind, ll)
> names(dd) = names(ll)
> dd
   aa bb cc
1  30 50 50
2  45 35 40
3  30 40 40
4  50 40 50
5  50 20 40
6  20 50 40
7  35 25 35
8  50 20 40
9  35 30 30
10 35 50 25

Convert data frame common rows to columns

5 Answers5

Linked

Related