Activity 2.3 - STA556 Statistics and Data Science Computing Workflows

This page is an ipython notebook, meaning you can download it and run the executable cells. It directly follows Activity 2.3. Reference answers appear after the questions, challenges, predictions, and reflections. They show one reasonable solution or interpretation, but other correct approaches may exist.

Input/Output & External Data Sources¶

Learning objectives¶

By the end of this tutorial, you should be able to:

read and write CSV files;
inspect data after ingestion;
work with project-relative paths;
create and query an SQLite database;
load SQL results into pandas;
retrieve data from an HTTP API;
inspect status codes;
parse and normalize JSON;
read HTML tables;
save and load Parquet;
understand the role of HDF5;
validate externally sourced data;
preserve raw data and record provenance.

Part 0 — Create a Week 5 project structure¶

Create:

data/
├── raw/
├── processed/
└── external/

Create:

notebooks/week05_external_data.ipynb

Import:

from pathlib import Path
import sqlite3
import json

import pandas as pd
import requests

Define:

DATA_DIR = Path("data")
RAW_DIR = DATA_DIR / "raw"
PROCESSED_DIR = DATA_DIR / "processed"
EXTERNAL_DIR = DATA_DIR / "external"

Part 1 — Create a source dataset¶

participants = pd.DataFrame({
    "id": ["001", "002", "003", "004", "005"],
    "age": [24, 31, 27, 42, 35],
    "group": ["A", "B", "A", "B", "A"],
    "score": [81, 94, 88, 91, 85]
})

Inspect:

participants
participants.dtypes

Why might storing id as a string matter?

Reference answer¶

Storing id as a string preserves identifiers such as "001" exactly.

If pandas interprets the values as integers, leading zeros can disappear ("001" becomes 1). IDs are labels, not measurements, so arithmetic operations on them are generally meaningless.

Part 2 — Write a CSV¶

csv_path = RAW_DIR / "participants.csv"

participants.to_csv(
    csv_path,
    index=False
)

Check:

csv_path.exists()

Open the file as text.

Question¶

What pandas data-type information is explicitly stored in the CSV?

Reference answer¶

The CSV file stores the values as text separated by delimiters, but it does not store pandas dtype metadata such as string, category, or integer/float dtype declarations.

Part 3 — Read the CSV¶

loaded = pd.read_csv(csv_path)

Inspect:

loaded
loaded.dtypes

What happened to:

001
002
003

This illustrates that file ingestion can alter representation.

Reference answer¶

When the CSV is read without dtype instructions, pandas infers the column types from the file contents.

That means an identifier like 001 may be inferred as numeric and become 1, even though the original DataFrame stored it as a string.

Part 4 — Control data types¶

loaded = pd.read_csv(
    csv_path,
    dtype={
        "id": "string",
        "group": "category"
    }
)

Inspect:

loaded.dtypes
loaded

Why is an identifier often better represented as a string than as a numeric measurement?

Reference answer¶

An identifier is usually a label, not a quantitative measurement.

Representing it as a string helps preserve formatting such as leading zeros and prevents accidental interpretation of operations such as means, sums, or numeric comparisons as scientifically meaningful.

Part 5 — Missing-value codes¶

messy = pd.DataFrame({
    "id": ["001", "002", "003", "004"],
    "score": ["81", "-999", "88", "NA"]
})

Save:

messy_path = RAW_DIR / "messy_scores.csv"

messy.to_csv(
    messy_path,
    index=False
)

Read:

messy_loaded = pd.read_csv(
    messy_path,
    na_values=["NA", "-999"]
)

Inspect:

messy_loaded
messy_loaded.dtypes
messy_loaded.isna().sum()

Why is documenting missing-value coding part of provenance?

Reference answer¶

The values "NA" and "-999" are source-specific encodings for missingness. Using:

na_values=["NA", "-999"]

tells pandas to interpret them as missing rather than as valid observations.

Documenting this is part of provenance because another analyst needs to know how raw source values were translated into the analytical dataset.

Part 6 — Validate after reading¶

loaded.shape
loaded.head()
loaded.dtypes
loaded.isna().sum()
loaded["id"].nunique()
loaded["group"].value_counts()

Add checks:

assert len(loaded) == 5
assert loaded["id"].is_unique

