SrishylaEducationTrust®

GMUNIVERSITY
(EstablishedundertheKarnatakaStateActNo.19of2023) Post Box
No. 4, PB Road, Davanagere - 577006

Subject: Data Science with Business Intelligence

MODULE 3

3. Data cleaning:

Data collection and cleaning are fundamental steps in any data analysis or visualization
project.
Before visualizing data, it’s essential to ensure that the data is:

 Accurate (free from errors)

 Consistent
 Complete
 Formatted properly

Visualization tools such as Tableau, Power BI, or Google Data Studio can be used not only
for visualization but also for data preparation — including importing, transforming, and
cleaning data.

3.1 Steps in Data Cleaning

Step 1: Data Collection

Definition:
Data collection is the process of gathering raw data from different internal or external
sources for analysis.

Sources of Data:

 Files: CSV, Excel, JSON

 Databases: MySQL, SQL Server, PostgreSQL
 APIs/Web Data: Google Analytics, social media data, online surveys
 Manual Entry: Forms, questionnaires

In Visualization Tools:

 In Tableau: Use “Connect to Data” → choose source type (Excel, database, etc.)
 In Power BI: Use “Get Data” → connect to Excel, web, or SQL database

Example:
Connecting Power BI to an Excel sheet containing monthly sales data.

Dept. of AIMLPage 1
 SrishylaEducationTrust®

GMUNIVERSITY
(EstablishedundertheKarnatakaStateActNo.19of2023) Post Box
No. 4, PB Road, Davanagere - 577006

Step 3: Data Cleaning / Preprocessing

Data cleaning is the process of correcting, removing, or transforming inaccurate,

incomplete, or irrelevant data to improve quality.

Task Description Example

Removing Duplicates Deleting repeated entries Two identical sales transactions
Handling Missing Filling, replacing, or removing null
Replace missing sales values with 0
Values values
Convert “Date” field from text to
Data Type Correction Ensuring correct data types
date format
Ensuring consistency in naming “NY” and “New York” standardized
Data Standardization
or formatting to “New York”
Removing or adjusting extreme Removing sales amount >
Outlier Handling
values ₹10,00,000
Filtering Irrelevant Removing unnecessary columns
Dropping unused fields like “Notes”
Data or rows
“Order Date” should be before
Data Validation Checking logical consistency
“Delivery Date”

Step 4: Data Transformation

Purpose: To make data analysis-ready and compatible with visualizations.

Typical Transformations:

 Creating calculated fields (e.g., Profit = Sales – Cost)

 Grouping or categorizing data (e.g., by region or product type)
 Aggregating data (e.g., total sales per month)
 Merging multiple datasets

In Tools:

 Power BI: Use Power Query Editor → “Transform Data”

 Tableau: Use Data Interpreter or Calculated Fields

Step 5: Verification & Validation

Before creating visuals, verify that:

 Data columns are accurate and consistent.

Dept. of AIMLPage 2
 SrishylaEducationTrust®

GMUNIVERSITY
(EstablishedundertheKarnatakaStateActNo.19of2023) Post Box
No. 4, PB Road, Davanagere - 577006

 Total or average values match expected results.

 Missing or incorrect records have been handled properly.

3.2 Introduction to Descriptive Analytics

Descriptive Analytics is the first stage of data analytics that focuses on summarizing and
interpreting historical data to understand what has happened in the past.

It uses statistical techniques, data aggregation, and visualization to describe trends,

patterns, and insights from existing data — not to predict or explain causes.

Descriptive analytics answers the question — “What happened?”

3.2.1 Purpose of Descriptive Analytics

 To summarize large datasets in an understandable way.

 To identify trends, patterns, and relationships in historical data.
 To provide insights for business decision-making.
 To serve as a foundation for advanced analytics (diagnostic, predictive, prescriptive).

3.2.2 Process of Descriptive Analytics

The process generally follows four key steps:

Step 1: Data Collection

 Gather data from multiple sources (databases, files, sensors, APIs).

 Ensure accuracy, completeness, and consistency.

Step 2: Data Cleaning and Preparation

 Remove duplicates, handle missing values, and correct errors.

 Transform data into usable formats.

Step 3: Data Aggregation and Analysis

 Summarize data using measures like:

o Count, Sum, Average (Mean)
o Minimum, Maximum
o Percentage, Proportion
 Group data by categories (e.g., sales by region or month).

Step 4: Data Visualization and Reporting

 Use charts, dashboards, and tables to communicate results.

Dept. of AIMLPage 3
 SrishylaEducationTrust®

