Data Science & Analytics / essay

The First Hour With a Messy Dataset: A Practical Audit

A question-first data audit: preserve the source, inspect missingness and make cleaning decisions traceable.

In this note

A data-analysis project often begins with an apparently simple request: “Can you make a dashboard from this spreadsheet?” The temptation is to load the file and begin plotting. Yet the first hour is better spent checking what the file actually represents. A polished chart built on duplicated records, mismatched dates, and unclear units can be worse than no chart, because it makes a mistake look convincing.

This guide describes a practical first-pass data audit using concepts that apply to Python, R, SQL, or spreadsheets. The goal is not perfect data; it is an inspectable understanding of what you have and what must be resolved before making claims.

A four-row audit you can run

This synthetic CSV contains one repeated row and one missing measurement. It does not represent a real experiment or client file. The Python 3 example reports the issues without silently deleting or imputing anything.

import csv
from io import StringIO

source = "sample_id,condition,value\nA,control,12\nA,control,12\nB,treated,\nC,treated,8\n"
rows = list(csv.DictReader(StringIO(source)))
report = {
    "rows": len(rows),
    "distinct_samples": len({row["sample_id"] for row in rows}),
    "exact_repeated_rows": len(rows) - len({tuple(row.items()) for row in rows}),
    "missing_values": sum(not row["value"].strip() for row in rows),
}
assert report == {
    "rows": 4, "distinct_samples": 3,
    "exact_repeated_rows": 1, "missing_values": 1,
}

The next decision requires context: should sample A have one record or two measurements? Is B’s blank value unavailable, not measured or a recording error? The audit deliberately leaves those questions open. My Data Quality Workbench demonstration develops this separation between explicit rules, rejected-row inspection and downloadable reports; it is an independent software demonstration, not client-delivery evidence.

Update, 16 November 2026: added and executed the standard-library audit example. Its values are synthetic; no client data were used.

Clarify the question before cleaning

Data quality is relative to a task. A missing phone number may matter to a contact campaign but not to a sales-by-month summary. A duplicated row may be a mistake in a customer table but a valid repeated event in a transaction log. Begin by writing the requested decision or deliverable in plain language.

Ask what each row represents and what each column should mean. Record expected time periods, geographical scope, units, and any business rules. If a stakeholder cannot explain the unit of observation, that is an important discovery, not a reason to guess.

Preserve the original file

Save an untouched copy of every source. Work in a new directory or database table and record where the data came from. If a file was exported from a system, note the export date and any filters applied. Avoid repeatedly saving over the only copy of an Excel workbook while experimenting with transformations.

A minimal directory structure can contain raw, interim, processed, reports, and scripts. Keep derived outputs separate from manually provided data. That separation makes it possible to answer a fundamental review question: did the analysis change the information or only its presentation?

Inspect dimensions and types

Start by counting rows and columns, listing column names, and inspecting a representative sample. Then look at data types. Dates stored as text, numeric identifiers interpreted as floating-point numbers, and currency columns mixing symbols with values are common sources of error. Watch for leading zeros in postal codes or patient/sample identifiers; converting them to numbers can silently destroy meaningful formatting.

In Python, Pandas can summarize data types, missingness, and cardinality. In R, similar checks are available with common tidyverse tools. SQL queries can reveal unexpected duplicates or invalid values. The tool matters less than producing explicit, repeatable checks.

Quantify missing values by context

A single overall “percent missing” number rarely tells the full story. Check missingness per column, by month, by source, or by relevant group. Distinguish a blank field from zero, “not applicable,” “unknown,” and a test that was never performed. Those states often have different meanings.

Do not fill every blank with the median or the most common category simply because an algorithm expects complete inputs. Imputation introduces assumptions that need to match the analytical question and evaluation design. For descriptive dashboards, showing missingness may be more honest than hiding it.

Find duplicates without deleting evidence

Exact duplicate rows are one kind of problem; repeated entities are another. A customer can legitimately have multiple orders. A biological sample can have several measurements. To identify problematic duplicates, define a candidate key and ask whether it should be unique. A compound key such as order_id plus line_number may work where order_id alone does not.

Report duplicate counts before deleting anything. If you consolidate records, preserve an audit trail documenting which record was retained and why. Automated cleanup should be explainable to the person who supplied the data.

Test ranges and consistency

Check numeric minima, maxima, and distributions. A negative age, a date far outside the reporting period, or a percentage above 100 may signal a problem, but do not assume every extreme value is invalid. Outliers can be mistakes, important cases, or valid edge conditions. Investigate them according to domain knowledge.

Consistency checks catch errors that simple range tests miss. A delivery date preceding an order date may be impossible under normal definitions. A completed transaction with no associated amount may need investigation. Values in categorical columns should be standardized carefully: “NY,” “New York,” and “new york” may be equivalent, while two similarly named product categories might not be.

Create a data-quality report

A useful deliverable from the first audit is a compact table containing each issue, its frequency, likely impact, proposed action, and whether the action requires confirmation. Classify issues as blockers, warnings, or informational findings. Include sample rows where they can be shared safely.

This report changes the conversation with stakeholders. Instead of saying “the spreadsheet is messy,” you can say that 8% of rows lack a required date, the nominal identifier is duplicated in a specific subset, and two incompatible currency units appear. The numbers are illustrative; in a real project they must come from the actual file.

Keep transformations in code

Even if the final product is Excel, reproducible cleaning steps are easier to maintain in a script or auditable query. Write transformations in stages, with clear names and comments explaining non-obvious rules. Prefer validation that fails on unexpected input rather than silently continuing.

For example, checking that a join does not unexpectedly multiply row counts can prevent dramatic downstream errors. When merging tables, inspect unmatched keys and duplicates on each side. A join that runs successfully may still be logically wrong.

Explore before modeling

Only after basic checks should you move into distributions, group comparisons, trends, and correlations. Always ask whether counts or rates are appropriate. A category with the highest total may simply have the largest population or transaction volume. Rates require carefully defined denominators.

At the modeling stage, make sure data preparation is fitted within the training data where relevant. Leakage can occur when imputation, feature selection, or normalization uses information from the future or the evaluation set. A highly accurate model built on leaked data is not a successful analysis.

Deliver something a colleague can inspect

A strong handoff includes the original source location, a data dictionary, cleaning script, quality summary, processed dataset if permitted, and limitations. Include a small README explaining how to rerun the analysis and which assumptions still require stakeholder approval. For a dashboard, document refresh behavior and any filters that change the population being shown.

The first hour with a dataset should reduce uncertainty, not maximize the number of charts. When you begin with questions, explicit checks, and a preserved source, you set up the rest of the project to be both faster and more trustworthy.

Sources & further inspection

Related work

  • Data quality
  • Python
  • Reproducibility

Related reading

← Back to writing