Transposing two fields to one unique key in R

Question

I have a dataframe that has a productID, Seller1Name, Seller1Price, Seller2Name, Seller2Price as below. The table (DF) is unique by productID:

ProductID   Seller1Name    Seller1Price    Seller2Name     Seller2Price
1           A               $1             X                $3
2           B               $3             Y                $6
3           C               $2             Z                $1

The desired output should be DF:

ProductID    Seller  Price
1             A       $1
1             X       $3
2             B       $3
2             Y       $6
3             C       $2
3             Z       $1

I tried using the reshape package but the results are funky:

Output <-melt(DF, Id = c("ProductID"))

Is there a better way to do this?

This is a straightforward `reshape` operation - `reshape(dat, idvar="ProductID", direction="long", varying=list(c(2,4),c(3,5)), v.names=c("Seller","Price") )` — thelatemail, Mar 31 '15 at 23:55
...and use `varying=lapply(c("Name","Price"),grep,x=names(dat))` or similar if you don't want to rely on the positions of the variables in the data.frame. — thelatemail, Apr 01 '15 at 01:50

Arun · Accepted Answer · 2015-06-15T21:53:58.930

In data.table v1.9.5, current devel version, melt for data.tables has gained a new feature -- able to melt on to multiple columns..

require(data.table) ## v1.9.5+
ans = melt(setDT(DF), measure=patterns("Name$", "Price$"), 
                value.name=c("seller", "price"))

We just provide the columns to be grouped together while melting as a list in measure.vars argument.

Now, you can remove variable column and reorder as follows:

setorder(ans[, variable := NULL], ProductID)[]
#    ProductID seller price
# 1:         1      A    $1
# 2:         1      X    $3
# 3:         2      B    $3
# 4:         2      Y    $6
# 5:         3      C    $2
# 6:         3      Z    $1

HTH

score 1 · Answer 2 · answered Apr 01 '15 at 04:22

Another option is merged.stack from splitstackshape

 library(splitstackshape)
 res <- merged.stack(DF, var.stubs=c('Name', 'Price'), sep='var.stubs',
                     atStart=FALSE)[,2:= NULL][]
 setnames(res, 2, 'Seller')
 res 
 #    ProductID Seller Price
 #1:         1    A    $1
 #2:         1    X    $3
 #3:         2    B    $3
 #4:         2    Y    $6
 #5:         3    C    $2
 #6:         3    Z    $1

Transposing two fields to one unique key in R

2 Answers2

Linked

Related