Cleaning a Twitter Style Archive File That Is Not Quite JSON

Social platforms let you download an archive of your own data, and the format is almost always a surprise. This one is nearly JSON. Each file is a JavaScript assignment, the numbers are strings, the dates are in a format from a different decade, and the text is HTML-escaped. None of it is hard, but each piece blocks a naive json.load, and each is a way to produce a wrong number without any error.

I made an archive in that style: two part files, 30,300 tweet records, with the quirks I have seen in such exports. The data is synthetic, generated for this article, and the quirks are the point.

It is not JSON

raw = open("tweets.js").read()
json.loads(raw)
# JSONDecodeError: Expecting value: line 1 column 1 (char 0)

The first line is:

window.YTD.tweets.part0 = [
  {
    "tweet": {

The file is a script meant to be loaded by a web page, which assigns the array to a global. The data after the equals sign is valid JSON. Everything before it is not. Cut at the first =:

body = raw[raw.index("=") + 1:]
data = json.loads(body)
len(data)          # 20000
list(data[0])      # ['tweet']

20,000 records, each wrapped in a single-key object {"tweet": ...}, which is also a quirk of the format. Cutting at the first = is safe only because the prefix has no earlier =; if the name could contain one, use a regular expression anchored at the start (re.sub(r"^\s*window\.YTD\.\w+\.part\d+\s*=\s*", "", raw)). Do not eval it or run it in a JavaScript engine to read data you got from a download (why you should not eval JSON explains the problem with executing a data file).

Join the parts

Large archives are split into parts, with the part number in the variable name. Read all of them:

rows = []
for p in sorted(glob.glob("tweets*.js")):
    s = open(p).read()
    rows += [r["tweet"] for r in json.loads(s[s.index("=") + 1:])]
len(rows)          # 30300

30,300 records. A glob that sorts by name puts part10 before part2, which matters when order is meaningful, so sort by the number if it is. The unwrapping, r["tweet"], removes the extra level.

Duplicates

The count of distinct ids was 30,000:

df = pd.DataFrame(rows)
df.duplicated("id").sum()      # 300
df = df.drop_duplicates("id").copy()

300 records appeared twice, which happens when an export is stitched from overlapping windows or retried. Drop by the id, and decide which copy to keep if they differ. If the counts (likes, retweets) are the field that differs, keep the later export, since the numbers grow.

Strings that should be numbers

Every number in the file is a string:

{c: df[c].dtype for c in ["id", "favorite_count", "retweet_count", "created_at"]}
# all str

Convert the counts to integers, and the id to a 64-bit integer. Never let the id pass through a float: it is 19 digits, bigger than 2^53, and the conversion is wrong in the last digits:

int(float(df["id"].iloc[1])) == df["id"].iloc[1]     # False
df["id"] = df["id"].astype("int64")                  # parse the string directly

This is the failure that makes two exports "not match" for no visible reason. If the id is only ever used as a key and never calculated with, keep it as a string, and avoid the question (JSON security mistakes in apps and pipelines lists how often a 16 digit number gets rounded). The archive duplicates the id as id_str, which is a hint from the platform's earlier days, when JSON clients in JavaScript could not hold the number.

The date format

"created_at": "Sat May 01 12:00:00 +0000 2021"

This is an old RFC 822 style date with the year at the end. pd.to_datetime can guess, but a format string removes the guess and the warning:

df["created_at"] = pd.to_datetime(df["created_at"], format="%a %b %d %H:%M:%S %z %Y", utc=True)
# Timestamp('2021-05-01 12:00:00+0000', tz='UTC')

The result is a timezone-aware column. %a and %b are English names for the day and month, and they depend on the locale in some environments, so a machine set to another language can fail. Pass an explicit locale-independent parse if you run it on such a machine. Check the range afterwards: the minimum and maximum should match what you know about the account.

HTML in the text

df["text"] = df["full_text"].map(html.unescape)
# "Hello &amp; welcome &lt;3"  ->  "Hello & welcome <3"

The archive stores text HTML-escaped, so an ampersand is & and a less-than is <. Left alone, a word count or a search for & misses them. Unescape once, and remember that the result is plain text. If you put it into a web page, escape it again on output.

Fields like the client (source) are HTML fragments too:

df["client"] = df["source"].str.extract(r">([^<]+)<")
# Twitter Web App 15155, Twitter for iPhone 14845

A regular expression is enough for a single known anchor tag; for anything more varied, use a real HTML parser. Entities are another nested structure. Hashtags, mentions and URLs sit in lists, and the numbers inside them (the indices of each) are strings too. The shortened URLs in the text have an expanded form in the entities.urls list:

df["entities"].map(lambda e: [u["expanded_url"] for u in e["urls"]]).explode().dropna()
# https://example.com/a?b=1&c=2

Replace the shortened links in the text with the expanded ones if you want to analyze domains.

Missing keys are the norm

A reply has in_reply_to_status_id_str, and a tweet that is not a reply has no such key at all (not null). df["in_reply_to_status_id_str"].notna() gave 6,057 replies out of 30,000, because a missing key becomes a missing value when the records are assembled into a frame. In a loop over dictionaries, use .get(). A field that is sometimes a string and sometimes absent is common in archives, so reading with d["key"] fails at the first record without it.

Write something better than the source

df[["id","created_at","text","favorite_count","retweet_count","lang","client","is_reply"]].to_parquet("tweets.parquet")

The Parquet file was 547,228 bytes against 18,146,417 bytes of JavaScript, a 33 times reduction. The columns have types, so a query for tweets per month needs no parsing. The whole pass, from reading both files to writing Parquet, took 0.54 s. Keep the original archive untouched next to it, and write the cleaning as a script so it runs again on the next download (loading a JSON file into a pandas DataFrame has the options when the file is plain JSON). The entities and other nested lists can go in their own tables.

For the general toolkit, the pandas documentation for to_datetime lists the format codes, and the Python html.unescape is in the standard library. If you need to inspect one record, the JSON formatter is faster than printing it.

When the archive should stay as it is

You will not analyze it. If the archive is a backup, store it as downloaded. A cleaned copy is a derived file, and the original is the one you can prove.

Deleted or private content is in it. Archives contain things you may not want to copy into a shared dataset. Check what you are about to move before you move it.

The format will change. Platforms change their export format. A script that fits today's archive will break, so treat the cleaning code as a script with tests and a sample file, not as a one-time command.

Someone else's data. An archive of your own posts is yours. Scraped or exported data from others carries terms and privacy obligations that no amount of cleaning removes.