Acquire documented data from files, SQL and APIs
On this page
The I/O lesson introduces CSV, Excel, HTML and SQL. This lesson turns acquisition into an accountable step in an investigation: obtain data, preserve the response, inspect its structure, and explain permission and provenance. You should already understand a DataFrame, an index and a simple filter.
Start with a data contract
Before loading a large file, record its producer, observation unit, keys, fields, units, coverage and licence. A repository’s popularity does not establish any of these. A dataset on Kaggle or Hugging Face may be uploaded by its original producer or by someone else. Follow the provenance back to that producer and record a version or revision, rather than treating a platform name as evidence of validity.
A Hugging Face dataset card can document intended uses, fields, limitations and licence metadata. Check what is actually present; a card template is not proof that every field has been completed or independently reviewed. For Kaggle, inspect the dataset page and its version history and consult the official API documentation before choosing an authenticated acquisition route. Do not embed an access token in a notebook or manuscript. These platform routes are optional; the worked example below uses a public institutional API and a saved local snapshot.
Inspect a real World Bank snapshot
The companion data contain total-population records for Germany, France and the United Kingdom, 2018–2023. The source is the World Bank’s World Development Indicators, indicator SP.POP.TOTL. The request, retrieval time, raw response, indicator metadata and file hashes are saved alongside the derived CSV. This is a selected comparison, not a random sample of countries.
Run the examples from the Book 2 project folder. The reader also provides the data files under Companion resources. Keep the path relative to the project so another reader can reproduce it.
from pathlib import Path
import json
import pandas as pd
base = Path("data/completion/worldbank")
raw = json.loads((base / "response.json").read_text())
print(raw[0]["page"], raw[0]["pages"], len(raw[1]))
population = pd.read_csv(base / "population.csv")
print(population.shape)
print(population[["country_code", "year"]].duplicated().sum())
assert len(population) == 18
assert not population.duplicated(["country_code", "year"]).any()1 1 18
(18, 4)
0Inspect the API’s metadata before indexing its records. Do not assume the first request contains all pages. The World Bank call-structure documentation explains response formats and paging parameters. A live acquisition script should use a timeout, check the HTTP status, validate the response shape, and stop clearly if a page fails. Retrying a failed request does not authorize silently accepting a partial dataset.
The supplied snapshot has one page, but that observation is not a general statement about every API query. The acquisition script is preserved so you can inspect the exact request. Re-downloading may produce revised estimates; report the snapshot used for an analysis rather than pretending that a future request will always be byte-identical.
Preserve and verify provenance
A hash is a compact fingerprint of saved bytes. It can detect an unexpected change after acquisition; it cannot prove that the original measurement was correct. Check the raw response as well as the derived table, because a valid raw download can still be transformed incorrectly.
import hashlib
provenance = json.loads((base / "provenance.json").read_text())
actual = hashlib.sha256((base / "response.json").read_bytes()).hexdigest()
assert actual == provenance["response_sha256"]
print(provenance["producer"], provenance["indicator"])World Bank, World Development Indicators SP.POP.TOTLWorld Bank open data use CC BY 4.0 as a default, with dataset-specific notices and additional terms. Credit the producer and indicator, retain the source and retrieval record, and describe changes you made. Read the data-access and licensing terms rather than assuming that every item hosted by an institution has identical permission. In this example, extraction retains country, code, year and population; it performs no imputation.
Ask a database a bounded question
SQL lets you select rows and aggregate close to the data source. A WHERE clause filters observations; GROUP BY defines groups; ORDER BY makes presentation order explicit. Parameterize values rather than building a query by inserting untrusted text into its syntax.
import sqlite3
with sqlite3.connect(":memory:") as connection:
population.to_sql("population", connection, index=False, if_exists="replace")
latest = pd.read_sql_query(
"SELECT country_code, year, population FROM population "
"WHERE year = ? ORDER BY country_code",
connection, params=(2023,),
)
print(latest)
assert len(latest) == 3 country_code year population
0 DEU 2023 83287273
1 FRA 2023 68372286
2 GBR 2023 68526000An in-memory database is convenient for learning; it does not demonstrate persistent production storage. For a file database, preserve the database or the scripts and inputs needed to rebuild it. Writing with if_exists='replace' is appropriate for a disposable teaching table, not a general instruction to overwrite somebody’s database.
Choose formats intentionally
CSV is inspectable but does not preserve every data type. Excel may include multiple sheets, formulas and presentation features that a table import does not reproduce. Use the documented sheet_name parameter when selecting a worksheet. Parquet is useful when preserving typed columnar data and reading selected columns, but requires a compatible engine and does not replace a data dictionary. HTML tables are especially dependent on page structure; live examples such as the FDIC table may require revision when the producer changes its page.
The Pandas I/O guide documents format-specific readers and their parameters; check the installed version when following an older example.
Practice and independent check
Write a source register for the World Bank snapshot: one row per input, including location, producer, licence, selection rule, observation unit, coverage, hash and limitations. Compare the 2023 SQL result with a Pandas filter and verify equality after sorting columns and rows. Then choose one Kaggle or Hugging Face candidate and assess its producer, card, licence and version without assuming that being downloadable makes it suitable for your question.
Transition
Acquisition ends when you have documented evidence you can inspect again. Cleaning begins by checking that the evidence has the types, keys and values your question requires.