Introduction:

Year-Over-Year growth — referred to as Year-on-Year or YoY growth, is a fundamental metric business use to assess their performance and track progress over time. YoY metric provides valuable insights into the rate at which a business expands or contracts, enabling stakeholders to make informed decisions. It can measure the performance of products, services, and so on.

In this blog, I aim to define year-over-year growth, explain its calculation formula, highlights its significance for both small and large businesses and finally discuss its advantages and disadvantage.

What is Year-over-Year Growth?

Year-over-Year growth is a fundamental metric used to measure the percentage change in a particular variable, such as revenue, profit, sales, or customer base, over a year-long period. Basically, it compares data from the current year with data from the same period in the previous year to determine the rate of growth.

For example: If the company’s revenue was £1 million in 2021 and £2 million in 2022, the year-over-year growth rate would be 100%.

Formula to Calculate Year-over-Year Growth:

Year-over-Year Growth = (
                          (Current Year Data - Previous Year Data) / 
                           Previous Year Data
                        ) * 100

For example: Let’s consider a small business that recorded £70,000 in revenue in sales in 2021 and £100,000 in revenue in sales in 2022.

Year-over-Year Growth = ( (£100,000 - £70,000) / £70,000) ) * 100 = 42.86%

Importance for Small and Large Businesses:

Year-over-Year growth is a crucial metric for businesses of all sizes, and its significance can be observed through the following examples:

1. Small Business:

A local bakery experiencing consistent year-over-year growth in revenue can determine the effectiveness of its marketing campaigns, pricing strategies, and customer satisfaction initiatives.

By analyzing the year-over-year growth rate, the bakery can make data-driven decisions, such as expanding its product line, hiring additional staff, or investing in marketing channels that have yielded the highest returns.

Also for small businesses YoY growth rate plays an important role when businesses need to raise investment from investors in order to grow or expand.

2. Large Business:

A multinational technology and service-based company with operations in multiple countries can utilize YoY growth to access the performance of different segments, regions or markets.

By comparing YoY growth rates, the company can identify markets with the highest potential and allocate resources accordingly.

Moreover, investors and stakeholders rely on YoY growth to evaluate the company’s overall financial health and potential for future profitability.

Advantages of Year-over-Year Growth:

1. Long-term Performance Assessment: YoY growth allows business to evaluate their performance over an extended period, enabling them to identify trends, patterns, and potential areas for improvement or investment.
2. Benchmarking: Businesses can assess their market performance by comparing YoY growth rates to industry standards or competitors.
3. Goal Setting and Forecasting: YoY growth data provides a foundation for setting realistic growth targets and developing strategic plans.

Disadvantages of Year-over-Year Growth:

1. Limited Context: YoY growth provides a snapshot of performance without accounting the external factor, such as economic fluctuations or market dynamics, which may impact the result.
   For example, Many tech companies saw revenue growth during covid, and they invested heavily in employing and adapting new technology. However, the duration of the covid and other external circumstances such as war and inflation force corporations to reduce cash burn in the form of layoffs, suspend projects, stop new hiring and so on.
2. Incomplete Picture: Focusing solely on YoY growth may obscure short-term fluctuations, preventing organisations from discovering underlying difficulties or opportunities.
3. Data Quality: YoY growth is only as reliable as the accuracy and consistency of the data used for calculations. Inaccurate or incomplete data can lead to misleading conclusions.
   Personally, I believe that if there is any data quality issue available in data, it may bring serious consequences to the decisions. Hence, data quality must be taken seriously.

Conclusion:

Year-over-Year growth is a valuable metric that enables businesses to track their progress, make informed decisions, and assess their performance over time. By understanding the definition, calculation formula and significance of this metric, both small and large business can leverage year-over-year growth to enhance their strategic planning.

As previously said, YoY growth rates provide several advantages; nevertheless, prior to making any strategic decisions, it is important to consider the drawbacks as well to avoid future consequences.

Year-Over-Year Growth was originally published in The Startup on Medium, where people are continuing the conversation by highlighting and responding to this story.

Differences, examples on SubQuery vs Common Table Expressions (CTE)

In SQL, both Subqueries and Common Table Expressions (CTEs) are used to perform complex queries or manipulate data. However differences in terms of syntax and usage.

First, let's explore each Subquery and CTEs with examples.

1. SubQueries:

Subquery, also known as a nested query, is a query nested within another query. It allows you to retrieve data based on the results of another query.

Subqueries can be used in various parts of SQL statements, such as the SELECT ,FROM ,WHERE orJOIN clauses. Here’s an example:

SELECT 
    employee_id,  
    total_sales
FROM 
    (SELECT 
        employee_id, 
        SUM(sales_amount) AS total_sales
    FROM 
        sales
    GROUP BY 
        employee_id
    )
WHERE 
    total_sales > 10000

In the above example, (SELECT employee_id, SUM(sales_amount) AS total_sales FROM sales GROUP BY employee_id) calculate’s total_sales amount done by each employee. The outer query then retrieves the employee IDs and total sales of employees who generated above 10,000 in sales.

Subqueries are beneficial when you need to filter or retrieve data based on the results of another query. They can be used to perform calculations, filtering or joining operations within a single SQL statement.

2. Common Table Expressions (CTEs):

CTE is a temporary named result set that you can reference within a SQL statement. It allows you to break down complex queries into smaller, more manageable parts.

CTEs are defined using the WITH keyword and can be used in subsequent queries just like regular tables. Here’s an example:

WITH employee_sales AS 
(
    SELECT 
        employee_id, 
        SUM(sales_amount) AS total_sales
    FROM 
        sales
    GROUP BY 
        employee_id
)
SELECT 
    employee_id, 
    total_sales
FROM 
    employee_sales
WHERE 
    total_sales > 10000

In the above example, employee_sales is defined, which calculates the total sales for each employee from the sales table. Then, the main query references the employee_sales CTE and retrieves the employee IDs and total sales of employees who generated above 10,000 in sales.

CTEs are particularly useful when you need to reuse the same result set multiple times within the query or when you want to simplify complex queries by dividing them into logical parts.

Conclusion:

Subqueries and CTEs serve a similar purpose as you can see in the above examples, but differ in syntax and usage.

CTEs are defined using WITH keyword and can be referenced multiple times in a query, while Subqueries are nested queries used within other queries. Both techniques are powerful and can help you write complex SQL queries efficiently.

In the actual world, several tables must be combined to form a single data model. In a single data model, performed calculations are referred to several times. Subqueries and CTEs both aid in the efficient writing of queries. Personally, I like CTEs because they make queries easier to write and more readable.

I hope this blog is helpful for you and hopefully, it helps to understand the difference between Subqueries and CTEs.

Introduction:

In this blog I cover all steps required to install Python into your Mac or Windows or Linux machine. At the end of this blog you will be ready to start and explore the Python programming world.

1. Python on Mac

Since macOS version 10.8 it comes with Python 2.7 pre-installed by Apple. Which you can upgrade or install by following below simple steps below.

i. Check which Python version pre-installed into your mac.

• Press cmd+space_bar on keyboard & search for terminal.app to open terminal.
• Type command python --version to check current version of python.
  You will see the pre-installed Python version 2.7.
