BUSINESS INTELLIGENCE AND ANALYTICS LAB

AIDS409P

Faculty Name: Student Name: JATIN JAIN

Dr. Tripti Lamba Roll No: 20214811922
Semester: 7th
Batch: 2

Department of Artificial Intelligence Data Science

Maharaja Agrasen Institute of Technology, PSP area,
Sector-22, Rohini, New Delhi -110085

2025-2026
Rubrics Evaluation
 MAHARAJA AGRASEN INSTITUTE OF TECHNOLOGY

VISION

To nurture young minds in a learning environment of high academic value and imbibe spiritual and ethical values
with technological and management competence.

MISSION

The Institute shall endeavor to incorporate the following basic missions in the teaching methodology:
Engineering Hardware – Software Symbiosis

Practical exercises in all Engineering and Management disciplines shall be carried out by Hardware equipment as
well as the related software enabling deeper understanding of basic concepts and encouraging inquisitive nature.
Life – Long Learning

The Institute strives to match technological advancements and encourage students to keep updating their
knowledge for enhancing their skills and inculcating their habit of continuous learning.
Liberalization and Globalization

The Institute endeavors to enhance technical and management skills of students so that they are intellectually
capable and competent professionals with Industrial Aptitude to face the challenges of globalization.
Diversification

The Engineering, Technology and Management disciplines have diverse fields of studies with different attributes.
The aim is to create a synergy of the above attributes by encouraging analytical thinking.
Digitization of Learning Processes

The Institute provides seamless opportunities for innovative learning in all Engineering and Management
disciplines through digitization of learning processes using analysis, synthesis, simulation, graphics, tutorials and
related tools to create a platform for multi- disciplinary approach.
Entrepreneurship

The Institute strives to develop potential Engineers and Managers by enhancing their skills and research capabilities
so that they become successfully entrepreneurs and responsible citizens.
 INDEX

Experiment Date Experiment Name Marks (0-3) Total Signature

No. Mark
s (15)

R1 R2 R3 R4 R5

1. To provide students with

hands-on experience in
applying data cleaning and
preprocessing techniques for
business analytics

2. To develop skills in exploring

and analyzing data using
exploratory data analysis
methods for market research.

3. To understand and apply

predictive modeling
techniques, such as regression
analysis, for business
analytics.

4. To segment customers and

perform cluster analysis to gain
insights for targeted marketing
strategies.

5. To forecast future sales using

time series analysis and
evaluate the accuracy of the
predictions.

6. To mine association rules

from transactional data for
market basket analysis and
cross-selling opportunities.

7. To analyze customer sentiment

from text data and derive
insights for improving
products and services.
8. To build decision tree and
random forest models for
predicting customer churn and
identify factors influencing it.

9. To implement recommender
systems for personalized
product recommendations
based on user preferences.

10. To create interactive

dashboards and reports using
Power BI for effective
communication and decision-
making in business
intelligence.
 PRACTICAL – 1
AIM - To provide students with hands-on experience in applying data cleaning and preprocessing
techniques for business analytics.

THEORY –

ALGORITHM –
CODE –
import pandas as pd
import numpy as np

# 1. Create a sample "dirty" dataset

data = {'EmployeeID': ['A1', 'A2', 'A3', 'A1', 'A5', 'A6'],
'Department': ['Sales', 'IT', 'Sales', 'Sales', 'HR', 'IT'],
'YearsExperience': [5, 3, [Link], 5, 8, 2]}
df = [Link](data)
print("--- Original Data ---")
print(df)

# 2. Handle duplicates
df = df.drop_duplicates()

# 3, 4, 5. Handle missing 'YearsExperience'

