Data cleaning: five steps that preserve the meaning of your analysis

It is easy to fall in love with the final stage of an analysis. The chart looks good, the dashboard responds to clicks, and the headline number has a suitably impressive font. Earlier in the process, though, somebody had to work out why one order appeared three times. That is where I start.

Data cleaning means identifying problems and preparing data for a particular use. The particular use matters. A dataset that supports counting orders may still be unsuitable for calculating their total value. An unknown price does not affect every question in the same way. Before changing anything, I want to know which answer I am trying to produce.

I will work through duplicates, missing values, consistent representation, validation and outliers. I also check the data before and after each meaningful change. When the cleaning is finished, I want to explain what disappeared and why. All orders and numbers in the example are synthetic. They illustrate decisions rather than claim to describe a real business.

Establish what a row represents

Imagine a file with an order identifier, a city and an order value. Two rows contain the same identifier. It is tempting to remove one immediately. I wait until I know whether a row describes a whole order, an order line, a payment or a status change. Those are different levels of detail, usually called the grain of the table.

In an order-line table, the combination of order identifier and line identifier might be the key. A status history also needs a way to identify events. A repeated order number tells me very little on its own. Removing rows using the wrong key can delete perfectly valid products, payments or history.

I write down a sentence such as: “Each row represents one order after registration is complete.” Then I establish the expected key, time coverage, units and status definitions. If the source does not guarantee one row per order, I need to revise the assumption or prepare an aggregation. Documentation should describe the data I received. Wishful thinking is a poor data model.

Keep a reference point before making changes

I work on a copy or in a separate processing stage. The original should remain available in an appropriate controlled location, with its source and retrieval date. That makes it possible to distinguish an upstream change from my own transformation. In a company, its storage and retention rules determine how this is done.

Before cleaning, I record the row count, number of distinct keys, missing-value rates and date coverage. For numeric measurements, I inspect minimum, maximum and total where those operations make sense. Adding customer identifiers tells me very little. Adding amounts in different currencies also requires an agreed conversion policy first.

I inspect category frequencies as well. If most records say “completed” and a few say “Completed ”, I suspect a representation problem. If a completely new status appears, I do not automatically squeeze it into the nearest familiar category. The underlying process may have changed. The unexpected category could be useful evidence of that change.

Step one: resolve duplicates according to the key

I distinguish an identical copy of a complete record from conflicting records under the same key. Two identical lines might come from loading a file twice. Two orders with the same identifier but different amounts need a different response. Keeping whichever row happens to come first is not a general conflict-resolution policy.

For the exercise below, I assume that I have confirmed the source rule: an identifier represents one order, and an identical repetition is a technical copy. Under those conditions, I keep one copy. In an event dataset, two apparently identical observations could still be separate events. I would need their event identifiers or another reliable way of telling them apart.

The software implements the rule I choose. In pandas, drop_duplicates allows selection of comparison columns and a policy for retaining repetitions. Choosing the columns that establish identity remains my responsibility. When values conflict, I usually separate the records for investigation and explain the scale of the issue to someone who understands the source.

I also check whether repetitions appeared only after joining tables. A valid orders table can multiply when several customer contacts are attached to each order. In that case, I fix the join rather than hide its effect with another deduplication step. I explore that problem in the article on SQL joins.

Step two: give missing values a meaning

An empty field tells me that a value is absent in that location. The reason may vary: the question does not apply, a person did not answer, the system failed to transmit something, or the process has not finished yet. An empty return identifier alone does not prove that no return occurred. That interpretation depends on how the process works and whether the source is complete.

Replacing every missing value with zero is an attractive shortcut. For prices, it invents free purchases. For delivery times, it invents instant deliveries. For customer ratings, it converts a missing response into a very specific rating. The table looks more complete while the numbers quietly acquire a different meaning.

