Data Science: Complete Guide, Lifecycle, Tools & Use Cases

Discover the world of Data Science. Learn the data science lifecycle, machine learning techniques, top tools like Python and R, and real-world applications.

Part 1: DATA ENGINEERING & ANALYTICS – INTRODUCTION

1. Understanding the Data Ecosystem

1.1 What Is Data Engineering? (Building the Infrastructure)

Data Engineering is the practice of designing, building, and maintaining the infrastructure, systems, and pipelines that collect, store, process, and make data available for analysis and machine learning.

RealWorld Analogy: Data Engineering is like building the roads, water pipes, and electricity grid for a city. Without this infrastructure, nothing can function. The city (your business) needs these systems to operate smoothly.

What Data Engineers Do: Build data pipelines that move data from source to destination. Design and manage databases and data warehouses. Ensure data quality, reliability, and availability. Optimize performance and scalability. Work with cloud infrastructure (AWS, Azure, GCP). Build ETL/ELT processes.
Example: A Data Engineer at Uber builds systems that collect billions of GPS location points from drivers and riders, processes them in real-time, and makes them available for pricing, routing, and analytics.

Responsibility	Description
Build Data Pipelines	Create ETL/ELT processes to move data from sources to destinations
Design Databases	Architect efficient database schemas and storage systems
Ensure Data Quality	Validate data accuracy, completeness, and consistency
Optimize Performance	Tune queries, indexes, and systems for speed
Scale Infrastructure	Handle growing data volumes with cloud and distributed systems
Monitor Systems	Track pipeline health, errors, and performance

Key Skills:

• SQL (Expert level)
• Python (Data engineering libraries)
• Cloud platforms (AWS, Azure, GCP)
• Big data tools (Spark, Kafka)
• Orchestration (Airflow)

1.2 What Is Data Science? (Building the Models)

Data Science is the practice of extracting insights, patterns, and predictions from data using statistical methods, machine learning, and domain expertise. Data Scientists analyze data, build models, and extract insights to solve business problems.

Real-World Analogy: Data Science is like being a detective who examines evidence (data) to solve a case (business problem). You look for clues, connect dots, and make predictions about what will happen next.

What Data Scientists Do: Explore and understand data (EDA). Build predictive models using machine learning. Test hypotheses using statistical methods. Communicate insights to business leaders. Design experiments (A/B testing). Create data visualizations.

Example: A Data Scientist at Netflix analyzes viewing patterns to build recommendation algorithms that suggest what you should watch next, increasing user engagement and retention.

Responsibility	Description
Explore Data	Perform EDA to understand patterns and relationships
Build Models	Create machine learning models for prediction and classification
Run Experiments	Design A/B tests and analyze results
Communicate Insights	Present findings to business stakeholders
Validate Models	Ensure models are accurate and reliable
Collaborate with DE	Work with Data Engineers to access and prepare data

Key Skills:

• Python or R (Data science libraries)
• Statistics and probability
• Machine learning (Scikit-Learn, TensorFlow, PyTorch)
• SQL (Intermediate to Advanced)
• Data visualization
• Domain expertise

How Data Engineering and Data Science Work Together

DATA ENGINEERING(CLEAN, ORGANIZED DATA): Builds data pipelines, Maintains databases and warehouses, Ensures data quality and reliability, Provides clean and organized data to data scientists, Scales infrastructure for growing data volumes.
DATA SCIENCE: Explores data to find patterns, Builds predictive models, Tests hypotheses, Creates visualizations and dashboards, Communicates insights to business, Validates models with stakeholders.
Real-World Example: At Amazon, Data Engineers build systems that collect billions of customer click, purchase, and browsing events. Data Scientists then use this clean data to build recommendation models that suggest products to customers. Together, they drive billions in revenue.

The Key Insight:

Data Engineering builds the roads. Data Science drives on those roads. Neither can succeed without the other.

1.3 Data Analyst (Analyzing and Reporting)

Data Analysts focus on analyzing historical data, creating reports, and providing insights to business teams.

Responsibility	Description
Create Dashboards	Build visual dashboards for business monitoring
Generate Reports	Create regular business reports and presentations
Answer Business Questions	Use data to answer specific business queries
Identify Trends	Spot patterns in historical data
Support Decision Making	Provide data-driven recommendations

Key Skills:

• SQL (Advanced)
• Data visualization (Tableau, Power BI)
• Excel (Advanced)
• Business acumen
• Communication skills

1.4 Machine Learning Engineer (Deploying Models)

Machine Learning Engineers take models built by Data Scientists and deploy them into production systems.

Responsibility	Description
Deploy Models	Put machine learning models into production
Build APIs	Create APIs for model serving
Monitor Performance	Track model accuracy and performance in production
Scale Systems	Ensure models can handle production traffic
Automate Pipelines	Build automated retraining and deployment pipelines

Key Skills:

• Python (Advanced)
• Docker and containerization
• Cloud platforms (AWS, Azure, GCP)
• CI/CD pipelines
• API development (FastAPI, Flask)
• MLOps tools

1.5 Data Architect – Designing the Systems

Data Architects design the overall data strategy and architecture for an organization.

Responsibility	Description
Design Architecture	Create the overall data infrastructure design
Select Technologies	Choose appropriate databases, tools, and platforms
Define Standards	Establish data governance and quality standards
Plan Strategy	Develop long-term data strategy and roadmap
Oversee Implementation	Guide engineering teams in building systems

Key Skills:

• Deep knowledge of data technologies
• Enterprise architecture
• Data modeling (advanced)
• Data governance
• Strategic planning

1.6 Career Paths and Required Skills

Skills by Career Stage:

Stage	Data Engineer Skills	Data Scientist Skills
Entry	SQL, Python basics, Linux, Git	Python, SQL basics, Statistics fundamentals
Junior	ETL, Database design, Cloud basics	Pandas, Matplotlib, Machine learning basics
Mid	Big Data (Spark, Kafka), Airflow, Cloud architecture	Advanced ML, Deep learning, Experiment design
Senior	Distributed systems, Performance optimization	Advanced algorithms, Research, Business strategy
Lead/Architect	System design, Strategy, Mentoring	Architecture, Strategy, Leadership

1.7 The Complete Data Journey: Collect → Store → Process → Clean → Analyze → Visualize → Deploy

Before we dive into the details, let’s understand the complete journey that data takes from its origin to delivering business value.

Step-by-Step Breakdown and Who Does What in the Data Journey:

Phase	What Happens	Who Does This	Tools Used
1. COLLECT	Raw data is gathered from various sources like APIs, databases, websites, sensors, and logs	Data Engineer	APIs, Web Scraping, Kafka, Sensors
2. STORE	Data is organized and saved in databases, data lakes, or data warehouses	Data Engineer	SQL, NoSQL, Data Lakes, Warehouses
3. PROCESS	Data is transformed, aggregated, and moved through ETL/ELT pipelines	Data Engineer	ETL, ELT, Airflow, Spark
4. CLEAN	Data is fixed and prepared, Missing, Outliers, Encoding and formats are standardized	Both (DE + DS)	Pandas, Python, Cleaning Tools
5. ANALYZE	Data is studied and modeled, EDA, ML, Stats, Predictive. Data is studied using statistics and machine learning to find patterns and make predictions	Data Scientist	Statistics, ML, Python, R
6. VISUALIZE	Insights are presented using charts, dashboards, and reports	Data Analyst / Scientist	Charts, Graphs, Dashboards, BI Tools
7. DEPLOY	Models go live for users. Models and insights are put into production for real-world use	ML Engineer	APIs, Docker, Cloud, Monitoring

2. Understanding Data

2.1 What Is Data?

Data is the raw material that AI and analytics learn from numbers, text, images, audio, or any recorded observation.

Critical Insight: Bad data produces bad AI. No data produces no AI. You can have the most advanced neural network architecture in the world. If you feed it garbage data, it will produce garbage predictions. AI engineers spend far more time on data than on model architecture.

Data is simply a collection of recorded observations. Every time something happens and you write it down, that is data.

Real-World Examples	What Is Recorded
A hospital records every patient’s age, symptoms, and diagnosis	Patient health data
A store records every product sold, the time, and the customer	Sales transactions
A phone records every tap, scroll, and location	User interaction data
A weather station records temperature, humidity, and wind every hour	Environmental data

The Challenge: Real data is almost always messy, incomplete, inconsistent, and full of problems. Cleaning and preparing it properly is what separates a mediocre AI project from a great one.

2.2 Types of Data

Data can be classified in several ways:

Type	What It Is	Examples	How AI Uses It
Structured	Neat rows and columns like a spreadsheet	Customer records, stock prices, medical records	Classical ML (Random Forest, XGBoost)
Unstructured	No predefined format	Images, text documents, audio recordings, videos	Deep Learning (CNNs for images, Transformers for text)
Semi-structured	Some structure but flexible	JSON, XML	Often converted to structured format before use
Labeled (Supervised)	Each example has input + correct answer	Spam emails labeled “spam” or “not spam”	Used to train supervised learning models
Unlabeled (Unsupervised)	Only input features, no correct answers	Customer purchase history without categories	Used to find hidden patterns (clustering)

Structured vs Unstructured Data Breakdown:

Aspect	Structured	Unstructured
Format	Tabular (rows/columns)	Text, images, audio, video
Example	Excel spreadsheet	Word document, photo
Storage	Relational databases	Data lakes, object storage
Analysis	Easy (SQL)	Harder (NLP, computer vision)
Percentage of Data	~20%	~80%

2.3 Labeled vs Unlabeled Data

Type	Data	Label?
Labeled	Email text: “Congratulations you won 1 million dollars!”	SPAM
Labeled	Email text: “Meeting at 3pm tomorrow”	NOT SPAM
Unlabeled	Customer 1: age=25, purchases=12, avg_amount=3000	(no label)
Unlabeled	Customer 2: age=45, purchases=2, avg_amount=15000	(no label)

Supervised Learning uses labeled data to train models that can predict labels for new data.
Unsupervised Learning uses unlabeled data to find hidden patterns or groupings.

2.4 Data Sources: Where Data Comes From

Source	Description	Example
APIs	Programmatic access to data from services	Twitter API, Weather API, Google Maps API
Websites	Data extracted from web pages (scraping)	Product prices, news headlines, reviews
Databases	Structured data from applications	Customer records, sales data, inventory
Logs	System activity records	Server logs, application logs, access logs
Sensors	Physical-world measurements	IoT devices, GPS, temperature sensors
Streaming	Continuous real-time data	Stock prices, social media, video streams
Files	Structured data files	CSV, Excel, JSON, Parquet

2.5 Finding Datasets (Kaggle, UCI, Hugging Face, Google Datasets)

You do not need to collect your own data when learning. There are massive free repositories:

Source	What It Has	Best For
Kaggle (kaggle.com)	Thousands of datasets + competitions	All types of ML practice
UCI Machine Learning Repository	Classic research datasets	Academic learning
Google Dataset Search	Searches across the internet	Finding niche datasets
Scikit-learn Built-in Datasets	Ready to use immediately	Quick experiments
Seaborn Built-in Datasets	Clean, well-formatted datasets	Data visualization practice
Hugging Face Datasets	Massive text, image, audio datasets	NLP and deep learning

Python Code to Load a Dataset:

from sklearn.datasets import load_iris

# Load instantly, no files needed
iris = load_iris()
X = iris.data     # features
y = iris.target   # labels
print(X.shape)    # (150, 4)

DATA COLLECTION

3. Data Collection Methods

3.1 What Is Data Collection?

Data Collection is the process of gathering raw data from various sources so it can be stored, processed, and analyzed.

Simple Example: Data collection is like going to a grocery store to buy ingredients. You gather all the raw materials (data) you need before you can cook (analyze) a meal.

The Data Collection Process:

Source → Extraction → Validation → Storage → Ready for Processing

3.2 Collecting from Databases (SQL)

Databases are one of the most common sources of structured data.

import sqlite3
import pandas as pd

# Connect to database
conn = sqlite3.connect("company.db")

# Query data
query = "SELECT * FROM customers WHERE age > 25"
df = pd.read_sql_query(query, conn)

# Close connection
conn.close()

print(df.head())

Common SQL Data Collection Patterns:

-- Get all records
SELECT * FROM customers;

-- Get specific columns
SELECT name, email, city FROM customers;

-- Get filtered records
SELECT * FROM orders WHERE order_date > '2024-01-01';

-- Get aggregated data
SELECT product, SUM(quantity) as total_sold
FROM sales
GROUP BY product;

3.3 Collecting from APIs (Application Programming Interfaces)

An API (Application Programming Interface) is a way for programs to talk to other programs. It allows software systems to exchange data through structured requests and responses.

🔧 Real-World Example: A weather app gets weather data from a weather server. The process flows: App → request → weather server → response → App.

import requests

# Make API request
response = requests.get("https://api.example.com/data")
data = response.json()

print(data)

Common API Response Formats:

• JSON (most common)
• XML
• CSV

3.4 Collecting from Websites (Web Scraping)

Web scraping is the automated process of extracting data from websites.

Real-World Example: You want to compare prices for a product across multiple online stores. Instead of visiting each website manually, you build a scraper that automatically extracts prices from all sites.

from bs4 import BeautifulSoup
import requests

# Get the webpage
url = "https://example.com"
response = requests.get(url)

# Parse HTML
soup = BeautifulSoup(response.text, "html.parser")

# Extract data
titles = soup.find_all("h1")
for title in titles:
    print(title.text)

When to Use Web Scraping:

• No API is available
• You need data from public websites
• You need to monitor competitor prices
• You need news or article data

3.5 Collecting from Files (CSV, Excel, JSON)

Files are the most common way to exchange data.

import pandas as pd

# CSV files
df = pd.read_csv("data.csv")

# Excel files
df = pd.read_excel("data.xlsx", sheet_name="Sheet1")

# JSON files
df = pd.read_json("data.json")

# Parquet files (big data)
df = pd.read_parquet("data.parquet")

File Format Comparison:

Format	Best For	Pros	Cons
CSV	Small to medium datasets	Human-readable, widely supported	No schema, inefficient for large data
Excel	Business reports	Familiar, supports formatting	Limited size (~1M rows), slow
JSON	Nested/hierarchical data	Flexible, widely used in APIs	Larger file size, harder to query
Parquet	Big data, analytics	Columnar, compressed, fast	Not human-readable

3.6 Collecting from Sensors and IoT Devices

IoT (Internet of Things) devices collect physical-world data and send it to servers.

import random
import time
import requests

def collect_sensor_data():
    # Simulate reading from sensors
    data = {
        "temperature": random.uniform(20, 35),
        "humidity": random.uniform(40, 80),
        "timestamp": time.time()
    }

    # Send to server
    response = requests.post("https://api.example.com/sensors", json=data)
    print(f"Sent: {data}")

# Collect every minute
while True:
    collect_sensor_data()
    time.sleep(60)

Common IoT Applications:

• Smart home monitoring
• Industrial equipment monitoring
• Healthcare patient monitoring
• Agricultural sensors
• Environmental monitoring

3.7 Collecting from Logs

Logs are timestamped records generated by systems to track activities and errors.

Example Log Entry:

2025-01-10 12:00:01 LOGIN user123 IP:192.168.1.1
2025-01-10 12:05:33 ERROR payment_failed user456
2025-01-10 12:10:15 LOGOUT user123

import pandas as pd
import re

# Parse log file
logs = []
with open("system.log", "r") as f:
    for line in f:
        # Parse each line
        parts = line.split()
        if len(parts) >= 3:
            timestamp = parts[0] + " " + parts[1]
            event_type = parts[2]
            user = parts[3] if len(parts) > 3 else "Unknown"
            logs.append({
                "timestamp": timestamp,
                "event": event_type,
                "user": user
            })

# Convert to DataFrame
df = pd.DataFrame(logs)

# Analyze logs
print(df.groupby("event").count())

3.8 Collecting from Streaming Data (Real-Time)

Streaming data is a continuous flow of data generated in real time.

Real-World Example: Think of a stock ticker showing prices that update every second. You can’t wait for a batch process you need to process the data as it arrives.

from kafka import KafkaConsumer
import json

# Setup Kafka consumer
consumer = KafkaConsumer(
    'stock_prices',
    bootstrap_servers=['localhost:9092'],
    value_deserializer=lambda m: json.loads(m.decode('utf-8'))
)

# Process messages in real-time
for message in consumer:
    data = message.value
    print(f"Symbol: {data['symbol']}, Price: {data['price']}")

Real-Time Data Examples:

Source	Data Type	Use Case
Stock Markets	Price updates	Trading algorithms
GPS Devices	Location updates	Navigation, tracking
Social Media	Posts, interactions	Sentiment analysis
IoT Sensors	Temperature, humidity	Monitoring, alerts

4. APIs & Data Retrieval

4.1 What Is an API?

An API (Application Programming Interface) is a set of rules and protocols that allows different software applications to communicate with each other.

Real-World Analogy: An API is like a restaurant menu. The kitchen (server) has many dishes it can prepare, and the waiter (API) takes your order (request) and brings you your food (response). You don’t need to know how the food is prepared—you just need to know how to order.

How APIs Work:

Client (Your App) → Request → API Server → Response → Client

Key Components:

• Endpoint: The URL where you send the request
• Method: GET, POST, PUT, DELETE
• Headers: Authentication, content type
• Body: Data sent with the request
• Response: Data returned from the server

4.2 How APIs Work: Request → Response

Step-by-Step API Flow:

┌─────────────┐    ┌─────────────┐    ┌─────────────┐
│   Client    │───▶│   Request   │───▶│   Server    │
│  (Your App) │    │  (Endpoint) │    │   (API)     │
└─────────────┘    └─────────────┘    └─────────────┘
       ▲                   │                  │
       │                   │                  │
       │                   ▼                  ▼
       │            ┌─────────────┐    ┌─────────────┐
       └────────────│   Response  │◀───│   Process   │
                    │  (Data)     │    │   Request   │
                    └─────────────┘    └─────────────┘

Types of API Requests:

Method	Purpose	Example
GET	Retrieve data	Get user information
POST	Create new data	Create a new user account
PUT	Update existing data	Update user profile
DELETE	Remove data	Delete a user account

4.3 Real-World Example: Weather API

Example using OpenWeather API:

import requests

# Your API key (get from openweathermap.org)
API_KEY = "your_api_key_here"

# Make the request
url = f"https://api.openweathermap.org/data/2.5/weather?q=London&appid={API_KEY}"
response = requests.get(url)

if response.status_code == 200:
    weather_data = response.json()

    temperature = weather_data['main']['temp']
    humidity = weather_data['main']['humidity']
    condition = weather_data['weather'][0]['description']

    print(f"Temperature: {temperature}K")
    print(f"Humidity: {humidity}%")
    print(f"Condition: {condition}")
else:
    print(f"Error: {response.status_code}")

API Response Structure (JSON):

{
    "main": {
        "temp": 293.15,
        "humidity": 82
    },
    "weather": [
        {
            "description": "light rain"
        }
    ],
    "name": "London"
}

