📂 Data Loading with Pandas

📚 What You'll Learn

By the end of this lesson, you will be able to:

Load CSV files with pd.read_csv and control parsing through key parameters (sep, encoding, parse_dates, index_col)
Read Excel workbooks, including selecting a single sheet or loading every sheet at once
Parse JSON data and flatten nested structures with pd.json_normalize
Query relational databases into DataFrames using SQLAlchemy engines and pd.read_sql
Handle files too large for memory using chunking and explicit dtype optimization
Inspect data right after loading and sidestep common pitfalls with encoding, dates, and missing values

⏱️ Estimated Time: 45–60 minutes

🎯 Project: Build a "morning data pipeline" that pulls a product catalog from Excel, daily orders from CSV, customers from a SQL database, and shipping status from a JSON API, then combines them into one dataset.

Master the art of importing data from various sources into pandas DataFrames

🌟 Your Gateway to Data

Think of pandas as a universal translator for data formats. Just like a skilled interpreter who can understand multiple languages and convert between them seamlessly, pandas can read data from virtually any source - CSV files, Excel spreadsheets, JSON documents, SQL databases, and more!

In the real world, data rarely comes in a single, perfect format. You might receive sales data in Excel, customer feedback in CSV, API responses in JSON, and transaction records in a SQL database. Pandas is your Swiss Army knife for bringing all this data together.

🗂️ Data Loading Pipeline

graph LR A[Data Sources] --> B{Pandas Read Functions} B --> C[DataFrame] D[CSV Files] --> E[pd.read_csv] F[Excel Files] --> G[pd.read_excel] H[JSON Files] --> I[pd.read_json] J[SQL Database] --> K[pd.read_sql] L[HTML Tables] --> M[pd.read_html] N[Clipboard] --> O[pd.read_clipboard] E --> C G --> C I --> C K --> C M --> C O --> C style A fill:#f9f,stroke:#333,stroke-width:2px style C fill:#9f9,stroke:#333,stroke-width:2px style B fill:#99f,stroke:#333,stroke-width:2px

📄 CSV Files

The bread and butter of data exchange - simple, universal, and human-readable

Real-world example: Sales reports, sensor data logs, export from spreadsheets

# Basic CSV loading
df = pd.read_csv('sales_data.csv')

# With custom settings
df = pd.read_csv('data.csv',
    sep=';',           # European CSV with semicolon
    encoding='utf-8',  # Handle special characters
    parse_dates=['date_column'],  # Auto-parse dates
    index_col='id'     # Use 'id' column as index
)

📊 Excel Files

Corporate data's favorite format - multiple sheets, formatting, and formulas

Real-world example: Financial reports, inventory sheets, budget planning

# Read specific sheet
df = pd.read_excel('report.xlsx', sheet_name='Q1_Sales')

# Read multiple sheets
all_sheets = pd.read_excel('report.xlsx', sheet_name=None)
# Returns dictionary: {'Sheet1': df1, 'Sheet2': df2, ...}

# Skip rows and use specific columns
df = pd.read_excel('data.xlsx',
    skiprows=2,         # Skip header rows
    usecols='A:E',      # Only columns A to E
    na_values=['N/A', 'Missing']  # Custom NA values
)

🔗 JSON Files

The language of web APIs - nested, hierarchical, and flexible

Real-world example: API responses, NoSQL exports, configuration files

# Simple JSON
df = pd.read_json('api_response.json')

# Nested JSON with normalization
import json
with open('nested_data.json') as f:
    data = json.load(f)
df = pd.json_normalize(data, 
    record_path=['results'],  # Path to records
    meta=['timestamp', 'version']  # Include metadata
)

# From API endpoint
df = pd.read_json('https://api.example.com/data')

🗄️ SQL Databases

Enterprise data warehouses - structured, relational, and massive

Real-world example: Customer databases, transaction systems, data warehouses

# Using SQLAlchemy
from sqlalchemy import create_engine

# Connect to database
engine = create_engine('sqlite:///database.db')
# Or: 'postgresql://user:pass@host/db'
# Or: 'mysql://user:pass@host/db'

# Read entire table
df = pd.read_sql_table('customers', engine)

# Custom SQL query
query = """
    SELECT customer_id, order_date, total
    FROM orders
    WHERE order_date >= '2024-01-01'
    ORDER BY total DESC
"""
df = pd.read_sql_query(query, engine)

🎮 Interactive Data Loading Simulator

Watch how different file formats are loaded and processed:

🔍 Advanced Loading Techniques

💡 Memory Optimization

When dealing with large files, use these techniques to manage memory:

# Read in chunks
chunk_size = 10000
for chunk in pd.read_csv('huge_file.csv', chunksize=chunk_size):
    # Process each chunk
    processed = chunk.groupby('category').sum()
    # Save or aggregate results

# Specify data types to reduce memory
dtypes = {
    'product_id': 'category',  # Categorical for repeated values
    'quantity': 'int32',        # Smaller integer type
    'price': 'float32'          # Single precision float
}
df = pd.read_csv('data.csv', dtype=dtypes)

# Only load specific columns
df = pd.read_csv('data.csv', usecols=['col1', 'col2', 'col3'])

⚠️ Common Pitfalls

