DEV Community

Cover image for Counting Twitter Spaces in Python without counting other row types
Dataspryva
Dataspryva

Posted on Fully Autonomous

Counting Twitter Spaces in Python without counting other row types

A scheduled Space can disappear from your tracker simply by starting.

You're checking the list before publication. An announced show has vanished from the next pull, and “cancelled” looks like the obvious label. It's also a claim your dataset may not support.

spaceState filters exact links as well as discovery. Requesting a saved link with "upcoming" still excludes a Space that has become live or ended. An exact identifier doesn't override the filter.

Why did the scheduled Space disappear?

The trap is using the same query to find your subjects and decide what happened to them.

An upcoming-only pull answers which matching Spaces are scheduled now. It can't establish the fate of everything that appeared in an earlier pull.

Start with discovery, then retain the identities it gives you. Example: this input finds announced Spaces for a keyword and keeps the scheduled ones.

import os
import json
import uuid
from datetime import datetime, timezone

import pandas as pd
import duckdb
from apify_client import ApifyClient

client = ApifyClient(os.environ["APIFY_TOKEN"])

def fetch(run_input):
    run = client.actor("dataspryva/twitter-x-space-scraper").call(
        run_input=run_input
    )
    items = []
    for item in client.dataset(run["defaultDatasetId"]).iterate_items():
        items.append(item)
    return run, items

run_input = {
    "searchQueries": ["#Space"],
    "spaceState": "upcoming",
    "maxSpaces": 20,
    "includeParticipants": False,
    "includeParticipantProfiles": False,
}
discovery_run, discovery_items = fetch(run_input)
Enter fullscreen mode Exit fullscreen mode

Streaming the dataset avoids treating a page of results as the entire dataset.

Discovery reads recent public announcement posts. It isn't an archive of every Space, so membership in this initial cohort already has a coverage boundary. That boundary belongs in your reporting notes, before anyone calls the list a census.

Save the identity before asking about its fate

Use id from rows whose type is "space" as the Space key. Keep its spelling and case intact.

Other record types can refer to the Space through spaceId; their own rows aren't additional shows. Counting a mixed dataset without separating record types is an efficient way to manufacture an audience programme.

Several inputs resolving to the same Space produce a single collection of that Space. Your database should preserve that identity across runs, rather than treating each announcement post or URL as another subject.

The flow separates recruitment from follow-up:

announcement discovery -> retained Space IDs
                               |
                               v
                    exact IDs + spaceState: any
                               |
                               v
                    snapshots + unresolved pulls
Enter fullscreen mode Exit fullscreen mode

New discoveries can expand the cohort. A missing follow-up must never shrink it automatically.

Otherwise, the records most in need of investigation remove themselves from your investigation.

Give each pull a durable place

A snapshot key needs both the Space identity and an external run identifier. Keying only on id overwrites the evidence you're trying to compare.

Create a database that retains the cohort, requested IDs, snapshots and problem rows between invocations:

con = duckdb.connect("space_tracker.duckdb")
con.execute("SET TimeZone = 'UTC'")

con.execute("""
CREATE TABLE IF NOT EXISTS runs (
    run_id VARCHAR PRIMARY KEY,
    recorded_at TIMESTAMPTZ,
    actor_status VARCHAR
);
CREATE TABLE IF NOT EXISTS cohort (
    id VARCHAR PRIMARY KEY
);
CREATE TABLE IF NOT EXISTS requested (
    run_id VARCHAR,
    id VARCHAR,
    PRIMARY KEY (run_id, id)
);
CREATE TABLE IF NOT EXISTS snapshots (
    run_id VARCHAR,
    id VARCHAR,
    state VARCHAR,
    scheduled_start TIMESTAMPTZ,
    started_at TIMESTAMPTZ,
    ended_at TIMESTAMPTZ,
    updated_at TIMESTAMPTZ,
    collected_at TIMESTAMPTZ,
    raw_json VARCHAR,
    PRIMARY KEY (run_id, id)
);
CREATE TABLE IF NOT EXISTS observation_log (
    run_id VARCHAR,
    row_index BIGINT,
    raw_json VARCHAR,
    PRIMARY KEY (run_id, row_index)
);
""")
Enter fullscreen mode Exit fullscreen mode

recorded_at is your ingestion time. It orders saved pulls; it isn't a claim about when the Space changed state.

Keep collection time and event time separate. A timestamp saying when you obtained evidence can't quietly become the timestamp of the event.

