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