Keep the original data and write down what counts as valid before cleaning it. For a temperature column, separate a measured zero, a missing reading and text the parser cannot read. Flag the latter two states separately so a report can show how many records it could actually use.
Tell a missing reading from an invalid one
Imagine a community greenhouse logs soil temperature each morning. A zero could be an actual reading. A blank could mean a sensor was off. The word error could mean the logger recorded a failure. A temperature in Fahrenheit in a column labeled Celsius could be a machine configuration error. The first three are different data states. The last one remains an unverified assumption until you have a device record or another way to establish the unit.
Create a short data dictionary with column names, expected units, valid formats and missing codes. Keep this alongside the original export. If one producer uses the string NA as a legitimate site code and another uses it to mean unavailable, a global find-and-replace will erase valid sites. Read according to the contract for each column and source.
Flag failed conversions with pandas
One useful pattern stores the raw text, the parsed value and a status. The parsed column can be summarized, while the raw column makes reconciliation possible. The example assumes the producer explicitly uses an empty string and NA as missing temperature codes.
import pandas as pd
data = pd.DataFrame({"temperature_raw": ["0", " 12.5 ", "", "NA", "error"]})
raw = data["temperature_raw"].str.strip()
missing = raw.isin(["", "NA"])
parsed = pd.to_numeric(raw.mask(missing), errors="coerce")
data["temperature_c"] = parsed
data["status"] = "recorded"
data.loc[missing, "status"] = "missing"
data.loc[~missing & parsed.isna(), "status"] = "invalid"
assert data["status"].value_counts().to_dict() == {
"recorded": 2, "missing": 2, "invalid": 1,
}
The example records an input failure without throwing away the row. In production, validate bounds and reject nonfinite numbers if your instrument’s contract excludes them. A value that parses successfully can still be impossible, in the wrong unit, or tied to the wrong sensor. If the next step is a daily mean, decide whether there is a minimum coverage threshold and how to show days that fall below it.
Decide what makes a row a duplicate
Two identical temperatures from the same sensor on different days are separate observations. Repeated imports of the same sensor ID and timestamp may be duplicates, but multiple measurements within one second can be legitimate if the recorder samples faster than that. Identify the key by asking which fields distinguish one intended observation from the next.
If two records have the same key and conflicting values, avoid silently keeping the first unless input order is a reliable part of the source protocol. Look for an authoritative revision number or a correction timestamp. If none exists, flag both records for review. Record both the number of duplicate records removed and the number of conflicted keys held out. These counts show different kinds of data quality problems.
Normalize the values that should match
For a reporting label, leading and trailing spaces are often accidental. A stable identifier might permit exact spaces or distinctions in letter case. Preserve the original field and use a separate comparison key for matches. If you normalize labels to identical text, check for collisions such as two separately named facilities becoming one. A mapping table of confirmed aliases can preserve those distinctions.
Ask the file producer how to interpret an ambiguous date or currency. If the producer confirms that 03/04/2026 means April 3, use the day-first format for that source and flag rows that violate it. See how to parse a pandas date column and count failed conversions.
Follow the effect of a cleaning rule into the answer
Consider a greenhouse where the outdoor baseline still logs measurements during a sensor outage in the warmest growing bay. If you average only the observed temperatures, the monthly result has a larger share of outdoor readings than planned. The output can be internally consistent while failing to represent the original mix of locations. Compare expected and observed coverage for each group you intend to summarize.
Ask how a change affects the population the analysis describes. If missing measurements are concentrated during maintenance, showing a separate outage category may answer a better question than filling the gaps. A missing flag does not fix bias in the average. It makes the conditions under which you calculated the average visible. For a decision such as comparing different growing bays, restrict all groups to a comparable observation period or explain why that would change the question.
Use separate checks for plausibility and correction. Flagging an unlikely value is a reasonable first step. Replacing it requires stronger evidence, such as identifying a documented calibration or unit mistake. If some replacements are well supported and others are guesses, keep the categories distinct and measure their contribution to the result.
Reconcile the result before analysis
Compare totals before and after cleaning. For a simple partition, raw records should match usable plus dropped plus held-for-review records. Document the order of the filters if records can fail more than one check. Compare summary counts by date or source to see whether cleaning disproportionately affected one group. If records from the coldest week disappear because a logging format changed, the mean for the month may rise for a reason unrelated to the greenhouse.
Create a tiny fixture that contains a valid zero, an explicit missing marker, an invalid value and a duplicate conflict. Run it before each rule change. Publish the disposition of rejected rows alongside the cleaned dataset if the recipient can appropriately access them. A well-explained “coverage is incomplete” is often more useful than an apparently finished table with its assumptions hidden.
Review a handful of accepted rows too. If a new parser drops the comma and interprets 12,5 as 125 instead of twelve and a half, rejection statistics may be silent. Compare source and normalized values for examples that have known answers. When a value crosses from one unit to another, verify the conversion using two distinct numbers, including one outside the range of values you usually expect.
Keep a short change log when you revise a rule. A report that originally excluded records with unmatched sensor identifiers might later classify them from a corrected device inventory. State when the rule changed and how many old records now qualify. That helps readers distinguish a real change in temperature from an improvement in the available metadata.