Mean of means with a subset

Question

Imagine a data table

ID    Score
1     10
1     13
1     12
2     10
3     6
3     6

The average for user 1 is 11.67. The average for user 2 is 10. The average for user 3 is 6.

The UniqAverage I am looking for would be ((11.67+10+6)/3) =9.223`

For replication purposes feel free to use...

library(data.table)
df = data.frame( ID=c(1,1,1,2,3,3), Score1=c(10,13,12,10,6,6) ) 
dt = data.table(df)

A previous question lead to me learning I could do

dt[, mean(Score1), ID][,mean(V1)]

To get this average of averages by ID.

However, what if I only wanted the mean of scores > 10?

So I'd wind up with

So my final result would be 4.167

Attempts to use which failed.

dt[, mean( which(Score1) > 10 ), ID][,mean(V1)]

In most (if not all) cases computing an average of averages is ill advised. See ksrowell.com/blog-visualizing-data/2014/05/09/… — PavoDive
Yup. This is much simpler than what I had. I got it working now by doing dt[ dt[,Score1>10], mean(Score1), ID] — Jibril
You can construct an example like dt = data.table(...). No need to start with df = data.frame(..). — Frank
Why dt[ dt[,Score1>10], mean(Score1), ID]? Why not dt[Score1>10, mean(Score1), ID] — David Arenburg

akrun akrun · Accepted Answer · 2016-04-17T03:19:49

We can join on a dataset with unique 'ID' after getting the mean of 'Score1' by 'ID' where the "Score1" is greater than 10.

dt1 <- dt[Score1>10, .(V1= mean(Score1)), by = ID
          ][data.table(ID= unique(dt$ID)), on = "ID"][is.na(V1), V1 :=0][]
dt1
#   ID   V1
#1:  1 12.5
#2:  2  0.0
#3:  3  0.0

dt1[, mean(V1)]
#[1] 4.166667

Mean of means with a subset

2 Answers