Data Analysis & Visualisation

What this covers

The part after collection, where a pile of scraped rows becomes a claim you are willing to publish. Cleaning, relating, sequencing and showing are four separate jobs with four different failure modes, and analysis is where most errors enter an investigation because a chart makes a weak claim look strong. Every step below either loses rows or changes their meaning, so the discipline is to know which, and to write it down.

Method

1. Count the rows you started with, and count them again after every step. A step that drops rows silently is the single most common way a dataset ends up saying the wrong thing. Record the number before and after; if it changed and you did not intend it, stop.
2. Normalise before you deduplicate, deduplicate before you count. Northgate Ltd, Northgate Limited and northgate ltd are three rows and one company. Any count you compute before merging them is wrong, and no later step fixes it.
3. Disable type inference on first contact. Tools guess, and a date guessed month-first turns 1 March into 3 January without a warning. Load everything as text, inspect, then convert deliberately with a format you specified.
4. Decide what the unit of analysis is before you join. One row per post, per account, per account-day? A join that silently fans out one-to-many inflates every count downstream, and the resulting chart looks fine.
5. Keep raw, cleaned and derived as three separate files. The raw file is never edited. The cleaning is a script, not a sequence of clicks you cannot repeat. The derived file is what the chart reads.
6. Mark the gaps. A collection outage renders as a quiet period and reads as an event. If you know when your collection was incomplete, that belongs on the timeline as a shaded band, not as a footnote nobody reads.
7. Choose the figure that makes the weakest honest claim. If the finding is “these two accounts post within sixty seconds of each other, 94 times”, say that. A force-directed graph of the whole dataset with those two nodes somewhere in it is prettier and claims more than you can support.

Key tools

OpenRefine

The cleaning tool to learn properly, because its clustering is the one feature that no CLI replicates well: it proposes groups of values that are probably the same thing and lets you merge each group in one click, with the count of affected rows visible as you decide. That visibility is the point — merging is a judgement, and OpenRefine makes you make it row-count in hand.

Download the current release (3.10.0, February 2026) from openrefine.org/download and run it locally; it serves a browser UI on 127.0.0.1:3333 and your data never leaves the machine.

Clustering lives under a column’s dropdown, Edit cells → Cluster and edit. Work the methods in this order:

key collision / fingerprint          lowercases, strips punctuation and diacritics, sorts
                                     the words. Start here: almost no false positives.
                                     Catches "SMITH, John" = "John Smith".
key collision / ngram-fingerprint    n=2 catches typos and missing spaces that fingerprint
                                     misses. Raise n to loosen, lower it to tighten.
key collision / metaphone3           English phonetics: "Stephen" = "Steven". Use
                                     cologne-phonetic for German, daitch-mokotoff for
                                     Slavic and Yiddish names, beider-morse for a stricter pass.
nearest neighbour / levenshtein      edit distance, with a radius you set. Slow. This is
                                     where real false positives start, so read every group.
nearest neighbour / ppm              compression-based, for long strings. Last resort; it
                                     over-merges short values badly.

GREL, in the Edit cells → Transform box, is for everything clustering cannot do:

value.trim()                                     strip the whitespace that breaks every join
value.replace(/\s+/, " ").trim()                 collapse runs of whitespace to one space
fingerprint(value)                               the clustering key itself, as a new column,
                                                 so you can group on it in SQL later
value.replace(/\b(Ltd|Limited|PLC|LLC|Inc)\b\.?/i, "").trim()
                                                 drop corporate suffixes before matching names
value.toDate("dd/MM/yyyy")                       parse an unambiguous day-first date explicitly
value.toDate(false, "dd/MM/yyyy", "yyyy-MM-dd")  try day-first, then ISO; false = not month-first
value.toDate("dd/MM/yyyy").datePart("hours")     pull a component back out
diff(value.toDate("yyyy-MM-dd"), cells["first_seen"].value.toDate("yyyy-MM-dd"), "days")
                                                 day gap between two columns
value.match(/^(\+?\d{1,3})[\s-]?(\d{6,})$/)[1]   capture group from a regex, as a value
value.splitByCharType().join("|")                expose where letters and digits meet, which
                                                 is how you find "acct12" vs "acct 12"
