How to aggregate multiple variables with different functions in R

Problem

We want to group the following data frame by Branch, using different functions to summarize the results.

  Branch Loan_Amount TAT
1      A         100 2.0
2      A         120 4.0
3      A         300 9.0
4      B         150 1.5
5      B         200 2.0

This is the expected outcome. We want to group by Branch, count the Number_of_loans, and sum Loan_Amount and TAT.

  Branch Number_of_loans Loan_Amount  TAT
1      A               3         520 15.0
2      B               2         350  3.5

Data

df <- structure(list(Branch = structure(c(1L, 1L, 1L, 2L, 2L), .Label = c("A", 
"B"), class = "factor"), Loan_Amount = c(100L, 120L, 300L, 150L, 
200L), TAT = c(2, 4, 9, 1.5, 2)), .Names = c("Branch", "Loan_Amount", 
"TAT"), class = "data.frame", row.names = c(NA, -5L))

Options

Base package

df1 <- aggregate(.~ Branch, df, FUN = "sum")
df2 <- setNames(aggregate(Loan_Amount~Branch, df, length)[2], c("Number_of_loans"))
cbind(df1, df2)

  Branch Loan_Amount  TAT Number_of_loans
1      A         520 15.0               3
2      B         350  3.5               2

dplyr

library(dplyr)
df %>% 
  group_by(Branch) %>% 
  summarise(Number_of_loans = n(),
            Loan_Amount = sum(Loan_Amount),
            TAT = sum(TAT))

Source: local data frame [2 x 4]

  Branch Number_of_loans Loan_Amount   TAT
  (fctr)           (int)       (int) (dbl)
1      A               3         520  15.0
2      B               2         350   3.5

sqldf

library(sqldf)
sqldf("SELECT Branch, 
              COUNT(Loan_Amount) Number_of_loans, 
              SUM(Loan_Amount) Loan_Amount, 
              SUM(TAT) TAT 
      FROM df 
      GROUP BY Branch")

  Branch Number_of_loans Loan_Amount  TAT
1      A               3         520 15.0
2      B               2         350  3.5

References

• stackoverflow

Return the sum of multiple columns with SUMPRODUCT

Problem

For a given lookup value we want to return the sum of multiple columns. In our table, for Year 1 we'd like to return the sum of January, February, April, and December.

Solution

1. We use the function SUMPRODUCT with multiple AND (asterisk: *) and OR (plus: +) criteria.

=SUMPRODUCT((B2:M4)*(A2:A4=B7)*
      ((B1:M1=B8)+(B1:M1=B9)+(B1:M1=B10)+(B1:M1=B11)))

First we pass the range we want to sum (B2:M4), and then add the conditions: 1) Year 1 (A2:A4=B7); and 2) Months to sum: ((B1:M1=B8)+(B1:M1=B9)+(B1:M1=B10)+(B1:M1=B11))). In English, the year must be equal to Year 1 and the months must be January or February or April or December.

Related posts

• Return the sum of multiple columns with VLOOKUP

Return the sum of multiple columns with VLOOKUP

Problem

For a given lookup value we want to return the sum of multiple columns. In our table, for Year 1 we'd like to return the sum of January, February, April, and December.

Solution

1. With VLOOKUP we pass within the third argument col_index_num the array of columns we'd like to sum: {2,3,5,13}, always enclosed in curly brackets. This will return an array of 4 values {98,52,75,60}. :


{=VLOOKUP(B7,A1:M4,{2,3,5,13},0)}

2. To sum the elements of the array, we use the function sum and then press CTRL + SHIFT + ENTER or we can use the function SUMPRODUCT that doesn't require CTRL + SHIFT + ENTER.

{=SUM(VLOOKUP(B7,A1:M4,{2,3,5,13},0))}
=SUMPRODUCT(VLOOKUP(B7,A1:M4,{2,3,5,13},0))

3. An different approach would be to use SUMPRODUCT with multiple OR criteria described in this post.

Related posts

• Return the sum of multiple columns with SUMPRODUCT

Múltiples funciones de agregación con aggregate en R

Problema

Partimos del siguiente data frame:

  Branch Loan_Amount TAT
1      A         100 2.0
2      A         120 4.0
3      A         300 9.0
4      B         150 1.5
5      B         200 2.0

Y deseamos el siguiente resultado. Se agrupar por Branch, se cuenta el Number_of_loans y se suman tanto Loan_Amount como TAT.

  Branch Number_of_loans Loan_Amount  TAT
1      A               3         520 15.0
2      B               2         350  3.5

Datos

df <- structure(list(Branch = structure(c(1L, 1L, 1L, 2L, 2L), .Label = c("A", 
"B"), class = "factor"), Loan_Amount = c(100L, 120L, 300L, 150L, 
200L), TAT = c(2, 4, 9, 1.5, 2)), .Names = c("Branch", "Loan_Amount", 
"TAT"), class = "data.frame", row.names = c(NA, -5L))

Opciones

Paquete base

df1 <- aggregate(.~ Branch, df, FUN = "sum")
df2 <- setNames(aggregate(Loan_Amount~Branch, df, length)[2], c("Number_of_loans"))
cbind(df1, df2)

  Branch Loan_Amount  TAT Number_of_loans
1      A         520 15.0               3
2      B         350  3.5               2

Paquete dplyr

library(dplyr)
df %>% 
  group_by(Branch) %>% 
  summarise(Number_of_loans = n(),
            Loan_Amount = sum(Loan_Amount),
            TAT = sum(TAT))

Source: local data frame [2 x 4]

  Branch Number_of_loans Loan_Amount   TAT
  (fctr)           (int)       (int) (dbl)
1      A               3         520  15.0
2      B               2         350   3.5

Paquete sqldf

library(sqldf)
sqldf("SELECT Branch, 
              COUNT(Loan_Amount) Number_of_loans, 
              SUM(Loan_Amount) Loan_Amount, 
              SUM(TAT) TAT 
      FROM df 
      GROUP BY Branch")

  Branch Number_of_loans Loan_Amount  TAT
1      A               3         520 15.0
2      B               2         350  3.5

Entradas relacionadas

Referencias

• stackoverflow