Converting types: numbers, dates and Indian formats

Numbers and dates often arrive as text: "1,200.50", "(75)", "05/01/2026". Converting them is parsing, applying a rule that says what the text means, and pandas has two converters for it: pd.to_numeric for numbers and pd.to_datetime for dates. Both stop at the first value they cannot read, and both turn failures into missing values instead when you pass errors="coerce".

The error is not the danger. The danger is the conversion that succeeds and is wrong: a day-first date read month first, a currency column left as text, an unreadable amount quietly counted as missing. So the work has three steps: clean the text with a rule you can state, convert it with an explicit format, then count and look at every value that failed. This lesson does each step on numbers, on day-first dates and on Indian amount formats.

Text to numbers with to_numeric

import pandas as pd

raw = pd.Series(["1200", "1,200.50", "$350", "(75)", "12.5%", "", "n/a", "-40", "3.2e3"])

# errors="raise" (the default) stops at the first value it cannot read.
try:
    pd.to_numeric(raw)
except ValueError as e:
    print("ValueError:", e)

# errors="coerce" turns every unreadable value into NaN. Count the failures and look at each one.
numbers = pd.to_numeric(raw, errors="coerce")
report = pd.DataFrame({"raw": raw, "number": numbers, "failed": numbers.isna() & raw.notna()})
print(report.to_string(index=False))
print("converted:", int(numbers.notna().sum()), "of", len(raw))

ValueError: Unable to parse string "1,200.50" at position 1
     raw  number  failed
    1200  1200.0   False
1,200.50     NaN    True
    $350     NaN    True
    (75)     NaN    True
   12.5%     NaN    True
             NaN    True
     n/a     NaN    True
     -40   -40.0   False
   3.2e3  3200.0   False
converted: 3 of 9

to_numeric reads plain digits, a minus sign and scientific notation (3.2e3), and nothing else. The default, errors="raise", stops at the first value it cannot read and names its position: here 1,200.50, because a comma is not part of a number to it. errors="coerce" converts what it can and turns the rest into NaN, so this column ends with 3 numbers out of 9.

Look at what failed before you move on, because the failures are of two kinds. A blank and n/a are missing values, which the previous lesson handles. 1,200.50, $350, (75) and 12.5% are real amounts in a format that needs a rule first. Coercing all six to NaN would throw away four amounts and report them as missing.

Clean the text first, with a rule you can state

Each format needs a rule, and each rule is one string operation before the conversion:

import pandas as pd

raw = pd.Series(["1200", "1,200.50", "$350", " (75) ", "€ 1.000", "", "n/a", "-40"])

text = raw.str.strip()
negative = text.str.fullmatch(r"\(.*\)")                 # accounting style: (75) means -75
digits = text.str.replace(r"[()$,\s]", "", regex=True)   # drop brackets, $ and thousands commas
amount = pd.to_numeric(digits, errors="coerce").mask(negative, lambda x: -x)

report = pd.DataFrame({"raw": raw, "cleaned": digits, "amount": amount})
print(report.to_string(index=False))

# What failed, and why it failed, goes in the data log before anyone uses the column.
unreadable = amount.isna() & text.ne("") & text.str.casefold().ne("n/a")
print("unreadable:", raw[unreadable].tolist())

     raw cleaned  amount
    1200    1200  1200.0
1,200.50 1200.50  1200.5
    $350     350   350.0
   (75)       75   -75.0
 € 1.000  €1.000     NaN
                     NaN
     n/a     n/a     NaN
     -40     -40   -40.0
unreadable: ['€ 1.000']

Accounting exports write negative amounts in brackets, so the rule first records which values are bracketed, then removes the brackets, the dollar sign, commas and spaces in one regular expression, converts, and makes the bracketed values negative. 1,200.50 becomes 1200.5 and (75) becomes −75.

