A reading column can contain a true zero, a blank, and text such as 3 units. Before averaging the readings, check which entries pandas can convert and which ones you need to review.

Keep the original values and create a numeric column with pd.to_numeric. Flag empty readings before conversion so you can identify nonblank values that failed to parse.

Readings 12.5 and 0 parse as numbers. Three spaces and None are blank. The value 3 units requires review.
Preserve raw readings when you add a numeric column and flag entries for review.Credit: MathBlog

Check the raw readings

Five rows in a fictional reading log
Row Raw reading Expected status
1 12.5 Parsed
2 0 Parsed
3 Three spaces Blank
4 3 units Review
5 None Blank

Convert a copy and flag failed parses

Trim spaces from a temporary string Series. An entry is blank if it was already missing or becomes empty after trimming. With errors="coerce", entries that pandas cannot parse become missing in the numeric column. Review the rows where conversion produced a missing value from a nonblank entry.

import pandas as pd

log = pd.DataFrame({
    "row": [1, 2, 3, 4, 5],
    "reading_raw": ["12.5", "0", "   ", "3 units", None],
})
clean = log["reading_raw"].astype("string").str.strip()
blank = clean.isna() | clean.eq("").fillna(False)
log["reading"] = pd.to_numeric(clean.mask(blank), errors="coerce")
invalid = ~blank & log["reading"].isna()

valid = log.loc[log["reading"].notna(), "reading"].tolist()
blank_ids = log.loc[blank, "row"].tolist()
review = log.loc[invalid, ["row", "reading_raw"]].to_dict("records")
print("Parsed readings:", valid)
print("Blank rows:", blank_ids)
print("Invalid rows:", review)

The example was run in Python 3.11.3 with pandas 2.3.3.

Parsed readings: [12.5, 0.0]
Blank rows: [3, 5]
Invalid rows: [{'row': 4, 'reading_raw': '3 units'}]

Review the original value before changing it

Row 2 keeps its numeric zero. Rows 3 and 5 have no source reading. Row 4 produces a missing numeric result, and reading_raw still shows what was entered. Check the source before extracting 3 from 3 units.

A negative number may parse successfully even when the measurement should be nonnegative. Apply any range or unit rules after conversion. For empty entries, see how to find and fill missing values in pandas.

Further reading