First-N Distinct Values Per Column

Sample the first N distinct decoded data values per column in encounter order, counting empty strings as values and reporting the full distinct count.

Description

Distinct-value sampling summarizes a column’s vocabulary in encounter order.

Distinct-value sampling summarizes a column’s vocabulary in encounter order. Empty strings can be informative and are included under this rule.^[1]

Sample the first N distinct decoded data values per column in encounter order, counting empty strings as values and reporting the full distinct count.

When to use First-N Distinct Values Per Column

Use this for the following task: Sample the first N distinct decoded data values per column in encounter order, counting empty strings as values and reporting the full distinct count.

Prepare the CSV text, decoded cells or byte values requested below. Choose delimiter and header flags explicitly when those fields are present.

How to use First-N Distinct Values Per Column and understand its results

Prepare the CSV text, decoded cells or byte values requested below. Choose delimiter and header flags explicitly when those fields are present.
Enter csv, delimiter, hasHeader, count using the field table, then run the tool.
Read columns. Interpret flags, units, ordering and placeholder values using the rules below.
Review any reported issue or failed check, correct the supplied values when appropriate, and verify the result in its intended context.

Input field	Meaning	Value type and bounds	Requirement
csv	CSV text	string; maximum text length 100000	Required
delimiter	Delimiter	string; choose ",", ";", "\t"	Required
hasHeader	Has header	boolean	Required
count	Count	integer; minimum 0; maximum 10000	Required

Parse rectangular records and exclude an explicit header. Build each column’s full distinct set in encounter order, return the first count values and full distinctCount.^[2]

Parse delimiters only outside quoted fields and decode doubled quotes. A quoted line break stays inside its cell; malformed quoting is returned as an input error.

values = first N distinct decoded data strings; truncated = distinctCount>N

This summarizes the declared operation. Units, indexing, inclusivity and exceptional cases are explained in the surrounding text.

Output field	Meaning	Value type
columns	Columns	list of record: column (integer), distinctCount (integer), values (list of string), truncated (boolean)

json

{
  "input": {
    "csv": "color\nred\nblue\nred\ngreen",
    "delimiter": ",",
    "hasHeader": true,
    "count": 2
  },
  "output": {
    "columns": [
      {
        "column": 0,
        "distinctCount": 3,
        "values": [
          "red",
          "blue"
        ],
        "truncated": true
      }
    ]
  }
}

Limitations and assumptions

Count zero returns no samples while still reporting distinct count. No random sampling or trimming is performed.

Where CSV is parsed, the shared parser accepts comma, semicolon or tab separators, doubled quotes and quoted line breaks, with CR/LF/CRLF record endings. Blank records remain records. CSV inputs are bounded to 100000 UTF-16 units and 10000 fields. Malformed requests produce input errors. A successfully evaluated request can still contain false flags, empty result lists or a result outside the supplied threshold; those outcomes have the meanings stated above.

Alternative or Complementary methods

Choose Low-Cardinality Category Lister for this operation: List first-encounter distinct nonempty values for data columns whose observed category count is at most an explicit maximum, excluding an explicit header. For a complementary task, use High-Cardinality Column Flagger for this operation: Flag columns whose distinct-to-observed ratio exceeds an explicit threshold, counting exact nonempty data cells and excluding an explicit header; zero observations never flag.

Further background: Format and language reference: First-N Distinct Values Per Column.

References

Format and language reference: First-N Distinct Values Per Column — RFC Editor
First-N Distinct Values Per Column — tool.io

Similar or alternative tools

Low-Cardinality Category Lister
List first-encounter distinct nonempty values for data columns whose observed category count is at most an explicit maximum, excluding an explicit header.
High-Cardinality Column Flagger
Flag columns whose distinct-to-observed ratio exceeds an explicit threshold, counting exact nonempty data cells and excluding an explicit header; zero observations never flag.