mean_experience = df['YearsExperience'].mean()
df['YearsExperience'] = df['YearsExperience'].fillna(mean_experience)
print(f"\nMissing 'YearsExperience' filled with mean:
{mean_experience:.1f}")

# 6. Display cleaned data

print("\n--- Cleaned Data ---")
print(df)

OUTPUT –
--- Original Data ---
EmployeeID Department YearsExperience
0 A1 Sales 5.0
1 A2 IT 3.0
2 A3 Sales NaN
3 A1 Sales 5.0
4 A5 HR 8.0
5 A6 IT 2.0

Missing 'YearsExperience' filled with mean: 4.5

--- Cleaned Data ---

EmployeeID Department YearsExperience
0 A1 Sales 5.0
1 A2 IT 3.0
2 A3 Sales 4.5
4 A5 HR 8.0
5 A6 IT 2.0

VIVA – VOCE
1. Q: Why is data cleaning important for business?

A: It ensures that business decisions are based on accurate and reliable information.

2. Q: What is "imputation"?

A: The process of replacing missing data (like NaN) with a substitute value.
3. Q: What is a common way to fill missing numerical data?

A: Replace the missing values with the mean (average) value of that column.

4. Q: What Pandas function removes duplicate rows?

A: The .drop_duplicates() method.

5. Q: How can you find the total number of missing values in each column?

A: By using [Link]().sum().
 PRACTICAL – 2
AIM - To develop skills in exploring and analyzing data using exploratory data analysis methods
for market research.

THEORY –

ALGORITHM –
CODE –
import pandas as pd
import [Link] as plt

# 1. Sample data for market research

data = {'CustomerID': [1, 2, 3, 4, 5, 6],
'Age': [25, 32, 45, 28, 55, 38],
'Annual Income (k$)': [40, 55, 80, 50, 110, 60],
'Spending Score (1-100)': [70, 40, 60, 45, 80, 50]}
df = [Link](data)

# 2. Get statistical summary

print("--- Data Summary (describe) ---")
print([Link]())

# 3. Get correlation
correlation = df['Annual Income (k$)'].corr(df['Spending Score (1-100)'])
print(f"\nCorrelation (Income vs. Spending): {correlation:.2f}")

# 4. Visualize 'Age' distribution

[Link](df['Age'], bins=5, edgecolor='black')
[Link]('Distribution of Customer Age')
[Link]('Age')
[Link]('Number of Customers')
[Link]()

OUTPUT –
--- Data Summary (describe) ---
CustomerID Age Annual Income (k$) Spending Score (1-100)
count 6.000000 6.000000 6.000000 6.000000
mean 3.500000 37.166667 65.833333 57.500000
std 1.870829 11.391517 26.289859 15.921683
min 1.000000 25.000000 40.000000 40.000000
25% 2.250000 29.000000 51.250000 46.250000
50% 3.500000 35.000000 57.500000 55.000000
75% 4.750000 43.250000 75.000000 67.500000
max 6.000000 55.000000 110.000000 80.000000

Correlation (Income vs. Spending): 0.69

VIVA - VOCE

1. Q: What is the main purpose of EDA?

A: To summarize data, find patterns, and gain initial insights before formal modeling.

2. Q: What Pandas function gives a quick statistical overview (mean, median, etc.)?

A: The .describe() method.

3. Q: What is a histogram used for?

A: To visualize the frequency distribution of a single numerical variable (like 'Age').

4. Q: What does a correlation of 0.69 (like in the output) suggest?

A: A moderately strong positive relationship (as income rises, spending tends to rise).

5. Q: What plot is best for seeing the relationship between two numerical variables?

A: A scatter plot.
 PRACTICAL – 3
AIM - To understand and apply predictive modeling techniques, such as regression analysis, for
business analytics.

THEORY –

ALGORITHM –
CODE –
import numpy as np
from sklearn.model_selection import train_test_split
from sklearn.linear_model import LinearRegression
from [Link] import r2_score

# 1. Sample data: Ad Spend (X) vs. Sales (y)

X = [Link]([100, 200, 300, 400, 500]).reshape(-1, 1)
y = [Link]([50, 70, 90, 110, 130])

# 2. Split the data

X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2,
random_state=42)

