Cleaning Reddit comment data without flattening the thread

A comment that reads "that fixed it" is worthless on its own. Its meaning lives entirely in the comment above it, and the moment you flatten a thread into a table of one comment per row, that meaning is gone. The sentiment classifier will still return positive with high confidence. It just will not be able to tell you what got fixed.

Reddit's JSON gives you everything you need to avoid that. The problem is that three of its conventions look like noise on first read, so they tend to get normalized away by the loader before anyone notices they were load-bearing.

Fullnames encode the type, and the prefix is the useful half

Every object carries a fullname: the type followed by an underscore and a base-36 id. PRAW's glossary lists the mapping, of which two prefixes matter for comment data: t1 is a comment and t3 is a submission.

This is what makes parent_id more than an opaque string. A comment whose parent_id starts with t3_ is a top-level reply to the post. One starting with t1_ is a reply to another comment. Strip the prefix during loading, as a schema expecting a plain foreign key will, and you lose the ability to tell a root comment from a nested one without a second pass.

PREFIX_TO_KIND = {"t1": "comment", "t3": "submission"}


def split_fullname(fullname: str) -> tuple[str, str]:
    """'t1_abc123' -> ('comment', 'abc123')"""
    prefix, sep, base36 = fullname.partition("_")
    if not sep or not base36:
        raise ValueError(f"not a fullname: {fullname!r}")
    return PREFIX_TO_KIND.get(prefix, prefix), base36


def resolve_parents(rows: list[dict]) -> list[dict]:
    """Annotate each row with its parent's kind, id, and whether we hold it."""
    present = {row["record_id"] for row in rows}

    for row in rows:
        kind, parent_id = split_fullname(row["parent_fullname"])
        row["parent_kind"] = kind
        row["parent_id"] = parent_id
        row["is_top_level"] = kind == "submission"
        row["parent_resolved"] = row["is_top_level"] or parent_id in present

    return rows

parent_resolved is the field that earns its place. A reply whose parent is missing from the export is not a corrupt row, it is a visible edge of your sample, and marking it is far more useful than dropping it. Delete those rows and the gap in the collection disappears along with them.

[deleted] is a string, not a null

When a comment is gone, the body field does not become null. It becomes the literal string [deleted] or [removed], and the two mean different things: the first is usually the author, the second usually a moderator or automated action. Separately, author can read [deleted] while the body text is still intact, which is an account removal rather than a comment removal.

Load that into a text column and it survives as content. Somewhere downstream, a word-frequency count reports deleted as a recurring theme.

TOMBSTONES = {"[deleted]": "deleted", "[removed]": "removed"}


def normalize_text(body: str | None, author: str | None) -> dict:
    if body is None:
        return {"body_clean": None, "text_status": "missing", "author_status": "unknown"}

    author_status = "deleted" if (author or "").strip() == "[deleted]" else "present"
    marker = TOMBSTONES.get(body.strip())
    if marker:
        return {"body_clean": None, "text_status": marker, "author_status": author_status}

    return {"body_clean": body, "text_status": "available", "author_status": author_status}

Three states rather than two is deliberate. missing means the field never arrived, which is a collection problem on your side; deleted and removed mean the content was gone before you asked, which is a fact about the thread. Collapsing them into one null loses the distinction that tells you whether to re-collect.

Keep body_raw beside body_clean as well. Every transformation you apply, from unescaping HTML entities to stripping markdown, is a guess that someone will eventually want to check.

created_utc is epoch seconds, and it is not when you collected it

created_utc is a Unix timestamp in seconds, and it answers a different question from "when did I see this". A score of 340 is not a property of a post, it is an observation of a post at a moment. Store both times or the difference becomes unrecoverable.

from datetime import datetime, timezone


def to_utc_iso(epoch_seconds: float | int | None) -> str | None:
    if epoch_seconds is None:
        return None
    return datetime.fromtimestamp(float(epoch_seconds), tz=timezone.utc).isoformat()

Use fromtimestamp with an explicit tz, not utcfromtimestamp, which returns a naive datetime and is deprecated from Python 3.12. A naive UTC datetime compared against an aware one raises; compared against another naive local one, it silently gives you a wrong answer.

