About this article
This article was created using an automated generation workflow powered by generative AI. Referring to Python's official csv module specifications, we organized the procedure for identifying column count mismatches in sample CSV data.

Verification Status: 📘 Official specification checked, implementation execution not verified.

What if columns are misaligned in Python CSV? Detecting extra and missing columns in DictReader

Loading a CSV as a dictionary allows you to retrieve values by names like product code and quantity without remembering column indices. However, what happens if an extra comma sneaks into a file received from a vendor? Even if it seems to load normally, the quantity might end up in a different column. What is even more troublesome is when the loading process proceeds without raising an exception, and the inconsistency is only discovered in a downstream process.

Python'scsv.DictReaderdoes not necessarily error out on rows where the column count does not match the header.Where do extra and missing values go?It has a built-in mechanism for handling them. This time, without using real customer data, we will reproduce this entirely within strings and review how to isolate invalid rows immediately after reading. The result examples are projected based on the official specification, and actual code execution has not been performed here.

Target Environment and Goal of This Article

First, let us clarify what a "CSV column" is. Assuming the first row iscode,qty, the data row requires two fields. However,A01,3,urgenthas three, andB02has only one. The important point here is not simply counting how many commas exist. Quoted"東京,本社"are treated as a single field even if they contain a comma. If you inspect CSV using only string splitting, valid rows will be misjudged.

Initial Experiment: Do Extra Columns Disappear?

The following code usesio.StringIOto treat strings like a file. It does not read or write actual files. First, we intentionally mix extra and missing columns.

import csv
import io

sample = "code,qty\nA01,3,urgent\nB02\nC03,5\n"
reader = csv.DictReader(io.StringIO(sample))
for line_number, row in enumerate(reader, start=2):
    print(line_number, row)

Based on the expected results according to the official specification, the extraA01in the second row'surgentis stored in a list corresponding to the keyNonerather than being a regular column name. The third row'sB02has itsqtyvalue set toNone. The fourth row'sC03normally corresponds to two columns. In other words, excess values are not silently discarded, but exceptions are not automatically raised either.

Without knowing this differencerow["qty"]and using only this, you may overlook the fact that extra columns exist. Conversely, if you assume that the missing column'sNoneis the same as an empty string, you will not be able to distinguish whether the column itself was missing in the input or if a blank was specified. In your initial observation, look not only at the values, but also at the dictionary'sset of keys and value types.

Why None Appears in Dictionaries

DictReadermaps header names to the values of each row. When there is no name to hold an extra value,restkey, and when there are insufficient values,restvalis used. Both default toNone, but their roles are completely different. The former is a key that acts as a container for extra values, while the latter is a value used to fill in missing fields.

The following diagram compares the number of fields in a single row against the headers and illustrates only where they are stored. It is not a diagram explaining the CSV string parsing itself.

flowchart TD
    A["見出しの列数とデータ行の列数を比較"] --> B{"列数は一致?"}
    B -->|一致| C["各見出し名へ値を対応"]
    B -->|データが多い| D["余剰値をrestkeyのリストへ"]
    B -->|データが少ない| E["不足箇所へrestvalを設定"]

The two branches on the right side of the diagram represent the trap here. Because both fit the dictionary format, you cannot simply conclude that "a dictionary was created, so it is normal." Due to CSV specifications, a blank may sometimes be valid data. The detection of column count mismatches and the validation of business rules regarding whether a value is allowed to be blank must be considered separately.

1. Changing a Condition: Adding Detection-Specific Markers

Instead of using the defaultNone, you can specify a dedicated key for extra columns and a dedicated value for missing columns. This makes it easier for humans to read what happened.

import csv
import io

sample = "code,qty\nA01,3,urgent\nB02\nC03,5\n"
reader = csv.DictReader(
    io.StringIO(sample),
    restkey="__EXTRA__",
    restval="__MISSING__"
)
for line_number, row in enumerate(reader, start=2):
    problems = []
    if "__EXTRA__" in row:
        problems.append("余分な列")
    if "__MISSING__" in row.values():
        problems.append("不足した列")
    print(line_number, problems or ["列数は一致"])

