PadhaikoAll subjects

Data Sources and APIs

Advanced Python Programming for Data Science (ENCT 325)

Open in the Padhaiko app

2.1 Reading and writing structured/unstructured data (CSV, JSON, Excel, text)

Data science workflows ingest data from heterogeneous sources: plain logs, delimited flat files, nested API payloads, and multi-sheet spreadsheets. Choosing the right parser, encoding (utf-8), and context manager (with open(...)) prevents resource leaks and character corruption.

Structured vs. Semi-Structured vs. Unstructured Data
DimensionStructured DataSemi-Structured DataUnstructured Data
Schema ModelRigid tabular schema (fixed columns and types)Self-describing tags/keys; schema can vary per recordNo predefined schema or field boundaries
ExamplesCSV tables, SQL tables, Excel sheetsJSON documents, XML payloads, YAML configsRaw text logs, emails, PDFs, images, audio
Parsing Strategycsv module, pandas.read_csv(), read_excel()json module, xml.etree.ElementTreeLine-by-line text scanning, regex, NLP tokenizers

Plain Text Files

Always open text files using a with context manager and explicit encoding="utf-8" so file descriptors close automatically even when exceptions occur:

Python
from pathlib import Path

log_path = Path("events.log")
log_path.write_text("2026-11-18 INFO pipeline_started\n", encoding="utf-8")

with log_path.open("r", encoding="utf-8") as fh:
    for line in fh:
        parts = line.strip().split(maxsplit=2)

Delimited Files (CSV)

Python's built-in csv module handles quoting, embedded commas, and header mapping via DictReader and DictWriter (always pass newline="" on Windows/cross-platform writes to prevent blank rows):

Python
import csv

rows = [{"id": 1, "city": "Kathmandu", "temp": 22.5},
        {"id": 2, "city": "Pokhara", "temp": 24.0}]

with open("weather.csv", "w", newline="", encoding="utf-8") as f:
    writer = csv.DictWriter(f, fieldnames=["id", "city", "temp"])
    writer.writeheader()
    writer.writerows(rows)

with open("weather.csv", "r", newline="", encoding="utf-8") as f:
    records = list(csv.DictReader(f))

For analytical work, pandas.read_csv("weather.csv", usecols=["city", "temp"], dtype={"city": "string"}) parses numeric columns and missing tokens (NA, NaN) directly into a DataFrame.

JSON (JavaScript Object Notation)

Python json Module Functions
FunctionInput / OutputUse Case
json.load(fp)File-like object → Python dict/listReading a .json file from disk
json.loads(s)JSON str → Python dict/listParsing an HTTP API response body
json.dump(obj, fp, indent=2)Python object → .json fileSaving structured configuration or results
json.dumps(obj)Python object → JSON strSerializing a payload for an API POST request

For line-delimited JSON logs (JSONL / NDJSON), iterate line by line and call json.loads(line) rather than loading one huge JSON array, or use pandas.read_json("events.jsonl", lines=True).

Excel Workbooks (.xlsx)

Excel files support multiple worksheets, formulas, and formatting. In Python:


2.2 Database access with relational database and non-relational database

Production datasets rarely live only in flat files; they reside in relational (SQL) and non-relational (NoSQL) databases for concurrent access, indexing, and persistence.

AspectRelational Database (RDBMS)Non-Relational Database (NoSQL)
Data ModelNormalized tables with rows, columns, and foreign keysDocument (JSON/BSON), Key-Value, Wide-Column, or Graph
SchemaFixed schema enforced upfront (schema-on-write)Flexible/dynamic fields per document (schema-on-read)
Query LanguageDeclarative SQL (SELECT, JOIN, GROUP BY)Vendor APIs / JSON filter queries (collection.find())
Scaling & ConsistencyVertical scaling, strong ACID transactionsHorizontal sharding, eventual or tunable consistency

Relational Database Access (sqlite3 and Parameterized Queries)

Python ships with sqlite3 (implementing PEP 249 — DB-API 2.0), while libraries such as psycopg (PostgreSQL) and SQLAlchemy follow the same connection/cursor workflow:

  1. Open a connection (conn = sqlite3.connect("analytics.db")).
  2. Obtain a cursor (cur = conn.cursor()) or use pandas.read_sql_query(...).
  3. Execute parameterized queries using placeholders (? in sqlite3, %s in psycopg)—never string interpolation (f"..."), which opens SQL injection vulnerabilities.
  4. Commit write transactions (conn.commit()) or use with conn: for automatic commit/rollback, then close the connection.