# 3. Create the model

model = LinearRegression()

# 4. Train the model

[Link](X_train, y_train)

# 5. Make predictions
y_pred = [Link](X_test)

# 6. Evaluate (R-squared: 1.0 = perfect fit)

print(f"R-squared score: {r2_score(y_test, y_pred):.2f}")

# 7. Predict sales for a new ad spend of $600

new_ad_spend = [Link]([[600]])
predicted_sales = [Link](new_ad_spend)
print(f"Predicted sales for $600 ad spend: ${predicted_sales[0]:.2f}")

OUTPUT –
R-squared score: 1.00
Predicted sales for $600 ad spend: $150.00

VIVA – VOCE -

1. Q: What is regression used to predict in business?

A: A continuous numerical value, like sales, stock prices, or customer spending.

2. Q: What is the "target" variable?

A: The main variable we are trying to predict (e.g., 'Sales').

3. Q: Why do we split data into training and testing sets?

A: To check if the model can make accurate predictions on new data it has never seen.

4. Q: What does the .fit() method do?

A: It trains the model by learning the mathematical relationship between X and y.

5. Q: What is a perfect R-squared score, and what does it mean?

A: A score of 1.0, which means the model perfectly explains all the variations in the
data.
 PRACTICAL – 4
AIM - To segment customers and perform cluster analysis to gain insights for targeted marketing
strategies.

THEORY –

ALGORITHM –
CODE –
import pandas as pd
from [Link] import KMeans
from [Link] import StandardScaler

# 1. Sample customer data

data = {'Income (k$)': [15, 16, 20, 25, 30, 70, 72, 80, 85, 120],
'Spending (1-100)': [80, 85, 6, 9, 12, 88, 90, 75, 10, 80]}
df = [Link](data)

# 2. Scale the data

scaler = StandardScaler()
df_scaled = scaler.fit_transform(df)

# 3, 4. Create and 5. fit the model (K=4)

kmeans = KMeans(n_clusters=4, n_init=10, random_state=42)
[Link](df_scaled)

# 6, 7. Assign labels back to the original data

df['Cluster'] = kmeans.labels_
print("--- Customers with their Segments ---")
print(df)

OUTPUT –
--- Customers with their Segments ---
Income (k$) Spending (1-100) Cluster
0 15 80 1
1 16 85 1
2 20 6 2
3 25 9 2
4 30 12 2
5 70 88 0
6 72 90 0
7 80 75 0
8 85 10 2
9 120 80 3

VIVA - VOCE

1. Q: What is customer segmentation?

A: The process of dividing customers into distinct groups based on similar traits.

2. Q: Why is K-Means called "unsupervised"?

A: Because we don't provide the "correct answers" or labels; the algorithm finds the
groups on its own.

3. Q: What does 'K' in K-Means represent?

A: The number of clusters (segments) we want the algorithm to find.

4. Q: Why do we scale data before clustering?

 A: To ensure all features are treated equally and one doesn't (like high-value 'Income')
dominate the others.

5. Q: How does this help marketing?

A: It allows a company to send targeted (and more effective) ads to each specific
cluster.
 PRACTICAL – 5
AIM - To forecast future sales using time series analysis and evaluate the accuracy of the
predictions.

THEORY –

ALGORITHM –
CODE –
import pandas as pd
from [Link] import ARIMA

# 1. Sample monthly sales data

data = [200, 210, 205, 220, 230, 235, 225, 240, 250, 260, 245, 270]
index = pd.date_range(start='2024-01-01', periods=12, freq='MS') # MS =
Month Start
sales_ts = [Link](data, index=index)
print("--- Last 3 Months of Sales ---")
print(sales_ts.tail(3))

# 2. Define model order (p=1, d=1, q=1)

# (This order is often found through a more complex analysis)
order = (1, 1, 1)

# 3. Create the model

model = ARIMA(sales_ts, order=order)

# 4. Fit the model

