2020-12-14

How to remove all comments in Excel

Title

Problem

We want to delete all comments in Excel. We will see three alternative, the last one using VBA.

Solution

  • Option 1
    1. On the Review tab click on Show all comments
    2. Press Ctrl+A to select all comments
    3. Click on Delete
  • Option 2
    1. Press F5 or Ctrl + G to open Go To dialog box, click on Special and then OK
    2. All comments will be selected
    3. Right click to open context menu and click on Delete Comment
  • Option 3 - VBA
    1. Delete all comments from active sheet
    2. Sub Delete_Comments_from_Sheet()
       Cells.ClearComments
      End Sub
      
    3. Delete all comments from the workbook
    4. Sub Delete_All_Comments()
          Dim ws As Worksheet
      
          For Each ws In Worksheets
              ws.UsedRange.ClearComments
          Next
      
      End Sub
      

2020-12-11

How to modify whiskers of a boxplot in ggplot2?

Title

Problem

We want to modify the colour of the whiskers in a boxplot using ggplot2. In our example, the default colour (black) to red.

library(ggplot2)
p <- ggplot(mtcars, aes(factor(cyl), mpg, fill = factor(am)))
p + geom_boxplot()

Solution

  • Option 1
  • We plot error bars in red and then superimpose the boxplots in black.

    library(ggplot)
    p + stat_boxplot(
      geom = "errorbar",
      colour = "red",
      width = 0,
      position = position_dodge(0.75)
    ) +
      geom_boxplot(coef = 0, outlier.shape = NA)
    
  • Option 2
  • We superimpose two boxplots on top of each other. The first one with red borders and the secong one without whiskers in black.

    p + geom_boxplot(color="red") + 
      geom_boxplot(aes(ymin=..lower.., ymax=..upper..)) 
    

References

2020-12-10

Cómo reducir el espacio entre barras de un diagramas de barra con ggplot2

Title

Problema

Queremos reducir el espacio entre las barras de un diagrama de barras con ggplot2.

Diagrama de barras vertical

Diagrama de barras horizontal

library(ggthemes)
library(ggplot2)
df <- data.frame(x = c("firm", "vendor"), y = c(50, 20))

# Vertical
ggplot(df, aes(x = x, y = y)) + 
  geom_bar(stat = "identity", width = 0.4) + 
  theme_tufte() + 
  labs(x = "", y = "")

# Horizontal
ggplot(df, aes(x = x, y = y)) + 
  geom_bar(stat = "identity", width = 0.4) + 
  theme_tufte() +  coord_flip() +
  labs(x = "", y = "")

Solución

Tan sólo necesitamos añadir theme(aspect.ratio = xx), y luego necesitamos ajustar el ese ratio para conseguir las distancia deseada. Con coord_flip() podemos voltear el gráfico


# Vertical
ggplot(df, aes(x = x, y = y)) + 
  geom_bar(stat = "identity") + 
  theme_tufte() + theme(aspect.ratio = 2) +
  labs(x = "", y = "")

# Horizontal
ggplot(df, aes(x = x, y = y)) + 
  geom_bar(stat = "identity") + 
  theme_tufte() + theme(aspect.ratio = .2) +
  coord_flip() +
  labs(x = "", y = "")

Resultados

Diagrama de barras vertical

Diagrama de barras horizontal

2020-12-09

How to reduce space between bars in ggplot2

Title

Problem

We want to reduce the space between bars using geom_bar with ggplot2.

Vertical bar plot

Horizontal bar plot

library(ggthemes)
library(ggplot2)
df <- data.frame(x = c("firm", "vendor"), y = c(50, 20))

# Vertical
ggplot(df, aes(x = x, y = y)) + 
  geom_bar(stat = "identity", width = 0.4) + 
  theme_tufte() + 
  labs(x = "", y = "")

# Horizontal
ggplot(df, aes(x = x, y = y)) + 
  geom_bar(stat = "identity", width = 0.4) + 
  theme_tufte() +  coord_flip() +
  labs(x = "", y = "")

Solution

We just need to to introduce theme(aspect.ratio = xx), then we need to play with the ratio to find the desired width. To flip cartesian coordinates we use coord_flip()


# Vertical
ggplot(df, aes(x = x, y = y)) + 
  geom_bar(stat = "identity") + 
  theme_tufte() + theme(aspect.ratio = 2) +
  labs(x = "", y = "")

# Horizontal
ggplot(df, aes(x = x, y = y)) + 
  geom_bar(stat = "identity") + 
  theme_tufte() + theme(aspect.ratio = .2) +
  coord_flip() +
  labs(x = "", y = "")

Results

Vertical bar plot

Horizontal bar plot

Related posts

2020-12-08

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

Title

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

2020-12-07

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

Title

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

2020-12-04

How to remove whiskers in a boxplot in ggplot2

Title

Problem

We want to remove whiskers in a boxplot created with ggplot2.

library(ggplot2)
p <- ggplot(mtcars, aes(factor(cyl), mpg))
p + geom_boxplot()

Solution

We specify coef = 0, overwriting the default value (coef = 1.5).

p <- ggplot(mtcars, aes(factor(cyl), mpg))
p + geom_boxplot(outlier.size = 0, coef = 0)

Related posts

Nube de datos