cross(value, "officers", "company_name")[0].cells["role"].value
                                                 look a value up in another OpenRefine project

Reconciliation is the other thing worth the setup: it matches a column of names against an external authority and returns scored candidates rather than a guess. Add a service under Reconcile → Start reconciling → Add standard service:

https://wikidata.reconci.link/en/api          Wikidata; swap "en" for another language code

That host now 307-redirects to wikidata-reconciliation.wmcloud.org, which is the current canonical home — either URL works, the second avoids the hop. The older wdreconcile.toolforge.org service is deprecated and unreliable; do not point new projects at it.

Read reconciliation output as candidates, never as answers. The service returns a match score and OpenRefine auto-matches above a threshold, which means it will confidently bind a small company to a famous one of a similar name. Set the threshold high, review the auto-matches, and keep the unmatched rows visible rather than dropping them — an unmatched name is information about your authority’s coverage, not about the entity.

csvkit

The right tool between “I have a CSV” and “I have a database”: it does the inspection, cutting, filtering and joining that would otherwise be a throwaway script, and it streams, so it works on files larger than memory. Reach for it before pandas when the job is shaped like a shell pipeline.

pipx install csvkit

# what are the columns actually called, and in what order
csvcut -n collected.csv

# does the file parse at all: ragged rows, empty columns, mismatched header
csvclean -a --label - collected.csv > /dev/null

# a readable look at the first rows, before you decide anything
csvcut -c 1-6 collected.csv | csvlook --max-rows 20

# the whole statistical profile: type, nulls, unique count, min, max, frequencies
csvstat collected.csv

# just the questions that matter on first contact
csvstat --nulls collected.csv
csvstat -c company --freq --freq-count 25 collected.csv

# convert from whatever you were given, naming the sheet explicitly
in2csv -f xlsx --sheet 'Payments' register.xlsx > payments.csv
in2csv -f json -k results api_dump.json > api.csv

# filter by regex, case-insensitively, keeping the header
csvgrep -c company -r '(?i)northgate' collected.csv > northgate.csv

# rows where ANY column matches, for a name you cannot locate in a known column
csvgrep -a -r '(?i)northgate' collected.csv

# join two collections on an id, keeping every left row so you can see the misses
csvjoin -I -c id --left payments.csv officers.csv > merged.csv

# stack files from separate collection runs, tagging each with its source
csvstack -g 2026-02,2026-03 -n batch feb.csv mar.csv > all.csv

# ad-hoc SQL over CSVs without creating a database
csvsql --query "SELECT company, COUNT(*) n, SUM(amount) total
                FROM payments GROUP BY 1 HAVING n > 1 ORDER BY total DESC" payments.csv

Use -I on first contact, every time. csvkit infers types by default, and the inference is month-first: a column containing 01/03/2026 comes out of a plain csvjoin as 2026-01-03, so 1 March silently becomes 3 January, with no warning and no error. -I passes the value through verbatim. If you do want parsing, say what the format is with --date-format '%d/%m/%Y' — and note that a column with genuinely mixed formats will defeat that too, which is a reason to split the column rather than to trust the parser.

Two other limits worth knowing: csvjoin reads every input fully into memory and says so in its own help, so a multi-gigabyte join belongs in SQLite rather than here; and the --blanks flag exists because csvkit converts "", na, n/a, none and . to NULL by default, which is usually right and is occasionally the destruction of a meaningful category.

sqlite-utils

The step that turns a directory of CSVs into something you can actually interrogate. It builds the schema from the data, handles the inserting, and gives you full-text search and foreign keys without writing DDL. Past roughly a hundred thousand rows this is where analysis should live rather than in a dataframe.

pipx install sqlite-utils

# CSV straight into a table, with a primary key so re-runs are idempotent
sqlite-utils insert case.db payments payments.csv --csv --pk id

# everything as TEXT, which is the safe default when the dates are ambiguous
sqlite-utils insert case.db payments_raw payments.csv --csv --no-detect-types

