Data Acquisition and Metadata Documentation

Data Acquisition and Metadata Documentation

Why Provenance Matters

Before any analysis can be trusted, you must know exactly where your data came from, when it was collected, who maintains it, and what its known limitations are. In professional data science, undocumented data is treated as unreliable data. City and municipal open-data portals publish datasets that are genuinely useful — permit records, 311 service calls, traffic counts, crime reports — but they vary widely in completeness, update frequency, and licensing terms. Your job in this lab is to pull those datasets programmatically, inspect them carefully, and document every important fact about each source in a standardized data-source log before you build a single chart or model.

Open-Data APIs and the Socrata Platform

Many US cities host their open-data portals on a platform called Socrata, which exposes datasets through a consistent REST API called SODA (Socrata Open Data API). Chicago, New York, Los Angeles, San Francisco, and dozens of smaller cities all use this platform. The API returns data in JSON (or CSV) and supports URL-based query parameters such as `$limit`, `$offset`, `$where`, and `$select`. You authenticate with an app token — a free API key you register on the portal — which raises your rate limit and identifies your application.

A basic Python `requests` call to a Socrata endpoint looks like this:

```python

import requests

import pandas as pd

url = "https://data.cityofchicago.org/resource/ijzp-q8t2.json"

params = {"$limit": 5000, "$$app_token": "YOUR_TOKEN_HERE"}

response = requests.get(url, params=params)

response.raise_for_status() # raises an error on HTTP 4xx/5xx

df = pd.DataFrame(response.json())

print(df.dtypes)

print(df.isnull().sum())

```

Always call `raise_for_status()` immediately after the request. This converts silent HTTP error codes into Python exceptions, so problems surface early rather than corrupting downstream analysis.

Inspecting Columns and Data Types

Once data is in a DataFrame, your first moves are defensive:

1. Check column data types — numeric fields imported from JSON often arrive as strings. Cast them explicitly.

2. Count nulls per column — a field that is 40 % null may be unusable for your research question.

3. Check date ranges — confirm the data covers the period you need.

4. Look for duplicates — use `df.duplicated().sum()` before loading anything.

5. Cross-reference the official data dictionary — the portal's dataset page or a linked PDF defines what each column means, its unit of measure, and any known coding issues.

Never assume a column name is self-explanatory. "Status" in one city's permit dataset means something different from "Status" in another city's inspection dataset.

Loading into PostgreSQL

Storing acquired data in PostgreSQL rather than raw CSV files gives you a versioned, queryable record of what you ingested and when. Use `psycopg2` to write DataFrames row by row, or use `pandas.DataFrame.to_sql()` with a SQLAlchemy engine pointed at your local PostgreSQL 15 instance. Create a dedicated schema for each data source and add an `ingested_at` timestamp column so you always know when you pulled the data.

```python

from sqlalchemy import create_engine

engine = create_engine("postgresql+psycopg2://user:password@localhost:5432/capstone")

df["ingested_at"] = pd.Timestamp.utcnow()

df.to_sql("chicago_crimes_raw", engine, schema="city_data",

if_exists="replace", index=False)

```

Use `if_exists="replace"` during early development, but switch to `"append"` with deduplication logic once your pipeline stabilizes.

Completing the Data-Source Log

The data-source log is a structured spreadsheet with one row per dataset. Required fields for this program are:

• Dataset name — official name from the portal
• Source URL and API endpoint — full URL including version path
• API parameters used — every query parameter you passed
• License — e.g., Public Domain, CC0, ODC-PDDL, or a city-specific open-data license
• Data owner / publisher — the city department responsible
• Date range of records — earliest and latest dates in the data
• Row count ingested — actual count after your query
• Column definitions — for every column you plan to use, note the source dictionary definition and data type
• Update frequency — how often the portal refreshes this dataset
• Known caveats and limitations — incomplete years, reporting lags, definitional changes, geographic coverage gaps

This log is not a formality. It is the foundation of your methodology section and the document a peer must use to reproduce your work independently.

Pair Review and Reproducibility

Reproducibility is a professional standard. After you complete your log, you swap it with a classmate. Your partner's only inputs are the URL, API parameters, and app token instructions you documented. If they cannot retrieve the same dataset and get a row count within a reasonable margin, your documentation has a gap. Fix every flagged item before submitting. This mirrors how data engineering teams conduct peer reviews of data pipelines.

Credential Connections

This lab maps directly to CompTIA Data+ Domain 1 (data acquisition and quality), which tests knowledge of data sources, metadata, data dictionaries, and data governance. Google Data Analytics and IBM Data Science Professional both emphasize documented, reproducible workflows. Microsoft PL-300 candidates must understand data source connections and refresh settings — the same provenance concepts you practice here.

A Note on Data Ethics and Licensing

Open does not mean unrestricted. Read the license for every dataset. Some city portals require attribution; a few restrict commercial use. Check whether the dataset contains any personally identifiable information (PII) — even publicly posted data can raise privacy concerns when combined with other sources. Document the license in your log and flag any PII-adjacent fields (name, address, birth date) so your team can handle them appropriately.