Retain the awkward rows too

Empty output fields are omitted. Treat an absent timestamp as unknown, rather than inventing a value or interpreting it as an event being reversed.

The ingestion function preserves raw records, normalises state casing and parses timestamp strings into timezone-aware values. It also collapses identical repeated records; conflicting records sharing an ID go into the observation log instead of forcing an arbitrary winner.

time_fields = {
    "scheduledStart": "scheduled_start",
    "startedAt": "started_at",
    "endedAt": "ended_at",
    "updatedAt": "updated_at",
    "collectedAt": "collected_at",
}

def save_run(actor_run, records, expected_ids):
    run_key = uuid.uuid4().hex
    rows = [
        {
            "run_id": run_key,
            "id": record.get("id"),
            "state": record.get("state"),
            **{
                column: record.get(field)
                for field, column in time_fields.items()
            },
            "raw_json": json.dumps(
                record, ensure_ascii=False, sort_keys=True
            ),
        }
        for record in records
        if record.get("type") == "space"
    ]
    columns = [
        "run_id", "id", "state",
        *time_fields.values(), "raw_json",
    ]
    batch = pd.DataFrame(rows, columns=columns)

    if not batch["id"].map(
        lambda value: isinstance(value, str) and bool(value.strip())
    ).all():
        raise ValueError("Space row has no usable id")

    batch = batch.drop_duplicates(subset=["id", "raw_json"])
    conflicting_ids = set(
        batch.loc[batch["id"].duplicated(keep=False), "id"]
    )
    batch = batch.loc[~batch["id"].isin(conflicting_ids)].copy()

    batch["state"] = (
        batch["state"].astype("string").str.strip().str.lower()
        .replace("", pd.NA)
    )
    for column in time_fields.values():
        batch[column] = pd.to_datetime(
            batch[column], format="ISO8601", utc=True, errors="coerce"
        )

    problems = [
        (
            run_key,
            index,
            json.dumps(record, ensure_ascii=False, sort_keys=True),
        )
        for index, record in enumerate(records)
        if (
            record.get("type") != "space"
            or record.get("id") in conflicting_ids
        )
    ]
    requests = [(run_key, value) for value in sorted(set(expected_ids))]

    con.register("snapshot_batch", batch)
    con.execute("BEGIN TRANSACTION")
    try:
        con.execute(
            "INSERT INTO runs VALUES (?, ?, ?)",
            [run_key, datetime.now(timezone.utc), actor_run["status"]],
        )
        if requests:
            con.executemany("INSERT INTO requested VALUES (?, ?)", requests)
        if problems:
            con.executemany(
                "INSERT INTO observation_log VALUES (?, ?, ?)", problems
            )
        con.execute("INSERT INTO snapshots SELECT * FROM snapshot_batch")
        con.execute("""
            INSERT INTO cohort
            SELECT id FROM snapshot_batch
            ON CONFLICT DO NOTHING
        """)
    except Exception:
        con.rollback()
        raise
    else:
        con.commit()
    finally:
        con.unregister("snapshot_batch")

    return run_key

discovery_run_id = save_run(discovery_run, discovery_items, [])
Enter fullscreen mode Exit fullscreen mode

An unparseable timestamp becomes unknown in the typed columns. Its original value remains available for investigation in raw_json.

Private, deleted or unavailable Spaces return a status row. Preserve those rows separately without assuming they all mean cancellation.

This code also avoids guessing how a problem row maps to an individual requested Space. When the returned identity is insufficient, retain the problem at run level and leave the Space unresolved.

The duplicate policy is deliberately conservative: even a difference confined to collection metadata sends conflicting records for inspection. Choosing whichever row arrived last would quietly turn dataset order into editorial evidence.

Follow the cohort with any

The follow-up asks about saved subjects, rather than recruiting another set of currently scheduled subjects.

This input uses the retained identifiers directly:

saved_ids = [
    row[0] for row in con.execute(
        "SELECT id FROM cohort ORDER BY id"
    ).fetchall()
]

if saved_ids:
    run_input = {
        "spaceUrls": saved_ids,
        "spaceState": "any",
        "includeParticipants": False,
        "includeParticipantProfiles": False,
    }
    follow_run, follow_items = fetch(run_input)
    follow_run_id = save_run(follow_run, follow_items, saved_ids)
Enter fullscreen mode Exit fullscreen mode

Store the requested list before using absence as evidence. It distinguishes “we asked and have no Space snapshot” from “this subject wasn't in the request”.

