Files, text, JSON and dates

Before data reaches a NumPy array or a pandas table, it usually arrives as something messier: files in folders, lines of log text, nested JSON from a web service, or timestamps written in a dozen formats and time zones. Lesson 1 covered core Python; this lesson applies it to the everyday work of getting raw data into a usable shape. Libraries such as pandas (Lesson 5) handle many of these jobs in one line, but knowing what happens underneath lets you deal with the files that do not fit the standard pattern, which in real projects is most of them.

Paths and files

The pathlib module represents file paths as objects rather than strings. A Path can be joined with the / operator, tested for existence, listed, and read or written directly, and it uses the correct separator on Windows, macOS and Linux. Avoid building paths by gluing strings together with "/" or "\"; it breaks on other systems and makes mistakes easy.

Files are opened with open(path, mode, encoding=...). Mode "r" reads, "w" writes (replacing any existing file), "a" appends and "x" creates a new file but fails if it exists. Always use a with block, which closes the file even if an error occurs, and always state the encoding: "utf-8" is the right choice for almost all new data, and leaving it out means Python uses the operating system's default, which differs between computers.

CSV files

Comma-separated values (CSV) is the most common format for tabular data. The csv module handles the details that naive splitting on commas gets wrong: quoted fields that contain commas, embedded newlines, and different line endings. DictReader returns each row as a dictionary keyed by the header, which makes code readable and robust to column order.

import csv
from pathlib import Path

folder = Path("site_data")
folder.mkdir(exist_ok=True)
path = folder / "rack_power.csv"

rows = [
    {"rack": "A01", "kw": 7.4, "status": "ok"},
    {"rack": "A02", "kw": 11.9, "status": "ok"},
    {"rack": "A03", "kw": 0.0, "status": "offline"},
    {"rack": "A04", "kw": 9.1, "status": "ok"},
]
with open(path, "w", newline="", encoding="utf-8") as f:
    writer = csv.DictWriter(f, fieldnames=["rack", "kw", "status"])
    writer.writeheader()
    writer.writerows(rows)

print(path, "exists:", path.exists(), "size:", path.stat().st_size, "bytes")
print(path.read_text(encoding="utf-8").splitlines()[:2])

total = 0.0
with open(path, newline="", encoding="utf-8") as f:
    for row in csv.DictReader(f):
        if row["status"] == "ok":
            total += float(row["kw"])
print(f"Load of online racks: {total:.1f} kW")
print("CSV files here:", sorted(p.name for p in folder.glob("*.csv")))

site_data/rack_power.csv exists: True size: 70 bytes
['rack,kw,status', 'A01,7.4,ok']
Load of online racks: 28.4 kW
CSV files here: ['rack_power.csv']

Notice that every value read from a CSV file is a string: "7.4", not 7.4. The code must convert with float() before doing arithmetic, and must decide what to do with values that cannot be converted (Lesson 1). Passing newline="" when opening CSV files lets the csv module handle line endings itself, as its documentation requires.

Working with text

Much useful data arrives as free text: equipment logs, maintenance notes, support tickets, sensor messages. Python's string methods do most of the work:

• split() breaks a string on whitespace (or a given separator); split(maxsplit=3) stops after three splits so the rest of the line stays together.
• strip(), lower(), upper() and replace() clean and normalize text.
• startswith(), endswith() and the in operator test for content.
• f-strings format output: f"{value:.1f}" gives one decimal place, f"{name:<7}" pads to seven characters on the left, and f"{when:%H:%M}" formats a date.

The Counter class from the collections module counts things in one step, which is one of the most common operations in exploring text data.

from collections import Counter

log = """2026-11-01T01:15:04 WARN  pdu-07  input voltage low (198 V)
2026-11-01T01:16:40 INFO  crac-2  fan speed set to 70%
2026-11-01T01:17:02 ERROR pdu-07  breaker 12 tripped
2026-11-01T01:18:55 WARN  pdu-07  input voltage low (196 V)
2026-11-01T01:20:13 ERROR ups-1   battery string 2 fault
2026-11-01T01:22:31 INFO  crac-2  fan speed set to 75%"""

records = []
for line in log.splitlines():
    stamp, level, device, message = line.split(maxsplit=3)
    records.append({"time": stamp, "level": level, "device": device, "msg": message})

