How to Audit a Messy Dataset Before Analysis

Analysis built on a dataset nobody checked is a gamble. An audit is the step before cleaning: a systematic look at what the data contains, what is missing, and what cannot be true, so you can decide whether it is fit for the question and what cleaning it needs. It takes an hour and routinely saves days. This guide audits two real datasets, each with a different class of hidden problem.

Step 1: Read the documentation and ask where the data came from

Before opening the data, answer: who produced it, how, covering what period and population, and with what definitions? What does one row represent? Are some values estimated, imputed or coded? Several findings below would be impossible to interpret without the source's documentation.

Step 2: Profile every column

A small reusable function gives a first picture of any table: types, missing values, distinct counts, ranges and the most common value.

import io
import urllib.request
import zipfile
import pandas as pd

def audit(df):
    report = pd.DataFrame({
        "type": df.dtypes.astype(str),
        "missing": df.isna().sum(),
        "distinct": df.nunique(),
        "min": df.min(numeric_only=True),
        "max": df.max(numeric_only=True),
        "most common": df.mode().iloc[0],
    })
    print(f"{len(df)} rows, {df.shape[1]} columns, {df.duplicated().sum()} exact duplicate rows")
    return report

url = "https://archive.ics.uci.edu/static/public/468/online+shoppers+purchasing+intention+dataset.zip"
sessions = pd.read_csv(zipfile.ZipFile(io.BytesIO(urllib.request.urlopen(url).read())).open("online_shoppers_intention.csv"))
report = audit(sessions)
print(report.loc[["ProductRelated", "ProductRelated_Duration", "PageValues", "Month", "VisitorType", "Revenue"]].to_string())

12330 rows, 18 columns, 125 exact duplicate rows
                            type  missing  distinct    min          max        most common
ProductRelated             int64        0       311      0          705                  1
ProductRelated_Duration  float64        0      9551    0.0  63973.52223                0.0
PageValues               float64        0      2704    0.0   361.763742                0.0
Month                     object        0        10    NaN          NaN                May
VisitorType               object        0         3    NaN          NaN  Returning_Visitor
Revenue                     bool        0         2  False         True              False

The data is 12,330 online shop sessions over a year (UCI: Online Shoppers Purchasing Intention). The profile raises questions immediately. There are no missing values at all, which is unusual and worth confirming. There are 125 exact duplicate rows, although the documentation says each session belongs to a different user, so they may be distinct short sessions that happen to look identical rather than errors; that needs a decision, not an automatic deletion. And Month has only 10 distinct values in a dataset described as covering a year.

Step 3: Targeted checks for coverage, consistency and plausibility

The profile tells you where to look. Follow up each question with a specific check:

print("month labels:", sorted(sessions["Month"].unique()))
print("sessions with product pages but zero time on them:",
      int(((sessions["ProductRelated"] > 0) & (sessions["ProductRelated_Duration"] == 0)).sum()))
print(f"longest product-page time: {sessions['ProductRelated_Duration'].max() / 3600:.1f} hours")

month labels: ['Aug', 'Dec', 'Feb', 'Jul', 'June', 'Mar', 'May', 'Nov', 'Oct', 'Sep']
sessions with product pages but zero time on them: 717
longest product-page time: 17.8 hours

Three findings, each a different quality dimension:

• Coverage: January and April do not appear at all. Any analysis of seasonality, or any "annual" total, is missing two months, and the report must say so.
• Consistency: "June" is spelled out while every other month is abbreviated. Harmless here, but the same kind of inconsistency splits categories in other datasets, such as "Delhi" and "New Delhi", or "Bangalore" and "Bengaluru".
• Plausibility: 717 sessions viewed product pages but record zero seconds on them, and one session spent 17.8 hours on product pages. These may reflect how the tracking tool measures time, such as a final page with no measurable duration, or tabs left open, but they will distort any average time on page.

Step 4: Look for codes that pretend to be numbers

The second dataset is NOAA's monthly record of carbon dioxide at Mauna Loa, a carefully maintained scientific series. Its columns include the number of measurement days and the standard deviation for each month:

co2 = pd.read_csv("https://gml.noaa.gov/webdata/ccgg/trends/co2/co2_mm_mlo.csv", comment="#")
co2 = co2[co2["year"] <= 2025]                                           # fixed window for reproducibility
print("rows with negative values:", {c: int((co2[c] < 0).sum()) for c in ["ndays", "sdev", "unc"]})
print(f"mean monthly sdev, taken naively: {co2['sdev'].mean():.3f} | excluding the negative codes: {co2.loc[co2['sdev'] >= 0, 'sdev'].mean():.3f}")

rows with negative values: {'ndays': 195, 'sdev': 196, 'unc': 194}
mean monthly sdev, taken naively: -2.021 | excluding the negative codes: 0.507

A standard deviation cannot be negative, yet almost 200 rows contain negative values. The file's own header explains why: missing months were interpolated and are marked with negative standard deviation and uncertainty, and values unavailable for the older data are also indicated by negative numbers (NOAA: Mauna Loa monthly data file). Averaging the column naively gives a negative standard deviation, nonsense that a quick glance at a summary would catch but a dashboard might not. The same trap appears in business data as codes like -1, 0, 999 or 1900-01-01 standing in for "unknown". Note also what the header implies for the main series: some monthly averages are interpolated rather than measured, a fact that matters for certain analyses.

Questions to put to whoever owns the data

Some findings can only be explained by the people who produce the data. Useful questions:

• How is each row created, and can one real-world event produce several rows?
• Which fields are entered by people, and which by systems?
• Have definitions, tracking or systems changed during the period covered?
• Are any values estimated, imputed or filled with codes when unknown?
• Are some records excluded before the data reaches you, such as test accounts or internal users?

The answers often explain several audit findings at once, and they belong in the audit note alongside the numbers.

An audit checklist by data quality dimension

The UK government's data quality framework describes six core dimensions of data quality (GOV.UK: Government Data Quality Framework). They make a practical checklist:

Write the audit down

Finish with a short note: the dataset and source, what was checked, what was found with counts, and a verdict for each intended use. For example: "Fit for comparing conversion between visitor types; not fit for monthly seasonality without January and April; time-on-page averages need outlier handling." That note is what lets others trust your analysis, and it tells whoever cleans the data exactly what to fix. Data cleaning, step by step takes it from there, and handling missing data and outlier detection cover the two most common follow-ups.

Data understanding and summary statistics, outlier detection, and data cleaning and preparation are part of the Data Analytics course.

Sources

• Online Shoppers Purchasing Intention Dataset (UCI Machine Learning Repository) — UCI Machine Learning Repository
• Mauna Loa monthly mean CO2 data file (NOAA Global Monitoring Laboratory) — NOAA Global Monitoring Laboratory
• The Government Data Quality Framework (GOV.UK) — UK Government