SQL CASE or multiple if else statements in R

Problem

We would like to create in R a conditional statement equivalent to the CASE statement in SQL.

    idnat     idbp
1  french mainland
2  french   colony
3  french overseas
4 foreign  foreign

df <-structure(list(idnat = structure(c(2L, 2L, 2L, 1L), .Label = c("foreign", 
"french"), class = "factor"), idbp = structure(c(3L, 1L, 4L, 
2L), .Label = c("colony", "foreign", "mainland", "overseas"), class = "factor")), .Names = c("idnat", 
"idbp"), class = "data.frame", row.names = c(NA, -4L))

Solution

There are multiple alternatives. We will provide a couple of options using dplyr and sqldf.

• Option 1: dplyr

library(dplyr)
df %>%
  mutate(idnat2 = case_when(
    idbp == 'mainland' ~ "mainland",
    idbp %in% c("colony", "overseas") ~ "overseas",
    TRUE ~ "foreign"
  ))

• Option 2: sqldf

library(sqldf)
sqldf(
  "SELECT idnat, idbp,
        CASE
          WHEN idbp IN ('colony', 'overseas') THEN 'overseas'
          ELSE idbp
        END AS idnat2
       FROM df"
)
 

Results

    idnat     idbp   idnat2
1  french mainland mainland
2  french   colony overseas
3  french overseas overseas
4 foreign  foreign  foreign

References

• A general vectorised if
• SQL CASE Statement
• stackoverflow

Cómo combinar múltiples condiciones para filtrar un data frame usando "OR”

Problema

Queremos filtrar un data frame basándonos en múltiples condiciones usando el operador "O". En nuestro ejemplo filtraremos aquellas filas del data frame donde la v1 sea menor que 0.5 o donde v2 sea igual a g.

Data frame original

           v1 v2
1  0.26550866  a
2  0.37212390  b
3  0.57285336  c
4  0.90820779  d
5  0.20168193  e
6  0.89838968  f
7  0.94467527  g
8  0.66079779  h
9  0.62911404  i
10 0.06178627  j

Resultado esperado

          v1 v2
1 0.26550866  a
2 0.37212390  b
3 0.20168193  e
4 0.94467527  g
5 0.06178627  j

set.seed(1)
df <- data.frame(v1 = runif(10), v2 = letters[1:10])

Solución

Hay múltiples opciones:

• Funciones del paquete base

subset(df , v1 < 0.5 | v2 == "g")
df[which(df$v1 < 0.5 | df$v2 == "g"), ]

