Types, Time, Nulls & Numeric Correctness

A file only holds text. When Python reads a CSV file, every value arrives as a string, even a number.

A string (str) is text in quotes. This chapter turns that text into the right type: a number, a date, or a missing value.

You know the shop from the earlier chapters. Here is orders.csv again. Order 105 has an empty amount.

order_id,customer_id,status,amount
101,1,paid,40.0
102,1,paid,25.0
103,2,refunded,60.0
104,3,paid,15.5
105,3,pending,
106,4,paid,80.0

Read the file with the csv module from the files chapter. It gives one dict per line. Look at the first and the fifth dict:

import csv

with open("orders.csv", newline="") as file:
    rows = list(csv.DictReader(file))

print(rows[0])
print(rows[4])

What you will learn

1. Why text is not a number, and how to convert it.
2. How None, empty text and 0 differ.
3. Why money needs Decimal, and how to round it.
4. How to read dates and times, and turn them into UTC.
5. How to count events in a half-open time range.
6. How to check rows before you count them.

A type tells Python what a value is and what you can do with it. Text and numbers behave in different ways.

Add the amounts of orders 101 and 102 while they are still text. Python joins the two strings. It does not add them:

print(rows[0]["amount"] + rows[1]["amount"])
print(float(rows[0]["amount"]) + float(rows[1]["amount"]))
print(sorted(["100", "9", "25"]))
print(sorted([100, 9, 25]))

Convert text to a number with int() or float(). Use int() for whole numbers, such as order_id. Use float() for numbers with a decimal point.

print(int("101") + 1)
print(float("15.5"))
print(int("40.0"))

You will use three number types in data work. Choose by what the number means:

Two sections later you will see why float is only approximate, and why money needs Decimal.

In SQL, NULL means a value is missing. In Python, the missing value is None. It has its own type, NoneType.

From a CSV file, a missing amount does not arrive as None. It arrives as the empty string "". Order 105 shows it.

These are three different facts. Python agrees: no two of them are equal.

Convert the blank cell to None as soon as you read it. Then the rest of your code sees one kind of missing.

Here float is only a teaching tool. Money gets Decimal soon:

def to_amount(text):
    text = text.strip()
    if text == "":
        return None
    return float(text)

print([to_amount(row["amount"]) for row in rows])
print(None == "", None == 0, "" == 0)
print(None == None)

Without this step, float("") crashes with a ValueError. Test for missing with is None.

The trap: if x

An if test turns a value into True or False. This is called truthiness. None, "", 0, 0.0 and an empty list all count as False.

Now keep the orders that have an amount. The list has a free order with amount 0.0:

amounts = [40.0, 0.0, None, 15.5]

print([a for a in amounts if a])
print([a for a in amounts if a is not None])

A float keeps a number as 0s and 1s (binary). The number 0.1 cannot be written exactly with 0s and 1s, just as 1/3 cannot be written exactly with decimal digits. Python keeps the closest value.

Add 0.1 and 0.2, then compare the result to 0.3. Look at both answers:

from decimal import Decimal

print(0.1 + 0.2)
print(0.1 + 0.2 == 0.3)
print(Decimal("0.1") + Decimal("0.2"))
print(Decimal("0.1") + Decimal("0.2") == Decimal("0.3"))

The error is tiny, but it adds up. Add 0.1 ten times in a loop:

total = 0.0
total_decimal = Decimal("0")
for _ in range(10):
    total += 0.1
    total_decimal += Decimal("0.1")

print(total, total == 1.0)
print(repr(total_decimal), total_decimal == 1)

Build a Decimal from a string, never from a float. A float already carries the error:

print(Decimal("0.1"))
print(Decimal(0.1))
print(Decimal("15.50"))
print(Decimal("1.5") + 1)

From CSV text, use Decimal(text) directly. The text never becomes a float, so no error enters. Write money back as text with str().

A tie is a number exactly halfway between two choices, such as 2.5 between 2 and 3. Python's round() does not always round a tie up.

