Nube de datos

How to calculate weighted average in Pivot Table in Excel

2022-02-07T17:41:00.002+01:00

Title

Problem

We would like to calculate the weighted average in a pivot table in Excel.

In our example, using the mtcars data set, we want to calculate the mpg average weighted by dips.

Solution

Let's see two alternatives:

Option1: Adding a column.

We add a new columng multiplying the variable (mpg) by the weights (disp)

We create a Calculated Field dividing the new column (mpg*disp) by the weights (disp)

We create a pivot table adding the Calculated Field.

Option 2: Using a DAX functions.

We add the table to the Data Model.

We create a new Measure.

weighted_average:=SUMX( Table1,[mpg] * [disp]) / SUM([disp])

We create a pivot table from Data Model as a data source adding the previous Measure.

Media aritmética ponderada en R

References

mtcars data set

Cómo calcular la media ponderada en tablas dinámicas en Excel

2022-02-04T16:45:00.003+01:00

Title

Problema

Queremos calcular la media ponderada dentro de una tabla dinámica en Excel.

En nuestro ejemplo, usando el conjunto de datos mtcars , queremos calcular la media de mpg ponderada por disp.

Solución

Vamos a ver dos alternativas:

Opción 1: Mediante una columna auxiliar.

Añadimos una columna auxiliar multiplicando la variable (mpg) por los pesos (disp)

Creamos un campo calculado dividiendo la columna auxiliar (mpg*disp) por los pesos (disp)

Creamos la tabla dinámica agrupando por el campo deseado

Opción 2: Con una fórmula DAX.

Agregamos la tabla original al modelo de datos. No es necesario crear una columna auxiliar.

Creamos una nueva medida

weighted_average:=SUMX( Table1,[mpg] * [disp]) / SUM([disp])

Creamos la tabla dinámica tomando como origen Desde Modelo de datos, usando la medida recién creada

Entradas relacionadas

Media aritmética ponderada en R

Referencias

Conjunto de datos mtcars

Ignore zero values in conditional formatting in Excel

2021-03-22T05:00:00.019+01:00

Title

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:

Create a new conditional formatting rule.

Apply no format to cells with value equal to 0.

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

Results

Formato condicional

References

Use conditional formatting to highlight information

Ignorar ceros en el formato condicional en Excel

2021-03-19T05:00:00.002+01:00

Title

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:

Creamos una nueva regla.

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

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

2021-03-18T09:36:00.001+01:00

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

2021-03-16T12:27:00.000+01:00

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

2021-03-12T14:59:00.003+01:00

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

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

2021-03-09T12:19:00.001+01:00

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

2021-03-04T15:24:00.001+01:00

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

How to filter multiple values on a column in R

2021-03-04T15:19:00.004+01:00

Problem

We want to filter multiple values on a column in R. In our example, we want to subset the rows containing the string Tom or Lynn for the column name.

Example

  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")
  )