• Now type python3 --version to check python3 available in the system,
  if it throw error means version 3 is not available or
  if it give output like Python 3.0 means mac already have Python version 3.

ii. To install Python version 3 into mac.

• Go to browser and search www.python.org and then click on download tab or click this Python download link.
• Download latest version of Python for macOS.
• Double click (open installed file) and follow normal installation steps.
• At end you will get message Congratuation! Python 3.10.5 for macOS or later was successfully installed.
• Close the installation window & move downloaded file to bin.

iii. Check Python3 Installation successful or not.

• Same as above open terminal.app & type python3 --version
• You will get output as Python with installed version number.
• If you still get an error restart the terminal and run same command to check installed version.
• Still got an error then again follow step 2.

2. Python on Windows Machine

Windows Machine not come with pre-installed Python. To install Python on Windows machine follow below steps.

i. Install Python3

• Go to browser and search www.python.org and then click on download tab or click this Python download link.
• Download latest version of Python for Windows.
• Double click (open installed file) and follow normal installation steps.
• Please follow below necessary steps to have proper installation.
• First window — Check both option i.e.
  Install Launcher for all users (recommended) &
  Add Python 3.5 to path (to avoid further manual steps to add python environment path)
• Second window — Check below recommended options
  Install for all users
  Associate files with Python (requires the py launcher)
  Create Shortcuts for installed applications
  Add Python to environment variables
  Precompile standard library
• Click to install.
• End window — display message Setup was successful — now close this window.

ii. Check installation successful or not.

You can check python installation by 2 option

a. Using Python IDLE i.e. Python Integrated Development and Learning Environment.

• Click on windows icon and search for Python IDE & press enter.
• Run command python --version it will display an output with Python and its installed version number.

b. Using cmd i.e. Windows Command Prompt

• Click on windows icon & search for cmd & press enter.
• Run command python this will open same shell as Python IDLE. If it throws means Python not present in your system path.
• To set Python into system path.
  Click on windows icon and search Environment Variables
  Click on Edit the system environment variables
  Click on Environment Variables
  Select Path and Edit
  Add Python installed directory path along with add ; semicolon at end of path.
• Now open windows cmd and search python if path added correctly it will open Python IDLE type shell.
• Run command python --version this will display installed Python version.

3. Install Python on Linux or Ubuntu

Mostly Python not pre-installed into Linux or Ubuntu. To install Python follow below steps.

i. Install Python3

• Press super_key or windows key & search terminal & press enter.
• To install write command sudo apt install python3
  It will prompt password enter password to proceed with installation.
• Once above installation done, install other essential packages like pip, build essential, dev and so on.
  To install all packages in one command run command sudo apt install python3-pip build essential python3-dev
• Python has been installed successfully to you Linux or Ubuntu machine.

ii. Check Python installation successful or not.

• On the above same terminal screen type python3 -V to check current version of python.
  You will see the output with Python and its installed version number.

Conclusion:

In this blog, I tried to cover all steps required to install Python into your Mac or Windows or Linux Machine.

I hope you like this blog, feel free to join me on LinkedIn. I love to know your thoughts on this article and feedback.

Thank you for reading.

If this post was helpful, please click the clap 👏 button below a few times to show your support for the author 👇

🚀Developers: Learn and grow by keeping up with what matters, JOIN FAUN.

Install Python into Mac / Windows / Linux Machine was originally published in FAUN.dev() 🐾 on Medium, where people are continuing the conversation by highlighting and responding to this story.

Different Types Of SQL Windows Functions.

Introduction:

A Window Function performs a Data Analysis calculation across a set of table rows that are somehow related to the current row. Address the comparable type of calculation can be done with an aggregate function that gives a single row or grouped by condition (refer to Figure 1).

Window function does not cause rows to become grouped into a single output row. Rows retain their separate identities also able to access more than just the current row of the query result. (refer to Figure 1).

Window Function Syntax:
Window_Function([All] expression) 
OVER( [PARTITION BY expression_list] [ORDER BY order_list Row_or_ Range clause] )

For more information please visit my last blog, I Introduced the SQL Window Function-Part I and gave an overview of different types of SQL Window Functions.

In this blog, I am explaining all three major types of SQL Window Function along with examples.

The database used to explain the below concepts: Postgres database.

Dataset: Available at Github: Retails.csv

Types of SQL Window Functions:

1. Window Aggregated Function
2. Window Ranking Aggregated Function
3. Window Analytical Function

1. Window Aggregated Function

Consist one of the supporting aggregated function i.e. AVG(), COUNT(), MIN(), MAX(), SUM().

a. AVG():

• Returns average value for the input expression values.
• The function works with Numeric Values and ignores NULL values.

Example:

Write a query to calculate the average order price.

Query:
SELECT order_id, product_id, price, AVG(price) OVER (PARTITION BY order_id) AS Average_Order_Price 
FROM retails

Explanation:

• To calculate average order price, window function used AVG() on price columns and partition by on order_id
• Consider order id: 1112 — consist 3 products (i.e. 1,2,5). The average value of those 3 products are (866 + 163 +173) / 3 = 400.667

b. COUNT():

• Calculates the number of rows with NULL values too if available in column or expression.
• This window function is helpful while creating a new feature in the dataset. Like count number of entries belong to each customer.

Example:

Query 1: Calculate the number of products purchased by a customer in the order.

SELECT order_id, name, product_id, COUNT(*) OVER (Partition BY order_id) AS Number_of_Products 
FROM retails

Query 2: Calculate the number of product sales purchased (running total) by the customer.

SELECT order_id, name, product_id, COUNT(*) OVER (Order BY order_id) AS Number_of_Products 
FROM retails

Explanation:

Query 1:

• Partition by order_id counts number of records belongs to particular order_id.
• In the output, we can see several products for each order displayed.

Query 2:

• Order by order_id counts a number of records and particular order_id and then add number records of consecutive order.
• Output: We can see the count is increased by the number of records related to particular order_id.

c. Min() or Max():

• Min() or Max() return Minimum or Maximum value of the expression across the input values respectively.
• Both window function works with Numeric values and ignores NULL values.

Example:

Below query add a new feature into the result set, Minumum and Maximum price of the product purchased in respective order.

Query:
SELECT order_id, name, product_id, price, 
MIN(price) OVER (Partition BY order_id) AS Minimum_Price_Product,
MAX(price) OVER (Partition BY order_id) AS Maximum_Price_Product 
FROM retails

Explanation:

• For each order_id record respective minimum and maximum price of the product has been added.
• We can use each function separately too.

d. Sum():

• Returns sum/total expression across all input value.
• The function works with Numeric Values and ignores NULL values.

Example:

Below query return Total Price of each order_id.

Query:
SELECT order_id, name, product_id, price, 
SUM(price) OVER (PARTITION BY order_id) AS Average_Order_Price 
FROM retails

Explanation:

• New column added with total_order_price for each order_id.
• Helpful to analyse data, where we have a number of records, belongs to each order_id.

2. Window Ranking Aggregated Function:

Consist one of the supporting ranking function i.e. RANK(), DENSE_RANK(), ROW_NUMBER().

a. RANK():

• The Rank of a value in a group of values based on the ORDER BY expression in the OVER clause (refer Query 1).
• Each value is ranked within its PARTITION BY expression (refer Query 2).
• Rows with equal values for the ranking criteria receive the same rank.
• Tie or same rank skip the consecutive rank eg. Rank (): 1,1,3,4,5.