One value stays unreadable on purpose: € 1.000. In German, for example, the point separates thousands and the comma marks decimals, as the Unicode locale data records, so this is one thousand euros, while the same text in a dollar file would be one euro. A rule that guessed would be wrong for one of them. Give such files their own rule, or read them with read_csv(..., thousands=".", decimal=","), and keep listing anything unreadable for review.

read_csv can apply the simplest rules while it reads: thousands="," removes a thousands separator, decimal="," reads decimal commas, and dtype={"account_no": "str"} keeps codes such as account numbers and postal codes as text, so their leading zeros survive.

Indian number formats

import io

import pandas as pd

# Lakh grouping puts commas after the first three digits and then every two: 1,23,456.78
good = 'id,fee\n1,"1,23,456.78"\n2,"5,000"\n3,750\n'
fees = pd.read_csv(io.StringIO(good), thousands=",")
print(fees["fee"].tolist(), fees["fee"].dtype)

# One cell with the rupee sign keeps the whole column as text.
bad = 'id,fee\n1,"1,23,456.78"\n2,"₹5,000"\n3,750\n'
fees = pd.read_csv(io.StringIO(bad), thousands=",")
print(fees["fee"].tolist(), fees["fee"].dtype)

# Devanagari digits: Python's int() reads them, pandas' to_numeric does not.
print(int("१२०००"), pd.to_numeric(pd.Series(["१२०००"]), errors="coerce").tolist())

[123456.78, 5000.0, 750.0] float64
['1,23,456.78', '₹5,000', '750'] str
12000 [nan]

Words that multiply a number need a small parser of their own. A regular expression that matches the whole cell, with one group for the number and one for the word, keeps the rule readable and refuses anything it does not know:

import re

import pandas as pd

WORDS = {"thousand": 1_000, "lakh": 100_000, "lakhs": 100_000, "crore": 10_000_000, "crores": 10_000_000}
PATTERN = re.compile(r"(?:₹|rs\.?|inr)?\s*([\d,]*\.?\d+)\s*([a-z]*)")


def parse_amount(text):
    """'₹1,20,000', '1.2 lakh', 'Rs. 45,500' -> a number of rupees; None for a blank; ValueError otherwise."""
    if text is None or not text.strip():
        return None
    m = PATTERN.fullmatch(text.strip().casefold())
    if not m or (m.group(2) and m.group(2) not in WORDS):
        raise ValueError(f"cannot read {text!r}")
    return float(m.group(1).replace(",", "")) * WORDS.get(m.group(2), 1)


# An invented scholarship sheet: one amount written six ways, plus a blank and a typo.
raw = pd.Series(["₹1,20,000", "1.2 lakh", "120000", "Rs. 1,20,000", "INR 1.20 lakh", "0.012 crore", "", "1.2 lac"])

parsed, problems = [], []
for value in raw:
    try:
        parsed.append(parse_amount(value))
    except ValueError as e:
        parsed.append(None)
        problems.append(str(e))

report = pd.DataFrame({"raw": raw, "rupees": pd.Series(parsed, dtype="float64")})
print(report.to_string(index=False))
print("problems:", problems)

          raw   rupees
    ₹1,20,000 120000.0
     1.2 lakh 120000.0
       120000 120000.0
 Rs. 1,20,000 120000.0
INR 1.20 lakh 120000.0
  0.012 crore 120000.0
                   NaN
      1.2 lac      NaN
problems: ["cannot read '1.2 lac'"]

Dates: say what the text means

Dates are the bigger trap, because a wrong reading produces a valid date. 05/01/2026 is the fifth of January when written day first and the first of May when written month first, and both are standard somewhere: the Unicode locale data (CLDR) writes short dates as dd/MM/yy for English in India and as M/d/yy for English in the United States. pandas cannot tell, so without a format it guesses from the first value, and its default, dayfirst=False, prefers month first:

import warnings

import pandas as pd

# Payment dates written day first.
paid = pd.DataFrame({
    "paid_on": ["05/01/2026", "06/01/2026", "20/01/2026", "02/02/2026", "13/02/2026"],
    "amount": [500, 700, 300, 400, 600],
})

