How to find the mean of a column summarized by other column names and common values in those columns in data.table object in R?

To find the mean of a column summarized by other column names and common values in those columns in data.table object in R, we can follow the below steps −

• First of all, create a data.table object.

• Then, melt the data.table object using melt function from reshape2 package.

• After that, use dcast function to find the mean of a column summarized by other column names and common values in those columns.

Example

Create the data.table object

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

library(data.table)
ID<-LETTERS[1:25]
f1<-sample(0:2,25,replace=TRUE)
f2<-sample(0:2,25,replace=TRUE)
DV<-rnorm(25)
DT<-data.table(ID,f1,f2,DV)
DT

Output

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

    ID f1  f2      DV
1:  A  0   2   -0.43728169
2:  B  0   2    0.94425821
3:  C  2   2   -0.36174159
4:  D  2   0   -1.61506759
5:  E  0   1   -0.91609245
6:  F  0   0   -1.59289758
7:  G  1   2   -0.91523489
8:  H  0   0   -1.21002004
9:  I  0   1    0.84662264
10: J  1   0    1.08053374
11: K  0   0    1.30727422
12: L  1   0    0.66752310
13: M  1   0   -1.06868916
14: N  1   0   -0.04441251
15: O  2   2    1.47602752
16: P  2   0   -1.24926504
17: Q  2   2    1.01722018
18: R  1   2   -1.88543815
19: S  0   0   -2.13536456
20: T  0   0   -0.30234787
21: U  0   1   -0.17677848
22: V  1   1   -0.34633266
23: W  2   2    0.98363925
24: X  0   0    0.78338684
25: Y  0   2   -0.35833690
    ID f1 f2       DV

Melt the data.table object

Using melt function from reshape2 package to melt the data.table object DT −

library(data.table)
ID<-LETTERS[1:25]
f1<-sample(0:2,25,replace=TRUE)
f2<-sample(0:2,25,replace=TRUE)
DV<-rnorm(25)
DT<-data.table(ID,f1,f2,DV)
library(reshape2)
DT_new<-melt(DT,id.vars=c("ID","DV"))
DT_new

Output

   ID  DV         variable value
1  A  -0.43728169  f1       0
2  B   0.94425821  f1       0
3  C  -0.36174159  f1       2
4  D  -1.61506759  f1       2
5  E  -0.91609245  f1       0
6  F  -1.59289758  f1       0
7  G  -0.91523489  f1       1
8  H  -1.21002004  f1       0
9  I   0.84662264  f1       0
10 J   1.08053374  f1       1
11 K   1.30727422  f1       0
12 L   0.66752310  f1       1
13 M  -1.06868916  f1       1
14 N  -0.04441251  f1       1
15 O   1.47602752  f1       2
16 P  -1.24926504  f1       2
17 Q   1.01722018  f1       2
18 R  -1.88543815  f1       1
19 S  -2.13536456  f1       0
20 T  -0.30234787  f1       0
21 U  -0.17677848  f1       0
22 V  -0.34633266  f1       1
23 W   0.98363925  f1       2
24 X   0.78338684  f1       0
25 Y  -0.35833690  f1       0
26 A  -0.43728169  f2       2
27 B   0.94425821  f2       2
28 C  -0.36174159  f2       2
29 D  -1.61506759  f2       0
30 E  -0.91609245  f2       1
31 F  -1.59289758  f2       0
32 G  -0.91523489  f2       2
33 H  -1.21002004  f2       0
34 I   0.84662264  f2       1
35 J   1.08053374  f2       0
36 K   1.30727422  f2       0
37 L   0.66752310  f2       0
38 M  -1.06868916  f2       0
39 N  -0.04441251  f2       0
40 O   1.47602752  f2       2
41 P  -1.24926504  f2       0
42 Q   1.01722018  f2       2
43 R  -1.88543815  f2       2
44 S  -2.13536456  f2       0
45 T  -0.30234787  f2       0
46 U  -0.17677848  f2       1
47 V  -0.34633266  f2       1
48 W   0.98363925  f2       2
49 X   0.78338684  f2       0
50 Y  -0.35833690  f2       2

Find the mean of a column summarized by other column names and common values

Using dcast function to find the mean of DV summarized by column names f1 and f2 and common values 0,1, and 2 in these group columns −

library(data.table)
ID<-LETTERS[1:25]
f1<-sample(0:2,25,replace=TRUE)
f2<-sample(0:2,25,replace=TRUE)
DV<-rnorm(25)
DT<-data.table(ID,f1,f2,DV)
library(reshape2)
DT_new<-melt(DT,id.vars=c("ID","DV"))
dcast(DT_new,variable~value,value.var="DV",fun.aggregate=mean)

Output

   variable      0           1         2
1    f1      -0.2706315 -0.3588644 0.04180212
2    f2      -0.4482789 -0.1481452 0.05145688