How to create pivot table with sum for data stored in data.table object 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

Nizamuddin Siddiqui

e

Updated on: 16-Nov-2021

120 Views

Kickstart Your Career

Get certified by completing the course

Get Started