# newline-delimited JSON, e.g. a scraper's output, flattening nested objects
sqlite-utils insert case.db posts posts.jsonl --nl --flatten --pk id

# re-run a collection without duplicating: replace rows that already exist
sqlite-utils insert case.db posts new.jsonl --nl --pk id --replace

# add a column from the second batch without rebuilding the table
sqlite-utils insert case.db posts batch2.jsonl --nl --pk id --alter

# inspect what you built
sqlite-utils schema case.db
sqlite-utils tables case.db --counts

# full-text search across the columns that carry names and free text
sqlite-utils enable-fts case.db payments name company --create-triggers
sqlite-utils search case.db payments 'northgate' -c id -c company

# the normalisation that makes counting honest: one row per distinct company
sqlite-utils extract case.db payments company --table companies --fk-column company_id

# a query, as JSON, straight out of the CLI
sqlite-utils case.db "SELECT company, COUNT(*) n FROM payments GROUP BY 1 ORDER BY n DESC LIMIT 20"

# throwaway query against CSVs with no database at all
sqlite-utils memory payments.csv officers.csv \
  "SELECT p.company, o.role FROM payments p JOIN officers o ON p.id = o.id"

# indexes, once a query starts being slow rather than instant
sqlite-utils create-index case.db payments company date

--pk is the one flag to never omit: without it every re-run of a collection appends duplicates, and you will not notice until a count is wrong. Note that type detection is on by default — the flag is --no-detect-types to turn it off, and there is no --detect-types. extract is the underused one: it pulls a repeated text column into its own table with a foreign key, which is both the normalisation step and a cheap way to see how many distinct entities you really have.

What it does not do: validate anything. sqlite-utils will happily build a table where amount is TEXT because one row contained n/a, and every SUM over it then returns zero without complaining. Check the schema after every insert.

Datasette

Publishing a dataset other people can query, without building an application. Point it at the SQLite file and you get faceted browsing, arbitrary SQL in the URL, CSV and JSON export, and a permalink per row — which means a colleague can cite a specific record and you can both see the query that produced a number.

pipx install datasette

# serve locally, read-only, and open a browser
datasette serve -i case.db -o

# bind somewhere a colleague on the network can reach, with a port you chose
datasette serve -i case.db -h 0.0.0.0 -p 8080

# several databases at once, with cross-database joins enabled
datasette serve -i case.db -i reference.db --crossdb

# source, licence and column descriptions, which is the difference between a
# dataset and a pile of rows
datasette serve -i case.db -m metadata.yml

# ad-hoc: query CSVs in memory without building a database first
datasette --memory

# a scripted query against the JSON API, for a figure you will regenerate
datasette serve -i case.db --get '/case.json?sql=select+company,count(*)+from+payments+group+by+1'

# publish to a container host
datasette publish cloudrun case.db --service case-2026-014
datasette publish heroku case.db --name case-2026-014

-i opens the file immutable, which is what you want for published evidence: nothing served can alter the database, and Datasette can cache aggressively because the contents cannot change.

Note that datasette publish ships only cloudrun and heroku targets. Vercel, Fly and Cloud Run with custom settings are plugins (datasette-publish-vercel, datasette-publish-fly) that must be installed first — a datasette publish vercel invocation fails with an unknown-command error on a bare install. Current stable is the 0.6x line; the 1.0 alphas change the plugin and metadata APIs, so pin your version if you are publishing something you need to rebuild identically later.

The caution with Datasette is social rather than technical: a published database is far more exposing than a chart. Every row is readable, every join is possible, and arbitrary SQL means anybody can compute something you did not anticipate. Before publishing, drop the columns you only needed for cleaning, and remember that a source_url column can re-identify people a redacted name column was meant to protect.

pandas, for timelines

Sequence is the most load-bearing and most abused structure in open-source work: who posted first, how fast something propagated, whether an account’s activity pattern changed. Build it in pandas because the operations you need — timezone conversion, resampling, gap measurement — are one line each and are exactly the ones a spreadsheet gets wrong.

pipx install pandas

import pandas as pd

df = pd.read_csv("collected.csv", dtype=str)          # everything as text; convert deliberately

