2020-11-24

How to aggregate multiple variables with different functions in R

Title

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

    2020-11-21

    How to reorder all other columns in a data frame in dplyr

    Title

    Problem

    In dplyr when using select, we want to reorder all other columns at the beginning or end of a data frame without having to type these column names again. In the following example, the data drame flights has 19 columns and we want to order 5 of them at the beginning or end of a data frame.

    library(nycflights13)
    library(dplyr)
    head(flights)
    
    # A tibble: 6 × 19
       year month   day dep_time sched_dep_time dep_delay arr_time sched_arr_time
                                         
    1  2013     1     1      517            515         2      830            819
    2  2013     1     1      533            529         4      850            830
    3  2013     1     1      542            540         2      923            850
    4  2013     1     1      544            545        -1     1004           1022
    5  2013     1     1      554            600        -6      812            837
    6  2013     1     1      554            558        -4      740            728
    # ... with 11 more variables: arr_delay , carrier , flight ,
    #   tailnum , origin , dest , air_time , distance ,
    #   hour , minute , time_hour 
    

    Solution

  • 5 columns at the beginning of the data frame.

    col <- c("carrier", "tailnum", "year", "month", "day")
    select(flights, one_of(col), everything())
    
    # A tibble: 336,776 × 19
       carrier tailnum  year month   day dep_time sched_dep_time dep_delay arr_time
                                      
    1       UA  N14228  2013     1     1      517            515         2      830
    2       UA  N24211  2013     1     1      533            529         4      850
    3       AA  N619AA  2013     1     1      542            540         2      923
    4       B6  N804JB  2013     1     1      544            545        -1     1004
    5       DL  N668DN  2013     1     1      554            600        -6      812
    6       UA  N39463  2013     1     1      554            558        -4      740
    7       B6  N516JB  2013     1     1      555            600        -5      913
    8       EV  N829AS  2013     1     1      557            600        -3      709
    9       B6  N593JB  2013     1     1      557            600        -3      838
    10      AA  N3ALAA  2013     1     1      558            600        -2      753
    # ... with 336,766 more rows, and 10 more variables: sched_arr_time ,
    #   arr_delay , flight , origin , dest , air_time ,
    #   distance , hour , minute , time_hour 
    
  • 5 columns at the end of the data frame.

    select(flights, -one_of(col), one_of(col))
    
    # A tibble: 336,776 × 19
       dep_time sched_dep_time dep_delay arr_time sched_arr_time arr_delay flight
                                              
    1       517            515         2      830            819        11   1545
    2       533            529         4      850            830        20   1714
    3       542            540         2      923            850        33   1141
    4       544            545        -1     1004           1022       -18    725
    5       554            600        -6      812            837       -25    461
    6       554            558        -4      740            728        12   1696
    7       555            600        -5      913            854        19    507
    8       557            600        -3      709            723       -14   5708
    9       557            600        -3      838            846        -8     79
    10      558            600        -2      753            745         8    301
    # ... with 336,766 more rows, and 12 more variables: origin , dest ,
    #   air_time , distance , hour , minute , time_hour ,
    #   carrier , tailnum , year , month , day 
    

  • To add all columns
  • In this case we want to add all the data frame at the beginning or the end again, resulting in duplicates of our 5 columns.

    # 5 columns at the beginning
    bind_cols(select(flights, one_of(col)), flights)
    # 5 columns at the end
    bind_cols(flights, select(flights, one_of(col)))
    

    References

    2020-11-20

    How to change the width of error bars in ggplot2

    Problem

    We want to change the width of the error bars in a boxplot created with ggplot2. We use stat_boxplot(geom ='errorbar') to add error bars.

    library(ggplot2)
    ggplot(iris, aes(factor(Species), Sepal.Width, fill = Species)) +
        geom_boxplot() + 
        stat_boxplot(geom ='errorbar')
    

    Solution

    We use the argument width inside stat_boxplot.

    library(ggplot2)
    ggplot(iris, aes(factor(Species), Sepal.Width, fill = Species)) +
      geom_boxplot() + 
      stat_boxplot(geom ='errorbar', width = 0.5) 

    2020-11-11

    How to create dummy variables in R

    Title

    Problem

    We want to create dummy variables based on other variables in R. In our example based on variables Sex and Embarked.

      PassengerId Survived Pclass    Sex Age SibSp Parch    Fare Embarked Age.NA
    1           1        0      3   male  22     1     0  7.2500        S      0
    2           2        1      1 female  38     1     0 71.2833        C      0
    3           3        1      3 female  26     0     0  7.9250        S      0
    4           4        1      1 female  35     1     0 53.1000        S      0
    5           5        0      3   male  35     0     0  8.0500        S      0
    6           6        0      3   male  NA     0     0  8.4583        Q      1
    
    df <- structure(list(PassengerId = 1:6, Survived = c(0L, 1L, 1L, 1L, 
    0L, 0L), Pclass = c(3L, 1L, 3L, 1L, 3L, 3L), Sex = structure(c(2L, 
    1L, 1L, 1L, 2L, 2L), .Label = c("female", "male"), class = "factor"), 
        Age = c(22L, 38L, 26L, 35L, 35L, NA), SibSp = c(1L, 1L, 0L, 
        1L, 0L, 0L), Parch = c(0L, 0L, 0L, 0L, 0L, 0L), Fare = c(7.25, 
        71.2833, 7.925, 53.1, 8.05, 8.4583), Embarked = structure(c(3L, 
        1L, 3L, 3L, 3L, 2L), .Label = c("C", "Q", "S"), class = "factor"), 
        Age.NA = c(0, 0, 0, 0, 0, 1)), .Names = c("PassengerId", 
    "Survived", "Pclass", "Sex", "Age", "SibSp", "Parch", "Fare", 
    "Embarked", "Age.NA"), row.names = c("1", "2", "3", "4", "5", 
    "6"), class = "data.frame")
    

    Solution

    We use the function dummy.data.frame from the package dummies. By default it will expand dummy variables for character and factor classes.

    library(dummies)
    dummy.data.frame(df)
    

    Results

    The original columns Sex and Embarked are replaced by the dummy variable columns Sexfemale, Sexmale, EmbarkedC, EmbarkedQ and EmbarkedS.

      PassengerId Survived Pclass Sexfemale Sexmale Age SibSp Parch    Fare
    1           1        0      3         0       1  22     1     0  7.2500
    2           2        1      1         1       0  38     1     0 71.2833
    3           3        1      3         1       0  26     0     0  7.9250
    4           4        1      1         1       0  35     1     0 53.1000
    5           5        0      3         0       1  35     0     0  8.0500
    6           6        0      3         0       1  NA     0     0  8.4583
      EmbarkedC EmbarkedQ EmbarkedS Age.NA
    1         0         0         1      0
    2         1         0         0      0
    3         0         0         1      0
    4         0         0         1      0
    5         0         0         1      0
    6         0         1         0      1
    

    References

    Cómo crear variables ficticias en R

    Title

    Problema

    Queremos crear variables ficticias basadas en determinadas variables en R. En nuestro ejemplo, basadas en las variables Sex y Embarked.

      PassengerId Survived Pclass    Sex Age SibSp Parch    Fare Embarked Age.NA
    1           1        0      3   male  22     1     0  7.2500        S      0
    2           2        1      1 female  38     1     0 71.2833        C      0
    3           3        1      3 female  26     0     0  7.9250        S      0
    4           4        1      1 female  35     1     0 53.1000        S      0
    5           5        0      3   male  35     0     0  8.0500        S      0
    6           6        0      3   male  NA     0     0  8.4583        Q      1
    
    df <- structure(list(PassengerId = 1:6, Survived = c(0L, 1L, 1L, 1L, 
    0L, 0L), Pclass = c(3L, 1L, 3L, 1L, 3L, 3L), Sex = structure(c(2L, 
    1L, 1L, 1L, 2L, 2L), .Label = c("female", "male"), class = "factor"), 
        Age = c(22L, 38L, 26L, 35L, 35L, NA), SibSp = c(1L, 1L, 0L, 
        1L, 0L, 0L), Parch = c(0L, 0L, 0L, 0L, 0L, 0L), Fare = c(7.25, 
        71.2833, 7.925, 53.1, 8.05, 8.4583), Embarked = structure(c(3L, 
        1L, 3L, 3L, 3L, 2L), .Label = c("C", "Q", "S"), class = "factor"), 
        Age.NA = c(0, 0, 0, 0, 0, 1)), .Names = c("PassengerId", 
    "Survived", "Pclass", "Sex", "Age", "SibSp", "Parch", "Fare", 
    "Embarked", "Age.NA"), row.names = c("1", "2", "3", "4", "5", 
    "6"), class = "data.frame")
    

    Solución

    Utilizamos la función dummy.data.frame del paquete dummies. Por defecto creará variables ficticias por las variables que seas caracteres o factores.

    library(dummies)
    dummy.data.frame(df)
    

    Resultado

    Las columnas originales Sex y Embarked son reemplazadas por las columnas Sexfemale, Sexmale, EmbarkedC, EmbarkedQ y EmbarkedS.

      PassengerId Survived Pclass Sexfemale Sexmale Age SibSp Parch    Fare
    1           1        0      3         0       1  22     1     0  7.2500
    2           2        1      1         1       0  38     1     0 71.2833
    3           3        1      3         1       0  26     0     0  7.9250
    4           4        1      1         1       0  35     1     0 53.1000
    5           5        0      3         0       1  35     0     0  8.0500
    6           6        0      3         0       1  NA     0     0  8.4583
      EmbarkedC EmbarkedQ EmbarkedS Age.NA
    1         0         0         1      0
    2         1         0         0      0
    3         0         0         1      0
    4         0         0         1      0
    5         0         0         1      0
    6         0         1         0      1
    

    Referencias

    2020-10-20

    Cómo usar la primera fila como nombre de las columnas en R

    Title

    Problema

    Queremos usar la primera fila como nombre de las columnas en R. En nuestro ejemplo, queremos reemplzar los nombres originales de la columna (V1, V2, etc) por los de la primera fila (col1, col2, etc).

        V1   V2   V3   V4   V5
    1 col1 col2 col3 col4 col5
    2 row1    2    4    5   56
    3 row2   74   74    3  534
    4 row3  865  768    8    7
    5 row4   68   86   65   87
    
    df <- read.table(text =  "V1    V2  V3  V4  V5
                            col1    col2    col3    col4 col5
                            row1    2   4   5   56
                            row2    74  74  3   534
                            row3    865 768 8   7
                            row4    68  86  65  87", header = TRUE )
    

    Solución

  • Opción 1
  • colnames(df) <- df[1,]
    df <- df[-1, ] 
    

  • Opción 2
  • names(df) <- lapply(df[1, ], as.character)
    df <- df[-1,] 
    

    Resultado

      col1 col2 col3 col4 col5
    2 row1    2    4    5   56
    3 row2   74   74    3  534
    4 row3  865  768    8    7
    5 row4   68   86   65   87
    
    Si queremos reiniciar los nombres de fila:

    rownames(df) <- NULL
    
      col1 col2 col3 col4 col5
    1 row1    2    4    5   56
    2 row2   74   74    3  534
    3 row3  865  768    8    7
    4 row4   68   86   65   87
    

    References

    How to use the first row as column names in R

    Title

    Problem

    We want to use the first row as column names in R. In our example, we'd like to replace the original column names (V1, V2, etc.) for the first row (col1, col2, etc.).

        V1   V2   V3   V4   V5
    1 col1 col2 col3 col4 col5
    2 row1    2    4    5   56
    3 row2   74   74    3  534
    4 row3  865  768    8    7
    5 row4   68   86   65   87
    
    df <- read.table(text =  "V1    V2  V3  V4  V5
                            col1    col2    col3    col4 col5
                            row1    2   4   5   56
                            row2    74  74  3   534
                            row3    865 768 8   7
                            row4    68  86  65  87", header = TRUE )
    

    Solution

  • Option 1
  • colnames(df) <- df[1,]
    df <- df[-1, ] 
    

  • Option 2
  • names(df) <- lapply(df[1, ], as.character)
    df <- df[-1,] 
    

    Results

      col1 col2 col3 col4 col5
    2 row1    2    4    5   56
    3 row2   74   74    3  534
    4 row3  865  768    8    7
    5 row4   68   86   65   87
    
    If we want to reset row names:

    rownames(df) <- NULL
    
      col1 col2 col3 col4 col5
    1 row1    2    4    5   56
    2 row2   74   74    3  534
    3 row3  865  768    8    7
    4 row4   68   86   65   87
    

    References

    Nube de datos