# The first value fits month-first, so pandas guesses %m/%d/%Y from it and fails on the third value.
try:
    pd.to_datetime(paid["paid_on"])
except ValueError as e:
    print("ValueError:", str(e).split(". ")[0])

# On the first two values alone the same guess succeeds silently: 5 and 6 January become 1 May and 1 June.
print(pd.to_datetime(paid["paid_on"].head(2)).dt.strftime("%Y-%m-%d").tolist())

# Row order changes the guess: with 20/01/2026 first, pandas reads day first and only warns.
with warnings.catch_warnings(record=True) as caught:
    warnings.simplefilter("always")
    guessed = pd.to_datetime(paid["paid_on"].iloc[[2, 0, 1]])
print(guessed.dt.strftime("%Y-%m-%d").tolist(), "| warning:", caught[0].category.__name__)

# Monthly totals with the wrong reading (month first, failures dropped) and with the right one.
wrong = pd.to_datetime(paid["paid_on"], format="%m/%d/%Y", errors="coerce")
right = pd.to_datetime(paid["paid_on"], format="%d/%m/%Y")
print("month first:", paid.groupby(wrong.dt.strftime("%Y-%m"))["amount"].sum().to_dict())
print("day first:  ", paid.groupby(right.dt.strftime("%Y-%m"))["amount"].sum().to_dict())
print(right.dtype)

ValueError: time data "20/01/2026" doesn't match format "%m/%d/%Y"
['2026-05-01', '2026-06-01']
['2026-01-20', '2026-01-05', '2026-01-06'] | warning: UserWarning
month first: {'2026-02': 400, '2026-05': 500, '2026-06': 700}
day first:   {'2026-01': 1500, '2026-02': 1000}
datetime64[us]

Read the output from the top. With all five values, pandas guesses %m/%d/%Y from 05/01/2026 and raises ValueError at 20/01/2026, which cannot be month first. With only the first two values, the same guess succeeds silently and returns the first of May and the first of June. Put 20/01/2026 first and the guess flips to day first with only a UserWarning. The same column read in a different order gives different dates: a result that depends on row order is not a result.

The last two lines show the cost. Read month first, with failures coerced, the payments move into February, May and June, and the 900 paid on two dates that cannot be month first disappear from the totals; read day first, the January total is 1,500 and the February total 1,000. Always pass the format, format="%d/%m/%Y", or at least dayfirst=True. A file in ISO 8601 form, 2026-01-05, has only one reading, which is why it is the safest format to write data in.

Count and inspect every failure

With an explicit format, a value that does not fit is a failure, and errors="coerce" turns it into NaT:

import pandas as pd

raw = pd.Series(["05/01/2026", "31/02/2026", "2026-01-07", "", "soon", "15/13/2026", "09/01/26"])

dates = pd.to_datetime(raw, format="%d/%m/%Y", errors="coerce")
report = pd.DataFrame({"raw": raw, "date": dates.dt.strftime("%Y-%m-%d"), "failed": dates.isna() & raw.ne("")})
print(report.to_string(index=False))
print("failed:", int(report["failed"].sum()), "of", int(raw.ne("").sum()), "non-blank values")

# Two-digit years follow the C library's rule: 69-99 are 1900s, 00-68 are 2000s.
print(pd.to_datetime(pd.Series(["09/01/26", "09/01/68", "09/01/69"]), format="%d/%m/%y").dt.strftime("%Y-%m-%d").tolist())

# pandas 3 stores parsed text as microseconds: integer conversions are 1,000 times smaller than in pandas 2.
one_day = pd.to_datetime(pd.Series(["1970-01-02"]))
print(one_day.dtype, one_day.astype("int64").iloc[0], one_day.dt.as_unit("ns").astype("int64").iloc[0])

       raw       date  failed
05/01/2026 2026-01-05   False
31/02/2026        NaN    True
2026-01-07        NaN    True
                  NaN   False
      soon        NaN    True
15/13/2026        NaN    True
  09/01/26        NaN    True