4.4 API Authentication Methods

1. API Key (Simplest)

url = "https://api.example.com/data?api_key=YOUR_KEY"

2. Bearer Token (JWT)

headers = {
    "Authorization": "Bearer YOUR_TOKEN"
}
response = requests.get(url, headers=headers)

3. Basic Authentication

response = requests.get(url, auth=("username", "password"))

4. OAuth 2.0 (Most Secure)

# Complex flow: redirect to login, get authorization code, exchange for token
# Used by Google, Facebook, GitHub

Authentication Method Comparison:

Method	Security Level	Complexity	Use Case
API Key	Low	Simple	Public APIs, simple services
Bearer Token	Medium	Moderate	JWT-based authentication
Basic Auth	Low	Simple	Internal systems
OAuth 2.0	High	Complex	User authorization, enterprise

4.5 API Error Handling

Common API Errors:

Status Code	Meaning	What to Do
200	Success	Process the data
400	Bad Request	Check your request parameters
401	Unauthorized	Check your authentication
403	Forbidden	You don’t have permission
404	Not Found	Check the endpoint URL
429	Too Many Requests	Wait and retry (rate limiting)
500	Server Error	Try again later

Robust API Call with Error Handling:

import requests
import time

def fetch_api_data(url, max_retries=3):
    for attempt in range(max_retries):
        try:
            response = requests.get(url, timeout=5)

            if response.status_code == 200:
                return response.json()

            elif response.status_code == 429:
                # Rate limited - wait longer
                wait_time = 2 ** attempt  # Exponential backoff
                print(f"Rate limited. Waiting {wait_time} seconds...")
                time.sleep(wait_time)
                continue

            elif response.status_code >= 500:
                # Server error - retry
                time.sleep(1)
                continue

            else:
                print(f"Error: {response.status_code}")
                return None

        except requests.RequestException as e:
            print(f"Request failed: {e}")
            time.sleep(1)

    print("Max retries exceeded")
    return None

4.6 Project: Collect Data from an API

Project Objective: Create a script that collects Bitcoin price data from an API and saves it to a file.

import requests
import json
import datetime

# API endpoint for Bitcoin price
url = "https://api.coindesk.com/v1/bpi/currentprice.json"

def fetch_bitcoin_price():
    try:
        response = requests.get(url, timeout=10)

        if response.status_code == 200:
            data = response.json()

            # Extract the price
            price = data['bpi']['USD']['rate_float']
            timestamp = datetime.datetime.now().isoformat()

            # Create record
            record = {
                'timestamp': timestamp,
                'price_usd': price,
                'date': datetime.datetime.now().strftime('%Y-%m-%d'),
                'time': datetime.datetime.now().strftime('%H:%M:%S')
            }

            return record
        else:
            print(f"Error: {response.status_code}")
            return None

    except Exception as e:
        print(f"Failed to fetch data: {e}")
        return None

# Collect and store data
records = []

for _ in range(5):  # Get 5 data points
    data = fetch_bitcoin_price()
    if data:
        records.append(data)
        print(f"Fetched: ${data['price_usd']}")
    time.sleep(10)  # Wait 10 seconds between requests

# Save to file
with open('bitcoin_prices.json', 'w') as f:
    json.dump(records, f, indent=2)

print(f"Saved {len(records)} records to bitcoin_prices.json")

# Load and display saved data
with open('bitcoin_prices.json', 'r') as f:
    saved_data = json.load(f)

print("\nSaved Records:")
for record in saved_data:
    print(f"{record['timestamp']}: ${record['price_usd']}")

5. Web Scraping

5.1 What Is Web Scraping?

Web scraping is the automated extraction of data from websites by parsing HTML content.

Real-World Analogy: Web scraping is like reading a newspaper, cutting out relevant articles, and pasting them into a scrapbook. Instead of doing it manually, a computer program does it automatically.

How Web Scraping Works:

Website → HTML Content → Parser → Extract Data → Save

5.2 When to Use Web Scraping

Use Case	Example
Competitor Analysis	Monitor competitor prices, products, and reviews
Market Research	Collect product information from e-commerce sites
News Monitoring	Gather news articles and headlines
Job Listings	Collect job postings from multiple sites
Real Estate	Gather property listings and prices
Social Media	Extract public posts and engagement data

When NOT to Use Web Scraping:

• When an API is available (use the API instead)
• When scraping violates the website’s terms of service
• When the data requires authentication (use proper APIs)
• When you need high-frequency updates (real-time streams are better)

Legal Considerations:

• Always check robots.txt
• Respect the website’s terms of service
• Don’t overwhelm servers with too many requests
• Use reasonable rate limiting
• Consider the website’s resources

5.3 Web Scraping with BeautifulSoup

BeautifulSoup is a Python library that makes it easy to parse HTML and extract data.

from bs4 import BeautifulSoup
import requests

# Step 1: Get the webpage
url = "https://news.ycombinator.com"
response = requests.get(url)

# Step 2: Parse the HTML
soup = BeautifulSoup(response.text, "html.parser")

# Step 3: Extract data
# Find all article titles
titles = soup.select(".titleline > a")
title_texts = [title.text for title in titles]

# Find all article scores
scores = soup.select(".score")
score_texts = [score.text for score in scores]

# Step 4: Combine and display
for title, score in zip(title_texts, score_texts):
    print(f"{title} - {score}")

Common BeautifulSoup Operations:

# Find by tag
soup.find_all("h1")

# Find by class
soup.find_all("div", class_="article")

# Find by id
soup.find(id="main-content")

# Using CSS selectors
soup.select(".titleline > a")

# Extract text
element.text
element.get_text()

# Extract attributes
element.get("href")
element["href"]

# Navigate the HTML tree
element.parent
element.children
element.next_sibling

5.4 Professional Scraping with Scrapy

Scrapy is a professional web scraping framework that handles large-scale scraping projects.

import scrapy

class ArticleSpider(scrapy.Spider):
    name = "articles"
    start_urls = ["https://example.com/articles"]

    def parse(self, response):
        for article in response.css("div.article"):
            yield {
                "title": article.css("h2::text").get(),
                "summary": article.css("p.summary::text").get(),
                "link": article.css("a::attr(href)").get(),
                "published": article.css("span.date::text").get()
            }

        # Follow pagination
        next_page = response.css("a.next::attr(href)").get()
        if next_page:
            yield response.follow(next_page, self.parse)

Scrapy Advantages:

Feature	Benefit
Built-in support	Handles HTTP requests, retries, redirects
Concurrent scraping	Scrapes multiple pages in parallel
Error handling	Built-in error recovery and retry logic
Data export	Export to JSON, CSV, XML, or databases
Middleware support	Add custom headers, proxies, user agents
Item pipelines	Process and validate scraped data

To Run the Scrapy Spider:

scrapy crawl articles -o articles.json

5.5 Project: Build a Web Scraper

Project Objective: Scrape news headlines from Hacker News and save to CSV.

import requests
from bs4 import BeautifulSoup
import pandas as pd
import time

def scrape_hacker_news(pages=2):
    all_data = []

    for page in range(pages):
        url = f"https://news.ycombinator.com/?p={page+1}"
        response = requests.get(url)

        if response.status_code != 200:
            print(f"Failed: {response.status_code}")
            continue

        soup = BeautifulSoup(response.text, "html.parser")

        # Get all article rows
        rows = soup.select("tr.athing")

        for row in rows:
            # Get title and link
            title_elem = row.select_one(".titleline > a")
            if not title_elem:
                continue

            title = title_elem.text
            link = title_elem.get("href", "")

            # Get score (if available)
            score_elem = row.find_next("td").find_next("span", class_="score")
            score = score_elem.text if score_elem else "0 points"

            # Get author
            author_elem = row.find_next("a", class_="hnuser")
            author = author_elem.text if author_elem else "Unknown"

            # Get time
            time_elem = row.find_next("span", class_="age")
            time_posted = time_elem.text if time_elem else "Unknown"

            all_data.append({
                "title": title,
                "link": link,
                "score": score,
                "author": author,
                "posted": time_posted,
                "page": page + 1
            })

        time.sleep(1)  # Be respectful

    return all_data

# Run the scraper
data = scrape_hacker_news(pages=3)

# Save to CSV
df = pd.DataFrame(data)
df.to_csv("hacker_news_articles.csv", index=False)

print(f"Scraped {len(data)} articles")
print(df.head())

5.6 Logging and Automation

Logging helps track what your scraper is doing and identify issues.

import logging
import requests
import datetime

# Setup logging
logging.basicConfig(
    filename='scraper.log',
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s'
)

def scrape_with_logging():
    try:
        logging.info("Starting scrape")
        response = requests.get("https://example.com", timeout=10)

        if response.status_code == 200:
            logging.info(f"Success: {len(response.text)} bytes")
            # Process data...
        else:
            logging.error(f"Failed: {response.status_code}")

    except Exception as e:
        logging.error(f"Error: {e}")

# Schedule to run daily
import schedule
import time

schedule.every().day.at("09:00").do(scrape_with_logging)

while True:
    schedule.run_pending()
    time.sleep(60)

6. Streaming & Real-Time Data

6.1 What Is Streaming Data?

Streaming data is a continuous, unbounded flow of data that is generated in real-time and needs to be processed as it arrives.

Real-World Analogy: Streaming data is like a river flowing continuously. You can’t wait for the entire river to pass—you need to take samples and process water as it flows.

Batch vs Streaming:

Aspect	Batch Processing	Streaming Processing
Data Volume	Large, fixed chunks	Continuous, unbounded
Latency	Minutes to hours	Milliseconds to seconds
Processing	Processed at scheduled times	Processed as data arrives
Use Case	Daily reports, analytics	Real-time monitoring, alerts
Example	End-of-day sales report	Live stock price updates

6.2 Real-World Examples

Stock Price Monitoring:

import time

def monitor_stock_price(symbol, threshold):
    while True:
        # Get current price from API
        price = get_stock_price(symbol)  # Simulated

        if price > threshold:
            send_alert(f"{symbol} price exceeded {threshold}")

        time.sleep(1)  # Check every second

GPS Tracking:

def track_vehicle(vehicle_id):
    while True:
        location = get_gps_location(vehicle_id)  # Simulated

        # Store in database
        store_location(vehicle_id, location)

        # Check if vehicle is in allowed zone
        if is_outside_zone(location):
            send_alert(f"Vehicle {vehicle_id} is outside allowed zone")

        time.sleep(5)  # Update every 5 seconds

6.3 Apache Kafka: Producers, Topics, Consumers

Apache Kafka is the industry standard for streaming data.

┌─────────────┐    ┌─────────────┐    ┌─────────────┐
│  Producer   │───▶│   Topic     │───▶│  Consumer   │
│  (Sender)   │    │  (Channel)  │    │  (Receiver) │
└─────────────┘    └─────────────┘    └─────────────┘

Key Concepts:

• Producer: Sends data to a topic
• Topic: A named stream of data (like a channel)
• Partition: Topics are split into partitions for scalability
• Consumer: Reads data from a topic

Kafka Example in Python:

from kafka import KafkaProducer, KafkaConsumer
import json
import time

# Producer: Send data
producer = KafkaProducer(
    bootstrap_servers=['localhost:9092'],
    value_serializer=lambda v: json.dumps(v).encode('utf-8')
)

# Send messages
for i in range(10):
    message = {
        "id": i,
        "timestamp": time.time(),
        "value": f"Message {i}"
    }
    producer.send('my_topic', value=message)
    print(f"Sent: {message}")
    time.sleep(1)

producer.flush()

# Consumer: Receive data
consumer = KafkaConsumer(
    'my_topic',
    bootstrap_servers=['localhost:9092'],
    value_deserializer=lambda m: json.loads(m.decode('utf-8'))
)

for message in consumer:
    print(f"Received: {message.value}")

6.4 Stream Processing: Apache Flink, Spark Streaming

Apache Spark Streaming:

from pyspark import SparkContext
from pyspark.streaming import StreamingContext

# Create a StreamingContext
sc = SparkContext("local[2]", "NetworkWordCount")
ssc = StreamingContext(sc, 1)  # 1 second batch interval

# Create a DStream that reads data from a TCP socket
lines = ssc.socketTextStream("localhost", 9999)

# Process the stream
words = lines.flatMap(lambda line: line.split(" "))
word_counts = words.map(lambda word: (word, 1)).reduceByKey(lambda a, b: a + b)

# Print the results
word_counts.pprint()

# Start the computation
ssc.start()
ssc.awaitTermination()

DATA STORAGE

7. Database Design

7.1 Relational Databases: Rows, Columns, Tables

A Relational Database organizes data into tables with rows and columns.

Example: Customers Table

customer_id	name	age	city
1	Ali	25	Lahore
2	Sara	30	Karachi
3	John	35	Islamabad

Key Components:

Component	Description	Example
Table	A collection of related data	Customers table
Row	A single record	A specific customer
Column	A data field	Name, Age, City
Primary Key	Unique identifier for each row	customer_id
Foreign Key	References a primary key in another table	customer_id in Orders table

7.2 Primary Keys and Foreign Keys

Primary Key – A unique identifier for each row in a table.

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100) UNIQUE
);

Foreign Key – A field that references a primary key in another table.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Why Foreign Keys Matter:

• Maintains data consistency
• Prevents orphaned records
• Allows JOIN operations between tables

7.3 What Is Normalization?

Normalization is the process of organizing data to reduce redundancy and improve data integrity.

Real-World Analogy: Think of normalization like organizing your closet. Instead of having duplicate items in different places, you have one place for each type of item. This makes things easier to find and reduces confusion.

Before Normalization (Bad):

order_id	customer_name	customer_address	product_1	product_2	product_3
1	Ali	123 Main St	Laptop	Phone	–
2	Ali	123 Main St	Tablet	–	–
3	Sara	456 Oak Ave	Laptop	Mouse	Keyboard

Problems:

• Duplicate data (Ali’s address is repeated)
• Fixed number of columns (max 3 products)
• Hard to add more products
• Update anomalies (change Ali’s address requires updating multiple rows)

After Normalization (Good):

Customers Table:

customer_id	name	address
1	Ali	123 Main St
2	Sara	456 Oak Ave

Products Table:

product_id	name
1	Laptop
2	Phone
3	Tablet

Orders Table:

order_id	customer_id	order_date
1	1	2024-01-01
2	1	2024-01-02

Order_Items Table:

order_id	product_id	quantity
1	1	1
1	2	1
2	3	1
3	2	1
3	1	1
3	4	1

7.4 Normalization Forms

1NF (First Normal Form): Eliminate repeating groups

Before (Bad – Not 1NF)
customer_id	name	products
1	Ali	Laptop, Phone, Tablet
2	Sara	Laptop, Mouse

After (1NF)
customer_id	name	product
1	Ali	Laptop
1	Ali	Phone
1	Ali	Tablet
2	Sara	Laptop
2	Sara	Mouse

2NF (Second Normal Form): Remove partial dependencies

Rule: All non-key attributes must depend on the entire primary key.

Before (Bad – Not 2NF)
order_id	product_id	product_name
1	1	Laptop
1	2	Phone

Problem: product_name depends on product_id, not on the full key (order_id, product_id)

After (2NF)
Order_Items:
order_id	product_id	quantity
1	1	1
1	2	1

Products:
product_id	product_name	price
1	Laptop	1000
2	Phone	500

3NF (Third Normal Form): Remove transitive dependencies

Rule: No non-key attribute should depend on another non-key attribute.

Before (Bad – Not 3NF)
order_id	customer_id	customer_city	order_total
1	1	Lahore	1500
2	2	Karachi	500

Problem: customer_city depends on customer_id, not on order_id

After (3NF)
Orders:
order_id	customer_id	order_total
1	1	1500
2	2	500

Customers:
customer_id	name	city
1	Ali	Lahore
2	Sara	Karachi

Normalization Summary Table:

Normal Form	Rule	Key Requirement
1NF	No repeating groups	Each cell has single value
2NF	No partial dependencies	All non-key attributes depend on entire key
3NF	No transitive dependencies	No non-key depends on another non-key

7.5 Normalization Example: Library System

Design a normalized library database:

Before (Denormalized):

books: book_id, book_title, book_author, author_birth, member_id, member_name, borrow_date, return_date

After Normalization (3NF):

Authors Table:

author_id	name	birth_year
1	J.K. Rowling	1965
2	George Orwell	1903

Books Table:

book_id	title	author_id	isbn
1	Harry Potter	1	1234567890
2	1984	2	0987654321

Members Table:

member_id	name	join_date
1	Ali	2024-01-01
2	Sara	2024-01-15

Borrowing Table:

borrow_id	book_id	member_id	borrow_date	return_date
1	1	1	2024-02-01	2024-02-15
2	2	2	2024-02-10	2024-02-24

8. Types of Data Storage Systems

8.1 Relational Databases (MySQL, PostgreSQL, Oracle)

Relational Databases store structured data in tables with rows and columns, using SQL for queries.

Key Characteristics:

• Structured schema (fixed columns)
• ACID compliance (Atomicity, Consistency, Isolation, Durability)
• Use SQL for queries
• Enforce data integrity

Best For:

• Financial systems (banking, accounting)
• E-commerce platforms
• Inventory management
• Customer Relationship Management (CRM)

Popular Relational Databases:

Database	Best For	Key Features
MySQL	Web applications, e-commerce	Fast, open-source, widely used
PostgreSQL	Complex queries, enterprise	Advanced features, JSON support, extensions
Oracle	Large enterprises	Robust, high security, expensive
SQL Server	Microsoft ecosystem	Integration with .NET, Azure

Example: Creating a Table in PostgreSQL

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

8.2 NoSQL Databases (MongoDB, Cassandra, Redis)

NoSQL Databases are non-relational databases designed for flexibility, scalability, and performance.

Key Characteristics:

• Flexible schema (no fixed columns)
• Designed for horizontal scaling
• Handle semi-structured data
• Often eventually consistent

Types of NoSQL Databases:

Type	Description	Examples	Best For
Document	Store data as JSON-like documents	MongoDB, CouchDB	Content management, catalogs
Key-Value	Simple key-value pairs	Redis, DynamoDB	Caching, session management
Columnar	Store data in columns	Cassandra, HBase	Analytics, time-series data
Graph	Store relationships	Neo4j, JanusGraph	Social networks, recommendation

Example: MongoDB Document

{
    "customer_id": 1,
    "name": "Ali",
    "email": "ali@example.com",
    "orders": [
        {
            "order_id": 101,
            "product": "Laptop",
            "price": 1000
        },
        {
            "order_id": 102,
            "product": "Phone",
            "price": 500
        }
    ]
}

9.3 Data Lakes (S3, Azure Data Lake, Google Cloud Storage)

Data Lakes store vast amounts of raw data in its native format.

🔧 Real-World Analogy: A Data Lake is like a large body of water where raw data flows in from all sources. You can later “fish out” what you need when you need it.

Key Characteristics:

• Store raw data (no processing before storage)
• Schema-on-read (define structure when reading)
• Support structured, semi-structured, and unstructured data
• Very cost-effective for large volumes