The expected outcomes are "extra column" for the second row, "missing column" for the third row, and "column counts match" for the fourth row. Changing the configuration does not repair the data itself; it simply makes input issues easier to find by adding markers. This leads to a design where only rows that pass inspection are forwarded to subsequent processing.

However, this sample uses strings as markers for illustrative purposes. If actual data could contain__MISSING__as a legitimate value, judgment based solely on the value will conflict. Furthermore, confusion will also occur if the header itself contains__EXTRA__. For robust handling in production, you should choose to use a dedicated unique object for missing values, or inspect the column count withcsv.readerbefore converting to a dictionary.

Practical approach: Count the columns first, then convert to a dictionary

If you want to reliably validate only for missing or extra columns,csv.readerreading the header and each row as arrays and comparing their lengths is a straightforward approach. You should also separately check for duplicate header names. Below is a minimal example using safe dummy data that excludes problematic rows.

import csv
import io

sample = "code,qty\nA01,3,urgent\nB02\nC03,5\n"
reader = csv.reader(io.StringIO(sample, newline=""))
header = next(reader)
if not header or len(set(header)) != len(header):
    raise ValueError("見出しが空、または重複しています")

accepted = []
rejected = []
for record_number, fields in enumerate(reader, start=2):
    if len(fields) != len(header):
        rejected.append((record_number, len(fields)))
        continue
    accepted.append(dict(zip(header, fields)))

print("採用:", accepted)
print("保留:", rejected)

The expected result based on the specification is thatC03only the row is accepted, while the second and third rows are held. Althoughzipis used here, lengths are compared before conversion, so excess values are never silently truncated. Duplicate headers are also caught beforehand. In actual files, records may contain line breaks, so the numbers displayed here representthe order of CSV recordsand do not necessarily match the physical file line numbers. If the actual read position is required, also checkreader.line_num.

Boundary condition: Matching column counts does not guarantee correctness

Here is an unexpected comparison.D04,A row like still has two fields even if the second value is empty. Therefore, it passes the column count validation. On the other hand,D04fails because it contains only one field. "Quantity is blank" and "Quantity column does not exist" are distinct issues. If quantity is mandatory, you must verify that after the column count checkqtyis not an empty string.

Another consideration is quotes."E,05",7contains a comma within the product code, but it constitutes two fields in CSV format. A string-basedsplit(",")would incorrectly appear as three. This is precisely why column counts should also rely on the CSV parser's output. If CSV delimiter and quotation rules vary by partner, also consider specifyingdelimiterorquotechar. Differences in character encoding can also cause issues during the read phase before CSV parsing.

Blank lines, empty header strings, duplicate headers, and non-numeric values in quantity fields are also separate validation targets. For example,qtybutthreeHowever, the number of columns still matches. It is important not to use the column count check to mean that CSV quality assurance is complete. Also, when opening actual files, follow the official Python documentationnewline=""and specifyencodingas needed.

Which one should you use in your work?

For simple exploratory tasks or when you want to handle data in dictionaries while recording surplus values,DictReadertherestkeyandrestvalof are convenient. On the other hand, for imports where column misalignment cannot be tolerated, such as order or inventory data, it is easier to make a decision by validating the column count with and isolating mismatched rows before converting them to dictionaries. The former retains information during loading, while the latter rejects invalid rows.csv.readerThe former retains information at read time, while the latter rejects invalid rows.

When integrating into operations, we recommend maintaining the original pre-import files, logging the number and reasons for pending records, and preventing processing from proceeding to subsequent stages when anomalies are detected. Automatically correcting problematic rows and treating them as valid data can lead to erroneous registration of quantities or amounts. First, verify expected results with a small read-only sample, and only apply the process to actual data after confirming character encodings, column names, blank handling rules, and exception handling.

References

• Official Python Documentation: csv — CSV File Reading and Writing

• Official Python Documentation: io — Core tools for working with streams