Summarizing Data with pandas

The working routine of numerical summaries in Python, and the defaults that change their answers. What describe() reports and leaves out; the divisor of the standard deviation in pandas, NumPy, scipy, the statistics module and statsmodels; nine quantile definitions giving interquartile ranges from 21.7 to 31.3 mm Hg on the same ten blood pressures; skewness and kurtosis with and without bias correction; modes, counts and the rules for missing values; correlations by pairwise deletion; grouped and weighted summaries. Worked on NHANES 2017-2018 adults, with links to the Applied Statistics pages for definitions and proofs.

The first summary most analysts compute on a new table is df.describe(). On five columns of the 5,533 adults examined by the National Health and Nutrition Examination Survey (NHANES) in 2017 and 2018, it returns this:

Systolic (mm Hg) BMI (kg/m²) HbA1c (%) CRP (mg/L) Prescriptions
count 5,247 5,434 5,261 5,146 5,524
mean 126.36 29.69 5.84 4.17 2.30
std 20.13 7.44 1.09 8.20 3.08
min 72.67 14.20 3.80 0.11 0
25% 112.00 24.60 5.30 0.88 0
50% 123.33 28.50 5.60 1.94 1
75% 136.67 33.50 6.00 4.48 4
max 238.00 86.20 16.20 182.82 22

Eight numbers per column, and every one of them depends on choices that the table does not show. The counts differ because each column is missing for different people, and each statistic was computed on a different subset of the 5,533. The standard deviations divide by $n - 1$, which NumPy does not do by default. The quartiles come from one of nine definitions in common use. C-reactive protein (CRP), with a mean more than twice its median and a maximum 40 times its 75th percentile, is so skewed that its mean and standard deviation describe almost nothing. None of this is wrong, but all of it has to be known before the numbers are reported.

The definitions of the summaries, their properties and when to prefer each are covered in the Applied Statistics course pages: Types of Numerical Data for which summaries suit which kinds of variable, Measures of Center and Spread for the mean, median, quartiles, variance and standard deviation with their proofs, and Tables and Graphs for Data for frequency tables and histograms. This article is about computing them: which function returns what, the defaults that change the answer, and the habits that keep a first summary honest. The data are the NHANES adults used throughout the Data Exploration topic, built from nine public survey files.

What describe() reports

describe() on a DataFrame summarizes the numeric columns only. On the adults table that means 17 of the 20 columns: the two True/False columns that flag laboratory values below the detection limit and the text column for smoking status are left out without notice. Among the 17 are the respondent number, sex and race, which are codes and whose means are meaningless; Performing Exploratory Data Analysis finds a "mean race" of 3.504 this way and converts such columns to categories before anything else.

Three options change what is reported:

  • include="all" adds the other columns, and for a text or categorical column reports a different set of rows: the count, the number of distinct values, the most frequent value and its frequency. For smoking status: 5,533 values, 3 distinct, most frequent "never" with 3,301.
  • percentiles=[0.05, 0.5, 0.95] replaces the quartiles. For systolic pressure the 5th and 95th percentiles are 100.00 and 164.67, a better description of the range of usual values than the minimum of 72.67 and maximum of 238.
  • describe() on one column, or df.groupby(key)[col].describe(), gives the same rows per group (below).

The count row is the one most often ignored and the one that matters most. Systolic pressure is present for 5,247 adults, CRP for 5,146 and the prescription count for 5,524, so the means in the table are means over different people. A column with a low count needs an explanation before its summary is used; Cleaning Data covers the reasons values go missing and what the common remedies do.

The divisor of the standard deviation

The sample variance divides the sum of squared deviations by $n - 1$, and the population variance by $n$; Confidence Intervals for a Mean shows why $n - 1$ makes the sample variance unbiased. Libraries disagree about which to use by default, and the argument that chooses is called ddof, "delta degrees of freedom": the divisor is $n - \text{ddof}$.

Function Default divisor Systolic SD, 5,247 adults
Series.std(), DataFrame.describe() (pandas) $n - 1$ 20.1267
ndarray.std(), np.std() (NumPy) $n$ 20.1247
statistics.stdev / statistics.pstdev $n - 1$ / $n$ 20.1267 / 20.1247
scipy.stats.describe (variance) $n - 1$ 20.1267
scipy.stats.zscore $n$
scipy.stats.sem $n - 1$
DescrStatsW(x).std (statsmodels) $n$