Data Lake vs Data Warehouse:

Aspect	Data Lake	Data Warehouse
Data Type	Raw, unstructured	Processed, structured
Schema	Schema-on-read	Schema-on-write
Users	Data scientists, engineers	Business analysts, BI
Cost	Low (cheap storage)	Higher (compute and storage)
Purpose	Exploration, ML, archival	Reporting, analytics

Example: Using AWS S3 for Data Lake

import boto3

# Upload file to S3
s3 = boto3.client('s3')
s3.upload_file('data.csv', 'my-data-lake', 'raw/data.csv')

# List files
response = s3.list_objects_v2(Bucket='my-data-lake')
for obj in response.get('Contents', []):
    print(obj['Key'])

8.4 Data Warehouses (Snowflake, Redshift, BigQuery)

Data Warehouses are optimized for fast querying of structured, cleaned data.

Real-World Analogy: A Data Warehouse is like a well-organized grocery store. Everything is cleaned, categorized, and placed on the right shelf so you can quickly find what you need.

Key Characteristics:

• Data is cleaned and transformed before loading
• Schema-on-write
• Optimized for complex queries
• Uses columnar storage

Example: Querying BigQuery

SELECT
    DATE(order_date) as order_day,
    product_category,
    COUNT(DISTINCT customer_id) as unique_customers,
    SUM(order_amount) as total_revenue
FROM sales_data
WHERE order_date >= '2024-01-01'
GROUP BY order_day, product_category
ORDER BY total_revenue DESC;

Data Warehouse Comparison:

Feature	Snowflake	Redshift (AWS)	BigQuery (GCP)
Cloud	Multi-cloud	AWS only	GCP only
Pricing	Separate compute & storage	Cluster-based	Serverless, pay per query
Scalability	Automatic	Manual	Automatic
Best For	Enterprise	Large-scale analytics	Google ecosystem

8.5 When to Use Each System

System	Use When	Example
Relational Database	Transactional data, ACID required	Banking, e-commerce checkout
NoSQL Database	Flexible schema, high volume	User profiles, session data
Data Lake	Raw data storage, exploration	Data science, archival
Data Warehouse	Cleaned data, business intelligence	Executive dashboards, reporting

9. SQL – Structured Query Language

9.1 What Is SQL?

SQL (Structured Query Language) is the standard language for managing relational databases.

Real-World Analogy: SQL is like the language you speak to a librarian. You ask specific questions, and the librarian (database) brings you the exact information you need.

Why SQL Is Essential:

• Most widely used database language
• Used by ALL data professionals (Engineers, Scientists, Analysts)
• Powerful for data extraction and analysis
• Standard across different database systems

9.2 Basic SQL Operations

SELECT – Retrieve Data

-- Select all columns
SELECT * FROM customers;

-- Select specific columns
SELECT name, email, city FROM customers;

-- Select with alias
SELECT name AS customer_name, city FROM customers;

-- Select distinct values
SELECT DISTINCT city FROM customers;

WHERE – Filter Data

-- Simple filter
SELECT * FROM customers WHERE age > 25;

-- Multiple conditions
SELECT * FROM customers WHERE age > 25 AND city = 'Lahore';

-- OR condition
SELECT * FROM customers WHERE city = 'Lahore' OR city = 'Karachi';

-- IN operator
SELECT * FROM customers WHERE city IN ('Lahore', 'Karachi', 'Islamabad');

-- LIKE operator (pattern matching)
SELECT * FROM customers WHERE name LIKE 'A%';  -- Starts with A
SELECT * FROM customers WHERE name LIKE '%Ali%';  -- Contains Ali

-- NULL checks
SELECT * FROM customers WHERE email IS NULL;
SELECT * FROM customers WHERE email IS NOT NULL;

ORDER BY – Sort Data

-- Ascending (default)
SELECT * FROM customers ORDER BY name;

-- Descending
SELECT * FROM orders ORDER BY order_date DESC;

-- Multiple columns
SELECT * FROM products ORDER BY category, price DESC;

LIMIT – Limit Results

-- Get top 5
SELECT * FROM customers LIMIT 5;

-- Get top 10 highest paid employees
SELECT * FROM employees ORDER BY salary DESC LIMIT 10;

9.3 Aggregation & Grouping

Aggregate Functions

Function	Description	Example
COUNT	Count rows	SELECT COUNT(*) FROM customers;
SUM	Sum of values	SELECT SUM(salary) FROM employees;
AVG	Average	SELECT AVG(salary) FROM employees;
MIN	Minimum value	SELECT MIN(salary) FROM employees;
MAX	Maximum value	SELECT MAX(salary) FROM employees;

-- Multiple aggregations
SELECT
    COUNT(*) as total_customers,
    AVG(age) as average_age,
    MIN(age) as youngest,
    MAX(age) as oldest
FROM customers;

GROUP BY

-- Group by city
SELECT city, COUNT(*) as customer_count
FROM customers
GROUP BY city;

-- Group by multiple columns
SELECT city, age_group, COUNT(*)
FROM customers
GROUP BY city, age_group;

-- With averages per group
SELECT city, AVG(age) as avg_age, COUNT(*) as count
FROM customers
GROUP BY city;

HAVING (Filter Groups)

-- Only cities with more than 10 customers
SELECT city, COUNT(*) as customer_count
FROM customers
GROUP BY city
HAVING COUNT(*) > 10;

-- Only products with average price > 100
SELECT category, AVG(price) as avg_price
FROM products
GROUP BY category
HAVING AVG(price) > 100;

9.4 Joins: Combining Tables

Joins combine data from multiple tables.

INNER JOIN (Most Common)

Returns only matching rows from both tables.

SELECT orders.order_id, customers.name, orders.total
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id;

Visual:

Table A         Table B         Result
┌──────┐       ┌──────┐       ┌──────────┐
│ 1    │───┬───│ 1    │───────│ 1, 1     │
│ 2    │   │   │ 2    │       │ 2, 2     │
│ 3    │   │   │ 4    │       └──────────┘
└──────┘   │   └──────┘
           │
           └─── Only matches (1,2) included

LEFT JOIN

Returns all rows from left table, matching rows from right.

SELECT customers.name, orders.total
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

Visual:

Table A         Table B         Result
┌──────┐       ┌──────┐       ┌──────────┐
│ 1    │───┬───│ 1    │───────│ 1, 1     │
│ 2    │   │   │ 2    │       │ 2, 2     │
│ 3    │   │   │ 4    │       │ 3, NULL  │
└──────┘   │   └──────┘       └──────────┘
           │
           └─── All from left, matches from right

RIGHT JOIN

Returns all rows from right table, matching rows from left.

SELECT customers.name, orders.total
FROM customers
RIGHT JOIN orders ON customers.id = orders.customer_id;

FULL OUTER JOIN

Returns all rows from both tables.

SELECT customers.name, orders.total
FROM customers
FULL OUTER JOIN orders ON customers.id = orders.customer_id;

9.5 Subqueries

A subquery is a query inside another query.

-- Find employees earning more than average
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Find customers who have placed orders
SELECT name
FROM customers
WHERE id IN (SELECT customer_id FROM orders);

-- Find products with no sales
SELECT name
FROM products
WHERE id NOT IN (SELECT product_id FROM order_items);

---

9.6 Window Functions

Window functions perform calculations across a set of rows related to the current row.

-- Rank employees by salary
SELECT name, salary,
    RANK() OVER (ORDER BY salary DESC) as salary_rank
FROM employees;

-- Rank within department
SELECT name, department, salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank
FROM employees;

-- Running total
SELECT order_date, amount,
    SUM(amount) OVER (ORDER BY order_date) as running_total
FROM orders;

-- Moving average (last 3 orders)
SELECT order_date, amount,
    AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) as moving_avg
FROM orders;

9.7 Indexing for Performance

Indexes speed up query performance by creating data structures that allow fast lookups.

-- Create an index
CREATE INDEX idx_customer_name ON customers(name);

-- Create a composite index
CREATE INDEX idx_customer_city_age ON customers(city, age);

-- Remove index
DROP INDEX idx_customer_name;

When to Use Indexes:

Use Case	Example
Frequent WHERE clauses	WHERE name = 'Ali'
JOIN columns	INNER JOIN ON customers.id = orders.customer_id
ORDER BY columns	ORDER BY order_date
Foreign keys	customer_id in orders table

Index Best Practices:

Practice	Explanation
Don’t over-index	Indexes add overhead to INSERT, UPDATE, DELETE
Use composite indexes	For multiple columns queried together
Index selective columns	Columns with many unique values
Monitor performance	Use EXPLAIN to see query plans

10. Cloud Databases

10.1 AWS Database Services

Service	Type	Use Case
RDS	Relational (MySQL, PostgreSQL, Oracle)	OLTP, web applications
DynamoDB	NoSQL (Key-Value, Document)	High-scale applications, gaming
Redshift	Data Warehouse	Analytics, reporting
Aurora	Relational (MySQL/PostgreSQL compatible)	High-performance applications

Example: AWS RDS Connection

import pymysql

# Connect to RDS
connection = pymysql.connect(
    host='my-database.xxx.amazonaws.com',
    user='admin',
    password='password',
    database='mydb'
)

# Query data
cursor = connection.cursor()
cursor.execute("SELECT * FROM customers")
results = cursor.fetchall()

10.2 Azure Database Services

Service	Type	Use Case
SQL Database	Relational	Web applications, enterprise
Cosmos DB	NoSQL (Multi-model)	Global applications
Synapse Analytics	Data Warehouse	Analytics, big data
Azure Data Lake	Data Lake	Raw data storage

10.3 Google Cloud Database Services

Service	Type	Use Case
Cloud SQL	Relational (MySQL, PostgreSQL)	Web applications
BigQuery	Data Warehouse	Analytics, serverless
Spanner	Relational (Global)	Global applications
Firestore	NoSQL (Document)	Mobile, web apps

10.4 Benefits of Cloud Databases

Benefit	Description
Auto-scaling	Scale up/down automatically based on load
Managed Service	AWS/Azure/Google handles maintenance, backups, patches
Global Availability	Deploy databases across regions for low latency
Pay-as-you-go	Pay only for what you use
Security	Built-in encryption, IAM, and compliance

11. Data Modeling

11.1 What Is Data Modeling?

Data Modeling is the process of defining how data is structured, organized, and related in a database.

Real-World Analogy: Data modeling is like creating an architect’s blueprint before building a house. The blueprint defines where rooms are, how they connect, and what each room is used for.

11.2 Star Schema: Fact Tables and Dimension Tables

Star Schema is the most common data modeling approach for data warehouses.

                    ┌──────────────────┐
                    │   Dim_Product    │
                    │  product_id PK   │
                    │  name            │
                    │  category        │
                    │  price           │
                    └────────┬─────────┘
                             │
         ┌───────────────────┼───────────────────┐
         │                   │                   │
    ┌────┴────┐       ┌──────┴──────┐       ┌────┴────┐
    │ Dim_Date│       │  Fact_Sales │       │Dim_Date │
    │ date_id │───────│  date_id    │───────│ date_id │
    │ day     │       │  product_id │       │ day     │
    │ month   │       │  customer_id│       │ month   │
    │ year    │       │  quantity   │       │ year    │
    │ quarter │       │  revenue    │       │ quarter │
    └─────────┘       └──────┬──────┘       └─────────┘
                             │
                    ┌────────┴─────────┐
                    │   Dim_Customer   │
                    │  customer_id PK  │
                    │  name            │
                    │  city            │
                    │  segment         │
                    └──────────────────┘

Components:

Component	Description	Example
Fact Table	Contains quantitative measures	Sales (quantity, revenue)
Dimension Tables	Contain descriptive attributes	Products (name, category), Customers (name, city)
Primary Key	Unique identifier in each dimension table	product_id, customer_id
Foreign Key	References dimension table keys in fact table	product_id, customer_id

Example: Star Schema for E-Commerce

Fact_Sales Table:

sale_id	date_id	product_id	customer_id	quantity	revenue
1	2024-01-01	101	1	2	2000
2	2024-01-01	102	2	1	500
3	2024-01-02	101	1	1	1000

Dim_Product Table:

product_id	name	category	price
101	Laptop	Electronics	1000
102	Phone	Electronics	500
103	Book	Education	25

Dim_Customer Table:

customer_id	name	city	segment
1	Ali	Lahore	Premium
2	Sara	Karachi	Standard

11.3 Snowflake Schema

Snowflake Schema is an extension of Star Schema where dimensions are normalized.

                    ┌──────────────────┐
                    │   Dim_Product    │
                    │  product_id PK   │
                    │  name            │
                    │  category_id     │
                    └────────┬─────────┘
                             │
                  ┌──────────┴──────────┐
                  │  Dim_Category       │
                  │  category_id PK     │
                  │  category_name      │
                  └─────────────────────┘

Star vs Snowflake:

Aspect	Star Schema	Snowflake Schema
Structure	Denormalized	Normalized
Complexity	Simple	Complex
Query Performance	Faster	Slower (more joins)
Storage	More redundant	Less redundant

11.4 Example: E-Commerce Data Model

Complete E-Commerce Data Model:

-- Dimension Tables
CREATE TABLE dim_customer (
    customer_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    city VARCHAR(50),
    registration_date DATE,
    segment VARCHAR(20)
);

CREATE TABLE dim_product (
    product_id INT PRIMARY KEY,
    name VARCHAR(100),
    category VARCHAR(50),
    price DECIMAL(10,2)
);

CREATE TABLE dim_date (
    date_id DATE PRIMARY KEY,
    year INT,
    month INT,
    day INT,
    quarter INT,
    is_holiday BOOLEAN
);

-- Fact Table
CREATE TABLE fact_sales (
    sale_id INT PRIMARY KEY,
    date_id DATE,
    product_id INT,
    customer_id INT,
    quantity INT,
    revenue DECIMAL(10,2),
    FOREIGN KEY (date_id) REFERENCES dim_date(date_id),
    FOREIGN KEY (product_id) REFERENCES dim_product(product_id),
    FOREIGN KEY (customer_id) REFERENCES dim_customer(customer_id)
);

-- Query Example
SELECT
    dp.category,
    dc.city,
    SUM(fs.revenue) as total_revenue
FROM fact_sales fs
JOIN dim_product dp ON fs.product_id = dp.product_id
JOIN dim_customer dc ON fs.customer_id = dc.customer_id
JOIN dim_date dd ON fs.date_id = dd.date_id
WHERE dd.year = 2024
GROUP BY dp.category, dc.city
ORDER BY total_revenue DESC;

DATA PIPELINES

12. Introduction to Data Pipelines

12.1 What Is a Data Pipeline?

A Data Pipeline is a series of automated processes that move data from source systems to destination systems, transforming it along the way.

Real-World Analogy: A data pipeline is like a manufacturing assembly line. Raw materials (data) enter at one end, go through various processing stages (transformations), and come out as finished products (insights) at the other end.

Simple Pipeline Example:

Source (API) → Extract → Transform → Load → Destination (Database) → Dashboard

12.2 Why Data Pipelines Are Important

Reason	Explanation	Example
Automation	Eliminate manual data processing	Data moves without human intervention
Reliability	Consistent and repeatable processes	Same transformations every time
Scalability	Handle growing data volumes	Process millions of records
Timeliness	Data available when needed	Real-time or scheduled updates
Quality	Data validated and cleaned	Filter out bad records

Without a Pipeline:

1. Manual export from source
2. Manual email/file transfer
3. Manual import to destination
4. Manual transformation
5. Takes hours, error-prone

With a Pipeline:

1. Automated extraction
2. Automated transformation
3. Automated loading
4. Data always ready
5. Takes minutes, reliable

12.3 Pipeline Architecture: Collect → Process → Store

┌─────────────────────────────────────────────────────────────────────────────┐
│                         DATA PIPELINE ARCHITECTURE                          │
│                                                                             │
│  ┌─────────────┐    ┌─────────────┐    ┌─────────────┐    ┌─────────────┐ │
│  │   SOURCE    │───▶│  EXTRACT    │───▶│  TRANSFORM  │───▶│   LOAD      │ │
│  │             │    │  (Collect)  │    │  (Process)  │    │  (Store)    │ │
│  └─────────────┘    └─────────────┘    └─────────────┘    └─────────────┘ │
│        │                  │                  │                  │          │
│        ▼                  ▼                  ▼                  ▼          │
│  API / DB /          Raw Data          Cleaned Data       Destination     │
│  Website /           Extraction       Transformation    (Database /      │
│  Files /             (Pull data)      (Format, Clean,    Data Lake /     │
│  Sensors                               Aggregate,       Warehouse)       │
│                                         Enrich)                           │
└─────────────────────────────────────────────────────────────────────────────┘

Pipeline Stages:

Stage	What Happens	Tools
Extract	Pull raw data from sources	APIs, SQL, Scraping, Kafka
Transform	Clean, format, and enrich data	Python, Spark, SQL
Load	Store processed data	Databases, Data Lakes, Warehouses

12.4 Real-World Examples

Uber’s Data Pipeline:

Uber processes billions of data points daily:

GPS Data (Riders/Drivers) → Kafka → Real-time Processing → Pricing Engine
                              ↓
                         Data Warehouse → Analytics → Dashboard
                              ↓
                         Machine Learning → Predictions → Recommendations

Netflix’s Data Pipeline:

User Activity → Cassandra → Real-time → Recommendations
     ↓
  Data Lake → ETL → Data Warehouse → Analytics
     ↓
  Machine Learning → Personalization → User Experience

13. ETL vs ELT

13.1 ETL: Extract, Transform, Load

ETL extracts data, transforms it before loading.

Extract → Transform → Load
   │         │          │
   ▼         ▼          ▼
 Source → Clean & Format → Destination

ETL Process:

Step	What Happens	Where
Extract	Pull raw data from sources	Source systems
Transform	Clean, validate, format data	Staging area (separate server)
Load	Insert processed data into destination	Destination (Data Warehouse)

When to Use ETL:

• Complex transformations needed
• Data quality is critical
• Historical data needs to be validated
• Compliance requirements (data must be checked before loading)

---

13.2 ELT: Extract, Load, Transform

ELT loads raw data first, then transforms it.

Extract → Load → Transform
   │         │          │
   ▼         ▼          ▼
 Source → Store → Clean & Format

ELT Process:

Step	What Happens	Where
Extract	Pull raw data from sources	Source systems
Load	Store raw data in destination	Destination (Data Lake/Warehouse)
Transform	Clean, validate, format data	Destination (using SQL/Spark)

When to Use ELT:

• Using modern data warehouses (Snowflake, BigQuery, Redshift)
• Transformation is simpler
• Need to keep raw data for future exploration
• Data volumes are large

13.3 When to Use ETL vs ELT

Factor	ETL	ELT
Data Volume	Small to medium	Large to massive
Complexity	Complex transformations	Simple transformations
Tool	Traditional ETL tools	Modern data warehouses
Speed	Slower (transform before load)	Faster (load raw data)
Flexibility	Less (transforms are fixed)	More (can re-transform raw data)
Cost	Higher (separate staging)	Lower (use warehouse compute)