# parse explicitly, and keep the failures visible rather than dropping them
df["ts"] = pd.to_datetime(df["ts"], errors="coerce", utc=True, format="mixed")
bad = df["ts"].isna()
print(f"{bad.sum()} of {len(df)} timestamps unparseable")
df.loc[bad, ["id", "ts"]].to_csv("unparsed_timestamps.csv", index=False)
df = df.dropna(subset=["ts"]).sort_values("ts")

# volume per day, with empty days present as zero rather than missing
print(df.set_index("ts").resample("1D").size())

# posting hours in the timezone the actor plausibly lives in, not in UTC
local = df["ts"].dt.tz_convert("Europe/Kyiv")
print(local.dt.hour.value_counts().sort_index())

# activity heatmap: hour of day by actor, which is how a shift pattern shows up
print(df.groupby([local.dt.hour, "actor"]).size().unstack(fill_value=0))

# gaps between consecutive events, in seconds — the coordination signal
df["gap_s"] = df["ts"].diff().dt.total_seconds()
print(df.nsmallest(20, "gap_s").loc[:, ["ts", "actor", "gap_s"]])

# near-simultaneous posts by different actors, which is the claim worth making
cols = ["ts", "actor", "id"]
pairs = pd.merge_asof(
    df.loc[:, cols].rename(columns={"actor": "a", "id": "id_a"}),
    df.loc[:, cols].rename(columns={"actor": "b", "id": "id_b"}),
    on="ts", tolerance=pd.Timedelta("60s"), direction="nearest", allow_exact_matches=False,
)
print(pairs.query("a != b").groupby(["a", "b"]).size().sort_values(ascending=False).head(20))

# known collection gaps, recorded as data so they reach the chart
gaps = pd.DataFrame({"start": pd.to_datetime(["2026-03-05T00:00Z"]),
                     "end":   pd.to_datetime(["2026-03-07T12:00Z"])})
gaps.to_csv("collection_gaps.csv", index=False)

df.to_csv("timeline.csv", index=False)                # the derived file the chart reads

format="mixed" is not optional. Without it, pandas infers a single format from the first value and silently coerces every differently-formatted-but-perfectly-valid value to NaT: a column holding 2026-03-01T08:14:00Z and 2026-03-01 09:02 loses the second row entirely under errors="coerce". That is real data loss that reports as a clean parse, which is why the row count and the unparsed_timestamps.csv dump above exist.

Two more traps. tz_convert on a naive column raises; tz_localize first if the source had no offset, and if you do not know the source timezone, say so rather than assuming UTC. And a resample over a sparse series fabricates the zeros it displays, which is correct for volume and wrong for anything you then average.

networkx

Build and measure the graph in code, then hand it to Gephi to look at. Doing the metrics here rather than in the GUI is what makes them reproducible: the numbers come out of a script you can re-run and diff, instead of a sequence of panel clicks nobody recorded.

pipx install networkx

import networkx as nx
import pandas as pd

edges = pd.read_csv("edges.csv")        # source, target, weight, kind
G = nx.from_pandas_edgelist(edges, "source", "target",
                            edge_attr=["weight", "kind"], create_using=nx.DiGraph)
print(G.number_of_nodes(), "nodes,", G.number_of_edges(), "edges")

# who is most connected, in and out separately — they mean different things
print(sorted(G.in_degree(weight="weight"), key=lambda x: -x[1])[:10])
print(sorted(G.out_degree(weight="weight"), key=lambda x: -x[1])[:10])

# who bridges otherwise separate clusters: usually the actual finding
btw = nx.betweenness_centrality(G, normalized=True)
print(sorted(btw.items(), key=lambda x: -x[1])[:10])

# communities, computed on the undirected view as modularity requires
communities = nx.community.greedy_modularity_communities(G.to_undirected())
print([len(c) for c in communities])

# is the graph one thing or several disconnected pieces
print([len(c) for c in nx.weakly_connected_components(G)])

# the ownership or reply chain between two specific entities
print(nx.shortest_path(G, "acct_a", "acct_z"))