round() on a float sends a tie to the nearest even number (some books call this banker's rounding). It also reads the stored float, which may be slightly off:

print(round(0.5), round(1.5), round(2.5), round(3.5))
print(round(0.125, 2))
print(round(2.675, 2))

2.675 is stored a little below 2.675, so it gives 2.67. No rounding rule can fix that, because the stored value is already below the tie.

Shops and banks often want the school rule: a tie goes up. Use Decimal with quantize() and ROUND_HALF_UP. The first argument says how many places to keep:

from decimal import Decimal, ROUND_HALF_UP

cents = Decimal("0.01")
print(Decimal("0.125").quantize(cents, rounding=ROUND_HALF_UP))
print(Decimal("0.125").quantize(cents))
print(Decimal("-2.5").quantize(Decimal("1"), rounding=ROUND_HALF_UP))

To parse is to read text and turn it into a typed value. Real files have bad values: "N/A", extra spaces, letters in a number.

You parse once, at the point where data enters. This point is the boundary. A bad value must not crash the run, and it must not become a silent 0.

Send the bad row to a list of bad rows, with its line number and the reason. Money is Decimal, as in the last sections. Here is a messy copy of the amounts, and line 5 holds "N/A":

from decimal import Decimal, InvalidOperation

def parse_amount(text):
    text = text.strip()
    if text == "":
        return None
    try:
        return Decimal(text)
    except InvalidOperation:
        raise ValueError(f"bad amount {text!r}")

good, bad = [], []
for row in raw_rows:
    try:
        amount = parse_amount(row["amount"])
        good.append({"order_id": int(row["order_id"]), "amount": amount})
    except ValueError as error:
        bad.append({"line": row["line"], "reason": str(error)})

print(good)
print(bad)

Decimal raises InvalidOperation when text is not a number. We turn it into a ValueError, so one except line catches every kind of bad value. The !r in the message shows the value in quotes, so you can see spaces.

A blank amount is not a bad row here. It means "no amount yet", so it becomes None. Only text that is not a number goes to the bad list.

Now add up the amounts. Skip the None values first, because you cannot add None to a number. Then take the average:

with_amount = [row["amount"] for row in good if row["amount"] is not None]

print(len(good) + len(bad), len(good), len(bad), len(with_amount))
total = sum(with_amount)
print(total)
print(total / len(with_amount))
print(total / 6)

To aggregate means to add up or count many rows into one number. Check the rows first, then count.

If you drop bad rows after you count, the numbers do not match. The average above divides by the four rows that have an amount. It does not divide by all six rows read.

Always print the counts next to the result. A reader can then check that the numbers add up:

print(f"read={len(good) + len(bad)} good={len(good)} bad={len(bad)}")

Dates in files are text too. ISO 8601 is the standard text form: 2026-01-03 for a date, and 2026-01-03T10:00:00 for a date with a time. The T separates the two.

Use date.fromisoformat() for a date and datetime.fromisoformat() for a date with a time. Suppose order 101 was placed on 3 January, and order 106 on 21 January:

from datetime import date, timedelta

placed = date.fromisoformat("2026-01-03")
print(placed)
print(repr(placed))
print(date.fromisoformat("2026-01-21") - placed)
print(placed + timedelta(days=10))

Text that is not ISO fails. A date that does not exist fails too. Both raise a ValueError, so they belong in your bad-rows list:

from datetime import date, datetime

for text in ["03/01/2026", "2026-02-30"]:
    try:
        date.fromisoformat(text)
    except ValueError as error:
        print(error)

print(datetime.strptime("03/01/2026", "%d/%m/%Y").date())

In SQL, a column of type DATE or TIMESTAMP does this for you. In Python, you do it by hand, at the boundary.

A naive datetime has no time zone. "10:00" could be London or Delhi. An aware datetime carries an offset from UTC, so it names one exact moment.

UTC is the world reference time. An offset says how far a clock is from UTC. +05:30 means 5 hours 30 minutes ahead of UTC. So 15:30+05:30 is 10:00 UTC. -04:00 means 4 hours behind.

Look at three shapes of timestamp text. Which ones name an exact moment?

Pipelines store every moment in UTC. Python 3.11 and newer read a final Z as UTC. Parse the three texts, which means read them into datetime values, and compare:

from datetime import datetime, timezone

a = datetime.fromisoformat("2026-01-03T10:00:00Z")
b = datetime.fromisoformat("2026-01-03T15:30:00+05:30")
c = datetime.fromisoformat("2026-01-03T10:00:00")

print(repr(a))
print(b.astimezone(timezone.utc))
print(a == b)
print(c.tzinfo)

Do not mix the two kinds. Ordering a naive time against an aware time raises a TypeError.

Equality does not raise. It silently says False:

print(c == a)
print(c < a)

Now write one function that turns all three shapes into UTC. A naive time needs a decision.

Here we decide that naive means UTC, because we know our source. Write your decision down:

def to_utc(text):
    value = datetime.fromisoformat(text)
    if value.tzinfo is None:
        value = value.replace(tzinfo=timezone.utc)
    return value.astimezone(timezone.utc)

for text in ["2026-01-03T10:00:00Z",
             "2026-01-03T15:30:00+05:30",
             "2026-01-03T10:00:00",
             "2026-01-03T23:30:00-04:00"]:
    print(to_utc(text).isoformat())

Optional: some sources send a zone name, not an offset. The offset of a zone name changes during the year. Use zoneinfo for these:

from zoneinfo import ZoneInfo

new_york = ZoneInfo("America/New_York")
summer = datetime(2026, 6, 19, 9, 0, tzinfo=new_york)
winter = datetime(2026, 1, 19, 9, 0, tzinfo=new_york)

print(summer.astimezone(timezone.utc))
print(winter.astimezone(timezone.utc))

A range [start, end) includes start but not end. This is a half-open range.

The square bracket means "included". The round bracket means "not included". In Python, start <= t < end means start <= t and t < end.

The SQL essentials chapter taught the same rule: write >= start AND < end, not BETWEEN.

Look at five events. Two sit exactly on midnight: e3 at the start of 11 January and e5 at the start of 12 January:

from datetime import datetime, timedelta, timezone

events = [datetime.fromisoformat(t) for t in [
    "2026-01-10T09:15:00Z", "2026-01-10T23:59:59Z",
    "2026-01-11T00:00:00Z", "2026-01-11T08:30:00Z",
    "2026-01-12T00:00:00Z",
]]

day10 = datetime(2026, 1, 10, tzinfo=timezone.utc)
day11 = day10 + timedelta(days=1)
day12 = day11 + timedelta(days=1)

def count_half_open(start, end):
    return sum(1 for t in events if start <= t < end)

def count_closed(start, end):
    return sum(1 for t in events if start <= t <= end)

print("half-open:", count_half_open(day10, day11), count_half_open(day11, day12))
print("closed:", count_closed(day10, day11), count_closed(day11, day12))

Look at e3. Which ranges contain it?

The closed counts add up to 6, but only e1 to e4 belong to these two days. Closed counts e3 twice, and it also counts e5, which belongs to 12 January.

The half-open counts add up to 4: e1, e2, e3 and e4, each once. The end of one range is the start of the next, so no event is lost or counted twice.

A warehouse is a database built for reports, such as BigQuery or Snowflake. Each Python type has a matching warehouse type. Pick the match on purpose:

Writing a value out means turning it back into text. Use str() for Decimal and isoformat() for dates and times:

from decimal import Decimal
from datetime import date, datetime, timezone

print(str(Decimal("40.00")))
print(date(2026, 1, 3).isoformat())
print(datetime(2026, 1, 3, 10, 0, tzinfo=timezone.utc).isoformat())

A type hint is a note in the code that says what type a value should have. Python does not check it when the code runs.

This function says amount is an int. Call it with text and look at what happens:

def double(amount: int) -> int:
    return amount * 2

print(double("40"))

Real checks run at the boundary and reject the row. The validation chapter shows how.

You learned to convert text, handle missing values, use Decimal for money, and convert times. Each exercise opens in the Python workspace with its own data.

Before you run each exercise, guess one thing that could go wrong. Then check your guess.