Lesson 4 of 5 · 7 min · Shape and relationships
Percentiles, quartiles and box plots
What you will learn
- What a percentile and a quartile are
- Calculate them in Excel, SQL, pandas and Python
- Compute the IQR and the outlier fences
- Read a box plot
- Use percentiles for targets such as p90 delivery time
In simple words
A percentile tells you where a value stands compared with the rest. If your exam score is at the 90th percentile, 90 out of every 100 students scored below you. Quartiles cut the sorted data into four equal groups. Percentiles are especially useful for skewed data, where the average misleads, and for promises like "9 out of 10 orders arrive within 6 days".
Words to know
- Percentile: the value below which a given share of the data falls.
- Quartile: the 25th, 50th and 75th percentiles (Q1, Q2, Q3).
- IQR: the interquartile range, Q3 minus Q1.
- Whisker: the line on a box plot showing the range of typical values.
What a percentile means
The p-th percentile is the value that is greater than or equal to p percent of the data. The median is the 50th percentile. The 25th, 50th and 75th are the quartiles: Q1, Q2 (the median) and Q3. Together they split sorted data into four groups, each holding a quarter of the values.
Using the delivery data, sorted: 2, 3, 3, 3, 4, 4, 5, 6, 15 (nine values):
- Q1 = 3, the median = 4, Q3 = 5.
- IQR = Q3 - Q1 = 2.
- The 90th percentile is 7.8 days: nine of ten deliveries arrive within about 7.8 days.
(Percentiles are usually found by interpolating between neighbouring values, so a percentile need not be a value in the data. Tools have a few slightly different methods, which is why Excel, NumPy and SQL can differ in the last digit for small samples.)
Calculating them
Excel:
=PERCENTILE.INC(A2:A10, 0.9) =QUARTILE.INC(A2:A10, 1) =MEDIAN(A2:A10)
=PERCENTRANK.INC(A2:A10, 5) the rank of a value
SQL (PostgreSQL):
SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY days) AS q1,
PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY days) AS median,
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY days) AS q3,
PERCENTILE_CONT(0.90) WITHIN GROUP (ORDER BY days) AS p90
FROM deliveries;
PERCENTILE_CONT interpolates; PERCENTILE_DISC returns an actual value from the data. For "which decile is each row in?" use the window function NTILE(10) OVER (ORDER BY days).
pandas:
df["days"].quantile([0.25, 0.5, 0.75, 0.9])
df["days"].describe() # includes 25%, 50%, 75%
pd.qcut(df["amount"], 4, labels=["Q1", "Q2", "Q3", "Q4"]) # equal-count bands
Python standard library: statistics.quantiles(days, n=4, method="inclusive") returns the three quartile cut points [3.0, 4.0, 5.0].
The IQR and outlier fences
The IQR is the spread of the middle 50 percent, so it ignores extremes. A common rule marks values as outliers beyond the fences:
lower fence = Q1 - 1.5 x IQR
upper fence = Q3 + 1.5 x IQR
Here: upper fence = 5 + 1.5 x 2 = 8, so the 15-day delivery is flagged as an outlier, and the lower fence is 3 - 3 = 0, so nothing is low. This is the same rule the data cleaning lesson used.
Box plots
A box plot (box-and-whisker plot) shows all of this at once:
- The box runs from Q1 to Q3, with a line at the median.
- The whiskers extend to the most extreme values within the fences (1.5 x IQR from the box).
- Dots beyond the whiskers are outliers.
df.boxplot(column="amount", by="city")
plt.show()
How to read one: the line's position inside the box shows skew (a line near the bottom means the data bunches low); a long box means large spread; a long upper whisker or dots above mean a right tail. Box plots are excellent for comparing groups side by side, such as salary by city or delivery time by courier, because you can compare medians and spreads in one glance without drawing many histograms.
Percentiles in business
Service levels and targets are usually written as percentiles because averages hide bad experiences:
- "p90 delivery time is 7 days": 90 percent of orders arrive within 7 days; 10 percent take longer.
- "p95 page load time is 3 seconds": the slowest 5 percent are slower than that.
- Salary bands: "The market median for this role is 6 lakhs and the 75th percentile is 8 lakhs."
- Exam results: "Your score is at the 82nd percentile."
- Customer value: the top 10 percent of customers (above the 90th percentile of spend) may account for most of the revenue.
Be clear about the difference between a percentile (a position: "at the 90th percentile") and a percentage (a share: "90 percent of the questions right"); mixing them up is a common error.
Common mistakes
- Reporting only averages for skewed measures such as delivery or load times.
- Confusing percentile and percentage.
- Mixing percentile methods between tools and being puzzled by small differences.
- Calculating percentiles on tiny samples and treating them as precise.
- Plotting box plots of very different group sizes without noting the counts.
Try it yourself
- Find Q1, the median, Q3, the IQR and the fences of your own list of 15 numbers by hand, then verify with Python.
- Compute the 90th percentile of order values in SQL or pandas.
- Draw box plots of an amount column grouped by city and write two sentences about what they show.
- Divide customers into four equal-count spend bands with
qcutand compute each band's share of revenue.
Check yourself
What are the quartiles?
The 25th, 50th and 75th percentiles, which split sorted data into four equal-count groups.
How do you calculate the IQR?
Q3 minus Q1.
What does the p90 of delivery time being 7 days mean?
Ninety percent of deliveries take 7 days or less and ten percent take longer.
What do the dots beyond the whiskers on a box plot show?
Values outside 1.5 times the IQR from the box, which are flagged as potential outliers.
Interview questions
Why use percentiles instead of the mean for response times?
Response times are right-skewed, so the mean hides the slow tail that customers feel; percentiles such as p90 and p99 show what most users and the worst users experience.
How would you find the top 10 percent of customers by spend?
Compute the 90th percentile of total spend per customer and keep customers at or above it, or use NTILE(10) and take the top bucket.
What is the difference between a percentile and a percentage?
A percentile is a position in a distribution; a percentage is a share of a whole. A score at the 90th percentile is better than 90 percent of other scores, not necessarily 90 percent correct.
Summary
- Percentiles show position; quartiles split data into four groups; the IQR is Q3 minus Q1.
- Use
PERCENTILE.INC,PERCENTILE_CONT,quantileorstatistics.quantilesto calculate them. - The 1.5 x IQR fences flag outliers; box plots show median, spread and outliers at a glance.
- Percentiles express service levels and are better than averages for skewed measures.
Going deeper
- statistics.quantiles:
exclusiveandinclusivemethods. - pandas Series.quantile: interpolation options.
- PostgreSQL ordered-set aggregates:
percentile_contandpercentile_disc. - Matplotlib boxplot: drawing box plots.
See how much of this you can explain
A free mock interview asks you new questions on Statistics for analysts for Data Analyst and scores every answer, with what to fix.