With 5,247 values the two divisors differ in the fifth significant figure. With five, they do not: the first five systolic pressures in the table (202.00, 111.33, 141.00, 118.67 and 101.33) have a standard deviation of 40.27 in pandas and 36.01 in NumPy, a ratio of $\sqrt{5/4} = 1.118$. The same difference passes into anything built on the standard deviation. scipy.stats.zscore divides by the $n$-divisor standard deviation, so the first of those five values has a z-score of 1.864 from scipy and 1.667 computed as (s - s.mean()) / s.std() in pandas. statsmodels' DescrStatsW also reports the $n$-divisor standard deviation as its std attribute, although its confidence intervals for the mean use the correct standard error (Inferential Statistics in Python shows this). The rule is to pass ddof explicitly whenever a standard deviation leaves the function that computed it.

Quantiles: nine definitions

A quantile of a sample is not uniquely defined. The $q$ quantile should have a fraction $q$ of the data below it, but with $n$ values there is usually no single point that does this exactly, and the definitions in use differ in how they choose or interpolate between neighboring values. Measures of Center and Spread works through the three most common definitions by hand. NumPy implements the nine of Hyndman and Fan's (1996) survey, numbered in its documentation as there, alongside four older options (lower, higher, midpoint and nearest) kept for backward compatibility. On ten systolic pressures drawn at random from the adults (99.33, 124.67, 130.00, 145.33, 147.33, 150.67, 154.00, 156.00, 168.00 and 184.00 mm Hg):

NumPy method Hyndman–Fan type Q1 Q3 IQR
inverted_cdf 1 130.000 156.000 26.000
averaged_inverted_cdf 2 130.000 156.000 26.000
closest_observation 3 124.667 156.000 31.333
interpolated_inverted_cdf 4 127.333 155.000 27.667
hazen 5 130.000 156.000 26.000
weibull 6 128.667 159.000 30.333
linear (default) 7 133.833 155.500 21.667
median_unbiased 8 129.556 157.000 27.444
normal_unbiased 9 129.667 156.750 27.083

The interquartile range runs from 21.7 to 31.3 mm Hg depending on the definition, and a box plot's fences at 1.5 IQR move with it. Which definition a program uses is a matter of its defaults:

  • NumPy's np.quantile and np.percentile, and pandas' Series.quantile and describe(), use type 7 (linear), as do R and Excel. pandas offers only linear and the four simpler options lower, higher, midpoint and nearest through its interpolation argument; for the other types, use NumPy on the values.
  • Python's statistics.quantiles uses type 6 by default (its "exclusive" method), the definition of the NIST/SEMATECH e-Handbook, which gives 128.667 and 159.0 here; method="inclusive" gives type 7.
First (circles) and third (squares) quartiles of ten systolic pressures under NumPy's nine methods; dotted lines mark the data values. Types 1 and 3 always return a data value and type 2 a data value or the mean of two; the others interpolate. The default, type 7 (orange), gives the narrowest interquartile range, 21.7 mm Hg, and type 3 the widest, 31.3.
First (circles) and third (squares) quartiles of ten systolic pressures under NumPy's nine methods; dotted lines mark the data values. Types 1 and 3 always return a data value and type 2 a data value or the mean of two; the others interpolate. The default, type 7 (orange), gives the narrowest interquartile range, 21.7 mm Hg, and type 3 the widest, 31.3.

The differences shrink as $n$ grows, because neighboring order statistics move closer together. On all 5,247 systolic pressures the nine methods give exactly the same quartiles, 112.00 and 136.67, because these values are averages of a few even-numbered readings and are heavily tied. A quartile reported to more precision than the gap between neighboring data values, or compared across programs on a small sample, needs its definition stated.

Shape: skewness and kurtosis

The sample skewness measures asymmetry by the standardized third central moment. scipy's stats.skew computes by default the Fisher–Pearson coefficient

$$g_1 = \frac{m_3}{m_2^{3/2}}, \qquad m_r = \frac{1}{n}\sum_{i=1}^{n}(x_i - \bar{x})^r,$$

and with bias=False the adjusted coefficient $G_1 = \frac{\sqrt{n(n-1)}}{n-2}\, g_1$ (scipy documentation, scipy.stats.skew). pandas' Series.skew() computes $G_1$, which its documentation calls "unbiased skew". Kurtosis has the same two forms, and a third choice: whether to subtract 3, the kurtosis of the normal distribution. pandas' kurt() returns the bias-adjusted excess kurtosis ("Fisher's definition", normal = 0); scipy's stats.kurtosis returns the unadjusted excess kurtosis by default, and with fisher=False the kurtosis itself.

Systolic, pandas Systolic, scipy default CRP, pandas CRP, scipy default
skewness 1.0381 1.0378 7.8947 7.8924
excess kurtosis 1.6825 1.6797 97.5612 97.4652