GMUNIVERSITY
(EstablishedundertheKarnatakaStateActNo.19of2023) Post Box
No. 4, PB Road, Davanagere - 577006

 Tools: Power BI, Tableau, Excel Charts, Google Data Studio.

3.2.3 Common Techniques Used in Descriptive Analytics

Technique Description Example

Data
Computing totals, averages, counts Total sales per month
Summarization
Using charts/graphs to represent Line chart showing monthly
Data Visualization
data visually revenue trend
Cross-tabulation Comparing two or more variables Sales by product and region
Identifying historical patterns over Tracking customer growth per
Trend Analysis
time quarter
Statistical Mean, median, mode, variance,
Average age of customers
Measures standard deviation
Correlation Checking relationships between Relationship between advertising
Analysis variables spend and sales

3.2.4 Applications / Use Cases

Industry Use Case Example

Retail Analyze monthly sales trends and product performance

Finance Summarize customer transactions, revenue, or expenses

Healthcare Report on patient visits, treatment outcomes, or costs

Marketing Track campaign performance and customer engagement

Education Measure student performance and attendance trends

Dept. of AIMLPage 4
 SrishylaEducationTrust®

GMUNIVERSITY
(EstablishedundertheKarnatakaStateActNo.19of2023) Post Box
No. 4, PB Road, Davanagere - 577006

3.2.5 Advantages of Descriptive Analytics

 Provides quick insights into business performance.

 Helps identify trends and patterns.
 Supports data-driven decision-making.
 Simplifies complex data for easy understanding.
 Acts as a base for advanced analytics (predictive, prescriptive).

3.2.6 Limitations

 Focuses only on past data (no predictions).

 Cannot identify why something happened (diagnostic analytics does).
 May miss hidden correlations or causes.
 Insights depend heavily on data quality.

3.2.7 Relationship with Other Analytics Types

Analytics Type Question Answered Focus

Descriptive What happened? Past data
Diagnostic Why did it happen? Causes and correlations
Predictive What is likely to happen? Forecasting future outcomes
Prescriptive What should we do? Decision optimization

3.3 Measures of Central Tendency

Measures of Central Tendency are statistical tools used to describe the center or average
value of a dataset.

They give a single representative value that summarizes an entire distribution of data.

The three most common measures are:

1. Mean
2. Median
3. Mode

Dept. of AIMLPage 5
 SrishylaEducationTrust®

GMUNIVERSITY
(EstablishedundertheKarnatakaStateActNo.19of2023) Post Box
No. 4, PB Road, Davanagere - 577006

3.3.1 Importance of Central Tendency

 Simplifies large data into a single value for easy understanding.

 Helps in comparing different datasets.
 Useful in data analysis, economics, business, and research.
 Acts as the foundation for advanced statistical methods.

1. Mean
The mean is the arithmetic average of a set of values.
It is calculated by dividing the sum of all observations by the number of observations.

Where:

 ∑X = Sum of all observations

 N = Total number of observations

Merits:

 Simple to calculate and understand.

 Uses all observations in the dataset.
 Suitable for further statistical analysis.

Demerits:

 Affected by extreme values (outliers).

 Cannot be used for qualitative data (e.g., colors, gender).
 Misleading if data is skewed.

2. Median

The median is the middle value of an ordered dataset (arranged in ascending or

descending order).
It divides the dataset into two equal halves.

Steps to Find Median:

1. Arrange data in ascending order.

2. Identify the middle position

Dept. of AIMLPage 6
 SrishylaEducationTrust®

GMUNIVERSITY
(EstablishedundertheKarnatakaStateActNo.19of2023) Post Box
No. 4, PB Road, Davanagere - 577006

Merits:

 Not affected by extreme values.

 Easy to understand and compute.
 Useful for skewed distributions (e.g., income data).

Demerits:

 Does not consider all values in the dataset.

 Not suitable for further mathematical operations.
 May be difficult to determine for large or grouped data.

3. Mode

The mode is the most frequently occurring value in a dataset.

Special Cases:

 If all values occur with the same frequency → No mode.

 If two values occur most frequently → Bimodal.
 If more than two → Multimodal.

Merits:

 Simple and easy to identify.

 Can be used for qualitative data (e.g., most common brand).
 Not affected by extreme values.

Demerits:

 May not be unique (more than one mode possible).

 Not useful for further statistical analysis.

Dept. of AIMLPage 7
 SrishylaEducationTrust®

GMUNIVERSITY
(EstablishedundertheKarnatakaStateActNo.19of2023) Post Box
No. 4, PB Road, Davanagere - 577006
 Sometimes poorly represents the data if frequencies are close.

