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

Variaciones diarias de acciones o índices en R

Problema

Queremos crear un mapa de calor en forma de calendario con las variaciones diarias de acciones o índices en R.

Solución

1. Ejecutamos el código de Paul Bleicher para crear la función calendarHeat. Editamos el código fuente para modificar la paleta de colores si fuera necesario.
2. Extraemos los datos usando la función getSymbols.
3. Calculamos las variaciones diarias.
4. Creamos el gráfico de las series temporales.

Un par de ejemplos:

1. Dow Jones, emulando la paleta usada por Mike Bostock here. Los días del índice son verde cuando suben y rosas cuando bajan. Los varaciones están calculadas en porcentaje.

library(tidyverse)
library(tidyquant)
# Dow Jones
symb <- getSymbols(Symbols = "^DJI", QQQ = 'yahoo', auto.assign = FALSE)
n <- gsub("^.*\\.", "", names(symb))
symb <- as.data.frame(symb)
colnames(symb) <- n
symb$date <- rownames(symb)
rownames(symb) <- NULL
df <- symb %>%
  mutate(date = as.Date(date),
         pct_vol = round(100 * (Adjusted / lag(Adjusted) - 1), 2)) %>%
  filter(!is.na(pct_vol), date >= '2016-01-01', date <= '2020-12-31') # o filter(!is.na(pct_vol), date >= '2011-01-01', date <= '2015-12-31')
calendarHeat(
  df$date,
  df$pct_vol,
  varname = "Dow Jones Industrial Average",
  ncolors = 50,
  color = "g2p"
)

2. S&P500, usando otra paleta de azul a rojo. Los varaciones están calculadas en porcentaje.

symb <- getSymbols(Symbols = "^GSPC", QQQ = 'yahoo', auto.assign = FALSE)
n <- gsub("^.*\\.", "", names(symb))
symb <- as.data.frame(symb)
colnames(symb) <- n
symb$date <- rownames(symb)
rownames(symb) <- NULL
df <- symb %>%
  mutate(date = as.Date(date),
         pct_vol = round(100 * (Adjusted / lag(Adjusted) - 1), 2)) %>%
  filter(!is.na(pct_vol), date >= '2016-01-01', date <= '2020-12-31')
calendarHeat(
  df$date,
  df$pct_vol,
  varname = "S&P 500",
  ncolors = 50,
  color = "b2r"
)

Resultados

En los siguentes gráficos, las últimas variaciones diarias desencadenadas por la pandemia del coronavirus (COVID-19) hacen que el resto de días aparezcan muy pálidos en comparación.

Entradas relacionadas

• Mapa de calor en forma de calendario en R
• Generar un mapa de calor por días y horas con ggplot2

Daily changes of stocks in R

Problem

We want to create a calendar heatmap with the daily changes of stocks or indexes in R.

Solution

1. We run the calendarHeat function created by Paul Bleicher to display calendar heatmaps. Editing the palettes in source code if needed.
2. We extract the stock or index data using getSymbols.
3. We calculate the daily changes.
4. We plot the time series.

Let's see a couple of examples:

1. Dow Jones, emulating the palette used by Mike Bostock here. Days the index went up are green, and down are pink. The changes are in percentages.

library(tidyverse)
library(tidyquant)
# Dow Jones
symb <- getSymbols(Symbols = "^DJI", QQQ = 'yahoo', auto.assign = FALSE)
n <- gsub("^.*\\.", "", names(symb))
symb <- as.data.frame(symb)
colnames(symb) <- n
symb$date <- rownames(symb)
rownames(symb) <- NULL
df <- symb %>%
  mutate(date = as.Date(date),
         pct_vol = round(100 * (Adjusted / lag(Adjusted) - 1), 2)) %>%
  filter(!is.na(pct_vol), date >= '2016-01-01', date <= '2020-12-31') # or filter(!is.na(pct_vol), date >= '2011-01-01', date <= '2015-12-31')
calendarHeat(
  df$date,
  df$pct_vol,
  varname = "Dow Jones Industrial Average",
  ncolors = 50,
  color = "g2p"
)