With 5,000 values the adjustment hardly matters. With 10 it does, and neither form is unbiased in fact: the adjustment is exact only for normal data. Drawing 2,000 samples of 10 systolic pressures for each of 30 seeds, the average skewness was 0.5235 by scipy's default (median over seeds; IQR 0.5193 to 0.5322) and 0.6208 by pandas' formula (IQR 0.6158 to 0.6311), against 1.0378 for all 5,247 values. With samples of 30 the averages were 0.8200 and 0.8638. Small samples from a right-skewed population mostly miss the long tail, so both estimates are far too low; a skewness from a small sample is a rough indication at best. The relation between skewness and the order of the mean and median, which holds less reliably than is often taught, is examined in Measures of Center and Spread, and the CRP column's shape on a logarithmic scale in Visualizing Data with One Feature.

Modes, distinct values and frequencies

Series.mode() returns a Series, not a number, because several values can tie for most frequent. The systolic mode is 125.33 mm Hg, shared by 92 adults among 237 distinct values: the column takes few values because each is the mean of up to three readings recorded in even numbers. The mode of age is 80, with 382 adults, but only because everyone aged 80 or over is recorded as 80. For measurements, a mode is usually a fact about the recording process rather than about the people; for categories it is the natural summary of center.

For a categorical column, value_counts() is the first summary. normalize=True turns counts into shares (never smoked 59.66%, former smokers 22.77%, current smokers 17.57%), and dropna=False counts missing values as a category, which should be the habit until the column is known to be complete. Text columns read in pandas 3 have a dedicated string type rather than the generic object type of pandas 2, so code that selects columns with select_dtypes("object") relies on a compatibility rule: pandas 3.1 still includes the new string columns but warns that this is deprecated and asks for "str" to be named explicitly.

Missing values

Every pandas reduction skips missing values by default (skipna=True), which is convenient and hides how much data each statistic rests on. A few rules follow from that default and are worth knowing exactly:

  • count() counts non-missing values and len() or size counts rows: 5,247 against 5,533 for systolic pressure.
  • Series.sum() of nothing is 0, not missing: the sum over an empty selection, or over a column that is entirely missing, returns 0.0. sum(min_count=1) returns NaN instead when there are no values to add. The mean of nothing is NaN.
  • mean(skipna=False) returns NaN if any value is missing, as NumPy's np.mean does on an array containing NaN; np.nanmean skips them. Code that moves between NumPy and pandas silently changes this rule.
  • A True/False column has no way to record "unknown" in its default type. The flag for CRP below the detection limit is False for the 387 adults with no CRP measurement at all, so its mean over all rows, 0.0022, slightly understates the share among adults who were measured, 0.0023. Pandas' nullable boolean type keeps the missing values as missing.

The general advice is to compute counts alongside every statistic, and to decide deliberately whether missing values should be skipped, counted or filled.

Correlation and pairwise deletion

DataFrame.corr() computes each pairwise correlation from the rows where both variables are present, so different cells of the matrix use different rows. For four laboratory and blood pressure variables:

Rows used Systolic HbA1c Cadmium CRP
Systolic 5,247 5,007 5,010 4,898
HbA1c 5,261 5,257 5,142
Cadmium 5,264 5,141
CRP 5,146

Only 4,892 adults have all four. The pairwise correlation of systolic pressure and HbA1c is 0.2091; on the complete cases it is 0.2106. The differences are small here, because the missing values are few and scattered. With more missing data, a correlation matrix computed pairwise can be internally inconsistent (in extreme cases not even a valid correlation matrix, with negative eigenvalues), and a model fitted later on the complete cases will not reproduce it. corr(min_periods=5000) returns NaN for any pair with fewer than 5,000 shared rows, which here blanks the systolic–CRP cell. corr() on a table with a text column raises an error in pandas 2 and later; numeric_only=True drops such columns.

The default is Pearson's coefficient, a measure of linear association that a few extreme values can dominate. Spearman's coefficient, the Pearson correlation of the ranks, is less sensitive: systolic pressure and HbA1c correlate at 0.2091 by Pearson's measure and 0.3407 by Spearman's, and HbA1c and CRP at 0.1345 and 0.2272. A large gap between the two is a reason to plot the pair, as Visualizing Data with Multiple Features does on the same data.

Grouped summaries

Every summary above can be computed group by group with groupby, which Exploring Data with Groups and Pivot Tables covers in full. Two forms are worth having at hand. groupby(key)[col].describe() repeats the eight-row summary per group:

count mean std min 25% 50% 75% max
Men 2,547 127.41 18.37 72.67 114.67 124.67 136.67 234.0
Women 2,700 125.37 21.61 84.00 109.33 121.33 136.67 238.0

Women's systolic pressures are more spread out than men's (standard deviations 21.61 and 18.37) with a lower median and the same third quartile, a difference in shape that the means alone would miss. Named aggregation builds a custom table with readable column names, mixing counts and statistics of several columns:

