Transforming a data frame in R

Question

I have a dataframe in R with client information and sales per product. Product is a field with multiple values. Sales is a separate field. I would like to convert the table so the sales from each product has its own column so that I have one row per client (rather than one row per client per product). I have seen information on how to transpose a table, but this is different. Below are two simplified examples of what I am starting with and the desired end result. The real situation will have many more columns, clients and products.

Starting point:

start <- data.frame(client = c(1,1,1,2,2,2), 
product=c("Product1","Product2","Product3","Product1","Product2","Product3"),
          sales = c(100,500,300,200,400,600))

Output:

  client  product sales
1      1 Product1   100
2      1 Product2   500
3      1 Product3   300
4      2 Product1   200
5      2 Product2   400
6      2 Product3   600

Following is the desired end result:

end <- data.frame(client = c(1,2),
                      Product1 = c(100,200), Product2 = c(500,400),
                      Product3 = c(300,600))

Output:

  client Product1 Product2 Product3
1      1      100      500      300
2      2      200      400      600

How can I transform this data from the start to end in R? Thanks in advance for any assistance!

`reshape(start, idvar = "client", timevar = "product", direction = "wide")` — digEmAll, Mar 23 '18 at 16:26
Here's a great info-graphic that detail how to approach these with the `reshape2` package. https://i2.wp.com/www.r-statistics.com/wp-content/uploads/2012/01/reshaping-data-using-melt-and-cast.png?resize=825%2C510&ssl=1 — Stephen Witkowski, Mar 23 '18 at 16:32
thanks all. both the reshape and dcast functions worked for me. I appreciate the help and quick responses! — DPM, Mar 23 '18 at 17:24

score 1 · Accepted Answer · answered Mar 23 '18 at 16:23

> install.packages("reshape2") # to install 'reshape2'.
> library(reshape2)
> dcast(start, client ~ product)
Using sales as value column: use value.var to override.
  client Product1 Product2 Product3
1      1      100      500      300
2      2      200      400      600

Transforming a data frame in R

1 Answers1