Decision Guide:

┌─────────────────────────────────────────────────────────────────┐
│                 ETL vs ELT Decision Tree                        │
│                                                                 │
│  Do you need to keep raw data for future exploration?           │
│  ┌──────────────────────────────────────────────────────────┐   │
│  │  YES → Use ELT (Load raw data, transform later)          │   │
│  │  NO  → Use ETL (Transform before loading)                │   │
│  └──────────────────────────────────────────────────────────┘   │
│                                                                 │
│  Are transformations complex and require specialized tools?     │
│  ┌──────────────────────────────────────────────────────────┐   │
│  │  YES → Use ETL (Transform before load)                   │   │
│  │  NO  → Use ELT (Transform in warehouse)                  │   │
│  └──────────────────────────────────────────────────────────┘   │
│                                                                 │
│  Is your data volume in terabytes or petabytes?                 │
│  ┌──────────────────────────────────────────────────────────┐   │
│  │  YES → Use ELT (Load raw data first)                     │   │
│  │  NO  → ETL may be simpler                                │   │
│  └──────────────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────────────┘

14. Data Orchestration

14.1 What Is Data Orchestration?

Data Orchestration is the automation of data pipeline tasks, scheduling, and workflow management.

🔧 Real-World Analogy: Data orchestration is like a restaurant kitchen manager who coordinates all the chefs, ensures tasks are done in the right order, and handles timing so everything is ready together.

Orchestration Handles:

• Scheduling jobs (run daily at 2 AM)
• Task dependencies (Task B runs after Task A completes)
• Error handling (retry failed tasks)
• Monitoring (track pipeline health)

14.2 Apache Airflow: DAGs, Tasks, and Scheduling

Apache Airflow is the most popular workflow orchestration tool.

Airflow Concepts:

Concept	Description	Example
DAG (Directed Acyclic Graph)	Collection of tasks with dependencies	ETL pipeline
Task	A single step in a workflow	Extract data, clean data, load data
Operator	Defines what a task does	PythonOperator, SQLOperator
Schedule	When to run the DAG	Daily at 2 AM
Sensor	Wait for external condition	Wait for file to arrive

Example Airflow DAG:

from airflow import DAG
from airflow.operators.python_operator import PythonOperator
from datetime import datetime, timedelta

# Define default arguments
default_args = {
    'owner': 'data_team',
    'depends_on_past': False,
    'start_date': datetime(2024, 1, 1),
    'email_on_failure': True,
    'retries': 1,
    'retry_delay': timedelta(minutes=5)
}

# Define the DAG
dag = DAG(
    'etl_pipeline',
    default_args=default_args,
    description='Extract, Transform, Load pipeline',
    schedule_interval='0 2 * * *',  # Run daily at 2 AM
    catchup=False
)

# Define tasks
def extract_data():
    print("Extracting data from source...")
    # Code to extract data
    return "extracted_data"

def transform_data():
    print("Transforming data...")
    # Code to transform data
    return "transformed_data"

def load_data():
    print("Loading data to warehouse...")
    # Code to load data

# Create tasks
extract_task = PythonOperator(
    task_id='extract_data',
    python_callable=extract_data,
    dag=dag
)

transform_task = PythonOperator(
    task_id='transform_data',
    python_callable=transform_data,
    dag=dag
)

load_task = PythonOperator(
    task_id='load_data',
    python_callable=load_data,
    dag=dag
)

# Define dependencies
extract_task >> transform_task >> load_task  # Task order

14.3 Alternative Orchestration Tools

Tool	Best For	Key Features
Apache Airflow	General-purpose	Python-based, extensive integrations
Prefect	Modern cloud	Simpler than Airflow, cloud-ready
Dagster	Data engineering	Type safety, testing focus
Luigi	Simple pipelines	Spotify’s tool, straightforward
AWS Step Functions	AWS ecosystem	Serverless, visual interface

14.4 Monitoring and Alerting

Pipeline Monitoring:

Metric	What to Watch	Alert Threshold
Success Rate	Jobs completing successfully	< 95%
Execution Time	How long jobs take	> 30 min
Data Volume	Number of records processed	20% deviation
Error Count	Number of failures	> 0

Monitoring Code Example:

import logging
import datetime

def monitor_pipeline():
    # Check success/failure status
    # Log results
    # Send alerts if needed

    if job_failed:
        send_alert_email(
            subject=f"Pipeline Failed: {job_name}",
            message=f"{job_name} failed at {datetime.now()}"
        )
    else:
        logging.info(f"Pipeline {job_name} completed successfully")

15. Data Extraction

15.1 What Is Data Extraction?

Data Extraction is the process of retrieving data from source systems for further processing and analysis.

🔧 Real-World Analogy: Data extraction is like going to a library and pulling specific books off the shelves. You know what you need and where to find it.

15.2 Extraction Methods

Method	Description	Example
Full Extraction	Extract all data from source	SELECT * FROM orders
Incremental Extraction	Extract only new/changed data	SELECT * FROM orders WHERE updated_at > last_run
Change Data Capture (CDC)	Track all changes in real-time	Database log reading

Incremental Extraction Example:

import pandas as pd

def extract_incremental(last_run_time):
    query = f"""
        SELECT *
        FROM orders
        WHERE updated_at > '{last_run_time}'
    """
    df = pd.read_sql_query(query, connection)
    return df

15.3 Change Data Capture (CDC)

CDC tracks all changes to a database in real-time.

# Using Debezium (CDC tool)
# Automatically captures INSERT, UPDATE, DELETE events

def process_cdc_event(event):
    if event['operation'] == 'CREATE':
        insert_record(event['data'])
    elif event['operation'] == 'UPDATE':
        update_record(event['data'])
    elif event['operation'] == 'DELETE':
        delete_record(event['data'])

15.4 Database Replication

Database Replication copies data from one database to another.

Types:

Type	Description	Use Case
Master-Slave	One master writes, replicas read	Read-heavy workloads
Master-Master	Multiple databases write	High availability
Snapshot	Copy entire database	Backup, reporting

MySQL Replication Example:

-- On Master
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';

-- On Slave
CHANGE MASTER TO
    MASTER_HOST='master.example.com',
    MASTER_USER='replica_user',
    MASTER_PASSWORD='password';
START SLAVE;

DATA WRANGLING – CLEANING

16. Introduction to Data Wrangling

16.1 What Is Data Wrangling?

Data Wrangling (also called data munging) is the process of cleaning, transforming, and preparing raw data for analysis and machine learning.

🔧 Real-World Analogy: Data wrangling is like preparing vegetables before cooking. You wash them (clean), remove the bad parts (handle missing data), cut them into uniform pieces (standardize), and group similar items together (organize).

The Data Wrangling Process:

Raw Data → Discover → Structure → Clean → Enrich → Validate → Ready Data

16.2 Why Data Is Messy (The 70-80% Rule)

Data scientists spend 70-80% of their time on data preparation and cleaning.

Why Data Is Messy:

Problem	Example	Impact
Missing Values	Customer Age is blank	Models can’t process missing data
Duplicate Rows	Same customer appears twice	Overcounting in analysis
Incorrect Formats	“25” vs “Twenty-Five”	Can’t process consistently
Outliers	Age = 200	Skews statistics
Inconsistent Encoding	“USA” vs “United States”	Misclassification
Garbage Values	“N/A”, “Unknown”, “NULL”	Misleading results

The 80/20 Rule:

┌─────────────────────────────────────────────────────────────────┐
│                    DATA SCIENCE TIME BREAKDOWN                  │
│                                                                 │
│  ┌────────────────────────────────────────────────────────┐     │
│  │               80% DATA WRANGLING                       │     │
│  │  • Collecting data                                     │     │
│  │  • Cleaning data                                       │     │
│  │  • Handling missing values                             │     │
│  │  • Feature engineering                                 │     │
│  │  • Data validation                                     │     │
│  └────────────────────────────────────────────────────────┘     │
│                                                                 │
│  ┌────────────────────────────────────────────────────────┐     │
│  │               20% MODELING & ANALYSIS                  │     │
│  │  • Building models                                     │     │
│  │  • Testing models                                      │     │
│  │  • Analyzing results                                   │     │
│  │  • Communicating insights                              │     │
│  └────────────────────────────────────────────────────────┘     │
└─────────────────────────────────────────────────────────────────┘

16.3 Messy Data Examples

Example 1: Customer Data

id	name	age	city	email	join_date
1	Ali	25	Lahore	ali@email.com	2024-01-01
2	Sara		Karachi	sara@email.com	2024-01-15
3	Ali	25	Lahore	ali@email.com	2024-01-01
4	john	30	isb	john@email.com	2024/02/01
5	Ahmed	-5	Lahore	ahmed@email.com	2024-03-01

Problems Identified:

1. Missing value (Sara’s age)
2. Duplicate row (Ali appears twice)
3. Inconsistent capitalization (john)
4. Inconsistent city format (isb)
5. Invalid age (-5 is impossible)
6. Inconsistent date format (2024/02/01 vs 2024-01-01)

After Cleaning:

id	name	age	city	email	join_date
1	Ali	25	Lahore	ali@email.com	2024-01-01
2	Sara	28	Karachi	sara@email.com	2024-01-15
4	John	30	Islamabad	john@email.com	2024-02-01
5	Ahmed	25	Lahore	ahmed@email.com	2024-03-01

16.4 Data Wrangling Process Overview

The 6 Steps of Data Wrangling:

Step	What Happens	Example
1. Discover	Explore and understand the data	View sample rows, column info
2. Structure	Organize and reformat data	Convert data types, rename columns
3. Clean	Fix errors, handle missing data	Remove duplicates, fill missing values
4. Enrich	Add more information	Add age groups, categories
5. Validate	Verify data quality	Check for inconsistencies
6. Publish	Save cleaned data	Export to CSV, database

17. Essential Python Libraries

17.1 NumPy – Numerical Computing

NumPy provides fast, efficient numerical computing in Python.

import numpy as np

# Create arrays
arr = np.array([1, 2, 3, 4, 5])
matrix = np.array([[1, 2], [3, 4]])

# Vectorized operations (fast!)
arr + 10  # [11, 12, 13, 14, 15]
arr * 2   # [2, 4, 6, 8, 10]

# Statistics
print(arr.mean())  # 3.0
print(arr.std())   # 1.41

# Indexing
print(arr[0])      # 1
print(arr[:3])     # [1, 2, 3]

17.2 Pandas – Data Analysis

Pandas is the most important library for data wrangling and analysis.

import pandas as pd

# Create DataFrame
df = pd.DataFrame({
    'name': ['Ali', 'Sara', 'John'],
    'age': [25, 30, 35],
    'salary': [50000, 70000, 60000]
})

# View data
df.head()
df.describe()

# Select data
df['name']
df[df['age'] > 30]

# Clean data
df.drop_duplicates()
df.fillna(df.mean())

17.3 Polars – High-Performance DataFrames

Polars is a modern, high-performance DataFrame library.

import polars as pl

# Create DataFrame
df = pl.DataFrame({
    'name': ['Ali', 'Sara', 'John'],
    'age': [25, 30, 35],
    'salary': [50000, 70000, 60000]
})

# Operations (similar to Pandas but faster)
df.filter(pl.col('age') > 30)
df.group_by('age').agg(pl.col('salary').mean())

Comparison:

Feature	Pandas	Polars
Performance	Fast	Very Fast
Memory	Moderate	Efficient
Learning Curve	Easy	Moderate
Large Datasets	May struggle	Designed for scale
Community	Large	Growing

19. Working with DataFrames

19.1 What Is a DataFrame?

A DataFrame is a 2-dimensional labeled data structure (like a spreadsheet or SQL table).

import pandas as pd

data = {
    "Name": ["Ali", "Sara", "John"],
    "Age": [25, 30, 35],
    "Salary": [50000, 70000, 60000]
}
df = pd.DataFrame(data)
print(df)

Output:

   Name  Age  Salary
0   Ali   25   50000
1  Sara   30   70000
2  John   35   60000

DataFrame Components:

Component	Description	Example
Index	Row labels	0, 1, 2
Columns	Column labels	‘Name’, ‘Age’, ‘Salary’
Values	Data	The actual numbers/text

18.2 Creating DataFrames

# From dictionary
df = pd.DataFrame({"Name": ["Ali", "Sara"], "Age": [25, 30]})

# From list of dictionaries
data = [{"Name": "Ali", "Age": 25}, {"Name": "Sara", "Age": 30}]
df = pd.DataFrame(data)

# From CSV
df = pd.read_csv("data.csv")

# From Excel
df = pd.read_excel("data.xlsx")

# From JSON
df = pd.read_json("data.json")

# From SQL
import sqlite3
conn = sqlite3.connect("database.db")
df = pd.read_sql_query("SELECT * FROM customers", conn)

18.3 Selecting Columns

# Single column (returns Series)
df["Name"]
df.Name  # If no spaces in column name

# Multiple columns (returns DataFrame)
df[["Name", "Salary"]]

# Select columns with conditions
df[df.columns[df.columns.str.contains('^A')]]  # Columns starting with A

18.4 Selecting Rows

# By index position (iloc)
df.iloc[0]        # First row
df.iloc[0:2]      # First two rows
df.iloc[[0, 2]]   # First and third rows

# By label (loc)
df.loc[0]         # Row with index 0
df.loc[0:1]       # Rows 0-1

# By condition
df[df["Age"] > 30]

# First/Last rows
df.head(3)        # First 3 rows
df.tail(2)        # Last 2 rows
df.sample(5)      # Random 5 rows

19. Filtering Data

19.1 Single Conditions

# Numeric filter
filtered = df[df["Age"] > 30]

# String filter
filtered = df[df["City"] == "Lahore"]

# Multiple values
filtered = df[df["City"].isin(["Lahore", "Karachi"])]

# Contains substring
filtered = df[df["Name"].str.contains("A")]

# Starts with
filtered = df[df["Name"].str.startswith("A")]

19.2 Multiple Conditions

# AND condition
filtered = df[(df["Age"] > 25) & (df["Salary"] > 60000)]

# OR condition
filtered = df[(df["Age"] < 25) | (df["Salary"] > 60000)]

# NOT condition
filtered = df[~(df["Age"] < 25)]

# Between
filtered = df[df["Age"].between(25, 35)]

# Not null
filtered = df[df["Age"].notna()]

19.3 Complex Filters

# Using query method
filtered = df.query("Age > 25 and Salary > 60000")

# Using .loc with conditions
filtered = df.loc[df["Age"] > 30, ["Name", "Salary"]]

# Complex condition with functions
filtered = df[df.apply(lambda row: row["Salary"] > row["Age"] * 1000, axis=1)]

20. Merging and Joining Data

20.1 Why Combine Data?

Combining tables is essential for enriching data with additional information.

Example:

• Customers table has customer details
• Orders table has order information
• Merge them to see customer orders

20.2 Merging DataFrames in Pandas

# Sample data
customers = pd.DataFrame({
    'id': [1, 2, 3],
    'name': ['Ali', 'Sara', 'John'],
    'city': ['Lahore', 'Karachi', 'Islamabad']
})

orders = pd.DataFrame({
    'customer_id': [1, 2, 1, 3],
    'product': ['Laptop', 'Phone', 'Tablet', 'Mouse'],
    'price': [1000, 500, 300, 25]
})

# Inner join (default)
merged = pd.merge(customers, orders, left_on='id', right_on='customer_id')

20.3 Inner, Left, Right, and Outer Joins

# Inner Join (only matching rows)
inner = pd.merge(customers, orders, left_on='id', right_on='customer_id', how='inner')

# Left Join (all customers, orders where exist)
left = pd.merge(customers, orders, left_on='id', right_on='customer_id', how='left')

# Right Join (all orders, customers where exist)
right = pd.merge(customers, orders, left_on='id', right_on='customer_id', how='right')

# Outer Join (all rows from both)
outer = pd.merge(customers, orders, left_on='id', right_on='customer_id', how='outer')

Join Visualization:

INNER JOIN:
┌──────┐       ┌──────┐       ┌──────────┐
│ 1    │───┬───│ 1    │───────│ 1, 1     │
│ 2    │   │   │ 2    │       │ 2, 2     │
│ 3    │   │   │ 4    │       └──────────┘
└──────┘   │   └──────┘
           │
           └─── Only matches (1,2) included

LEFT JOIN:
┌──────┐       ┌──────┐       ┌──────────┐
│ 1    │───┬───│ 1    │───────│ 1, 1     │
│ 2    │   │   │ 2    │       │ 2, 2     │
│ 3    │   │   │ 4    │       │ 3, NULL  │
└──────┘   │   └──────┘       └──────────┘
           │
           └─── All from left, matches from right

OUTER JOIN:
┌──────┐       ┌──────┐       ┌──────────┐
│ 1    │───┬───│ 1    │───────│ 1, 1     │
│ 2    │   │   │ 2    │       │ 2, 2     │
│ 3    │   │   │ 4    │       │ 3, NULL  │
└──────┘   │   └──────┘       │ NULL, 4  │
           │                  └──────────┘
           └─── All from both

21. Data Cleaning

21.1 Removing Duplicates

# Remove duplicate rows
df = df.drop_duplicates()

# Remove duplicates based on specific columns
df = df.drop_duplicates(subset=["Name", "City"])

# Keep first or last occurrence
df = df.drop_duplicates(keep="first")
df = df.drop_duplicates(keep="last")

# Find duplicates
df.duplicated()
df.duplicated(subset=["Name"]).sum()

21.2 Validating Data

# Validate Age must be positive
df = df[df["Age"] > 0]

# Validate Age must be within reasonable range
df = df[(df["Age"] > 0) & (df["Age"] < 120)]

# Validate City must be in allowed list
valid_cities = ["Lahore", "Karachi", "Islamabad"]
df = df[df["City"].isin(valid_cities)]

# Validate email format
df = df[df["Email"].str.contains(r'@')]

21.3 Fixing Formats

# Fix date formats
df["Date"] = pd.to_datetime(df["Date"])

# Fix string casing
df["Name"] = df["Name"].str.title()  # Ali Khan
df["Name"] = df["Name"].str.upper()  # ALI KHAN
df["Name"] = df["Name"].str.lower()  # ali khan

# Fix whitespace
df["Name"] = df["Name"].str.strip()

# Fix data types
df["Age"] = df["Age"].astype(int)
df["Salary"] = df["Salary"].astype(float)

22. Handling Missing Data

22.1 Detecting Missing Values

# Check for missing values
df.isnull()  # True where missing

# Count missing per column
df.isnull().sum()

# Percentage of missing per column
df.isnull().sum() / len(df) * 100

# Visualize missing data
import seaborn as sns
sns.heatmap(df.isnull(), cbar=False)

Example Output:

Name      0
Age       5   # 5 missing values
City      2
Salary    3

---

22.2 Strategy 1: Drop Missing Values

