I2 · Working with Data: NumPy, pandas, SQL and Visualisation

Validating a CSV Dataset

Data validation is the automatic checking of a dataset against explicit, written rules about its structure, its values and its records before the data is used. A validator reports every broken rule with its location, so that bad records are corrected or set aside instead of silently changing a result.

25 min Intermediate · Sublevel Beginner Jump to the activity

What you need: The earlier topics of this module.

Definition

A school exports Monday's attendance from a tablet app. The file opens perfectly in a spreadsheet, yet one learner appears twice, one row has no ID and an absent learner has a score. Would your analysis notice before it reports the average?

Data validation is the automatic checking of a dataset against explicit, written rules about its structure, its values and its records before the data is used. A validator reports every broken rule with its location, so that bad records are corrected or set aside instead of silently changing a result.

In simple words

Think of the security check at a railway station. Every bag goes through the same scanner with the same rules, and a suspicious bag is pulled aside with a note saying why. A validator does that for every row of a file: the same rules every time, and a reason for each rejection.

Overview

  • Rules work at four levels: the file and its header, each value, the columns within one row, and the rows compared with each other and with trusted lists.
  • Common rules are required columns, data types, missing values, ranges, allowed values, formats, unique keys and cross-field conditions.
  • A good validator collects every problem with its line and the rule broken, instead of stopping at the first error.
  • Failed rows are quarantined or sent back for correction. They are never silently dropped, filled with zero or 'fixed' by guessing.
  • The same checks run on every new file, so the validator itself is tested, including at the exact boundary of each rule.
  • At scale, rules are written as a schema in tools such as pandera, Great Expectations or a Frictionless Table Schema, and run before training or reporting.

Why it matters

A file can open without any error message and still be wrong. Duplicate rows inflate attendance, a blank read as zero drags an average down, and an ID that loses its leading zero becomes a different learner. Once such a file feeds a dashboard or trains a model, the mistake is copied into every result, so it is far cheaper to catch it at the door.

Key terms

Schema
The written description of a dataset: which columns must exist, what each means, its type and its allowed values.
Header check
A file-level test that every required column is present before any row is read.
Data type
The kind of value a field holds, such as whole number, decimal, date or text. An ID with leading zeros must stay text.
Missing value
A cell with no value. It is different from zero, from the text NA and from a value deliberately left unknown.
Range check
A test that a number lies between the allowed lower and upper bounds, including or excluding the endpoints as the rule states.
Allowed values
A fixed list that a field's value must come from, such as present or absent, or Monday to Saturday.
Composite key
Two or more fields that together identify exactly one record, such as learner ID plus day.
Cross-field rule
A rule linking columns in the same row, such as 'an absent learner has no marks'.
Referential check
A test that a value exists in another trusted list, such as every learner ID appearing on the roster.
Quarantine
Setting failed rows aside with their reasons, so clean rows can continue while someone reviews the rest.
Validation report
The list of failures, each giving the file, the line, the value and the rule broken.

Types

Validation rules are grouped by what they look at. A complete validator uses all of them, roughly in this order, because a failure at an earlier level makes the later checks meaningless.

1. Structure checks

Checks that the file can be read and has the expected shape: the right encoding and header, every required column present, and each row holding as many values as there are columns.

  • How it works: Read the header first and compare it with the schema's required columns; stop with one clear message if any is missing. Then flag rows with too many or too few values.
  • Example: A file whose column says mark instead of marks is rejected as a whole, instead of producing a blank score on every row.
  • When to use it: Always, as the first check, especially for files from different apps or edited by hand in a spreadsheet.

2. Type and format checks

Checks that each value can be read as the type the schema promises, and that text follows its agreed pattern.

  • How it works: Parse each value strictly, for example with a regular expression that accepts only digits or a date parser with one fixed format, and report the value that failed.
  • Example: Marks of 7.5 fail a whole-number rule, an Indian mobile number must be ten digits starting with 6, 7, 8 or 9, and 03/04/2025 is ambiguous unless the date format is fixed.
  • When to use it: For every column. Keep identifiers such as roll numbers and phone numbers as text: they are labels, not quantities.

3. Missing-value checks