adults.groupby("smoking").agg(n=("seqn", "size"), cd_n=("cadmium", "count"),
                              cd_median=("cadmium", "median"),
                              cd_q90=("cadmium", lambda v: v.quantile(0.9)))
Smoking Adults Cadmium measured Median (µg/L) Mean 90th percentile
Current 972 918 0.930 1.163 2.20
Former 1,260 1,218 0.315 0.388 0.73
Never 3,301 3,128 0.240 0.314 0.59

The table reports the size of each group, the count behind each statistic and three summaries of a skewed variable, which is the form in which grouped results should usually be shown. The finding itself, blood cadmium almost four times as high in current smokers as in people who never smoked, is the conclusion of Performing Exploratory Data Analysis.

Weighted summaries

When rows stand for different numbers of people, as in a survey with sampling weights, summaries must be weighted. Data Collection and Sampling explains NHANES's weights; for the 5,182 adults analyzed there, the unweighted mean systolic pressure is 126.48 mm Hg and the weighted mean, np.average(sbp, weights=w), is 123.47: the sample has more older adults than the population it represents.

A weighted quantile is the value at which the cumulative share of the weights first reaches $q$. Since NumPy 2.0, np.quantile and np.percentile accept weights, but only with method="inverted_cdf" (type 1); with the default method they raise an error. The weighted median systolic pressure is 121.00 against 123.33 unweighted, and the weighted quartiles 110.67 and 132.67 against 112.00 and 136.67. pandas has no weighted quantile or weighted standard deviation; the weighted mean is (df.x * df.w).sum() / df.w.sum() and the weighted variance $\sum_k w_k (x_k - \bar{x}_w)^2 / \sum_k w_k$, here a standard deviation of 17.96 against 20.07 unweighted. Each such number describes the population; its standard error needs the survey design, not a formula for independent observations.

A short checklist

  1. Know the type of each column before summarizing it. Codes are categories, not numbers (Types of Numerical Data).
  2. Report the count behind every statistic, and look at why counts differ between columns.
  3. Pass ddof explicitly, and state the quantile method when $n$ is small or results will be compared across programs.
  4. For skewed variables, prefer the median and quartiles, or summarize on a log scale, and treat a skewness from a small sample as rough.
  5. Use dropna=False in frequency tables until a column is known to be complete, and watch for True/False columns that store "unknown" as False.
  6. Remember that pairwise statistics use different rows; check the complete-case count.
  7. If rows carry weights, weight the summaries, and do not use their simple-random-sample standard errors.

Corrections to the course notes

The unit's notes give the standard definitions and pandas methods correctly; a few points are stated more precisely here. They describe the standard deviation as how far "on average" an observation lies from the mean; it is the root mean square of the deviations, which is at least as large as their average absolute size (for the systolic pressures, 20.12 with divisor $n$, against a mean absolute deviation of 15.47 mm Hg). They note that pandas' kurtosis() uses Fisher's definition; it is also bias-adjusted, so it differs from scipy's default, as the table above shows. Their rule that a right-skewed distribution has its mean above its median holds for these data but not universally, as Measures of Center and Spread shows with exceptions.

In Python

import numpy as np
import pandas as pd
from scipy import stats

adults.describe(include="all", percentiles=[0.05, 0.5, 0.95]).T
adults[["sbp", "crp"]].agg(["count", "mean", "median", "std", "skew", "kurt"])

x = adults.sbp.dropna().to_numpy()
x.std(ddof=1)                                         # match pandas' default
np.quantile(x, [0.25, 0.75], method="weibull")        # type 6, the NIST definition
stats.skew(x, bias=False), stats.kurtosis(x, bias=False)   # match pandas
stats.zscore(x, ddof=1)                               # z-scores with the sample SD

adults.smoking.value_counts(dropna=False, normalize=True)
adults.sbp.sum(min_count=1)                           # NaN rather than 0 if all values are missing
adults[["sbp", "hba1c", "cadmium", "crp"]].corr(method="spearman", min_periods=5000)

np.average(sbp, weights=w)                            # weighted mean
np.quantile(sbp, 0.5, weights=w, method="inverted_cdf")   # weighted median, NumPy 2.0 or later

The numbers in this article are computed by tools/experiments/data-science/descriptive-statistics.py in the site's repository, from the tables built by exploredata.py and psdata.py beside it, which download the NHANES files and check their SHA-256 hashes; the script gives identical results with pandas 2.2.3 and NumPy 2.2.5 and with pandas 3.1.1 and NumPy 2.5.4. The figure is drawn by tools/figures/descriptive-statistics.py.

References