Solution

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)
# Two alternatives:
sqldf('SELECT *
      FROM df 
      WHERE name = "Tom" OR name = "Lynn"')
sqldf('SELECT *
      FROM df 
      WHERE name IN ("Tom", "Lynn")')

Filter rows containing a certain string in R

References

stackoverflow

How to draw a stratified sample in R

2021-03-01T16:08:00.005+01:00

Problem

We want to draw a stratified sample from a data frame in R.

Solution

Let's look at two examples, with one or several groups.

One group

We extract 3 records from each of the species: setosa, versicolor y virginica.

Base package

set.seed(1)
iris1 <- lapply(split(iris, iris$Species), function(x) x[sample(nrow(x), 3), ])
do.call("rbind", iris1)

              Sepal.Length Sepal.Width Petal.Length Petal.Width    Species
setosa.14              4.3         3.0          1.1         0.1     setosa
setosa.19              5.7         3.8          1.7         0.3     setosa
setosa.28              5.2         3.5          1.5         0.2     setosa
versicolor.96          5.7         3.0          4.2         1.2 versicolor
versicolor.60          5.2         2.7          3.9         1.4 versicolor
versicolor.94          5.0         2.3          3.3         1.0 versicolor
virginica.148          6.5         3.0          5.2         2.0  virginica
virginica.133          6.4         2.8          5.6         2.2  virginica
virginica.131          7.4         2.8          6.1         1.9  virginica

dplyr

library(dplyr)
set.seed(1)
iris %>%
  group_by(Species) %>%
  sample_n(., 3)

 Source: local data frame [9 x 5]
Groups: Species

  Sepal.Length Sepal.Width Petal.Length Petal.Width    Species
1          4.3         3.0          1.1         0.1     setosa
2          5.7         3.8          1.7         0.3     setosa
3          5.2         3.5          1.5         0.2     setosa
4          5.7         3.0          4.2         1.2 versicolor
5          5.2         2.7          3.9         1.4 versicolor
6          5.0         2.3          3.3         1.0 versicolor
7          6.5         3.0          5.2         2.0  virginica
8          6.4         2.8          5.6         2.2  virginica
9          7.4         2.8          6.1         1.9  virginica

Two groups

For each number of cylinders (4, 6 u 8) we will extract 2 records with automatic transmission (am = 0) and 2 with manual transmission (am = 1).

Base package

set.seed(1)
mtcars1 <- lapply(split(mtcars, list(mtcars$cyl, mtcars$am)), function(x) x[sample(nrow(x), 2), ])
do.call("rbind", mtcars1)

                       mpg cyl  disp  hp drat    wt  qsec vs am gear carb
0.4.Merc 240D         24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2
0.4.Toyota Corona     21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1
1.4.Fiat X1-9         27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1
1.4.Lotus Europa      30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2
0.6.Hornet 4 Drive    21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1
0.6.Merc 280          19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4
1.6.Ferrari Dino      19.7   6 145.0 175 3.62 2.770 15.50  0  1    5    6
1.6.Mazda RX4 Wag     21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4
0.8.Chrysler Imperial 14.7   8 440.0 230 3.23 5.345 17.42  0  0    3    4
0.8.Hornet Sportabout 18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2
1.8.Ford Pantera L    15.8   8 351.0 264 4.22 3.170 14.50  0  1    5    4
1.8.Maserati Bora     15.0   8 301.0 335 3.54 3.570 14.60  0  1    5    8

dplyr

set.seed(1)
mtcars %>%
  group_by(cyl, am) %>%
  sample_n(., 2)

Source: local data frame [12 x 11]
Groups: cyl, am

    mpg cyl  disp  hp drat    wt  qsec vs am gear carb
1  24.4   4 146.7  62 3.69 3.190 20.00  1  0    4    2
2  21.5   4 120.1  97 3.70 2.465 20.01  1  0    3    1
3  27.3   4  79.0  66 4.08 1.935 18.90  1  1    4    1
4  30.4   4  95.1 113 3.77 1.513 16.90  1  1    5    2
5  21.4   6 258.0 110 3.08 3.215 19.44  1  0    3    1
6  19.2   6 167.6 123 3.92 3.440 18.30  1  0    4    4
7  19.7   6 145.0 175 3.62 2.770 15.50  0  1    5    6
8  21.0   6 160.0 110 3.90 2.875 17.02  0  1    4    4
9  14.7   8 440.0 230 3.23 5.345 17.42  0  0    3    4
10 18.7   8 360.0 175 3.15 3.440 17.02  0  0    3    2
11 15.8   8 351.0 264 4.22 3.170 14.50  0  1    5    4
12 15.0   8 301.0 335 3.54 3.570 14.60  0  1    5    8

How to select a random sample in R

References

stackoverflow

How to set tick labels to the edges of a continuous ggplot2 legend

2021-02-09T17:31:00.000+01:00

Title

Problem

We want to show the minimum and maximum values in the legend of our plot in ggplot2.

library(ggplot2)
d <- subset(diamonds, price >= 257 & price <= 8888)
ggplot(d, aes(depth, carat, colour = price)) +
  geom_point() +
  scale_colour_gradient(limits = c(257, 8888))

The legend shows values from 2.000 al 8.000 but we do not know the minimum and the maximum values.

Solution

Within scale_colour_gradient, we pass the arguments breaks and labels.

ggplot(d, aes(depth, carat, colour = price)) +
  geom_point() +
  scale_colour_gradient(limits = c(257, 8888), 
                        breaks = c(257, 2000, 4000, 6000, 8000, 8888),
                        labels = c(257, 2000, 4000, 6000, 8000, 8888))

Now we have added to the legend the minimum 257 and the maximum 8.888 values.

Notes

Instead of using the default colour scale colours from light blue to dark blue, we can adjust colour choices with the arguments low and high.

ggplot(d, aes(depth, carat, colour = price)) +
  geom_point() +
  scale_colour_gradient(limits = c(257, 8888), 
                        breaks=c(257, 2000, 4000, 6000, 8000, 8888),
                        labels=c(257, 2000, 4000, 6000, 8000, 8888), low = "red")

References

Cómo crear una pirámide de población con ggplot2

2021-01-29T05:33:00.001+01:00

Title

Problema

Queremos crear una pirámide de población con ggplot2. Nuestro ejemplo es la población española por edad y sexo en 2020.

Ejemplo

# A tibble: 42 x 3
   Age      Gender   Total
           
 1 "0-4 "   male   1018039
 2 "0-4 "   female  963653
 3 "5-9"    male   1196380
 4 "5-9"    female 1129063
 5 "10-14"  male   1297635
 6 "10-14"  female 1225863
 7 "15-19 " male   1232566
 8 "15-19 " female 1156455
 9 "20-24 " male   1207902
10 "20-24 " female 1152765
# ... with 32 more rows

df <- structure(list(Age = c("0-4 ", "0-4 ", "5-9", "5-9", "10-14", 
"10-14", "15-19 ", "15-19 ", "20-24 ", "20-24 ", "25-29 ", "25-29 ", 
"30-34 ", "30-34 ", "35-39 ", "35-39 ", "40-44 ", "40-44 ", "45-49 ", 
"45-49 ", "50-54 ", "50-54 ", "55-59 ", "55-59 ", "60-64 ", "60-64 ", 
"65-69 ", "65-69 ", "70-74 ", "70-74 ", "75-79 ", "75-79 ", "80-84 ", 
"80-84 ", "85-89 ", "85-89 ", "90-94 ", "90-94 ", "95-99 ", "95-99 ", 
"100+", "100+"), Gender = c("male", "female", "male", "female", 
"male", "female", "male", "female", "male", "female", "male", 
"female", "male", "female", "male", "female", "male", "female", 
"male", "female", "male", "female", "male", "female", "male", 
"female", "male", "female", "male", "female", "male", "female", 
"male", "female", "male", "female", "male", "female", "male", 
"female", "male", "female"), Total = c(1018039L, 963653L, 1196380L, 
1129063L, 1297635L, 1225863L, 1232566L, 1156455L, 1207902L, 1152765L, 
1308197L, 1275776L, 1421558L, 1417845L, 1702135L, 1688865L, 2024303L, 
1971909L, 1968659L, 1926866L, 1828015L, 1840434L, 1652558L, 1712299L, 
1410111L, 1502563L, 1153768L, 1270544L, 1020478L, 1191698L, 773823L, 
974046L, 513692L, 759379L, 361702L, 634714L, 133032L, 302885L, 
27305L, 84007L, 3732L, 13576L)), class = "data.frame", row.names = c(NA, 
-42L))
tibble(df)

Solución

Primero necesitamos crear dos columnas. En la primera cambiamos el signo de la población masculina para mostrarla invertida en el gráfico. En la segunda calculamos el porcentaje de población por edad y sexo.

df <- df %>%
  group_by(Gender) %>% 
  mutate(
    Population = ifelse(Gender == "female", Total,-Total),
    Percent = ifelse(Gender == "female", 100 * (Total / sum(Total)),-100 * (Total / sum(Total))))
df$Age <- factor(df$Age, levels=unique(df$Age)) # Para mantener el orden original de vector carácter

Totales

ggplot(df, aes(x = Age, Population, fill = Gender)) + 
  geom_bar(data = filter(df, Gender == "female"), stat = "identity") + 
  geom_bar(data = filter(df, Gender == "male"),  stat = "identity") + 
  scale_y_continuous(breaks = seq(-2000000, 2000000, 500000), labels = comma(abs(seq(-2000000, 2000000, 500000))))+
  coord_flip()

Porcentajes

ggplot(df, aes(x = Age, Percent, fill = Gender)) +
  geom_bar(data = filter(df, Gender == "female"), stat = "identity") +
  geom_bar(data = filter(df, Gender == "male"),  stat = "identity") +
  scale_y_continuous(breaks = seq(-10, 10, 2), labels = comma(abs(seq(-10, 10, 2)))) +
  coord_flip()

Referencias

How to create a population pyramid with ggplot2

2021-01-27T12:30:00.006+01:00

Title

Problem

We want to create a population pyramid with ggplot2. Our example is the Spanish population by age and gender in 2020.

Example

# A tibble: 42 x 3
   Age      Gender   Total
           
 1 "0-4 "   male   1018039
 2 "0-4 "   female  963653
 3 "5-9"    male   1196380
 4 "5-9"    female 1129063
 5 "10-14"  male   1297635
 6 "10-14"  female 1225863
 7 "15-19 " male   1232566
 8 "15-19 " female 1156455
 9 "20-24 " male   1207902
10 "20-24 " female 1152765
# ... with 32 more rows

df <- structure(list(Age = c("0-4 ", "0-4 ", "5-9", "5-9", "10-14", 
"10-14", "15-19 ", "15-19 ", "20-24 ", "20-24 ", "25-29 ", "25-29 ", 
"30-34 ", "30-34 ", "35-39 ", "35-39 ", "40-44 ", "40-44 ", "45-49 ", 
"45-49 ", "50-54 ", "50-54 ", "55-59 ", "55-59 ", "60-64 ", "60-64 ", 
"65-69 ", "65-69 ", "70-74 ", "70-74 ", "75-79 ", "75-79 ", "80-84 ", 
"80-84 ", "85-89 ", "85-89 ", "90-94 ", "90-94 ", "95-99 ", "95-99 ", 
"100+", "100+"), Gender = c("male", "female", "male", "female", 
"male", "female", "male", "female", "male", "female", "male", 
"female", "male", "female", "male", "female", "male", "female", 
"male", "female", "male", "female", "male", "female", "male", 
"female", "male", "female", "male", "female", "male", "female", 
"male", "female", "male", "female", "male", "female", "male", 
"female", "male", "female"), Total = c(1018039L, 963653L, 1196380L, 
1129063L, 1297635L, 1225863L, 1232566L, 1156455L, 1207902L, 1152765L, 
1308197L, 1275776L, 1421558L, 1417845L, 1702135L, 1688865L, 2024303L, 
1971909L, 1968659L, 1926866L, 1828015L, 1840434L, 1652558L, 1712299L, 
1410111L, 1502563L, 1153768L, 1270544L, 1020478L, 1191698L, 773823L, 
974046L, 513692L, 759379L, 361702L, 634714L, 133032L, 302885L, 
27305L, 84007L, 3732L, 13576L)), class = "data.frame", row.names = c(NA, 
-42L))
tibble(df)

Solution

First we need to create two columns. One to convert male population to negative so it is reversed in the plot. Another column with the population percentage by age and sex.

df <- df %>%
  group_by(Gender) %>% 
  mutate(
    Population = ifelse(Gender == "female", Total,-Total),
    Percent = ifelse(Gender == "female", 100 * (Total / sum(Total)),-100 * (Total / sum(Total))))
df$Age <- factor(df$Age, levels=unique(df$Age)) # To keep the original order of the character vector

Absolute numbers

ggplot(df, aes(x = Age, Population, fill = Gender)) + 
  geom_bar(data = filter(df, Gender == "female"), stat = "identity") + 
  geom_bar(data = filter(df, Gender == "male"),  stat = "identity") + 
  scale_y_continuous(breaks = seq(-2000000, 2000000, 500000), labels = comma(abs(seq(-2000000, 2000000, 500000))))+
  coord_flip()

Percentage

ggplot(df, aes(x = Age, Percent, fill = Gender)) +
  geom_bar(data = filter(df, Gender == "female"), stat = "identity") +
  geom_bar(data = filter(df, Gender == "male"),  stat = "identity") +
  scale_y_continuous(breaks = seq(-10, 10, 2), labels = comma(abs(seq(-10, 10, 2)))) +
  coord_flip()

References

How to insert a picture in an Excel comment

2021-01-06T07:47:00.005+01:00

Title

Problem

We want to insert a picture in an Excel comment

Solution

Select cell, right click, and Insert Comment.

Right click on the border of the comment and clic on Format Comment.

On the Colors and Lines tab, click on the drop-down arrow for Color and select Fill Effects.

On the picture tab, click on Select Picture.

Select the picture, insert, and click OK.
Resize the picture by draggin the border of the comment.
Hide if you only want to show the picture when you hover over the cell.

Cómo insertar una imagen en comentario de una celda en Excel

2021-01-04T07:50:00.001+01:00

Title

Problema

Queremos insertar una imagen en el comentario de una celda en Excel

Solución

Seleccionamo una celda, clic con el botón derecho e insertamos un comentario.

Clic con el botón derecho en el borde del comentario y luego en Formato de comentario.

En la pestaña Colores y líneas, clic en el desplegable color y selecciona Efectos de relleno.

En la pestaña imagen, clic en Seleccionar Imagen.

Seleccionamos la imagen, insertar, y aceptar.
Ajustamos el tamaño de la imagen arrastrando los bordes del comentario.
Ocultamos el comentario si solamente queremos que aparezca al pasar el cursor por encima.

Entradas relacionadas

Cómo crear una serie temporal con intervalos de 30 minutos

2021-01-01T05:54:00.001+01:00

Title

Problema

Queremos crear una serie temporal de intervalos de 30 minutos.

Ejemplo

[1] "2017-01-01 00:00:00 UTC"
[2] "2017-01-01 00:30:00 UTC"
[3] "2017-01-01 01:00:00 UTC"
[4] "2017-01-01 01:30:00 UTC"
[5] "2017-01-01 02:00:00 UTC"
[6] "2017-01-01 02:30:00 UTC"

Solución

Usamos la función seq y especificando los minutos en el argumento by, así como el uso horario "UTC". Tecleamos ?seq.POSIXt para obtener más detalles sobre el argumento que podemos especificar como cadena sde texto:

A character string, containing one of "sec", "min", "hour", "day", "DSTday", "week", "month", "quarter" or "year". This can optionally be preceded by a (positive or negative) integer and a space, or followed by "s".

seq(as.POSIXct("2017-01-01", tz = "UTC"),
    as.POSIXct("2017-01-02", tz = "UTC"),
    by = "30 min")

Referencias

stackoverflow

How to create a time series by 30 minute intervals

2020-12-23T05:35:00.008+01:00

Title

Problem

We want to create a time series by 30 minute intervals.

Example

[1] "2017-01-01 00:00:00 UTC"
[2] "2017-01-01 00:30:00 UTC"
[3] "2017-01-01 01:00:00 UTC"
[4] "2017-01-01 01:30:00 UTC"
[5] "2017-01-01 02:00:00 UTC"
[6] "2017-01-01 02:30:00 UTC"

Solution

We use the function seq and specify minutes in the by argument, and pass the time zone "UTC". Type ?seq.POSIXt for more details about the by argument specified as a character string:

A character string, containing one of "sec", "min", "hour", "day", "DSTday", "week", "month", "quarter" or "year". This can optionally be preceded by a (positive or negative) integer and a space, or followed by "s".

seq(as.POSIXct("2017-01-01", tz = "UTC"),
    as.POSIXct("2017-01-02", tz = "UTC"),
    by = "30 min")

References

stackoverflow

How to create a symmetric matrix in R

2020-12-21T05:50:00.000+01:00

Title

Problem

We want to create a symmetric matrix based on the following example.

Example

  V1 V2 V3 V4 V5
1  0  2  3  4  5
2  0  0  6  8 10
3  0  0  0 12 15
4  0  0  0  0 20
5  0  0  0  0  0

df <- structure(list(V1 = c(0L, 0L, 0L, 0L, 0L), V2 = c(2L, 0L, 0L, 
0L, 0L), V3 = c(3L, 6L, 0L, 0L, 0L), V4 = c(4L, 8L, 12L, 0L, 
0L), V5 = c(5L, 10L, 15L, 20L, 0L)), .Names = c("V1", "V2", "V3", 
"V4", "V5"), class = "data.frame", row.names = c("1", "2", "3", 
"4", "5"))

Solution

Adding the transpose of the matrix.

df + t(df)

Function lower.tri

We assign to the lower triangle of the matrix the transpose of the lower triangle.

df[lower.tri(df)] <- t(df)[lower.tri(df)]

  V1 V2 V3 V4 V5
1  0  2  3  4  5
2  2  0  6  8 10
3  3  6  0 12 15
4  4  8 12  0 20
5  5 10 15 20  0

Using upper.tri would not create a symmetrical matrix.

df[lower.tri(df)] <- df[upper.tri(df)]

  V1 V2 V3 V4 V5
1  0  2  3  4  5
2  2  0  6  8 10
3  3  8  0 12 15
4  6 12 10  0 20
5  4  5 15 20  0

References

stackoverflow

How to change the spacing between the legend and the panel in ggplot2

2020-12-18T06:01:00.001+01:00

Title

Problema

We want to increase the spacing between the legend and the panel in ggplot2.

library(ggplot2)
xy <- data.frame(x = 1:10, y = 10:1, type = rep(LETTERS[1:2], each=5))
plot <- ggplot(data = xy) +
        geom_point(aes(x = x, y = y, colo r= type))

Solution

We use the argument legend.box.margin to add 20 points to the left.

plot <- plot + theme(legend.box.margin = margin(0, 0, 0, 20))

References

stackoverflow

Cómo eliminar todos los comentarios en Excel

2020-12-17T11:45:00.002+01:00

Title

Problema

Queremos borrar todos los comentarios en Excel. Veremos tres alternativas, en la última usaremos VBA

Solución

Opción 1

En la pestaña Revisar clic en Mostrar todos los comentarios

Press Ctrl+E para seleccionar todos los comentarios
Clic en Eliminar

Opción 2

Presionamos F5 o Ctrl + I para abrir el cuadro de diálogo Ir a, clic en Especial

Todos los comentarios serán seleccionados

Clic con el botón derecho para abrir el menú contextual y clic en Eliminar comentario

Opción 3 - VBA

Borra todos los comentarios de la hoja activa

Sub Borra_Comentarios_de_la_Hoja()
 Cells.ClearComments
End Sub

Borra todos los comentarios del libro de Excel

Sub Borra_todos_los_Comentarios()
    Dim ws As Worksheet

    For Each ws In Worksheets
        ws.UsedRange.ClearComments
    Next

End Sub

How to remove all comments in Excel

2020-12-14T05:57:00.001+01:00

Title

Problem

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

Solution

Option 1

On the Review tab click on Show all comments

Press Ctrl+A to select all comments
Click on Delete

Option 2

Press F5 or Ctrl + G to open Go To dialog box, click on Special and then OK

All comments will be selected

Right click to open context menu and click on Delete Comment

Option 3 - VBA

Delete all comments from active sheet

Sub Delete_Comments_from_Sheet()
 Cells.ClearComments
End Sub

Delete all comments from the workbook

Sub Delete_All_Comments()
    Dim ws As Worksheet

    For Each ws In Worksheets
        ws.UsedRange.ClearComments
    Next

End Sub

How to modify whiskers of a boxplot in ggplot2?

2020-12-11T05:30:00.002+01:00

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

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

2020-12-10T06:28:00.001+01:00

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

How to reduce space between bars in ggplot2

2020-12-09T06:09:00.001+01:00

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

How to remove whiskers in a boxplot with the R Base Package

Nube de datos

How to calculate weighted average in Pivot Table in Excel

Problem

Solution

Related posts

References

Cómo calcular la media ponderada en tablas dinámicas en Excel

Problema

Solución

Entradas relacionadas

Referencias

Ignore zero values in conditional formatting in Excel

Problem

Solution

Results

Related posts

References

Ignorar ceros en el formato condicional en Excel

Problema

Solución

Resultado

Entradas relacionadas

Cómo representar funciones en R

Problema

Solución

Notas

Entradas relacionadas

Referencias

How to plot functions in R

Problem

Solution

Notes

References

SQL CASE or multiple if else statements in R

Problem

Solution

Results

References

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

Problem

Solution

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

Problema

Ejemplo

Solución

Entradas relacionadas

Referencias

How to filter multiple values on a column in R

Problem

Example

Solution

Related posts

References

How to draw a stratified sample in R

Problem

Solution

Related posts

References

How to set tick labels to the edges of a continuous ggplot2 legend

Problem

Solution

Notes

References

Cómo crear una pirámide de población con ggplot2

Problema

Ejemplo

Solución

Referencias

How to create a population pyramid with ggplot2

Problem

Example

Solution

References

How to insert a picture in an Excel comment

Problem

Solution

Related Posts

Cómo insertar una imagen en comentario de una celda en Excel

Problema

Solución