Skip to content
Expert O'clock

Python

APIs for analysts: reading JSON into a report

How analysts can pull JSON from an API into a reporting table: inspect the shape, authenticate safely, paginate, flatten, and validate.

Talha Ansari··5 min read

Key takeaways

  • Inspect the response shape and decide the reporting grain before flattening.
  • Keep credentials out of code and stop on authentication failures.
  • Paginate until the result is complete, then deduplicate by record key.
  • Validate types and counts against the API before the data reaches a report.
On this page

Read the response before writing code

An API response is a document with a shape, not a table. Send one request, save the response, and inspect it. Identify the collection that holds the records, the fields that identify each record, the fields that repeat, and the metadata that reports totals or pagination.

A response often nests line items inside an order, or addresses inside a customer. The nesting usually indicates the grain. Deciding whether the report needs one row per order or one row per line determines how the JSON must be flattened and which totals can be summed.

Authenticate without leaking credentials

Read tokens from environment variables or a secret manager. Never paste a key into a notebook, a script committed to Git, or a shared dashboard. Put the token in a request header and keep it out of logs; log the endpoint and status code instead of the full request.

Set a timeout on every request and handle the common failures: expired token, rate limit, server error, and malformed response. Retry with exponential backoff for rate limits and transient server errors. A 401 or 403 should stop the run and alert the owner, not retry indefinitely.

  • Store the token outside the repository and load it at runtime.
  • Set connect and read timeouts so a stalled request cannot hang the job.
  • Respect the documented rate limit and leave headroom for other clients.
  • Alert on authentication failures instead of retrying them.

Paginate until the result is complete

Many APIs return a page of records plus a cursor or a link to the next page. Loop until the cursor is empty, not until a page contains fewer records than the page size; some APIs return short pages before the end. Keep a maximum page count as a guard against an infinite loop.

Deduplicate by record ID after collection. Overlapping pages, retries, and records updated during the run can all produce duplicates. Log the number of records requested, received, and kept so the final count can be explained.

Flatten nested JSON deliberately

json_normalize turns a list of dictionaries into a table. Use record_path for the nested list that defines the grain, and meta for parent fields that should repeat on each row. Check the resulting column names; dots in nested keys become awkward names to query.

If the API returns a single object per request, pass a list containing that object. If a field can be either an object or a list, inspect several real responses before writing a transformation. Coercing one shape into another produces rows that look valid but are not.

Read one page of orders with nested line itemsPython
import os

import pandas as pd
import requests

token = os.environ['REPORTING_API_TOKEN']
response = requests.get(
    'https://api.example.com/v1/orders',
    headers={'Authorization': f'Bearer {token}'},
    params={'status': 'open', 'page_size': 100},
    timeout=30,
)
response.raise_for_status()

orders = pd.json_normalize(
    response.json()['data'],
    record_path='lines',
    meta=['order_id', 'customer_id', 'order_date'],
    errors='raise',
)

Type and validate the output

JSON has no date, decimal, or integer type in the way a database does. Timestamps may be ISO strings with an offset, epoch seconds, or local time without an offset. Convert them to a consistent timezone before reporting, and keep the original string in the raw file when the conversion is ambiguous.

Validate required fields, uniqueness of the record key, and the count reported by the API. Compare a known total with the source if one is available. A missing field often becomes a null that shifts an average; a schema change can rename a field without warning.

JSON patterns that cause reporting errors
PatternRiskHandling
Nested line itemsOrder totals repeat per lineChoose the grain and aggregate once
Null fieldAverages shift and counts changeFill, flag, or reject with a stated rule
Timestamp with offsetMixed time zonesConvert to a consistent zone and keep the source
Numeric stringText sorting and failed sumsCast explicitly and log failures
Renamed fieldSilent null columnValidate the schema and fail visibly
Pagination cursorPartial extractLoop until empty and reconcile counts

Keep the raw response

Save the raw JSON with the request parameters and a fetch timestamp before transforming it. Storage is cheap compared with rerunning an API that has since changed. The raw file also lets you reproduce last month's report when the source has moved on.

Do not commit raw responses containing customer data to a public repository. Keep them in a controlled landing area with a retention rule, and restrict access to the people who need it.

Frequently asked questions

Do I need Python to use an API?+

No. Any tool that can send an HTTP request can call one. Python is useful when the response is nested, paginated, or needs to be joined with other data before reporting.

How do I find the API documentation?+

Ask the vendor or internal platform team for the base URL, authentication method, rate limits, and pagination scheme. Check whether an OpenAPI description exists, and test one request before building the full extract.

What is the difference between an API and a CSV export?+

An API returns structured data, often nested and usually fresher, but it is rate-limited and can change without notice. A CSV export is flat and easy to inspect, but it is a snapshot and may be produced on a different schedule.

How often should I pull the data?+

Match the refresh to the decision being supported. Use incremental requests where the API supports them, and a full refresh where the dataset is small enough. More frequent pulls add cost without improving the decision.

Need help applying this?

Get help with Data Cleanup & Pipelines.

Review the service scope or tell us what you are trying to improve. We will reply with a clear next step.

Discuss this serviceView service
Free KPI definition template

Related reading