model_fit = [Link]()

# 5. Forecast the next 3 months

forecast = model_fit.forecast(steps=3)

print("\n--- Sales Forecast for Next 3 Months ---")

print(forecast)

OUTPUT
--- Last 3 Months of Sales ---
2024-10-01 260
2024-11-01 245
2024-12-01 270
Freq: MS, dtype: int64

--- Sales Forecast for Next 3 Months ---

2025-01-01 271.026742
2025-02-01 275.549117
2025-03-01 278.411641
Freq: MS, Name: predicted_mean, dtype: float64

VIVA - VOCE

1. Q: What is a time series?

A: A set of data points collected in chronological (time) order.

2. Q: Why is sales forecasting important for a business?

A: It helps in managing inventory, planning budgets, and making strategic decisions.

3. Q: What does ARIMA stand for?

A: AutoRegressive Integrated Moving Average.

4. Q: What does the 'd' parameter in ARIMA's (p,d,q) order represent?

A: "Integrated," which refers to the differencing (d) needed to make the data
stationary.

5. Q: What statsmodels method is used to generate the future predictions?

A: The .forecast() method.

 PRACTICAL – 6
AIM - To mine association rules from transactional data for market basket analysis and cross-
selling opportunities.

THEORY –

ALGORITHM –
CODE –
import pandas as pd
from [Link] import TransactionEncoder
from mlxtend.frequent_patterns import apriori, association_rules

# 1. Sample transaction data

transactions = [
['Milk', 'Bread', 'Eggs'],
['Milk', 'Bread', 'Diapers', 'Beer'],
['Bread', 'Eggs', 'Juice'],
['Milk', 'Bread', 'Diapers', 'Beer'],
['Milk', 'Eggs', 'Juice']
]

# 2. One-hot encode the data

te = TransactionEncoder()
te_ary = [Link](transactions).transform(transactions)
df = [Link](te_ary, columns=te.columns_)

# 3. Find frequent itemsets (min support = 40%)

frequent_itemsets = apriori(df, min_support=0.4, use_colnames=True)

# 4. Generate rules (min confidence = 70%)

rules = association_rules(frequent_itemsets, metric="confidence",
min_threshold=0.7)

print("--- Generated Association Rules ---")

# Show the "if" part (antecedents) and "then" part (consequents)
print(rules[['antecedents', 'consequents', 'support', 'confidence',
'lift']])

OUTPUT
--- Generated Association Rules ---
antecedents consequents support confidence lift
0 (Milk) (Bread) 0.6 0.750000 0.937500
1 (Bread) (Milk) 0.6 0.750000 0.937500
2 (Diapers) (Beer) 0.4 1.000000 2.500000
3 (Beer) (Diapers) 0.4 1.000000 2.500000
4 (Diapers, Milk) (Beer) 0.4 1.000000 2.500000
5 (Beer, Milk) (Diapers) 0.4 1.000000 2.500000
6 (Diapers, Bread) (Beer) 0.4 1.000000 2.500000
7 (Beer, Bread) (Diapers) 0.4 1.000000 2.500000
VIVA - VOCE

1. Q: What is the goal of Market Basket Analysis?

A: To find relationships and co-occurrence patterns between items in transaction data.

2. Q: What is an association rule?

A: An "if-then" statement showing the likelihood of buying one item given you
bought another.

3. Q: What is Support?

A: The percentage of total transactions that contain a specific itemset.

4. Q: What is Confidence?

A: The probability that a customer will buy item B, given that they bought item A.

5. Q: What does a Lift value greater than 1 mean?

A: It means the two items are more likely to be bought together than by random
chance.
 PRACTICAL – 7
AIM - To analyze customer sentiment from text data and derive insights for improving products
and services.

THEORY –

ALGORITHM –
CODE –
from [Link] import SentimentIntensityAnalyzer

# 1. Create the analyzer

analyzer = SentimentIntensityAnalyzer()

