data-analysis-and-visualisation.md (36458B)
1 --- 2 title: "Data Analysis & Visualisation" 3 description: "Clean messy collected data, map relationships between entities, build timelines, and publish a figure people can read." 4 category: osint 5 subcategory: "Archiving & Analysis" 6 tags: [osint, analysis, visualisation, graphs, timelines] 7 tools: [openrefine, csvkit, sqlite-utils, datasette, pandas, networkx, gephi, datawrapper, pinpoint] 8 difficulty: intermediate 9 updated: 2026-10-04 10 references: 11 - name: "Bellingcat's Online Investigation Toolkit" 12 url: "https://bellingcat.gitbook.io/toolkit" 13 author: "Bellingcat" 14 license: none 15 relation: derived 16 note: "Tool catalogue: names, descriptions, cost flags and links for this area." 17 - name: "OSINT Newsletter Tools Library" 18 url: "https://tools.osintnewsletter.com" 19 author: "The OSINT Newsletter" 20 license: none 21 relation: derived 22 note: "Second tool catalogue, cross-checked against the above." 23 --- 24 25 ## What this covers 26 27 The part after collection, where a pile of scraped rows becomes a claim you are willing to publish. 28 Cleaning, relating, sequencing and showing are four separate jobs with four different failure 29 modes, and analysis is where most errors enter an investigation because a chart makes a weak claim 30 look strong. Every step below either loses rows or changes their meaning, so the discipline is to 31 know which, and to write it down. 32 33 ## Method 34 35 1. **Count the rows you started with, and count them again after every step.** A step that drops 36 rows silently is the single most common way a dataset ends up saying the wrong thing. Record the 37 number before and after; if it changed and you did not intend it, stop. 38 2. **Normalise before you deduplicate, deduplicate before you count.** `Northgate Ltd`, 39 `Northgate Limited` and `northgate ltd` are three rows and one company. Any count you compute 40 before merging them is wrong, and no later step fixes it. 41 3. **Disable type inference on first contact.** Tools guess, and a date guessed month-first turns 42 1 March into 3 January without a warning. Load everything as text, inspect, then convert 43 deliberately with a format you specified. 44 4. **Decide what the unit of analysis is before you join.** One row per post, per account, per 45 account-day? A join that silently fans out one-to-many inflates every count downstream, and the 46 resulting chart looks fine. 47 5. **Keep raw, cleaned and derived as three separate files.** The raw file is never edited. The 48 cleaning is a script, not a sequence of clicks you cannot repeat. The derived file is what the 49 chart reads. 50 6. **Mark the gaps.** A collection outage renders as a quiet period and reads as an event. If you 51 know when your collection was incomplete, that belongs on the timeline as a shaded band, not as 52 a footnote nobody reads. 53 7. **Choose the figure that makes the weakest honest claim.** If the finding is "these two accounts 54 post within sixty seconds of each other, 94 times", say that. A force-directed graph of the whole 55 dataset with those two nodes somewhere in it is prettier and claims more than you can support. 56 57 ## Key tools 58 59 ### OpenRefine 60 61 The cleaning tool to learn properly, because its clustering is the one feature that no CLI 62 replicates well: it proposes groups of values that are probably the same thing and lets you merge 63 each group in one click, with the count of affected rows visible as you decide. That visibility is 64 the point — merging is a judgement, and OpenRefine makes you make it row-count in hand. 65 66 Download the current release (3.10.0, February 2026) from 67 [openrefine.org/download](https://openrefine.org/download) and run it locally; it serves a browser 68 UI on `127.0.0.1:3333` and your data never leaves the machine. 69 70 Clustering lives under a column's dropdown, `Edit cells` → `Cluster and edit`. Work the methods in 71 this order: 72 73 ```text 74 key collision / fingerprint lowercases, strips punctuation and diacritics, sorts 75 the words. Start here: almost no false positives. 76 Catches "SMITH, John" = "John Smith". 77 key collision / ngram-fingerprint n=2 catches typos and missing spaces that fingerprint 78 misses. Raise n to loosen, lower it to tighten. 79 key collision / metaphone3 English phonetics: "Stephen" = "Steven". Use 80 cologne-phonetic for German, daitch-mokotoff for 81 Slavic and Yiddish names, beider-morse for a stricter pass. 82 nearest neighbour / levenshtein edit distance, with a radius you set. Slow. This is 83 where real false positives start, so read every group. 84 nearest neighbour / ppm compression-based, for long strings. Last resort; it 85 over-merges short values badly. 86 ``` 87 88 GREL, in the `Edit cells` → `Transform` box, is for everything clustering cannot do: 89 90 ```text 91 value.trim() strip the whitespace that breaks every join 92 value.replace(/\s+/, " ").trim() collapse runs of whitespace to one space 93 fingerprint(value) the clustering key itself, as a new column, 94 so you can group on it in SQL later 95 value.replace(/\b(Ltd|Limited|PLC|LLC|Inc)\b\.?/i, "").trim() 96 drop corporate suffixes before matching names 97 value.toDate("dd/MM/yyyy") parse an unambiguous day-first date explicitly 98 value.toDate(false, "dd/MM/yyyy", "yyyy-MM-dd") try day-first, then ISO; false = not month-first 99 value.toDate("dd/MM/yyyy").datePart("hours") pull a component back out 100 diff(value.toDate("yyyy-MM-dd"), cells["first_seen"].value.toDate("yyyy-MM-dd"), "days") 101 day gap between two columns 102 value.match(/^(\+?\d{1,3})[\s-]?(\d{6,})$/)[1] capture group from a regex, as a value 103 value.splitByCharType().join("|") expose where letters and digits meet, which 104 is how you find "acct12" vs "acct 12" 105 cross(value, "officers", "company_name")[0].cells["role"].value 106 look a value up in another OpenRefine project 107 ``` 108 109 Reconciliation is the other thing worth the setup: it matches a column of names against an external 110 authority and returns scored candidates rather than a guess. Add a service under 111 `Reconcile` → `Start reconciling` → `Add standard service`: 112 113 ```text 114 https://wikidata.reconci.link/en/api Wikidata; swap "en" for another language code 115 ``` 116 117 That host now 307-redirects to `wikidata-reconciliation.wmcloud.org`, which is the current canonical 118 home — either URL works, the second avoids the hop. The older 119 `wdreconcile.toolforge.org` service is deprecated and unreliable; do not point new projects at it. 120 121 Read reconciliation output as candidates, never as answers. The service returns a match score and 122 OpenRefine auto-matches above a threshold, which means it will confidently bind a small company to 123 a famous one of a similar name. Set the threshold high, review the auto-matches, and keep the 124 unmatched rows visible rather than dropping them — an unmatched name is information about your 125 authority's coverage, not about the entity. 126 127 ### csvkit 128 129 The right tool between "I have a CSV" and "I have a database": it does the inspection, cutting, 130 filtering and joining that would otherwise be a throwaway script, and it streams, so it works on 131 files larger than memory. Reach for it before pandas when the job is shaped like a shell pipeline. 132 133 ```bash 134 pipx install csvkit 135 ``` 136 137 ```bash 138 # what are the columns actually called, and in what order 139 csvcut -n collected.csv 140 141 # does the file parse at all: ragged rows, empty columns, mismatched header 142 csvclean -a --label - collected.csv > /dev/null 143 144 # a readable look at the first rows, before you decide anything 145 csvcut -c 1-6 collected.csv | csvlook --max-rows 20 146 147 # the whole statistical profile: type, nulls, unique count, min, max, frequencies 148 csvstat collected.csv 149 150 # just the questions that matter on first contact 151 csvstat --nulls collected.csv 152 csvstat -c company --freq --freq-count 25 collected.csv 153 154 # convert from whatever you were given, naming the sheet explicitly 155 in2csv -f xlsx --sheet 'Payments' register.xlsx > payments.csv 156 in2csv -f json -k results api_dump.json > api.csv 157 158 # filter by regex, case-insensitively, keeping the header 159 csvgrep -c company -r '(?i)northgate' collected.csv > northgate.csv 160 161 # rows where ANY column matches, for a name you cannot locate in a known column 162 csvgrep -a -r '(?i)northgate' collected.csv 163 164 # join two collections on an id, keeping every left row so you can see the misses 165 csvjoin -I -c id --left payments.csv officers.csv > merged.csv 166 167 # stack files from separate collection runs, tagging each with its source 168 csvstack -g 2026-02,2026-03 -n batch feb.csv mar.csv > all.csv 169 170 # ad-hoc SQL over CSVs without creating a database 171 csvsql --query "SELECT company, COUNT(*) n, SUM(amount) total 172 FROM payments GROUP BY 1 HAVING n > 1 ORDER BY total DESC" payments.csv 173 ``` 174 175 **Use `-I` on first contact, every time.** csvkit infers types by default, and the inference is 176 month-first: a column containing `01/03/2026` comes out of a plain `csvjoin` as `2026-01-03`, so 177 1 March silently becomes 3 January, with no warning and no error. `-I` passes the value through 178 verbatim. If you do want parsing, say what the format is with `--date-format '%d/%m/%Y'` — and note 179 that a column with genuinely mixed formats will defeat that too, which is a reason to split the 180 column rather than to trust the parser. 181 182 Two other limits worth knowing: `csvjoin` reads every input fully into memory and says so in its 183 own help, so a multi-gigabyte join belongs in SQLite rather than here; and the `--blanks` flag 184 exists because csvkit converts `""`, `na`, `n/a`, `none` and `.` to NULL by default, which is 185 usually right and is occasionally the destruction of a meaningful category. 186 187 ### sqlite-utils 188 189 The step that turns a directory of CSVs into something you can actually interrogate. It builds the 190 schema from the data, handles the inserting, and gives you full-text search and foreign keys 191 without writing DDL. Past roughly a hundred thousand rows this is where analysis should live rather 192 than in a dataframe. 193 194 ```bash 195 pipx install sqlite-utils 196 ``` 197 198 ```bash 199 # CSV straight into a table, with a primary key so re-runs are idempotent 200 sqlite-utils insert case.db payments payments.csv --csv --pk id 201 202 # everything as TEXT, which is the safe default when the dates are ambiguous 203 sqlite-utils insert case.db payments_raw payments.csv --csv --no-detect-types 204 205 # newline-delimited JSON, e.g. a scraper's output, flattening nested objects 206 sqlite-utils insert case.db posts posts.jsonl --nl --flatten --pk id 207 208 # re-run a collection without duplicating: replace rows that already exist 209 sqlite-utils insert case.db posts new.jsonl --nl --pk id --replace 210 211 # add a column from the second batch without rebuilding the table 212 sqlite-utils insert case.db posts batch2.jsonl --nl --pk id --alter 213 214 # inspect what you built 215 sqlite-utils schema case.db 216 sqlite-utils tables case.db --counts 217 218 # full-text search across the columns that carry names and free text 219 sqlite-utils enable-fts case.db payments name company --create-triggers 220 sqlite-utils search case.db payments 'northgate' -c id -c company 221 222 # the normalisation that makes counting honest: one row per distinct company 223 sqlite-utils extract case.db payments company --table companies --fk-column company_id 224 225 # a query, as JSON, straight out of the CLI 226 sqlite-utils case.db "SELECT company, COUNT(*) n FROM payments GROUP BY 1 ORDER BY n DESC LIMIT 20" 227 228 # throwaway query against CSVs with no database at all 229 sqlite-utils memory payments.csv officers.csv \ 230 "SELECT p.company, o.role FROM payments p JOIN officers o ON p.id = o.id" 231 232 # indexes, once a query starts being slow rather than instant 233 sqlite-utils create-index case.db payments company date 234 ``` 235 236 `--pk` is the one flag to never omit: without it every re-run of a collection appends duplicates, 237 and you will not notice until a count is wrong. Note that type detection is on by default — the 238 flag is `--no-detect-types` to turn it off, and there is no `--detect-types`. `extract` is the 239 underused one: it pulls a repeated text column into its own table with a foreign key, which is both 240 the normalisation step and a cheap way to see how many distinct entities you really have. 241 242 What it does not do: validate anything. `sqlite-utils` will happily build a table where `amount` is 243 `TEXT` because one row contained `n/a`, and every `SUM` over it then returns zero without 244 complaining. Check the schema after every insert. 245 246 ### Datasette 247 248 Publishing a dataset other people can query, without building an application. Point it at the 249 SQLite file and you get faceted browsing, arbitrary SQL in the URL, CSV and JSON export, and a 250 permalink per row — which means a colleague can cite a specific record and you can both see the 251 query that produced a number. 252 253 ```bash 254 pipx install datasette 255 ``` 256 257 ```bash 258 # serve locally, read-only, and open a browser 259 datasette serve -i case.db -o 260 261 # bind somewhere a colleague on the network can reach, with a port you chose 262 datasette serve -i case.db -h 0.0.0.0 -p 8080 263 264 # several databases at once, with cross-database joins enabled 265 datasette serve -i case.db -i reference.db --crossdb 266 267 # source, licence and column descriptions, which is the difference between a 268 # dataset and a pile of rows 269 datasette serve -i case.db -m metadata.yml 270 271 # ad-hoc: query CSVs in memory without building a database first 272 datasette --memory 273 274 # a scripted query against the JSON API, for a figure you will regenerate 275 datasette serve -i case.db --get '/case.json?sql=select+company,count(*)+from+payments+group+by+1' 276 277 # publish to a container host 278 datasette publish cloudrun case.db --service case-2026-014 279 datasette publish heroku case.db --name case-2026-014 280 ``` 281 282 `-i` opens the file immutable, which is what you want for published evidence: nothing served can 283 alter the database, and Datasette can cache aggressively because the contents cannot change. 284 285 Note that `datasette publish` ships only `cloudrun` and `heroku` targets. Vercel, Fly and Cloud Run 286 with custom settings are plugins (`datasette-publish-vercel`, `datasette-publish-fly`) that must be 287 installed first — a `datasette publish vercel` invocation fails with an unknown-command error on a 288 bare install. Current stable is the 0.6x line; the 1.0 alphas change the plugin and metadata APIs, 289 so pin your version if you are publishing something you need to rebuild identically later. 290 291 The caution with Datasette is social rather than technical: a published database is far more 292 exposing than a chart. Every row is readable, every join is possible, and arbitrary SQL means 293 anybody can compute something you did not anticipate. Before publishing, drop the columns you only 294 needed for cleaning, and remember that a `source_url` column can re-identify people a redacted name 295 column was meant to protect. 296 297 ### pandas, for timelines 298 299 Sequence is the most load-bearing and most abused structure in open-source work: who posted first, 300 how fast something propagated, whether an account's activity pattern changed. Build it in pandas 301 because the operations you need — timezone conversion, resampling, gap measurement — are one line 302 each and are exactly the ones a spreadsheet gets wrong. 303 304 ```bash 305 pipx install pandas 306 ``` 307 308 ```python 309 import pandas as pd 310 311 df = pd.read_csv("collected.csv", dtype=str) # everything as text; convert deliberately 312 313 # parse explicitly, and keep the failures visible rather than dropping them 314 df["ts"] = pd.to_datetime(df["ts"], errors="coerce", utc=True, format="mixed") 315 bad = df["ts"].isna() 316 print(f"{bad.sum()} of {len(df)} timestamps unparseable") 317 df.loc[bad, ["id", "ts"]].to_csv("unparsed_timestamps.csv", index=False) 318 df = df.dropna(subset=["ts"]).sort_values("ts") 319 320 # volume per day, with empty days present as zero rather than missing 321 print(df.set_index("ts").resample("1D").size()) 322 323 # posting hours in the timezone the actor plausibly lives in, not in UTC 324 local = df["ts"].dt.tz_convert("Europe/Kyiv") 325 print(local.dt.hour.value_counts().sort_index()) 326 327 # activity heatmap: hour of day by actor, which is how a shift pattern shows up 328 print(df.groupby([local.dt.hour, "actor"]).size().unstack(fill_value=0)) 329 330 # gaps between consecutive events, in seconds — the coordination signal 331 df["gap_s"] = df["ts"].diff().dt.total_seconds() 332 print(df.nsmallest(20, "gap_s").loc[:, ["ts", "actor", "gap_s"]]) 333 334 # near-simultaneous posts by different actors, which is the claim worth making 335 cols = ["ts", "actor", "id"] 336 pairs = pd.merge_asof( 337 df.loc[:, cols].rename(columns={"actor": "a", "id": "id_a"}), 338 df.loc[:, cols].rename(columns={"actor": "b", "id": "id_b"}), 339 on="ts", tolerance=pd.Timedelta("60s"), direction="nearest", allow_exact_matches=False, 340 ) 341 print(pairs.query("a != b").groupby(["a", "b"]).size().sort_values(ascending=False).head(20)) 342 343 # known collection gaps, recorded as data so they reach the chart 344 gaps = pd.DataFrame({"start": pd.to_datetime(["2026-03-05T00:00Z"]), 345 "end": pd.to_datetime(["2026-03-07T12:00Z"])}) 346 gaps.to_csv("collection_gaps.csv", index=False) 347 348 df.to_csv("timeline.csv", index=False) # the derived file the chart reads 349 ``` 350 351 **`format="mixed"` is not optional.** Without it, pandas infers a single format from the first 352 value and silently coerces every differently-formatted-but-perfectly-valid value to `NaT`: a column 353 holding `2026-03-01T08:14:00Z` and `2026-03-01 09:02` loses the second row entirely under 354 `errors="coerce"`. That is real data loss that reports as a clean parse, which is why the row count 355 and the `unparsed_timestamps.csv` dump above exist. 356 357 Two more traps. `tz_convert` on a naive column raises; `tz_localize` first if the source had no 358 offset, and if you do not know the source timezone, say so rather than assuming UTC. And a 359 `resample` over a sparse series fabricates the zeros it displays, which is correct for volume and 360 wrong for anything you then average. 361 362 ### networkx 363 364 Build and measure the graph in code, then hand it to Gephi to look at. Doing the metrics here 365 rather than in the GUI is what makes them reproducible: the numbers come out of a script you can 366 re-run and diff, instead of a sequence of panel clicks nobody recorded. 367 368 ```bash 369 pipx install networkx 370 ``` 371 372 ```python 373 import networkx as nx 374 import pandas as pd 375 376 edges = pd.read_csv("edges.csv") # source, target, weight, kind 377 G = nx.from_pandas_edgelist(edges, "source", "target", 378 edge_attr=["weight", "kind"], create_using=nx.DiGraph) 379 print(G.number_of_nodes(), "nodes,", G.number_of_edges(), "edges") 380 381 # who is most connected, in and out separately — they mean different things 382 print(sorted(G.in_degree(weight="weight"), key=lambda x: -x[1])[:10]) 383 print(sorted(G.out_degree(weight="weight"), key=lambda x: -x[1])[:10]) 384 385 # who bridges otherwise separate clusters: usually the actual finding 386 btw = nx.betweenness_centrality(G, normalized=True) 387 print(sorted(btw.items(), key=lambda x: -x[1])[:10]) 388 389 # communities, computed on the undirected view as modularity requires 390 communities = nx.community.greedy_modularity_communities(G.to_undirected()) 391 print([len(c) for c in communities]) 392 393 # is the graph one thing or several disconnected pieces 394 print([len(c) for c in nx.weakly_connected_components(G)]) 395 396 # the ownership or reply chain between two specific entities 397 print(nx.shortest_path(G, "acct_a", "acct_z")) 398 399 # a subgraph two hops out from one node, which is what you actually draw 400 ego = nx.ego_graph(G, "acct_a", radius=2) 401 print(ego.number_of_nodes(), "nodes in the two-hop neighbourhood") 402 403 # write metrics back onto the nodes, then export for Gephi 404 nx.set_node_attributes(G, btw, "betweenness") 405 nx.set_node_attributes(G, dict(G.degree()), "degree") 406 nx.write_gexf(G, "case.gexf") 407 ``` 408 409 Compute the metrics on the graph you can defend, not the one you collected. Betweenness on a graph 410 built from "both accounts used the same hashtag" measures hashtag popularity and nothing else; 411 betweenness on a graph of declared company directorships measures something real. The edge 412 definition is the entire analysis, and it belongs in the caption. 413 414 `betweenness_centrality` is roughly O(nodes × edges) and becomes impractical in the tens of 415 thousands of nodes — pass `k=500` to sample pivots and accept an approximation. Note that 416 `greedy_modularity_communities` needs the undirected view, and that community detection is 417 stochastic in most implementations: run it twice, and if the communities differ materially, the 418 structure is not there. 419 420 ### Gephi 421 422 The place to look at the graph, after the metrics are computed. Current line is 0.11, free and 423 open source, and it reads the `.gexf` written above with node attributes intact. 424 425 ```text 426 1. File → Open → case.gexf. Check the import report for discarded edges. 427 2. Data Laboratory tab first, not Overview. Confirm the node and edge counts 428 match what networkx printed. A mismatch means parallel edges were merged. 429 3. Appearance → Nodes → Size → Ranking → betweenness (the attribute you wrote 430 from networkx). Size by the metric; never size by eye. 431 4. Appearance → Nodes → Colour → Partition → modularity_class if you computed 432 it in Gephi, or your own community column from networkx. 433 5. Layout → ForceAtlas2. Enable "Prevent Overlap" and "LinLog mode" only once 434 it has settled. Stop it; do not let it run to a shape you like. 435 6. Filters → Topology → Degree Range to drop the degree-1 fringe, which is 436 usually most of the nodes and none of the finding. 437 7. Preview tab for the export. Turn node labels on only for the nodes you will 438 name in the text. 439 ``` 440 441 The honest use of Gephi is to communicate a structure you already established numerically. A 442 force-directed layout is not a measurement: the distance between two nodes on screen has no units, 443 clusters appear in random data, and re-running the layout moves everything. If the figure is doing 444 the arguing, the figure is overclaiming — put the degree and betweenness numbers in the caption and 445 let the picture be an illustration. 446 447 ### Datawrapper 448 449 Publishing a chart that survives being read on a phone by someone who does not trust you. It 450 handles the things hand-rolled charts get wrong — colour-blind-safe palettes, responsive layout, 451 accessible axis labelling, a visible source line — and the output is an embed plus a static 452 fallback. Free tier covers everything below; the paid tiers add custom themes and private 453 workspaces. 454 455 ```text 456 1. Upload: paste the derived CSV (timeline.csv, not collected.csv) into the 457 Upload Data step. Check the "Check & Describe" screen — it shows the parsed 458 type per column, and this is where a month-first date misread surfaces 459 before it reaches the chart. 460 2. Visualize → chart type. Lines for a rate over time, bars for counts by 461 category, a symbol map only if position is the finding. 462 3. Refine → Appearance: leave the default palette. Its colours are chosen for 463 colour-vision deficiency and for greyscale printing. 464 4. Refine → Axes: a bar chart's value axis starts at zero. Non-zero baselines 465 belong only on line charts, and then with the baseline labelled. 466 5. Annotate → Title, description, source and byline. The source field is not 467 optional: name the dataset, the date collected, and the number of records. 468 6. Annotate → highlight the specific data points you discuss in the text, and 469 add a shaded range for every known collection gap. 470 7. Publish & Embed → take both the responsive iframe and the static PNG. The 471 PNG is what survives in a PDF, an email and an archive of your own article. 472 ``` 473 474 The failure mode is not the tool, it is reaching for a chart too early. A figure built from four 475 observations is a figure about four observations, and "50% increase" on a base of four is noise 476 rendered at 300 dpi. State the denominator in the subtitle, every time. 477 478 ### Maps and spatial data 479 480 Anything where position is the finding belongs in QGIS and GDAL, which are covered properly on 481 [Maps & Satellite Imagery](/sheets/osint/maps-and-satellite-imagery) — including `qgis_process` 482 for scripted geoprocessing and the `gdal_translate` / `gdalwarp` workflow. Use that sheet rather 483 than a second copy here. The one rule that belongs on this page: a coordinate column is not spatial 484 data until you know its CRS, and measuring distances in degrees because the layer is in `EPSG:4326` 485 is the standard way to publish a wrong number. 486 487 For document sets rather than coordinates, 488 [Pinpoint](https://journaliststudio.google.com/pinpoint/) does OCR, transcription and entity 489 extraction across thousands of scanned pages, and is free for journalists and researchers on 490 application. It is the fastest route from a PDF dump to a searchable corpus, and its entity 491 extraction is a lead generator, not a finding. 492 493 ## Tool reference 494 495 | Tool | What it does | Cost | 496 | --- | --- | --- | 497 | [4CAT](https://4cat.nl/) | 4CAT is a tool designed for the easy collection and analysis of online datasets. It allows researchers to uncover patterns and trends in data from social… | free | 498 | [Atlos](https://www.atlos.org/) | ATLOS is a platform for collaborative and large-scale open source investigations. | partly free | 499 | [Blender](https://www.blender.org/) | Blender is an open-source 3D creation suite supporting the 3D pipeline—modeling, rigging, animation, simulation, rendering, compositing, and motion… | free | 500 | [Datasette](https://datasette.io) | Open-source “WordPress-for-data” that turns any SQLite database into an interactive website and JSON API in seconds; ideal for publishing, exploring and… | free | 501 | [Datawrapper](https://www.datawrapper.de/) | A tool for creating interactive charts, maps, and tables from your data, offering a user-friendly interface for visualizing information. | partly free | 502 | [Gephi](https://gephi.org) | Open-source network analysis and visualization software | free | 503 | [Logseq](https://logseq.com/) | Logseq is an open-source knowledge management tool that enables users to organize their notes, tasks, and projects. | free | 504 | [Maltego Graph](https://www.maltego.com/downloads/) | Maltego Graph is an investigation platform that combines two things at once: (1) It acts as a search tool, and (2) It creates a graph establishing links… | partly free | 505 | [Obsidian](https://obsidian.md/) | A knowledge management and note-taking app with extensive customization options. | partly free | 506 | [Pinpoint](https://journaliststudio.google.com/pinpoint/about) | A tool by Google to catalogue uploaded documents and files, providing automated text recogntion, indexing, audiotranscriptions and other (AI-powered)… | free | 507 | [QGIS](https://www.qgis.org) | QGIS is a free Open Source Geographic Information System (GIS). | free | 508 | [RAWGraphs](https://app.rawgraphs.io/) | RAWGraphs is an open-source data visualization tool designed for non-technical users, enabling the creation of customizable, editable charts without… | free | 509 | [Time.Graphics](https://time.graphics) | A tool for creating, visualizing, and managing timelines online. | partly free | 510 511 ## Pitfalls 512 513 - **Cleaning changes findings.** Every merge and exclusion is a judgement. Write down what you did; 514 an undocumented cleaning step is an unreproducible result. 515 - **Correlation in a graph is not connection.** Two accounts posting the same link are not 516 necessarily related. 517 - **Missing data looks like absence.** A collection gap renders as a quiet period. Mark known gaps 518 on any timeline. 519 - **Pretty visualisations oversell weak data.** The more convincing the figure, the more carefully 520 the caveats need stating. 521 - **Small numbers do not support percentages.** "50% increase" on a base of four is noise. 522 - **Type inference is a silent rewrite.** csvkit reads `01/03/2026` month-first and emits 523 `2026-01-03`; pandas without `format="mixed"` coerces every value that does not match the first 524 row's format to `NaT`. Load as text, convert deliberately, and print the row count either side. 525 - **A null is not a zero.** Columns with missing values make `COUNT(*)` and `COUNT(col)` disagree, 526 and a mean computed over the non-nulls reported against the full row count understates by exactly 527 the share that was missing. 528 529 ## Worked example 530 531 One datum: `collected.csv`, 12,400 rows scraped from a set of accounts, with columns 532 `id,ts,actor,company,amount,url`. The numbers below are invented; the order of the steps, and what 533 each one reveals or destroys, is not. 534 535 ```bash 536 # 1. before anything: does it parse, and how many rows are there really 537 wc -l collected.csv 538 csvcut -n collected.csv 539 csvclean -a --label - collected.csv > /dev/null 540 # 12401 collected.csv 541 # 1: id 2: ts 3: actor 4: company 5: amount 6: url 542 # 14 rows were longer than the header row 543 ``` 544 545 Fourteen ragged rows. Those are almost always unescaped commas inside a free-text field, which 546 means the data in them is shifted one column right — `amount` holding a URL fragment. Fix or 547 exclude them now, because every count below would otherwise be wrong by fourteen in an unknown 548 direction. 549 550 ```bash 551 # 2. the profile, with inference OFF so nothing is reinterpreted on the way in 552 csvstat --nulls collected.csv 553 csvstat -c company --freq --freq-count 15 collected.csv 554 # amount: True (1,902 nulls) 555 # { "Northgate Ltd": 412, "Northgate Limited": 198, "northgate ltd": 61, 556 # "NORTHGATE LTD.": 44, "Westvale PLC": 390, ... } 557 ``` 558 559 Four spellings of one company, 715 rows between them. Counted raw, Northgate is the fourth-largest 560 counterparty; counted merged, it is the largest. That single merge changes the headline, which is 561 exactly why clustering comes before counting. 562 563 ```text 564 # 3. OpenRefine: cluster the company column 565 Edit cells → Cluster and edit → key collision / fingerprint 566 → 1 cluster, 4 values, 715 rows → merge to "Northgate Ltd" 567 Then: key collision / ngram-fingerprint (n=2) 568 → 1 further cluster: "Westvale PLC" + "Westvale P.L.C" (390 + 7 rows) 569 Then: nearest neighbour / levenshtein, radius 2 570 → proposes "Northgate Ltd" + "Northgate Mining Ltd" — REJECT. 571 Different companies. Export the cleaning operations as JSON so the 572 merge is reproducible and the rejection is on the record. 573 ``` 574 575 The levenshtein rejection is the part worth recording. An automatic merge at that radius would have 576 folded two real companies into one and inflated the headline figure by another 130 rows, and nothing 577 downstream would have shown it. 578 579 ```bash 580 # 4. into SQLite, as text first, so the ambiguous dates survive the trip 581 sqlite-utils insert case.db payments cleaned.csv --csv --pk id --no-detect-types 582 sqlite-utils schema case.db 583 sqlite-utils case.db "SELECT company, COUNT(*) n, COUNT(amount) with_amount 584 FROM payments GROUP BY 1 ORDER BY n DESC LIMIT 5" 585 # [{"company": "Northgate Ltd", "n": 715, "with_amount": 601}, 586 # {"company": "Westvale PLC", "n": 397, "with_amount": 397}, ...] 587 ``` 588 589 114 Northgate rows have no amount. If you had summed without checking, the mean would have been 590 computed over 601 rows and reported over 715 — a 19% understatement presented as a fact. 591 592 ```python 593 # 5. the timeline, with the parse failures written out rather than dropped 594 import pandas as pd 595 df = pd.read_csv("cleaned.csv", dtype=str) 596 df["ts"] = pd.to_datetime(df["ts"], errors="coerce", utc=True, format="mixed") 597 print(df["ts"].isna().sum(), "of", len(df), "unparseable") # 38 of 12386 598 df.loc[df["ts"].isna(), ["id", "ts", "url"]].to_csv("unparsed_timestamps.csv", index=False) 599 df = df.dropna(subset=["ts"]).sort_values("ts") 600 print(df.set_index("ts").resample("1D").size().describe()) 601 # the 38 all carry a trailing " (edited)" in the ts field — a scraper bug, recoverable 602 ``` 603 604 Thirty-eight rows lost to a scraper artefact, now visible in a file with their URLs, so they can be 605 re-collected rather than quietly vanishing. Had `format="mixed"` been omitted, the count would have 606 been in the thousands and would have looked equally clean. 607 608 ```python 609 # 6. the actual claim: who posts within a minute of whom 610 cols = ["ts", "actor", "id"] 611 pairs = pd.merge_asof( 612 df.loc[:, cols].rename(columns={"actor": "a", "id": "id_a"}), 613 df.loc[:, cols].rename(columns={"actor": "b", "id": "id_b"}), 614 on="ts", tolerance=pd.Timedelta("60s"), direction="nearest", allow_exact_matches=False, 615 ) 616 print(pairs.query("a != b").groupby(["a", "b"]).size().sort_values(ascending=False).head(10)) 617 # acct_f acct_k 94 618 # acct_k acct_f 91 619 # acct_f acct_m 12 620 ``` 621 622 `acct_f` and `acct_k` land within sixty seconds of each other 94 times. That is the finding, and it 623 is a sentence with a number in it. The graph comes next only to show *where* those two sit, not to 624 establish that they are linked. 625 626 ```python 627 # 7. the graph, with metrics computed before anything is drawn 628 import networkx as nx 629 edges = pairs.query("a != b").groupby(["a", "b"]).size().reset_index(name="weight") 630 G = nx.from_pandas_edgelist(edges, "a", "b", edge_attr="weight", create_using=nx.DiGraph) 631 btw = nx.betweenness_centrality(G, normalized=True) 632 print(sorted(btw.items(), key=lambda x: -x[1])[:5]) 633 nx.set_node_attributes(G, btw, "betweenness") 634 nx.write_gexf(G, "case.gexf") 635 # acct_f 0.31, acct_k 0.28, acct_m 0.04, ... 636 ``` 637 638 ```bash 639 # 8. publish the queryable dataset and the figure 640 datasette serve -i case.db -m metadata.yml -o 641 ``` 642 643 Then `case.gexf` into Gephi, sized by the `betweenness` attribute already on the nodes, degree-1 644 fringe filtered out, labels on for `acct_f` and `acct_k` only. And the daily volume series into 645 Datawrapper, with the two-day collection gap on 5–7 March drawn as a shaded band and the source 646 line reading "12,386 posts collected 2026-02-01 to 2026-03-31; 38 timestamps unparsed; four company 647 name variants merged". 648 649 What you can assert: a corrected counterparty ranking, a reproducible cleaning record 650 including one rejected merge, and a quantified timing relationship between two accounts. What it 651 does not establish: that `acct_f` and `acct_k` are operated by the same person, or coordinated at 652 all — posting within a minute is consistent with coordination, with both reacting to the same 653 trigger, and with a scheduler neither of them controls. The chart cannot distinguish those, and 654 saying so is the finding's credibility. 655 656 What would falsify it: rerunning the saved cleaning steps changes the row count, the sixty-second 657 pairing disappears when collection gaps and duplicate posts are removed, or the relationship is 658 fully explained by a shared external event. The timing threshold is the sensitive measurement; 659 publish the result at several thresholds rather than treating sixty seconds as a natural law. 660 661 ## Broader catalogues 662 663 - [Data Extraction OSINT](https://tools.osintnewsletter.com/tool-categories/data-extraction-osint) 664 - [Language Translation OSINT](https://tools.osintnewsletter.com/tool-categories/language-translation-osint) 665 666 667 ## More tools 668 669 Further tools for this area from the OSINT Newsletter Tools Library ([Data Extraction OSINT](https://tools.osintnewsletter.com/tool-categories/data-extraction-osint)), excluding those already listed above. 670 671 | Tool | What it does | 672 | --- | --- | 673 | [Simple Scraper](https://simplescraper.io/) | A web scraping tool that allows users to extract data from websites quickly and easily. | 674 | [Thunderbit](https://thunderbit.com/) | AI-powered web scraper for fast, no-code data extraction. | 675 676 ## Sources 677 678 Both catalogues below are maintained by other people and are considerably larger than 679 this page. Use them as the canonical index; this sheet is a working route through them. 680 681 - [Bellingcat's Online Investigation Toolkit](https://bellingcat.gitbook.io/toolkit) — ~340 tools, each with its own 682 review page covering cost, difficulty, requirements and limitations. 683 - [OSINT Newsletter Tools Library](https://tools.osintnewsletter.com) — ~280 tools, organised by investigative goal. 684 685 Neither publishes a licence, so nothing here is copied from them: tool names, one-line 686 descriptions, cost flags and links are catalogue facts, and the method and commentary are 687 this site's own. See [credits](/credits).