Relationship between Mean, Median, and Mode

 For a symmetrical distribution:Mean=Median=Mode

 For a positively skewed distribution (right-skewed):Mean>Median>Mode
 For a negatively skewed distribution (left-skewed):Mean<Median<Mode

3.4 MEASURES OF DISPERSION

 Dispersion refers to the degree to which data values are spread out or scattered
around a central value (like the mean or median).
 It tells how much variation or diversity exists in a dataset.
 While measures of central tendency (mean, median, mode) show the center of the
data, measures of dispersion show the spread.

3.4.1 Types of Measures of Dispersion

Common measures include:

 Range
 Variance
 Standard Deviation

1. Range

The simplest measure of [Link] is the difference between the largest and smallest
observation in a dataset.

Range=Maximum value−Minimum value

Advantages:

 Simple and easy to calculate.

 Useful for quick comparisons.

Limitations:

 Depends only on extreme values (may ignore middle data).

 Affected by outliers.
 Not reliable for large or skewed datasets.

Dept. of AIMLPage 8
 SrishylaEducationTrust®

GMUNIVERSITY
(EstablishedundertheKarnatakaStateActNo.19of2023) Post Box
No. 4, PB Road, Davanagere - 577006

2. Variance

Variance measures the average of squared deviations from the mean. It indicates how far
each data point is from the mean.

here
xi​= each observation
xˉ = mean of the data
N = number of observations (for population)

3. Standard Deviation (SD)

The square root of variance. It measures the average amount by which data values differ
from the mean.

3.5 CORRELATION & COVARIANCE

In statistics, we often study two or more variables to understand how they are related to
each other.
For example:
 Height and Weight
 Income and Expenditure
 Temperature and Ice cream sales

Two key tools for studying relationships between variables are Covariance and Correlation.

Covariance

Covariance measures the degree to which two variables vary together.

It indicates whether an increase in one variable corresponds to an increase or decrease in
another variable.

Dept. of AIMLPage 9
 SrishylaEducationTrust®

GMUNIVERSITY
(EstablishedundertheKarnatakaStateActNo.19of2023) Post Box
No. 4, PB Road, Davanagere - 577006

Where:

 Y​ i​: individual data values

 Xˉ,Yˉ: means of X and Y
 N: total number of observations

Interpretation of Covariance

Covariance Value Meaning

Positive (+) As X increases, Y also increases (direct relationship)
Negative (−) As X increases, Y decreases (inverse relationship)
Zero (0) No linear relationship between X and Y

Correlation

Correlation measures both the direction and strength of a linear relationship** between
two variables.
It is the standardized form of covariance.

Where:

 r = correlation coefficient
 σX​,σY​= standard deviations of X and Y

3.5.1 Types of Correlation

1. Positive Correlation:
Both variables increase or decrease together.
Example: Height and Weight.
2. Negative Correlation:
One variable increases while the other decreases.
Example: Price and Demand.

Dept. of AIMLPage 10
 SrishylaEducationTrust®

GMUNIVERSITY
(EstablishedundertheKarnatakaStateActNo.19of2023) Post Box
No. 4, PB Road, Davanagere - 577006
3. Zero Correlation:
No linear relation between the two variables.
Example: Shoe size and Intelligence.

3.5.2 Relationship Between Covariance and Correlation

3.5.3 Applications

a. Covariance Applications

 Used in portfolio theory in finance to measure how assets move together.

 Helps in understanding multivariate relationships.
 Used in machine learning and data analysis for covariance matrices.

b. Correlation Applications

 Economics: Relationship between income & expenditure, demand & price.

 Business & Marketing: Sales vs. advertising expenditure.
 Science & Engineering: Temperature vs. resistance, pressure vs. volume.
 Health & Psychology: Height vs. weight, stress vs. performance.
 Finance: Correlation between two stock prices or returns.

3.6 CROSS TABULATIONS – Understanding Relationships

In data analysis, we often want to understand the relationship between two or more
categorical variables (e.g., gender and product preference, education level and income
group).

A powerful way to summarize and analyze such relationships is through a Cross Tabulation,
also known as a Contingency Table.

Cross Tabulation (or Cross-tab) is a statistical tool used to analyze the relationship
between two or more categorical variables by displaying their joint frequency distribution
in a table format.

Dept. of AIMLPage 11
 SrishylaEducationTrust®

GMUNIVERSITY
(EstablishedundertheKarnatakaStateActNo.19of2023) Post Box
No. 4, PB Road, Davanagere - 577006
It shows how different categories of one variable relate to categories of another variable.