# 2. Sample customer reviews

reviews = [
"This new update is fantastic! Love the features.",
"The app keeps crashing after 5 minutes. Very frustrating.",
"The product is okay, not great, but not terrible either.",
"I HATE the new design. It's so confusing!"
]

print("--- Customer Sentiment Analysis ---")

# 3, 4, 5. Loop, analyze, and classify
for sentence in reviews:
vs = analyzer.polarity_scores(sentence)
compound = vs['compound']

sentiment = "Neutral"
if compound >= 0.05:
sentiment = "Positive"
elif compound <= -0.05:
sentiment = "Negative"

print(f"\nReview: {sentence}")
print(f"Sentiment: {sentiment} (Score: {compound})")

OUTPUT
--- Customer Sentiment Analysis ---

Review: This new update is fantastic! Love the features.

Sentiment: Positive (Score: 0.8439)

Review: The app keeps crashing after 5 minutes. Very frustrating.

Sentiment: Negative (Score: -0.6 frustration.)

Review: The product is okay, not great, but not terrible either.
Sentiment: Negative (Score: -0.4703)

Review: I HATE the new design. It's so confusing!

Sentiment: Negative (Score: -0.8569)
VIVA - VOCE

1. Q: What is sentiment analysis?

A: The process of using NLP to determine the emotional tone (positive, negative, or
neutral) of text.

2. Q: Why is this useful for a business?

A: It helps to quickly understand customer opinions from reviews or social media at a

large scale.

3. Q: What is VADER?

A: A pre-trained sentiment analysis tool that is good at understanding social media

text, slang, and emojis.

4. Q: What does the "compound" score represent?

A: A single, normalized score from -1 (very negative) to +1 (very positive)

summarizing the text.

5. Q: What compound score would a neutral review have?

A: A score near 0 (e.g., between -0.05 and 0.05).

 PRACTICAL – 8
AIM - To build decision tree and random forest models for predicting customer churn and identify
factors influencing it.

THEORY –

ALGORITHM –
CODE –
import pandas as pd
from sklearn.model_selection import train_test_split
from [Link] import DecisionTreeClassifier
from [Link] import RandomForestClassifier
from [Link] import accuracy_score

# 1. Sample churn data

data = {'Tenure (Months)': [1, 15, 2, 24, 8, 30],
'MonthlyBill ($)': [50, 70, 45, 100, 60, 105],
'Churn': ['Yes', 'No', 'Yes', 'No', 'Yes', 'No']}
df = [Link](data)

# Define features (X) and target (y)

X = df[['Tenure (Months)', 'MonthlyBill ($)']]
y = df['Churn']

# 2. Split data
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.3,
random_state=42)

# 3, 4, 5. Decision Tree
dt = DecisionTreeClassifier(random_state=42)
[Link](X_train, y_train)
dt_pred = [Link](X_test)
print(f"Decision Tree Accuracy: {accuracy_score(y_test, dt_pred):.2f}")

# 6, 7, 8. Random Forest
rf = RandomForestClassifier(random_state=42)
[Link](X_train, y_train)
rf_pred = [Link](X_test)
print(f"Random Forest Accuracy: {accuracy_score(y_test, rf_pred):.2f}")

OUTPUT
Decision Tree Accuracy: 0.50
Random Forest Accuracy: 1.00
VIVA - VOCE

1. Q: What is customer churn?

A: The event of a customer ending their relationship with a company.

2. Q: Why is it important to predict churn?

A: It's cheaper for a business to keep an existing customer than to get a new one.

3. Q: What is a Decision Tree?

A: A predictive model that uses a flowchart-like series of if-then rules.

4. Q: What is a Random Forest?

A: An "ensemble" model that combines the predictions of many individual decision

trees.
5. Q: Why is a Random Forest often more accurate than one Decision Tree?

A: It reduces overfitting (errors) by averaging the "votes" of many diverse trees

 PRACTICAL – 9