assert set(
    loaded["group"].astype(str)
) <= {"A", "B"}

A successful read operation is only the beginning of ingestion.

Reference answer¶

A successful file read only proves that pandas could parse the file.

Validation should still check properties such as:

expected number of rows and columns;
column names and dtypes;
missingness;
uniqueness of IDs;
allowed categorical values;
plausible numeric ranges.

The supplied assertions verify that there are 5 rows, IDs are unique, and groups are limited to A and B.

Part 7 — Create an SQLite database¶

db_path = RAW_DIR / "study.db"

connection = sqlite3.connect(db_path)

Write:

participants.to_sql(
    "participants",
    connection,
    if_exists="replace",
    index=False
)

Part 8 — Read an SQL table¶

sql_df = pd.read_sql(
    "SELECT * FROM participants",
    connection
)

Inspect:

sql_df

What role did SQL play, and what role did pandas play?

Reference answer¶

SQL retrieves data from the database and can perform selection, filtering, aggregation, and joins before the data reach Python.

Pandas receives the query result as a DataFrame and is then used for downstream analysis and manipulation.

Part 9 — Select columns with SQL¶

query = """
SELECT
    id,
    age,
    score
FROM participants
"""

selected = pd.read_sql(
    query,
    connection
)

Compare with:

participants[
    ["id", "age", "score"]
]

Part 10 — Filter with SQL¶

query = """
SELECT *
FROM participants
WHERE age >= 30
"""

older = pd.read_sql(
    query,
    connection
)

Compare with:

participants.loc[
    participants["age"] >= 30
]

Why might filtering in SQL be advantageous for a very large database?

Reference answer¶

Filtering in SQL can reduce the amount of data transferred from the database to Python.

For a very large database, this can save memory, reduce network I/O, and let the database engine use indexes and query optimization to do the filtering efficiently.

Part 11 — SQL aggregation¶

query = """
SELECT
    group,
    COUNT(*) AS n,
    AVG(score) AS mean_score
FROM participants
GROUP BY group
"""

summary = pd.read_sql(
    query,
    connection
)

Challenge¶

Write SQL returning:

group;
minimum score;
maximum score.

Reference answer¶

One solution is:

SELECT
    group,
    MIN(score) AS min_score,
    MAX(score) AS max_score
FROM participants
GROUP BY group;

Part 12 — Parameterized SQL¶

query = """
SELECT *
FROM participants
WHERE group = ?
"""

group_a = pd.read_sql(
    query,
    connection,
    params=("A",)
)

Why might parameters be preferable to inserting values directly into a query string?

Reference answer¶

Parameterized SQL separates the query structure from the values supplied to it.

This is preferable because it handles quoting/escaping correctly and helps prevent SQL-injection vulnerabilities. It is also cleaner than constructing SQL with string concatenation.

Part 13 — Create another table¶

outcomes = pd.DataFrame({
    "id": ["001", "002", "003", "005"],
    "followup_score": [85, 97, 91, 89]
})

outcomes.to_sql(
    "outcomes",
    connection,
    if_exists="replace",
    index=False
)

Part 14 — SQL join¶

query = """
SELECT
    p.id,
    p.age,
    p.group,
    p.score,
    o.followup_score
FROM participants AS p
LEFT JOIN outcomes AS o
    ON p.id = o.id
"""

joined = pd.read_sql(
    query,
    connection
)

Inspect:

joined
joined.isna().sum()

Which Week 4 pandas merge is equivalent?

Reference answer¶

The SQL LEFT JOIN is equivalent to a pandas left merge such as:

participants.merge(
    outcomes,
    on="id",
    how="left"
)

Every participant is retained, and unmatched outcome values become missing.

Part 15 — Close the connection¶

connection.close()

Why close resources when they are no longer needed?

Reference answer¶

Database connections, files, and network responses use finite system resources.

Closing them when they are no longer needed releases those resources, avoids locked files or lingering connections, and makes the lifecycle of the workflow explicit.

Part 16 — Make an HTTP request¶

Use a public practice API:

url = "https://jsonplaceholder.typicode.com/users"

response = requests.get(
    url,
    timeout=10
)

Inspect:

response.status_code

Then:

response.raise_for_status()

Why should the program check the response before analysis?

Reference answer¶

