Distinctive Analytics

In previous article we saw how to calculate the IQR by hand with multiple methods. In this article, we will learn how to utilize the functionalities provided by excel and python libraries to achieve the same.

Calculating Interquartile Range with Excel and python libraries.

In the previous article, how to find IQR with mathematical formulae. Now we will learn to use these mathematical formulas implemented in Excel and various python libraries.

Formula for IQR (Inter Quartile Range)

1. Excel Sheet and Google sheet

In all the quartile functions below, the format for the argument is the same.

QUARTILE(range, Q_no)

QUARTILE(A₁:A₉, 1)

QUARTILE.INC(A₁:_A9, 1)

QUARTILE.EXC(A₁:A₉, 1)

Excel provides three functions to calculate quartile, quartile.exc and quartile.inc.

1. Quartile()

Quartile provides answers similar to quartile.inc.

2. Quartile.inc() 

Quartile.inc uses median inclusion method to find quartiles of the provided range of numbers and number of quartile in the function.

3. Quartile.exc()

Inversely, Quartile.exc uses median exclusion to find quartiles of the provided range of numbers and numbers of quartile in the function.

In the following example, series column shows a2:a9 cells are filled with dataset. 

We are showing a quartile calculated left side table. We are calculating quartiles 1,2 and 3. Which are 25%, 50% and 75% respectively.

We show formulas and arguments for calculating the IQR with each method.

In the following image, we show actual calculations and answers of the series

2. IQR with python numpy quantile function

Following is an example for calculating the quartiles and IQR using python NumPy library.

# import libraries
import numpy as np
import pandas as pd
# create list data series
series=[15,36,39,40,41,42,43,47,49]
# create dataaaframe and insert series as a column
df=pd.DataFrame(data=series,columns=['series'])
print(df)

series
0      15
1      36
2      39
3      40
4      41
5      42
6      43
7      47
8      49

Quartiles

# calculate first quartile
q1=np.quantile(df,0.25)
# calculate second quartile
q2=np.quantile(df,0.5)
# calculate three quartile
q3=np.quantile(df,0.75)

print(f'Q1: {q1}\nQ2: {q2}\nQ3: {q3}')
# calculate IQR by substracting Q3 with Q1
print(f'IQR: {q3-q1}')

Q1: 39.0
Q2: 41.0
Q3: 43.0
IQR: 4.0

3. IQR with other methods of Quantile calculation

# Load all the available methods provided by library.
methods=['inverted_cdf','averaged_inverted_cdf','closest_observation','interpolated_inverted_cdf','hazen','weibull','linear','median_unbiased','normal_unbiased']
# traverse through all methods and calculate IQR with each method.
for i in methods:
  print(i.upper())
  q1=np.quantile(df,0.25,method=i)
  q2=np.quantile(df,0.5,method=i)
  q3=np.quantile(df,0.75,method=i)
  print(f'Q1: {q1}\nQ2: {q2}\nQ3: {q3}')
  print(f'IQR: {q3-q1}\n\n')

4. Python SciPy direct IQR calculation

In the above example, we calculated IQR with NumPy’s quartile function. Let’s calculate IQR directly using SciPy library. This library directly provides ability to calculate IQR of a series. It also provides different interpolation techniques for calculations. Linear, Lower, Higher, Nearest, Midpoint.

interpol = ['linear', 'lower', 'higher', 'nearest', 'midpoint']
import scipy.stats
for i in interpol:
    print(i.upper())
    print(f'IQR: {scipy.stats.iqr(df,interpolation=i)}')

LINEAR
IQR: 4.0
LOWER
IQR: 4
HIGHER
IQR: 4
NEAREST
IQR: 4
MIDPOINT
IQR: 4.0