Noah Noah - 1 month ago 11
R Question

Transform unique values of a column into multiple columns containing their corresponding values in another column

Here is a sample dataframe similar to the one I'm working with:

set.seed(74.3)
df<-data.frame(ID=sample(c(1000:1004),size=20,replace=T),
Fruit=sample(c("Apple","Banana","Pear","Orange","Plum"),size=20,replace=T))
library(dplyr)
df <- df %>%
group_by(ID,Fruit) %>%
summarise(n=n())


ID Fruit n
(int) (fctr) (int)
1 1000 Banana 1
2 1000 Orange 3
3 1000 Pear 1
4 1001 Banana 1
5 1001 Plum 2
6 1002 Banana 1
7 1003 Banana 1
8 1003 Orange 2
9 1003 Pear 1
10 1003 Plum 1
11 1004 Apple 2
12 1004 Banana 2
13 1004 Orange 1
14 1004 Pear 1


How can I transpose the Fruit and n columns and group the IDs so that each type of fruit is a separate column containing the number of fruits that correspond to the ID?

ID Banana Orange Pear Plum Apple
1000 1 3 1 0 0
1001 1 0 0 2 0
1002 1 0 0 0 0
1003 1 2 1 1 0
1004 2 1 1 0 2


Sorry if this question has already been posted. I've spent several hours searching and haven't an answer.

Answer

Using tidyr

library(tidyr); 
spread(df, Fruit, n, fill=0)

Source: local data frame [5 x 6]
Groups: ID [5]

     ID Apple Banana Orange  Pear  Plum
* <int> <dbl>  <dbl>  <dbl> <dbl> <dbl>
1  1000     0      1      3     1     0
2  1001     0      1      0     0     2
3  1002     0      1      0     0     0
4  1003     0      1      2     1     1
5  1004     2      2      1     1     0

Alternatively you can use dcast from reshape2 or data.table as

reshape2::dcast(df, ID~Fruit, value.var = "n", fill = 0)