For a scheduler, keep discovery and cohort follow-up as separate jobs. Failed requests belong in its failure log; an exception must never be replaced with an empty successful response.

Compare observations, not whole records

A whole-row diff will reliably discover that collection time changed. This is accurate and editorially useless.

Compare the fields that answer your reporting question. The query below compares successful observations of each retained identity:

diffs = con.execute("""
WITH paired AS (
    SELECT
        s.*,
        r.recorded_at,
        lag(s.run_id) OVER w AS previous_run_id,
        lag(r.recorded_at) OVER w AS previous_recorded_at,
        lag(s.state) OVER w AS previous_state,
        lag(s.scheduled_start) OVER w AS previous_schedule,
        lag(s.started_at) OVER w AS previous_start,
        lag(s.ended_at) OVER w AS previous_end
    FROM snapshots s
    JOIN runs r USING (run_id)
    WHERE r.actor_status = 'SUCCEEDED'
    WINDOW w AS (
        PARTITION BY s.id ORDER BY r.recorded_at, s.run_id
    )
)
SELECT
    id, previous_run_id, run_id,
    previous_recorded_at, recorded_at,
    previous_state, state,
    previous_schedule, scheduled_start,
    started_at, ended_at,
    previous_state IS NOT NULL AND state IS NOT NULL
        AND state IS DISTINCT FROM previous_state AS state_changed,
    previous_schedule IS NOT NULL AND scheduled_start IS NOT NULL
        AND scheduled_start IS DISTINCT FROM previous_schedule
        AS schedule_changed,
    previous_start IS NULL AND started_at IS NOT NULL
        AS start_newly_available,
    previous_end IS NULL AND ended_at IS NOT NULL
        AS end_newly_available
FROM paired
WHERE previous_run_id IS NOT NULL
""").df()
Enter fullscreen mode Exit fullscreen mode

The flags separate a revised advertised time from newly available evidence of an actual start or end. Those answer different questions.

The schedule comparison requires a usable value on both sides. A schedule field disappearing from a response therefore doesn't become a rescheduling claim.

The query compares consecutive successful Space observations. They may span unresolved pulls. Its interval is an evidence interval, not a guaranteed transition time.

Keep unresolved pulls visible

With Twitter (X) Space Scraper, "any" removes the upcoming-only exclusion. It doesn't guarantee that every requested Space will be available.

Build a coverage ledger alongside the lifecycle comparison:

coverage = con.execute("""
SELECT
    q.run_id,
    q.id,
    r.recorded_at,
    r.actor_status,
    CASE
        WHEN r.actor_status <> 'SUCCEEDED' THEN 'run_incomplete'
        WHEN s.id IS NULL THEN 'unresolved'
        WHEN s.state IS NULL THEN 'state_missing'
        ELSE 'observed'
    END AS observation
FROM requested q
JOIN runs r USING (run_id)
LEFT JOIN snapshots s USING (run_id, id)
ORDER BY r.recorded_at, q.id
""").df()
Enter fullscreen mode Exit fullscreen mode

Partial records from an incomplete run remain stored for inspection, but the lifecycle query excludes them. That's a conservative comparison policy, not a statement that those records are false.

For publication, keep the distinctions explicit:

Evidence Safe comparison Editorial limit
state Normalised value across usable observations A changed state doesn't reconstruct intervening states
scheduledStart Different populated timestamps An advertised schedule change doesn't establish an actual start
startedAt, endedAt Newly available values or later corrections First observed availability isn't the event time
updatedAt, collectedAt Retain for audit and provenance Neither alone establishes a lifecycle transition
listenersLive, listenersReplay Analyse separately as audience measurements Changing counts don't establish cancellation

If an existing start or end timestamp changes, compare the populated values and inspect the raw records before publishing a corrected time. The query's “newly available” flags deliberately don't classify revisions as new events.

Leave room for the unobserved interval

Periodic pulls can miss the live state entirely. A Space observed as upcoming and later ended may have passed through live between collections.

A missing observation also can't distinguish a cancelled event from deleted content, restricted availability or a retrieval problem. Establishing the cause needs evidence beyond that absence.

For a journalist, the useful distinction is between “observed ended”, “reported start time available” and “unresolved”. Each has a different evidential weight.

Your filter can explain a disappearance. It can't announce a cancellation.

Drafted with AI assistance and reviewed for accuracy.

Top comments (0)