Python
import sqlite3

with sqlite3.connect("students.db") as conn:
    cur = conn.cursor()
    cur.execute(
        "CREATE TABLE IF NOT EXISTS scores (id INTEGER PRIMARY KEY, name TEXT, gpa REAL)"
    )
    cur.execute("INSERT INTO scores (id, name, gpa) VALUES (?, ?, ?)", (1, "Aarav", 3.85))
    rows = cur.execute("SELECT name, gpa FROM scores WHERE gpa >= ?", (3.5,)).fetchall()

For larger applications, SQLAlchemy provides an engine connection pool (create_engine("postgresql+psycopg://...")) and seamless integration with pandas.read_sql(query, engine).

Non-Relational Database Access (Document & Key-Value Stores)

PyMongo Document Query ExampleText
from pymongo import MongoClient

client = MongoClient("mongodb://localhost:27017/")
db = client["ecommerce"]
orders = db["orders"]
high_value = list(orders.find({"total": {"$gte": 5000}}, {"_id": 0, "customer": 1, "total": 1}))

2.3 Accessing and processing data from APIs (REST, SOAP)

Modern applications expose live datasets (weather, finance, social feeds, government portals) over HTTP Web APIs.

Definition

REST vs. SOAP Web Services

AspectREST APISOAP API
Message FormatJSON (most common), XML, or plain textStrictly XML inside SOAP Envelope
Contract DefinitionInformal or OpenAPI / Swagger specFormal WSDL XML schema
Transport & CachingHTTP/HTTPS; GET responses are cacheableHTTP, SMTP, JMS; rarely HTTP-cached
Payload OverheadCompact, fast to parse in Python (resp.json())Verbose XML tags, heavier parsing (zeep / lxml)

Consuming REST APIs Safely in Python

Best practices when fetching API data with requests:

  1. Always set a timeout=(connect_s, read_s) so hung sockets never stall a pipeline forever.
  2. Check HTTP status codes with resp.raise_for_status().
  3. Pass query parameters via params={...} instead of manual URL concatenation.
  4. Handle authentication (API keys, Authorization: Bearer <token> headers stored in environment variables—never hardcoded) and pagination (page, offset, or next cursor URLs) plus rate-limit backoff on HTTP 429.
Python
import requests

def fetch_all_pages(base_url: str, token: str, limit: int = 100) -> list[dict]:
    headers = {"Authorization": f"Bearer {token}", "Accept": "application/json"}
    results = []
    page = 1
    while True:
        resp = requests.get(
            base_url,
            headers=headers,
            params={"page": page, "limit": limit},
            timeout=(5, 15),
        )
        resp.raise_for_status()
        payload = resp.json()
        batch = payload.get("items", [])
        if not batch:
            break
        results.extend(batch)
        if len(batch) < limit:
            break
        page += 1
    return results

Consuming SOAP APIs

