2020-12-01

Mostrar las áreas de densidad deseadas con stat_density_2d en ggplot2

Title

Problema

Tenemos el siguiente diagrama de dispersión para dos variables categóricas.

Cuando creamos un gráfico de densidad en 2D, obtenemos varios contornos de densidad. Queremos controlar precisamente el número de contornos.

library(ggplot2)
set.seed(123)
plot_data <-
  data.frame(
    X = c(rnorm(300, 3, 2.5), rnorm(150, 7, 2)),
    Y = c(rnorm(300, 6, 2.5), rnorm(150, 2, 2)),
    Label = c(rep('A', 300), rep('B', 150))
  )

ggplot(plot_data, aes(X, Y, colour = Label)) + geom_point()
ggplot(plot_data, aes(X, Y)) +
  stat_density_2d(geom = "polygon", aes(alpha = ..level.., fill = Label))

Solución

  • Opción 1
  • Añadiendo stat_density_2d con el argumento bins (número de contornos) evitamos una sobrecagarda de información, controlamos y centramos la atención en un número concreto de contornos de densidad.

    ggplot(plot_data, aes(X, Y, group = Label)) +
      stat_density_2d(geom = "polygon",
                      aes(alpha = ..level.., fill = Label),
                      bins = 4) 
    
  • Opción 2
  • Asignando manualmente los colores, especificando NA para aquellos niveles que no queremos mostrar. La principal desventaja es que necesitamos saber por adelantado el número de valores que scale_fill_manual necesita. En nuestro ejemplo, necesitamos especificar 7 valores en la escala manual.

    ggplot(plot_data, aes(X, Y, group = Label)) +
      stat_density_2d(geom = "polygon", aes(fill = as.factor(..level..))) +
      scale_fill_manual(values = c(NA, NA, NA, "#BDD7E7", "#6BAED6", "#3182BD", "#08519C"))
    

Referencias

Show only high density areas with stat_density_2d with ggplot2

Title

Problem

We have the following scatterplot for two categorical variables.

When we create a 2D-density plot, we obtain overlapping densities. We want to control the number of contour bins.

library(ggplot2)
set.seed(123)
plot_data <-
  data.frame(
    X = c(rnorm(300, 3, 2.5), rnorm(150, 7, 2)),
    Y = c(rnorm(300, 6, 2.5), rnorm(150, 2, 2)),
    Label = c(rep('A', 300), rep('B', 150))
  )

ggplot(plot_data, aes(X, Y, colour = Label)) + geom_point()
ggplot(plot_data, aes(X, Y)) +
  stat_density_2d(geom = "polygon", aes(alpha = ..level.., fill = Label))

Solution

  • Option 1
  • By adding to stat_density_2d the argument bins (number of contour bins) we definitely avoid overplotting, control and draw the attention to a number of density areas in a very economical fashion.

    ggplot(plot_data, aes(X, Y, group = Label)) +
      stat_density_2d(geom = "polygon",
                      aes(alpha = ..level.., fill = Label),
                      bins = 4) 
    
  • Option 2
  • Assigning manually the colours, NA for those levels we do not want to plot. The main disadvantage is that we should know the number of values needed by scale_fill_manual in advance. In our example, we need to pass 7 values in manual scale.

    ggplot(plot_data, aes(X, Y, group = Label)) +
      stat_density_2d(geom = "polygon", aes(fill = as.factor(..level..))) +
      scale_fill_manual(values = c(NA, NA, NA, "#BDD7E7", "#6BAED6", "#3182BD", "#08519C"))
    

References

2020-11-28

How to get the Windows user name with VBA

Problem

We need to get the Windows user name using VBA. For example, in Excel we can display different info depending on the user opening the Excel file.

Solución

  1. Open the Visual Basic Editor: press Alt+F11.
  2. Copy the following subroutine into a module.
    • Sub user()
      MsgBox Environ("UserName")
      End Sub
      
  3. Run the subroutines: press F5.
  4. Excel will display a pop up message showing the Windows user name.
We can also type the following text in the Immediate window and press Enter.

?Environ("Username")
Or we can capture and print the user name in a cell to customize the information displayed in that sheet or workbook.

Sub user_cell()
Sheets("Hoja1").Range("A1") = Environ("UserName")
End Sub

References

2020-11-27

Error: No Such Column using SQLDF

Title

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

2020-11-26

Cómo ocultar el contenido de una celda mediante formato condicional en Excel

Problema

Queremos ocultar el contenido de una celda en función del contenido de otra celda.

Solución

En A2 aplicamos el formato condicional que oculta el contenido de la celda si es igual a B2.

En Formato condicional creamos la siguiente regla: =$A$2=$B$2. Luego hacemos clic en Formato...
  1. En el menú Formato de celdas, hacemos clic en la pestaña Número.
  2. Debajo de Categoría, clic en Personalizada.
  3. En la celda Tipo, tecleamos ;;; (es decir, tres puntos y coma seguidos), y clic en aceptar

Notas

¿Por qué necesitamos añadir tres puntos y comas en el formato de número personalizado? El formato personalizado está compuesto por 4 secciones separadas de tres puntos y comas, de manera que si solamente tecleamos los separadores (los tres puntos y comas), la celda aparecerá vacía.

Referencias

2020-11-24

How to select all columns in dplyr

Title

Problem

We want to select all variables in a data frame using dplyr.

Solution

select(iris, everything())
# Using the pipe operator %>%
iris %>% select(everything())

Notes

The function select( ) subsets columns based on their names and other features. We can use the function everything( ) a selection helper to select all variables.

Results

Source: local data frame [150 x 5]

   Sepal.Length Sepal.Width Petal.Length Petal.Width Species
1           5.1         3.5          1.4         0.2  setosa
2           4.9         3.0          1.4         0.2  setosa
3           4.7         3.2          1.3         0.2  setosa
4           4.6         3.1          1.5         0.2  setosa
5           5.0         3.6          1.4         0.2  setosa
6           5.4         3.9          1.7         0.4  setosa
7           4.6         3.4          1.4         0.3  setosa
8           5.0         3.4          1.5         0.2  setosa
9           4.4         2.9          1.4         0.2  setosa
10          4.9         3.1          1.5         0.1  setosa
..          ...         ...          ...         ...     ...

References

How to aggregate multiple variables with different functions in R

Title

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

    Nube de datos