3.6.1 Types of Cross Tabulations

1. Simple Cross Tabulation:

Relationship between two categorical variables (e.g., Gender vs. Product Type).
2. Multiple Cross Tabulation:
Involves three or more variables (e.g., Gender vs. Product Type vs. Age Group).

3.6.2 Purpose and Importance

 Summarizes large data into a compact, easy-to-read form.

 Identifies relationships or associations between variables.
 Compares categories within variables (e.g., male vs. female behavior).
 Forms the basis for further statistical tests like the Chi-square test of independence.
 Helps in decision-making and market segmentation.

3.6.3 Applications of Cross Tabulation

Field Applications
Business & Studying relationships between customer demographics and buying
Marketing preferences.
Education Analyzing relationship between gender and academic performance.
Healthcare Comparing treatment outcomes across different patient groups.
Understanding associations between occupation, education, and
Social Sciences
income.
Public Policy Examining survey data on attitudes or voting behavior.

Advantages

 Simple and visual representation.

 Helps discover patterns, relationships, and trends.
 Useful for categorical and survey data.
 Works well as a foundation for deeper statistical tests.

Limitations

Dept. of AIMLPage 12
 SrishylaEducationTrust®

GMUNIVERSITY
(EstablishedundertheKarnatakaStateActNo.19of2023) Post Box
No. 4, PB Road, Davanagere - 577006
 Not suitable for continuous (numerical) data without grouping.
 Does not show cause-effect relationships — only associations.
 Large tables can be complex to interpret.
 Sensitive to sample size and missing data.

3.7 Pivot Tables – Creation & Analysis

A Pivot Table is a powerful data summarization and analysis tool used in Excel,
spreadsheets, or data analytics software (like Python’s pandas).
It helps to summarize, analyze, explore, and present large datasets by grouping and
aggregating data in a flexible and interactive way.

3.7.1 Purpose of a Pivot Table

 To summarize large amounts of data quickly.

 To identify patterns, trends, and insights.
 To compare data across multiple dimensions (e.g., sales by region, product, and
month).
 To create dynamic reports without using complex formulas.

3.7.2 Components of a Pivot Table

Component Description
Rows Represents categories or groups (e.g., Region, Product).
Columns Represents subcategories or comparison groups (e.g., Year, Quarter).
Values Numeric data to be summarized (e.g., Sum of Sales, Average Profit).
Used to display specific portions of the dataset (e.g., filter by Year or Product
Filters
Type).

3.7.3 Steps to Create a Pivot Table (in Excel or general tools)

1. Prepare Data
o Ensure the dataset is in tabular form with column headers.
o No blank rows or merged cells.
2. Insert Pivot Table
o Go to Insert → Pivot Table (in Excel).
o Select the data range and the location for the Pivot Table.
3. Design the Table
o Drag fields to Rows, Columns, Values, and Filters areas.
o Choose the appropriate aggregation function (Sum, Average, Count, etc.).
4. Analyze and Format
o Add totals, subtotals, or percentage values.
o Sort or filter results.

Dept. of AIMLPage 13
 SrishylaEducationTrust®

GMUNIVERSITY
(EstablishedundertheKarnatakaStateActNo.19of2023) Post Box
No. 4, PB Road, Davanagere - 577006
o Apply formatting or conditional formatting for better visualization.

3.7.4 Analysis Using Pivot Tables

Pivot Tables allow users to:

 Compare sales performance by region, product, or time period.

 Analyzecustomer behavior (e.g., most profitable customers).
 Identify trends and outliers using pivot charts.
 Drill down to specific details for deeper insights.
 Create interactive dashboards using slicers and filters.

3.7.5 Pivot Charts

 Pivot Charts are graphical representations of Pivot Table data.

 They automatically update when the Pivot Table changes.
 Common chart types: Column, Bar, Line, Pie, Area.

Advantages of Pivot Tables

✅ Easy to summarize large datasets

✅ Flexible and interactive analysis
✅ No need for complex formulas
✅ Quick comparison across multiple variables
✅ Integrates with visualization tools

Limitations

❌ Cannot handle unstructured or inconsistent data

❌ Performance issues with very large datasets
❌ Limited statistical functions
❌ Requires manual refresh when source data changes

Applications

 Business Analytics: Sales, revenue, and profit analysis

 Marketing: Customer segmentation and campaign performance
 Finance: Expense tracking, budgeting reports
 HR Analytics: Employee count by department or region
 Education: Student performance summaries

Dept. of AIMLPage 14
 SrishylaEducationTrust®