failed: 5 of 6 non-blank values
['2026-01-09', '2068-01-09', '1969-01-09']
datetime64[us] 86400000000 86400000000000

Five of the six non-blank values fail, each for a reason worth a line in the data log: 31/02/2026 does not exist, 2026-01-07 is a different format, soon is not a date, 15/13/2026 has a thirteenth month, and 09/01/26 has a two-digit year. Report the count and the raw text, decide value by value, and never let a coerced column through with its new NaT values unexplained.

Two-digit years follow the POSIX and ISO C rule that Python’s documentation describes for strptime, and pandas reads them the same way: 69 to 99 mean the 1900s and 00 to 68 the 2000s. So 68 becomes 2068, which is wrong for a date of birth. Refuse two-digit years, or fix the century with a rule you can defend.

The last line is a pandas 3 change. Text parsed into dates is now stored in microseconds, datetime64[us], where pandas 2 used nanoseconds, so astype("int64") returns a number 1,000 times smaller than older code expects: 86,400,000,000 for one day after 1970-01-01. The pandas release notes recommend avoiding integer conversions or choosing the unit first with .dt.as_unit("ns").

Money: float for analysis, Decimal for exact amounts

A float stores most decimal amounts as the nearest binary fraction, which is close enough for averages and charts and not for equality:

from decimal import Decimal

import pandas as pd

# Fee and tax should add up to the total on every invoice.
invoices = pd.DataFrame({"fee": [100.10, 250.75, 99.99], "tax": [200.20, 45.15, 0.01], "total": [300.30, 295.90, 100.00]})

print("== :", ((invoices["fee"] + invoices["tax"]) == invoices["total"]).tolist())
print("rounded to 2 places:", ((invoices["fee"] + invoices["tax"]).round(2) == invoices["total"]).tolist())
print("first sum as stored:", float(invoices["fee"].iloc[0] + invoices["tax"].iloc[0]))

# Decimal built from the text keeps every paisa or cent exact.
fee, tax, total = Decimal("100.10"), Decimal("200.20"), Decimal("300.30")
print("Decimal:", fee + tax == total, fee + tax)

== : [False, True, True]
rounded to 2 places: [True, True, True]
first sum as stored: 300.29999999999995
Decimal: True 300.30

100.10 plus 200.20 is stored as 300.29999999999995, so a check that fee plus tax equals the total fails on the first invoice. Compare money rounded to its smallest unit, .round(2), or with a tolerance. When every paisa or cent must balance, as in a reconciliation, keep amounts as Decimal built from text, or as whole numbers of the smallest unit. For analysis, float64 is fine: round for display only, at the end.

Exercise

import re
from datetime import date, datetime

MULTIPLIERS = {"thousand": 1_000, "lakh": 100_000, "lakhs": 100_000, "million": 1_000_000, "crore": 10_000_000, "crores": 10_000_000}


def parse_amount(text):
    """Return the amount in text as a float rounded to 2 places, None for a blank; raise ValueError otherwise."""
    # Replace these lines with your code.
    if text is None:
        return None
    return float(text)


def parse_date(text):
    """Return a datetime.date for DD/MM/YYYY, DD-MM-YYYY or YYYY-MM-DD, None for a blank; raise ValueError otherwise."""
    # Replace these lines with your code.
    if text is None:
        return None
    return datetime.strptime(text, "%m/%d/%Y").date()

from datetime import date

from parsing import parse_amount, parse_date


def raises(f, text):
    try:
        f(text)
    except ValueError:
        return True
    return False


def test_plain_and_grouped_numbers():
    """reads digits with commas in any grouping, and decimals"""
    assert parse_amount("120000") == 120000.0
    assert parse_amount("120,000") == 120000.0
    assert parse_amount("1,20,000") == 120000.0
    assert parse_amount("1,200.50") == 1200.5
    assert parse_amount(" 42 ") == 42.0


