CODEWITHSUNDEEP
rdata.tablegrouping
Soheil
Soheil

Reputation: 974

How to group by different columns and find the percentage based on one column?

I want to group the following data table by var1 and var2 and then find the percentage in var2:

data <- as.data.table(list(var1 = c("x1","x1","x2","x1","x2"), 
                           var2 = c("y1","y1","y1","y2","y2"))) 

data[, .(count = .N), by=.(var1, var2)]

#    var1 var2 count
#1:   x1   y1     2
#2:   x2   y1     1
#3:   x1   y2     1
#4:   x2   y2     1

This is the outcome that I am interested in:

#    var1 var2 count  ratio in var2
#1:   x1   y1     2         0.66
#2:   x2   y1     1         0.33
#3:   x1   y2     1         0.5
#4:   x2   y2     1         0.5

How to change the code to achieve this?

Upvotes: 0

Views: 183

Answers (1)

Jaap
Jaap

Reputation: 83215

This should give you what you want:

data <- data[, .N, by = .(var1, var2)][, ratio:=N/sum(N), by = var2]

which results in:

> data
   var1 var2 N     ratio
1:   x1   y1 2 0.6666667
2:   x2   y1 1 0.3333333
3:   x1   y2 1 0.5000000
4:   x2   y2 1 0.5000000

Upvotes: 3

Related Questions