Descriptive statistics in R

Problem

We'd like to compute descriptive statistics in R.

Solution

The summary function returns a set of summary statistics for the input (a vector, data frame or model).

# For a variable
summary(iris$Sepal.Length)

   Min. 1st Qu.  Median    Mean 3rd Qu.    Max. 
  4.300   5.100   5.800   5.843   6.400   7.900 

# For a data frame
summary(iris)

  Sepal.Length    Sepal.Width     Petal.Length    Petal.Width          Species  
 Min.   :4.300   Min.   :2.000   Min.   :1.000   Min.   :0.100   setosa    :50  
 1st Qu.:5.100   1st Qu.:2.800   1st Qu.:1.600   1st Qu.:0.300   versicolor:50  
 Median :5.800   Median :3.000   Median :4.350   Median :1.300   virginica :50  
 Mean   :5.843   Mean   :3.057   Mean   :3.758   Mean   :1.199                  
 3rd Qu.:6.400   3rd Qu.:3.300   3rd Qu.:5.100   3rd Qu.:1.800                  
 Max.   :7.900   Max.   :4.400   Max.   :6.900   Max.   :2.500  

The fivenum function returns Tukey's five number summary (minimum, lower-hinge, median, upper-hinge, maximum) for the input data.

# For a variable
fivenum(iris$Sepal.Width)

[1] 2.0 2.8 3.0 3.3 4.4

The boxplot.stats function returns the statistics necessary for producing box plots.

boxplot.stats(iris$Sepal.Width)

$stats
[1] 2.2 2.8 3.0 3.3 4.0

$n
[1] 150

$conf
[1] 2.935497 3.064503

$out
[1] 4.4 4.1 4.2 2.0

To return a specific statistic we type boxplot.stats(iris$Sepal.Width) followed by:

$stats - vector with Tukey's five number summary.
$n - the number of non-NA observations.
$conf - the lower and upper extremes of the ‘notch’.
$out- outliers.

The psych package

For a data frame

library(psych)
describe(iris)

             vars   n mean   sd median trimmed  mad
Sepal.Length    1 150 5.84 0.83   5.80    5.81 1.04
Sepal.Width     2 150 3.06 0.44   3.00    3.04 0.44
Petal.Length    3 150 3.76 1.77   4.35    3.76 1.85
Petal.Width     4 150 1.20 0.76   1.30    1.18 1.04
Species*        5 150  NaN   NA     NA     NaN   NA
             min  max range  skew kurtosis   se
Sepal.Length 4.3  7.9   3.6  0.31    -0.61 0.07
Sepal.Width  2.0  4.4   2.4  0.31     0.14 0.04
Petal.Length 1.0  6.9   5.9 -0.27    -1.42 0.14
Petal.Width  0.1  2.5   2.4 -0.10    -1.36 0.06
Species*     Inf -Inf  -Inf    NA       NA   NA

Statistics by group

describeBy(iris, group = iris$Species)

group: setosa
             vars  n mean   sd median trimmed  mad
Sepal.Length    1 50 5.01 0.35    5.0    5.00 0.30
Sepal.Width     2 50 3.43 0.38    3.4    3.42 0.37
Petal.Length    3 50 1.46 0.17    1.5    1.46 0.15
Petal.Width     4 50 0.25 0.11    0.2    0.24 0.00
Species*        5 50  NaN   NA     NA     NaN   NA
             min  max range skew kurtosis   se
Sepal.Length 4.3  5.8   1.5 0.11    -0.45 0.05
Sepal.Width  2.3  4.4   2.1 0.04     0.60 0.05
Petal.Length 1.0  1.9   0.9 0.10     0.65 0.02
Petal.Width  0.1  0.6   0.5 1.18     1.26 0.01
Species*     Inf -Inf  -Inf   NA       NA   NA
--------------------------------------- 
group: versicolor
             vars  n mean   sd median trimmed  mad
Sepal.Length    1 50 5.94 0.52   5.90    5.94 0.52
Sepal.Width     2 50 2.77 0.31   2.80    2.78 0.30
Petal.Length    3 50 4.26 0.47   4.35    4.29 0.52
Petal.Width     4 50 1.33 0.20   1.30    1.32 0.22
Species*        5 50  NaN   NA     NA     NaN   NA
             min  max range  skew kurtosis   se