• Operatores [ y [[

df[df[1] < 0.5 | df[2] == "g", ] 
df[df[[1]] < 0.5 | df[[2]] == "g", ] 
df[df["v1"] < 0.5 | df["v2"] == "g", ]

df$name is equivalent to df[["name", exact = FALSE]]

• dplyr

library(dplyr)
filter(df, v1 < 0.5 | v2 == "g")

• sqldf

library(sqldf)
sqldf('SELECT *
      FROM df 
      WHERE v1 < 0.5 OR v2 = "g")

Referencias

• stackoverflow

How to combine multiple conditions to subset a data frame using “OR”?

Problem

We want to subset a data frame based on multiple conditions using "OR". In our example we want to subset the data frame to include all rows where v1 is less than 0.5 or rows where v2 is equal to g.

Original data frame

           v1 v2
1  0.26550866  a
2  0.37212390  b
3  0.57285336  c
4  0.90820779  d
5  0.20168193  e
6  0.89838968  f
7  0.94467527  g
8  0.66079779  h
9  0.62911404  i
10 0.06178627  j

Expected output

          v1 v2
1 0.26550866  a
2 0.37212390  b
3 0.20168193  e
4 0.94467527  g
5 0.06178627  j

set.seed(1)
df <- data.frame(v1 = runif(10), v2 = letters[1:10])

Solution

There are multiple options:

• Base functions

subset(df , v1 < 0.5 | v2 == "g")
df[which(df$v1 < 0.5 | df$v2 == "g"), ]

• Operators [ and [[

df[df[1] < 0.5 | df[2] == "g", ] 
df[df[[1]] < 0.5 | df[[2]] == "g", ] 
df[df["v1"] < 0.5 | df["v2"] == "g", ]

df$name is equivalent to df[["name", exact = FALSE]]

• dplyr

library(dplyr)
filter(df, v1 < 0.5 | v2 == "g")

• sqldf

library(sqldf)
sqldf('SELECT *
      FROM df 
      WHERE v1 < 0.5 OR v2 = "g")

References

• stackoverflow

Error: No Such Column using SQLDF

Problem

When column names include dots, we get the following error: 'No Such Column using SQLDF'.

library(sqldf)
sqldf('SELECT Species, Sepal.Length 
      FROM iris 
      WHERE Species = "virginica" LIMIT 5')

Error: no such column: Sepal.Length

Solution

We only need to write the SQL statement between single quotes, and the column names including dots between double quotes or backticks/backquotes interchangeably.

sqldf('SELECT Species, "Sepal.Length" 
      FROM iris 
      WHERE Species = "virginica" LIMIT 5')
sqldf('SELECT Species, `Sepal.Length` 
      FROM iris 
      WHERE Species = "virginica" LIMIT 5')

    Species Sepal.Length
1 virginica          6.3
2 virginica          5.8
3 virginica          7.1
4 virginica          6.3
5 virginica          6.5

Notes

Previously we had to replace dots for underscores. This is no longer needed:

Staring with RSQLite 1.0.0 and sqldf 0.4-9 dots in column names are no longer translated to underscores.
If you are using an older version of these packages then note that since dot is an SQL operator the RSQLite driver package converts dots to underscores so that SQL statements can reference such columns unquoted.

References

• sqldf
• Error: No Such Column using SQLDF
• sqldf manual

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

How to do cross join in R?

Problem

We want to calculate a cross join, the Cartesian product of rows from tables —or elements of a vector— in the join. In other words, we want to combine each row from the first table with each row from the second table.

Solution

Two vectors

• expand.grid

expand.grid(1:5, 1:5)

• merge

merge(1:5, 1:5)

• Cross join with sqldf

library(sqldf)
df1 <- data.frame(a = 1:5)
df2 <- df1
sqldf("SELECT df1.a, df2.a FROM df1 
      CROSS JOIN df2")

• Results

   x y
1  1 1
2  2 1
3  3 1
4  4 1
5  5 1
6  1 2
7  2 2
8  3 2
9  4 2
10 5 2
11 1 3
12 2 3
13 3 3
14 4 3
15 5 3
16 1 4
17 2 4
18 3 4
19 4 4
20 5 4
21 1 5
22 2 5
23 3 5
24 4 5
25 5 5

More than two vectors

• expand.grid.

a <- c('europe', 'asia')
b <- c('co2', 'temperature')
c <- c('min', 'max')
expand.grid(a, b, c)

    Var1        Var2 Var3
1 europe         co2  min
2   asia         co2  min
3 europe temperature  min
4   asia temperature  min
5 europe         co2  max
6   asia         co2  max
7 europe temperature  max
8   asia temperature  max

References

• Merge Two Data Frames
• Create a Data Frame from All Combinations of Factor Variables
• Generating Cartesian product of strings in R
• Getting all combinations in R, repetition allowed

Convertir de formato largo a ancho con sqldf en R

Problema

Deseamos transformar un data frame de formato largo a ancho. Partimos del siguiente data frame:

        name numbers      value
1  firstName       1 -0.3016990
2  firstName       2  0.4782982
3  firstName       3 -0.3274221
4  firstName       4  0.8950889
5 secondName       1 -1.3476795
6 secondName       2 -0.4671124
7 secondName       3 -1.0883609
8 secondName       4  1.8702156

Y queremos llegar a este otro. Con name como nombres de filas, cuatro columnas basadas en numbers y como valores, values.

                  X1         X2         X3        X4
firstName  -0.301699  0.4782982 -0.3274221 0.8950889
secondName -1.347680 -0.4671124 -1.0883609 1.8702156

Datos originales

dat1 <- structure(list(name = structure(c(1L, 1L, 1L, 1L, 2L, 2L, 2L, 
2L), .Label = c("firstName", "secondName"), class = "factor"), 
    numbers = c(1L, 2L, 3L, 4L, 1L, 2L, 3L, 4L), value = c(-0.301698990300544, 
    0.47829821507312, -0.327422119821659, 0.895088877410118, 
    -1.3476795169412, -0.467112422933039, -1.08836089961649, 
    1.87021564288651)), .Names = c("name", "numbers", "value"
), row.names = c(NA, -8L), class = "data.frame")

Solución

Paquete sqldf

Empleamos la sintaxis de SQL de sqldf para transformar o pivotar de formato largo a ancho. Empleamos MAX(CASE WHEN.

library(sqldf)
sqldf('SELECT name,
      MAX(CASE WHEN numbers = 1 THEN value ELSE NULL END) x1, 
      MAX(CASE WHEN numbers = 2 THEN value ELSE NULL END) x2,
      MAX(CASE WHEN numbers = 3 THEN value ELSE NULL END) x3,
      MAX(CASE WHEN numbers = 4 THEN value ELSE NULL END) x4
      FROM dat1
      GROUP BY name')

                  X1         X2         X3        X4
firstName  -0.301699  0.4782982 -0.3274221 0.8950889
secondName -1.347680 -0.4671124 -1.0883609 1.8702156

Entradas relacionadas

• Convertir de formato largo a ancho con la función unstack en R
• Convertir de formato largo a ancho usando una función de agregación en R
• Tablas de contingencia en R
• Transformar tabla de contingencia en tabla de de frecuencias

Referencias

• stackoveflow