Create Pivot Table with Sum in R

To create pivot table with sum for data stored in data.table object in R, we can follow the below steps −

• First of all, create a data.table object with two categorical and one numerical column.

• Then, use dcast function from reshape2 package to create pivot table for the data stored in data.table object.

Example

Create the data.table object

Let’s create a data.table object as shown below −

library(data.table)
Factor<-sample(c("I","II","III","IV","V","VI","VII"),25,replace=TRUE)
Level<-sample(c("Low","Medium","High"),25,replace=TRUE)
Response<-sample(1:100,25)
DT<-data.table(Factor,Level,Response)
DT

Output

On executing, the above script generates the below output(this output will vary on your system due to randomization) −

    Factor Level Response
1:  III    Low    82
2:  IV     Low    42
3:  III    High   87
4:  IV     High   97
5:  IV     Low    29
6:  V      Low    20
7:  V      Medium 50
8:  II     Low    23
9:  I      Low    26
10: V      Medium 39
11: V      Low    13
12: I      High   92
13: I      Low    74
14: III    High    9
15: V      Medium 61
16: II     Medium  2
17: VI     Medium 38
18: IV     Medium 98
19: VI     Low    76
20: I      High   85
21: IV     Low    91
22: II     High   60
23: V      Medium 71
24: IV     High    8
25: I      High   55
   Factor  Level Response

Create pivot table

Using dcast function from reshape2 package to create pivot table for the data stored in data.table object DT −

library(data.table)
Factor<-sample(c("I","II","III","IV","V","VI","VII"),25,replace=TRUE)
Level<-sample(c("Low","Medium","High"),25,replace=TRUE)
Response<-sample(1:100,25)
DT<-data.table(Factor,Level,Response)
library(reshape2) dcast(data=DT,formula=Factor~Level,fun.aggregate=sum,value.var="Response")

Output

 Factor High Low Medium
1 I     232  100   0
2 II     60   23   2
3 III    96   82   0
4 IV    105  162  98
5 V       0   33 221
6 VI      0   76  38