Example:

Query 1: Rank the product based on their prices.

SELECT order_id, name, product_id, price, 
RANK() OVER (ORDER BY price) AS Rank_Product_Price 
FROM retails

Query 2: Rank the product based on their prices in each order (i.e. partition by order_id).

SELECT order_id, name, product_id, price, 
RANK() OVER (PARTITION BY order_id ORDER BY price) AS Rank_Product_Price 
FROM retails

Explanation:

As we can see in both query, ORDER BY states the expression used to rank the values.

Query 1:

• The ranking is done based on product_price.
• Also note, 9 rows with the same value has tie rank 1.
• So next Rank value starts with 10.

Query 2:

• Rank has been done by ORDER BY expression i.e. price column.
• Check order_id 114, we can see the rank for first 2 product prices are same. Hence rank assign to it is 1.
• Next product price rank with 3 within that order_id.

b. DENSE_RANK():

• Similarly to Rank() function, Rank of a value in a group of values based on the ORDER BY expression and the OVER clause and each value is ranked within its PARTITION BY expression.
• The difference is, Rows with equal values receive the same rank and Tie or the same rank not skip the consecutive rank.
• Example: Dense_Rank(): 1,1,2,3,4

EXAMPLE:

Query: Dense_Rank the product based on their prices in each order (i.e. partition by order_id).

SELECT order_id, name, product_id, price, 
DENSE_RANK() OVER (PARTITION BY order_id ORDER BY price) AS Dense_Rank_Product_Price 
FROM retails

Explanation:

• As we can see ranking to each row done based on ORDER BY expression i.e. price values also within each order_id i.e. (PARTITION BY order_id).
• order_id 1114, have 5 products out of which 2 products having the same price hence rank tie i.e. 1.
• The next rank starts with 2 (this is the major difference between Rank() and Desne_Rank() function).
• Dense_Rank() not skip the consecutive rank number.

c. CUME_DIST():

• Calculates Relative Rank of the current row within a window partition based on below Formula:

EXAMPLE:

Query: CUME_DIST i.e. Relative rank the product based on their prices in each order (i.e. partition by order_id).

SELECT order_id, name, product_id, price, 
CUME_DIST() OVER (PARTITION BY order_id ORDER BY price) AS Dense_Rank_Product_Price 
FROM retails

Explanation:

Let's consider order_id 1112 having 3 products, relative rank calculated as discussed below formula used:

• Row no. 3— First product: 1/3 = 0.3333
• Row no. 4— Second product: 2/3 = 0.666
• Row no. 5— Third product: 3/3 = 1

Similarly, if product having same value or price then relative rank also same check order_id 1114 in output screenshot.

d. ROW_NUMBER():

• An ordinal number of the current row within its partition based on ORDER BY expression in the OVER clause.
• Each value is ordered within its PARTITION BY expression.
• Rows with equal values for the ORDER BY expressions receive different row numbers non deterministically.

EXAMPLE:

Query: Assign Row_Number to the product based on their prices in each order (i.e. partition by order_id).

SELECT order_id, name, product_id, price, 
ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY price) AS Row_Number_Product_Price 
FROM retails

Explanation:

• As we can see in output screenshot, row number assign based on price (ORDER BY expression) within each order (PARTITION BY order_id).
• Not consider value same or not, just assign row_number to each row in the expression.

e. NTILE():

• Divides the rows for each window partition, as equally as possible, into a specified number of ranked groups.
• Requires ORDER BY clause in the OVER clause.
• The column or expression specified in ORDER BY clause, first all values has been sorted in ascending order and then equally assign group number.

Example:

Query: Assign Group/cluster/bucket number to all row into 10 different groups based on the product price.

SELECT order_id, name, product_id, price, 
NTILE(10) OVER (ORDER BY price) AS NTile_Product_Price 
FROM retails

Explanation:

• In this dataset, we have a total of 50 records.
• Hence, each clustered consist of 5 rows as shown in the output screenshot.
• First, all rows have been sorted with the price and then assign a group number to each row.

f. PERCENT_RANK()

• Percentage rank of the current row using the following formula:

Example:

Query: Calculate or assign percentage rank to all row based on the product price.

SELECT order_id, name, product_id, price, 
PERCENT_RANK() OVER (PARTITION BY order_id ORDER BY price) AS Row_Number_Product_Price 
FROM retails

Explanation:

Let’s consider order_id 1114 having 5 products, relative rank calculated as discussed below formula used:

• Row no. 9— First product: (1–1)/(5–1) = 0
• Row no. 10— Second product: (1–1)/(5–1) = 0
• Row no. 11— Third product: (3–1)/(5–1) = 0.5
• Row no. 12 — Forth product: (4–1)/(5–1) = 0.75
• Row no. 13 — Fifth product: (5–1)/(5–1) = 1

3. Window Analytic Functions:

Consist one of the supporting ranking function i.e. LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE().

a. LAG() or LEAD():

Syntax:

LAG | LEAD (expression)
    OVER ([ PARTITION BY expression_list] [ORDER BY order_list] )

• LAG or LEAD returns, value for the row value before or after the current row in a partition respectively.
• If no row exists, null is returned.

Example:

Query: Add new feature 1 step LAG or LEAD product price within each order (i.e. PARTITION BY order_id)

SELECT order_id, name, product_id, price, 
LAG(price,1) OVER (PARTITION BY order_id ORDER BY price) AS LAG_Product_Price,
LEAD(price,1) OVER (PARTITION BY order_id ORDER BY price) AS LEAD_Product_Price
FROM retails

Explanation:

• As we can see both lag and lead column give 1 step value respectively within the partition of order_id.
• When its first row in LAG() gets imputed with NULL.
• Similarly, in LEAD() the last row gets imputed with NULL.

b. FIRST_VALUE() or LAST_VALUE():

Syntax:

FIRST_VALUE | LAST_VALUE ( expression ) 
      OVER ( [PARTITION BY expression_list ] [ ORDER BY order_list ][ row_or_range_clause ] )

• FIRST_VALUE or LAST_VALUE return First-row or Last-row value of the specified expression with respect to the window frame (partition) respectively.

Example:

Query: Add new feature FIRST_VALUE or LAST_VALUE product price within each order (i.e. PARTITION BY order_id).

SELECT order_id, name, product_id, price, 
FIRST_VALUE(price) OVER (PARTITION BY order_id) AS FIRST_VALUE_Product_Price,
LAST_VALUE(price) OVER (PARTITION BY order_id) AS LAST_VALUE_Product_Price
FROM retails

Explanation:

• As we can see both First_Value() and Last_Value() column give first-row value and last-row value respectively.

c. NTH_VALUE():

Syntax:

NTH_VALUE (expression, nth_value ) 
      OVER ( [PARTITION BY expression_list ] [ ORDER BY order_list ][ row_or_range_clause ] )

• NTH_VALUE return nth value assigned with expression with respect to the window frame (partition) respectively.
• If nth_value is not available then its get imputed with NULL.

Example:

Query: Add new feature NTH_VALUE product price within each order (i.e. PARTITION BY order_id).