print(len(records), "records; first:", records[0])
print("By level:", Counter(r["level"] for r in records))
print("Busiest device:", Counter(r["device"] for r in records).most_common(1))

for r in records:
    if r["level"] == "ERROR":
        t, dev, msg = r["time"], r["device"], r["msg"]
        print(f"{t[11:]}  {dev:<7} {msg.upper()}")

6 records; first: {'time': '2026-11-01T01:15:04', 'level': 'WARN', 'device': 'pdu-07', 'msg': 'input voltage low (198 V)'}
By level: Counter({'WARN': 2, 'INFO': 2, 'ERROR': 2})
Busiest device: [('pdu-07', 3)]
01:17:02  pdu-07  BREAKER 12 TRIPPED
01:20:13  ups-1   BATTERY STRING 2 FAULT

This pattern, turning each line into a dictionary of named fields and then working with a list of dictionaries, is the bridge from raw text to a table: pandas can build a DataFrame directly from the list (pd.DataFrame(records)).

Regular expressions

When text follows a pattern but not a fixed layout, regular expressions (the re module) find and extract it. A pattern such as "inc-([0-9]{5})" means "the letters inc, a hyphen, then exactly five digits", and the parentheses capture the digits. Useful pieces include [0-9] (any digit), [a-z] (any lowercase letter), [^a-z0-9] (anything that is not a letter or digit), + (one or more), {5} (exactly five) and the IGNORECASE flag. Patterns are usually written as raw strings such as r"\d{5}" in Python source, where \d also means a digit.

import re

notes = [
    "Replaced PSU in rack A07, ticket INC-20419, cost 1,250.00",
    "rack b12: fan alarm cleared (ticket inc-20433)",
    "No ticket. Inspected racks A01-A04.",
]

ticket = re.compile("inc-([0-9]{5})", re.IGNORECASE)
rack = re.compile("rack ([a-z][0-9]{2})", re.IGNORECASE)

for n in notes:
    t = ticket.search(n)
    racks = [r.upper() for r in rack.findall(n)]
    print(t.group(1) if t else "-----", racks)

amount = re.search("cost ([0-9,]+[.][0-9]{2})", notes[0]).group(1)
print("Amount:", float(amount.replace(",", "")))
print(re.sub("[^a-z0-9]+", "-", "  Hall 2 / Row C (East)  ".lower()).strip("-"))

20419 ['A07']
20433 ['B12']
----- []
Amount: 1250.0
hall-2-row-c-east

Look at the third note: "racks A01-A04" produced no match, because the pattern requires "rack" followed directly by a space. Real text is full of such variations, and the honest way to handle them is to test patterns against a sample of real data, count how many records match, and inspect the ones that do not. Regular expressions are powerful but easy to make unreadable; for anything complex, prefer several simple patterns with comments, or a proper parser.

JSON and nested data

JSON (JavaScript Object Notation) is the standard format of web APIs and configuration files. It has objects (which become Python dictionaries), arrays (lists), strings, numbers, true/false (True/False) and null (None). Unlike CSV, JSON is naturally nested: a site contains halls, each hall contains racks.

import json

text = """{
  "site": "YYC-2",
  "updated": "2026-11-01T06:00:00Z",
  "halls": [
    {"name": "Hall 1", "racks": [{"id": "A01", "kw": 7.4}, {"id": "A02", "kw": 11.9}]},
    {"name": "Hall 2", "racks": [{"id": "B01", "kw": 32.0}, {"id": "B02", "kw": null}]}
  ]
}"""

data = json.loads(text)
print(type(data).__name__, "with keys", list(data))

for hall in data["halls"]:
    loads = [r["kw"] for r in hall["racks"] if r["kw"] is not None]
    missing = sum(r["kw"] is None for r in hall["racks"])
    name = hall["name"]
    print(f"{name}: {sum(loads):.1f} kW from {len(loads)} racks, {missing} missing")

flat = [{"hall": h["name"], **r} for h in data["halls"] for r in h["racks"]]
print(flat[-1])
print(json.dumps({"site": data["site"], "total_kw": sum(r["kw"] or 0 for r in flat)}))