2. S&P500, using another palette from blue to red. The changes are in percentages.

symb <- getSymbols(Symbols = "^GSPC", QQQ = 'yahoo', auto.assign = FALSE)
n <- gsub("^.*\\.", "", names(symb))
symb <- as.data.frame(symb)
colnames(symb) <- n
symb$date <- rownames(symb)
rownames(symb) <- NULL
df <- symb %>%
  mutate(date = as.Date(date),
         pct_vol = round(100 * (Adjusted / lag(Adjusted) - 1), 2)) %>%
  filter(!is.na(pct_vol), date >= '2016-01-01', date <= '2020-12-31')
calendarHeat(
  df$date,
  df$pct_vol,
  varname = "S&P 500",
  ncolors = 50,
  color = "b2r"
)

Results

In the next two plots, because of the large daily variations in the last days due to the coronavirus pandemic (COVID-19), the rest of the days are very pale in comparison.

Related posts

• How to create a calendar heatmap in R
• Calendar heatmap by hour and weekday in ggplot2

How to create a calendar heatmap in R

Problem

We want to create a calendar heatmap for a time series in R.

Solution

First we run the calendarHeat function created by Paul Bleicher to display calendar heatmaps. Secondly we create some random time series data. Finally we plot the time series in two palettes.

library(tidyverse)
# Random data
set.seed(2015)
df <-
  data.frame(dates = sample(seq(
    as.Date('2018-01-01'),
    as.Date('2020-12-31'),
    by = "day"
  ), 1000))
df <- df %>% mutate(sessions = floor(runif(nrow(.), 1, 101)) )
# Two calendar with different palettes
calendarHeat(df$dates, df$sessions, varname = "Sessions")
calendarHeat(df$dates, df$sessions, varname = "Sessions", ncolors = 99, color = "r2b" )

Results

Related posts

• Calendar heatmap by hour and weekday in ggplot2

Generate random email addresses in R

Problem

We want to create a vector with random email addresses.

Solution

There are multiple options, I show two options using the packages generator y stringi.

generator

library(generator)
# Example 1: 10 email addresses
set.seed(1977)
r_email_addresses(10)
# Example 2: Added to a data frame
library(dplyr)
set.seed(1977)
mtcars %>% mutate(email = r_email_addresses(n())) %>% head(10)

stringi

A more verbose option concatenating three random text strings separated by @. We gain in flexibility because we can choose the lenght of the strings.

library(stringi)
set.seed(1977)
paste0(stringi::stri_rand_strings(10, 5),
       "@",
       stringi::stri_rand_strings(10, 5)
       ".",
       stringi::stri_rand_strings(n(), 3)
)
set.seed(1977)
mtcars %>% mutate(email = paste0(
  stringi::stri_rand_strings(n(), 5),
  "@",
  stringi::stri_rand_strings(n(), 5),
  ".",
  stringi::stri_rand_strings(n(), 3)
)) %>% head(10)

Results

[1] "rdkvi@kfjaul.myo"      "qpm@bxvqkrtl.fhk"      "aedcq@a.ruc"          
 [4] "xi@l.wkp"              "bhk@obcjyzdi.tda"      "da@qksrfahvup.yel"    
 [7] "ahjq@lg.gmo"           "agcmyp@i.itc"          "rutfv@ponbi.jtm"      
[10] "yeaxlbsko@ntlvkfe.fsg"

    mpg cyl  disp  hp drat    wt  qsec vs am gear carb                     email
1  21.0   6 160.0 110 3.90 2.620 16.46  0  1    4    4          rdkvi@kfjaul.myo
2  21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4          qpm@bxvqkrtl.fhk
3  22.8   4 108.0  93 3.85 2.320 18.61  1  1    4    1               aedcq@a.ruc
4  21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1                  xi@l.wkp
5  18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2          bhk@obcjyzdi.tda
6  18.1   6 225.0 105 2.76 3.460 20.22  1  0    3    1         da@qksrfahvup.yel
7  14.3   8 360.0 245 3.21 3.570 15.84  0  0    3    4               ahjq@lg.gmo
8  24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2              agcmyp@i.itc
9  22.8   4 140.8  95 3.92 3.150 22.90  1  0    4    2           rutfv@ponbi.jtm
10 19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4     yeaxlbsko@ntlvkfe.fsg