Checks which cells are empty and whether an empty cell is allowed there, always keeping a blank separate from zero.

  • How it works: Strip spaces, treat an empty string and any agreed marker such as NA as missing, and test it against the column's rule instead of filling it in.
  • Example: A present learner with a blank score is reported for correction, while an absent learner with a blank score is exactly right.
  • When to use it: Whenever a missing value could be mistaken for a real one, such as zero marks, zero rainfall or zero sales.

4. Range and allowed-value checks

Checks that numbers fall inside their bounds and that categories come from their fixed list.

  • How it works: Compare each number with the written minimum and maximum, including the endpoints only if the rule says so, and look up each category in a set of allowed values after normalising its case.
  • Example: Marks of 105 fail a 0 to 100 rule while 0 and 100 pass, and Sun fails because sessions run Monday to Saturday.
  • When to use it: For every measured number and every category. Test the exact boundaries, because off-by-one mistakes hide there.

5. Uniqueness and referential checks

Checks that each record's key appears only once and that every key refers to something that really exists.

  • How it works: Remember the line where each complete key first appeared and report later repeats with that line number, and look up each ID in the trusted roster.
  • Example: Learner 01 on Monday twice is a duplicate, learner 01 on Monday and Tuesday is two valid records, and learner 05 is reported when the roster lists only 01 to 04.
  • When to use it: Whenever rows are counted, added up or joined to another table, because a duplicate or an unknown ID changes the totals.

6. Cross-field and batch checks

Checks rules that involve several columns or the whole batch: consistency inside a row, and sudden changes compared with earlier files.

  • How it works: Write each condition as an if-then rule for the row, and for a batch compare simple statistics, such as the share of blanks or the average, with their usual range.
  • Example: An absent learner with 40 marks breaks a cross-field rule, and a batch in which 30 per cent of scores are blank, when 2 per cent is usual, deserves a look even if every row passes.
  • When to use it: Once the simpler checks pass, and before the data trains a model or reaches a report people act on.

Explained in detail

Start with what one row means

Before writing a single rule, write one sentence describing a row. In this register, one row says that one learner attended, or missed, one session on one day, with a practice score if they were present. The school holds one session per day, so a learner ID and a day together identify a record: that pair is the composite key. Every later rule follows from this sentence. If the school adds an evening session, two Monday rows for the same learner can both be correct, and the key must grow to include a session ID before such files are checked.

ColumnMeaningRule
student_idRoll number from the rosterRequired, text, must be on the roster
daySession dayRequired, one of Mon, Tue, Wed, Thu, Fri, Sat
statusAttendanceRequired, present or absent in any capitalisation
marksPractice scoreWhole number from 0 to 100 if present, blank if absent

Check the file before its rows

Some problems belong to the whole file. A missing marks column is not a hundred separate row errors; it is one header error, and the file should be rejected with one clear message. CSV looks simple, but RFC 4180, the 2005 document that describes the format, shows why reading it by hand is risky: a value containing a comma, such as "Sharma, Riya", must be wrapped in quotes, and splitting lines on commas would cut it in two. Python's csv module handles quoting correctly, so use it instead of splitting lines yourself, and flag any row that ends up with more or fewer values than the header has columns.

Encoding causes the strangest header failures. When a spreadsheet saves a file as CSV UTF-8, it may put an invisible byte order mark (BOM) before the first column name. The file looks perfect, yet the first column is read with a hidden U+FEFF character in front of student_id, so a correct file fails the header check. Opening files with Python's utf-8-sig encoding, or removing the mark as the code below does, fixes it. The general lesson: when a check fails on a file that looks right, print the raw header and look for characters you cannot see.

Check each value

Strip the spaces around each value and normalise case where the rule allows it, so ' 01 ' and Present are accepted as 01 and present. Then test the type strictly. Identifiers such as roll numbers are labels, not quantities: read them as text, because converting 007 to a number gives 7, and a Delhi landline written 011 2345 6789 loses its leading zero. Marks must be a whole number, so 7.5 fails. A regular expression that accepts only the digits 0 to 9 is stricter than Python's int(), which also accepts text such as 1_000. Dates need one fixed format, because 03/04/2025 means 3 April in India but 4 March in the United States; agree on the ISO 8601 form 2025-04-03, or validate against the single format the app produces.

