Add New Column Based on Duplicate Row

[Chris Boyers]

I'm trying to import atablethat has duplicate Key Identifier (Case) values like a simple one below.

 

 

 

Input Table

 

 

 

 

Case

Fruit

Vegetable

 

 

A

Apple

Brocolli

 

 

A

Grape

Okra

 

 

B

Orange

Peas

 

 

B

Kiwi

Okra

 

 

B

Banana

Cucumber

 

 

C

Mango

Okra

 

 

C

Grape

Carrots

 

 

C

Kiwi

Peas

 

 

C

Strawberry

Cucumber

 

 

C

Orange

Squash

 

 

 

 

I would like to end up with adatatable that looks likethe one belowwhere each case is only once in a single row, but the other data is put into individual columns.

 

 

 

Desired Outcome

 

 

 

 

 

 

 

 

 

 

 

Case

Fruit 1

Fruit 2

Fruit 3

Fruit 4

Fruit 5

Vegetable 1

Vegetable 2

Vegetable 3

Vegetable 4

Vegetable 5

 

 

A

Apple

Grape

 

 

 

Brocolli

Okra

 

 

 

 

 

B

Orange

Kiwi

Banana

 

 

Peas

Okra

Cucumber

 

 

 

 

C

Mango

Grape

Kiwi

Strawberry

Orange

Okra

Carrots

Peas

Cucumber

Squash

 

 

 

I've tried some TERR scripts, pivot/unpivot, but always end up with some elements that aren't working on the actual dataset 1.4million rows.

Thank you for your help.

[Gaia Paolini]

Here's a TERR script: you will need to install the data.table and splitstackshape packages.

 

To install librariesgo to Tools > TERR Tools > Package Management, load the repositoryand select your libraries to install.

Then define the following data function, input is table dt (your original data) and output table dt2 (your transformed data):

library(data.table)

library(splitstackshape)

setDT(dt) #turns input data frame into data table

#first collapse into two comma-separated columns

dt1=dt[ , .(Fruit = paste(Fruit, collapse=','),

Vegetable = paste(Vegetable,collapse=',')), by = Case]

#then expand into individual columns

dt2=cSplit(dt1, c('Fruit','Vegetable'), ",")

Gaia

[Chris Boyers]

This works perfectly.  Thank you!