In this article, we will learn how to utilize the functionalities provided by excel and python libraries to calculate IQR,
IQR with Excel and python
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, Qno)
QUARTILE(A1:A9, 1)
QUARTILE.INC(A1:A9, 1)
QUARTILE.EXC(A1:A9, 1)
Excel provides three functions to calculate quartile, quartile.exc and quartile.inc.
- Quartile()
Quartile provides answers similar to quartile.inc.
- Quartile.inc()
Quartile.inc uses median inclusion method to find quartiles of the provided range of numbers and number of quartile in the function.
- 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.
Python
Tab 3
Python
Python
Tab 3
# 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 dataframe and insert series as a column
df=pd.DataFrame(data=series,columns=['series'])
print(df)
Output
series
0 15
1 36
2 39
3 40
4 41
5 42
6 43
7 47
8 49Quartiles
Python
Tab 3
Python
Python
Tab 3
# 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}')Output
Q1: 39.0
Q2: 41.0
Q3: 43.0
IQR: 4.03. IQR with other methods of Quantile calculation
Python
Tab 3
Python
Python
Tab 3
# 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')| INVERTED_CDF Q1: 39 Q2: 41 Q3: 43 IQR: 4 | INTERPOLATED_INVERTED_CDF Q1: 36.75 Q2: 40.5 Q3: 42.75 IQR: 6.0 | LINEAR Q1: 39.0 Q2: 41.0 Q3: 43.0 IQR: 4.0 |
| AVERAGED_INVERTED_CDF Q1: 39.0 Q2: 41.0 Q3: 43.0 IQR: 4.0 | HAZEN Q1: 38.25 Q2: 41.0 Q3: 44.0 IQR: 5.75 | MEDIAN_UNBIASED Q1: 38.0 Q2: 41.0 Q3: 44.33333333333333 IQR: 6.333333333333329 |
| CLOSEST_OBSERVATION Q1: 36 Q2: 41 Q3: 43 IQR: 7 | WEIBULL Q1: 37.5 Q2: 41.0 Q3: 45.0 IQR: 7.5 | NORMAL_UNBIASED Q1: 38.0625 Q2: 41.0 Q3: 44.25 IQR: 6.1875 |
Inverted CDF:
- Uses the inverse of the cumulative distribution function (CDF).
- Finds the value where the CDF equals the desired quantile.
- Produces the most accurate quantiles for large datasets but can be less effective for small datasets.
Averaged Inverted CDF:
- Averages the inverted CDF values.
- Improves smoothness and stability in the quantile calculation compared to the basic inverted CDF, especially for smaller datasets.
Closest Observation:
- This method simply picks the closest actual observation (data point) as the quantile.
- It does not interpolate between data points, which can make it less precise when working with small datasets or unevenly spaced data.
Interpolated Inverted CDF:
- Similar to inverted CDF, but interpolates between data points to get a more accurate quantile.
- This is useful for small datasets or datasets with unevenly spaced values.
Hazen:
- Hazen’s method adjusts the position of quantiles slightly, aiming for a better approximation by averaging two adjacent data points if necessary.
- It is often used in hydrology and other fields to get a smoother result.
- It tries to balance precision for both small and large datasets.
Weibull:
- This method is based on the Weibull plotting position formula.
- It is particularly useful when analyzing data that follows a Weibull distribution, often used in reliability and survival analysis.
- This method can yield different results than linear methods for highly skewed datasets.
Linear:
- Assumes a linear interpolation between points when calculating quantiles.
- This is one of the most commonly used methods and provides a straightforward interpolation of the data.
- Works well with most types of datasets, especially if the data is evenly spaced.
Median Unbiased:
- Ensures that the median of the sample is unbiased, meaning that the computed Q2 is as close as possible to the true median of the distribution.
- This method tends to minimize bias but can have slight differences in how it handles Q1 and Q3.
Normal Unbiased:
- Similar to median unbiased, but tailored to data that follow a normal distribution.
- Works best when the dataset closely follows a bell curve, making it less applicable for skewed or non-normal data.
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.
Python
Tab 3
Python
Python
Tab 3
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)}')Output
LINEAR
IQR: 4.0
LOWER
IQR: 4
HIGHER
IQR: 4
NEAREST
IQR: 4
MIDPOINT
IQR: 4.0Learn Python The Fun Way
What if we learn topics in a desirable way!! What if we learn to write Python codes from gamers data !!
Meet the most efficient and intelligent AI assistant : NotebookLM
Start using NotebookLM today and embark on a smarter, more efficient learning journey!
ANCOVA: Analysis of Covariance with python
ANCOVA is an extension of ANOVA (Analysis of Variance) that combines blocks of regression analysis and ANOVA. Which makes it Analysis of Covariance.
Break the ice
This can be a super guide for you to start and excel in your data science career.
Understanding Demand Forecasting and Addressing Common Challenges
Are demand forecasting truly predictable? Or are they changing randomly?
The Realistic though Dramatic story of AI Superstar
Let’s enjoy the highly interesting story of Tech Superstar chronologically.
Start Your Python Journey from Scratch
starting your Python journey from scratch is a fantastic endeavour.
5 ways to Improve yourself as a Technical Professional
Try learning a topic from basic > if not understood, ask somebody>
10 Ways To Stay Excellent When Exams Are Near
A friendly guide what every computer science student should have when exams are coming
SQL CRUD basics in 5 mins
Learn SQL CRUD basics and Here’s a fast overview of how to utilize them in 5 minutes.
Important SQL functions
This article will introduce important functions in SQL rank, denserank, over, partition.
Important queries in SQL
In SQL you can make queries in number of ways ,though we can break complex codes into small readable and calculated parts.
Points You Earned
0 distinction_points
python_points 0
0 Solver points
Leave a Reply
You must be logged in to post a comment.