Missing values need a decision, not a default. A zero is a real score; an empty cell means nobody recorded one. Replacing the blank with zero invents a result and lowers the average, and deleting the row hides the fact that a score is missing. Here the rule depends on another column: a present learner must have marks, and an absent learner must not. Range and allowed-value checks come last: marks run from 0 to 100 including both ends, and sessions run Monday to Saturday.

RulePassesFails
Whole number 0 to 100 when present0, 58, 100105, 7.5, -5, blank
Allowed statuspresent, Present, absentpresnt, P, blank
Session dayMon to SatSun, Monday, 2025-04-07
ID on the roster01, ' 01 '05, and 1 when the roster says 01

Check rows against each other and against trusted lists

A duplicate is defined by the key, not by the whole row. Learner 01 on Monday with 75 and again with 81 is a duplicate even though the scores differ, while learner 01 on Monday and on Tuesday is two valid records. Remember the line where each key first appeared and report later repeats with that line number. The checker cannot know which of the two scores the teacher meant, so a person must decide. A referential check compares each ID with the roster, which catches a mistyped 05 when only 01 to 04 are enrolled, and cross-field rules link columns in one row, such as 'absent means no marks'.

Report everything, quarantine, never guess

Collect every problem instead of stopping at the first, so that whoever corrects the file can fix everything in one pass. Then decide what happens to the batch. A header problem rejects the whole file. A few bad rows can be quarantined, set aside with their reasons, while clean rows continue. If more than an agreed share of rows fails, say 5 per cent, stop the batch, because something upstream has probably broken. Keep the original file untouched, send the report to whoever owns the data, and run exactly the same checks on the corrected file. A useful message contains:

  • the file name and the line number,
  • the column and the value actually found,
  • the rule that was broken, in words a data owner understands,
  • and, where it helps, what a correct value looks like.

A clean report proves only that the data obeys the rules you wrote. A score typed as 72 instead of 27 is a valid whole number within range, so no rule catches it. Spot-check a sample of rows against the paper register, and add a new rule whenever you discover a new kind of mistake.

When spreadsheets silently change data

Validation matters most where data passes between tools. In 2016 Mark Ziemann, Yotam Eren and Assam El-Osta reported that about one fifth of the genetics papers they screened with Excel gene lists, 704 of 3,597, contained gene names that the spreadsheet had turned into dates, such as SEPT2 becoming 2 September. A follow-up covering 2014 to 2020 found about 31 per cent. In 2020 the committee that names human genes renamed some of them, so SEPT1 became SEPTIN1 and MARCH1 became MARCHF1, partly to stop this. In the same year Public Health England left 15,841 positive COVID-19 results out of its daily figures between 25 September and 2 October, because an automated process loaded results into a spreadsheet in the older .xls format, which holds at most 65,536 rows per sheet, and rows beyond the limit were dropped. A simple check that the rows written equal the rows received would have caught it.

Validation at scale

The same ideas scale to millions of rows with pandas. Read every column as text with dtype=str and keep_default_na=False, so that nothing is converted behind your back. Convert numbers with pd.to_numeric(errors='coerce') and report the values that became NaN, find repeated keys with df.duplicated(subset=['student_id', 'day'], keep='first'), and test categories with isin. Teams then go one step further and write the rules as a schema. Libraries such as pandera and Great Expectations, or a Frictionless Table Schema file, describe each column's type and constraints, and the pipeline runs them automatically before the data trains a model or builds a report. When two teams agree on such a schema, some organisations call it a data contract between the team that produces the data and the teams that use it.

Run the code below. monday.csv passes only 1 of its 8 rows and reports 8 problems, with line 9 breaking two rules at once. tuesday.csv passes all 4 rows although it starts with an invisible byte order mark, pads an ID with spaces, writes Present with a capital letter and uses both boundary scores, 0 and 100. wednesday.csv is rejected at the header because its column is called mark, and thursday.csv passes 1 of 3 rows, catching one row with too many values and one with too few.