How to convert a continuous variable to discrete in R?

Problem

We want to convert continuous variable to discrete in R:

'Create a new qualitative variable, called Elite, by binning the Top10perc variable. We are going to divide universities into two groups based on whether or not the proportion of students coming from the top 10% of their high school classes exceeds 50%'.

library(ISLR)
library(tidyverse)
glimpse(College)

Observations: 777
Variables: 18
$ Private      Yes, Yes, Yes, Yes, Yes, Yes, Yes, Yes, Yes, Yes, Yes, Ye...
$ Apps         1660, 2186, 1428, 417, 193, 587, 353, 1899, 1038, 582, 17...
$ Accept       1232, 1924, 1097, 349, 146, 479, 340, 1720, 839, 498, 142...
$ Enroll       721, 512, 336, 137, 55, 158, 103, 489, 227, 172, 472, 484...
$ Top10perc    23, 16, 22, 60, 16, 38, 17, 37, 30, 21, 37, 44, 38, 44, 2...
$ Top25perc    52, 29, 50, 89, 44, 62, 45, 68, 63, 44, 75, 77, 64, 73, 4...
$ F.Undergrad  2885, 2683, 1036, 510, 249, 678, 416, 1594, 973, 799, 183...
$ P.Undergrad  537, 1227, 99, 63, 869, 41, 230, 32, 306, 78, 110, 44, 63...
$ Outstate     7440, 12280, 11250, 12960, 7560, 13500, 13290, 13868, 155...
$ Room.Board   3300, 6450, 3750, 5450, 4120, 3335, 5720, 4826, 4400, 338...
$ Books        450, 750, 400, 450, 800, 500, 500, 450, 300, 660, 500, 40...
$ Personal     2200, 1500, 1165, 875, 1500, 675, 1500, 850, 500, 1800, 6...
$ PhD          70, 29, 53, 92, 76, 67, 90, 89, 79, 40, 82, 73, 60, 79, 3...
$ Terminal     78, 30, 66, 97, 72, 73, 93, 100, 84, 41, 88, 91, 84, 87, ...
$ S.F.Ratio    18.1, 12.2, 12.9, 7.7, 11.9, 9.4, 11.5, 13.7, 11.3, 11.5,...
$ perc.alumni  12, 16, 30, 37, 2, 11, 26, 37, 23, 15, 31, 41, 21, 32, 26...
$ Expend       7041, 10527, 8735, 19016, 10922, 9727, 8861, 11487, 11644...
$ Grad.Rate    60, 56, 54, 59, 15, 55, 63, 73, 80, 52, 73, 76, 74, 68, 5...

Solution

1. Option 1: form ISLR's book.

Elite = rep("No", nrow(College))
Elite[College$Top10perc > 50] = "Yes"
Elite <- as.factor(Elite)
college <- data.frame(College,  Elite)
summary(college[, c("Top10perc", "Elite")])

There are 78 elite universities.

  Top10perc     Elite    
 Min.   : 1.00   No :699  
 1st Qu.:15.00   Yes: 78  
 Median :23.00            
 Mean   :27.56            
 3rd Qu.:35.00            
 Max.   :96.00    

2. Option 2: ifelse from base package and dplyr

# base 
College$Elite <- factor(ifelse(College$Top10perc > 50, "Yes", "No"))
# dplyr
library(dplyr)
College <-
  college %>%
  mutate(Elite = factor(ifelse(College$Top10perc > 50, "Yes", "No")))

3. Option 3: creating a logical vector.

There are multiple options. I show two examples.

college$Elite <- transform(College, Elite = Top10perc > 50)
College$Elite <- College$Top10perc > 50

References

• An Introduction to Statistical Learning - Page 54.

From 'An Introduction to Statistical Learning' (ISLR), page 54.

Related posts