Using proportion as the aggregate function in Data.Table's dcast

1
votes

When creating a pivot table using data.table, I am using the dcast function:

dcast(my_data, var1 ~ var2, length)

This gives a table with rows as var1 lables and column as var2 labels and value as count of cells common to particular row and column.

But instead of length I want to calculate the proportion and put it as the value, i.e. {count of cells common to particular row and column} divided by {count of all cells in the column i.e. a particular level of var2}

I have searched and couldn't able to implement it. Any help would be appreciated.

2
rdata.tabledcast
do you have any sample data? and desired output? – chinsoon12

2 Answers

4
votes

There is a relatively simple solution, but it requires a second step after the dcast().

First, this is the data I am working on:

library(data.table)

set.seed(666)
my_data <- data.table(var1 = sample(letters[1:3], 10, TRUE),
                      var2 = sample(letters[4:6], 10, TRUE))

    var1 var2
 1:    c    f
 2:    a    d
 3:    c    d
 4:    a    d
 5:    b    d
 6:    c    f
 7:    c    d
 8:    b    f
 9:    a    e
10:    a    e

After the dcast 

my_data_dcast <- dcast(my_data, var1 ~ var2, length)

the data looks like this:

   var1 d e f
1:    a 2 2 0
2:    b 1 0 1
3:    c 2 0 2

You can then simply go through all columns and divide each element in a column by the sum of all values in a column.

Select the columns to transform:

cols <- unique(my_data$var2)

Go through columns using lapply() on the subset of columns specified in .SDcols and override the values of all cols:

my_data_dcast[, (cols) := (lapply(.SD, function(col) col / sum(col))),
              .SDcols = cols]

The final result is this:

   var1   d e         f
1:    a 0.4 1 0.0000000
2:    b 0.2 0 0.3333333
3:    c 0.4 0 0.6666667
3
votes

We can use Reduce with + if we need a row wise proportion

dcast(my_data, var1~ var2, length)[, .SD/Reduce(`+`, .SD), var1]
#   var1         A         B         C    D
#1:    a 0.3750000 0.0000000 0.3750000 0.25
#2:    b 0.6000000 0.2000000 0.2000000 0.00
#3:    c 0.2857143 0.1428571 0.5714286 0.00

If we need column wise

dcast(my_data, var1~ var2, length)[, .SD, var1][, 
        (2:5) := Map(`/`, .SD, colSums(.SD)), .SDcols = -1][]
#   var1     A   B     C D
#1:    a 0.375 0.0 0.375 1
#2:    b 0.375 0.5 0.125 0
#3:    c 0.250 0.5 0.500 0

This would be more compact with base R

prop.table(table(my_data), 1)
prop.table(table(my_data), 2)

data

set.seed(24)
my_data <- data.table(var1 = sample(letters[1:3], 20, replace = TRUE),
           var2 = sample(LETTERS[1:4], 20, replace = TRUE))