# a subgraph two hops out from one node, which is what you actually draw
ego = nx.ego_graph(G, "acct_a", radius=2)
print(ego.number_of_nodes(), "nodes in the two-hop neighbourhood")

# write metrics back onto the nodes, then export for Gephi
nx.set_node_attributes(G, btw, "betweenness")
nx.set_node_attributes(G, dict(G.degree()), "degree")
nx.write_gexf(G, "case.gexf")

Compute the metrics on the graph you can defend, not the one you collected. Betweenness on a graph built from “both accounts used the same hashtag” measures hashtag popularity and nothing else; betweenness on a graph of declared company directorships measures something real. The edge definition is the entire analysis, and it belongs in the caption.

betweenness_centrality is roughly O(nodes × edges) and becomes impractical in the tens of thousands of nodes — pass k=500 to sample pivots and accept an approximation. Note that greedy_modularity_communities needs the undirected view, and that community detection is stochastic in most implementations: run it twice, and if the communities differ materially, the structure is not there.

Gephi

The place to look at the graph, after the metrics are computed. Current line is 0.11, free and open source, and it reads the .gexf written above with node attributes intact.

1. File → Open → case.gexf. Check the import report for discarded edges.
2. Data Laboratory tab first, not Overview. Confirm the node and edge counts
   match what networkx printed. A mismatch means parallel edges were merged.
3. Appearance → Nodes → Size → Ranking → betweenness (the attribute you wrote
   from networkx). Size by the metric; never size by eye.
4. Appearance → Nodes → Colour → Partition → modularity_class if you computed
   it in Gephi, or your own community column from networkx.
5. Layout → ForceAtlas2. Enable "Prevent Overlap" and "LinLog mode" only once
   it has settled. Stop it; do not let it run to a shape you like.
6. Filters → Topology → Degree Range to drop the degree-1 fringe, which is
   usually most of the nodes and none of the finding.
7. Preview tab for the export. Turn node labels on only for the nodes you will
   name in the text.

The honest use of Gephi is to communicate a structure you already established numerically. A force-directed layout is not a measurement: the distance between two nodes on screen has no units, clusters appear in random data, and re-running the layout moves everything. If the figure is doing the arguing, the figure is overclaiming — put the degree and betweenness numbers in the caption and let the picture be an illustration.

Datawrapper

Publishing a chart that survives being read on a phone by someone who does not trust you. It handles the things hand-rolled charts get wrong — colour-blind-safe palettes, responsive layout, accessible axis labelling, a visible source line — and the output is an embed plus a static fallback. Free tier covers everything below; the paid tiers add custom themes and private workspaces.

1. Upload: paste the derived CSV (timeline.csv, not collected.csv) into the
   Upload Data step. Check the "Check & Describe" screen — it shows the parsed
   type per column, and this is where a month-first date misread surfaces
   before it reaches the chart.
2. Visualize → chart type. Lines for a rate over time, bars for counts by
   category, a symbol map only if position is the finding.
3. Refine → Appearance: leave the default palette. Its colours are chosen for
   colour-vision deficiency and for greyscale printing.
4. Refine → Axes: a bar chart's value axis starts at zero. Non-zero baselines
   belong only on line charts, and then with the baseline labelled.
5. Annotate → Title, description, source and byline. The source field is not
   optional: name the dataset, the date collected, and the number of records.
6. Annotate → highlight the specific data points you discuss in the text, and
   add a shaded range for every known collection gap.
7. Publish & Embed → take both the responsive iframe and the static PNG. The
   PNG is what survives in a PDF, an email and an archive of your own article.

The failure mode is not the tool, it is reaching for a chart too early. A figure built from four observations is a figure about four observations, and “50% increase” on a base of four is noise rendered at 300 dpi. State the denominator in the subtitle, every time.

Maps and spatial data

Anything where position is the finding belongs in QGIS and GDAL, which are covered properly on Maps & Satellite Imagery — including qgis_process for scripted geoprocessing and the gdal_translate / gdalwarp workflow. Use that sheet rather than a second copy here. The one rule that belongs on this page: a coordinate column is not spatial data until you know its CRS, and measuring distances in degrees because the layer is in EPSG:4326 is the standard way to publish a wrong number.

