Ignore zero values in conditional formatting in Excel

Problem

We want conditional formatting in Excel to ignore cells containing zero values. In the example below, after applying colour scales in conditional formatting, we can see that Excel applies red formatting to cells with zero values.

Solution

To make conditional formatting ignore zero values, we follow these steps:

1. Create a new conditional formatting rule.

2. Apply no format to cells with value equal to 0.

3. Make sure that this rule is the first to be evaluated, and select the Stop If True check box.

Results

Related posts

Formato condicional

References

Use conditional formatting to highlight information

Ignorar ceros en el formato condicional en Excel

Problema

Queremos que Excel no aplique el formato condicional a las celdas cuyo valor sea 0. En el siguiente ejemplo vemos como, tras aplicar el formato condicional con escalas de colores, los ceros aparecen en rojo.

Solución

Para evitar que aplique un formato condicional a los ceros seguimos los siguientes pasos:

1. Creamos una nueva regla.

2. No aplicamos ningún formato a las celdas cuyo valor sea 0.

3. Nos aseguramos de que es la primera regla evaluada y activamos la casilla Detener si es verdad.

Resultado

Entradas relacionadas

Formato condicional

Cómo representar funciones en R

Problema

Queremos representar funciones en R.

Solución

En nuestro ejemplo queremos representar la siguiente ecuación de segundo grado: 2x² + 20x + 3 = 0

• Opción 1: base package

f <- function(x) {
  x ^ 2 + 20 * x + 3
}
curve(expr = f, from = -100, to = 100)


• Opción 2: ggplot2

library(ggplot2)
ggplot(data.frame(x = c(-100, 100)), aes(x)) +
  stat_function(
    fun = function(x) {
      x ^ 2 + 20 * x + 3
    }
  ) +
  geom_hline(yintercept = 0) +
  geom_vline(xintercept = 0)

 

Notas

Si queremos añadir más funciones:

• Opción 1: Base package

f1 <- function(x) {
  x ^ 2 + 20 * x + 3
}
f2 <- function(x) {
  x ^ 2 + 50 * x + 100
}
curve(expr = f1, from = -100, to = 100)
curve(expr = f2, from = -100, to = 100, col  = 2, add = TRUE)


• Opción 2: ggplot2

ggplot(data.frame(x = c(-100, 100)), aes(x)) +
  stat_function(
    fun = function(x) {
      x ^ 2 + 20 * x + 3
    }
  ) +
  stat_function(
    fun = function(x) {
      x ^ 2 + 50 * x + 100
    },
    colour = "red"
  ) +
  geom_hline(yintercept = 0) +
  geom_vline(xintercept = 0)
 

Entradas relacionadas

• Resolver ecuaciones de segundo grado en R

Referencias

• ggplot2 reference

How to plot functions in R

Problem

We would like to plot functions in R.

Solution

In our example we will plot the following quadratic equation: 2x² + 20x + 3 = 0

• Option 1: base package

f <- function(x) {
  x ^ 2 + 20 * x + 3
}
curve(expr = f, from = -100, to = 100)


• Option 2: ggplot2

library(ggplot2)
ggplot(data.frame(x = c(-100, 100)), aes(x)) +
  stat_function(
    fun = function(x) {
      x ^ 2 + 20 * x + 3
    }
  ) +
  geom_hline(yintercept = 0) +
  geom_vline(xintercept = 0)

 

Notes

If we want to add more functions:

• Option 1: Base package

f1 <- function(x) {
  x ^ 2 + 20 * x + 3
}
f2 <- function(x) {
  x ^ 2 + 50 * x + 100
}
curve(expr = f1, from = -100, to = 100)
curve(expr = f2, from = -100, to = 100, col  = 2, add = TRUE)


• Option 2: ggplot2

ggplot(data.frame(x = c(-100, 100)), aes(x)) +
  stat_function(
    fun = function(x) {
      x ^ 2 + 20 * x + 3
    }
  ) +
  stat_function(
    fun = function(x) {
      x ^ 2 + 50 * x + 100
    },
    colour = "red"
  ) +
  geom_hline(yintercept = 0) +
  geom_vline(xintercept = 0)
 

References

• ggplot2 reference

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

How to highlight specific data points in a scatter plot with ggplot2

Problem

We would like to highlight specific data points in a scatter plot created with ggplot2. In our example, we will use the mpg dataset (Fuel economy data from 1999 and 2008 for 38 popular models of car). We want to highlight in red and a larger point size those data points whose displ (engine displacement) are greater than 5 and hwy (highway miles per gallon) greater than 20.

Solution

Option 1

We add the conditions for both attributes, colour and size, inside geom_point. Then we control manually those using scale_colour_manual and scale_size_manual respectively. Finally, we remove the legend.

ggplot(data = mpg) + 
  geom_point(mapping = aes(x = displ, y = hwy, colour = displ > 5 & hwy > 20, size = displ > 5 & hwy > 20)) + 
  scale_colour_manual(values = c("black", "red")) + 
  scale_size_manual(values =c(1.5, 3))+
  theme(legend.position = "none")

Option 2

We create two layers of data points using geom_point. The first layer include all data points in black (by default but e). The second layer adds the points we would like to highlight in red with a larger point size.

ggplot(data = mpg) + 
  geom_point(mapping = aes(x = displ, y = hwy), colour= "black") +
  geom_point(data = subset(mpg, displ > 5 & hwy > 20), aes(x = displ, y = hwy), colour= "red", size = 3)
 

In ggplot2 the layers are added sequentially. If we change the order of the layers, we would get the following result.

ggplot(data = mpg) + 
   geom_point(data = subset(mpg, displ > 5 & hwy > 20), aes(x = displ, y = hwy), colour= "red", size = 3) +
   geom_point(mapping = aes(x = displ, y = hwy), colour= "black")
 

Cómo filtrar múltiples valores en una columna en R

Problema

Queremos filtrar múltiples valores en una columna en R. En nuestro ejemplo, queremos filtrar las filas que contengan la cadena de texto Tom o Lynn en la columna name.

Ejemplo

  days  name
1   88  Lynn
2   11   Tom
3    2 Chris
4    5  Lisa
5   22  Kyla
6    1   Tom
7  222  Lynn
8    2  Lynn

df <-
  data.frame(
    days = c(88, 11, 2, 5, 22, 1, 222, 2),
    name = c("Lynn", "Tom", "Chris", "Lisa", "Kyla", "Tom", "Lynn", "Lynn")
  ) 

Solución

Base package

df[df$name %in% c("Tom", "Lynn"), ] # or
target <- c("Tom", "Lynn")
df[df$name %in% target, ]

  days name
1   88 Lynn
2   11  Tom
6    1  Tom
7  222 Lynn
8    2 Lynn

dplyr

library(dplyr)
filter(df, name %in% c("Tom", "Lynn")) # or
target <- c("Tom", "Lynn")
filter(df, name %in% target)

data.table

library(data.table)
DT <- data.table(df)
DT[name %in% target]

sqldf

library(sqldf)
# Dos alternativas:
sqldf('SELECT *
      FROM df 
      WHERE name = "Tom" OR name = "Lynn"')
sqldf('SELECT *
      FROM df 
      WHERE name IN ("Tom", "Lynn")')

Entradas relacionadas

• Filtrar filas que contengan una cadena de texto en R

Referencias

• stackoverflow