Turning Money Strings into Numbers

Ask the messy export for its total sales and you get something strange. No error, no number: a long string starting '$65.0018.0033.00...'. The total column looks numeric in a spreadsheet, but nearly half its values start with a dollar sign, so pandas stored the whole column as text, and adding text joins it. Money arriving as text is one of the most common problems in exported data, and it needs a deliberate, checked fix.

Spot it: dtype object on a number column

import pandas as pd

messy = pd.read_csv("orders_messy.csv")
print(messy["total"].dtype)
print(messy["total"].head(3).tolist())
print(repr(messy["total"].sum()[:40]))
print(messy["total"].str.startswith("$").sum(), "values start with $")

The inspection routine from Lesson 2.2 catches this immediately: info() would show total as object where you expected float64. Whenever a column that should hold numbers is object, at least one value contains something that is not a number. Here, 76 of 168 values start with $.

Fix it: strip the symbols, then convert

Remove every character that is not part of the number, then convert the clean text:

import pandas as pd

messy = pd.read_csv("orders_messy.csv")
total = (
    messy["total"]
    .str.strip()
    .str.replace("$", "", regex=False)
    .str.replace(",", "", regex=False)
    .astype(float)
)
print(total.dtype, round(total.sum(), 2))
print(total.describe().round(2))

Wrapping a chain in parentheses lets you put one step per line, which is easier to read and to comment out while debugging. The thousands separator is removed too: this export happens to have no totals above 1,000 dollars, but the next one will, and "1,234.50" would break astype(float).

describe() is the sanity check. Totals run from 18 to 942.90 dollars with a median of 72, which matches Cartwheel’s catalogue: the cheapest product is a jump rope at 18 dollars, and the largest line is two standing desks. A minimum of 0 or a maximum of 94,290 would tell you a decimal point went missing.

The 23,500.67 sum is not a revenue figure yet: the export still contains nine duplicate rows. You will remove them in Lesson 3.5.

The dollar sign trap

str.replace can also work with regular expressions, a pattern language in which some characters have special meanings. $ is one of them: it means “the end of the text”. Watch what happens when you ask for a pattern by accident:

import pandas as pd

messy = pd.read_csv("orders_messy.csv")
as_pattern = messy["total"].str.replace("$", "", regex=True)
print(as_pattern.head(1).tolist())   # still '$65.00'
try:
    pd.to_numeric(as_pattern)
except ValueError as error:
    print("ValueError:", error)

The pattern $ matched the empty position at the end of each string and replaced it with nothing, so every dollar sign survived. In pandas 2, str.replace defaults to regex=False, but older tutorials and older pandas versions defaulted to patterns, and you will meet code written either way. Writing regex=False explicitly makes the intent clear to every reader and every version. If you do want a pattern, escape the special character or use a character class: str.replace(r"[$,]", "", regex=True) removes both symbols in one step.

When some values will not convert

astype(float) is all or nothing: one bad value and the whole conversion fails. pd.to_numeric with errors="coerce" converts what it can and turns the rest into NaN. That is useful and dangerous in equal measure, so it always comes with a follow-up check:

import pandas as pd

raw = pd.Series(["$1,234.50", "18.00", "N/A", "€89.00", " $65.00 "])
cleaned = raw.str.strip().str.replace("$", "", regex=False).str.replace(",", "", regex=False)
amount = pd.to_numeric(cleaned, errors="coerce")
print(amount)

failed = amount.isna() & raw.notna()
print("Could not convert:", raw[failed].tolist())

The check lists exactly what failed: N/A, a genuinely missing value, and €89.00, a real sale in a currency your cleaner did not expect. Those need different treatment. The first stays missing; the second needs a conversion rule, or at least a conversation with whoever produced the file. Coercing without looking would have quietly dropped 89 euros of sales from every total.

Make it a function

You will clean money columns again, in this course and in your job. Put the logic where it can be reused and tested:

import pandas as pd

def parse_money(values):
    """Convert text like ' $1,234.50' to floats; unparseable values become NaN."""
    cleaned = (
        values.astype("string")
        .str.strip()
        .str.replace("$", "", regex=False)
        .str.replace(",", "", regex=False)
    )
    return pd.to_numeric(cleaned, errors="coerce")

messy = pd.read_csv("orders_messy.csv")
messy["total"] = parse_money(messy["total"])
print(messy["total"].dtype, messy["total"].isna().sum(), "unparsed")

astype("string") first makes the function safe on columns that pandas already read as numbers (when a file happens to have no $ at all), because .str methods only work on text. Because the input is a string column, to_numeric returns the nullable Float64 dtype from Lesson 3.1, with <NA> wherever a value failed. The function returns a new Series, and the caller decides where to store it, the same principle as returning rather than printing in Lesson 1.5.

In the exercise you build the converter and use it on the export. Next comes the hardest column in the file: dates written three different ways.