# Drop rows with any missing value
df_dropped = df.dropna()

# Drop rows with all missing values
df_dropped = df.dropna(how='all')

# Drop rows with missing in specific columns
df_dropped = df.dropna(subset=['Age', 'Salary'])

# Drop columns with too many missing values
threshold = 0.5  # Drop columns with >50% missing
cols_to_drop = [col for col in df.columns if df[col].isnull().mean() > threshold]
df_dropped = df.drop(columns=cols_to_drop)

When to Use Drop:

• You have lots of data (can afford to lose some rows)
• Missing values are random (not systematic)
• You’re willing to lose some information

22.3 Strategy 2: Fill with a Value

# Fill with mean (for numeric data)
df["Age"] = df["Age"].fillna(df["Age"].mean())

# Fill with median (better if outliers exist)
df["Age"] = df["Age"].fillna(df["Age"].median())

# Fill with mode (for categorical data)
df["City"] = df["City"].fillna(df["City"].mode()[0])

# Fill with specific value
df["Cabin"] = df["Cabin"].fillna("Unknown")

# Forward fill (use previous row - good for time series)
df = df.fillna(method="ffill")

# Backward fill (use next row)
df = df.fillna(method="bfill")

# Fill with group-specific value
df["Age"] = df.groupby("City")["Age"].transform(lambda x: x.fillna(x.mean()))

Comparison of Fill Methods:

Method	Formula	Best For
Mean	Average of values	Symmetric data, no outliers
Median	Middle value	Data with outliers
Mode	Most frequent value	Categorical data
Forward Fill	Previous value	Time series data
Specific Value	User-defined	Known default

22.4 Strategy 3: Predict Missing Values (Imputation)

from sklearn.impute import SimpleImputer, KNNImputer

# Simple imputation with mean
imputer = SimpleImputer(strategy="mean")
df[["Age_imputed"]] = imputer.fit_transform(df[["Age"]])

# KNN imputation (uses similar rows to predict)
knn_imputer = KNNImputer(n_neighbors=5)
df[["Age_knn"]] = knn_imputer.fit_transform(df[["Age"]])

# Model-based imputation
from sklearn.linear_model import LinearRegression

# Use other features to predict missing values
train = df[df["Age"].notna()]
test = df[df["Age"].isna()]

model = LinearRegression()
model.fit(train[["Salary", "Education_Level"]], train["Age"])
test["Age_predicted"] = model.predict(test[["Salary", "Education_Level"]])

22.5 When to Use Each Strategy

Strategy	When to Use	Pros	Cons
Drop	Lots of data, random missing	Simple, fast	Loses information
Fill with Mean	Numeric, symmetric data	Simple, fast	Can skew data
Fill with Median	Numeric with outliers	Robust to outliers	Ignores data shape
Fill with Mode	Categorical data	Simple, preserves categories	May oversimplify
Forward Fill	Time series	Preserves sequence	Assumes pattern continues
KNN Imputation	Enough data for similarity	Most accurate	Slower, needs tuning
Model Imputation	Complex patterns	Very accurate	Complex, overfitting risk

22.6 Mini Project: Missing Data Handling

import pandas as pd
import numpy as np

# Create a small dataset with missing values
df = pd.DataFrame({
    "Age": [25, np.nan, 30, 35, np.nan, 28, 45, np.nan, 32, 40],
    "Salary": [50000, 60000, np.nan, 70000, 55000, 65000, 80000, 75000, np.nan, 68000],
    "City": ["Lahore", "Karachi", np.nan, "Lahore", "Islamabad",
             "Karachi", "Lahore", np.nan, "Islamabad", "Karachi"],
    "Experience": [2, np.nan, 5, 7, 3, 4, 10, 8, 6, np.nan]
})

print("Original Data:")
print(df)
print("\nMissing Values:")
print(df.isnull().sum())

# Strategy 1: Drop rows with >2 missing values
df1 = df.dropna(thresh=4)  # Keep rows with at least 4 non-null
print(f"\nAfter dropping rows (Strategy 1): {len(df1)} rows")

# Strategy 2: Fill numeric with median, categorical with mode
df2 = df.copy()
df2["Age"] = df2["Age"].fillna(df2["Age"].median())
df2["Salary"] = df2["Salary"].fillna(df2["Salary"].median())
df2["Experience"] = df2["Experience"].fillna(df2["Experience"].median())
df2["City"] = df2["City"].fillna(df2["City"].mode()[0])
print(f"\nAfter filling (Strategy 2):")
print(df2)

# Strategy 3: Forward fill (time series style)
df3 = df.copy()
df3 = df3.fillna(method="ffill")
print(f"\nAfter forward fill (Strategy 3):")
print(df3)

23. Outlier Detection and Treatment

23.1 What Is an Outlier?

An outlier is a data point that is significantly different from other observations in the dataset.

Real-World Analogy: Imagine a class of 30 students. Most are 18-22 years old, but one student is 65. That student is an outlier. They might be a legitimate case (a returning student), or it might be a data entry error.

Common Causes of Outliers:

• Data entry errors
• Measurement errors
• Legitimate rare events
• Data processing mistakes
• Natural variation

When to Keep Outliers:

• Fraud detection (fraudulent transactions ARE outliers)
• Rare diseases (the outliers are the cases of interest)
• Extreme weather events (the outliers are the events we want to predict)

When to Remove Outliers:

• Data entry errors
• Sensor malfunctions
• Mistakes in data collection
• Unrealistic values

23.2 Visualizing Outliers

import matplotlib.pyplot as plt
import seaborn as sns
import numpy as np
import pandas as pd

# Create sample data with outliers
np.random.seed(42)
normal_data = np.random.normal(50, 10, 200)  # 200 normal values
outliers = np.array([120, 150, -30, 200])     # 4 outliers
data = np.concatenate([normal_data, outliers])
df = pd.DataFrame({"values": data})

# Method 1: Histogram
plt.figure(figsize=(12, 4))

plt.subplot(1, 3, 1)
plt.hist(df["values"], bins=30, color="steelblue", edgecolor="black")
plt.title("Histogram")
plt.xlabel("Value")
plt.ylabel("Frequency")

# Method 2: Box Plot
plt.subplot(1, 3, 2)
plt.boxplot(df["values"])
plt.title("Box Plot")
plt.ylabel("Value")

# Method 3: Seaborn Box Plot
plt.subplot(1, 3, 3)
sns.boxplot(y=df["values"])
plt.title("Seaborn Box Plot")

plt.tight_layout()
plt.show()

What to Look For:

• Histogram: Bars far away from the main distribution
• Box Plot: Points beyond the whiskers (dots)
• Scatter Plot: Points far away from the main cluster

23.3 IQR Method (Interquartile Range)

The IQR method is the most common way to detect outliers.

# Calculate quartiles
Q1 = df["values"].quantile(0.25)   # 25th percentile
Q3 = df["values"].quantile(0.75)   # 75th percentile
IQR = Q3 - Q1                      # Interquartile range

# Calculate fences
lower_fence = Q1 - 1.5 * IQR
upper_fence = Q3 + 1.5 * IQR

print(f"Q1: {Q1:.2f}")
print(f"Q3: {Q3:.2f}")
print(f"IQR: {IQR:.2f}")
print(f"Lower fence: {lower_fence:.2f}")
print(f"Upper fence: {upper_fence:.2f}")

# Detect outliers
outliers = df[(df["values"] < lower_fence) | (df["values"] > upper_fence)]
print(f"\nOutliers found: {len(outliers)}")
print(outliers.head())

Example Output:

Q1: 42.50
Q3: 57.50
IQR: 15.00
Lower fence: 20.00
Upper fence: 80.00

Outliers found: 4
   values
0    120.0
1    150.0
2    -30.0
3    200.0

23.4 Z-Score Method

The Z-score method identifies outliers based on standard deviations from the mean.

from scipy import stats

# Calculate Z-scores
z_scores = np.abs(stats.zscore(df["values"]))

# Identify outliers (>3 standard deviations)
outliers_zscore = df[z_scores > 3]

print(f"\nOutliers by Z-score: {len(outliers_zscore)}")
print(outliers_zscore)

Interpretation:

• Z-score tells you how many standard deviations a value is from the mean
• |Z| > 3 is typically considered an outlier
• Works well for normally distributed data

23.5 Handling Outliers

Action	Code	When to Use
Remove	df_no_outliers = df[(df >= lower_fence) & (df <= upper_fence)]	Data entry errors, sensor malfunctions
Keep	Leave as is	Genuine rare cases (fraud, extreme events)
Cap (Winsorize)	df.clip(lower=lower_fence, upper=upper_fence)	When you want to reduce extreme influence but keep the data
Replace with Median	df.loc[idx] = median	When you want to fix errors but keep the row

# Option 1: Remove outliers
df_no_outliers = df[(df["values"] >= lower_fence) & (df["values"] <= upper_fence)]
print(f"After removing outliers: {len(df_no_outliers)} rows (was {len(df)})")

# Option 2: Cap outliers
df["values_capped"] = df["values"].clip(lower=lower_fence, upper=upper_fence)
print(f"Max after capping: {df['values_capped'].max():.2f}")

# Option 3: Replace with median
df["values_fixed"] = df["values"].copy()
median_value = df["values"].median()
df.loc[z_scores > 3, "values_fixed"] = median_value
print(f"Median replacement applied to {len(df[z_scores > 3])} outliers")

23.6 Mini Project: Outlier Detection

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

# Create a dataset with outliers
np.random.seed(42)
df = pd.DataFrame({
    'Age': np.random.normal(35, 10, 1000),
    'Salary': np.random.normal(60000, 15000, 1000)
})

# Add some outliers
df.loc[0, 'Age'] = 200
df.loc[1, 'Salary'] = 1000000
df.loc[2, 'Age'] = -5
df.loc[3, 'Salary'] = 0

print("Dataset Summary:")
print(df.describe())

# Detect outliers using IQR for Age
Q1 = df['Age'].quantile(0.25)
Q3 = df['Age'].quantile(0.75)
IQR = Q3 - Q1
lower = Q1 - 1.5 * IQR
upper = Q3 + 1.5 * IQR

age_outliers = df[(df['Age'] < lower) | (df['Age'] > upper)]
print(f"\nAge outliers: {len(age_outliers)}")

# Detect outliers using IQR for Salary
Q1 = df['Salary'].quantile(0.25)
Q3 = df['Salary'].quantile(0.75)
IQR = Q3 - Q1
lower = Q1 - 1.5 * IQR
upper = Q3 + 1.5 * IQR

salary_outliers = df[(df['Salary'] < lower) | (df['Salary'] > upper)]
print(f"Salary outliers: {len(salary_outliers)}")

# Visualize
plt.figure(figsize=(12, 5))
plt.subplot(1, 2, 1)
plt.boxplot(df['Age'])
plt.title('Age - Box Plot')
plt.ylabel('Age')

plt.subplot(1, 2, 2)
plt.boxplot(df['Salary'])
plt.title('Salary - Box Plot')
plt.ylabel('Salary')
plt.tight_layout()
plt.show()

24. Feature Engineering

24.1 What Is Feature Engineering?

Feature Engineering is the process of creating new, more useful features from existing data to improve machine learning model performance.

Real-World Analogy: Feature engineering is like a chef deciding which ingredients to combine and how to prepare them to create a delicious dish. You take raw ingredients (raw data) and combine them in clever ways to create new flavors (features) that make the dish better.

Why Feature Engineering Matters:

• Better features → Better models
• Transforms raw data into predictive signals
• Can capture complex relationships
• Reduces the burden on the model

24.2 Binary Features

Binary features convert continuous values into 0/1 flags.

# Create binary from threshold
df["High_Salary"] = (df["Salary"] > 60000).astype(int)

# Create binary from multiple conditions
df["Is_Young_Adult"] = ((df["Age"] >= 18) & (df["Age"] <= 25)).astype(int)

# Create binary from categories
df["Is_Student"] = (df["Occupation"] == "Student").astype(int)
df["Is_Senior"] = (df["Age"] > 65).astype(int)

# Create binary from time
df["Is_Weekend"] = df["Date"].dt.dayofweek.isin([5, 6]).astype(int)

24.3 Interaction Features

Interaction features capture relationships between two or more variables.

# Multiply two features
df["Salary_per_Age"] = df["Salary"] / df["Age"]

# Multiply dummy variables
df["Female_and_High_Salary"] = (df["Is_Female"] & df["High_Salary"]).astype(int)

# Product of two features
df["Age_Income"] = df["Age"] * df["Income"]

# Ratio of two features
df["Debt_to_Income"] = df["Monthly_Debt"] / df["Monthly_Income"]

24.4 Polynomial Features

Polynomial features add higher-order terms.

# Square terms
df["Age_Squared"] = df["Age"] ** 2
df["Age_Cubed"] = df["Age"] ** 3

# Using Scikit-learn
from sklearn.preprocessing import PolynomialFeatures

poly = PolynomialFeatures(degree=2, include_bias=False)
X_poly = poly.fit_transform(X)

24.5 Temporal Features

Temporal features extract information from dates and times.

# Convert to datetime
df["Date"] = pd.to_datetime(df["Date"])

# Extract components
df["Year"] = df["Date"].dt.year
df["Month"] = df["Date"].dt.month
df["Day"] = df["Date"].dt.day
df["Day_of_Week"] = df["Date"].dt.dayofweek  # Monday=0, Sunday=6
df["Quarter"] = df["Date"].dt.quarter
df["Is_Weekend"] = df["Date"].dt.dayofweek.isin([5, 6]).astype(int)

# Time since event
df["Days_Since_Start"] = (df["Date"] - df["Start_Date"]).dt.days

# Age from birth date
df["Age"] = (pd.Timestamp.now() - df["Birth_Date"]).dt.days / 365

25. Encoding Categorical Variables

25.1 Why Encoding Is Necessary

AI models work with numbers. Only numbers. They cannot process text like “Male” or “Lahore” directly. You need to convert categories into numbers first.

Real-World Analogy: Encoding categorical variables is like translating text from one language to another. The model only understands “numeric language,” so you must translate your categories into numbers.

Types of Categorical Data:

Type	Description	Example	Encoding Method
Nominal	No inherent order	Cities, colors, gender	One-Hot Encoding
Ordinal	Has inherent order	Education level, satisfaction	Label/Ordinal Encoding
Binary	Only two categories	Yes/No, Male/Female	Binary Encoding

25.2 Label Encoding

Label Encoding assigns a number to each category.

from sklearn.preprocessing import LabelEncoder

df = pd.DataFrame({
    "city": ["Lahore", "Karachi", "Islamabad", "Lahore", "Karachi"],
    "education": ["Bachelor", "Master", "PhD", "Bachelor", "Master"]
})

le = LabelEncoder()
df["city_encoded"] = le.fit_transform(df["city"])
df["education_encoded"] = le.fit_transform(df["education"])

print(df)

Output:

        city education  city_encoded  education_encoded
0     Lahore  Bachelor             1                  0
1    Karachi    Master             0                  2
2  Islamabad       PhD             2                  1
3     Lahore  Bachelor             1                  0
4    Karachi    Master             0                  2

Problem: The model might think Islamabad (2) is “bigger” than Karachi (0). For cities, there is no such ordering. Label encoding works well for ordinal data (education levels) but poorly for nominal data (cities).

When to Use Label Encoding:

• Good: Ordered categories (education level, seniority, ranking)
• Bad: Nominal categories (cities, colors, countries)

25.3 One-Hot Encoding

One-Hot Encoding creates a separate column for each category.

# Using pandas
df_encoded = pd.get_dummies(df, columns=["city"])
print(df_encoded)

Output:

   city_Islamabad  city_Karachi  city_Lahore
0           False         False         True
1           False          True        False
2            True         False        False
3           False         False         True
4           False          True        False

Now instead of one city column, we have three binary columns. The model treats each city independently with no implied ordering.

# Using scikit-learn
from sklearn.preprocessing import OneHotEncoder

cities = np.array(["Lahore", "Karachi", "Islamabad", "Lahore"]).reshape(-1, 1)

ohe = OneHotEncoder(sparse_output=False)
encoded = ohe.fit_transform(cities)

print(encoded)
print(f"Feature names: {ohe.get_feature_names_out()}")

When to Use One-Hot Encoding:

• Good: Nominal categories with no ordering
• Bad: Categories with many unique values (creates too many columns)

Trade-off:

• More columns = more memory
• Can cause multicollinearity (drop one column to avoid)
• Better for model interpretability

25.4 Ordinal Encoding

Ordinal Encoding is for categories with meaningful ordering.

from sklearn.preprocessing import OrdinalEncoder

education = [["Bachelor"], ["Master"], ["PhD"], ["Bachelor"], ["Master"]]

oe = OrdinalEncoder(categories=[["Bachelor", "Master", "PhD"]])
encoded = oe.fit_transform(education)

print(encoded)
# [[0.]  — Bachelor
#  [1.]  — Master
#  [2.]  — PhD
#  [0.]
#  [1.]]

The ordering is preserved: PhD (2) > Master (1) > Bachelor (0).

When to Use Ordinal Encoding:

• Good: Ordered categories with meaningful hierarchy
• Bad: Nominal categories with no natural order

25.5 When to Use Each Method

┌─────────────────────────────────────────────────────────────────┐
│                 CATEGORICAL ENCODING DECISION TREE              │
│                                                                 │
│  ┌────────────────────────────────────────────────────────┐     │
│  │  Does the category have a natural order?               │     │
│  └────────────────────────────────────────────────────────┘     │
│                              │                                  │
│              ┌───────────────┴───────────────┐                  │
│              ▼                               ▼                  │
│            YES                              NO                  │
│              │                               │                  │
│              ▼                               ▼                  │
│     ┌─────────────────────┐         ┌─────────────────────┐     │
│     │  Ordinal Encoding   │         │  One-Hot Encoding   │     │
│     │  (Label Encoding)   │         │                     │     │
│     └─────────────────────┘         └─────────────────────┘     │
│              │                               │                  │
│              ▼                               ▼                  │
│     Example: Education Level          Example: Cities           │
│     Bachelor < Master < PhD          Lahore vs Karachi          │
└─────────────────────────────────────────────────────────────────┘

Comparison Summary:

Method	For	Pros	Cons
Label Encoding	Ordinal	Simple, compact	Implies order that doesn’t exist
One-Hot Encoding	Nominal	No implied order	Many columns, memory heavy
Ordinal Encoding	Ordinal	Preserves order	Only for ordered categories

26. Feature Scaling and Normalization

26.1 Why Scaling Is Necessary

Features on different scales can mislead models.

Real-World Analogy: Imagine you’re evaluating job applicants. If one feature is measured in “experience years” (0-50) and another in “salary” (20,000-200,000), the salary feature will dominate simply because the numbers are bigger. Scaling puts everything on the same level.

Example Problem:

Feature	Range	Problem
Age	18-70	Small numbers
Income	10,000-5,000,000	Huge numbers → dominates model
Experience	0-40	Small numbers

Unscaled Data:

Age: 18-70
Income: 10,000-5,000,000
Experience: 0-40