In Python, SOAP endpoints are consumed either with zeep (which parses the service's ?wsdl URL and auto-generates Python client methods) or by posting a SOAP XML envelope via requests.post(..., headers={"Content-Type": "text/xml"}) and parsing the XML reply with xml.etree.ElementTree.


2.4 Web scraping using requests and BeautifulSoup

When a website publishes public data in HTML pages without an official API, web scraping extracts structured records from the DOM tree.

Ethical & Legal Scraping Checklist

  1. Check if an official API or bulk CSV download already exists first.
  2. Inspect https://example.com/robots.txt and respect Disallow rules and Crawl-delay.
  3. Identify your scraper with a polite User-Agent header and throttle requests (time.sleep(1.0)) to avoid overloading servers.
  4. Never bypass authentication, CAPTCHAs, or copyright restrictions.

Scraping Workflow with requests and BeautifulSoup (bs4)

Definition

Key BeautifulSoup Parsing Methods

Python
import requests
from bs4 import BeautifulSoup

url = "https://example.org/courses"
resp = requests.get(url, headers={"User-Agent": "AcademicScraper/1.0"}, timeout=10)
resp.raise_for_status()

soup = BeautifulSoup(resp.text, "html.parser")
courses = []
for row in soup.select("table#catalog tbody tr"):
    cells = [td.get_text(strip=True) for td in row.find_all("td")]
    if len(cells) >= 3:
        courses.append({"code": cells[0], "title": cells[1], "credits": int(cells[2])})

Note that requests + BeautifulSoup fetches static server-rendered HTML; pages whose content is rendered client-side by JavaScript require inspecting network XHR/JSON endpoints or using headless browser automation (e.g., Playwright/Selenium).


2.5 Handling large datasets with chunking and lazy evaluation

Loading a 10 GB CSV file into memory at once (pd.read_csv("huge.csv") or f.read()) exhausts RAM and triggers an Out-Of-Memory (MemoryError / OOM kill) crash. Two complementary patterns keep memory footprint bounded: chunking and lazy evaluation.

AspectChunking (Bounded Batch Processing)Lazy Evaluation (Deferred Execution)
MechanismSplits input into fixed-size batches (chunksize=50_000) processed sequentiallyBuilds a computation graph/iterator; evaluates only when output is materialized
Memory ProfileO(chunk_size) — peak RAM fixed regardless of total file rowsO(1) for streaming generators; optimized batch memory in lazy DataFrames
Python / DS Toolspd.read_csv(..., chunksize=N), batched SQL fetchmany(N)Python generators (yield), itertools.islice, polars.scan_csv(), dask.dataframe
Typical OperationRunning sum/count, filtering rows, writing cleaned batches to Parquet/SQLitePredicate pushdown, projection pushdown, chained filter/map pipelines

Chunking with pandas and sqlite3

When computing aggregate statistics or filtering a multi-gigabyte CSV:

Python
import pandas as pd

total_revenue = 0.0
row_count = 0

for chunk in pd.read_csv("transactions_10gb.csv", chunksize=100_000, usecols=["amount", "status"]):
    valid = chunk[chunk["status"] == "completed"]
    total_revenue += float(valid["amount"].sum())
    row_count += len(valid)

avg_order = total_revenue / row_count if row_count else 0.0

Streaming & Lazy Evaluation in Pure Python

Combining generator functions with csv.DictReader streams arbitrarily large files in constant memory:

Python
import csv

def stream_high_amounts(path: str, threshold: float):
    with open(path, "r", newline="", encoding="utf-8") as fh:
        for row in csv.DictReader(fh):
            val = float(row["amount"])
            if val >= threshold:
                yield row["tx_id"], val

In modern data engineering, lazy DataFrame engines (Polars scan_csv().filter(...).select(...).collect() and Dask) go further by applying projection pushdown (reading only needed columns from disk) and predicate pushdown (skipping row groups that fail filter bounds).


Worked Examples

Example 2.1

2.1

A CSV file has 850,000 rows, each occupying roughly 64 bytes in memory when parsed. Compare the peak data memory of loading the entire file at once versus reading it with pd.read_csv(..., chunksize=50_000). How many chunks are processed? Also write the chunked aggregation code to compute the mean of "price" where "valid" == 1.

Calculating Batch Count and Memory Usage:

  • Total number of chunks = 850,000 / 50,000 = 17 full chunks (with zero remainder).
  • Full-load data size = 850,000 * 64 bytes = 54,400,000 bytes ≈ 51.88 MiB (before pandas object/index overhead, which often multiplies raw size 3–5x).
  • Single-chunk data size = 50,000 * 64 bytes = 3,200,000 bytes ≈ 3.05 MiB, reducing peak memory by a factor of 17x.

Chunked Aggregation Code: Accumulate running numerator (total_price) and denominator (valid_count) across chunks rather than averaging chunk averages (which would weight unequal chunks incorrectly):

Python
import pandas as pd

total_price = 0.0
valid_count = 0

for chunk in pd.read_csv("listings.csv", chunksize=50_000, usecols=["price", "valid"]):
    filtered = chunk[chunk["valid"] == 1]
    total_price += float(filtered["price"].sum())
    valid_count += len(filtered)

overall_mean = total_price / valid_count if valid_count > 0 else 0.0
Answer:

17 chunks of 50,000 rows are processed. Peak row buffer drops from 51.88 MiB to 3.05 MiB (a 17x reduction). Accumulate total_price and valid_count across chunks and compute total_price / valid_count once after the loop finishes.

Example 2.2

2.2

Write a Python script that inserts three product records [("Mouse", 120), ("Keyboard", 80), ("Cable", 50)] safely into a SQLite table using parameterized queries, queries items priced above 60, and computes their average price across all three items.

Inserting Safely with Parameterized Queries (executemany): Use ? placeholders so values are never interpolated into SQL strings:

Python
import sqlite3

products = [("Mouse", 120.0), ("Keyboard", 80.0), ("Cable", 50.0)]

with sqlite3.connect(":memory:") as conn:
    cur = conn.cursor()
    cur.execute("CREATE TABLE products (name TEXT, price REAL)")
    cur.executemany("INSERT INTO products (name, price) VALUES (?, ?)", products)

    above_60 = cur.execute(
        "SELECT name, price FROM products WHERE price > ? ORDER BY price DESC", (60.0,)
    ).fetchall()
    avg_all = cur.execute("SELECT AVG(price) FROM products").fetchone()[0]

Tracing Query Output and Arithmetic:

  • above_60 returns [("Mouse", 120.0), ("Keyboard", 80.0)] ("Cable" at 50.0 is excluded by price > 60).
  • AVG(price) across all three rows = (120 + 80 + 50) / 3 = 250 / 3 ≈ 83.33.
Answer:

above_60 returns [("Mouse", 120.0), ("Keyboard", 80.0)], and the average price across all three rows is 83.33 (250 / 3).

Example 2.3

2.3

How should a student answer an exam question asking: "Compare REST and SOAP APIs, and explain when to choose an API versus BeautifulSoup web scraping for data collection"?

Part 1 — Structured Comparison of REST vs. SOAP: Present a crisp 4-row table covering format (JSON vs. XML Envelope), contract (OpenAPI vs. WSDL), transport/caching (HTTP verbs + cacheable GET vs. protocol-agnostic/non-cached), and parsing complexity in Python (requests + .json() vs. zeep/XML parsing).

Part 2 — API vs. Web Scraping Decision Criteria:

  1. Always prefer an official REST/GraphQL API when available: structured JSON schemas rarely break, authentication and rate limits are explicit, and bandwidth is minimal.
  2. Fall back to web scraping (requests + BeautifulSoup) only when public HTML pages have no API or downloadable dump, while honoring robots.txt, throttling requests, and checking whether data is static HTML or dynamically loaded via XHR.
Answer:

Contrast REST (lightweight JSON over HTTP verbs, cacheable, requests) with SOAP (strict XML envelopes, WSDL contracts, zeep) in a comparison table, then justify preferring official APIs for schema stability and legal clarity over HTML scraping (BeautifulSoup) with robots.txt and rate-limiting safeguards.


Exercises

Practice Exercises — Data Sources and APIs

Question 1

Compare CSV, JSON, and Excel (.xlsx) formats for storing and exchanging datasets in Python, mentioning which Python modules/functions read and write each format and one pitfall to avoid for CSV and JSON.

Format Comparison:

  • CSV: Flat, row-oriented plain text; minimal storage overhead but lacks native types (everything parses as strings unless cast) and cannot represent nested structures. Read/written via csv.DictReader/DictWriter or pandas.read_csv()/to_csv(). Pitfall: forgetting newline="" on Windows writes extra blank lines, or unquoted commas shift columns.
  • JSON: Hierarchical key-value/array text format preserving types (str, int, bool, null, list, dict); ideal for REST APIs and nested records, though verbose for large flat tables. Read/written via json.load/loads/dump/dumps. Pitfall: loading a huge multi-GB JSON array exhausts RAM (prefer JSON Lines .jsonl).
  • Excel (.xlsx): Binary zip-XML workbook supporting multiple sheets, formulas, and formatting; slower to parse and limited to ~1,048,576 rows per sheet. Read/written via pandas.read_excel() or openpyxl.
Answer:

CSV suits flat tabular text (csv, pandas.read_csv); JSON suits nested/API records (json.load/loads/dump/dumps); Excel suits multi-sheet formatted workbooks (openpyxl, pandas.read_excel). Avoid missing newline="" in Python CSV writers and avoid loading giant single-array JSON files into memory instead of line-delimited JSONL.

Question 2

Why must you never use Python f-strings to build SQL queries in sqlite3? Show an unsafe query example, explain the risk, and rewrite it safely using parameterized placeholders.

Risk of String Formatting (SQL Injection): Interpolating user input directly into SQL—such as cur.execute(f"SELECT * FROM users WHERE name = '{user_input}'")—allows malicious input like ' OR '1'='1 or '; DROP TABLE users; -- to alter the query's syntax and execute unintended SQL commands.

Safe Parameterized Rewrite: Passing parameters as a tuple in the second argument lets the database driver bind values safely as data literals, never executable SQL:

Python
cur.execute("SELECT * FROM users WHERE name = ?", (user_input,))
Answer:

String interpolation allows SQL injection, where crafted input alters the SQL statement itself. Always use parameterized queries (cur.execute("SELECT * FROM users WHERE name = ?", (user_input,))) so the database driver treats input strictly as data.

Question 3

A paginated REST API returns at most limit = 100 items per request and reports total = 375 records.

  1. How many GET requests are needed to fetch all records, and how many items are in the final page?
  2. Write a Python snippet using requests with a timeout, raise_for_status(), and params to fetch one page safely.

Calculating Page Count and Final Page Size:

  • With 375 total items and 100 items per page:
    • Pages 1, 2, 3 each return 100 items (3 * 100 = 300 items).
    • Page 4 returns the remaining 375 - 300 = 75 items.
    • Total requests needed = ceil(375 / 100) = 4 requests (or 5 if the loop checks for an empty page rather than len(batch) < limit). Using the len(batch) < limit stop condition takes exactly 4 requests, with 75 items on the final page.

Safe requests.get Call:

Python
import requests

resp = requests.get(
    "https://api.example.com/v1/records",
    params={"page": 1, "limit": 100},
    headers={"Accept": "application/json"},
    timeout=(5, 10),
)
resp.raise_for_status()
data = resp.json()
Answer:
  1. 4 GET requests are required (stopping when len(batch) < 100), with 75 items returned on the 4th page.
  2. Pass params={"page": 1, "limit": 100}, timeout=(5, 10), call resp.raise_for_status(), and parse via resp.json().
Question 4

Given an HTML snippet containing <div class="card"><span class="name">Sensor A</span><span class="val">1200</span></div> (and two more cards with values 850 and 450), write a BeautifulSoup script to extract all values as integers and compute their average. Also state two ethical rules for web scraping.

Extracting and Averaging Values with BeautifulSoup:

Python
from bs4 import BeautifulSoup

soup = BeautifulSoup(html_doc, "html.parser")
vals = [int(el.get_text(strip=True)) for el in soup.select("div.card span.val")]
avg_val = sum(vals) / len(vals)

For [1200, 850, 450]: sum = 2500, count = 3, avg = 2500 / 3 ≈ 833.33.

Two Ethical Scraping Rules:

  1. Respect robots.txt directives and Terms of Service, and prefer official APIs when provided.
  2. Throttle request frequency (e.g., time.sleep rate limiting) and set an honest User-Agent header without bypassing login gates or CAPTCHAs.
Answer:

soup.select("div.card span.val") extracts [1200, 850, 450], giving an average of 833.33 (2500 / 3). Two ethical rules: honor robots.txt/site policies and throttle requests with a clear User-Agent.

Question 5

Explain chunking and lazy evaluation when processing datasets larger than available RAM. If a 2.4-million-row file is processed with chunksize=150_000, how many chunks are read, and why is generator-based streaming (yield) memory-efficient?

Chunking vs. Lazy Evaluation:

  • Chunking reads bounded slices (batches) of rows into RAM, processes/aggregates each slice, and discards it before loading the next slice (O(chunksize) memory).
  • Lazy evaluation defers computation until values are actually requested (e.g., Python generator functions using yield, generator expressions, or polars.scan_csv()), avoiding intermediate lists and enabling pushdown optimizations.

Chunk Count Calculation: Number of chunks = 2,400,000 / 150,000 = 16 chunks. A yield-based generator holds only one row/frame at a time in O(1) memory regardless of whether the source has 2.4 million or 2.4 billion rows.

Answer:

Chunking bounds peak RAM to O(chunksize), while lazy evaluation defers execution and streams one item/plan step at a time. A 2.4-million-row file at chunksize=150_000 reads 16 chunks, and a yield generator uses O(1) memory by yielding one record per iteration without storing the full dataset.

← Advanced Python Concepts and Best PracticesAdvanced Data Wrangling and Transformation (part 1: 3.1–3.3) →