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

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

• First of all, create a data frame.

• Then, melt the data frame 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 frame

Let’s create a data frame as shown below −

S.No<-1:25
Grp1<-sample(1:3,25,replace=TRUE)
Grp2<-sample(1:3,25,replace=TRUE)
Score<-sample(1:50,25)
df<-data.frame(S.No,Grp1,Grp2,Score)
df

Output

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

 S.No Grp1 Grp2 Score
1   1    1  3   39
2   2    1  1   50
3   3    2  2    5
4   4    1  1   28
5   5    3  3    3
6   6    2  1   20
7   7    3  2   10
8   8    2  2   41
9   9    2  3   25
10 10    3  1   11
11 11    3  1   13
12 12    2  1    2
13 13    2  2   22
14 14    2  1   30
15 15    3  1   15
16 16    1  3   29
17 17    3  3    1
18 18    1  1   24
19 19    3  3   34
20 20    1  2   44
21 21    3  3   48
22 22    3  2   43
23 23    1  2   33
24 24    2  2   19
25 25    3  3   42

Melt the data frame

Using melt function from reshape2 package to melt the data frame df −

S.No<-1:25
Grp1<-sample(1:3,25,replace=TRUE)
Grp2<-sample(1:3,25,replace=TRUE)
Score<-sample(1:50,25)
df<-data.frame(S.No,Grp1,Grp2,Score)
library(reshape2)
df_new<-melt(df,id.vars=c("S.No","Score"))
df_new

Output

  S.No Score variable value
1   1   28    Grp1    2
2   2   14    Grp1    3
3   3   17    Grp1    2
4   4   36    Grp1    3
5   5   47    Grp1    3
6   6   27    Grp1    2
7   7   48    Grp1    1
8   8   50    Grp1    3
9   9   29    Grp1    3
10 10   20    Grp1    2
11 11   32    Grp1    1
12 12    9    Grp1    3
13 13   16    Grp1    1
14 14    3    Grp1    1
15 15   42    Grp1    3
16 16   26    Grp1    2
17 17   44    Grp1    2
18 18   39    Grp1    2
19 19   46    Grp1    1
20 20   19    Grp1    2
21 21   22    Grp1    2
22 22   23    Grp1    3
23 23   49    Grp1    3
24 24    8    Grp1    1
25 25   30    Grp1    3
26  1   28    Grp2    2
27  2   14    Grp2    2
28  3   17    Grp2    2
29  4   36    Grp2    2
30  5   47    Grp2    3
31  6   27    Grp2    1
32  7   48    Grp2    2
33  8   50    Grp2    1
34  9   29    Grp2    1
35 10   20    Grp2    2
36 11   32    Grp2    3
37 12    9    Grp2    2
38 13   16    Grp2    1
39 14    3    Grp2    1
40 15   42    Grp2    2
41 16   26    Grp2    3
42 17   44    Grp2    1
43 18   39    Grp2    2
44 19   46    Grp2    3
45 20   19    Grp2    2
46 21   22    Grp2    1
47 22   23    Grp2    2
48 23   49    Grp2    3
49 24    8    Grp2    3
50 25   30    Grp2    3

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

Using dcast function to find the mean of Score summarized by column names Grp1 and Grp2 and common values 1,2, and 3 in these group columns −

S.No<-1:25
Grp1<-sample(1:3,25,replace=TRUE)
Grp2<-sample(1:3,25,replace=TRUE)
Score<-sample(1:50,25)
df<-data.frame(S.No,Grp1,Grp2,Score)
library(reshape2)
df_new<-melt(df,id.vars=c("S.No","Score"))
dcast(df_new,variable~value,value.var="Score",fun.aggregate=mean)

Output

  variable   1      2       3
1 Grp1   25.50000 26.88889 32.9
2 Grp2   27.28571 26.81818 34.0