Prevalence Estimates from Observations in data.table Containing Many Binary Classification Columns

user_14815134 0
Viewed 95

I am doing prevalence estimates from my raw data.table by brute force and I need to be more efficient. Can you help?

My data.table contains one weighted observation per row. There are many columns acting as binary dummy variables indicating if the particular observation belongs to one or more of many possible classifications. (e.g., a story could be 'amazing', 'boring', or 'charming', or any combination of the three.)

There's got to be a data.table way to replace my forloop. I also suspect that I might not need to necessarily generate the queries set. I appreciate a fresh set of eyes on this problem.

library(data.table)

set.seed(42)
# I have many weighted observations that can be labeled as belonging to one of many categories
# in this example, I simulate 10 observations and only 3 categories
dt = data.table(
        weight = runif( n = 10 , min = 0, max = 1 ),
        a = sample( x = c(0,1) , size = 10 , replace = TRUE ),
        b = sample( x = c(0,1) , size = 10 , replace = TRUE ),
        c = sample( x = c(0,1) , size = 10 , replace = TRUE )
)

# Generate all combinations of categories
queries = as.data.table( expand.grid( rep( list(0:1) , length(names(dt))-1 ) ) )
names(queries) = names(dt)[ 2:length(names(dt)) ] # rename Var1, Var2, Var3 to a, b, c

# Brute force through each possible combination to calculate prevalence
prevalence = rep( NA, nrow(queries) )
for( q in 1:nrow(queries) ){
    prevalence[q] = dt[ a == queries[q, a] & b == queries[q, b] & c == queries[q, c] , sum(weight) ] / dt[ , sum(weight) ]
}

results = copy(queries)
results$prevalence = prevalence

results

The output is:

#   a b c prevalence
#1: 0 0 0 0.09771385
#2: 1 0 0 0.10105192
#3: 0 1 0 0.36229784
#4: 1 1 0 0.00000000
#5: 0 0 1 0.00000000
#6: 1 0 1 0.05993197
#7: 0 1 1 0.00000000
#8: 1 1 1 0.37900443

Updated: The original question had 42 simulated observations and the data covered each possible combination of categories (a, b, c). The question was revised to only include 10 simulated observations so there would be combinations with no observations (and zero prevalence).

data.table r
2 Answers

updated answer

Method 1:

  1. use CJ to create the full combination of a,b,c then join with dt (as in @TMo's answer)
  2. sum the weight of each group then divide it by totoal_weight
  3. The appearance of NA is rational. You can also fill it by 0 with nafill function if you want.
total_weight = sum(dt$weight)
dt[CJ(a, b, c, unique = TRUE),  
   on = .(a, b, c)][, 
                    .( prevalence = sum(weight)/total_weight), 
                    by = .(a,b,c)]

#      a     b     c prevalence
#   <num> <num> <num>      <num>
#1:     0     0     0 0.09771385
#2:     0     0     1         NA
#3:     0     1     0 0.36229784
#4:     0     1     1         NA
#5:     1     0     0 0.10105192
#6:     1     0     1 0.05993197
#7:     1     1     0         NA
#8:     1     1     1 0.37900443

Method2:

dt2 = dt[,.( prevalence = sum(weight) / total_weight ), by = .(a,b,c)]
dt2[queries, on = .(a,b,c)]
# or `queries[, prevalence := fcoalesce(dt2[queries, prevalence])]`
#       a     b     c prevalence
#   <int> <int> <int>      <num>
#1:     0     0     0 0.09771385
#2:     1     0     0 0.10105192
#3:     0     1     0 0.36229784
#4:     1     1     0         NA
#5:     0     0     1         NA
#6:     1     0     1 0.05993197
#7:     0     1     1         NA
#8:     1     1     1 0.37900443

original answer

You can calculate it by group

dt[,.( prevalence = sum(weight) / dt[,sum(weight)] ), by = .(a,b,c)]
  • each group corresponds to your categories
  • sum the weight of each group then divide it by totoal weight
user_75227640

Here are some solutions (in both cases, you can replace keyby argument with by)

If your dataset (dt) already contains all possible combinations of the different categories, then you could do (as in @Peace Wang solution)

dt[, .(prevalence = sum(weight)/sum(dt$weight)), keyby=.(a, b, c)]

#        a     b     c prevalence
# 1:     0     0     0 0.10876301
# 2:     0     0     1 0.02135357
# 3:     0     1     0 0.03775363
# 4:     0     1     1 0.12806864
# 5:     1     0     0 0.18204696
# 6:     1     0     1 0.15197811
# 7:     1     1     0 0.25629705
# 8:     1     1     1 0.11373903

Instead, if the dataset does not contain all possible combinations of the different categories, then you could solve it as follows (CJ(a, b, c, unique=TRUE) computes all combinations and remove duplicates)

dt[CJ(a, b, c, unique=TRUE), .(prevalence = sum(weight)/sum(dt$weight)), keyby=.(a, b, c), on=.(a, b, c)]

#        a     b     c prevalence
# 1:     0     0     0 0.10876301
# 2:     0     0     1 0.02135357
# 3:     0     1     0 0.03775363
# 4:     0     1     1 0.12806864
# 5:     1     0     0 0.18204696
# 6:     1     0     1 0.15197811
# 7:     1     1     0 0.25629705
# 8:     1     1     1 0.11373903
user_108488980
Related