CODEWITHSUNDEEP
rtranspose
user2426619
user2426619

Reputation: 33

Create additional rows and transpose in R

Im handling some stock order data and am having a problem with what I suspect needs a transpose. The data frame lists the qty for each supply location across the row for each customer for each item but I need it to have a separate row for each supply location

What I have looks like this - Each of the numbered columns is a supply location

1. Customer Cust.location Product 116 117 41 25 81 Total.Order
2. ABC Tap 123 5 3 0 2 1 11 
3. ABC Tap 456 0 1 4 0 2 7
4. DEF Kar 123 1 0 0 3 4 8

What I need is 

1. Customer  Cust.Location  Product  Source  Total  
2. ABC       Tap            123      116     5   
3. ABC       Tap            123      117     3  
4. ABC       Tap            123      25      2  
5. ABC       Tap            123      81      1  
6. ABC       Tap            456      117     1  
7. ABC       Tap            456      41      4  
8. ABC       Tap            456      81      2  
9. DEF       Kar            123      116     1  
10.DEF       Kar            123      25      3  
11.DEF       Kar            123      81      4

Sorry abou the poor layout - first time post here.
Not worried too much about handling 0 qty lines so if you have a solution that retains them it doesn't matter

Upvotes: 0

Views: 112

Answers (2)

thelatemail
thelatemail

Reputation: 93813

Base reshape method that @alexwhan alludes to is very similar:

dat <- read.table(text="Customer Cust.location Product 116 117 41 25 81 Total.Order
ABC Tap 123 5 3 0 2 1 11 
ABC Tap 456 0 1 4 0 2 7
DEF Kar 123 1 0 0 3 4 8",header=TRUE)

reshape(
  dat[,-9],
  idvar=c("Customer","Cust.location", "Product"),
  varying=4:8,
  v.names="Total",
  timevar="Source",
  times=names(dat[4:8]),
  direction="long"
)

Upvotes: 2

alexwhan
alexwhan

Reputation: 16026

This is classic reshaping from wide to long format. The melt function from the reshape2 package is how I prefer to do it, though you can use the reshape function in base R. If your data.frame is dat:

library(reshape2)
dat.m <- melt(dat[,-9], id.vars= c("Customer", "Cust.location", "Product"),
              variable.name="Source", value.name="Total")

I've removed the Total.Order column (dat[,-9]) because it seems you don't need that.

#    Customer Cust.location Product Source Total
# 1       ABC           Tap     123    116     5
# 2       ABC           Tap     456    116     0
# 3       DEF           Kar     123    116     1
# 4       ABC           Tap     123    117     3
# 5       ABC           Tap     456    117     1
# 6       DEF           Kar     123    117     0
# 7       ABC           Tap     123     41     0
# 8       ABC           Tap     456     41     4
# 9       DEF           Kar     123     41     0
# 10      ABC           Tap     123     25     2
# 11      ABC           Tap     456     25     0
# 12      DEF           Kar     123     25     3
# 13      ABC           Tap     123     81     1
# 14      ABC           Tap     456     81     2
# 15      DEF           Kar     123     81     4

Upvotes: 2

Related Questions