reshape - How to create a pivot table in R with multiple (3+) variables

Question

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 1

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