def test_currency_marks():
    """accepts one currency mark in front, in any case and with spaces"""
    assert parse_amount("₹1,20,000") == 120000.0
    assert parse_amount("$350") == 350.0
    assert parse_amount("€ 99.90") == 99.9
    assert parse_amount("Rs. 45,500") == 45500.0
    assert parse_amount("rs 45,500") == 45500.0
    assert parse_amount("INR 1,000") == 1000.0


def test_negative_amounts():
    """reads a leading minus and accounting brackets as negative"""
    assert parse_amount("-40") == -40.0
    assert parse_amount("(1,500)") == -1500.0
    assert parse_amount("($ 75.25)") == -75.25


def test_multiplier_words():
    """multiplies by thousand, lakh, million and crore, singular or plural, any case"""
    assert parse_amount("1.2 lakh") == 120000.0
    assert parse_amount("1.20 Lakhs") == 120000.0
    assert parse_amount("Rs. 0.012 crore") == 120000.0
    assert parse_amount("2 crores") == 20000000.0
    assert parse_amount("3.5 million") == 3500000.0
    assert parse_amount("15 thousand") == 15000.0


def test_rounding():
    """rounds to 2 decimal places"""
    assert parse_amount("0.333 lakh") == 33300.0
    assert parse_amount("1.23456") == 1.23


def test_blanks():
    """returns None for None and blank text"""
    assert parse_amount(None) is None
    assert parse_amount("") is None
    assert parse_amount("   ") is None
    assert parse_date(None) is None
    assert parse_date(" ") is None


def test_refused_amounts():
    """raises ValueError for text it cannot read"""
    for text in ["12 lac", "abc", "1.2.3", "₹", "12%", "--5", "1,2a0", "(1,500", "1,500)", "(-40)"]:
        assert raises(parse_amount, text), text


def test_dates():
    """reads day-first and ISO dates"""
    assert parse_date("05/01/2026") == date(2026, 1, 5)
    assert parse_date("13/02/2026") == date(2026, 2, 13)
    assert parse_date("05-01-2026") == date(2026, 1, 5)
    assert parse_date("2026-01-05") == date(2026, 1, 5)
    assert parse_date(" 29/02/2024 ") == date(2024, 2, 29)


def test_refused_dates():
    """refuses impossible dates, two-digit years and other formats"""
    for text in ["31/02/2026", "13/13/2026", "05/01/26", "2026/01/05", "Jan 5 2026", "29/02/2026"]:
        assert raises(parse_date, text), text

Interview questions

What does errors="coerce" do in pd.to_numeric and pd.to_datetime, and what must you do after using it?

It converts every value it can and turns every value it cannot read into NaN (numbers) or NaT (dates), without raising and without saying which values failed. That keeps a pipeline running, but it mixes two kinds of gap: values that were missing all along and values that were real but written in a format the converter did not understand, such as 1,200 or a day-first date read with the wrong format. So count the missing values before and after the conversion, list the raw text of every row that became missing only during it, and decide case by case: clean the text with a rule and convert again, mark the value as missing on purpose, or ask the source. Write the count of each outcome in the data log.

Key takeaways

• pd.to_numeric reads digits, a sign and scientific notation only; commas, currency marks, brackets and percent signs need a cleaning rule first.
• errors="coerce" hides failures among missing values. Count what became NaN or NaT during the conversion and read the raw text behind it.
• Write cleaning rules you can state: brackets mean negative, which marks to strip, which files use decimal commas.
• read_csv(thousands=",") reads any digit grouping; one cell with a currency sign keeps a column as text; digits of other scripts, such as Devanagari, do not survive to_numeric.
• Parse dates with an explicit format, format="%d/%m/%Y", or dayfirst=True. Without one, pandas guesses from the first value, so results can depend on row order.
• Refuse two-digit years or fix the century by rule; in pandas 3, parsed dates are microseconds, so integer conversions are 1,000 times smaller than in pandas 2.
• Use float64 for analysis and round for display; use Decimal or whole paise or cents where amounts must balance exactly.