Build a pandas ETL job around Trends API

This page is a pipeline. pandas pulls Trends API, concatenates keywords, and writes parquet or a warehouse table. It is not a notebook and not the languages page that stops at one DataFrame. POST https://api.trendsapi.ai/api. Parse the string body before DataFrame. Schedule the job with Airflow or cron. Fields: the API reference. Caps: pricing.

Pull function returns records, not the envelope

import json, os, requests, pandas as pd

def series_frame(keywords, source="google search"):
    session = requests.Session()
    session.headers.update({
        "Authorization": f"Bearer {os.environ['TRENDSAPI_API_KEY']}",
        "Content-Type": "application/json",
    })
    frames = []
    for keyword in keywords:
        resp = session.post(
            "https://api.trendsapi.ai/api",
            json={"mode": "get_time_series", "source": source, "keyword": keyword},
            timeout=60,
        )
        resp.raise_for_status()
        points = json.loads(resp.json()["body"])
        frames.append(pd.DataFrame.from_records(points))
    return pd.concat(frames, ignore_index=True)

from_records on the inner array yields date, value, keyword, source. pd.read_json on the envelope does not. The parse rule is on parse the body.

Reuse one Session for the list. Sleep with jitter on 429. Do not map a process pool that stampedes the monthly cap. Parse date with to_datetime. A missing volume key is not a zero score. That split is on volume versus score.

Two frames, then a write

weekly = series_frame(["bitcoin", "ethereum"])
weekly.to_parquet("s3://bucket/series/part.parquet")

A growth job is a separate function that reads results and DataFrame.from_records. Do not merge weekly value onto growth without renaming. 0-100 and a signed percent are different columns. Mode shapes sit on get_time_series and get_growth.

Warehouse DDL is on BigQuery. pandas can to_gbq after the concat. Retry the load from parquet if the insert fails. Do not POST the same keyword again for a load error.