Data Mining Lab
Using Scilab/ MATLAB/ C/ Python/ R
1. Build a Data Warehouse and perform its operations.
import sqlite3
import pandas as pd
import numpy as np
from datetime import datetime, timedelta
[Link](1)
start = datetime(2025, 1, 1)
dates = [start + timedelta(days=int(x)) for x in [Link](0, 120, 500)]
# products and stores
products = [
{"product_id": "P001", "name": "Widget A", "category": "Widgets"},
{"product_id": "P002", "name": "Widget B", "category": "Widgets"},
{"product_id": "P003", "name": "Gadget X", "category": "Gadgets"},
{"product_id": "P004", "name": "Gadget Y", "category": "Gadgets"},
stores = [
{"store_id": "S01", "name": "Central", "region": "North"},
{"store_id": "S02", "name": "Mall", "region": "South"},
{"store_id": "S03", "name": "Outlet", "region": "East"},
rows = []
for i in range(500):
dt = dates[i]
prod = products[[Link](0, len(products))]
store = stores[[Link](0, len(stores))]
qty = [Link](1, 10)
price = round(10 + [Link]()*90, 2) # 10-100
[Link]({
"tx_id": f"T{i+1:04d}",
"tx_date": [Link]("%Y-%m-%d"),
"product_id": prod["product_id"],
"product_name": prod["name"],
"category": prod["category"],
"store_id": store["store_id"],
"store_name": store["name"],
"region": store["region"],
"quantity": qty,
"unit_price": price,
})
df_source = [Link](rows)
df_source["sales_amount"] = df_source["quantity"] * df_source["unit_price"]
df_source.to_csv("sales_source.csv", index=False)
print("Wrote sales_source.csv (synthetic)")
con = [Link]("dw_example.db")
cur = [Link]()
for t in ["fact_sales", "dim_date", "dim_product", "dim_store"]:
[Link](f"DROP TABLE IF EXISTS {t}")
[Link]("""
CREATE TABLE dim_date (
date_key INTEGER PRIMARY KEY, -- YYYYMMDD numeric key
date TEXT,
year INTEGER,
month INTEGER,
day INTEGER,
month_name TEXT,
quarter INTEGER)
""")
[Link]("""
CREATE TABLE dim_product (
product_key INTEGER PRIMARY KEY AUTOINCREMENT,
product_id TEXT UNIQUE,
product_name TEXT,
category TEXT)""")
[Link]("""
CREATE TABLE dim_store (
store_key INTEGER PRIMARY KEY AUTOINCREMENT,
store_id TEXT UNIQUE,
store_name TEXT,
region TEXT)""")
[Link]("""
CREATE TABLE fact_sales (
fact_id INTEGER PRIMARY KEY AUTOINCREMENT,
date_key INTEGER,
product_key INTEGER,
store_key INTEGER,
quantity INTEGER,
unit_price REAL,
sales_amount REAL,
FOREIGN KEY(date_key) REFERENCES dim_date(date_key),
FOREIGN KEY(product_key) REFERENCES dim_product(product_key),
FOREIGN KEY(store_key) REFERENCES dim_store(store_key)
)""")
[Link]()
date_df = [Link]({
"date": pd.to_datetime(df_source["tx_date"]).[Link]
}).drop_duplicates().sort_values("date")
date_df["date_key"] = date_df["date"].apply(lambda d: int([Link]("%Y%m%d")))
date_df["year"] = [Link](date_df["date"]).year
date_df["month"] = [Link](date_df["date"]).month
date_df["day"] = [Link](date_df["date"]).day
date_df["month_name"] = [Link](date_df["date"]).strftime("%B")
date_df["quarter"] = [Link](date_df["date"]).quarter
date_df[['date_key','date','year','month','day','month_name','quarter']].to_sql("dim_date", con,
if_exists="append", index=False)
prod_df = df_source[["product_id","product_name","category"]].drop_duplicates()
prod_df.to_sql("dim_product", con, if_exists="append", index=False)
store_df = df_source[["store_id","store_name","region"]].drop_duplicates()
store_df.to_sql("dim_store", con, if_exists="append", index=False)
dim_date = pd.read_sql("SELECT * FROM dim_date", con)
dim_prod = pd.read_sql("SELECT * FROM dim_product", con)
dim_store = pd.read_sql("SELECT * FROM dim_store", con)
src = df_source.copy()
src["date_key"] = src["tx_date"].apply(lambda s: int(pd.to_datetime(s).strftime("%Y%m%d")))
src = [Link](dim_prod[["product_key","product_id"]], on="product_id", how="left")
src = [Link](dim_store[["store_key","store_id"]], on="store_id", how="left")
fact = src[["date_key","product_key","store_key","quantity","unit_price","sales_amount"]]
fact.to_sql("fact_sales", con, if_exists="append", index=False)
[Link]()
print("DW created: dw_example.db with dims and fact_sales")
print("\n=== SQL: Total Sales by Product ===")
q1 = """
SELECT p.product_name, [Link], SUM(f.sales_amount) AS total_sales, SUM([Link]) AS total_qty
FROM fact_sales f
JOIN dim_product p ON f.product_key = p.product_key
GROUP BY p.product_name, [Link]
ORDER BY total_sales DESC
"""
for row in [Link](q1).fetchall()[:10]:
print(row)
print("\n=== SQL: Roll-up by Month (Year-Month) ===")
q2 = """
SELECT [Link], [Link], SUM(f.sales_amount) AS total_sales
FROM fact_sales f
JOIN dim_date d ON f.date_key = d.date_key
GROUP BY [Link], [Link]
ORDER BY [Link], [Link]
"""
for row in [Link](q2).fetchall():
print(row)
print("\n=== SQL: Slice (Product = 'Widget A') and Dice (Region = 'North') ===")
q3 = """
SELECT [Link], p.product_name, [Link], SUM(f.sales_amount) as total_sales
FROM fact_sales f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_store s ON f.store_key = s.store_key
WHERE p.product_name = 'Widget A' AND [Link] = 'North'
GROUP BY [Link], p.product_name, [Link]
ORDER BY [Link]
LIMIT 10
"""
for row in [Link](q3).fetchall():
print(row)
df_f = pd.read_sql("""
SELECT f.*, [Link], [Link], p.product_name, [Link], s.store_name, [Link]
FROM fact_sales f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_product p ON f.product_key = p.product_key
JOIN dim_store s ON f.store_key = s.store_key
""", con)
pivot = df_f.pivot_table(values="sales_amount", index=["year","month"], columns="product_name",
aggfunc="sum", fill_value=0)
print("\n=== Pivot table (year-month vs product) ===")
print([Link]())
top_product = df_f.groupby("product_name")["sales_amount"].sum().idxmax()
print(f"\nTop product: {top_product}\nDaily sales (first 10 rows):")
daily =
df_f[df_f["product_name"]==top_product].groupby("date_key")["sales_amount"].sum().reset_index().sort_valu
es("date_key")
print([Link](10))
[Link]()
2. Perform data preprocessing tasks and Demonstrate performing association rule mining on data sets.
import pandas as pd
import numpy as np
from mlxtend.frequent_patterns import apriori, association_rules
data = {
"Customer": ["C1","C2","C3","C4","C5","C6"],
"Age": [25, 32, [Link], 45, 900, 29], # missing + outlier
"City": ["Delhi","Mumbai","Delhi","Chennai","Delhi", None], # missing value
"Items": [
"Milk,Bread",
"Bread,Butter,Eggs",
"Milk,Eggs",
"Bread",
"Butter,Milk,Bread",
"Eggs"
]
}
df = [Link](data)
print("Original Data:\n", df, "\n")
df["Age"].fillna(df["Age"].median(), inplace=True)
# Fill missing (City) with mode
df["City"].fillna(df["City"].mode()[0], inplace=True)
print("After Filling Missing Values:\n", df, "\n")
[Link][df["Age"] > 100, "Age"] = df["Age"].median()
print("After Removing Outlier in Age:\n", df, "\n")
df["Age_norm"] = (df["Age"] - df["Age"].min()) / (df["Age"].max() - df["Age"].min())
print("After Normalization:\n", df, "\n")
df["Age_group"] = [Link](df["Age"], bins=[20,30,40,50], labels=["Young","Adult","Middle"])
print("After Binning:\n", df, "\n")
df["ItemList"] = df["Items"].apply(lambda x: [Link](","))
all_items = sorted(list(set(sum(df["ItemList"].tolist(), []))))
basket = [Link](0, index=[Link], columns=all_items)
for i in [Link]:
for item in df["ItemList"][i]:
[Link][i, item] = 1
print("Transaction Matrix (One-Hot):\n", basket, "\n")
frequent_items = apriori(basket, min_support=0.3, use_colnames=True)
print("Frequent Itemsets:\n", frequent_items, "\n")
rules = association_rules(frequent_items, metric="confidence", min_threshold=0.4)
print("Association Rules:\n", rules)
3. Demonstrate performing classification on data sets.
# INSTALL FIRST (if needed):
# pip install pandas scikit-learn matplotlib
from [Link] import load_iris
from sklearn.model_selection import train_test_split
from [Link] import DecisionTreeClassifier, plot_tree
from [Link] import accuracy_score, confusion_matrix, classification_report
import [Link] as plt
import pandas as pd
iris = load_iris()
X = [Link] # features
y = [Link] # target classes
df = [Link](X, columns=iris.feature_names)
df['species'] = [Link]
print("Sample Data:\n", [Link](), "\n")
X_train, X_test, y_train, y_test = train_test_split(
X, y, test_size=0.3, random_state=42
)
model = DecisionTreeClassifier(criterion="gini", random_state=0)
[Link](X_train, y_train)
y_pred = [Link](X_test)
accuracy = accuracy_score(y_test, y_pred)
cm = confusion_matrix(y_test, y_pred)
report = classification_report(y_test, y_pred)
print("Accuracy:", accuracy)
print("\nConfusion Matrix:\n", cm)
print("\nClassification Report:\n", report)
[Link](figsize=(12, 8))
plot_tree(model, feature_names=iris.feature_names, class_names=iris.target_names, filled=True)
[Link]("Decision Tree for Iris Classification")
[Link]()
4. Demonstrate performing clustering on data sets.
from [Link] import load_iris
from [Link] import KMeans
from [Link] import silhouette_score
import pandas as pd
import [Link] as plt
import seaborn as sns
iris = load_iris()
X = [Link]
df = [Link](X, columns=iris.feature_names)
print("Sample Data:\n", [Link](), "\n")
k = 3 # Number of clusters (iris has 3 species)
kmeans = KMeans(n_clusters=k, random_state=42)
clusters = kmeans.fit_predict(X)
df["cluster"] = clusters
print("Clustered Data:\n", [Link](), "\n")
sil_score = silhouette_score(X, clusters)
print("Silhouette Score:", sil_score, "\n")
[Link](figsize=(8,5))
[Link](
x=df['petal length (cm)'],
y=df['petal width (cm)'],
hue=df['cluster'],
palette='viridis',
s=80
)
[Link]("K-Means Clustering (Iris Petal Length vs Width)")
[Link]("Petal Length (cm)")
[Link]("Petal Width (cm)")
[Link](title="Cluster")
[Link]()
print("Cluster Centroids:\n", kmeans.cluster_centers_)
5. Demonstrate performing Regression on data sets.
import pandas as pd
import numpy as np
from sklearn.model_selection import train_test_split
from sklearn.linear_model import LinearRegression
from [Link] import mean_squared_error, r2_score
import [Link] as plt
data = {
"Area": [800, 900, 1000, 1100, 1200, 1500, 1800, 2000],
"Price": [75, 85, 95, 110, 120, 155, 180, 200] # in lakhs
}
df = [Link](data)
print("Dataset:\n", df, "\n")
X = df[["Area"]] # Features
y = df["Price"] # Target variable
X_train, X_test, y_train, y_test = train_test_split(
X, y, test_size=0.2, random_state=42
)
model = LinearRegression()
[Link](X_train, y_train)
y_pred = [Link](X_test)
print("Actual Prices:", list(y_test))
print("Predicted Prices:", list([Link](y_pred, 2)), "\n")
mse = mean_squared_error(y_test, y_pred)
rmse = [Link](mse)
r2 = r2_score(y_test, y_pred)
print("RMSE:", rmse)
print("R² Score:", r2, "\n")
[Link](df["Area"], df["Price"], label="Actual Data")
[Link](df["Area"], [Link](df[["Area"]])
6. Credit Risk Assessment. Sample Programs using German Credit Data.
#include <stdio.h>
int main() {
int age, credit_history, loan_amount, income;
float score = 0;
printf("Enter Age: ");
scanf("%d", &age);
printf("Enter Credit History (1=Good, 0=Bad): ");
scanf("%d", &credit_history);
printf("Enter Loan Amount: ");
scanf("%d", &loan_amount);
printf("Enter Annual Income: ");
scanf("%d", &income);
if (credit_history == 1) score += 50;
if (income > loan_amount) score += 30;
if (age > 25) score += 20;
printf("\nCredit Score = %.2f", score);
if (score >= 60)
printf("\nCredit Risk: GOOD\n");
else
printf("\nCredit Risk: BAD\n");
return 0;
7. Sample Programs using Hospital Management System.
#include <stdio.h>
#include <string.h>
struct Patient {
int id;
char name[50];
int age;
char disease[50];
} p[100];
int count = 0;
void addPatient() {
printf("\nEnter Patient ID: ");
scanf("%d", &p[count].id);
printf("Enter Name: ");
scanf("%s", p[count].name);
printf("Enter Age: ");
scanf("%d", &p[count].age);
printf("Enter Disease: ");
scanf("%s", p[count].disease);
count++;
printf("Patient Added Successfully!\n");
void displayPatients() {
printf("\n---- Patient List ----\n");
for (int i=0; i < count; i++) {
printf("ID: %d | Name: %s | Age: %d | Disease: %s\n",
p[i].id, p[i].name, p[i].age, p[i].disease);
void searchPatient() {
int id;
printf("\nEnter Patient ID to Search: ");
scanf("%d", &id);
for (int i=0; i<count; i++) {
if (p[i].id == id) {
printf("Patient Found:\n");
printf("ID: %d | Name: %s | Age: %d | Disease: %s\n",
p[i].id, p[i].name, p[i].age, p[i].disease);
return;
printf("Patient Not Found!\n");
}
int main() {
int choice;
while (1) {
printf("\n--- Hospital Management System ---\n");
printf("1. Add Patient\n2. Display All\n3. Search Patient\n4. Exit\n");
printf("Enter Choice: ");
scanf("%d", &choice);
switch (choice) {
case 1: addPatient(); break;
case 2: displayPatients(); break;
case 3: searchPatient(); break;
case 4: return 0;
default: printf("Invalid Choice!\n");