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

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.

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

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

3. Creamos la tabla dinámica agrupando por el campo deseado

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

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

2. Creamos una nueva medida


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

3. 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

Analizar subgrupos de un data frame con la función aggregate en R

Problema

Deseamos calcular para varios grupos de un data frame diferentes indicadores. En nuestro ejemplo usaremos los datos de TootGrowth.

[,1] len numeric Tooth length
[,2] supp factor Supplement type (VC or OJ).
[,3] dose numeric Dose in milligrams/day

Calcularemos la media y la mediana de la longitud de los dientes (len) para cada uno de los suplementos (supp) de vitaminas (VC o OJ).

Solución

Empleamos la función aggregate.

aggregate(len ~ supp, data = ToothGrowth, 
          FUN = function(x) c(media =mean(x), mediana = median(x)))

Empleamos la estructura de fórmula y ~ x, donde y es la variable numérica de la que queremos el resultado y x es la variable por la que agruparemos.

Resultado

  supp len.media len.mediana
1   OJ  20.66333    22.70000
2   VC  16.96333    16.50000

Más niveles de agrupación

Si deseamos añadir el nivel de agrupación por dosis (dose).

aggregate(len ~ supp + dose, data = ToothGrowth, 
          FUN = function(x) c(media =mean(x), mediana = median(x)))

 supp dose len.media len.mediana
1   OJ  0.5     13.23       12.25
2   VC  0.5      7.98        7.15
3   OJ  1.0     22.70       23.45
4   VC  1.0     16.77       16.50
5   OJ  2.0     26.06       25.95
6   VC  2.0     26.14       25.95

Más variables numéricas

Utilizamos ahora el conjunto de datos iris.

aggregate(cbind(Sepal.Length, Sepal.Width) ~ Species, data = iris, 
          FUN = function(x) c(media =mean(x), mediana = median(x)))

     Species Sepal.Length.media Sepal.Length.mediana Sepal.Width.media
1     setosa              5.006                5.000             3.428
2 versicolor              5.936                5.900             2.770
3  virginica              6.588                6.500             2.974
  Sepal.Width.mediana
1               3.400
2               2.800
3               3.000

Es necesario emplear la función cbind, de lo contrario, nos sumará los resultados de las variables numéricas:

aggregate(Sepal.Length + Sepal.Width~ Species, data = iris, 
          FUN = function(x) c(media = mean(x), mediana = median(x)))

     Species Sepal.Length + Sepal.Width.media Sepal.Length + Sepal.Width.mediana
1     setosa                            8.434                              8.450
2 versicolor                            8.706                              8.600
3  virginica                            9.562                              9.600

Entradas relacionadas

• Subconjunto de estadísticas descriptivas en R

Medidas de tendencia central en histogramas en R: media y mediana

En esta entrada añadiremos a los histogramas dos medidas de tendencia central: la media y la mediana. Construiremos 3 distribuciones usando la distribución beta: asimétrica negativa, simétrica y asimétrica positiva:

Asimétrica negativa o a la izquierda

media < mediana

rbeta(n, 5, 2)

Simétrica

media = mediana

rbeta(n, 5, 5)

Asimétrica positiva o a la derecha

media > mediana

rbeta(n, 2, 5)
parámetro x de legend = "topright"

Código

Empleamos la función de densidad de beta:

dbeta(x, shape1, shape2, ncp = 0, log = FALSE)

Modificaremos los parámetros de la distribución beta a (shape1) y b (shape2) para alterar la forma de la distribución y que sea asimétrica negativa, simétrica o asimétrica positiva.

Asimétrica negativa: shape1 = 5, shape2 =2
Simétrica: shape1 = 5, shape2 =5
Asimétrica positiva: shape1 = 2, shape2 =5

# Ejemplo: asimétrica negativa
set.seed(2014)
vble  <- rbeta(1000000, 5, 2) # Parametros a modificar

# Histograma
hist(vble, 
     prob = TRUE,
     xlim = c(0, 1),
     col = "slategray2",
     border = "white",
     main = "Asimetría negativa", 
     xlab = "", 
     las = 1) 

# Función de densidad
lines(density(vble), # density plot
      lwd = 2, # thickness of line
      col = "darkblue")

# Líneas
  # Media
mean  <- mean(vble)
segments(x0 = mean, y0 = 0, 
         x1 = mean, y1 = dbeta(mean, 5, 2), # Parametros a modificar
         col = "blue", lwd = 2) # lty = 3 dotted line
  # Mediana
median <-  median(vble)
segments(x0 = median, y0 = 0, 
         x1 = median, y1 = dbeta(median, 5, 2), # Parametros a modificar
         col = "red", lwd = 2)

# Leyenda
legend(x = "topleft", # Ubicación de la leyenda
       c("Función de densidad", "Media", "Mediana"),
       col = c("darkblue", "blue", "red"),
       lwd = c(2, 2, 2),
       bty = "n")

Alternativa

Podemos añadir las líneas de la media y mediana mediante la función abline. La diferencias respecto al ejemplo anterior con segments es que la línea cortará a la función de densidad pues es infinita.

set.seed(2014)
vble  <- rnorm(n = 1000000) 
hist(vble, 
     prob = TRUE,
     xlim = c(-3,3), 
     ylim = c(0, .4),
     col = "slategray2",
     border = "white",
     main = "Simétrica", 
     xlab = "", 
     las = 1) 

# Función de densidad
lines(density(vble), # density plot
      lwd = 2, # thickness of line
      col = "darkblue")
# Media
abline(v = mean(vble),
       col = "blue",
       lwd = 2)
# Mediana
abline(v = median(vble),
       col = "red",
       lwd = 2)

Entradas relacionadas

Generar una distribución normal aleatoria en R

Operaciones básicas con la distribución normal en R