Does a column full of numbers refuse to be compared to a number?
pd.Series(['10', '20']) > 15
TypeError: '>' not supported between instances of 'str' and 'int'
Those are not numbers. They are strings that contain digits, and Python has no opinion about whether the text '10' is greater than the integer 15. Convert first:
pd.to_numeric(pd.Series(['10', '20'])) > 15
0 False
1 True
dtype: bool
Why the column was text
Something upstream decided it. The usual culprits, in the order I meet them:
- A stray non-numeric value. One
'n/a', one'-', one footnote marker, andread_csvtypes the whole column as text. - Formatting. Currency symbols, thousands separators and percent signs are text characters.
'$1,200'cannot be a number. - Leading zeros that matter. Zip codes and product codes are often read as text on purpose, sometimes by an explicit
dtype=str. - Excel. A column formatted as text in the spreadsheet arrives as text.
dtypes settles which one you have, and .unique() on a small column usually shows the offender immediately.
Converting without losing the bad rows
astype(float) raises on the first value it cannot parse, which tells you there is a problem but not where. to_numeric with errors='coerce' converts what it can and marks the rest:
revenue = pd.to_numeric(raw['revenue'], errors='coerce')
raw[revenue.isna()]
That gives you the rows that failed, which is what you need to decide whether they are junk to drop or a format to handle. Silently coercing to NaN without looking is how a total quietly comes out wrong.
For known formatting, strip it before converting:
raw['revenue'].str.replace(r'[$,%]', '', regex=True).astype(float)
And for thousands separators specifically, read_csv(..., thousands=',') handles it at load time, which is tidier than repairing it afterwards.
Sorting has the same problem, quietly
The comparison error is loud. Sorting is not:
pd.Series(['10', '9', '100']).sort_values()
Text sorts alphabetically, so '10' comes before '9', and '100' before both. No error, just an order that is wrong in a way nobody notices until a chart looks strange. If a sort gives an unexpected order, check the dtype before checking anything else.
The mistakes I see most often
Converting the display rather than the data. Formatting a column to look numeric does not change its dtype.
Using astype(int) on a column with missing values. That raises a different error about non-finite values. Convert to float first, or handle the nulls.
Assuming a successful read_csv means correct types. It always succeeds. df.dtypes immediately after loading is a habit worth having.
Practice
See to_numeric in Pandas for converting text to numbers, astype in Pandas for dtype changes generally, and read_csv in Pandas for getting the types right at load time.