Converting multiple duplicate rows into columns R

Question

I am new to R and I have a data frame with multiple duplicates in the first column, however the second column has unique values.

I want to keep the unique values in the first column and have a different column for each unique value.

My current data frame looks something like this:

Item    Value
Apricot 4
Apricot 2
Apricot 5
Banana  4
Carrot  7
Carrot  5

and I want it to look like this:

Item    Value 1     Value 2     Value 3
Apricot     4           2           5
Banana      4       
Carrot      7           5

Thanks in advance. Fojjit

score 6 · Accepted Answer · edited May 25 '16 at 12:48

6

This could be easily done with data.table (if you are using the devel version i.e. 1.9.7, installation details here). The convenient function rowid can get the sequence based on a variable. Use this in the dcast formula and we get the 'wide' output.

library(data.table)
dcast(setDT(df1), Item~rowid(Item, prefix="Value"), value.var="Value")
#      Item Value1 Value2 Value3
#1: Apricot      4      2      5
#2:  Banana      4     NA     NA
#3:  Carrot      7      5     NA

edited May 25 '16 at 12:48

Arun

116,683
26
284
387

answered May 25 '16 at 10:57

akrun

874,273
37
540
662

I am struggling to use the function rowid, could you help? thanks – Fojjit May 25 '16 at 13:13
@Fojjit. The `rowid` function is not available in `data.table` 1.9.6, the version available for download on CRAN. To use `rowid`, you would have to download the development version, 1.9.7 as is outlined in the link in the answer. – lmo May 25 '16 at 13:44
@akrun Though this is a old one, it helped me for one of my analysis. However, would be great if you could tell when one more variable get added in df1 which needs to be casted. i.e., Item, value, Amount. – ssan Jul 06 '19 at 14:10
@ssan Please post as a new question – akrun Jul 06 '19 at 14:40

score 3 · Answer 2 · answered May 25 '16 at 10:24

3

You need an extra column for the value order. Them you can use spread

library(dplyr)
library(tidyr)
your.data %>%
  group_by(Item) %>%
  mutate(Order = seq_along(Item)) %>%
  spread(key = Order, value = Value)

answered May 25 '16 at 10:24

Thierry

18,049
5
48
66

What do you mean by add a column for the value order? thanks – Fojjit May 25 '16 at 12:38

Converting multiple duplicate rows into columns R

2 Answers2