The pandas missing-data guide explains different missing-value representations and the isna and notna checks. My analytical decision comes first: preserve the absence, exclude a record from a particular measure, or apply a justified imputation. If I fill a value, I mark it so that an estimate can later be distinguished from an observation.

A collection failure does not disappear when I delete the empty records. Deletion may simply conceal the measurement gap. I check whether missing values cluster by date, channel or order type, then state which data is actually available. If I cannot explain the gap, I narrow the conclusion. Another decimal place cannot repair missing evidence.

Step three: align representation, units and types

“Warszawa”, “warszawa” and “ Warszawa ” may describe the same city. Trimming surrounding spaces and aligning case can help. Broader mappings need more care. An abbreviation such as “WA” can mean different things in different sources. I use an explicit mapping that someone can inspect and correct rather than an undocumented collection of replacements.

I do not convert every numeric-looking column into a number. Product code 0012 may require its leading zeros, just as a postal code might. Meaning determines the type. For dates, I establish the expected format and time-zone context. A value such as 03/04/2026 remains ambiguous without further information about its origin.

Amounts need currencies and measurements need units. Changing a column heading from dollars to another currency does not perform a conversion. Even within one currency, gross and net amounts must be distinguished. In this exercise I store amounts as whole minor units, so the calculation can be followed without introducing fractional representation issues.

“Normalization” has several meanings. In database design it concerns table organization and dependencies. In feature preparation it can refer to changing scale. Here I am primarily making representation and types consistent. Keeping those meanings separate prevents a meeting where everyone agrees enthusiastically while planning completely different operations.

Step four: validate relationships and business rules

A valid type does not guarantee a sensible value. A delivery date can precede the order date even though both fields contain valid dates. A status can belong to the accepted vocabulary while contradicting another field. Alongside individual-column checks, I need checks of relationships between values.

I first express a rule in business language. For example: “In this dataset, a completed order must have a completion date.” Only then do I choose a spreadsheet formula, a SQL condition or some code. Someone familiar with the process can review that sentence without reading a query. Occasionally, the conversation reveals an exception that changes the rule.

A negative amount could indicate an error, a refund or an adjustment. Before declaring it invalid, I establish the table’s scope. A table containing only new orders has different expectations from a settlement ledger. A rule should have a description, someone responsible for its meaning and a response when it fails. Otherwise the error list becomes another file that nobody opens.

Step five: investigate unusual observations

A large order may belong to a wholesale customer. A sudden inventory drop may represent a real stockout. An unusual record can contain exactly the information the analysis was meant to uncover. Smoothing away every unusual observation can produce a reassuring chart while hiding an operational problem.

NIST’s discussion of outliers emphasizes investigation and the assumptions behind detection methods. I treat a threshold as a prompt to check a record. I compare it with a relevant group, inspect the source and then decide what to do. The same threshold may be inappropriate for both retail and wholesale purchases.

If an error is confirmed, I correct it using evidence or exclude it from a clearly defined analysis. If it remains unresolved, I may show results with and without the value as a sensitivity analysis. I label both versions. The reader should see the effect of my decision rather than wonder why the headline figure changed between meetings.

A small example with a visible audit trail

I have six rows. Order A appears twice with the same amount of 10000 minor units. B has 20000, C has no recorded amount, D has zero and E has 500000. They all use one currency, PLN. For this exercise, I confirm that A is a technical copy, D is a valid free order and E is a valid large purchase.

rows = [
    ("A", 10000), ("A", 10000), ("B", 20000),
    ("C", None), ("D", 0), ("E", 500000),
]
unique = list(dict.fromkeys(rows))
known = [amount for _, amount in unique if amount is not None]
print(len(rows), len(unique), len(known))  # 6 5 4
print(sum(known))  # 530000
print(sum(known) / len(known))  # 132500.0

The Python code removes identical pairs, following the exercise’s agreed rule. It does not resolve different amounts under the same identifier. Before using this approach elsewhere, I must check the key and the fields. The article on Python data types explains the underlying behavior of None and other basic values.