The model might think income is millions of times more important than age simply because the numbers are larger.

Scaled Data:

Age: 0-1
Income: 0-1
Experience: 0-1

Now all features are on the same scale. The model can compare them fairly.

26.2 Min-Max Scaling (Normalization)

Min-Max Scaling scales values to a fixed range, typically [0, 1].

Formula: (value – minimum) / (maximum – minimum)

from sklearn.preprocessing import MinMaxScaler

data = np.array([[18, 25000],
                 [35, 80000],
                 [52, 150000],
                 [67, 400000]])

scaler = MinMaxScaler()
scaled = scaler.fit_transform(data)

print("Original:")
print(data)
print("\nAfter Min-Max Scaling:")
print(scaled.round(4))

Output:

Original:
[[ 18  25000]
 [ 35  80000]
 [ 52 150000]
 [ 67 400000]]

After Min-Max Scaling:
[[0.     0.    ]
 [0.3449 0.1459]
 [0.6939 0.3324]
 [1.     1.    ]]

When to Use Min-Max Scaling:

• You know the data has definite min and max values
• You need values specifically between 0 and 1
• Neural networks (activations often expect 0-1 input)
• Image data (pixel values are 0-255 → normalize to 0-1)

27.3 Standard Scaling (Standardization)

Standard Scaling transforms data to have mean=0 and standard deviation=1.

Formula: (value – mean) / standard_deviation

from sklearn.preprocessing import StandardScaler

scaler = StandardScaler()
scaled = scaler.fit_transform(data)

print("After Standard Scaling:")
print(scaled.round(4))
print(f"\nMean of each column: {scaled.mean(axis=0).round(4)}")
print(f"Std of each column: {scaled.std(axis=0).round(4)}")

Output:

After Standard Scaling:
[[-1.3416 -1.1459]
 [-0.4472 -0.5237]
 [ 0.4472 -0.0209]
 [ 1.3416  1.6905]]

Mean of each column: [0. 0.]
Std of each column: [1. 1.]

When to Use Standard Scaling:

• Most machine learning algorithms (Linear Regression, Logistic Regression, SVM, Neural Networks)
• Data has outliers (min-max is sensitive to outliers)
• You don’t know the min/max values
• You want to preserve the relative distances between values

27.4 Robust Scaling

Robust Scaling uses median and IQR, making it less sensitive to outliers.

from sklearn.preprocessing import RobustScaler

scaler = RobustScaler()
scaled = scaler.fit_transform(data)

print("After Robust Scaling:")
print(scaled.round(4))

When to Use Robust Scaling:

• When your data has many outliers
• When you want to be robust to extreme values
• When you care about the median rather than the mean

26.5 When to Use Each Method

┌─────────────────────────────────────────────────────────────────┐
│                  SCALING DECISION TREE                          │
│                                                                 │
│  ┌────────────────────────────────────────────────────────┐     │
│  │  Do you need values between 0 and 1?                   │     │
│  └────────────────────────────────────────────────────────┘     │
│                              │                                  │
│              ┌───────────────┴───────────────┐                  │
│              ▼                               ▼                  │
│            YES                              NO                  │
│              │                               │                  │
│              ▼                               ▼                  │
│     ┌─────────────────────┐         ┌─────────────────────┐     │
│     │  Min-Max Scaling    │         │  Does your data     │     │
│     │  (Normalization)    │         │  have outliers?     │     │
│     └─────────────────────┘         └─────────────────────┘     │
│                                                  │              │
│                                      ┌───────────┴───────────┐  │
│                                      ▼                       ▼  │
│                              ┌─────────────┐          ┌─────────┤
│                              │    YES      │          │   NO    │
│                              └─────────────┘          └─────────┤
│                                      │                       │  │
│                                      ▼                       ▼  │
│                              ┌─────────────┐          ┌─────────┤
│                              │   Robust    │          │ Standard│
│                              │   Scaling   │          │ Scaling │
│                              └─────────────┘          └─────────┤
└─────────────────────────────────────────────────────────────────┘

Comparison Summary:

Method	Range	Sensitive to Outliers?	Best For
Min-Max	[0, 1]	Yes (compresses others)	Neural networks, image data
Standard	Mean=0, Std=1	No (but affected)	Most ML algorithms
Robust	Median=0, IQR=1	No	Data with many outliers
Normalization	[0, 1]	Yes	Known bounds

27. The Golden Rule: Fit on Training Data Only

27.1 What Is Data Leakage?

Data Leakage occurs when information from outside the training dataset is used to create the model. This makes the model look better than it actually is.

Real-World Analogy: Data leakage is like studying for a test using the actual test questions. You’ll get a perfect score on the test, but you haven’t actually learned the material. When you face a different test (real-world data), you’ll fail.

Examples of Data Leakage:

1. Scaling before splitting
2. Using test data for feature selection
3. Imputing missing values using test data
4. Using future data for training (in time series)
5. Training on data that includes information you wouldn’t have at prediction time

27.2 Correct Approach: Fit → Transform Training, Transform Test

from sklearn.preprocessing import StandardScaler
from sklearn.model_selection import train_test_split

# Create dummy data
X = np.random.randn(100, 3) * [10, 1000, 0.5]
y = np.random.randint(0, 2, 100)

# Step 1: Split data FIRST
X_train, X_test, y_train, y_test = train_test_split(
    X, y, test_size=0.2, random_state=42
)

# Step 2: Create scaler
scaler = StandardScaler()

# Step 3: Fit on training data ONLY, transform both
X_train_scaled = scaler.fit_transform(X_train)  # fit + transform
X_test_scaled = scaler.transform(X_test)        # ONLY transform

# Training data uses fit_transform (calculates mean/std from training data)
# Test data uses transform (uses the training data's mean/std)

27.3 Wrong Approach (Data Leakage)

# WRONG: Fit on ALL data, then split
# This is data leakage!

scaler = StandardScaler()
X_scaled = scaler.fit_transform(X)  # Fit on all data

# Then split
X_train, X_test = train_test_split(X_scaled, test_size=0.2)

# The scaler has seen information from the test data!

What Happens:

• The scaler used the test data to calculate mean and std
• The model has “peeked” at the test data
• Performance will be overly optimistic
• In real deployment, you won’t have that data

27.4 Why This Rule Applies to ALL Preprocessing

Preprocessing Step	Data Engineering Perspective	Data Science Perspective
Scaling	Fit on training, transform test	Same
Encoding	Fit on training, transform test	Same
Imputation	Fit on training, transform test	Same
Feature Selection	Fit on training, transform test	Same
PCA/Dimensionality Reduction	Fit on training, transform test	Same

Example with Imputation:

# CORRECT
from sklearn.impute import SimpleImputer

# Split first
X_train, X_test = train_test_split(X, test_size=0.2)

# Fit imputer on training data
imputer = SimpleImputer(strategy="mean")
X_train_imputed = imputer.fit_transform(X_train)  # Fit + transform

# Transform test data using training mean
X_test_imputed = imputer.transform(X_test)  # Only transform

28. Train, Validation, and Test Sets

28.1 Why We Split Data

This is one of the most fundamental concepts in all of AI. Get this wrong and everything that follows is invalid.

Why do we split? If you train and test on the same data, the model can simply “memorize” every example. It would score 100% on the test, but fail completely on real new data. This is like showing a student the exact exam questions while studying, then testing them on those same questions. Of course they pass — but they have not actually learned anything useful.

28.2 The Three Sets

The Three Sets:

Set	Purpose	Typical Size
Training Set	What the model learns from	60-80%
Validation Set	Used during training to tune settings (hyperparameters)	10-20%
Test Set	Used only once, at the very end, for final evaluation	10-20%

Detailed Explanation:

Set	Purpose	How Used	When Used
Training Set	Learn patterns and weights	Model sees this data and updates	During training (multiple times)
Validation Set	Tune hyperparameters	Model doesn’t update weights here	During training (after each epoch)
Test Set	Final evaluation	Model never sees this data	Only once, after training is complete

Why Three Sets Instead of Two:

Without Validation Set:
┌─────────────────────────────────────────────────────────────┐
│  Train Model → Test on Test Data → Tune → Test Again        │
│                                                             │
│  Problem: You're optimizing for the test data!              │
│  (Data leakage through repeated testing)                    │
└─────────────────────────────────────────────────────────────┘

With Validation Set:
┌─────────────────────────────────────────────────────────────┐
│  Train Model → Validate on Val Data → Tune → Validate       │
│  → ... → Train Final Model → Test ONCE on Test Data         │
│                                                             │
│  Result: Unbiased evaluation of real-world performance      │
└─────────────────────────────────────────────────────────────┘

28.3 Split Ratios

Scenario	Train	Validation	Test
Standard	70%	15%	15%
Small Dataset	60%	20%	20%
Large Dataset	80%	10%	10%
Very Large Dataset	95%	2.5%	2.5%
Time Series	80% (past)	10% (mid)	10% (future)

28.4 Implementation in Python

from sklearn.model_selection import train_test_split

# Create dummy data
X = np.random.randn(1000, 5)
y = np.random.randint(0, 2, 1000)

# Split: 70% train, 15% validation, 15% test
X_temp, X_test, y_temp, y_test = train_test_split(
    X, y, test_size=0.15, random_state=42
)

X_train, X_val, y_train, y_val = train_test_split(
    X_temp, y_temp, test_size=0.176,  # 0.176 of 85% ≈ 15% of total
    random_state=42
)

print(f"Training set:   {len(X_train):4d} samples ({len(X_train)/len(X):.1%})")
print(f"Validation set: {len(X_val):4d} samples ({len(X_val)/len(X):.1%})")
print(f"Test set:       {len(X_test):4d} samples ({len(X_test)/len(X):.1%})")

Output:

Training set:    700 samples (70.0%)
Validation set:  150 samples (15.0%)
Test set:        150 samples (15.0%)

29. Data Augmentation

29.1 Why Augment Data?

Sometimes you do not have enough data. Data augmentation creates new training examples from existing ones.

Real-World Analogy: If you only have one photo of a dog, data augmentation is like taking that photo and making 20 variations of it cropping, rotating, flipping, adjusting brightness. Now you have 20 training examples instead of 1.

When to Use Data Augmentation:

• Small dataset
• Imbalanced classes
• Limited ability to collect more data
• Want to improve model generalization

29.2 Image Augmentation

from tensorflow.keras.preprocessing.image import ImageDataGenerator

datagen = ImageDataGenerator(
    rotation_range=20,      # Random rotation up to 20 degrees
    width_shift_range=0.1,  # Random horizontal shift up to 10%
    height_shift_range=0.1, # Random vertical shift up to 10%
    horizontal_flip=True,   # Random horizontal flip
    zoom_range=0.1,         # Random zoom
    brightness_range=[0.8, 1.2],  # Random brightness adjustment
    shear_range=0.1,        # Random shear
    fill_mode='nearest'     # How to fill empty pixels
)

# Apply augmentation
train_generator = datagen.flow_from_directory(
    'train_images/',
    target_size=(224, 224),
    batch_size=32,
    class_mode='categorical'
)

29.3 Text Augmentation

import random
import nltk

def augment_text(sentence):
    words = sentence.split()
    augmented_versions = []

    # 1. Synonym Replacement
    # Replace a word with its synonym
    # (Requires wordnet: nltk.download('wordnet'))

    # 2. Random Insertion
    # Insert a random word (could use synonyms)

    # 3. Random Deletion
    if len(words) > 3:
        idx = random.randint(0, len(words)-1)
        deleted = words[:idx] + words[idx+1:]
        augmented_versions.append(" ".join(deleted))

    # 4. Random Swap
    if len(words) > 2:
        idx1, idx2 = random.sample(range(len(words)), 2)
        swapped = words.copy()
        swapped[idx1], swapped[idx2] = swapped[idx2], swapped[idx1]
        augmented_versions.append(" ".join(swapped))

    return augmented_versions

original = "The movie was absolutely fantastic and I loved every minute"
augmented = augment_text(original)

print(f"Original:    {original}")
for i, aug in enumerate(augmented):
    print(f"Augmented {i+1}: {aug}")

Output:

Original:    The movie was absolutely fantastic and I loved every minute
Augmented 1: The movie absolutely fantastic and I loved every minute
Augmented 2: The was movie absolutely fantastic and I loved every minute

Part 2: DATA SCIENCE – ANALYZING THE CLEANED MATERIAL

30. Introduction to Data Science

30.1 What Is Data Science?

Data Science is the practice of extracting insights, patterns, and predictions from data using statistical methods, machine learning, and domain expertise.

Real-World Analogy: Data Science is like being a detective who examines evidence (data) to solve a case (business problem). You look for clues, connect dots, and make predictions about what will happen next.

The Data Science Mindset:

Aspect	Description
Curiosity	Always asking “Why?” and “What if?”
Skepticism	Questioning assumptions and results
Experimentation	Testing hypotheses with data
Communication	Explaining insights to non-technical audiences
Business Focus	Solving real problems that create value

30.2 The Data Science Intersection

Data science lives at the intersection of three core disciplines:

┌─────────────────────────────────────────────────────────────────┐
│                    DATA SCIENCE VENN DIAGRAM                    │
│                                                                 │
│                    ┌───────────────────┐                        │
│                    │   MATHEMATICS &    │                       │
│                    │    STATISTICS      │                       │
│                    │                   │                        │
│                    │  • Probability    │                        │
│                    │  • Linear Algebra │                        │
│                    │  • Calculus       │                        │
│                    │  • Statistical    │                        │
│                    │    Inference      │                        │
│                    └─────────┬─────────┘                        │
│                              │                                  │
│              ┌───────────────┼───────────────┐                  │
│              │               │               │                  │
│              ▼               ▼               ▼                  │
│  ┌───────────────────┐ ┌───────────────┐ ┌───────────────────┐  │
│  │   COMPUTER        │ │   DATA        │ │   BUSINESS        │  │
│  │   SCIENCE &       │ │   SCIENCE     │ │   & DOMAIN        │  │
│  │   PROGRAMMING     │ │   INTERSECTION│ │   KNOWLEDGE       │  │
│  │                   │ │               │ │                   │  │
│  │  • Python/R      │ │  • Machine    │ │  • Industry        │  │
│  │  • SQL           │ │    Learning   │ │    Expertise       │  │
│  │  • Data          │ │  • Data       │ │  • Problem         │  │
│  │    Structures    │ │    Visualization│    Framing         │  │
│  │  • Algorithms    │ │  • Predictive │ │  • Decision        │  │
│  │  • Software      │ │    Modeling   │ │    Making          │  │
│  │    Engineering   │ │               │ │                    │  │
│  └───────────────────┘ └───────────────┘ └───────────────────┘  │
└─────────────────────────────────────────────────────────────────┘

The Intersection Explained:

Discipline	Role in Data Science
Mathematics & Statistics	For building models, predicting numbers, and verifying if trends are statistically significant
Computer Science & Programming	For writing code that cleans millions of data rows in seconds
Business & Domain Knowledge	For asking the right questions so that the findings actually solve a business problem

30.3 How AI (ChatGPT, Midjourney) Is Built on Data Science

Modern AI tools are built on data science principles:

AI Tool	What It Does	Data Science Behind It
ChatGPT	Generates human-like text	Trained on billions of text documents to learn language patterns
Midjourney	Creates images from text	Learned from millions of image-caption pairs
Healthcare AI	Predicts diseases	Analyzed millions of medical records and scans
Recommendation Systems	Suggests products/content	Analyzed millions of user interactions

💡 Key Insight: These AI systems are not “intelligent” in the human sense. They are sophisticated pattern-matchers that have been trained on enormous datasets. The quality of their outputs depends entirely on the quality and quantity of the data they were trained on.

---

30.4 Data-Driven Decision Making

Data-Driven Decision Making means making decisions based on data rather than intuition or guesswork.

Key Principles:

Principle	Explanation
Measure what matters	Track key metrics that align with business goals
Test before implementing	Use A/B testing to validate changes
Learn from past data	Historical trends inform future strategies
Be objective	Let data override personal bias

Real-World Examples:

Company	Data-Driven Decision	Impact
Google	Uses data to rank search results	Better search accuracy
Amazon	Uses purchase history to recommend products	Increased sales
Netflix	Uses viewing patterns to recommend shows	Higher user engagement
Uber	Uses real-time data to adjust prices (surge pricing)	Balanced supply and demand

31. The Data Science Lifecycle

31.1 Step-by-Step Data Science Process

Step 1: Problem Definition – Step 2: Data Collection – Step 3: Data Cleaning – Step 4: EDA (Exploratory Data Analysis) – Step 5: Modeling – Step 6: Evaluation – Step 7: Deployment.

31.2 Step 1: Problem Definition

Goal: Understand what business problem needs to be solved. Define objectives, identify success metrics, understand constraints.

Key Questions:

• What is the business problem we’re trying to solve?
• What would success look like?
• What data do we need?
• What are the constraints (time, budget, resources)?

Example:

Aspect	Description
Business Problem	Customers are leaving (churn rate is 25%)
Success Metric	Reduce churn to 15% in 6 months
Data Needed	Customer demographics, usage patterns, support interactions
Resources	2 data scientists, 3 months, budget for cloud

31.3 Step 2: Data Collection

Goal: Gather the data needed to solve the problem. Identify sources, extract data, store data.

# Example: Collecting data from multiple sources
import pandas as pd

# From CSV
customers = pd.read_csv("customers.csv")

# From SQL database
import sqlite3
conn = sqlite3.connect("sales.db")
usage = pd.read_sql_query("SELECT * FROM usage_logs", conn)

# From API
import requests
response = requests.get("https://api.example.com/support_tickets")
support = response.json()

31.4 Step 3: Data Cleaning

Goal: Fix issues in the data. Handle missing values, remove duplicates, correct errors.

# Example: Cleaning data
# Handle missing values
df = df.fillna(df.median())

# Remove duplicates
df = df.drop_duplicates()

# Fix formats
df["Date"] = pd.to_datetime(df["Date"])

# Validate data
df = df[df["Age"] > 0]

31.5 Step 4: EDA (Exploratory Data Analysis)

Goal: Explore and understand the data. Visualize distributions, find patterns, detect anomalies

# Example: EDA
# View data structure
df.info()

# Summary statistics
df.describe()

# Visualizations
import matplotlib.pyplot as plt
df.hist(figsize=(12, 8))
plt.show()

# Correlation analysis
df.corr()

32.6 Step 5: Modeling

Goal: Build predictive models. Train algorithms, tune parameters, compare models.

# Example: Building a model
from sklearn.ensemble import RandomForestClassifier
from sklearn.model_selection import train_test_split

# Split data
X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2)

# Train model
model = RandomForestClassifier()
model.fit(X_train, y_train)

# Make predictions
predictions = model.predict(X_test)

31.7 Step 6: Evaluation

Goal: Test model performance. Validate on test data, check metrics, ensure business value.

# Example: Evaluation
from sklearn.metrics import accuracy_score, classification_report

