Grouping on two columns and performing multiple calculations

Question

I am currently working with a large dataframe which consists of rougly 20 columns and a lot of rows.

Simplified it can look like this:

letter = c("A", "A", "A", "B", "B", "B", "C", "C", "C", "C", "A", "A", "A", "B", "B", "B", "C")
number = c(1,2,3,1,2,1,2,3,2,1,2,3,2,2,3,2,1)\
value1 = c(1:17)
value2 = seq(18, 50, 2)
df = data.frame(letter, number, value1, value2)

I would like to do some calculations on specific sets of values. These are the unique combinations of letter and number. So all values who have the letter A and number 1 can be summed, counted (or count_not_zero), taken minimum, or other (more elaborate) calculations. My goal is to make a new data frame with these unique combinations and their calculations.

letter  number  value1.sum  value1.count  ..  value2.max  value2.elaborate
A       1       1           1             ..  18          0.56
A       2       26          3             ..  42          0.40
A       3       15          2             ..  40          0.44
B       1       ..          ..            ..  ..          ..
..      ..      ..          ..            ..  ..          ..
C       2       16          2             ..  34          0.44
C       3       8           1             ..  32          0.50

I tried doing this in several ways. For example making a list of matrices with the letter A (in this case), and then using aggregate while selecting on the individual letters. However this gets awfully big.

I tried several combinations of DPLYR package but it was difficult to do different kind of calculations, especially selfmade ones.

You could try `data.table` `dt = data.table(data.frame(letter, number, value1, value2)) newDt <- dt[, value1.sum := sum(value1), by = c('letter', 'number')]` — Kipras Kančys, Nov 29 '16 at 14:17

score 1 · Accepted Answer · edited Dec 02 '16 at 16:47

If you're looking into dplyr, what about using group_by and summarize_each ?

library(dplyr)

df %>% 
  group_by(letter, number) %>% 
  summarize_each(funs(mean, sum, max, min, n()), value1:value2)

 letter number value1_mean value2_mean value1_sum value2_sum value1_max value2_max value1_min value2_min value1_n value2_n
   (chr)  (dbl)       (dbl)       (dbl)      (int)      (dbl)      (int)      (dbl)      (int)      (dbl)    (int)    (int)
1      A      1    1.000000    18.00000          1         18          1         18          1         18        1        1
2      A      2    8.666667    33.33333         26        100         13         42          2         20        3        3
3      A      3    7.500000    31.00000         15         62         12         40          3         22        2        2
4      B      1    5.000000    26.00000         10         52          6         28          4         24        2        2
5      B      2   11.666667    39.33333         35        118         16         48          5         26        3        3
6      B      3   15.000000    46.00000         15         46         15         46         15         46        1        1
7      C      1   13.500000    43.00000         27         86         17         50         10         36        2        2
8      C      2    8.000000    32.00000         16         64          9         34          7         30        2        2
9      C      3    8.000000    32.00000          8         32          8         32          8         32        1        1

You can easily make your own helper functions to output the more elaborate calculations that you're looking for, and then call them inside funs.

score 0 · Answer 2 · answered Nov 29 '16 at 14:25

A crude way to define customized summary operation.

letter = c("A", "A", "A", "B", "B", "B", "C", "C", "C", "C", "A", "A", "A", "B", "B", "B", "C")
number = c(1,2,3,1,2,1,2,3,2,1,2,3,2,2,3,2,1)
value1 = c(1:17)
value2 = seq(18, 50, 2)
df = data.frame(letter, number, value1, value2)


library(dplyr)
myFunc <- function(a) { sum(abs(a)) }
myStats <- c("sum", "length", "max", "min", "myFunc")
dots <- lapply(1:2, function(i) sprintf("~%s(value%d)", myStats, i)) %>% 
  unlist()
dots <- setNames(dots, sub("\\(", ".", gsub("[\\)~]", "", dots))) %>%
  as.list() %>%
  lapply(as.formula)
group_by(df, letter, number) %>%
  summarize_(.dots = dots)

Grouping on two columns and performing multiple calculations

2 Answers2