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.0ANCOVA: 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.
Learn 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!
Break the ice
This can be a super guide for you to start and excel in your data science career.
For loop with Dictionary
Traverse a dictionary with for loop Accessing keys and values in dictionary. Use Dict.values() and Dict.keys() to generate keys and values as iterable. Nested Dictionaries with for loop Access Nested values of Nested Dictionaries How useful was this post? Click on a star to rate it! Submit Rating Average rating 0 / 5. Vote count:…
For Loops with python
For loop is one of the most useful methods to reuse a code for repetitive execution.
Metrics and terminologies of digital analytics
These all metrics are revolving around visits and hits which we are getting on websites. Single page visits, Bounce, Cart Additions, Bounce Rate, Exit rate,
A/B testing
A/B tests are randomly controlled experiments. In A/B testing, you get user response on various versions of the product, and users are split within multiple versions of the product to figure out the “winner” of the version.
For Loop With Tuples
This article covers ‘for’ loops and how they are used with tuples. Even if the tuples are immutable, the accessibility of the tuples is similar to that of the list.
Multivariate ANOVA (MANOVA) with python
MANOVA is an update of ANOVA, where we use a minimum of two dependent variables.
Two-Way ANOVA
You only need to understand two or three concepts if you have read the one-way ANOVA article. We use two factors instead of one in a two-way ANOVA.
ANOVA (Analysis of Variance ) part 1
A method to find a statistical relationship between two variables in a dataset where one variable is used to group data.
Basic plots with Seaborn
Seaborn library has matplotlib at its core for data point visualizations. This library gives highly statistical informative graphics functionality to Seaborn.
Matplotlib in python
The Matplotlib library helps you create static and dynamic visualisations. Dynamic visualizations that are animated and interactive. This library makes it easy to plot data and create graphs.
Points You Earned
0 distinction_points
python_points 0
0 Solver points
Leave a Reply
You must be logged in to post a comment.