There is a comforting assumption hidden inside many discussions of data analysis:

The data is already there. We just need to analyse it.

Load the file. Run the query. Make the chart. Calculate the average. Find the trend.

If only it were that simple.

In the real world, data rarely arrives in the shape we want. It arrives in spreadsheets with inconsistent column names, CSV files with strange encodings, databases with missing values, forms with fields left blank, and records entered by people who interpreted the same question in completely different ways.

The analysis may take an hour.

Getting the data into a condition where that analysis actually means something may take a day.

Sometimes much longer.

That is the reality of dirty data.

The data we want versus the data we get

Suppose a university wants to analyse the performance of its graduates.

We might design a neat table:

It looks wonderfully clean.

Every student has an identifier. Every programme has a name. Every year is a number. Every degree class is numeric. Every employment status belongs to a small set of possible values.

This is the data we would like to have.

But then somebody hands us the actual data.

Perhaps the programme appears as:

Computer Science
Computer Science
Comp Sci
Comp. Science
Computer science
CS
C.S.

The year might appear as:

2024
24
2023/24
2024/2025
"2024"

The Degree Class column might contain:

2.1
1
N/A
-
2.2
3

And the employment field might contain:

Employed
employed
EMP
Yes
Y
Working
Full time

Suddenly our beautiful table has disappeared.

We do not yet have a data-analysis problem.

We have a data-cleaning problem.

Reality does not come in tables

This is perhaps the first thing every data analyst eventually learns:

The world does not naturally organise itself into your database schema.

We impose schemas upon reality because computers need structure.

A university does not naturally produce a column called employment_status.

A graduate says:

“I’m working for a bank, but I’m doing a master’s part-time.”

A form designer has to decide what that means.

Does the graduate belong under:

Employed

or:

Further Study

or perhaps both?

That is no longer merely a technical question.

It is a question about meaning.

And this is why cleaning data can be much harder than analysing it.

Cleaning is not just fixing typos

It is tempting to think of data cleaning as a mechanical operation.

Find the errors.

Fix them.

Delete the blanks.

Standardise the spelling.

Move on.

But real data cleaning involves much more than that.

We have to ask:

• What does this field actually mean?
• What values are valid?
• What should happen when a value is missing?
• Is 0 the same thing as missing?
• Is "N/A" a genuine value or an indication that nobody supplied one?
• Are 24 and 2024 really the same year?
• Is CS definitely Computer Science?
• Is a Degree Class of 4.1 an error, or does this university use a 5-point scale?
• If two records have the same student ID, which one is correct?
• If two departments use different definitions of “employed”, can we safely combine their data?

Notice what happened.

We started with a spreadsheet.

We ended up asking questions about the world represented by the spreadsheet.

That is the deeper nature of data cleaning.

Missing data is still data

One of the most dangerous instincts in data analysis is to treat missing values as an inconvenience that should simply disappear.

Consider:

Student,Degree Class,Employment
001,3,Employed
002,2.1,
003,,Further Study
004,1,Employed

What does the blank in the second row mean?

Perhaps the student did not respond.

Perhaps the information was not collected.

Perhaps the student was unemployed.

Perhaps the data entry clerk forgot to enter it.

Those are four completely different situations.

Replacing every blank with:

Unknown

may be convenient, but it does not necessarily make the data more truthful.

Likewise, replacing missing degree classes with zero would be disastrous. A student with no recorded degree class is not necessarily a student with a degree class of zero.

The lesson is simple:

Missingness has meaning.

Good cleaning preserves that meaning rather than hiding it.

The tyranny of the template

This brings us to an interesting tension.

When we collect information, we naturally want a template.

Give everybody the same form.

Ask the same questions.

Put the answers into the same columns.

That makes analysis easier.

But templates are representations of reality, not reality itself.

The more complicated the world becomes, the more difficult it becomes to create a template that captures everything without becoming unwieldy.

We have all encountered this phenomenon.

A simple form begins with:

Name:
Age:
Programme:
Year:

Then requirements accumulate.

Employment status:
Employment type:
Employer:
Industry:
Further study:
Study mode:
Study institution:
Graduation classification:
Professional qualification:
...

Before long, our simple form has become a monster.

We have gained structure, but we have also imposed our assumptions upon the people entering the data.

This is one reason data formats are so interesting.

The long search for structure

Much of computing can be viewed as a long attempt to answer one question:

How should we represent information so that both humans and machines can understand it?

XML was one influential answer.

It allows us to define our own vocabulary:

<graduate>
    <name>Jane Doe</name>
    <programme>Computer Science</programme>
    <year>2024</year>
    <degree-class>3</degree-class>
</graduate>

This is powerful because the structure is explicit.

We can invent tags appropriate to our domain.

But XML also makes it possible to build extraordinarily elaborate documents. Namespaces, schemas, attributes and deeply nested structures can turn a relatively simple piece of information into something difficult to read and maintain.

JSON took a different approach:

{
  "name": "Jane Doe",
  "programme": "Computer Science",
  "year": 2024,
  "degree-class": 3
}

Less ceremony.

More direct representation of objects and values.

YAML goes further toward human readability:

name: Jane Doe
programme: Computer Science
year: 2024
degree-class: 3

TOML takes a similarly human-oriented approach for configuration data.