SELECT order_id, name, product_id, price, 
NTH_VALUE(price,3) OVER (PARTITION BY order_id) AS NTH_VALUE_Product_Price
FROM retails

Explanation:

• As in query we assigned nth_value as 3, it returns the 3rd-row value is partitioned by order_id.
• For order_id 1111 there are only 2 products, hence column gets imputed with the NULL value.

Conclusion:

In this blog, I tried to explain the major types of Window Function in SQL.

Window Functions are very useful while doing data analysis using SQL and easy to use.

Window Functions revolves around OVER, PARTITION BY and ROW or RANGE clauses.

I hope you like this blog, feel free to join me on LinkedIn. I love to know your thoughts on this article and feedback.

Thank you for reading.

Resources:

Dataset and SQL scripts were available at GITHUB.

References:

1. postgresql.org (2019). PostgreSQL: Documentation: 9.1: Window Functions. [online] Postgresql.org. Available at: https://www.postgresql.org/docs/9.1/tutorial-window.html.
2. drill.apache.org (n.d.). SQL Window Functions Introduction — Apache Drill. [online] drill.apache.org. Available at: https://drill.apache.org/docs/sql-window-functions-introduction/.

SQL Window Functions — Part 2 was originally published in TDS Archive on Medium, where people are continuing the conversation by highlighting and responding to this story.

Introduction & Overview Types of Window Function in SQL.

Introduction:

A window function performs a Data Analysis calculation across a set of table rows that are somehow related to the current row. Address the comparable type of calculation can be done with an aggregate function that gives a single row or grouped by condition (refer to Figure 1).

Window function does not cause rows to become grouped into a single output row. Rows retain their separate identities also able to access more than just the current row of the query result. (refer to Figure 1)

The database used to explain below concepts: Postgres database and Dataset: Available at Github Order_Table.csv

Window Function Syntax:

Window_Function([All] expression) 
OVER( [PARTITION BY expression_list] [ORDER BY order_list Row_or_ Range clause] )

Each part of syntax has been explained as follows:

Window_Function:

There are 3 major types of window_functions

1. Window Aggregated Function: Consist one of the supporting aggregated function i.e. AVG(), COUNT(), MIN(), MAX(), SUM().

2. Window Ranking Aggregated Function: Consist one of the supporting ranking function i.e. RANK(), DENSE_RANK(), ROW_NUMBER().

3. Window Analytical Function: Consist one of the supporting ranking function i.e. FIRST_VALUE(), LAST_VALUE(), NTH_VALUE().

Over() clause:

Over() clause is used to define the partitioning and ordering of rows (i.e. a window) for the functions (i.e. avg, count, etc.) to operate on. Hence, called windows function.

Over() clause have the following parameters:

1. Order by: Defines the logical order of the row.

For example:- Rank the product_ids with respect to the product price.

Query:
Select product_id, price, Rank() OVER (ORDER BY price desc) AS Average_price
From orders;

2. Partition by:- Divides the query result set into partitions i.e. window function applied to each partition separately.

For example:- Calculate the Average_Order_Price by gender i.e. partition by gender.

Observation:

The average_price has been calculated by considering partition column i.e. gender. Hence, the same average_price has been calculated for each gender category.

3. ROWS or RANGE clause: Limit the rows within the partition by specifying start and endpoints within the partition. The default value for Rows or Range clause is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Types of Rows or Range clause:

a. UNBOUNDED PRECEDING AND CURRENT ROW:

• It will start considering row values for aggregated function from ROW number 1 till current row.
• For example:-

Query:

