Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Pivoting rows into columns

Tags:

r

reshape

Suppose (to simplify) I have a table containing some control vs. treatment data:

Which, Color, Response, Count
Control, Red, 2, 10
Control, Blue, 3, 20
Treatment, Red, 1, 14
Treatment, Blue, 4, 21

For each color, I want a single row with the control and treatment data, i.e.:

Color, Response.Control, Count.Control, Response.Treatment, Count.Treatment
Red, 2, 10, 1, 14
Blue, 3, 20, 4, 21

I guess one way of doing this is by using an internal merge on each control/treatment subset (merging on the Color column), but is there a better way? I was thinking the reshape package or the stack function could somehow do it, but I'm not sure.

like image 275
grautur Avatar asked Sep 30 '10 22:09

grautur


People also ask

How do I PIVOT rows into columns in SQL?

In SQL Server you can use the PIVOT function to transform the data from rows to columns: select Firstname, Amount, PostalCode, LastName, AccountNumber from ( select value, columnname from yourtable ) d pivot ( max(value) for columnname in (Firstname, Amount, PostalCode, LastName, AccountNumber) ) piv; See Demo.


1 Answers

Using the reshape package.

First, melt your data.frame:

x <- melt(df) 

Then cast:

dcast(x, Color ~ Which + variable)

Depending on which version of the reshape package you're working with it could be cast() (reshape) or dcast() (reshape2)

Voila.

like image 150
Brandon Bertelsen Avatar answered Oct 02 '22 01:10

Brandon Bertelsen