dict with keys ['site', 'updated', 'halls']
Hall 1: 19.3 kW from 2 racks, 0 missing
Hall 2: 32.0 kW from 1 racks, 1 missing
{'hall': 'Hall 2', 'id': 'B02', 'kw': None}
{"site": "YYC-2", "total_kw": 51.3}

Three points from this example apply widely:

• null values become None and must be handled explicitly; here they are excluded from the sum and counted as missing rather than silently treated as zero.
• Flattening nested data into one record per innermost item, carrying the parent fields along (the hall name), turns it into a table. pandas offers pd.json_normalize for the same job.
• json.dumps writes Python data as JSON text, and json.dump(data, f) writes it to a file. Values such as datetime objects and NumPy numbers must be converted to plain types first.

Dates and times

Time stamps cause more subtle bugs than any other kind of data. Python's datetime module provides date, time, datetime and timedelta (a duration). A datetime may be naive (no time zone) or aware (with a time zone). The rules that prevent most trouble are:

• Store and compute in UTC; convert to local time only for display.
• Parse timestamps with an explicit format or the ISO 8601 standard (2026-11-01T06:00:00+00:00), which datetime.fromisoformat reads directly.
• Use real time zone rules from the zoneinfo module (such as ZoneInfo("America/Toronto")) rather than fixed offsets, because offsets change with daylight saving time.
• Never subtract naive local times that might span a daylight saving change.

from datetime import datetime, timedelta, timezone
from zoneinfo import ZoneInfo

toronto = ZoneInfo("America/Toronto")
start = datetime(2026, 11, 1, 0, 30, tzinfo=toronto)

print("Hourly readings across the end of daylight saving time:")
for h in range(4):
    utc = start.astimezone(timezone.utc) + timedelta(hours=h)
    local = utc.astimezone(toronto)
    print(f"  UTC {utc:%H:%M}  ->  Toronto {local:%H:%M} {local.tzname()}")

naive_a = datetime(2026, 11, 1, 0, 30)
naive_b = datetime(2026, 11, 1, 3, 30)
aware_a = naive_a.replace(tzinfo=toronto)
aware_b = naive_b.replace(tzinfo=toronto)
print("Naive difference:", naive_b - naive_a)
print("Real elapsed time:", aware_b.astimezone(timezone.utc) - aware_a.astimezone(timezone.utc))

stamp = datetime.fromisoformat("2026-11-01T06:00:00+00:00")
print("Parsed:", stamp.isoformat(), "| weekday:", stamp.strftime("%A"))

Hourly readings across the end of daylight saving time:
  UTC 04:30  ->  Toronto 00:30 EDT
  UTC 05:30  ->  Toronto 01:30 EDT
  UTC 06:30  ->  Toronto 01:30 EST
  UTC 07:30  ->  Toronto 02:30 EST
Naive difference: 3:00:00
Real elapsed time: 4:00:00
Parsed: 2026-11-01T06:00:00+00:00 | weekday: Sunday

The output shows why. On November 1, 2026, clocks in Toronto (and New York, which follows the same rules) fall back from 02:00 EDT to 01:00 EST, so local times between 01:00 and 02:00 occur twice. Subtracting the naive local times 00:30 and 03:30 gives 3 hours, but 4 hours really passed. A data center that logged energy use in naive local time would double-count one hour every autumn and lose one every spring.

The US spans six time zones, and Arizona (outside the Navajo Nation) and Hawaii do not observe daylight saving time, so data from several states must carry time zone information to be combined correctly.

Encodings

Text in files is stored as bytes, and an encoding maps bytes to characters. UTF-8 can represent every character in every language and is the standard today, but older files may use Windows-1252 or Latin-1, especially exports from older business software. Reading such a file as UTF-8 either fails with a UnicodeDecodeError or turns accented letters such as é into strange sequences like "é" (mojibake). When that happens, find out the file's real encoding rather than guessing repeatedly, read it with that encoding, and save your outputs as UTF-8.

Other formats

You will meet many other formats. Excel workbooks (.xlsx) are read with pandas.read_excel. Parquet is a compressed, column-oriented binary format that stores types and is much faster than CSV for large data, and is standard in data engineering. HDF5 and NetCDF hold large scientific arrays. Pickle files store arbitrary Python objects but can execute code when loaded, so never load a pickle from a source you do not trust. Lesson 10 covers databases and SQL, which hold most business data.