Encoding Issues: Use encoding='utf-8' or 'latin-1' for special characters
Date Parsing: Specify date columns explicitly with parse_dates
Missing Values: Define custom NA values with na_values
Large Files: Use chunking or Dask for files that don't fit in memory

🌍 Real-World Scenario: E-commerce Data Pipeline

Imagine you're a data analyst at an e-commerce company. You need to combine data from multiple sources:

# Morning routine: Load yesterday's data from various sources

import pandas as pd
from datetime import datetime, timedelta
from sqlalchemy import create_engine

# 1. Load product catalog from Excel (updated weekly)
products = pd.read_excel('product_catalog.xlsx', 
                         sheet_name='Active_Products')

# 2. Load customer orders from CSV (daily export from order system)
yesterday = (datetime.now() - timedelta(1)).strftime('%Y%m%d')
orders = pd.read_csv(f'orders_{yesterday}.csv',
                     parse_dates=['order_timestamp'],
                     dtype={'customer_id': str})  # Keep as string to preserve leading zeros

# 3. Load customer data from SQL database
engine = create_engine('postgresql://analyst:pass@datawarehouse/ecommerce')
customers = pd.read_sql_query("""
    SELECT customer_id, segment, lifetime_value, registration_date
    FROM customers
    WHERE status = 'active'
""", engine)

# 4. Load shipping data from JSON API response
shipping = pd.read_json('https://api.shipping.com/tracking/bulk')

# 5. Combine all data sources
# Merge orders with products
order_details = orders.merge(products, on='product_id', how='left')

# Add customer information
full_data = order_details.merge(customers, on='customer_id', how='left')

# Add shipping status
final_dataset = full_data.merge(shipping, on='order_id', how='left')

print(f"Combined dataset: {final_dataset.shape[0]} rows, {final_dataset.shape[1]} columns")
print(f"Data sources integrated successfully!")

📚 Quick Reference Guide

graph TD A[Choose Read Function] --> B{File Type?} B -->|Text/CSV| C[pd.read_csv] B -->|Excel| D[pd.read_excel] B -->|JSON| E[pd.read_json] B -->|SQL| F[pd.read_sql] B -->|HTML| G[pd.read_html] C --> H[Common Parameters] D --> H E --> H F --> H G --> H H --> I[sep/delimiter] H --> J[header/names] H --> K[index_col] H --> L[usecols] H --> M[dtype] H --> N[parse_dates] H --> O[na_values] H --> P[encoding] style A fill:#f9f,stroke:#333,stroke-width:2px style B fill:#99f,stroke:#333,stroke-width:2px style H fill:#9f9,stroke:#333,stroke-width:2px

🚀 Pro Tips

Always inspect your data after loading: Use df.head(), df.info(), and df.describe()
Set index wisely: Choose a unique identifier as index for faster operations
Handle dates early: Parse dates during loading, not after
Use compression: Pandas can read compressed files directly (.gz, .zip, .bz2)
Profile your loading: Use %%time in Jupyter to measure loading performance

📓 Learning Journal

Keep a learning journal — digital or physical. After this lesson, take a few minutes to write down:

Key concepts you learned
Techniques that clicked for you
Questions or confusion points to revisit
Ideas you want to try
Your progress and feelings about learning this

✍️ This lesson's prompt: Data rarely arrives in one tidy format. Which source type (CSV, Excel, JSON, or SQL) do you expect to work with most, and what one parameter or option from this lesson do you most want to remember when you load it?

📝 Lesson Summary

🎓 Key Takeaways

Pandas offers a dedicated read_* function for each format — read_csv, read_excel, read_json, read_sql, read_html — that all return a DataFrame.
The right parameters at load time (dates, dtypes, index, encoding) save you cleaning work later.
Large files don't require huge memory — read them in chunks or downcast dtypes as you go.
Always inspect with head(), info(), and describe() immediately after loading.

🎉 What You've Accomplished

You can now pull data into pandas from CSV, Excel, JSON, and SQL sources, tune how it's parsed, and combine several sources into a single working dataset — the essential first step of every analysis.

❓ Common Questions at This Stage

My CSV shows garbled characters — what went wrong?

That's almost always an encoding mismatch. Try encoding='utf-8', and if that fails, encoding='latin-1'. Files exported from Excel on Windows sometimes need encoding='cp1252'.

Why do my leading zeros disappear from IDs like "00123"?

Pandas infers those columns as integers and drops the zeros. Force them to stay as text with dtype={'id_column': str} when you read the file.

The file is too big to fit in memory. What do I do?

Read it in pieces with chunksize and aggregate each chunk, load only the columns you need with usecols, or downcast numeric columns to smaller dtypes. For truly massive data, tools like Dask extend the pandas API.

🔭 Looking Ahead

Loading is only step one — real datasets arrive messy. Next you'll learn to detect and fix missing values, duplicates, wrong types, and outliers so your data is ready for analysis.

✅ Before the Next Lesson

Load the same dataset from two different formats (e.g. export a CSV as Excel) and confirm you get an identical DataFrame.
Practice reading one file with parse_dates and a custom dtype map, then check the result with df.info().
Write your Learning Journal entry for this lesson

🌟 Encouragement for the Journey

Every great analysis starts with getting the data in the door. You now hold the keys to almost any data source you'll meet — that's a genuinely powerful skill. Onward!