AIM - To implement recommender systems for personalized product recommendations based on
user preferences.

THEORY –

ALGORITHM –
CODE –
import pandas as pd
import numpy as np

# 1. Utility Matrix: Users x Items (Movies)

data = {'User A': [5, 4, 1, [Link]],
'User B': [4, 5, 1, 2],
'User C': [[Link], 2, 5, 4],
'User D': [5, 4, [Link], 1]}
df = [Link](data, index=['Inception', 'The Matrix', 'Oppenheimer',
'Barbie'])

print("--- User-Item Ratings ---")

print(df)

# 2. Find users similar to 'User A'

similar_users = [Link](df['User A'])
print("\n--- Similarity to User A ---")
print(similar_users.sort_values(ascending=False))

# 3, 4, 5, 6. Make a recommendation
target_user = 'User A'
most_similar_user = 'User D' # From the output
# Get items rated by the similar user
similar_user_ratings = df[most_similar_user]
# Get items the target user has NOT seen
unseen_items = df[target_user][df[target_user].isnull()].index
# Recommend unseen items that the similar user liked
recommendations =
similar_user_ratings[unseen_items].sort_values(ascending=False)
print(f"\n--- Recommendations for {target_user} ---")
print(recommendations)

OUTPUT
--- User-Item Ratings ---
User A User B User C User D
Inception 5.0 4.0 NaN 5.0
The Matrix 4.0 5.0 2.0 4.0
Oppenheimer 1.0 1.0 5.0 NaN
Barbie NaN 2.0 4.0 1.0

--- Similarity to User A ---

User A 1.000000
User D 1.000000
User B 0.981981
User C -0.981981
dtype: float64

--- Recommendations for User A ---

Barbie 1.0
Name: User D, dtype: float64
VIVA - VOCE

1. Q: What is the main goal of a recommender system?

A: To predict a user's preference and suggest relevant items.

2. Q: What is Collaborative Filtering?

A: A method that makes recommendations based on the ratings of "similar" users.

3. Q: What is a "utility matrix"?

A: A table (DataFrame) that shows the ratings given by users (rows) to items
(columns).

4. Q: What does [Link]() calculate in this context?

A: It calculates the similarity (correlation) between a target user and all other users.

5. Q: How does this help a business like Amazon?

A: It drives sales by showing customers products they are highly likely to buy.
 PRACTICAL – 10
AIM - To create interactive dashboards and reports using Power BI for effective communication
and decision-making in business intelligence.

THEORY –

ALGORITHM –
CODE –

Code (N/A - Software Process)

(This experiment is performed using the Power BI software, not Python

code.)

1. Open Power BI Desktop.

2. Click 'Get Data' -> 'CSV' and load your sales data.
3. Go to the 'Report' view (the canvas).
4. From the 'Visualizations' pane, click the 'Slicer' icon.
5. From the 'Data' pane, drag your 'Year' column onto the slicer.
6. From 'Visualizations', click the 'Bar chart' icon.
7. Drag 'Sales' to the 'Y-axis' and 'Region' to the 'X-axis'.
8. Click a year (e.g., "2023") on the slicer.
9. Observe that the bar chart instantly updates to show sales for 2023.

OUTPUT –

VIVA - VOCE

1. Q: What is a Power BI dashboard?

A: An interactive, visual report that provides an at-a-glance summary of key business

data.

2. Q: What is the main difference between a dashboard and a static report (like a PDF)?

A: A dashboard is interactive, allowing users to filter and explore the data

themselves.

3. Q: What is a "Slicer" in Power BI?

 A: A visual, on-canvas filter (like a list of years or regions) that users can click to
filter the report.

4. Q: What does "drill down" mean?

A: To click on a high-level data point (like "2024") to see the lower-level details (like
"Q1", "Q2", "Q3", "Q4").

5. Q: How do dashboards help managers make decisions?

A: They provide fast, real-time access to business performance, allowing for quicker
insights and problem-solving.