How it works

  1. Write the schema: one sentence for what a row means, then each column's meaning, type, whether it may be blank and its allowed values.
  2. Check the file: read it with the csv module, remove a byte order mark and compare the header with the required columns. Reject the file if one is missing.
  3. Check each row's shape and values: the number of values, then type, missing values, range and allowed values after stripping spaces and normalising case.
  4. Check cross-field rules, then compare rows: repeated keys, and IDs that must appear in a trusted list.
  5. Write every failure to a report with file, line, value and rule. Quarantine failed rows and keep the original file unchanged.
  6. Send the report to the data's owner, rerun the same checks on the corrected file, and add each new kind of mistake as a new rule with a test.

The maths

Completeness of a column
Uniqueness of a key
Validity of a file

In monday.csv, 7 of the 8 rows have a learner ID, so ID completeness is 7 ÷ 8 = 87.5%. The 7 complete keys contain 6 different learner-day pairs, because (01, Mon) appears twice, so uniqueness is 6 ÷ 7 ≈ 85.7%. Yet only 1 of the 8 rows passes every rule, so validity is 1 ÷ 8 = 12.5%. Two healthy-looking ratios can sit beside a terrible third, which is why a report lists each rule's failures instead of giving one overall score.

In code

The validator follows the levels in order. It rejects a file whose header lacks a required column, then checks each row's shape, identity, values and the rule that links status to marks, and reports every problem with its line instead of stopping at the first. The four files are separate batches, each with its own traps.

attendance_check.py
import csv
import re
from io import StringIO

REQUIRED = ["student_id", "day", "status", "marks"]
ROSTER = {"01", "02", "03", "04"}          # learners enrolled in this batch
DAYS = {"Mon", "Tue", "Wed", "Thu", "Fri", "Sat"}
BOM = chr(0xFEFF)                          # invisible mark some spreadsheets put at the start

def field(row, name):
    return (row[name] or "").strip()      # a short row leaves None in the missing fields

def validate(text):
    """Return the number of data rows and a list of problems, each naming its line."""
    reader = csv.DictReader(StringIO(text.removeprefix(BOM)))
    missing = [column for column in REQUIRED if column not in (reader.fieldnames or [])]
    if missing:
        return 0, ["Header: missing column " + ", ".join(missing)]
    problems, first_seen, rows = [], {}, 0
    for row in reader:
        rows += 1
        line = reader.line_num
        # Shape: as many values as columns
        if None in row:
            problems.append(f"Line {line}: more values than columns")
        if None in row.values():
            problems.append(f"Line {line}: fewer values than columns")
        student, day = field(row, "student_id"), field(row, "day")
        status, marks = field(row, "status").lower(), field(row, "marks")
        # Identity: one learner, one day
        if not student or not day:
            problems.append(f"Line {line}: incomplete key")
        else:
            if student not in ROSTER:
                problems.append(f"Line {line}: learner {student} is not on the roster")
            if day not in DAYS:
                problems.append(f"Line {line}: day {day!r} is not a session day")
            key = (student, day)
            if key in first_seen:
                problems.append(f"Line {line}: repeats line {first_seen[key]}")
            else:
                first_seen[key] = line
        # Values, and the rule that links status to marks
        if status not in {"present", "absent"}:
            problems.append(f"Line {line}: status must be present or absent")
        elif status == "absent" and marks:
            problems.append(f"Line {line}: absent learner has marks")
        elif status == "present":
            if not marks:
                problems.append(f"Line {line}: marks missing")
            elif not re.fullmatch(r"[0-9]{1,3}", marks):
                problems.append(f"Line {line}: marks {marks!r} is not a whole number")
            elif int(marks) > 100:
                problems.append(f"Line {line}: marks {marks} above 100")
    return rows, problems