Sepal.Length 4.9  7.0   2.1  0.10    -0.69 0.07
Sepal.Width  2.0  3.4   1.4 -0.34    -0.55 0.04
Petal.Length 3.0  5.1   2.1 -0.57    -0.19 0.07
Petal.Width  1.0  1.8   0.8 -0.03    -0.59 0.03
Species*     Inf -Inf  -Inf    NA       NA   NA
--------------------------------------- 
group: virginica
             vars  n mean   sd median trimmed  mad
Sepal.Length    1 50 6.59 0.64   6.50    6.57 0.59
Sepal.Width     2 50 2.97 0.32   3.00    2.96 0.30
Petal.Length    3 50 5.55 0.55   5.55    5.51 0.67
Petal.Width     4 50 2.03 0.27   2.00    2.03 0.30
Species*        5 50  NaN   NA     NA     NaN   NA
             min  max range  skew kurtosis   se
Sepal.Length 4.9  7.9   3.0  0.11    -0.20 0.09
Sepal.Width  2.2  3.8   1.6  0.34     0.38 0.05
Petal.Length 4.5  6.9   2.4  0.52    -0.37 0.08
Petal.Width  1.4  2.5   1.1 -0.12    -0.75 0.04
Species*     Inf -Inf  -Inf    NA       NA   NA

La expresión SQL CASE usando una tabla intermedia en Ms Access

Problema

Queremos replicar la expresión SQL CASE, actualmente no disponible, en Ms Access. En esta ocasión usaremos una tabla intermadia para crear los invervalos.

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

En nuestro ejemplo para la columna Num queremos crear intervalos de 0 a >1000 con incrementos de 100. Usamos paréntesis y corchetes para denotar los intervalos semi-abiertos y semi-cerrados.

Solución

1. Creamos una tabla intermedia con los intervalos

2. Unimos ambas tablas basándonos en los límites inferior y superior de los intervalos

SELECT Tabla.Num, intervalos.Intervalos
FROM Tabla INNER JOIN intervalos ON (Tabla.Num > intervalos.inferior) AND (Tabla.Num <= intervalos.superior);

Entradas relacionadas

• La expresión SQL CASE en Ms Access

La expresión SQL CASE en Ms Access

Problema

Queremos replicar la expresión SQL CASE, actualmente no disponible, en Ms Access.

CASE
    WHEN condición1 THEN resultado1
    WHEN condición2 THEN resultado2
    WHEN condiciónN THEN resultadoN
    ELSE resultado
END;

En nuestro ejemplo para la columna Num queremos crear intervalos de 0 a >1000 con incrementos de 100. Usamos paréntesis y corchetes para denotar los intervalos semi-abiertos y semi-cerrados.

Solución

1. SiInm

SiInm:SiInm([Num]<=100, "(0,100]"
,SiInm([Num]<=200,"(100-200]",
SiInm([Num]<=300,"(200-300]",
SiInm([Num]<=400,"(300-400]",
SiInm([Num]<=500,"(400-500]",
SiInm([Num]<=600,"(500-600]",
SiInm([Num]<=700,"(600-700]",            
SiInm([Num]<=800,"(700-800]",              
SiInm([Num]<=900,"(800-900]",
SiInm([Num]<=1000,"(900-1000]",
SiInm([Num]>1000,">1000",""
)))))))))))

Usamos SiInm anidados para crear los intervalos. Necesitamos sere muy cuidadosos para incluir todos los paréntesis.
2. Conmutador

Conmutador:
Conmutador([Num]<=100,"(0,100]"
,[Num]<=200,"(100-200]"
,[Num]<=300,"(200-300]"
,[Num]<=400,"(300-400]"
,[Num]<=500,"(400-500]"
,[Num]<=600,"(500-600]"
,[Num]<=700,"(600-700]"
,[Num]<=800,"(700-800]"
,[Num]<=900,"(800-900]"
,[Num]<=1000,"(900-1000]"
,[Num]>1000,">1000")

Conmutador tiene una sintaxis más clara. Evitamos usar condiciones anidadas mediante pares de expresiones y valores.

Referencias

• SiInm
• Conmutador

Entradas relacionadas

• La expresión SQL CASE usando una tabla intermedia en Ms Access

