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

Updated on 16-Nov-2021 05:25:34

• Related Questions & Answers
• How to create pivot table with sum for data stored in R data frame?
• How to create a contingency table with sum on the margins from an R data frame?
• How to create a frequency table in data frame format in R?
• Create a Pivot Table with multiple columns – Python Pandas
• Insert data in a table in MySQL stored procedure?
• Where is the MySQL table data stored in Windows?
• How to create table of two factor columns in an R data frame?
• How to create a table of frequency for range of values in an R data frame column?
• How we have multiple stored GENERATED COLUMNS in MySQL table with CREATE TABLE statement?
• Update data in one table from data in another table in MySQL?
• How to find the frequency table for factor columns in an R data frame?
• How to repeat the values stored in a data column of MySQL table?
• How to create a single data frame from data frames stored in a list with row names in R?
• Create a Pivot Table as a DataFrame – Python Pandas
• How to create a column in an R data frame with cumulative sum?