How Can a Shell Pipeline Count Missing CSV Fields?
From the earlier reference collection. Read the current software blog.
Use a CSV-aware parser to count empty fields accurately, since splitting lines on commas breaks quoted values.
Why commas are not enough
A CSV row is not safely parsed by splitting at every comma. A field may contain a comma inside quotes, as in 17,"Market Street, East",active. A quoted field may also contain a quote represented by a doubled quote. A shell pipeline based on cut -d, or awk -F, will count the example as four fields even though it has three. It may then report false missing values or overlook real ones.
Line breaks can also occur inside quoted fields in some CSV files, so treating each physical line as one record is not generally reliable. A parser that implements CSV quoting rules should identify logical records and fields before counting empties.
A small Python count
For a quick count, Python’s standard csv module can read the file while respecting standard CSV quoting. This shell invocation prints the total number of empty cells across the file, including headers only if the file has a header row and you leave it in the input: python -c 'import csv,sys; rows=csv.reader(sys.stdin); print(sum(field == "" for row in rows for field in row))' < data.csv. The shell directs the file to the program’s standard input; the parser handles the record structure.
To report counts by column, first read the header and then count each row’s fields by position. That requires a policy for rows with too few or too many fields: flag them as malformed rather than silently treating absent trailing columns as empty cells. An explicitly empty field, such as the middle value in a,,c, differs from a column that is missing because the row ended early.
Define what counts as missing
Empty text is not always the only missing marker. A dataset may use NA, null, or a whitespace-only string. Decide which representations count before writing the count, and avoid treating the literal word 0 or false as missing unless the data contract says so. Trimming whitespace changes the meaning of values such as a space-containing identifier, so make that choice visible in the code.
For example, to count only truly empty parsed strings, test field == "". To count whitespace-only fields as well, test field.strip() == "". These produce different results for a cell containing three spaces.
Make the output useful
A single total can reveal a problem, but column-level counts help locate it. Include the input filename and report the number of records examined, malformed rows, and empty cells per column. If a file is too large for memory, iterate through the parser’s rows and update counters incrementally; do not build a complete list of records just to compute totals.
For a pipeline that will be reused, put the parser in a small script with a documented missing-value policy and an exit status for malformed input. The shell remains useful for directing files and combining commands, while CSV parsing stays with a component that understands the format.