Equivalente a coalesce en Excel

Problema

Queremos replicar la función coalesce en Excel. En nuestro ejemplo queremos que nos devuelva por fila la primera ocurrencia no en blanco.

Solución

1. INDICE y COINCIDIR con ESBLANCO.

{=INDICE(A2:F2,COINCIDIR(FALSO,ESBLANCO(A2:F2),FALSO))}

Al ser una fórmula matricial, presionamos Ctrl + Mayús + Entrar. Esta fórmula funcionará correctamente mientras no haya cadenas de texto de longitud cero (""). Por ejemplo, para la fila 5 de nuestro ejemplo. De lo contrario devolverá esa cadena de texto en lugar del primer número.
2. INDICE y COINCIDIR con IGUAL

{=INDICE(A2:F2,COINCIDIR(FALSO,IGUAL("",A2:F2),FALSO))}

Al ser una fórmula matricial, presionamos Ctrl + Mayús + Entrar. Esta fórmula resolverá el problema anterior con celdas que contengan cadenas de texto de longitud cero.

Resultados

SQL CASE Statement using an intermediate table in Ms Access

Problem

We need to create a SQL CASE Statement which is not currently supported in Ms Access. This time we will use an intermediate table to create the intervals.

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

In our example for the column Number we want to create intervals of 100 from 0 to >1000. We will use the interval notation: parentheses and/or brackets are used to show whether the endpoints are excluded or included respectively.

Solution

1. Create an intermediate table with the intervals

2. Join both tables based on the lower and upper bounds of the intervals.

SELECT Table.Number, intervals.Interval
FROM [Table] INNER JOIN intervals ON (Table.Number > intervals.lower) AND (Table.Number <= intervals.upper);

Related posts

• SQL CASE Statement in Ms Access

SQL CASE Statement in Ms Access

Problem

We need to create a SQL CASE Statement which is not currently supported in Ms Access

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

In our example for the column Number we want to create intervals of 100 from 0 to >1000. We will use the interval notation: parentheses and/or brackets are used to show whether the endpoints are excluded or included respectively.

Solution

1. IIF

IFF:IIf([Number]<=100, "(0,100]"
,IIf( [Number]<=200,"(100-200]",
IIf([Number]<=300,"(200-300]",
IIf([Number]<=400,"(300-400]",
IIf([Number]<=500,"(400-500]",
IIf([Number]<=600,"(500-600]",
IIf([Number]<=700,"(600-700]",            
IIf([Number]<=800,"(700-800]",              
IIf([Number]<=900,"(800-900]",
IIf([Number]<=1000,"(900-1000]",
IIf([Number]>1000,">1000",""
)))))))))))

We use nested IIF statements to create the intervals. We need to be very careful to include all parentheses.
2. SWITCH

SWTICH:
Switch([Number]<=100,"(0,100]"
,[Number]<=200,"(100-200]"
,[Number]<=300,"(200-300]"
,[Number]<=400,"(300-400]"
,[Number]<=500,"(400-500]"
,[Number]<=600,"(500-600]"
,[Number]<=700,"(600-700]"
,[Number]<=800,"(700-800]"
,[Number]<=900,"(800-900]"
,[Number]<=1000,"(900-1000]"
,[Number]>1000,">1000")

SWITCH has a cleaner syntax. We avoid nesting all conditions using pairs of expressions and values.

References

• IIF
• SWITCH

Related posts

• SQL CASE Statement using an intermediate table in Ms Access

Coalesce cells in Excel

Problem

We'd like to coalesce cells in a row in Excel. In our example we'd like the first non-blank occurrence found for each row.

Solution

1. INDEX and MATCH in conjunction with ISBLANK

{=INDEX(A2:F2,MATCH(FALSE,ISBLANK(A2:F2),FALSE))}

We need to press CTRL + SHIFT + ENTER to enter this array formula. This formula works fine as long as there are not zero-length string characters ("") in the cells. E.g.: row 5 in our example. Otherwise it will return that zero-length string instead of the first number.
2. INDEX and MATCH in conjunction with EXACT

{=INDEX(A2:F2,MATCH(FALSE,EXACT("",A2:F2),FALSE))}

We need to press CTRL + SHIFT + ENTER to enter this array formula. This will solve the issue of cells containing zero-length string characters.

Results