# Calculate metrics
accuracy = accuracy_score(y_test, predictions)
print(f"Accuracy: {accuracy:.2%}")

# Detailed report
print(classification_report(y_test, predictions))

31.8 Step 7: Deployment

Goal: Put model into production. Build API, integrate with systems, monitor performance.

# Example: Building an API for the model
from fastapi import FastAPI
import pickle

app = FastAPI()

# Load model
model = pickle.load(open("model.pkl", "rb"))

@app.post("/predict")
async def predict(data: dict):
    prediction = model.predict([data["features"]])
    return {"prediction": prediction[0]}

32. CRISP-DM Framework

CRISP-DM (Cross Industry Standard Process for Data Mining) is a standard data science methodology.

CRISP-DM Steps:

Step	Description	Key Activities
1. Business Understanding	Understand the business problem and objectives.	Define goals, assess situation, determine success criteria, Determine data mining goals, Produce project plan
2. Data Understanding	Collect and explore data to get familiar with it	Collect initial data, describe data, explore data, verify quality
3. Data Preparation	Clean, transform, and prepare data for modeling	Select data, clean data, Construct new attributes (feature engineering), integrate data, Format data
4. Modeling	Apply machine learning algorithms	Select modeling technique, Generate test design, build model, assess model
5. Evaluation	Assess if the model meets business objectives	Evaluate results, review process, determine next steps
6. Deployment	Deploy the model into production	Plan deployment, monitor and maintain, produce final report, Review project

Key Insight: CRISP-DM is iterative. You often go back to earlier steps as you learn more. It’s not a linear process—it’s a cycle of learning and refinement.

33. Exploratory Data Analysis (EDA)

33.1 What Is EDA?

EDA (Exploratory Data Analysis) is the process of analyzing datasets using statistical summaries and visualizations to discover patterns, anomalies, and relationships.

Real-World Analogy: EDA is like being a detective at a crime scene. You don’t jump to conclusions. Instead, you examine all the evidence (data), look for clues (patterns), identify anything unusual (anomalies), and build a theory (hypothesis) about what happened.

Key Questions EDA Answers:

• What does the data look like?
• Are there missing values?
• What is the distribution of values?
• Are there relationships between variables?
• Are there outliers?
• What patterns exist?

33.2 Descriptive Statistics

Descriptive statistics summarize the main information in data.

Example Dataset: [10, 20, 30, 40]

Statistic	Formula	Example	Python
Mean	Sum / Count	(10+20+30+40)/4 = 25	df.mean()
Median	Middle value	[10,20,30,40] → 25	df.median()
Mode	Most frequent	[10,20,20,30] → 20	df.mode()
Variance	Spread measure	Values far apart	df.var()
Std Dev	Square root of variance	Shows deviation	df.std()
Min	Smallest value	10	df.min()
Max	Largest value	40	df.max()
Range	Max – Min	30	df.max() - df.min()

Implementation in Python:

import pandas as pd

data = pd.Series([10, 20, 30, 40])
print("Mean:", data.mean())
print("Median:", data.median())
print("Std:", data.std())
print("Min:", data.min())
print("Max:", data.max())
print("Q1:", data.quantile(0.25))
print("Q3:", data.quantile(0.75))

33.3 Correlation Analysis

Correlation measures the relationship between two variables.

Type	Relationship	Example	Correlation Value
Positive	X increases → Y increases	Study hours and grades	+0.8 to +1.0
Negative	X increases → Y decreases	Hours spent and sleep	-0.8 to -1.0
No correlation	No relationship	Shoe size and IQ	0

Correlation Coefficients:

Value	Strength
1.00	Perfect positive
0.70-0.99	Strong positive
0.30-0.69	Moderate positive
0.10-0.29	Weak positive
0.00	No correlation
-0.10 to -0.29	Weak negative
-0.30 to -0.69	Moderate negative
-0.70 to -0.99	Strong negative
-1.00	Perfect negative

Python Implementation:

# Correlation matrix
df.corr()

# Visualize correlation
import seaborn as sns
sns.heatmap(df.corr(), annot=True, cmap="coolwarm")

33.4 Distribution Analysis

Distribution shows how data values are spread out.

Common Distributions:

Distribution	Shape	Example
Normal	Bell-shaped	Height, IQ scores
Uniform	Flat	Random numbers
Skewed Right	Long tail on right	Income, house prices
Skewed Left	Long tail on left	Exam scores (hard test)
Bimodal	Two peaks	Heights of men and women

Python Implementation:

import matplotlib.pyplot as plt

# Histogram
df["Age"].hist(bins=30)

# Box plot
df.boxplot(column="Age")

# KDE plot (smooth distribution)
df["Age"].plot.kde()

33.5 Pattern Discovery

Patterns are repeating behaviors in data.

Time Series Patterns:

Pattern	Description	Example
Trend	Long-term increase or decrease	Sales growing year over year
Seasonality	Regular pattern at fixed intervals	Holiday sales spikes
Cyclical	Irregular patterns over longer periods	Economic boom/bust cycles
Noise	Random variation	Daily fluctuations

Python Implementation:

# Time series plot
df.plot(x="Date", y="Sales")

# Rolling averages
df["Sales_SMA"] = df["Sales"].rolling(window=7).mean()

33.6 Data Profiling

Data Profiling is a health check of the dataset.

# Quick overview
df.info()

# Statistical summary
df.describe()

# Data types
df.dtypes

# Memory usage
df.memory_usage(deep=True)

# Missing values
df.isnull().sum()

# Duplicates
df.duplicated().sum()

33.7 Missing Data Patterns

# Count missing values
df.isnull().sum()

# Percentage of missing values
df.isnull().sum() / len(df) * 100

# Visualize missing data
import seaborn as sns
sns.heatmap(df.isnull(), cbar=False, yticklabels=False)

Missing Data Mechanisms:

Type	Description	Example
MCAR	Missing Completely At Random	Survey question skipped randomly
MAR	Missing At Random	Older patients more likely to skip age question
MNAR	Missing Not At Random	People with high income refuse to report it

34. Full EDA Project: Titanic

34.1 Load and Profile Data

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
import seaborn as sns

# Load data
df = pd.read_csv("https://raw.githubusercontent.com/datasciencedojo/datasets/master/titanic.csv")

print("Shape:", df.shape)
print("\nColumns:", df.columns.tolist())
print("\nMissing values:")
print(df.isnull().sum())

print("\nStatistical summary:")
print(df.describe())

Output:

Shape: (891, 12)

Columns: ['PassengerId', 'Survived', 'Pclass', 'Name', 'Sex', 'Age', 'SibSp', 'Parch', 'Ticket', 'Fare', 'Cabin', 'Embarked']

Missing values:
PassengerId      0
Survived         0
Pclass           0
Name             0
Sex              0
Age            177
SibSp            0
Parch            0
Ticket           0
Fare             0
Cabin          687
Embarked         2

34.2 Survival Count and Distribution

# Survival count
survival_counts = df["Survived"].value_counts()
print("Survival counts:")
print(survival_counts)

# Visualize
fig, axes = plt.subplots(1, 2, figsize=(12, 4))

# Bar chart
survival_counts.plot(kind="bar", ax=axes[0])
axes[0].set_title("Survival Count")
axes[0].set_xlabel("Survived (0=No, 1=Yes)")
axes[0].set_ylabel("Count")

# Pie chart
survival_counts.plot(kind="pie", ax=axes[1], autopct="%1.1f%%")
axes[1].set_title("Survival Percentage")

plt.tight_layout()
plt.show()

# Survival rate
print(f"\nOverall survival rate: {df['Survived'].mean():.1%}")

34.3 Age Distribution

# Age distribution
fig, axes = plt.subplots(1, 2, figsize=(12, 4))

# Histogram
df["Age"].dropna().hist(bins=30, ax=axes[0], edgecolor="black")
axes[0].set_title("Age Distribution")
axes[0].set_xlabel("Age")
axes[0].set_ylabel("Count")

# Box plot
df.boxplot(column="Age", ax=axes[1])
axes[1].set_title("Age Box Plot")

plt.tight_layout()
plt.show()

# Age statistics
print("Age Statistics:")
print(f"Mean: {df['Age'].mean():.1f}")
print(f"Median: {df['Age'].median():.1f}")
print(f"Min: {df['Age'].min():.1f}")
print(f"Max: {df['Age'].max():.1f}")

34.4 Survival by Gender

# Survival by gender
gender_survival = df.groupby("Sex")["Survived"].mean()
print("Survival rate by gender:")
print(gender_survival)

# Visualize
gender_survival.plot(kind="bar")
plt.title("Survival Rate by Gender")
plt.xlabel("Gender")
plt.ylabel("Survival Rate")
plt.ylim(0, 1)
plt.show()

print(f"Female survival rate: {df[df['Sex']=='female']['Survived'].mean():.1%}")
print(f"Male survival rate: {df[df['Sex']=='male']['Survived'].mean():.1%}")

Key Insight: Women had a much higher survival rate (74%) compared to men (19%).

34.5 Survival by Passenger Class

# Survival by class
class_survival = df.groupby("Pclass")["Survived"].mean()
print("Survival rate by class:")
print(class_survival)

# Visualize
class_survival.plot(kind="bar")
plt.title("Survival Rate by Passenger Class")
plt.xlabel("Passenger Class")
plt.ylabel("Survival Rate")
plt.ylim(0, 1)
plt.show()

print(f"1st Class survival rate: {df[df['Pclass']==1]['Survived'].mean():.1%}")
print(f"2nd Class survival rate: {df[df['Pclass']==2]['Survived'].mean():.1%}")
print(f"3rd Class survival rate: {df[df['Pclass']==3]['Survived'].mean():.1%}")

Key Insight: 1st class passengers had the highest survival rate (63%), 3rd class had the lowest (24%).

34.6 Fare Distribution

# Fare distribution
fig, axes = plt.subplots(1, 2, figsize=(12, 4))

# Histogram
df["Fare"].hist(bins=30, ax=axes[0], edgecolor="black")
axes[0].set_title("Fare Distribution")
axes[0].set_xlabel("Fare")
axes[0].set_ylabel("Count")

# Box plot
df.boxplot(column="Fare", ax=axes[1])
axes[1].set_title("Fare Box Plot")

plt.tight_layout()
plt.show()

print("Fare Statistics:")
print(f"Mean: {df['Fare'].mean():.2f}")
print(f"Median: {df['Fare'].median():.2f}")
print(f"Min: {df['Fare'].min():.2f}")
print(f"Max: {df['Fare'].max():.2f}")

34.7 Correlation Heatmap

# Correlation heatmap
plt.figure(figsize=(10, 8))
sns.heatmap(df.select_dtypes(include="number").corr(), annot=True, fmt=".2f", cmap="coolwarm")
plt.title("Correlation Heatmap")
plt.show()

Key Insights:

• Pclass and Survived: Negative correlation (-0.34) — higher class means higher survival
• Sex and Survived: Negative correlation (-0.54) — being male reduced survival
• Age and Survived: Weak negative correlation — younger passengers slightly more likely to survive

34.8 Key Insights

print("=" * 50)
print("TITANIC EDA KEY INSIGHTS")
print("=" * 50)

print(f"\n1. Overall survival rate: {df['Survived'].mean():.1%}")

print("\n2. Survival by Gender:")
print(f"   Women: {df[df['Sex']=='female']['Survived'].mean():.1%}")
print(f"   Men:   {df[df['Sex']=='male']['Survived'].mean():.1%}")

print("\n3. Survival by Class:")
for pclass in [1, 2, 3]:
    rate = df[df['Pclass']==pclass]['Survived'].mean()
    print(f"   Class {pclass}: {rate:.1%}")

print("\n4. Survival by Age Group:")
age_groups = ["0-12", "13-25", "26-40", "41-60", "60+"]
for age_group in age_groups:
    # Code to calculate survival by age group
    pass

print("\n5. Key Correlations:")
print(f"   Pclass vs Survived: {df['Pclass'].corr(df['Survived']):.2f}")
print(f"   Sex vs Survived: {df['Sex'].map({'male':0,'female':1}).corr(df['Survived']):.2f}")

DATA VISUALIZATION & BUSINESS ANALYTICS

35. Introduction to Data Visualization

35.1 What Is Data Visualization?

Data Visualization is the representation of data in graphical form to help humans understand patterns, trends, and insights.

Example: Data visualization is like translating a complex scientific paper into a picture book. The information is the same, but the picture book makes it accessible to everyone, not just experts.

Why Visualization Matters:

• Humans process images 60,000 times faster than text
• Visualization reveals patterns that numbers hide
• Makes data accessible to non-technical audiences
• Drives faster decision-making

35.2 Why Visualization Matters

The Human Brain and Visualization:

Aspect	Numbers	Visuals
Processing Speed	Slow	Fast
Pattern Recognition	Hard	Easy
Memory Retention	Low	High
Engagement	Low	High
Accessibility	Technical	Universal

Numbers Tell, Visuals Show:

• Numbers: “Sales increased by 15% in Q3”
• Visuals: A line chart showing the trend clearly

35.3 Data Storytelling

Data Storytelling combines data, visuals, and narrative to communicate insights effectively.

The Three Elements:

Element	Description	Example
Data	The facts and numbers	“Sales: $1.2M in Q3”
Visuals	Charts and graphs	Line chart showing growth
Narrative	The story that explains	“Marketing campaign drove Q3 growth”

Storytelling Structure:

Hook → Context → Insight → Action
  │        │         │         │
  ▼        ▼         ▼         ▼
  "Our    "Here's   "The     "We should
  sales    the       data     invest
  are      data"     shows"   more in
  falling"           "This    marketing"
                     is why"

36. Visualization Principles

36.1 Simplicity

Principle: Charts should be easy to understand at a glance.

Best Practice	Bad Practice
Use 3-5 data points	Use 20+ data points
Limit colors to 2-3	Use rainbow colors
Remove unnecessary elements	Add gridlines everywhere
Clear labels	Tiny, unreadable text

36.2 Accuracy

Principle: Represent data truthfully without distortion.

Best Practice	Bad Practice
Start y-axis at 0	Start y-axis at 50 to exaggerate differences
Use proper scales	Use inconsistent scales
Show full context	Cherry-pick data
Be transparent about limitations	Mislead with confusing visuals

36.3 Clarity

Principle: Viewer should understand within seconds.

Best Practice	Bad Practice
Clear title	No title
Axis labels	Missing axis labels
Legend	No legend
Annotations for key insights	No context

36.4 Consistency

Principle: Use uniform colors, scales, and styles.

Best Practice	Bad Practice
Same color for same data	Random colors
Consistent font sizes	Varying font sizes
Standard chart types	Unusual, confusing charts

36.5 Relevance

Principle: Show only what matters.

Best Practice	Bad Practice
Show decision-relevant data	Show every data point available
Focus on key insights	Include irrelevant details
Know your audience	Create generic charts

37. Python Visualization Libraries

37.1 Matplotlib – Foundational Plotting

Matplotlib is the foundational plotting library in Python.

import matplotlib.pyplot as plt

# Line chart
x = [1, 2, 3, 4, 5]
y = [10, 15, 12, 18, 20]

plt.plot(x, y, marker='o', linestyle='-', color='blue')
plt.title("Sales Trend")
plt.xlabel("Month")
plt.ylabel("Sales ($)")
plt.grid(True)
plt.show()

Common Matplotlib Charts:

Chart	Code	When to Use
Line Chart	plt.plot(x, y)	Trends over time
Bar Chart	plt.bar(x, y)	Comparing categories
Histogram	plt.hist(data)	Distribution of values
Scatter Plot	plt.scatter(x, y)	Relationship between two variables
Box Plot	plt.boxplot(data)	Outlier detection

37.2 Seaborn – Statistical Charts

Seaborn builds on Matplotlib with statistical charts.

import seaborn as sns

# Heatmap
sns.heatmap(df.corr(), annot=True, cmap="coolwarm")

# Box plot
sns.boxplot(x="Category", y="Value", data=df)

# Pair plot
sns.pairplot(df)

# Distribution plot
sns.histplot(df["Column"], kde=True)

# Violin plot
sns.violinplot(x="Category", y="Value", data=df)

37.3 Plotly – Interactive Visualizations

Plotly creates interactive visualizations.

import plotly.express as px

# Interactive scatter plot
fig = px.scatter(df, x="Age", y="Fare", color="Survived")
fig.show()

# Interactive line chart
fig = px.line(df, x="Date", y="Sales")
fig.show()

# Interactive bar chart
fig = px.bar(df, x="Category", y="Value")
fig.show()

# 3D scatter plot
fig = px.scatter_3d(df, x="Age", y="Fare", z="Survived", color="Pclass")
fig.show()

38. Business Intelligence (BI) Tools

38.1 Tableau

Tableau is the industry standard for enterprise dashboards.

Feature	Description
Drag-and-Drop	Easy to build complex visualizations
Data Sources	Connect to many data sources
Dashboards	Create interactive dashboards
Storytelling	Build data stories
Enterprise	Scales to large organizations

Key Capabilities:

• Connect to databases, spreadsheets, cloud data
• Create maps, charts, and dashboards
• Publish to Tableau Server or Tableau Public
• No coding required

38.2 Power BI

Power BI is Microsoft’s BI platform.

Feature	Description
Integration	Works with Excel, Azure, Office 365
DAX	Advanced calculation language
Power Query	Data transformation
Visuals	Custom visuals marketplace
Natural Language	Q&A feature for natural language queries

38.3 Looker

Looker is Google Cloud’s BI platform.

Feature	Description
LookML	Semantic modeling language
Integration	Deep integration with BigQuery
Embedded	Embed analytics in applications
Real-time	Real-time data access

38.4 Metabase

Metabase is open-source BI for small teams.

Feature	Description
Open Source	Free to use
Simple Setup	Easy to install and configure
Question Builder	No SQL required
Embedding	Embed charts in other apps

39. Business Analytics Techniques

39.1 KPI Analysis

Key Performance Indicators (KPIs) measure business performance.

KPI	Formula	Target
Revenue	Total sales	↑ 10%
Conversion Rate	(Conversions / Visitors) × 100	3-5%
Retention Rate	(Returning Users / Total Users) × 100	> 80%
Customer Acquisition Cost	Marketing Spend / New Customers	< $50
Average Order Value	Revenue / Orders	↑ 15%
Churn Rate	Lost Customers / Total Customers	< 5%

# KPI calculation example
def calculate_kpis(df):
    kpis = {}

    total_revenue = df["Revenue"].sum()
    total_orders = df["Orders"].sum()
    total_customers = df["Customers"].nunique()
    new_customers = df[df["Is_New"] == True]["Customers"].nunique()

    kpis["Total Revenue"] = total_revenue
    kpis["Total Orders"] = total_orders
    kpis["Avg Order Value"] = total_revenue / total_orders
    kpis["Customer Count"] = total_customers
    kpis["New Customers"] = new_customers

    return kpis

39.2 Cohort Analysis

Cohort Analysis groups users by sign-up date and tracks them over time.

