Checking if a CSV Column Is Normally Distributed With Python

Before running a t-test, drawing control limits at three standard deviations or fitting a model that assumes a bell curve, people are told to check that the data is normally distributed. Then they run a normality test on a column from a CSV with two million rows, get a p-value that rounds to zero, and are not sure what it means. It means very little. This article shows how to check a column properly: with the shape measures that tell you how the distribution differs, a quick percentile check you can do without any test and a Shapiro-Wilk test on a sample, with real output from a 2 million row orders file. It also says when the question is not worth asking. It is a move in more analysis moves for CSV data and follows on from finding outliers in a CSV column.

Load the column

Pull only the column you need out of the file into NumPy, with DuckDB doing the CSV parsing:

import duckdb, numpy as np
from scipy import stats

amount = duckdb.sql("SELECT amount FROM read_csv('orders-2m.csv')").df()["amount"].to_numpy()
print(f"n={len(amount)} mean={amount.mean():.2f} median={np.median(amount):.2f} sd={amount.std():.2f}")
print(f"skew={stats.skew(amount):.2f} excess kurtosis={stats.kurtosis(amount):.2f}")

n=2000000 mean=80.09 median=67.25 sd=56.60
skew=1.41 excess kurtosis=2.97

Two numbers already say a lot. In a normal distribution, the mean and the median coincide, because the curve is symmetric. Here the mean is 80.09 and the median 67.25, a gap of 12.84, or 16 percent of the median. The skew is 1.41: a normal distribution has skew 0, and values beyond about 0.5 in absolute terms are visibly lopsided. A positive skew means a long right tail, which is typical of money: many small orders, a few big ones. The excess kurtosis of 2.97 measures tail weight against a normal curve (0 for normal, positive for heavier tails), and a value near 3 means heavy tails.

Check the column before testing it. Missing values, placeholders and impossible values change every number here: a column with a few -1 markers or a 9999999 fills the tails and spoils the test. Look at the minimum and maximum, and at the percentiles (profiling a new CSV file covers the first pass).

Percentiles against the bell curve

You can judge shape without a test by comparing percentiles with what a normal curve with the same mean and standard deviation would give. For a normal distribution, 68.3 percent of values are within one standard deviation of the mean, 95.4 percent within two. The percentiles of the amount column:

percentile    1     5     25     50     75     95     99
amount     5.95  14.2  38.5  67.25  107.9  189.88  265.91

A normal distribution with mean 80.09 and standard deviation 56.60 would put its 1st percentile at 80.09 minus 2.33 times 56.60, which is about minus 52: a negative order amount. The data's 1st percentile is 5.95 and the minimum is above zero, so the left side is cut off, as it must be for an amount that cannot be negative. The quick count agrees: 73.7 percent of amounts fell within one standard deviation of the mean, against 68.3 percent for a normal distribution. The data is more concentrated near the middle with a long right tail, which is the shape of a skewed distribution and not of a bell. This check takes one line and shows the nature of the departure, which a p-value never does.

Shapiro-Wilk on a sample

The Shapiro-Wilk test is the standard formal check for a single sample. Its statistic W is close to 1 for normal data and falls as the data departs from it, and the p-value is the probability of seeing a W that low if the data were normal. SciPy's implementation is accurate for up to about 5,000 values, so take a random sample:

rng = np.random.default_rng(7)
sample = rng.choice(amount, 5000, replace=False)
w, p = stats.shapiro(sample)
print(f"Shapiro-Wilk on 5000: W={w:.4f} p={p:.2e}")

Shapiro-Wilk on 5000: W=0.8962 p=7.60e-50

W of 0.896 and a p-value near 10 to the minus 50: the amounts are not normal. For comparison, the same test on 5,000 values drawn from a true normal distribution gave W = 0.9997 and p = 0.65, which is what a pass looks like. The contrast between 0.896 and 0.9997 is more informative than the p-values, since W is a measure of how far the shape is from normal and p only says whether the difference is detectable.

D'Agostino's K-squared test (stats.normaltest), which combines skew and kurtosis, handles large samples. On all 2 million values its p-value underflowed to 0.00, and that is the weakness of testing a big file: with enough rows, any real column fails. Real data is never exactly normal, a test with enough power detects the tiniest departure, and a p-value of zero says you have a lot of data, not that the departure matters. For large samples, ignore the p-value and look at the skew, the kurtosis and the plot.

Try a transformation

If a method needs normal data and the column is skewed, a transformation can help. Amounts are often close to log-normal, which means the logarithm is closer to normal:

logs = np.log(amount[amount > 0])
print(f"log amount: skew={stats.skew(logs):.2f} excess kurtosis={stats.kurtosis(logs):.2f}")
w2, p2 = stats.shapiro(rng.choice(logs, 5000, replace=False))
print(f"Shapiro-Wilk on log, 5000: W={w2:.4f} p={p2:.2e}")

log amount: skew=-0.78 excess kurtosis=1.19
Shapiro-Wilk on log, 5000: W=0.9695 p=2.51e-31

The log reduced the skew from 1.41 to -0.78 (now a tail on the left, because the log stretches small values), and W rose from 0.896 to 0.970, a big improvement in shape that still fails the test. The transformation moved the data a long way toward the bell and did not arrive. That is typical, and it means a log transform helps the shape and is not a promise of normality. Other transformations, square root or Box-Cox, may do better, and none of them makes a CSV column normal if it was generated by something that is not.

A plot is better than a number

Draw it. A histogram shows the shape, and a Q-Q plot, stats.probplot(sample, plot=ax), plots the sample's quantiles against those of a normal curve: a straight line means normal, a curve at the ends means heavy tails and a bend through the middle means skew. For a quick look in the terminal, DuckDB's bar() function draws a histogram from a query (making quick charts from a CSV with Python and DuckDB). On the amount column, the histogram falls from 710,000 orders in the 50-wide bucket starting at 0 to 6,000 in the bucket starting at 300, a sharp right-hand slope, which is evident at a glance.

Do you need it to be normal?

Often not. The central limit theorem makes the mean of a large sample close to normal even when the data is not, so a t-test or a confidence interval for a mean works well on millions of skewed order amounts, and the check that matters is the sample size and independence. Methods that assume normality of the data itself, and are sensitive to it, include control charts with three-sigma limits, prediction intervals for single values, outlier rules based on standard deviations and many regression diagnostics. For skewed data, the alternatives are straightforward: work with medians and percentiles, use rank-based tests (Mann-Whitney in place of the t-test), bootstrap the statistic or model the log.

Outlier rules are the common casualty. A rule that flags values more than three standard deviations from the mean assumes a bell curve, and on this column the cutoff is 80.09 plus 3 times 56.60, or 249.89, which flags 1.42 percent of the orders on a column where large orders are normal, whereas a true normal distribution would flag 0.13 percent. A percentile cutoff or the interquartile range works better on skewed data (finding outliers in a CSV column in SQL).

When normality is not the question

If you are describing a column, you do not need it. Report the median and percentiles, and the mean with a caveat. If you are comparing two groups, the question is whether the difference is real and large enough to matter, and for samples in the thousands, a test of the means works regardless of the shape. If you have fewer than about 30 values and the shape matters, no test has enough power to tell, and a plot with the context of what generated the data is the best guide.

Make the decision from the use, not the test. Normality is a property of a model, and a CSV column is a pile of numbers whose shape depends on what produced them. The functions are documented in SciPy's Shapiro-Wilk page and the NIST handbook has a good discussion of checking assumptions.