R: regrouping multiple rows into a single row (by value in first column)

Question

I have a data frame from an imported CSV in R such as:

Salmon  6
Salmon  4
Salmon  7
Trout   2
Trout   3
Trout   2

that I would like to rearrange to:

Salmon  6  4  7

Trout  2  3  2

The transposed values are each in a new column.

score 2 · Answer 1 · answered Jun 22 '17 at 20:50

2

You can use aggregate:

aggregate(V2 ~ V1, data = mydata, c)

#       V1 V2.1 V2.2 V2.3
# 1 Salmon    6    4    7
# 2  Trout    2    3    2

Data:

read.table(text='Salmon  6
Salmon  4
Salmon  7
Trout   2
Trout   3
Trout   2', header=F, quote='"') -> 
mydata

answered Jun 22 '17 at 20:50

M--

25,431
8
61
93

1

I was trying to use `spread()` this looks simpler. `spread()` would return and error. – jpf5046 Jun 22 '17 at 21:00
Thanks. This appears to aggregate all of the numerical values into a single vector in V2. e.g. column 2 becomes c(4,7,2) – James Jun 22 '17 at 22:28

score 1 · Answer 2 · answered Jun 23 '17 at 05:11

Here is an option using dcast

library(data.table)
dcast(setDT(mydata), V1~paste0("VN", rowid(V1)), value.var = 'V2')
#       V1 VN1 VN2 VN3
#1: Salmon   6   4   7
#2:  Trout   2   3   2

data

mydata <- structure(list(V1 = structure(c(1L, 1L, 1L, 2L, 2L, 2L), .Label = c("Salmon", 
"Trout"), class = "factor"), V2 = c(6L, 4L, 7L, 2L, 3L, 2L)), .Names = c("V1", 
"V2"), class = "data.frame", row.names = c(NA, -6L))

R: regrouping multiple rows into a single row (by value in first column)

2 Answers2

data