SELECT order_id, gender, price, 
AVG(price) OVER (ORDER BY price ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Average_Price
FROM orders;

• Current Row no 7— Average_Price: ( 163 + 163 +163 +163 +376 + 376 + 376) / 7 = 254.2857

b. UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING:

• Unbounded Preceding consider the first row and Unbounded Following means it will consider the last row of computing column.
• For example:

Query:
SELECT order_id, gender, price, 
AVG(price) OVER (ORDER BY price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS Average_Price
FROM orders;

• As we can see the average_price for each column is same, because it considers all values in the price column.

c. Other Types:

BETWEEN 1 PRECEDING AND 1 FOLLOWING:

Query:
SELECT order_id, gender, price, 
AVG(price) OVER (ORDER BY price ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS Average_Price
FROM orders;

Average_Price calculation done by considering:

a. Current row value

b. One row preceding i.e. row above current_row value.

c. One row following i.e. row below the current_row value.

• Similarly, we can change the range of window as per requirement.

Conclusion:

SQL Windows Functions are very powerful. In this blog, I tried to give a short overview of Windows Function.

I hope this blog helpful for you and hopefully it sparks your interest to see what windows function can do.

I highly encourage to read referenced documents for a more in-depth explanation.

References/Study Materials:

1. postgresql.org (2019). PostgreSQL: Documentation: 9.1: Window Functions. [online] Postgresql.org. Available at: https://www.postgresql.org/docs/9.1/tutorial-window.html.
2. drill.apache.org (n.d.). SQL Window Functions Introduction — Apache Drill. [online] drill.apache.org. Available at: https://drill.apache.org/docs/sql-window-functions-introduction/ [Accessed 10 Nov. 2020].

SQL Windows Function was originally published in Analytics Vidhya on Medium, where people are continuing the conversation by highlighting and responding to this story.

Introduction:

Python is a popular language that allows programmers to write elegant, easy-to-write and read code like plain English. The unique feature of Python is a different type of comprehensions.

In Python, there are three types of comprehensions viz. List, Dictionary and Set.

By the end of this blog, you’ll understand the full power of Python comprehensions and how to easily use its functionality.

1. LIST COMPREHENSION

List: List is a collection of data surrounded by square brackets and each element are separated by a comma.

List Comprehension: Is also surrounded by square brackets but instead of the list of elements inside it contain expression like for loop &-or followed by if-clauses.

Example:

a. Create a List of the square of numbers between 1 to 100.

# Without List comprehension
SquaresWithoutComprehension = []
for i in range(1,101):
    SquaresWithoutComprehension.append(i**2)

## List Comprehension
SquaresWithComprehension = [i**2 for i in range(1,101)]

b. List comprehension with the condition.

# Suppose we have List consist movie names with released year.

MoviesYear = [('Star Wars',2019),('Glass',2020),('The Upside',2018), ('The LEGO Movie 2',2020),('Cold Pursuit',2017),
         ('Hotel Mumbai',2020)]

## Problem: Create List of movies released in 2020?

Movies_20 = [title for (title, year) in MoviesYear if year == 2020]

c. List comprehension — Mathematical Applications.

# Suppose we have List of numbers 1 to 10.

Numbers = [1,2,3,4,5,6,7,8,9,10]

## Problem 1: Perform scalar multiplication i.e multiply each number with 2 and store result into the List.

ScalarMultiplication  = [4*X for X in Numbers]

### Problem 2: Perform cartesian multiplication between List A and B.

A = [1,2,3,4]
B = [10,11,12,13]

CartesianProduct = [(a,b) for a in A for b in B]

##Output:

[(1, 10), (1, 11), (1, 12), (1, 13), (2, 10), (2, 11), (2, 12), (2, 13), (3, 10), (3, 11), (3, 12), (3, 13), (4, 10), (4, 11), (4, 12), (4, 13)]

Explanation:

The above will produce the same result but when we use list comprehension the line of codes get reduced and the same operation is done with a single line of code.

2. DICTIONARY COMPREHENSION

Dictionary: A dictionary is a collection which is unordered, changeable and indexed. In Python dictionary written with curly brackets, and they have key and value pairs.

DictionaryExample = {“IDE”: “JupyterNotebook”, “Language”: “Python”, “Version”: 3}

Dictionary Comprehension: A dictionary comprehension is also a collection which is unordered, changeable and indexed, where Key-Value pairs generated with help of expression.

Example of Dictionary Comprehension:

a. Create dictionary where Key as Alphabets and Value as the number of times alphabets occurred in the sentence (Dictionary definition).

# Sentence

DictDefination = "A dictionary is a collection which is unordered, changeable and indexed. In Python written with curly brackets, and they have keys and values."

## Dictionary comprehension

AlphabetDictionary = {
        key: DictDefination.count(key) for key in DictDefination
                     }

b. Dictionary Comprehension — Mathematical Application

# Problem: Create dictionary where Key as number and Value as cube of key i.e. number.

## Dictionary comprehension

NumSquareDictComprehension = {key:key**3 for key in range(1,10)}

OR

NumSquareDictComprehension = {f"The square of {key} is":key**3 for key in range(1,10)}

3. SET COMPREHENSION

Set: In Python Set is unique, unordered, mutable collection of elements. Set where elements enclosed with curly brackets and elements separated by a comma.

SetExample = {‘Python’,’Java’,’R’}

Set Comprehension: Simillar to list comprehension but consist of a collection of unique, unordered, mutable elements, where dictionary elements inside it contain an expression.

Example of Set Comprehension:

a. Create Set consist squares of a number between 1 to 10.

SquareSet = {num**2 for num in range (1,11)}

b. Create Set of Unique elements available in the list.

# Suppose list having duplicate elements

CarBrands = ['BMW','Chevrolet','Bentley','BMW']

## Set comprehension only give unique elements from the above List

CarBrandDict = {Brand for Brand in CarBrands}

Advantages of Comprehensions:

• Easy to implement.
• Reduce the number of lines of code.
• Faster execution and utilise fewer resources.

Disadvantages of Comprehensions:

• More number of comprehensions used in the program increase the complexity of code.
• Complicate to understand expression working.

Conclusion:

As discussed above, different types of comprehensions available in Python. Above implemented are basic examples expressions used in comprehensions can be used to call the functions. Most often, list and dictionary comprehension use to simplify the code.

Hope, this blog helps you understand some effective and easy-to-use Python techniques.

Thank you for reading!!

Python Comprehensions With Implementation was originally published in TDS Archive on Medium, where people are continuing the conversation by highlighting and responding to this story.

Introduction:

Time Series Forecasting is the use of the statistical model to predict future value based on past observation/results. Use any variables tracked and collected over time. For example, annual population, daily stock prices, daily-weekly-quarterly sales, etc.

Time Series Data Characteristics & Objectives:

• The data completely depend on data and time. Hence, if the order of date or time change leads to a change in the meaning of data.
• Data collection must be done sequentially and at an equal interval of time.
• Once data collection is done properly, the next step is to identify patterns with help of a sequence of observations and forecast/predict the future values in Time series.

Time Series Components

1. Time Series Plot

In Time-Series plot always, X-axis indicates Date/Time and Y-axis indicates Target Variable. For example, Want to predict monthly sales of the shop. Then X-axis: Days and Y-axis: Per day sales value.

2. Types of Trend in Time-Series plot

There are 3 types of trend can be observed in Time-Series graph. Also, each observed trend in Time-Series called Trend Cycle.

• Uptrend: The trend having HH (Higher High) & HL (Higher Low).
• Downtrend: The trend having LH(Lower High) & LL (Lower Low).
• Horizontal or Sideway Trend: The trend moving with same or no variation in time series and moving horizontally.

3. Seasonality

• Time-Series exhibits a repeated pattern at a fixed interval of time. For example, Sales of Air-Conditioner increase during summers and decrease during winters.
• Seasonality trend helps business to manage their inventories, staffs and other business-related decision.

3. Cyclical Pattern

• Time-Series has no fixed period for rising/fall pattern.
• Also, there is no repeated pattern within each year i.e no seasonality observed.
• The magnitude of trend cycles changes more frequently, hence much harder to predict the future trend.

Types of Time-Series Predictive/Forecasting Models

1. ETS — Error Trend Seasonality Model

ETS models are non-stationary. ETS models are considered as exponential smoothing and then state-space. ETS model describes how unobserved components of the data (error, trend, and seasonality) change over time.

In the ETS model giving more weight to most recent observations or values with weights gradually gets smaller as observations get older.

There are 4 types of ETS methods:

• Simple Exponential Smoothing Method (SES)
• Holt’s Linear Trend Method (HLT)
• Exponential Trend Method (ET)
• Holt’s-Winter Seasonal Method (HWS)

2. ARIMA — Auto-Regressive Integrated Moving Average Model

ARIMA models are stationary. ARIMA models focus on autocorrelation in data and forecast future trends.

There are two types of ARIMA models i.e. non-seasonal and seasonal.

Non-Seasonal ARIMA:

ARIMA(p,d,m)

Where: p = Auto regressive value,

d = Number of transformation done to make data stationery,

m = Moving Average

Seasonal ARIMA:

ARIMA (p,d,m) (P,D,M) m

Where:

p: Autoregressive value,

d: Number of transformation done to make data stationery,

m: Moving Average

P, D, M: Autoregressive, number of transformation done to make data stationery, Moving Average of Seasonal Data.

m: Number of periods in each season

Conclusion:

In this blog, I try to summarise the Time-Series its characteristics, types and about forecasting model. In upcoming blogs, I will explain each Time-Series Forecasting models (ETS & ARIMA) in details with Implementation using python.

Implemented popular techniques using Python

In my last blogs, I explained types of missing values and different ways to handle Continous and Categorical missing values with implementation.

After handle missing values in the dataset, the next step was to handle categorical data. In this blog, I will explain different ways to handle categorical features/columns along with implementation using python.

Introduction: All Machine Learning models are some kind of mathematical model that needs numbers to work with. Categorical data have possible values (categories) and it can be in text form. For example, Gender: Male/Female/Others, Ranks: 1st/2nd/3rd, etc.

While working on a data science project after handling the missing value of datasets. The next work is to handle categorical data in datasets before applying any ML models.

First, let’s understand the types of categorical data:

1. Nominal Data: The nominal data called labelled/named data. Allowed to change the order of categories, change in order doesn’t affect its value. For example, Gender (Male/Female/Other), Age Groups (Young/Adult/Old), etc.
2. Ordinal Data: Represent discretely and ordered units. Same as nominal data but have ordered/rank. Not allowed to change the order of categories. For example, Ranks: 1st/2nd/3rd, Education: (High School/Undergrads/Postgrads/Doctorate), etc.

Ways to handle categorical features:

The dataset used to explain is Titanic (Kaggle dataset):

import pandas as pd
import numpy as np
Data = pd.read_csv("train.csv")
Data.isnull().sum()

1. Create Dummies

Description: Create dummies or binary type columns for each category in the object/ category type feature. The value for each row is 1 if that category is available in that row else 0. To create dummies use pandas get_dummies() function.

Implementation:

DataDummies = pd.get_dummies(Data)
DataDummies

Advantage:

• Easy to use and fast way to handle categorical column values.

Disadvantage:

• get_dummies method is not useful when data have many categorical columns.
• If the category column has many categories leads to add many features into the dataset.

Hence, This method is only useful when data having less categorical columns with fewer categories.

2. Ordinal Number Encoding

Description: When the categorical variables are ordinal, the easiest approach is to replace each label/category by some ordinal number based on the ranks. In our data Pclass is ordinal feature having values First, Second, Third so each category replaced by its rank i.e 1,2,3 respectively.

Implementation:

Step 1: Create a dictionary with key as category and values with its rank.

Step 2: Create a new column and map the ordinal column with the created dictionary.

Step 3: Drop the original column.

# 1. 
PClassDict =   {   'First':1,
                    'Second':2,
                     'Third':3,              
                }

# 2. 
Data['Ordinal_Pclass'] = Data.Pclass.map(PClassDict)

# Display result
      
      Data[['PassengerId', 'Pclass', 'Ordinal_Pclass']].head(10)

# 3.

Data = Data.drop('Pclass', axis = 1)

Advantage:

• The easiest way to handle the ordinal feature in the dataset.

Disadvantage:

• Not good for Nominal type features in the dataset.

3. Count / Frequency Encoding

Description: Replace each category with its frequency/number of time that category occurred in that column.

Implementation:

Step 1. Create Dictionaries with key as category name and value with a count of categories i.e frequency of that category in each categorical column.

Step 2. Create a new column which acts as a weight for that category and map with its respective dictionary.

Step 3. Drop Orginal Columns.

# 1.

Pclass_Dict = Data['Pclass'].value_counts()
Salutation_Dict = Data['Salutation'].value_counts()
Sex_Dict = Data['Sex'].value_counts()
Embarked_Dict = Data['Embarked'].value_counts()
Cabin_Serial_Dict = Data['Cabin_Serial'].value_counts()
Cabin_Dict = Data['Cabin'].value_counts()

# 2.

Data['Encoded_Pclass'] = Data['Pclass'].map(Pclass_Dict)
Data['Salutation_Dict'] = Data['Salutation'].map(Salutation_Dict)
Data['Sex_Dict'] = Data['Sex'].map(Sex_Dict)
Data['Embarked_Dict'] = Data['Embarked'].map(Embarked_Dict)
Data['Cabin_Serial_Dict'] = Data['Cabin_Serial'].map(Cabin_Serial_Dict)
Data['Cabin_Dict'] = Data['Cabin'].map(Cabin_Dict)

# Display Result

Data[['Pclass','Encoded_Pclass','Salutation','Salutation_Dict','Sex'       ,'Sex_Dict','Embarked','Embarked_Dict','Cabin_Serial','Cabin_Serial_Dict','Cabin','Cabin_Dict']].head(10)

# 3. 
Data = Data.drop(['Pclass','Salutation','Sex','Embarked','Cabin_Serial','Cabin'], axis = 1)

Advantage:

• East to implement.
• Not increasing any extra features.

Disadvantage:

• Not able to handle the same number of categories i.e provide the same values to both categories.

4. Target/Guided Encoding

Description: Here, the category of the column has been replaced with its depending join probability ranking with respect to Target column.

Implementation: To show the implementation I am using Cabin column with respect to Survived target column. The same steps are applicable for any ordinal column in the dataset.

Step 1. Replace original cabin value with the first character of the cabin name.

Step 2. Calculate the joint probability of each category based on the target column value.

Step 3. Create a list with sorted index in ascending order of join probabilities.

Step 4. Create a dictionary where key as category name in cabin and values as joint probability ranking.

Step 5. Create a new column and map cabin values with dictionary joint probability ranking.

Step 6. Delete original cabin column.

# 1.

Data['Cabin'] = Data['Cabin'].astype(str).str[0]

# 2.

Data.groupby(['Cabin'])['Survived'].mean()

# 3.

Encoded_Lables = Data.groupby(['Cabin'])  ['Survived'].mean().sort_values().index

# 4.

Encoded_Lables_Ranks = { k:i for i, k in enumerate(Encoded_Lables, 0) }

# 5.

Data['Cabin_Encoded'] = Data['Cabin'].map(Encoded_Lables_Ranks)

# 6.

Data = Data.drop('Cabin', axis = 1)

Advantages:

• It doesn’t affect the volume of the data i.e not add any extra features.
• Helps the machine learning model to learn faster.

Disadvantages:

• Typically, mean or joint probability encoding leads for over-fitting.
• Hence, to avoid overfitting cross-validation or some other approach is required most of the time.

5. Mean Encoding

Description: Simillar to target/guided encoding only difference is here we replace category with the mean value with respect to target column. Here also we implement with cabin and survived target column.

Implementation:

Step 1. Calculate the mean for each category in the cabin column with respect to the target column (Survived).

Step 2. Create a new column and replace by mean i.e map cabin column categories with its encoded mean dictionary.

Step 3. Drop original cabin column

# 1.

Encoded_Mean_Dict = Data.groupby(['Cabin'])['Survived'].mean().to_dict()

# 2.

Data['Cabin_Mean_Encoded'] = Data['Cabin'].map(Encoded_Mean_Dict)

# Display result

Data[['Cabin','Cabin_Mean_Encoded']].head()

# 3.

Data = Data.drop('Cabin', axis = 1)

Advantages:

• Capture information within labels or categories, rendering more predictive features.
• Create a monotonous relationship between the independent variable and the target variable.

Disadvantages:

• May leads to overfit the model, to overcome this problem cross-validation is use most of the time.

6. Probability Ratio Encoding

Description: Here category of the column is replaced with a probability ratio with respect to Target variable. Here I am using Cabin as an independent variable and its categories is replaced with a probability ratio of person Survived vs Died in each cabin.

Implementation:

Step 1. Replace original cabin value with the first character of the cabin name.

Step 2. Find percentage (%) of people survived in a particular cabin and store into new dataframe.

Step 3. Create a new column into probability survived dataframe with the probability of people getting dead in a particular cabin.

Step 4. Create one more new column into probability survived dataframe i.e ratio of survived and died probability.

Step 5. Create a dictionary with probability ratio column.

Step 6. Create a new column in Data and replace by mapping cabin column categories with its encoded probability ratio dictionary.

Step 7. Drop original cabin column.

#1. 

Data['Cabin']  = Data['Cabin'].astype(str).str[0]

# 2. 

Probability_Survived = Data.groupby(['Cabin'])['Survived'].mean()
Probability_Survived = pd.DataFrame(Probability_Survived)

# 3.

Probability_Survived['Died'] = 1 - Probability_Survived['Survived']

# 4.

Probability_Survived['Prob_Ratio'] = Probability_Survived['Survived'] / Probability_Survived['Died']

# 5.

Encode_Prob_Ratio = Probability_Survived['Prob_Ratio'].to_dict()

# 6.

Data['Encode_Prob_Ratio'] = Data['Cabin'].map(Encode_Prob_Ratio)

# Display result

Data[['Cabin','Encode_Prob_Ratio']].head(10)

# 7.

Data = Data.drop('Cabin', axis = 1)

Advantages:

• Not increase any extra feature.
• Captures information within the labels or category hence creates more predictive features.
• Creates a monotonic relationship between the variables and the target. So it’s suitable for linear models.

Disadvantages:

• Not defined when the denominator is 0.
• Same as the above two methods lead to overfitting to avoid and validate usually cross-validation has been performed.

Conclusion:

Hence, in this blogs, I try to explain about most widely used ways to handle categorical variables while preparing data for machine learning. The actual code notebook available at https://github.com/GDhasade/Medium.com_Contents/blob/master/Handle_Categorical_Data.ipynb

For more information please visit http://contrib.scikit-learn.org/category_encoders/index.html.

References:

1. Scikit-learn.org. (2019). sklearn.preprocessing.OneHotEncoder — scikit-learn 0.22 documentation. [online] Available at: https://scikit-learn.org/stable/modules/generated/sklearn.preprocessing.OneHotEncoder.html.
2. ‌contrib.scikit-learn.org. (n.d.). Category Encoders — Category Encoders 2.2.2 documentation. [online] Available at: http://contrib.scikit-learn.org/category_encoders/index.html.
3. Krish Naik (2019). Feature Engineering-How to Perform One Hot Encoding for Multi Categorical Variables. YouTube. Available at: https://www.youtube.com/watch?v=6WDFfaYtN6s&list=PLZoTAELRMXVPwYGE2PXD3x0bfKnR0cJjN&ab_channel=KrishNaik [Accessed 10 Sep. 2020].

‌

Ways To Handle Categorical Data Before Train ML Models With Implementation. was originally published in TDS Archive on Medium, where people are continuing the conversation by highlighting and responding to this story.

In this blog, I will explain how to handle missing values of the Categorical data column in the dataset with implementation using python.

Discrete/ Categorical Data: discrete data is quantitative data that can be counted and has a finite number of possible values or data which may be divided into groups e.g. days in a week, number of months in a year, sex (Male/Female/Others), Grades (High/Medium/Low), etc.

The dataset used to explain is Titanic (Kaggle dataset):

import pandas as pd
import numpy as np
Data = pd.read_csv("train.csv")
Data.isnull().sum()
Data.dtypes()

The number of categories in each column:

# Code to get number of categories in missing value columns

print("Number of Categories in: ")

for ColName in DataFrame[['Embarked','Cabin_Serial','Cabin']]:
    print("{} = {}".format(ColName,       len(DataFrame[ColName].unique())))

1. Frequent Categorical Imputation

Assumptions: Data is Missing At Random (MAR) and missing values look like the majority.

Description: Replacing NAN values with the most frequent occurred category in variable/column.

Implementation:

Step 1: Find which category occurred most in each category using mode().

Step 2: Replace all NAN values in that column with that category.

Step 3: Drop original columns and keep newly imputed columns.

#1. Function to replace NAN values with mode value

def impute_nan_most_frequent_category(DataFrame,ColName):
    # .mode()[0] - gives first category name
     most_frequent_category=DataFrame[ColName].mode()[0]
    
    # replace nan values with most occured category
     DataFrame[ColName + "_Imputed"] = DataFrame[ColName]
     DataFrame[ColName + "_Imputed"].fillna(most_frequent_category,inplace=True)

#2. Call function to impute most occured category

for Columns in ['Embarked','Cabin_Serial','Cabin']:
    impute_nan_most_frequent_category(DataFrame,Columns)
    
# Display imputed result
DataFrame[['Embarked','Embarked_Imputed','Cabin_Serial','Cabin_Serial_Imputed','Cabin','Cabin_Imputed']].head(10)

#3. Drop actual columns

DataFrame = DataFrame.drop(['Embarked','Cabin_Serial','Cabin'], axis = 1)

Advantage: Simple and easy to implement for categorical variables/columns.

Disadvantage:

• Features having a max number of null values may bias prediction if replace with the most occurred category.
• It distorts the relation of the most frequent label.

2. Adding a Variable To Capture NAN

Assumptions: No assumption, can be work with all type categorical columns.

Description: Replace NAN categories with most occurred values, and add a new feature to introduce some weight/importance to non-imputed and imputed observations.

Implementation:

Step 1. Create a new column and replace 1 if the category is NAN else 0. This column is an importance column to the imputed category.

Step 2. Replace NAN value with most occurred category in the actual column.

# Function to impute most occured category and add importance vairable

def impute_nan_add_vairable(DataFrame,ColName):
    #1. add new column and replace if category is null then 1 else 0

    DataFrame[ColName+"_Imputed"] =   np.where(DataFrame[ColName].isnull(),1,0)
    
    # 2. Take most occured category in that vairable (.mode())
    
    Mode_Category = DataFrame[ColName].mode()[0]
    
    ## 2.1 Replace NAN values with most occured category in actual vairable
    
    DataFrame[ColName].fillna(Mode_Category,inplace=True)

# Call function to impute NAN values and add new importance feature

for Columns in ['Embarked','Cabin_Serial','Cabin']:
    impute_nan_add_vairable(DataFrame,Columns)
    
# Display top 10 row to see the result of imputation

DataFrame[['Embarked','Embarked_Imputed','Cabin_Serial','Cabin_Serial_Imputed','Cabin','Cabin_Imputed']].head(10)

Advantage: Capture the importance of missingness.

Disadvantage:

• Creating Additional Features(Curse of Dimensionality) e.g. if there are 10 columns have null values need to create 10 extra columns.
• Potentially misunderstood data & the number of missing data should be large enough.

3. Create a New Category (Random Category) for NAN Values

Assumptions: No assumption

Description: Create a new category for NAN values i.e random category.

Implementation:

Step 1. Replace NAN value with a new name (here we create a new category as Unknown).

Step 2. Display result

#1. Function to impute null value with new category
def impute_nan_create_category(DataFrame,ColName):
     DataFrame[ColName] = np.where(DataFrame[ColName].isnull(),"Unknown",DataFrame[ColName])

## Call function to create new category for variables
for Columns in ['Embarked','Cabin_Serial','Cabin']:
    impute_nan_create_category(DataFrame,Columns)

#2. Display result
DataFrame[['Embarked','Cabin_Serial','Cabin']].head(10)

Advantage: Simple and easy to implement for categorical variables/columns and preserves the variance.

Disadvantage:

• May create random data if the missing category is more.
• Doesn’t give good results when missing data is a high percentage of the data.

Conclusion:

The above implementation is to explain different ways we can handle missing categorical data. The most widely used methods are Create a New Category (Random Category) for NAN Values and Most frequent category imputation.

For reference: Jupyter notebook — code available at GitHub: https://github.com/GDhasade/Medium.com_Contents/blob/master/Handle_Categorical_Missing_Data.ipynb

Ways To Handle Categorical Column Missing Data & Its Implementations was originally published in Analytics Vidhya on Medium, where people are continuing the conversation by highlighting and responding to this story.

In this blog, I will explain how to handle missing values for the Continuous data column in the dataset with implementation.

Continous Data: Continuous data is quantitative data that can be measured, it has an infinite number of possible values within a selected range e.g. temperature range, height, weight, etc.

The dataset used to explain is Titanic (Kaggle dataset)

import pandas as pd
import numpy as np
Data = pd.read_csv("train.csv")
Data.isnull().sum()

1. Mean / Median / Mode Imputation:

Assumption: Data is Missing Completely at Random(MCAR).

Description: Replacing NAN values with the most frequent occurrence of variable.

Implementation: Handle Age missing values

# function use to impute_nan values with mean/median/mode
def impute_nan(DataFrame, ColumnName, ImputeValue):
    DataFrame[ColumnName + "_Imputed"] =                                                     DataFrame[ColumnName].fillna(ImputeValue)

# Call function to impute median value
median = Data.Age.median()
impute_nan(Data, 'Age', median)

Similarly, we can replace the NAN value with mean/mode.

This method has disadvantage i.e. impact the correlation and change distortion in original variance. To understand the impact, the below code used to plot data to see the distribution of variance.

# Code to plot distribution of vairance in data columns

fig = plt.figure()
ax = fig.add_subplot(111)
Data['Age'].plot(kind='kde', ax=ax)
Data['Age_Imputed'].plot(kind='kde', ax=ax, color='red')
lines, labels = ax.get_legend_handles_labels()
ax.legend(lines, labels, loc='best')

2. Random Sample Imputation

Assumption: Data is Missing Completely at Random(MCAR).

Description: Taking random observation from the dataset and use these observations to replace the NAN values.

Implementation: Handle Age missing values

Step 1: Create a copy of the Age column.

Step 2: Select random sample values to fill the NAN (Ignore NAN values and select only non-NAN values.)

Step 3: Replace NAN values with random sample values with the index of NAN values.

# function to impute_nan values with random observations
def impute_random_nan(Data,ColName):
    
    #Make copy of Column
    Data[ColName+"_random"]=Data[ColName]
    
    #Select random sample value to fill the na
    # .dropna() - to ignore NAN values and select non-NAN values
    
    random_sample =       Data[ColName].dropna().sample(Data[ColName].isnull().sum(),random_state=0)
    
    # Merge - pandas need to have same index in order to merge the dataset.
    
   random_sample.index = Data[Data[ColName].isnull()].index
    
    Data.loc[Data[ColName].isnull(),ColName+'_random']=random_sample

#Call function to impute median value
median = Data.Age.median()
impute_nan(Data, 'Age', median)

Advantage: Not add or minimize the distortion in imputed values. Let's plot the curves to see the variance distribution.

# Code to plot distribution of vairance in data columns
fig = plt.figure()
ax = fig.add_subplot(111)
DataFrame['Age'].plot(kind='kde', ax=ax)
DataFrame['Age_random'].plot(kind='kde', ax=ax, color='red')
lines, labels = ax.get_legend_handles_labels()
ax.legend(lines, labels, loc='best')

Disadvantage: This randomness does not work in every situation.

3. Capturing NAN Values With a New Feature

Assumption: Data is NOT Missing Completely at Random(MCAR).

Description: Add a new feature to introduce some weight/importance to non-imputed and imputed observations.

Implementation: Handle Age missing values

Step 1: Replace null values with mean/mode/median or random_sample_value.

Step 2: Add a new feature/column to indicate NAN value replaced/not replaced.

Step 3: Drop the Age column and keep imputed and new importance columns.

#1. Add new column with 1 for null values and 0 for not null values
DataFrame['Age_NAN']=np.where(DataFrame['Age'].isnull(),1,0)

#2. impute random sample values in Age column - call function
impute_random_nan (DataFrame, 'Age')

#Display results
DataFrame[['Age','Age_random','Age_NAN']].head(10)

#3. Drop Age Column
DataFrame = DataFrame.drop('Age',axis=1)

Advantage: Capture the importance of missingness.

Disadvantage: Creating Additional Features(Curse of Dimensionality) e.g. if there are 10 columns have null values need to create 10 extra columns.

4. End of Tail Distribution Imputation

Assumptions: Data is NOT Missing At Random (MAR); Data is skewed at the tail-end.

Description: Take the end tail distribution value (after 3rd std. deviation of distribution curve) and replace NAN/Null values.

Implementation: Handle Age missing values

Step 1: Get end distribution value, plot distribution of data using the below code:

# Take non NAN values
NonNANData = DataFrame['Age'].dropna().sample(DataFrame['Age'].isnull().sum())

# Fit a normal distribution to the data:
mu, std = norm.fit(NonNANData)

# Plot the histogram.
plt.hist(NonNANData, bins=50, density=True, alpha=0.6, color='g')
plt.plot(x, p, 'k', linewidth=2)
plt.show()

Step 2: Take to calculate the average (mean) of extreme values (end tail values) and replace with NAN values

#function to impute NAN values with Extreme tail value

def impute_nan_TailValue(DataFrame,variable,ExtremeValue):

    DataFrame[variable+"_end_distribution"] =     DataFrame[variable].fillna(extreme)

# Calculate extreme end tail mean value
ExtremeEndTailMean = DataFrame.Age.mean()+3*DataFrame.Age.std()

# Call funtion to replace NAN values with extreme values
impute_nan_TailValue(DataFrame,'Age',ExtremeEndTailMean)

# Display histograms - Data distribution

DataFrame.Age.hist(bins=50, color = 'g')
plt.title('AGE : Normal Distribution Histogram',fontsize=15)

plt.title('Age End Distribution : Normal Distribution Histogram',fontsize=15)
DataFrame.Age_end_distribution.hist(bins=50, color = 'g')

Advantage: Capture the importance of missingness if there.

Disadvantage:

• Changes Co-variance/variance i.e distorts the original distribution; may create biased data.
• If the number of NAN values is more then it may mask True outlier values.
• If the number of NAN values is less then replace NAN values may be considered as an outlier.

5. Arbitrary Imputation

Assumptions: Data is NOT Missing At Random (MAR)

Description: Replacing NAN values with fixed arbitrary value (a random value). The arbitrary value should not frequently occur value in the dataset.

Implementation: Handle Age missing values

The arbitrary value can be any random value of choice. Sometimes it decided on the basis of domain or system knowledge.

Here we select 0 and 100 to replace NAN Values.

# Function to replace NAN values with Arbitary values
def impute_nan_ArbitaryValue(df,variable, value):
    df[variable+"_"+ str(value)]=df[variable].fillna(value)

# Call funtion to replace NAN values with arbitary values
impute_nan_ArbitaryValue(DataFrame,'Age',0)
impute_nan_ArbitaryValue(DataFrame,'Age',100)

# Display top 10 data
DataFrame[['Age','Age_0','Age_100']].head(10)

Advantage: Capture the importance of missingness.

Disadvantage:

• Changes co-variance/variance; may create outliers
• May distort the original distribution of data.
• Hard to decide which value to use.

Conclusion:

The above implementation is to explain different ways we can handle missing continuous data. The most widely used methods are Random Sample Imputation and Mean/Median/Mode imputation.

For reference: Jupyter notebook — code available at GitHub: https://github.com/GDhasade/Medium.com_Contents/blob/master/Handle_Continous_Missing_Data.ipynb

Ways to Handle Continous Column Missing Data & Its Implementations was originally published in The Startup on Medium, where people are continuing the conversation by highlighting and responding to this story.