Calculate Percentage and other functions using data.table

Question

I want to apply aggregate functions and percentage function to column. I found threads that discuss aggregation (Calculating multiple aggregations with lapply(.SD, ...) in data.table R package) and threads that discuss percentage (How to obtain percentages per value for the keys in R using data.table? and Use data.table to calculate the percentage of occurrence depending on the category in another column), but not both.

Please note that I am looking for data.table based methods. dplyr wouldn't work on actual data set.

Here's the code to generate sample data:

set.seed(10)
  IData <- data.frame(let = sample( x = LETTERS, size = 10000, replace=TRUE), numbers1 = sample(x = c(1:20000),size = 10000), numbers2 = sample(x = c(1:20000),size = 10000))
  IData$let<-as.character(IData$let)

  data.table::setDT(IData)

Here's the code to generate output using dplyr

Output <- IData %>%
    dplyr::group_by(let) %>%
    dplyr::summarise(numbers1.mean = as.double(mean(numbers1)),numbers1.median = as.double(median(numbers1)),numbers2.mean=as.double(mean(numbers2)),sum.numbers1.n = sum(numbers1)) %>%
    dplyr::ungroup() %>%
    dplyr::mutate(perc.numbers1 = sum.numbers1.n/sum(sum.numbers1.n)) %>%
    dplyr::select(numbers1.mean,numbers1.median,numbers2.mean,perc.numbers1)

Sample Output (header) If I run head(output), I would get:

  let numbers1.mean numbers1.median numbers2.mean perc.numbers1
  <chr>         <dbl>           <dbl>         <dbl>         <dbl>
    N     10320.951         10473.0      9374.435    0.03567927
    H      9683.590          9256.5      9328.035    0.03648391
    L     10223.322         10226.0      9806.210    0.04005400
    S      9922.486          9618.0     10233.849    0.03678742
    C      9592.620          9226.0      9791.221    0.03517997
    F     10323.867         10382.0     10036.561    0.03962035

Here's what I tried using data.table (unsuccessfully)

  IData[, as.list(unlist(lapply(.SD, function(x) list(mean=mean(x),median=median(x),sum=sum(x))))), by=let, .SDcols=c("numbers1","numbers2")] [,.(Perc = numbers1.sum/sum(numbers1.sum)),by=let]

I have 2 Questions:

a) How can I solve this using data.table?

b) I have seen above threads have used prop.table. Can someone please guide me how to use this function?

I would sincerely appreciate any guidance.

akrun akrun · Accepted Answer · 2017-06-13T04:56:39

We can use the similar approach with data.table

res <- IData[, .(numbers1.mean = mean(numbers1),
          numbers1.median = median(numbers1),
          numbers2.mean=mean(numbers2),
          sum.numbers1.n = sum(numbers1)), let
          ][, perc.numbers1 := sum.numbers1.n/sum(sum.numbers1.n)
           ][, c("let", "numbers1.mean",  "numbers1.median", 
                        "numbers2.mean", "perc.numbers1"), with = FALSE]

head(res)
#    let numbers1.mean numbers1.median numbers2.mean perc.numbers1
#1:   N     10320.951         10473.0      9374.435    0.03567927
#2:   H      9683.590          9256.5      9328.035    0.03648391
#3:   L     10223.322         10226.0      9806.210    0.04005400
#4:   S      9922.486          9618.0     10233.849    0.03678742
#5:   C      9592.620          9226.0      9791.221    0.03517997
#6:   F     10323.867         10382.0     10036.561    0.03962035

Calculate Percentage and other functions using data.table

1 Answers