The program should check the HTTP response because receiving a response object does not guarantee that the request succeeded.

response.raise_for_status()

raises an exception for unsuccessful HTTP status codes instead of allowing the workflow to continue with an error page or invalid payload.

Part 17 — Inspect and parse JSON¶

response.text[:500]

Then:

data = response.json()

Inspect:

type(data)
len(data)
type(data[0])
data[0]

What Python structures does the JSON become?

Reference answer¶

response.json() converts the JSON response into ordinary Python objects.

For this endpoint:

the overall object is a list;
each user is a dict;
nested fields such as address are themselves dictionaries (and may contain further nested dictionaries).

Part 18 — Convert JSON to a DataFrame¶

users = pd.DataFrame(data)

Inspect:

users.head()
users.columns

Look at:

users.loc[0, "address"]

Why is this column not yet a simple scalar variable?

Reference answer¶

The address value is not yet a scalar variable because each cell contains a dictionary with multiple pieces of information such as street, city, zipcode, and geographic coordinates.

Many analyses require those nested fields to be expanded into separate columns.

Part 19 — Normalize nested JSON¶

users_flat = pd.json_normalize(data)

Inspect:

users_flat.head()
users_flat.columns

Challenge¶

Select:

id
name
email
address.city
company.name

Reference answer¶

After pd.json_normalize(data), nested fields become columns such as address.city and company.name.

For example:

users_flat.loc[
    :,
    ["id", "name", "email", "address.city"]
]

Part 20 — Save the raw API response¶

json_path = RAW_DIR / "users.json"

with open(
    json_path,
    "w",
    encoding="utf-8"
) as f:
    json.dump(
        data,
        f,
        indent=2
    )

Why does saving the response improve reproducibility?

Reference answer¶

Saving the raw API response creates a fixed snapshot of exactly what was retrieved.

That improves reproducibility because later analyses can use the same raw input even if the remote API changes, becomes unavailable, or returns different records in the future.

Part 21 — Read the local JSON snapshot¶

with open(
    json_path,
    "r",
    encoding="utf-8"
) as f:
    local_data = json.load(f)

Compare:

local_data == data

Now your analysis can use the saved snapshot rather than repeatedly calling the API.

Reference answer¶

Reading the saved JSON snapshot decouples analysis from the live API.

The comparison:

local_data == data

should be True, showing that the saved and reloaded representation matches the response used in the session.

Part 22 — API error experiment¶

bad_url = (
    "https://jsonplaceholder.typicode.com/"
    "does-not-exist"
)

bad_response = requests.get(
    bad_url,
    timeout=10
)

Inspect:

bad_response.status_code

Then:

bad_response.raise_for_status()

Why is failure handling part of data engineering?

Reference answer¶

Failure handling is part of data engineering because external systems can return errors, time out, change schema, or become unavailable.

A robust pipeline should fail clearly and early rather than silently treating an error response as valid analytical data.

Part 23 — Read HTML tables¶

Use a page containing tabular data:

url = (
    "https://en.wikipedia.org/wiki/"
    "List_of_states_and_territories_"
    "of_the_United_States"
)

tables = pd.read_html(url)

Inspect:

len(tables)
tables[0].head()

read_html() returns a list because a page may contain multiple tables.

Reference answer¶

pd.read_html() returns a list of DataFrames because one web page can contain multiple HTML tables.

The analyst must determine which returned table corresponds to the intended source data.

Part 24 — Identify the appropriate table¶

for i, table in enumerate(tables[:5]):
    print(
        i,
        table.shape,
        list(table.columns)[:4]
    )

Why is assuming tables[0] unsafe in a reusable workflow?

Reference answer¶

Assuming tables[0] is unsafe because page structure can change, extra tables can be inserted, or the first table may simply not be the one you need.

A reusable workflow should identify the desired table using its structure, expected columns, caption, or other stable characteristics and then validate it.

Part 25 — Save processed data¶

processed_csv = (
    PROCESSED_DIR / "users_processed.csv"
)

users_flat.to_csv(
    processed_csv,
    index=False
)

This separates raw external input from processed analytical data.

Part 26 — Write Parquet¶

parquet_path = (
    PROCESSED_DIR / "users_processed.parquet"
)

users_flat.to_parquet(
    parquet_path,
    index=False
)

Read:

users_parquet = pd.read_parquet(
    parquet_path
)

Inspect:

users_parquet.head()
users_parquet.dtypes

If a Parquet engine is missing, document the dependency indicated by the error.

Part 27 — Compare file sizes¶

processed_csv.stat().st_size
parquet_path.stat().st_size

Then:

ratio = (
    parquet_path.stat().st_size
    / processed_csv.stat().st_size
)

ratio

Should you generalize performance conclusions from such a tiny dataset?

Reference answer¶

No. This dataset is far too small to support general conclusions about storage size or performance.

File-size comparisons depend on row count, column types, repetition, compression, and the specific data. The exercise demonstrates how to compare formats, not which format is universally smaller.

Part 28 — Compare types¶

csv_again = pd.read_csv(
    processed_csv
)

Compare:

csv_again.dtypes
users_parquet.dtypes

What advantages can a typed binary format provide?

Reference answer¶

A typed binary format such as Parquet can preserve data types more faithfully than CSV, support compression, and allow efficient column-oriented reads.

Those properties can reduce storage and improve analytical I/O for larger datasets.

Part 29 — Optional HDF5 exercise¶

If PyTables is installed:

hdf_path = (
    PROCESSED_DIR / "users.h5"
)

users_flat.to_hdf(
    hdf_path,
    key="users",
    mode="w"
)

Read:

users_hdf = pd.read_hdf(
    hdf_path,
    key="users"
)

How does specifying a key differ from reading a simple CSV?

If the required dependency is not installed, document that rather than installing software without understanding why.

Reference answer¶

HDF5 can contain multiple named datasets inside one file. The key="users" argument identifies which internal dataset is being written or read.

A simple CSV typically corresponds to a single flat table and therefore has no equivalent internal key.

Part 30 — Create a provenance record¶

metadata = {
    "source": "JSONPlaceholder users endpoint",
    "url": "https://jsonplaceholder.typicode.com/users",
    "retrieved_for": "STA 556 Week 5",
    "raw_file": str(json_path),
    "processed_file": str(parquet_path)
}

Save:

metadata_path = (
    RAW_DIR / "users_metadata.json"
)

with open(
    metadata_path,
    "w",
    encoding="utf-8"
) as f:
    json.dump(
        metadata,
        f,
        indent=2
    )

What else would you record for a real research dataset?

Reference answer¶

For a real research dataset, useful provenance might also record:

retrieval date and time;
source organization;
exact endpoint/query parameters;
source version or release;
license or terms of use;
checksum/hash of the raw file;
software/package versions;
cleaning/transformation script version;
contact or documentation URL.

Part 31 — End-to-end ingestion challenge¶

Build a mini ingestion pipeline that:

requests the data;
checks the HTTP response;
converts the response to JSON;
saves the raw JSON;
normalizes the JSON;
selects useful variables;
validates row count and IDs;
saves processed data to Parquet;
writes provenance metadata.

Skeleton:

def fetch_users(
    url: str
) -> list[dict]:
    ...

def validate_users(
    data: pd.DataFrame
) -> None:
    ...

def save_users(
    data: pd.DataFrame,
    path: Path
) -> None:
    ...

Reference answer¶

A complete solution should implement the sequence explicitly: request → validate response → parse JSON → save raw JSON → normalize → select variables → validate → save processed data → write metadata.

The important design principle is that raw acquisition, validation, transformation, and processed output are separate, reproducible steps rather than ad hoc notebook edits.

Part 32 — Validate the pipeline¶

Write checks:

assert users_flat["id"].notna().all()

assert users_flat["id"].is_unique

assert len(users_flat) > 0

assert "email" in users_flat.columns

If the API schema changed tomorrow, which assertions could alert you?

Reference answer¶

Assertions that could reveal a future schema change include:

assert users_flat["id"].notna().all()
assert users_flat["id"].is_unique
assert len(users_flat) > 0
assert "email" in users_flat.columns

Additional checks could verify a set of required columns, expected dtypes, plausible row counts, or nested-field names.

A schema change might therefore fail an assertion even when the HTTP request itself succeeds.

Part 33 — Source-selection challenge¶

Choose the best approach.

A¶

A public agency provides a downloadable CSV.

B¶

A database contains 200 million transactions, but you need one state and one year.