For document sets rather than coordinates, Pinpoint does OCR, transcription and entity extraction across thousands of scanned pages, and is free for journalists and researchers on application. It is the fastest route from a PDF dump to a searchable corpus, and its entity extraction is a lead generator, not a finding.

Tool reference

Pitfalls

• Cleaning changes findings. Every merge and exclusion is a judgement. Write down what you did; an undocumented cleaning step is an unreproducible result.
• Correlation in a graph is not connection. Two accounts posting the same link are not necessarily related.
• Missing data looks like absence. A collection gap renders as a quiet period. Mark known gaps on any timeline.
• Pretty visualisations oversell weak data. The more convincing the figure, the more carefully the caveats need stating.
• Small numbers do not support percentages. “50% increase” on a base of four is noise.
• Type inference is a silent rewrite. csvkit reads 01/03/2026 month-first and emits 2026-01-03; pandas without format="mixed" coerces every value that does not match the first row’s format to NaT. Load as text, convert deliberately, and print the row count either side.
• A null is not a zero. Columns with missing values make COUNT(*) and COUNT(col) disagree, and a mean computed over the non-nulls reported against the full row count understates by exactly the share that was missing.

Worked example

One datum: collected.csv, 12,400 rows scraped from a set of accounts, with columns id,ts,actor,company,amount,url. The numbers below are invented; the order of the steps, and what each one reveals or destroys, is not.

# 1. before anything: does it parse, and how many rows are there really
wc -l collected.csv
csvcut -n collected.csv
csvclean -a --label - collected.csv > /dev/null
# 12401 collected.csv
# 1: id  2: ts  3: actor  4: company  5: amount  6: url
# 14 rows were longer than the header row

Fourteen ragged rows. Those are almost always unescaped commas inside a free-text field, which means the data in them is shifted one column right — amount holding a URL fragment. Fix or exclude them now, because every count below would otherwise be wrong by fourteen in an unknown direction.

# 2. the profile, with inference OFF so nothing is reinterpreted on the way in
csvstat --nulls collected.csv
csvstat -c company --freq --freq-count 15 collected.csv
# amount: True   (1,902 nulls)
# { "Northgate Ltd": 412, "Northgate Limited": 198, "northgate ltd": 61,
#   "NORTHGATE LTD.": 44, "Westvale PLC": 390, ... }

Four spellings of one company, 715 rows between them. Counted raw, Northgate is the fourth-largest counterparty; counted merged, it is the largest. That single merge changes the headline, which is exactly why clustering comes before counting.

# 3. OpenRefine: cluster the company column
Edit cells → Cluster and edit → key collision / fingerprint
  → 1 cluster, 4 values, 715 rows → merge to "Northgate Ltd"
Then: key collision / ngram-fingerprint (n=2)
  → 1 further cluster: "Westvale PLC" + "Westvale P.L.C" (390 + 7 rows)
Then: nearest neighbour / levenshtein, radius 2
  → proposes "Northgate Ltd" + "Northgate Mining Ltd" — REJECT.
    Different companies. Export the cleaning operations as JSON so the
    merge is reproducible and the rejection is on the record.

The levenshtein rejection is the part worth recording. An automatic merge at that radius would have folded two real companies into one and inflated the headline figure by another 130 rows, and nothing downstream would have shown it.

# 4. into SQLite, as text first, so the ambiguous dates survive the trip
sqlite-utils insert case.db payments cleaned.csv --csv --pk id --no-detect-types
sqlite-utils schema case.db
sqlite-utils case.db "SELECT company, COUNT(*) n, COUNT(amount) with_amount
                      FROM payments GROUP BY 1 ORDER BY n DESC LIMIT 5"
# [{"company": "Northgate Ltd", "n": 715, "with_amount": 601},
#  {"company": "Westvale PLC", "n": 397, "with_amount": 397}, ...]

114 Northgate rows have no amount. If you had summed without checking, the mean would have been computed over 601 rows and reported over 715 — a 19% understatement presented as a fact.