The rule that matters more than either: never substitute the current time for a missing timestamp. An unknown creation date recorded as "now" turns an old record into a new one, and nothing downstream can detect it.

Field	Holds	Fails when
created_at	Posting time, from created_utc	Defaulted to collection time
collected_at	When your client saw the record	Omitted, making scores undatable
record_id	Base-36 id, prefix stripped	Used as a key across object types
parent_fullname	Original t1_/t3_ string	Normalized to a bare id
text_status	available, deleted, removed, missing	Collapsed into a null body

The rate limit shapes your sample, so write it down

Reddit's Data API documents a free-tier limit of 100 queries per minute per OAuth client id, and 10 QPM for clients that do not authenticate. That is a per-client limit rather than a per-user one, which means your collection window, not your loop, is what decides how much of a thread you actually hold.

This is a data quality concern rather than an operational one. A sample bounded by a rate limit is still a bounded sample, and if the boundary is not recorded next to the dataset, the first chart anyone builds from it will be presented as a fact about a subreddit rather than a fact about 40 minutes of collection.

Record the scope as data, not as a note in a README:

manifest = {
    "collected_at": datetime.now(timezone.utc).isoformat(),
    "subreddits": ["example"],
    "listing": "new",
    "cutoff_utc": to_utc_iso(cutoff_epoch),
    "records": len(rows),
    "unresolved_parents": sum(1 for r in rows if not r["parent_resolved"]),
    "tombstoned": sum(1 for r in rows if r["text_status"] in {"deleted", "removed"}),
}

Those last two counts are the ones worth reading before anything else. A sudden jump in either usually means the collection changed, not that the conversation did.

Where the rows come from

The cleaning layer above does not care which client produced them, only that the fields survive the trip. There are three usual routes: the Data API directly, PRAW if you would rather not hand-roll pagination and rate-limit backoff, or a third-party export when it is a one-off and registering an app is more setup than the task deserves. AllyHub is one of the exports I have used for that last case.

Two things hold regardless of the route. Check the actual field names against your schema before mapping, because exporters rename things and parent_id is a common casualty. And the terms attached to wherever you got the data are the terms that apply to it: a comment being publicly readable is not the same as it being yours to redistribute, which is worth settling before a dataset leaves your machine rather than after.

An acceptance check before the dataset moves on

Assert the invariants rather than eyeballing the file. These run in under a second on a normal export and catch the loader regressions that are otherwise invisible until a chart looks odd:

def check(rows: list[dict]) -> list[str]:
    problems = []

    if any(r.get("record_id") is None for r in rows):
        problems.append("rows without a record_id")

    ids = [r["record_id"] for r in rows]
    if len(ids) != len(set(ids)):
        problems.append(f"{len(ids) - len(set(ids))} duplicate record_ids")

    if any(r.get("parent_fullname", "").count("_") != 1 for r in rows):
        problems.append("parent_fullname not in prefix_id form")

    if any(r["text_status"] == "available" and not r["body_clean"] for r in rows):
        problems.append("status says available but body is empty")

    if any(r.get("created_at") == r.get("collected_at") for r in rows):
        problems.append("created_at equals collected_at, check for a defaulted time")

    return problems

The last assertion is the one that has caught the most for me. created_at == collected_at across a whole file almost always means a missing timestamp was filled in with now() somewhere in the loader.

Questions worth settling before the next import

Should usernames be dropped during cleaning?

Keep the identifying fields the task needs and no more. A theme analysis usually runs fine on record ids and parent links alone. If you do drop usernames, treat it as a documented transformation rather than anonymization, since a distinctive comment body can still identify its author.

Should short replies be filtered out?

Not at the cleaning stage. "That worked" is low-information as a standalone string and high-information as a reply, so filtering it out during normalization throws away the confirmation signal along with the noise. Filter at the analysis stage instead, where the parent is still attached and the decision is reversible.

The check that catches the most is also the least technical: reconstruct three or four threads from the cleaned file and read them as conversations. If a thread reads differently than it does on the site, the bug is in a transformation, and it is much cheaper to find there than in the themes built on top of it.