def cohort_analysis(df, date_col, user_col, metric_col):
    # Group users by sign-up month
    df["Cohort"] = df[date_col].dt.to_period("M")

    # Track behavior over time
    cohort_data = df.groupby(["Cohort", "Month"])[metric_col].mean().unstack()

    return cohort_data

Why Cohort Analysis Matters:

• Shows retention patterns
• Identifies which cohorts perform best
• Helps understand customer lifetime value
• Guides marketing and product decisions

39.3 Funnel Analysis

Funnel Analysis shows steps users take before completing a goal.

Visitors → Sign-ups → Activations → Purchases
  1000       200          50           10
             20%          25%          20%

def funnel_analysis(steps, counts):
    import matplotlib.pyplot as plt

    # Calculate conversion rates
    conversion_rates = [counts[0]]
    for i in range(1, len(counts)):
        conversion_rates.append(counts[i] / counts[i-1])

    # Create funnel chart
    plt.figure(figsize=(10, 6))
    plt.barh(steps, counts)
    for i, (step, count) in enumerate(zip(steps, counts)):
        plt.text(count, i, f" {count} ({conversion_rates[i]:.1%})")

    plt.title("Funnel Analysis")
    plt.xlabel("Users")
    plt.show()

39.4 Customer Segmentation (RFM Score)

RFM Score segments customers based on:

Component	Description	Scoring
Recency	How recently they purchased	5 = recently, 1 = long ago
Frequency	How often they purchase	5 = frequently, 1 = rarely
Monetary	How much they spend	5 = high spend, 1 = low spend

def calculate_rfm(df):
    # Calculate RFM scores
    recency = df.groupby("Customer")["Date"].max()
    frequency = df.groupby("Customer")["Order_ID"].count()
    monetary = df.groupby("Customer")["Amount"].sum()

    # Score each component (1-5)
    recency_score = pd.qcut(recency.rank(method="first"), 5, labels=[5, 4, 3, 2, 1])
    frequency_score = pd.qcut(frequency.rank(method="first"), 5, labels=[1, 2, 3, 4, 5])
    monetary_score = pd.qcut(monetary.rank(method="first"), 5, labels=[1, 2, 3, 4, 5])

    # Combine into RFM score
    rfm = pd.DataFrame({
        "Recency": recency_score,
        "Frequency": frequency_score,
        "Monetary": monetary_score
    })
    rfm["RFM_Score"] = rfm["Recency"].astype(str) + rfm["Frequency"].astype(str) + rfm["Monetary"].astype(str)

    return rfm

Customer Segments:

Segment	RFM Score	Description	Action
Champions	555	High R, F, M	Reward, VIP treatment
Loyal Customers	545-555	High F, M	Engage, cross-sell
Potential Loyalists	355-455	Good F, M	Nurture, build loyalty
At Risk	155-255	Low R	Re-engage, win back
Lost Customers	111-144	Low R, F, M	Re-acquire

39.5 A/B Testing

A/B Testing compares two versions to determine which performs better.

from scipy import stats
import numpy as np

def ab_test(control, test):
    """
    Perform A/B test analysis.

    Parameters:
    control: array of control group results
    test: array of test group results

    Returns:
    t_stat: t-statistic
    p_value: p-value
    """

    # Perform t-test
    t_stat, p_value = stats.ttest_ind(control, test)

    # Calculate practical significance
    control_mean = np.mean(control)
    test_mean = np.mean(test)
    lift = (test_mean - control_mean) / control_mean * 100

    results = {
        "Control Mean": control_mean,
        "Test Mean": test_mean,
        "Lift": f"{lift:.2f}%",
        "T-statistic": t_stat,
        "P-value": p_value
    }

    # Determine significance
    if p_value < 0.05:
        results["Significant"] = "Yes"
        results["Winner"] = "Test" if lift > 0 else "Control"
    else:
        results["Significant"] = "No"
        results["Winner"] = "None (statistically insignificant)"

    return results

# Example usage
control_data = [10, 12, 11, 13, 10, 14]  # Version A (control)
test_data = [15, 18, 16, 17, 19, 18]     # Version B (test)

results = ab_test(control_data, test_data)
for key, value in results.items():
    print(f"{key}: {value}")

Interpretation:

P-Value	Meaning	Action
< 0.01	Very significant	Strong confidence
0.01 – 0.05	Significant	Good confidence
0.05 – 0.10	Marginally significant	Consider more data
> 0.10	Not significant	Need more data or no difference

39.6 Confidence Intervals

Confidence Intervals estimate the reliability of a result.

import numpy as np
from scipy import stats

def confidence_interval(data, confidence=0.95):
    """
    Calculate confidence interval for a dataset.

    Parameters:
    data: array of data points
    confidence: confidence level (0.95 for 95%)

    Returns:
    (lower, upper): confidence interval bounds
    """
    n = len(data)
    mean = np.mean(data)
    std_err = stats.sem(data)
    t_critical = stats.t.ppf((1 + confidence) / 2, n - 1)

    margin = std_err * t_critical
    return mean - margin, mean + margin

# Example usage
sales = [100, 110, 95, 120, 105, 115, 98, 102, 108, 112]
ci_low, ci_high = confidence_interval(sales)

print(f"95% Confidence Interval: ${ci_low:.2f} to ${ci_high:.2f}")
print(f"Mean: ${np.mean(sales):.2f}")

40. Dashboard Design

40.1 What Is a Dashboard?

A dashboard is a visual interface displaying key metrics and insights for monitoring and decision-making.

Real-World Analogy: A dashboard is like a car’s dashboard. You have speed (KPIs), fuel (resources), engine temperature (system health), and warnings (alerts) all in one place.

Types of Dashboards:

Type	Purpose	Examples
Strategic	High-level metrics for executives	Annual revenue, market share
Analytical	Deep dive for analysts	Trends, patterns, correlations
Operational	Real-time monitoring	Alerts, system health, daily metrics

40.2 Dashboard Metrics

Example Executive Dashboard:

Metric	Value	Trend	Status
Revenue	$500,000	↑ 12%	✅ On Target
Orders	1,200	↑ 8%	✅ On Target
Customers	900	↑ 5%	✅ On Target
Conversion Rate	3.2%	↓ 0.5%	⚠️ Warning
Average Order Value	$125	↑ 3%	✅ On Target
Retention Rate	78%	↓ 2%	⚠️ Warning
Customer Acquisition Cost	$45	↑ 10%	⚠️ Warning

40.3 Dashboard Project: Build an Executive Dashboard

import pandas as pd
import matplotlib.pyplot as plt
import numpy as np

# Generate sample data
np.random.seed(42)
df = pd.DataFrame({
    "Date": pd.date_range('2024-01-01', periods=100),
    "Revenue": np.random.normal(10000, 2000, 100),
    "Orders": np.random.poisson(100, 100),
    "Customers": np.random.poisson(50, 100),
    "Marketing_Spend": np.random.normal(5000, 1000, 100),
    "Retention_Rate": np.random.normal(0.75, 0.05, 100)
})

# Create dashboard layout
fig = plt.figure(figsize=(16, 10))

# 1. Revenue Trend (Top Left)
ax1 = plt.subplot(2, 3, 1)
df.plot(x="Date", y="Revenue", ax=ax1, color="blue")
ax1.set_title("Revenue Trend")
ax1.set_xlabel("")
ax1.set_ylabel("Revenue ($)")

# 2. Orders Trend (Top Middle)
ax2 = plt.subplot(2, 3, 2)
df.plot(x="Date", y="Orders", ax=ax2, color="green")
ax2.set_title("Orders Trend")
ax2.set_xlabel("")
ax2.set_ylabel("Orders")

# 3. Revenue Distribution (Top Right)
ax3 = plt.subplot(2, 3, 3)
df["Revenue"].hist(bins=20, ax=ax3, color="blue", edgecolor="black")
ax3.set_title("Revenue Distribution")
ax3.set_xlabel("Revenue ($)")
ax3.set_ylabel("Frequency")

# 4. Retention Rate (Bottom Left)
ax4 = plt.subplot(2, 3, 4)
df.plot(x="Date", y="Retention_Rate", ax=ax4, color="orange")
ax4.set_title("Retention Rate")
ax4.set_xlabel("")
ax4.set_ylabel("Retention Rate")
ax4.axhline(y=0.75, color='red', linestyle='--', label='Target')
ax4.legend()

# 5. Marketing Spend vs Revenue (Bottom Middle)
ax5 = plt.subplot(2, 3, 5)
ax5.scatter(df["Marketing_Spend"], df["Revenue"], alpha=0.5)
ax5.set_title("Marketing Spend vs Revenue")
ax5.set_xlabel("Marketing Spend ($)")
ax5.set_ylabel("Revenue ($)")

# 6. KPI Summary (Bottom Right)
ax6 = plt.subplot(2, 3, 6)
ax6.axis('off')
ax6.set_title("Key Metrics Summary")

kpis = {
    "Total Revenue": f"${df['Revenue'].sum():,.0f}",
    "Avg Order Value": f"${df['Revenue'].mean():.2f}",
    "Total Orders": f"{df['Orders'].sum():,}",
    "Total Customers": f"{df['Customers'].sum():,}",
    "Avg Retention Rate": f"{df['Retention_Rate'].mean():.1%}"
}

kpi_text = ""
for key, value in kpis.items():
    kpi_text += f"{key}: {value}\n\n"

ax6.text(0.1, 0.5, kpi_text, fontsize=14, verticalalignment='center')

plt.tight_layout()
plt.show()

COMPLETE PROJECTS

Project 1: Analyze a Public Dataset

Step 1: Install Python and Libraries

pip install pandas matplotlib seaborn

Step 2: Load Dataset

import pandas as pd
import matplotlib.pyplot as plt
import seaborn as sns

# Create a sample dataset
data = {
    'Customer': ['Alice', 'Bob', 'Charlie', 'David', 'Eva', 'Frank', 'Grace', 'Henry'],
    'Age': [25, 32, 47, 19, 38, 29, 55, 41],
    'Purchase_Amount': [120.50, 450.00, 80.25, 310.00, 520.75, 200.00, 150.00, 380.00],
    'Category': ['Electronics', 'Clothing', 'Books', 'Electronics', 'Clothing', 'Books', 'Electronics', 'Clothing']
}

# Load into DataFrame
df = pd.DataFrame(data)
print("Dataset loaded!")
print(df.head())

Step 3: Explore Data

print("\n--- Data Information ---")
print(df.info())

print("\n--- Statistical Summary ---")
print(df.describe())

print("\n--- Missing Values ---")
print(df.isnull().sum())

Step 4: Analyze Data

# Average purchase by category
category_avg = df.groupby('Category')['Purchase_Amount'].mean()
print("\nAverage Purchase by Category:")
print(category_avg)

# Customer age groups
df['Age_Group'] = pd.cut(df['Age'], bins=[0, 25, 35, 50, 100],
                          labels=['Young', 'Young Adult', 'Adult', 'Senior'])

# Average spend by age group
age_spending = df.groupby('Age_Group')['Purchase_Amount'].mean()
print("\nAverage Spend by Age Group:")
print(age_spending)

# Top customers
top_customers = df.nlargest(3, 'Purchase_Amount')
print("\nTop 3 Customers by Spending:")
print(top_customers[['Customer', 'Purchase_Amount']])

Step 5: Visualize Data

# Create visualizations
plt.figure(figsize=(12, 5))

# 1. Bar chart - Spending by Category
plt.subplot(1, 2, 1)
category_avg.plot(kind='bar', color=['blue', 'green', 'red'])
plt.title('Average Spending by Category')
plt.xlabel('Category')
plt.ylabel('Average Amount ($)')

# 2. Bar chart - Spending by Age Group
plt.subplot(1, 2, 2)
age_spending.plot(kind='bar', color=['orange', 'purple', 'brown', 'pink'])
plt.title('Average Spending by Age Group')
plt.xlabel('Age Group')
plt.ylabel('Average Amount ($)')

plt.tight_layout()
plt.show()

# Scatter plot - Age vs Purchase Amount
plt.figure(figsize=(8, 6))
plt.scatter(df['Age'], df['Purchase_Amount'], alpha=0.7)
plt.title('Age vs Purchase Amount')
plt.xlabel('Age')
plt.ylabel('Purchase Amount ($)')
plt.grid(True)
plt.show()

# Distribution of Purchase Amounts
plt.figure(figsize=(8, 6))
df['Purchase_Amount'].hist(bins=10, edgecolor='black')
plt.title('Distribution of Purchase Amounts')
plt.xlabel('Purchase Amount ($)')
plt.ylabel('Frequency')
plt.show()

---

Project 2: Write Data Insights Report

Sample Report Format:

# Customer Spending Analysis Report

## 1. Executive Summary
- Total Revenue: $2,211.50
- Average Order Value: $276.44
- Top Categories: Electronics ($296.83 avg), Clothing ($316.25 avg)
- Key Insight: Customers aged 35-50 spend the most ($397.50 avg)

## 2. Data Overview
- Dataset contains 8 customers
- 3 categories: Electronics, Clothing, Books
- Age range: 19-55 years

## 3. Key Findings

### 3.1 Spending by Category
- Electronics: $296.83 average
- Clothing: $316.25 average  
- Books: $140.13 average

### 3.2 Spending by Age Group
- Young (0-25): $215.00 average
- Young Adult (26-35): $310.00 average
- Adult (36-50): $331.67 average
- Senior (50+): $150.00 average

### 3.3 Top Customers
1. Eva: $520.75 (Clothing)
2. Bob: $450.00 (Clothing)
3. Henry: $380.00 (Clothing)

## 4. Recommendations
1. Focus marketing on Clothing category (highest average spend)
2. Target customers aged 36-50 (highest spenders)
3. Consider promotions for Books category (lowest spend)
4. Implement loyalty program for top customers

Project 3: CSV Data Cleaner Tool

import pandas as pd
import sys

def clean_data(filename):
    """
    Clean a CSV file by removing duplicates and handling missing values.
    """
    print(f"Processing: {filename}")

    # Step 1: Load CSV
    df = pd.read_csv(filename)
    print(f"Original: {len(df)} rows, {len(df.columns)} columns")

    # Step 2: Remove duplicates
    before = len(df)
    df = df.drop_duplicates()
    print(f"Removed {before - len(df)} duplicate rows")

    # Step 3: Remove empty rows (where ALL values are missing)
    before = len(df)
    df = df.dropna(how='all')
    print(f"Removed {before - len(df)} completely empty rows")

    # Step 4: Fill missing numeric values with median
    numeric_cols = df.select_dtypes(include=['float64', 'int64']).columns
    for col in numeric_cols:
        if df[col].isnull().any():
            df[col] = df[col].fillna(df[col].median())
            print(f"Filled missing values in '{col}' with median")

    # Step 5: Fill missing categorical values with mode
    categorical_cols = df.select_dtypes(include=['object']).columns
    for col in categorical_cols:
        if df[col].isnull().any():
            df[col] = df[col].fillna(df[col].mode()[0])
            print(f"Filled missing values in '{col}' with mode")

    # Step 6: Remove columns that are mostly missing (>50%)
    threshold = 0.5
    cols_to_drop = [col for col in df.columns if df[col].isnull().mean() > threshold]
    if cols_to_drop:
        df = df.drop(columns=cols_to_drop)
        print(f"Dropped columns (>50% missing): {cols_to_drop}")

    # Step 7: Save cleaned data
    output = f"clean_{filename}"
    df.to_csv(output, index=False)
    print(f"Cleaned data saved to: {output}")
    print(f"Cleaned: {len(df)} rows, {len(df.columns)} columns")

    return df

if __name__ == "__main__":
    if len(sys.argv) < 2:
        print("Usage: python clean.py <filename>")
    else:
        clean_data(sys.argv[1])

Project 4: SQL Analytics Queries

Example Table: Customers (id, name, age, city)

-- Query 1: Get all customers
SELECT * FROM customers;

-- Query 2: Customers older than 30
SELECT name, age FROM customers WHERE age > 30;

-- Query 3: Average age of customers
SELECT AVG(age) FROM customers;

-- Query 4: Count customers by city
SELECT city, COUNT(*) as customer_count
FROM customers
GROUP BY city
ORDER BY customer_count DESC;

-- Query 5: Top 5 customers by total orders
SELECT customers.name, COUNT(orders.id) as order_count
FROM customers
JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.id
ORDER BY order_count DESC
LIMIT 5;

-- Query 6: Customers with no orders
SELECT name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- Query 7: Monthly sales trend
SELECT
    DATE_TRUNC('month', order_date) as month,
    COUNT(*) as order_count,
    SUM(total) as revenue
FROM orders
GROUP BY month
ORDER BY month;

-- Query 8: Customer lifetime value (LTV)
SELECT
    customers.id,
    customers.name,
    COUNT(orders.id) as total_orders,
    SUM(orders.total) as lifetime_value,
    AVG(orders.total) as avg_order_value
FROM customers
JOIN orders ON customers.id = orders.customer_id
GROUP BY customers.id
ORDER BY lifetime_value DESC;

Project 5: Complete End-to-End Pipeline

# Complete ETL Pipeline
import pandas as pd
import requests
import sqlite3

class DataPipeline:
    def __init__(self):
        self.raw_data = None
        self.cleaned_data = None

    def extract(self):
        """Extract data from API and files"""
        print("1. EXTRACTING DATA...")

        # From API
        url = "https://api.example.com/data"
        response = requests.get(url)
        self.raw_data = response.json()

        print(f"   Extracted {len(self.raw_data)} records")
        return self.raw_data

    def transform(self):
        """Clean and transform data"""
        print("2. TRANSFORMING DATA...")

        # Convert to DataFrame
        df = pd.DataFrame(self.raw_data)

        # Clean missing values
        df = df.fillna(df.median())

        # Remove duplicates
        df = df.drop_duplicates()

        # Add calculated columns
        df["Total"] = df["Quantity"] * df["Price"]

        # Filter invalid data
        df = df[df["Price"] > 0]
        df = df[df["Quantity"] > 0]

        self.cleaned_data = df
        print(f"   Transformed to {len(df)} rows")
        return self.cleaned_data

    def load(self):
        """Load data to database"""
        print("3. LOADING DATA...")

        # Connect to database
        conn = sqlite3.connect("data_warehouse.db")

        # Save to database
        self.cleaned_data.to_sql("sales_data", conn, if_exists="replace", index=False)

        # Create summary table
        summary = self.cleaned_data.groupby("Product")["Total"].sum().reset_index()
        summary.to_sql("product_summary", conn, if_exists="replace", index=False)

        conn.close()
        print("   Data loaded successfully!")

    def run(self):
        """Run the complete pipeline"""
        print("=" * 50)
        print("RUNNING DATA PIPELINE")
        print("=" * 50)

        self.extract()
        self.transform()
        self.load()

        print("\nPipeline completed successfully!")
        print(f"Total records: {len(self.cleaned_data)}")
        print(f"Products: {self.cleaned_data['Product'].nunique()}")

# Run the pipeline
pipeline = DataPipeline()
pipeline.run()