Different formats make different trade-offs.

But none of them solves the fundamental problem.

They can tell us how to represent the data.

They cannot tell us whether the data we have put into that structure is the right data.

A perfect format cannot fix imperfect information

This distinction is important.

Imagine that we have designed the perfect JSON schema for our graduate database.

It might specify:

year       → integer
degree-class        → number
programme  → string
status     → controlled vocabulary

Excellent.

But somebody can still enter:

{
  "year": 2024,
  "degree-class": 3,
  "programme": "Computer Science",
  "status": "Employed"
}

when the graduate actually left the university in 2023.

The JSON is perfectly valid.

The data is wrong.

This is the difference between syntactic correctness and semantic correctness.

The computer can determine whether something fits the structure.

It cannot automatically determine whether the information accurately describes reality.

That is where the Textsmith has to enter the picture.

Cleaning is discovering structure

This is why I increasingly think of data cleaning as a form of textsmithing.

A Textsmith does not merely manipulate characters.

A Textsmith looks at information and asks:

What structure is hiding here?

A messy CSV may contain several different representations of the same thing.

A collection of reports may use different names for the same department.

A directory of files may encode dates differently in their filenames.

A collection of survey responses may contain several ways of saying the same thing.

The task is to discover those relationships and make them explicit.

We might transform:

CS
Comp Sci
Computer Science
computer science
C.S.

into:

Computer Science

But the important step is not the transformation itself.

The important step is deciding why those values should be considered equivalent.

That decision is knowledge.

The command that performs the replacement is merely implementation.

Cleaning before analysis

This explains an uncomfortable truth about data science:

A large part of data analysis happens before the analysis.

Before calculating an average, we need to know whether the numbers are comparable.

Before counting categories, we need to know whether differently spelled categories represent the same thing.

Before plotting a trend, we need to know whether the dates mean the same thing.

Before joining two datasets, we need to know whether their identifiers actually refer to the same entities.

Before drawing a conclusion, we need to know whether the missing values, duplicates and outliers have distorted the result.

The glamorous part is often the final chart.

The intellectually important part may have happened hours earlier in a shell pipeline.

Something as simple as:

cut -d, -f3 graduates.csv |
    sort |
    uniq -c

can reveal more about the condition of a dataset than a sophisticated visualisation.

Perhaps we discover:

   12 C.S.
   37 CS
    8 Comp Sci
  214 Computer Science
    4 computer science

Now we know that our “Computer Science” category is not actually one category in the source data.

The first step toward analysis is therefore not always analysing.

Sometimes it is looking.

The danger of cleaning too aggressively

There is another trap.

Once we realise that the data is messy, we may become too eager to make it clean.

We standardise everything.

We remove every blank.

We discard every strange value.

We force every record into our preferred schema.

The result looks beautiful.

And it may be completely misleading.

A clean dataset can be more dangerous than a dirty one if the cleaning process has erased important information.

There is value in preserving the original data.

Keep the raw file.

Create a cleaned version.

Record the transformations.

Use scripts where possible.

Then the path from:

raw data
    ↓
cleaned data
    ↓
analysis

can be reproduced.

This is where version control, plain text, shell tools and small programs become so powerful.

The cleaning process itself becomes part of the analysis.

The real question is not “How do I clean this?”

It is:

What does this data mean?

That question changes everything.

Instead of immediately opening a spreadsheet and correcting cells, we begin by inspecting the data.

What fields exist?

What values occur?

Which values are missing?

Which values appear inconsistent?

Which records are duplicated?

Which assumptions are embedded in the structure?

What information has already been lost?

What information should never be discarded?

Only after asking these questions should we decide how to transform the data.

This is exactly the mindset behind good text processing.

First understand the structure.

Then transform it.

From dirty data to trustworthy data

There is a useful distinction between three things:

Raw data is what the world gave us.

Cleaned data is what we have transformed into a consistent representation.

Trusted data is data whose meaning and provenance we understand well enough to use for a particular purpose.

Those are not necessarily the same thing.

A perfectly formatted JSON document can contain bad information.

A messy CSV can contain valuable information.

And a beautifully cleaned dataset can still be wrong if we cleaned it according to the wrong assumptions.

The goal is therefore not merely clean data.

The goal is data that is fit for purpose and whose transformations we can explain.

The Textsmith’s lesson

Perhaps this is one of the most important lessons in working with data:

The hardest part is often not extracting the answer. It is deciding what counts as an answerable question in the first place.

Data formats help us impose structure.

Templates help us collect consistent information.

Schemas help us validate what enters our systems.

Programming languages help us transform it.

Unix tools help us inspect and reshape it.

But none of these tools can remove the fundamental gap between the messy world and our representation of that world.

There will always be a gap.

The Textsmith learns to work within it.

We inspect before transforming.

We question before standardising.

We preserve the original before cleaning.

We document what we changed.

And, most importantly, we remember that every tidy dataset is the result of decisions.

The next time a data analysis tutorial begins with:

import pandas as pd

df = pd.read_csv("data.csv")

and immediately jumps to:

df.groupby(...)

remember that something has probably been left out of the story.

The real work may have started long before the first line of analysis.

It began when someone asked:

What exactly is this data?

And then spent the next several hours finding out.