C¶

A website provides a documented REST API.

D¶

A web page contains one clean HTML table but no downloadable file.

E¶

You repeatedly store and analyze a 20 GB tabular dataset.

Possible answers:

CSV
SQL query
API
read_html()
Parquet

Explain each choice.

Reference answer¶

A — CSV: the agency already provides a direct downloadable tabular file.
B — SQL query: push the state/year filter into the database rather than loading 200 million rows.
C — API: use the documented programmatic interface designed for data access.
D — read_html(): appropriate when the data exist as an HTML table and no better structured source is available.
E — Parquet: well suited to repeatedly storing and reading a large analytical table because it is typed, compressed, and column-oriented.

Part 34 — Reproducibility challenge¶

Compare:

Workflow A¶

Open website
Download file manually
Rename file
Move file to desktop
Edit several cells
Run notebook

Workflow B¶

Python downloads source
Raw source saved unchanged
Cleaning performed in code
Processed file created
Metadata recorded
Analysis reads processed file

Which is more reproducible?

What information is still missing even from Workflow B?

Codespaces secrets for authenticated APIs¶

The public APIs used in this tutorial may not require authentication. If a later API requires a key, never paste the key directly into a committed notebook or script.

Use an environment variable or a Codespaces secret, then read it in Python:

import os

api_key = os.environ[
    "MY_API_KEY"
]

Keep .env files and credentials out of Git.

Reference answer¶

Workflow B is more reproducible because acquisition and cleaning are encoded, the raw input is preserved, a processed artifact is created, and metadata are recorded.

Even Workflow B could still be missing information such as:

exact retrieval date/time;
source URL and query parameters;
source version;
package/environment versions;
data license;
checksums;
precise validation rules;
code/version-control commit used to create the processed file.

Authenticated APIs should also keep credentials outside committed code, for example in an environment variable or Codespaces secret.

Part 35 — Git checkpoint¶

Run:

git status

Ask:

Should raw data files be committed?

The answer depends on:

size;
licensing;
sensitivity;
reproducibility requirements.

Never automatically commit sensitive, proprietary, or extremely large data.

Commit appropriate code/documentation:

git add .
git commit -m "Complete Week 5 external data ingestion"
git push

Part 36 — Final reflection¶

1. I/O¶

What does input/output mean in a data-science workflow?

2. Data types¶

Why inspect dtypes after reading a CSV?

3. SQL¶

Why might filtering in SQL before loading into pandas be preferable?

4. APIs¶

What is the relationship between an HTTP response, JSON, and a DataFrame?

5. JSON¶

Why can nested JSON require normalization?

6. HTML¶

Why might an official API be preferable to scraping?

7. Parquet¶

Why might Parquet be preferable to CSV for a large analytical dataset?

8. Raw data¶

Why should raw source data generally remain unchanged?

9. Provenance¶

What would another researcher need to determine where your data came from?

10. Validation¶

Why can ingestion fail scientifically even when no Python exception occurs?

External data source
        ↓
Choose access method
        ↓
Read / query / request
        ↓
Check success
        ↓
Parse structure
        ↓
Validate schema + values
        ↓
Preserve raw input
        ↓
Transform
        ↓
Record provenance
        ↓
Save analysis-ready data

Next week we move into Exploratory Data Analysis and Visualization.

Reference answer¶

I/O: input is data entering a workflow; output is data or artifacts written or returned by it.
Data types: CSV does not preserve pandas dtype metadata, so inferred dtypes may be inappropriate or lossy.
SQL: filtering in the database reduces transferred data and can exploit database optimization/indexes.
APIs: an HTTP response carries the server’s reply; JSON is one possible serialized payload; parsed JSON can then be normalized into a DataFrame.
JSON: nested objects/lists are not simple scalar columns and often need flattening before tabular analysis.
HTML: an official API is usually more stable, structured, documented, and intended for programmatic access than scraping page markup.
Parquet: it preserves types, supports compression, and enables efficient column-oriented access.
Raw data: keeping raw inputs unchanged preserves an audit trail and allows transformations to be rerun.
Provenance: another researcher needs the source, retrieval details, version/query information, and transformation history.
Validation: data can parse successfully yet contain wrong IDs, unexpected missingness, changed schema, invalid ranges, or the wrong population.