files = {
    "monday.csv": """student_id,day,status,marks
01,Mon,present,75
01,Mon,present,81
,Mon,present,90
02,Mon,present,105
03,Mon,absent,40
04,Mon,present,
05,Mon,present,60
02,Sun,present,7.5""",
    "tuesday.csv": BOM + """student_id,day,status,marks
 01 ,Tue,present,0
02,Tue,present,100
03,Tue,absent,
04,Tue,Present,58""",
    "wednesday.csv": """student_id,day,status,mark
01,Wed,present,64""",
    "thursday.csv": """student_id,day,status,marks
01,Thu,present,88,90
02,Thu,present
03,Thu,absent,""",
}
for name, text in files.items():
    rows, problems = validate(text)
    if problems and problems[0].startswith("Header"):
        print(f"{name}: rejected. {problems[0]}")
        continue
    bad_lines = {problem.split(":")[0] for problem in problems}
    print(f"{name}: {rows - len(bad_lines)} of {rows} rows pass every rule")
    for problem in problems:
        print("   ", problem)

Output

monday.csv: 1 of 8 rows pass every rule
    Line 3: repeats line 2
    Line 4: incomplete key
    Line 5: marks 105 above 100
    Line 6: absent learner has marks
    Line 7: marks missing
    Line 8: learner 05 is not on the roster
    Line 9: day 'Sun' is not a session day
    Line 9: marks '7.5' is not a whole number
tuesday.csv: 4 of 4 rows pass every rule
wednesday.csv: rejected. Header: missing column marks
thursday.csv: 1 of 3 rows pass every rule
    Line 2: more values than columns
    Line 3: fewer values than columns
    Line 3: marks missing

Try it yourself

Where should this problem be caught?

Place each problem at the level of check that catches it.

  • An invisible mark sits before the first column name

  • An absent learner has 40 marks

  • Status written as presnt

  • Learner 01 appears twice on Monday

  • Learner 05 is not on the roster

  • A score of 105 out of 100

  • The marks column is missing from the export

Put the validation steps in order

Arrange the steps a careful validator follows.

  1. 1Compare keys across rows and with the roster
  2. 2Check each row's shape and each value
  3. 3Write down what one row means
  4. 4Report, quarantine and rerun on the corrected file
  5. 5Check the encoding and the header
  6. 6Check rules that link columns in a row

Challenge the checker

Edit the files in the code editor and run after each change.

  1. Before running, predict every problem in monday.csv. Which line breaks two rules, and which problem would a range check alone miss?
  2. In tuesday.csv change 100 to 101, then change Present to Presnt. Predict each message before you run.
  3. Delete .removeprefix(BOM) inside validate and run again. Explain why tuesday.csv is now rejected though nothing in it looks wrong, then restore the line.
  4. Add a rule that Saturday sessions have no marks because they are sports days. Write one row that passes and one that fails, and check both.

Think about it

  • Could any of your rules reject a correct row? How would you find out before using it on real files?

Common mistakes

  • Treating a blank as zero, which invents a score and pulls the average down.
  • Reading identifiers as numbers, so roll number 007 becomes 7 and a landline loses its leading zero.
  • Dropping every repeated ID, which also deletes the same learner's valid rows on other days.
  • Stopping at the first error, so the file goes back and forth once for every problem.
  • Silently 'fixing' failed rows instead of reporting them and keeping the original file.
  • Believing a clean report proves the data is right: 72 typed for 27 still passes every rule.

Check yourself

Question 1

1. Which record repeats the key (01, Mon)?

Question 2

2. The whole marks column is missing. Where should the validator stop?

Question 3

3. A present learner's score cell is empty. What should happen?

Question 4

4. Why keep learner IDs as text?

Question 5

5. In monday.csv, 1 of 8 rows passes every rule. What is its validity?

Question 6

6. An absent learner has 40 marks. Which kind of rule catches this?

Summary

  • Write the schema first: what one row means, and each column's type, blank rule and allowed values.
  • Check in order: file and header, each row's shape and values, cross-field rules, then rows against each other and trusted lists.
  • Keep blanks separate from zero and identifiers as text.
  • Report every failure with file, line and rule; quarantine bad rows and never guess a fix.
  • Test the validator at its boundaries, rerun it on every new file, and at scale write the rules as a schema in a tool.

Finished reading?

Sources: Python documentation · csv: CSV file reading and writing · RFC 4180 · Common format and MIME type for CSV files (2005) · pandas · read_csv · pandera · data validation for dataframes · Frictionless Data · Table Schema · Ziemann, Eren and El-Osta · Gene name errors are widespread in the scientific literature (2016)