How to create a pivot table in R with multiple (3+) variables
I am having problems in create a pivot table with a data frame like this:
c1 c2 c3 c4
E 5.76 201 A la vista
E 47530.71 201 A la vista
E 82.85 201 A la vista
L 11376.55 201 A la vista
E 6683.37 203 A la vista
E 66726.52 203 A la vista
E 2.39 203 A la vista
E 79066.07 202 Montoxv_a60d
E 14715.71 202 Montoxv_a60d
E 22661.78 202 Montoxv_a60d
L 81146.25 124 Montoxv_a90d
L 471730.2 124 Montoxv_a186d
E 667812.84 124 Montoxv_a186d
My problem is that I don't know how to create in R a pivot table or summary table with four variables, considering for the final table in the rows, the levels of c1 and c3 and as columns the levels of c4. The values of c2 variable must be aggregated by sum for each level considered in rows. I would like to get something like this:
A la vista Montoxv_a60d Montoxv_a186d Montoxv_a90d
E 201 47619.32 0 0 0
E 203 73412.28 0 0 0
E 202 0 116443.56 0 0
E 124 0 0 667812.84 0
L 201 11376.55 0 0 0
L 124 0 0 471730.2 81146.25
Answer
You can do this with dcast from the reshape2 package:
dcast(mydata, c1 + c3 ~ c4, value.var="c2", fun.aggregate=sum)
For example:
library(reshape2)
# reproducible version of your data
mydata = read.csv(text="c1,c2,c3,c4
E,5.76,201,A la vista
E,47530.71,201,A la vista
E,82.85,201,A la vista
L,11376.55,201,A la vista
E,6683.37,203,A la vista
E,66726.52,203,A la vista
E,2.39,203,A la vista
E,79066.07,202,Montoxv_a60d
E,14715.71,202,Montoxv_a60d
E,22661.78,202,Montoxv_a60d
L,81146.25,124,Montoxv_a90d
L,471730.2,124,Montoxv_a186d
E,667812.84,124,Montoxv_a186d", header=TRUE)
result = dcast(mydata, c1 + c3 ~ c4, value.var="c2", fun.aggregate=sum)
produces:
c1 c3 A la vista Montoxv_a186d Montoxv_a60d Montoxv_a90d
1 E 124 0.00 667812.8 0.0 0.00
2 E 201 47619.32 0.0 0.0 0.00
3 E 202 0.00 0.0 116443.6 0.00
4 E 203 73412.28 0.0 0.0 0.00
5 L 124 0.00 471730.2 0.0 81146.25
6 L 201 11376.55 0.0 0.0 0.00