After cleaning, I have five orders but only four known amounts. Their total is PLN 5300 and their mean is PLN 1325. I still cannot determine the complete value of all five orders without C’s amount. D’s zero belongs in the mean because it is a known measurement. C’s missing amount does not acquire an invented zero.

Replacing the missing amount with zero would produce a mean of PLN 1060. In a separate scenario, without filling the missing value, removing the large order E would leave a mean of PLN 100 among the remaining known amounts. These operations materially change the answer. When reporting a mean, I therefore state which records contributed to its denominator.

Explain how the result changed

My short audit note records six input rows, one removed copy, five orders after deduplication, one unknown amount, a retained zero and a checked large value. Amount completeness is four out of five, or 80 percent. I separately state that the total covers known amounts only. The number and its limitation travel together.

This makes reconciliation straightforward. If someone expects six orders, I can identify the copy. If they ask for complete sales value, I can point to the unresolved missing amount. I do not have to reconstruct a sequence of clicks from memory. On a larger dataset, a similar log should come from the process and include affected record identifiers and decision reasons.

A problematic record should not vanish without a trace. It may remain in a separate investigation dataset or carry a quality flag. The implementation depends on the intended use and the organization’s rules. I still want to explain how much data was set aside, which part of the business it concerns and whether its absence changes the report’s interpretation.

Decide which problems prevent an answer

Before setting alerts, I establish which errors block the intended answer. A missing order identifier can prevent reliable deduplication, while a missing optional note may have no effect on the total. Those problems deserve different priorities. I consider their impact on the question, their frequency and whether information can be recovered from the source.

A city-name error affecting two small orders may barely change the store-wide result while substantially changing the result for that city. I therefore assess quality at the reporting level. The same physical file can support one chart and be inadequate for another. A general label saying “clean” cannot capture every intended use.

I agree with the recipient what should happen when a threshold is exceeded: stop publication, display a warning or publish only selected measures. A threshold alone does not resolve anything. Someone needs to receive the information and know what response is expected. Otherwise automation produces the same daily warning until everyone stops noticing it.

Finally, I distinguish repairing the report from repairing the source. A mapping can combine three city-name variants today, while a better input form can reduce future errors. I record the cause and a proposed process improvement as well. Cleaning helps complete the immediate analysis, and the observations gathered along the way can reduce the need to repeat the same repairs next month.

Check whether the process is repeatable

A correct result today is insufficient if another file arrives tomorrow. I record the sequence of steps and the conditions that should stop processing. A missing required column, a new currency or a sudden increase in key conflicts deserves an explicit signal. Quietly skipping those problems merely postpones their discovery.

I also try a second pass over already prepared data. If it deletes more rows or converts units again, I investigate. For many cleaning operations, I expect a second pass to leave the valid result unchanged. Exceptions need an explanation, such as fetching a newer reference mapping or an updated source snapshot.

Finally, I compare the result with an independent reference: a small manually calculated sample, a control report or a known event count. Agreement does not prove that every record is correct, but disagreement provides a useful starting point for investigation. I prefer discovering it before the dashboard presentation. Publicly finding double-counted sales has limited entertainment value.

Exercise: add a conflict and a missing group

Add another A with an amount of 12000 to the example. Consider whether comparing entire rows still checks the expected key. The data now contains two different amounts for one order. Without further evidence, do not automatically select the larger, smaller or last value. Write down the information you would need to resolve the conflict.

Next, imagine that every order from one channel has an unknown amount. Overall completeness might look acceptable while a channel comparison becomes seriously limited. Calculate completeness by group as well as for the entire dataset. This illustrates why a single quality percentage can provide reassurance too early.

For your own project, start with one table, one measure and five written rules. For each rule, record the check result, the decision and the number of affected records. Then build the visualization. When you can explain the route from source to number, data cleaning becomes part of an argument that another person can inspect and challenge.