# Count and weight terms per document

Model / text

A variant of [Split text into words](https://meridian.online/operations/split-text-into-words).

Build a document-term table of counts or TF-IDF weights from tokenised text.

Page: <https://meridian.online/operations/split-text-into-words/weight-terms-per-document>

## Status

Runs as a SQL step.

arcform carries it as a `sql:` step today.

## The DuckDB step

count and weight terms per document with TF-IDF

```sql
CREATE TEMP TABLE docs AS SELECT * FROM (VALUES (1, 'the cat sat on the mat'), (2, 'the dog sat on the log'), (3, 'the cat chased the dog')) t(doc, body);
CREATE TEMP TABLE tokens AS SELECT doc, unnest(string_split(lower(body), ' ')) AS term FROM docs;
-- counts: one row per document and term
CREATE TEMP TABLE counts AS SELECT doc, term, count(*) AS n FROM tokens GROUP BY doc, term;
-- TF-IDF: the term's share of its document times ln(documents / documents holding the term)
CREATE TEMP TABLE weights AS
SELECT c.doc, c.term, c.n,
       c.n::DOUBLE / sum(c.n) OVER (PARTITION BY c.doc) * ln((SELECT count(*) FROM docs)::DOUBLE / count(*) OVER (PARTITION BY c.term)) AS tfidf
FROM counts c;
SELECT * FROM weights ORDER BY doc, term;
-- the document-term table in wide form: one row per document and one column per term
CREATE TEMP TABLE wide AS PIVOT (SELECT doc, term, n FROM counts) ON term USING sum(n) GROUP BY doc ORDER BY doc;
-- 'the' is in every document, so its weight is 0; 'chased' is only in document 3 and weighs most there
SELECT CASE WHEN ((SELECT count(*) = 14 FROM weights)
  AND (SELECT list(n ORDER BY term) = [1, 1, 1, 1, 2] AND list(term ORDER BY term) = ['cat', 'mat', 'on', 'sat', 'the'] FROM weights WHERE doc = 1)
  AND (SELECT max(tfidf) = 0 FROM weights WHERE term = 'the')
  AND (SELECT abs(tfidf - 0.067577518) < 1e-9 FROM weights WHERE doc = 1 AND term = 'cat')
  AND (SELECT abs(tfidf - 0.183102048) < 1e-9 FROM weights WHERE doc = 1 AND term = 'mat')
  AND (SELECT abs(tfidf - 0.081093022) < 1e-9 FROM weights WHERE doc = 3 AND term = 'cat')
  AND (SELECT abs(tfidf - 0.219722458) < 1e-9 FROM weights WHERE doc = 3 AND term = 'chased')
  AND (SELECT arg_max(term, tfidf) = 'chased' FROM weights WHERE doc = 3)
  AND (SELECT count(*) = 3 FROM wide) AND (SELECT count(*) = 9 FROM (DESCRIBE wide))
  AND (SELECT "the" = 2 AND "chased" = 1 AND "cat" = 1 AND "mat" IS NULL FROM wide WHERE doc = 3)) THEN 'as expected' ELSE error('the three documents should give 14 term counts, a TF-IDF of 0 for the in every document and a weight of 0.2197 for chased in document 3') END AS verdict;
```

The statement builds its own data.

It needs nothing installed beyond DuckDB itself.

## What other tools call it

Meridian's classification; an independent blind rating agreed on 95.7% of 324 rows (variant, version 3).

- RapidMiner Studio: [`Generate TFIDF`](https://docs.rapidminer.com/latest/studio/operators/blending/attributes/generation/generate_tfidf.html) (read 2026-09-27)
- RapidMiner Studio: [`Process Documents`](https://docs.rapidminer.com/latest/studio/operators/extensions/Text%20Processing/process_documents.html) (read 2026-09-27)
- RapidMiner Studio: [`Process Documents from Data`](https://docs.rapidminer.com/latest/studio/operators/extensions/Text%20Processing/process_document_from_data.html) (read 2026-09-27)
- RapidMiner Studio: [`Process Documents from Files`](https://docs.rapidminer.com/latest/studio/operators/extensions/Text%20Processing/process_document_from_file.html) (read 2026-09-27)
- RapidMiner Studio: [`Process Documents from Mail Store`](https://docs.rapidminer.com/latest/studio/operators/extensions/Text%20Processing/process_mail_documents.html) (read 2026-09-27)
- RapidMiner Studio: [`Text Vectorization`](https://docs.rapidminer.com/latest/studio/operators/blending/attributes/generation/text_vectorization.html) (read 2026-09-27)
- RapidMiner Studio: [`WordList to Data`](https://docs.rapidminer.com/latest/studio/operators/extensions/Text%20Processing/utility/wordlist_to_data.html) (read 2026-09-27)