# 5. the timeline, with the parse failures written out rather than dropped
import pandas as pd
df = pd.read_csv("cleaned.csv", dtype=str)
df["ts"] = pd.to_datetime(df["ts"], errors="coerce", utc=True, format="mixed")
print(df["ts"].isna().sum(), "of", len(df), "unparseable")       # 38 of 12386
df.loc[df["ts"].isna(), ["id", "ts", "url"]].to_csv("unparsed_timestamps.csv", index=False)
df = df.dropna(subset=["ts"]).sort_values("ts")
print(df.set_index("ts").resample("1D").size().describe())
# the 38 all carry a trailing " (edited)" in the ts field — a scraper bug, recoverable

Thirty-eight rows lost to a scraper artefact, now visible in a file with their URLs, so they can be re-collected rather than quietly vanishing. Had format="mixed" been omitted, the count would have been in the thousands and would have looked equally clean.

# 6. the actual claim: who posts within a minute of whom
cols = ["ts", "actor", "id"]
pairs = pd.merge_asof(
    df.loc[:, cols].rename(columns={"actor": "a", "id": "id_a"}),
    df.loc[:, cols].rename(columns={"actor": "b", "id": "id_b"}),
    on="ts", tolerance=pd.Timedelta("60s"), direction="nearest", allow_exact_matches=False,
)
print(pairs.query("a != b").groupby(["a", "b"]).size().sort_values(ascending=False).head(10))
# acct_f  acct_k    94
# acct_k  acct_f    91
# acct_f  acct_m    12

acct_f and acct_k land within sixty seconds of each other 94 times. That is the finding, and it is a sentence with a number in it. The graph comes next only to show where those two sit, not to establish that they are linked.

# 7. the graph, with metrics computed before anything is drawn
import networkx as nx
edges = pairs.query("a != b").groupby(["a", "b"]).size().reset_index(name="weight")
G = nx.from_pandas_edgelist(edges, "a", "b", edge_attr="weight", create_using=nx.DiGraph)
btw = nx.betweenness_centrality(G, normalized=True)
print(sorted(btw.items(), key=lambda x: -x[1])[:5])
nx.set_node_attributes(G, btw, "betweenness")
nx.write_gexf(G, "case.gexf")
# acct_f 0.31, acct_k 0.28, acct_m 0.04, ...

# 8. publish the queryable dataset and the figure
datasette serve -i case.db -m metadata.yml -o

Then case.gexf into Gephi, sized by the betweenness attribute already on the nodes, degree-1 fringe filtered out, labels on for acct_f and acct_k only. And the daily volume series into Datawrapper, with the two-day collection gap on 5–7 March drawn as a shaded band and the source line reading “12,386 posts collected 2026-02-01 to 2026-03-31; 38 timestamps unparsed; four company name variants merged”.

What you can assert: a corrected counterparty ranking, a reproducible cleaning record including one rejected merge, and a quantified timing relationship between two accounts. What it does not establish: that acct_f and acct_k are operated by the same person, or coordinated at all — posting within a minute is consistent with coordination, with both reacting to the same trigger, and with a scheduler neither of them controls. The chart cannot distinguish those, and saying so is the finding’s credibility.

What would falsify it: rerunning the saved cleaning steps changes the row count, the sixty-second pairing disappears when collection gaps and duplicate posts are removed, or the relationship is fully explained by a shared external event. The timing threshold is the sensitive measurement; publish the result at several thresholds rather than treating sixty seconds as a natural law.

Broader catalogues

• Data Extraction OSINT
• Language Translation OSINT

More tools

Further tools for this area from the OSINT Newsletter Tools Library (Data Extraction OSINT), excluding those already listed above.

Sources

Both catalogues below are maintained by other people and are considerably larger than this page. Use them as the canonical index; this sheet is a working route through them.

• Bellingcat’s Online Investigation Toolkit — ~340 tools, each with its own review page covering cost, difficulty, requirements and limitations.
• OSINT Newsletter Tools Library — ~280 tools, organised by investigative goal.

Neither publishes a licence, so nothing here is copied from them: tool names, one-line descriptions, cost flags and links are catalogue facts, and the method and commentary are this site’s own. See credits.