GMUNIVERSITY
(EstablishedundertheKarnatakaStateActNo.19of2023) Post Box
No. 4, PB Road, Davanagere - 577006

3.8 Business Metrics & KPIs

In business analytics, metrics and KPIs (Key Performance Indicators) are essential tools
used to measure performance, track progress, and evaluate success in achieving business
goals.

Quantitative measurements used to track and assess specific business

Business Metrics processes or activities. They show how a company performs in different
areas such as sales, marketing, finance, or operations.
KPIs (Key A subset of business metrics that are strategically important and
Performance directly linked to an organization’s goals or success factors. KPIs show
Indicators) whether the business is achieving its key objectives.

3.8.1 Importance of Business Metrics & KPIs

 Provide quantifiable insights into performance.

 Help in data-driven decision-making.
 Identify strengths, weaknesses, opportunities, and risks.
 Enable goal tracking and performance evaluation.
 Support continuous improvement and strategic alignment.

3.8.2 Case Study – Sales Performance Analysis

Sales Performance Analysis is the process of evaluating and understanding sales data to
measure how effectively a company is achieving its sales goals.
It involves analyzing sales trends, comparing actual performance with targets, and
identifying areas for improvement.

Sales performance analysis is a key part of business analytics — helping organizations make
data-driven decisions to improve revenue and profitability.

3.8.3 Objectives of Sales Performance Analysis

 To measure sales growth over time.

 To evaluate sales team performance by region, product, or representative.

Dept. of AIMLPage 15
 SrishylaEducationTrust®

GMUNIVERSITY
(EstablishedundertheKarnatakaStateActNo.19of2023) Post Box
No. 4, PB Road, Davanagere - 577006
 To identify best-selling products and underperforming ones.
 To understand customer buying patterns and preferences.
 To forecast future sales using historical data.
 To optimize marketing and inventory strategies.

3.8.4 Steps in Sales Performance Analysis

Step 1: Data Collection

Gather sales data from different sources:

 CRM systems
 POS (Point of Sale) records
 Invoices and billing data
 Marketing campaigns
 Customer feedback

Step 2: Data Preparation

 Clean and organize data.

 Remove duplicates or missing entries.
 Create structured tables with fields such as Product, Region, Salesperson, Month,
Revenue, Profit.

Step 3: Data Summarization (using Pivot Tables or BI Tools)

 Summarize sales by region, product, or time period.

 Calculate totals, averages, and growth rates.

Step 4: Analysis

Perform:

 Trend Analysis – Identify upward or downward sales trends.

 Comparative Analysis – Compare sales between products or regions.
 Contribution Analysis – Identify top contributors to total sales (Pareto/80-20 rule).
 Correlation Analysis – Check how sales are affected by marketing, pricing, or
discounts.

Step 5: Visualization

Represent findings through:

Dept. of AIMLPage 16
 SrishylaEducationTrust®

GMUNIVERSITY
(EstablishedundertheKarnatakaStateActNo.19of2023) Post Box
No. 4, PB Road, Davanagere - 577006
 Line charts (sales over time)
 Bar/column charts (sales by region or product)
 Pie charts (market share distribution)
 Dashboards (interactive visual summary)

Step 6: Interpretation & Decision-Making

 Identify strengths (high-performing products/regions).

 Detect weaknesses (declining products).
 Recommend actions (price adjustments, promotions, or better inventory planning).

3.8.5 Tools Used for Sales Performance Analysis

Tool Use
Excel / Google Sheets Pivot Tables, Charts, KPIs
Power BI / Tableau Dashboards and visual analytics
Python (Pandas, Matplotlib) Advanced data processing and visualization
CRM Tools (Salesforce, Zoho) Real-time tracking and reporting

Benefits of Sales Performance Analysis

✅ Identifies best-selling products and customers

✅ Tracks achievement of sales targets
✅ Helps plan inventory and marketing campaigns
✅ Improves decision-making through data insights
✅ Increases profitability and operational efficiency

Limitations

❌ Requires accurate and updated data

❌ May not capture external factors (market trends, economic changes)
❌ Analysis can be time-consuming without automation tools
❌ Overreliance on past data may affect future forecasting accuracy

4.1 Principles of Effective Data Visualization

Data Visualization is the graphical representation of data and information using charts,
graphs, maps, and dashboards. It helps to communicate insights clearly and effectively,
making complex data easier to understand and interpret.

Effective data visualization transforms raw data into meaningful visuals that help decision-
makers identify patterns, trends, and outliers quickly.

Dept. of AIMLPage 17