Two columns that print almost identically. Look at the dtype, then at what the same three methods return.
import pandas as pd text = pd.Series(['100', '9', '25']) # dtype: object real = pd.Series([100, 9, 25]) # dtype: int64 text.sum() # -> '100925' concatenation, not addition real.sum() # -> 134 text.max() # -> '9' alphabetical: '9' > '25' > '100' real.max() # -> 100 text.mean() # raises TypeError — the one that does tell you # The conversion, and what it does with something it cannot read: pd.to_numeric(pd.Series(['1200', 'n/a']), errors='coerce') # -> 0 1200.0 # 1 NaN unreadable becomes missing, no exception pd.Series(['1200', 'n/a']).astype(float) # raises ValueError: could not convert string to float: 'n/a'
- object dtype means 'a column of Python objects', usually strings
One 'n/a' in a million-row column is enough: pandas cannot store it as an int64, so the whole column becomes object and every value in it stays a string. Nothing in how it prints tells you — the values have no quotes in a frame’s output. frame.dtypes is the two-second check, and 'object' on a column you expect to add up is always worth a second look.
- The dangerous methods are the ones that still work
sum concatenating is the loud failure because the result is obviously not a number. max comparing alphabetically is the quiet one — it returns something that looks like a plausible figure, and '9' beating '1200' is a bug you can ship. mean raising TypeError is the kindest outcome of the three.
- to_numeric and astype fail differently, and you have to choose
astype(float) raises on the first value it cannot parse, which is right when an unparseable value means the file is broken. to_numeric(errors='coerce') turns it into NaN and carries on, which is right when bad rows are expected and you intend to report them. What is never right is coercing and then filling with zero — that silently invents revenue of nothing for a row you never actually read.
print(frame.dtypes) before anything else. If the amount column says object, every numeric method on it is operating on strings — and sum on strings is concatenation, which is exactly what the finance lead is looking at.
pd.to_numeric(column, errors='coerce') converts what it can and puts NaN where it cannot. That gives you two things at once: the values to total, and — via .isna() — the rows to list as unreadable.
to_numeric will not accept '1,200', so take the separators out first with .str.replace(',', '', regex=False). Then total the readable values with .sum(), count them with .notna().sum(), and pick the unreadable raw text out of the *original* column with a boolean mask — not out of the converted one, where the text is already gone.
import pandas as pd def revenue_summary(frame): raw = frame['amount'] cleaned = raw.str.replace(',', '', regex=False) amounts = pd.to_numeric(cleaned, errors='____') readable = amounts.____() return { 'total': round(float(amounts.sum()), 2), 'counted': int(readable.____()), 'unreadable': list(raw[____readable]), }
import pandas as pd
def revenue_summary(frame):
raw = frame["amount"]
# The separator is what made the column text; to_numeric will not take it.
# regex=False because a comma is a literal here, not a pattern.
cleaned = raw.str.replace(",", "", regex=False)
# coerce: anything unparseable becomes NaN instead of raising, because a
# bad row is something this report is meant to survive and mention.
amounts = pd.to_numeric(cleaned, errors="coerce")
readable = amounts.notna()
return {
# sum() skips NaN, so this is the total of the readable rows only.
"total": round(float(amounts.sum()), 2),
"counted": int(readable.sum()),
# From raw, not from amounts — the text is what a human needs to see.
"unreadable": list(raw[~readable]),
}
Every pandas aggregation defaults to skipna=True, so the NaN rows simply do not participate — the total is already the total of what was readable. fillna(0) before summing gives the same number here and a different count, a different mean and a different min, because a filled zero is a row that took part. Leaving the NaN in place is what keeps 'unreadable' and 'counted' honest.
readable.sum() counts the True values — booleans add as 1 and 0, which is why counting a condition needs no loop. raw[~readable] then selects the other rows from the untouched original, with ~ as elementwise not. Naming the mask rather than writing amounts.notna() twice is what makes the two uses obviously complementary.
to_numeric returns a new Series with the same index, so the boolean mask lines up against the original column row for row. That is the alignment from the previous exercise, being useful instead of surprising — and it is why you can convert a column, decide something from the conversion, and then act on the original without tracking positions yourself.
errors='coerce' is right here because unreadable rows are expected and the report’s job is to name them. It is wrong when they are not: if the file is supposed to be clean, coercing turns a broken export into a quietly smaller total, and the first person to notice will be whoever reconciles the quarter. There, astype(float) is better — it stops on the first bad value with the value in the message. Better still is to make the decision once, at the read: read_csv has converters and dtype, and a column that arrives as float64 cannot develop this bug later. Fixing it at the edge of the system beats fixing it in every function that touches the column.
- Report the row numbers of the unreadable rows alongside their text, so somebody can find them in the file.
- Accept a currency symbol as well as a separator ('£1,200') and decide where that cleaning belongs — here, or at the read.
- Add a strict=True argument that raises on the first unreadable row instead of collecting them, and see which of the two you would want by default.