Two listings cannot run here and say so on their first line, NOT EXECUTED:
PyMySQL and PyMongo, which need a MySQL server and a
mongod that these pages are not built against. The syllabus names both, so both are
shown — and each is paired with a listing that did run and produces the same answer:
sqlite3 for PyMySQL (the SQL is standard and identical; only the connect call and the
placeholder differ), and a plain-Python collection for PyMongo. Nothing on this page is
claimed to have run that did not.
| Prescribed | Where it is taught |
|---|---|
| NumPy arrays: shapes, sources, reshape, slice, indexes, arithmetic, logic, aggregation | Unit 1 — the ndarray, creation, dtypes, broadcasting, indexing and slicing, boolean indexing |
| Series and DataFrame as containers; single-level and hierarchical indexing | Unit 2 and Unit 5.4 |
| Handling missing data | Unit 3.3, with replacement and outlier filtering beside it |
| Arithmetic and Boolean operations on whole columns | Unit 2.6 — arithmetic and data alignment |
| Database-type operations: merging and aggregation | Unit 5.1, 5.2 and 5.6 |
| Plotting columns and whole tables | Basic visualizations with matplotlib, seaborn and plotly |
| Reading data from files and writing it back | Unit 3.1 and CSV, TXT, JSON and Excel |
| Python itself — files, strings, exceptions, classes | Python Programming and Data Structures |
| Relational design, normalisation and SQL | Database Management Systems |
| MongoDB: the document model, CRUD, aggregation, indexes | Document Databases |
| NLTK | Natural Language Processing |
re module outside Pandas, and driving
the two databases from Python — all held together by the pipeline the course asks for.
| Stage | Job | The rule |
|---|---|---|
| Extract | get the bytes out of whatever holds them and into one shape | every parser returns the same structure, so nothing downstream knows or cares what the source was |
| Validate | find what is missing, mistyped, duplicated or impossible | count and report every rejected record — a pipeline that silently loses rows is worse than one that stops |
| Transform | type, derive, reshape, join | never before validation: a transformation applied to a bad row propagates the fault somewhere harder to find |
| Load | write to the store the consumers read | in a transaction, so a half-finished load leaves the store as it was |
| Analyse | aggregate, model, plot | only now — and the row count at this stage should be explicable from the count at the first |
The order is the content of the objective. Any of these stages can be written; what makes it a pipeline is that each one may assume the guarantees of the one before and must not assume the ones after. The stages below are numbered in that order, and each consumes what the last produced.
Six customer records arrive spread over five sources: a pipe-delimited text file (all six),
and a CSV file, a JSON document, an XML file and an HTML table (two each). Parse every source into a
list of dictionaries with the keys id, name, city,
income and visits, and show why a CSV file must not be split on commas.
Parse the customer records out of five formats and return the same structure from each: a list of dictionaries with the same five keys.
str.split("|") on each line, zipped with the field names.csv.DictReader, which honours the quotes.json.loads, then lift address.city up to city: the structure is a tree, and flattening it is the parser's job.id is an attribute, read with get; the other fields are child elements, read with findtext.HTMLParser collects the text of each cell, row by row; the first row is the header.| Format | Module | What it costs you if you improvise |
|---|---|---|
| Delimited text | str.split |
nothing, if the delimiter cannot occur inside a field |
| CSV | csv.DictReader |
quoting, embedded commas, embedded newlines — all of which
split(",") gets wrong |
| JSON | json.loads |
nesting; the structure is a tree, not a table, and flattening it is your job |
| XML | xml.etree.ElementTree |
attributes and child elements are different things and are read differently |
| HTML | html.parser, or BeautifulSoup |
real pages are not well-formed XML, so an XML parser rejects them |
The point of returning one structure is that the validation, transform and load stages are written once. A pipeline whose second stage has to ask where the data came from has not finished its first.
| Function or statement | What it does |
|---|---|
csv.DictReader(io.StringIO(text)) | read CSV text as dictionaries keyed by the header, honouring quotes |
json.loads(text) | JSON text to nested dictionaries and lists |
ET.fromstring, findall, get, findtext | parse XML; find the elements; read an attribute; read a child's text |
class TableParser(HTMLParser) | an HTML parser whose handle_starttag, handle_data and handle_endtag collect the cells |
dict(zip(header, cells)) | pair each field name with its value |
BeautifulSoup(text, "html.parser"), find, find_all, select | the same table through BeautifulSoup; the parser named decides how broken markup is repaired |
# Stage 1a -- EXTRACT: the same six records from five formats, with the
# standard library only. Each parser returns the same list of dictionaries,
# so the rest of the pipeline never learns where the data came from.
import csv
import io
import json
import xml.etree.ElementTree as ET
from html.parser import HTMLParser
FIELDS = ["id", "name", "city", "income", "visits"]
# Step 1: Delimited text, split on the delimiter
RAW_TXT = """\
1|Asha Rao|Vizag|40|7
2|Biju Menon|Kochi|57|5
3|Chitra Das|Kolkata|37|8
4|Devan Iyer|Madurai|69|10
5|Esha Khan|Bhopal|36|6
6|Farid Ali|Patna|38|5
"""
def from_txt(text):
rows = []
for line in text.strip().splitlines():
rows.append(dict(zip(FIELDS, line.split("|"))))
return rows
# Step 2: CSV, naively and with csv.DictReader
# note the quoted field containing a comma -- this is why split(",") is wrong
RAW_CSV = '''\
id,name,city,income,visits
1,"Rao, Asha",Vizag,40,7
2,"Menon, Biju",Kochi,57,5
'''
def from_csv_naive(text):
rows = []
lines = text.strip().splitlines()
header = lines[0].split(",")
for line in lines[1:]:
rows.append(dict(zip(header, line.split(","))))
return rows
def from_csv(text):
return [dict(r) for r in csv.DictReader(io.StringIO(text))]
# Step 3: JSON, flattening the nested address
RAW_JSON = """
{"customers": [
{"id": 3, "name": "Chitra Das", "address": {"city": "Kolkata"},
"income": 37, "visits": 8},
{"id": 4, "name": "Devan Iyer", "address": {"city": "Madurai"},
"income": 69, "visits": 10}
]}
"""
def from_json(text):
doc = json.loads(text)
return [{"id": c["id"], "name": c["name"], "city": c["address"]["city"],
"income": c["income"], "visits": c["visits"]}
for c in doc["customers"]]
# Step 4: XML: an attribute and child elements
RAW_XML = """
<customers>
<customer id="5"><name>Esha Khan</name><city>Bhopal</city>
<income>36</income><visits>6</visits></customer>
<customer id="6"><name>Farid Ali</name><city>Patna</city>
<income>38</income><visits>5</visits></customer>
</customers>
"""
def from_xml(text):
root = ET.fromstring(text)
rows = []
for c in root.findall("customer"):
rows.append({"id": c.get("id"), # attribute
"name": c.findtext("name"), # child text
"city": c.findtext("city"),
"income": c.findtext("income"),
"visits": c.findtext("visits")})
return rows
# Step 5: HTML, with html.parser
RAW_HTML = """
<table id="customers">
<tr><th>id</th><th>name</th><th>city</th><th>income</th><th>visits</th></tr>
<tr><td>1</td><td>Asha Rao</td><td>Vizag</td><td>40</td><td>7</td></tr>
<tr><td>2</td><td>Biju Menon</td><td>Kochi</td><td>57</td><td>5</td></tr>
</table>
"""
class TableParser(HTMLParser):
"""Collect the cells of every row of the first table."""
def __init__(self):
super().__init__()
self.rows, self.row, self.cell, self.grab = [], [], [], False
def handle_starttag(self, tag, attrs):
if tag == "tr":
self.row = []
elif tag in ("td", "th"):
self.grab, self.cell = True, []
def handle_data(self, data):
if self.grab:
self.cell.append(data)
def handle_endtag(self, tag):
if tag in ("td", "th"):
self.row.append("".join(self.cell).strip())
self.grab = False
elif tag == "tr":
self.rows.append(self.row)
def from_html(text):
p = TableParser()
p.feed(text)
header, *body = p.rows
return [dict(zip(header, r)) for r in body]
# Step 6: Parse all five, and print the count and first record of each
for name, rows in (("delimited text", from_txt(RAW_TXT)),
("csv.DictReader", from_csv(RAW_CSV)),
("json", from_json(RAW_JSON)),
("xml.etree", from_xml(RAW_XML)),
("html.parser", from_html(RAW_HTML))):
print(f"{name:16s} {len(rows)} record(s); first = {rows[0]}")
# Step 7: Why csv.DictReader and not split(',')
print()
print("why csv.DictReader and not split(','):")
print(" naive :", from_csv_naive(RAW_CSV)[0])
print(" proper:", from_csv(RAW_CSV)[0])The syllabus names BeautifulSoup, and it is what a real scraper uses. The same table, read with it:
# Stage 1a, again -- the same HTML table read with BeautifulSoup, which the
# syllabus names. It is not in the standard library: pip install beautifulsoup4.
from bs4 import BeautifulSoup
# Step 1: Find the table, its header and its rows
def from_html_bs4(text):
soup = BeautifulSoup(text, "html.parser") # or "lxml", if installed
table = soup.find("table", id="customers")
header = [th.get_text(strip=True) for th in table.find_all("th")]
rows = []
for tr in table.find_all("tr")[1:]: # skip the header row
cells = [td.get_text(strip=True) for td in tr.find_all("td")]
if cells:
rows.append(dict(zip(header, cells)))
return rows
# the three selectors worth knowing, and what each returns
# soup.find("a") -> the first <a>, or None
# soup.find_all("a", class_="nav") -> a list, possibly empty
# soup.select("table#customers td") -> a list, by CSS selector
#
# note class_ with the underscore: "class" is a Python keyword.
#
# BeautifulSoup builds a tree from broken markup, but HOW it repairs it is
# decided by the parser it is given: "lxml" closes an unclosed cell, while
# "html.parser" nests the next one inside it (Step 3). It does NOT run
# JavaScript, so a page whose table is built in the browser yields nothing --
# fetch the API the page calls instead of scraping what it renders.
# Step 2: Read the same table as Stage 1a, and use the selectors
RAW_HTML = """
<table id="customers">
<tr><th>id</th><th>name</th><th>city</th><th>income</th><th>visits</th></tr>
<tr><td>1</td><td>Asha Rao</td><td>Vizag</td><td>40</td><td>7</td></tr>
<tr><td>2</td><td>Biju Menon</td><td>Kochi</td><td>57</td><td>5</td></tr>
</table>
"""
rows = from_html_bs4(RAW_HTML)
print(f"BeautifulSoup {len(rows)} record(s); first = {rows[0]}")
soup = BeautifulSoup(RAW_HTML, "html.parser")
print("find('a') ->", soup.find("a"))
print("select('table#customers td')[:5] ->", [td.get_text() for td in soup.select("table#customers td")][:5])
# Step 3: Broken markup: the repair belongs to the parser
BROKEN = '<table id="customers"><tr><th>id<th>name<tr><td>1<td>Asha Rao<tr><td>2<td>Biju</table>'
for parser in ("html.parser", "lxml"):
soup = BeautifulSoup(BROKEN, parser)
cells = [[c.get_text(strip=True) for c in tr.find_all(["th", "td"])] for tr in soup.find_all("tr")]
print(f"unclosed cells, {parser:11s} ->", cells)Saved as s1_extract.py and run with python3 s1_extract.py, it printed:
delimited text 6 record(s); first = {'id': '1', 'name': 'Asha Rao', 'city': 'Vizag', 'income': '40', 'visits': '7'}
csv.DictReader 2 record(s); first = {'id': '1', 'name': 'Rao, Asha', 'city': 'Vizag', 'income': '40', 'visits': '7'}
json 2 record(s); first = {'id': 3, 'name': 'Chitra Das', 'city': 'Kolkata', 'income': 37, 'visits': 8}
xml.etree 2 record(s); first = {'id': '5', 'name': 'Esha Khan', 'city': 'Bhopal', 'income': '36', 'visits': '6'}
html.parser 2 record(s); first = {'id': '1', 'name': 'Asha Rao', 'city': 'Vizag', 'income': '40', 'visits': '7'}
why csv.DictReader and not split(','):
naive : {'id': '1', 'name': '"Rao', 'city': ' Asha"', 'income': 'Vizag', 'visits': '40'}
proper: {'id': '1', 'name': 'Rao, Asha', 'city': 'Vizag', 'income': '40', 'visits': '7'}And s1_extract_bs4.py printed:
BeautifulSoup 2 record(s); first = {'id': '1', 'name': 'Asha Rao', 'city': 'Vizag', 'income': '40', 'visits': '7'}
find('a') -> None
select('table#customers td')[:5] -> ['1', 'Asha Rao', 'Vizag', '40', '7']
unclosed cells, html.parser -> [['idname1Asha Rao2Biju', 'name1Asha Rao2Biju', '1Asha Rao2Biju', 'Asha Rao2Biju', '2Biju', 'Biju'], ['1Asha Rao2Biju', 'Asha Rao2Biju', '2Biju', 'Biju'], ['2Biju', 'Biju']]
unclosed cells, lxml -> [['id', 'name'], ['1', 'Asha Rao'], ['2', 'Biju']]Read the last two lines of the first output. On the record
1,"Rao, Asha",Vizag,40,7 the naive split produces a name of "Rao and
a city of Asha", and shifts every later field by one. It raises no error, the row
count is right, and the damage is only visible if someone looks. Use the
csv module even when the file “looks simple”, because you
cannot tell from the first ten rows whether row nine thousand contains a comma.
And the last two lines of the second. Given cells whose closing tags are
missing, BeautifulSoup with "lxml" closes each one and returns the rows intact; with
"html.parser" it nests each cell inside the one before, and every cell's text runs on
to the end of the row. The repair belongs to the parser, so name the parser.
html.parser will not; Step 3 shows that the
repair depends on the parser BeautifulSoup is given, and the comment now says so.
All five parsers return a list of dictionaries with the same five keys — 6 records from the
text file and 2 from each of the others — so the later stages need not know the source, and
BeautifulSoup returns the same two records from the HTML table as html.parser. Splitting
the CSV on commas misreads the quoted name "Rao, Asha" as two fields and shifts every
later column.
“Check any anomalies, missing values etc.” A batch of seven customer records contains
a blank city, an income of "n/a", a record with no visits field, a
duplicate id and a negative visit count. Accept the clean records, convert their numeric fields, and
report every rejection with its reason.
Take a batch with five different faults in it, accept the clean records, and report every rejection with its reason.
ValueError; range-check what converted; check the id against those already seen. A record with any issue is rejected, with all its issues listed.Four kinds of fault, and they are not interchangeable.
KeyError
waiting to happen."n/a" where an integer belongs. Converting
without catching is how a pipeline dies at three in the morning.Plus duplicates, which are a property of the batch rather than of any one record and so must be checked while walking it.
| Function or statement | What it does |
|---|---|
f not in r | an absent field |
str(r[f]).strip() == "" | a blank one |
try: ... except ValueError: | convert, and record a failure instead of stopping |
seen = set() | the ids met so far, for the duplicate check |
enumerate(rows, 1) | each record with its row number, counted from 1 |
# Stage 1b -- the anomaly pass. Every record that fails is COUNTED and
# REPORTED, never silently dropped: a pipeline that quietly loses rows is
# worse than one that stops, because nobody finds out.
# Step 1: The batch, with five faults in it
RAW = [
{"id": "1", "name": "Asha Rao", "city": "Vizag", "income": "40", "visits": "7"},
{"id": "2", "name": "Biju Menon", "city": "Kochi", "income": "57", "visits": "5"},
{"id": "3", "name": "Chitra Das", "city": "", "income": "37", "visits": "8"},
{"id": "4", "name": "Devan Iyer", "city": "Madurai", "income": "n/a", "visits": "10"},
{"id": "5", "name": "Esha Khan", "city": "Bhopal", "income": "36"},
{"id": "2", "name": "Biju Menon", "city": "Kochi", "income": "57", "visits": "5"},
{"id": "7", "name": "Gita Nair", "city": "Surat", "income": "48", "visits": "-3"},
]
# Step 2: What each field must be
REQUIRED = ("id", "name", "city", "income", "visits")
NUMERIC = {"id": int, "income": int, "visits": int}
RANGES = {"income": (0, 1000), "visits": (0, 365)}
# Step 3: Presence, then type, then range, then duplicates, record by record
def clean(rows):
good, problems = [], []
seen = set()
for i, r in enumerate(rows, 1):
issues = []
missing = [f for f in REQUIRED if f not in r]
if missing:
issues.append(f"missing field(s) {missing}")
blank = [f for f in REQUIRED if f in r and str(r[f]).strip() == ""]
if blank:
issues.append(f"blank field(s) {blank}")
typed = {}
for f, fn in NUMERIC.items():
if f in r and str(r[f]).strip() != "":
try:
typed[f] = fn(r[f])
except ValueError:
issues.append(f"{f}={r[f]!r} is not an integer")
for f, (lo, hi) in RANGES.items():
if f in typed and not (lo <= typed[f] <= hi):
issues.append(f"{f}={typed[f]} outside [{lo}, {hi}]")
key = r.get("id")
if key in seen:
issues.append(f"duplicate id {key}")
elif key is not None:
seen.add(key)
if issues:
problems.append((i, key, issues))
else:
rec = dict(r)
rec.update(typed)
good.append(rec)
return good, problems
# Step 4: Run the pass, and report every rejection with its reason
good, problems = clean(RAW)
print(f"read {len(RAW)} record(s): {len(good)} accepted, {len(problems)} rejected")
print()
print("rejected, with the reason:")
for i, key, issues in problems:
print(f" row {i} (id {key}): " + "; ".join(issues))
print()
# Step 5: The accepted records, now typed
print("accepted:")
for r in good:
print(" ", r)
print()
# Step 6: Why the order of the checks matters
print("the three checks in order, and why the order matters:")
print(" presence -> a missing field cannot be converted")
print(" type -> a non-integer cannot be range-checked")
print(" range -> and only a number has a range")
print("running them in any other order raises exceptions instead of reporting faults.")Saved as s2_validate.py and run with python3 s2_validate.py, it printed:
read 7 record(s): 2 accepted, 5 rejected
rejected, with the reason:
row 3 (id 3): blank field(s) ['city']
row 4 (id 4): income='n/a' is not an integer
row 5 (id 5): missing field(s) ['visits']
row 6 (id 2): duplicate id 2
row 7 (id 7): visits=-3 outside [0, 365]
accepted:
{'id': 1, 'name': 'Asha Rao', 'city': 'Vizag', 'income': 40, 'visits': 7}
{'id': 2, 'name': 'Biju Menon', 'city': 'Kochi', 'income': 57, 'visits': 5}
the three checks in order, and why the order matters:
presence -> a missing field cannot be converted
type -> a non-integer cannot be range-checked
range -> and only a number has a range
running them in any other order raises exceptions instead of reporting faults.Two of the seven records survive, and the report says exactly why the other five did not. That report is the deliverable: a load that accepted two of seven rows without saying so would look like a success.
The three checks run in a fixed order — presence, then type, then range — because each one depends on the last. Range-checking a string raises; converting an absent field raises. Running them in the wrong order turns a report into a crash.
2 of the 7 records are accepted (ids 1 and 2) and 5 are rejected, each with its reason: a blank
city, a non-integer income, a missing visits field, a second id 2, and visits of
\(-3\). The report, not the accepted rows alone, is what this stage produces.
Write three customer records — an id, a ten-byte name, an income and a visit rate — to
a binary file of fixed-width records and read them back; show what reading with the wrong byte order
does; store a list of incomes as an array; and round-trip a Python object with
pickle.
Write and read fixed-width records with struct, homogeneous numbers with array, and arbitrary objects with pickle — and know which to use.
'<i10sif' into an in-memory file, then unpack them again at offsets of one record size; compare that size with the native, padded format.array('i'), written to bytes and read back.| Tool | Holds | Readable by | Use when |
|---|---|---|---|
struct | a declared layout of C types | any language, given the layout | the format is fixed and shared — instrument output, network frames |
array | one numeric type, many values | any language, given the type and byte order | a long homogeneous vector; far smaller than a list |
pickle | almost any Python object | Python only | a cache you wrote and will read yourself — never across a trust boundary |
The format string is the whole of struct. The first character
sets byte order and alignment: '<' little-endian with no padding,
'>' big-endian, '@' native with native alignment. The rest names
the fields — i a 4-byte signed integer, 10s ten raw bytes,
f a 4-byte float.
| Function or statement | What it does |
|---|---|
struct.pack(FMT, *rec), struct.unpack_from(FMT, raw, off) | a record to bytes, and bytes at an offset back to a record |
struct.calcsize(FMT) | the size of one record in bytes |
io.BytesIO() | an in-memory binary file |
array.array('i', ...), tobytes, frombytes | homogeneous integers, to bytes and back |
pickle.dumps(obj, protocol=4), pickle.loads | any object to bytes and back |
# Stage 2 -- BINARY files: struct for fixed-width records, array for
# homogeneous numbers, pickle for Python objects. All three are standard
# library; none of them is readable in a text editor, and that is the point.
import array
import io
import pickle
import struct
# Step 1: The records
RECORDS = [(1, b"Asha Rao ", 40, 7.25),
(2, b"Biju Menon", 57, 5.50),
(3, b"Chitra Das", 37, 8.00)]
# Step 2: struct: one format string describes every record
FMT = "<i10sif" # '<' little-endian, no padding; i=int32 10s=bytes i=int32 f=float32
SIZE = struct.calcsize(FMT)
print(f"format {FMT!r} record size {SIZE} bytes")
print(f"format '@i10sif' (native, padded) would be "
f"{struct.calcsize('@i10sif')} bytes -- alignment is not free")
buf = io.BytesIO()
for rec in RECORDS:
buf.write(struct.pack(FMT, *rec))
raw = buf.getvalue()
print(f"wrote {len(RECORDS)} records, {len(raw)} bytes")
print("first 22 bytes:", raw[:SIZE].hex(" "))
back = []
for off in range(0, len(raw), SIZE):
i, name, income, visits = struct.unpack_from(FMT, raw, off)
back.append((i, name.rstrip(b" ").decode(), income, round(visits, 2)))
print("read back:")
for r in back:
print(" ", r)
# Step 3: Byte order: the same bytes, read the other way round
one = struct.pack("<i", 1)
print()
print("the integer 1 packed little-endian:", one.hex(" "),
"-> read as big-endian:", struct.unpack(">i", one)[0])
print("a file written on one machine and read with the wrong byte order is not")
print("corrupt, it is WRONG -- which is far harder to notice. Always fix the")
print("byte order in the format string; never rely on the native default.")
# Step 4: array: homogeneous numbers, far more compact than a list
a = array.array("i", [40, 57, 37, 69, 36, 38])
print()
print(f"array('i') of {len(a)} ints -> {len(a.tobytes())} bytes "
f"({a.itemsize} each)")
b = array.array("i")
b.frombytes(a.tobytes())
print("round trip identical:", list(b) == list(a))
# Step 5: pickle: any Python object, at a price
obj = {"rows": RECORDS, "source": "customers.bin", "clean": True}
blob = pickle.dumps(obj, protocol=4) # pin the protocol: the default moves
# with the Python version, and so does the size
print()
print(f"pickle: {len(blob)} bytes, round trip identical:",
pickle.loads(blob) == obj)
print("pickle executes code while loading, so NEVER unpickle a file you did")
print("not write. For data that leaves your own machine use JSON, CSV or a")
print("declared binary layout like the struct format above.")Saved as s3_binary.py and run with python3 s3_binary.py, it printed:
format '<i10sif' record size 22 bytes
format '@i10sif' (native, padded) would be 24 bytes -- alignment is not free
wrote 3 records, 66 bytes
first 22 bytes: 01 00 00 00 41 73 68 61 20 52 61 6f 20 20 28 00 00 00 00 00 e8 40
read back:
(1, 'Asha Rao', 40, 7.25)
(2, 'Biju Menon', 57, 5.5)
(3, 'Chitra Das', 37, 8.0)
the integer 1 packed little-endian: 01 00 00 00 -> read as big-endian: 16777216
a file written on one machine and read with the wrong byte order is not
corrupt, it is WRONG -- which is far harder to notice. Always fix the
byte order in the format string; never rely on the native default.
array('i') of 6 ints -> 24 bytes (4 each)
round trip identical: True
pickle: 148 bytes, round trip identical: True
pickle executes code while loading, so NEVER unpickle a file you did
not write. For data that leaves your own machine use JSON, CSV or a
declared binary layout like the struct format above.Three things in that output are worth more than the code.
'<i10sif' and '@i10sif', differ by two bytes of padding the
native form inserts to align the float. A reader using the wrong one is off by two bytes from
the second record onward.pickle executes code while loading. Unpickling a file you
did not write is running a program you did not read. For anything that crosses a machine
boundary, use JSON, CSV, or a declared layout like the one above.The three records are written as 66 bytes, 22 per record, and read back unchanged; the native
format would have taken 24 bytes a record. The integer 1 read with the wrong byte order becomes
16,777,216. The array stores six incomes in 24 bytes and the pickle round trip is
identical — but only struct, with its byte order fixed, gives a file another
machine can be trusted to read.
From a four-line order log, find the first error, list every order number, and extract the date, level, order, customer e-mail and total of every line; then split, substitute and mask with patterns, and show the two mistakes that make a pattern wrong without making it fail.
Pull structured fields out of a log file, and meet the two mistakes that make a pattern wrong without making it fail.
re.search returns the first match and where it starts.re.findall with one group returns the group's text for every match.finditer and groupdict turn each line into a record.(.+) against (.+?) on two table cells.\d+\.\d+ against \d+\.\d{2}\b on money and a version number.re.compile is called once, outside the loop.| Call | Returns | Watch for |
|---|---|---|
re.search | the first match object, or None |
test for None before calling .group() |
re.findall | a list | with one group it returns the group, with several a list of tuples, and with none the whole match — three different shapes |
re.finditer | match objects | the one to use when you want groups and positions |
re.split | a list | a capturing group puts the separators in the result too |
re.sub | a string | the replacement may be a backreference (\1) or a function |
re.compile | a pattern object | compile once outside the loop, not once per row |
| Function or statement | What it does |
|---|---|
(?P<name>...), m.groupdict() | a named group, and all the named groups of a match as a dictionary |
re.VERBOSE | allow white space and comments inside the pattern |
r'\1@***.\3' | a replacement that reuses groups 1 and 3 |
lambda m: ... in re.sub | compute each replacement from its match |
.+? | the lazy form: as few characters as will do |
# Stage 3 -- REGULAR EXPRESSIONS: search, findall, split, sub, and the two
# traps that cost marks.
import re
# Step 1: The log to be searched
LOG = """\
2026-01-14 09:12:03 INFO order=A-1043 customer=asha@example.com total=1240.50
2026-01-14 09:15:47 WARN order=A-1044 customer=biju@example.in total=99.00
2026-01-15 11:02:19 ERROR order=B-2210 customer=chitra@mail.org total=15750.75
2026-01-15 11:40:00 INFO order=B-2211 customer=devan@example.com total=310.25
"""
# Step 2: search: the first match, with its position
m = re.search(r"ERROR", LOG)
print("search ->", m.group(), "at", m.start())
# Step 3: findall: every match; with ONE group it returns the group, not the match
print("findall ->", re.findall(r"order=([A-Z]-\d+)", LOG))
# Step 4: Groups, named groups, and finditer for structured extraction
PAT = re.compile(r"""
(?P<date>\d{4}-\d{2}-\d{2})\s+ # date
(?P<time>\d{2}:\d{2}:\d{2})\s+ # time
(?P<level>[A-Z]+)\s+ # level
order=(?P<order>[A-Z]-\d+)\s+
customer=(?P<email>\S+@\S+?)\s+
total=(?P<total>\d+\.\d{2})
""", re.VERBOSE)
print()
print("finditer with named groups:")
rows = [m.groupdict() for m in PAT.finditer(LOG)]
for r in rows:
print(" ", r["date"], r["level"], r["order"], r["email"], r["total"])
print("records parsed:", len(rows))
# Step 5: Split on a pattern, not a fixed string
print()
print("split on any run of whitespace:", re.split(r"\s+", "a b\tc\nd"))
print("split keeping the separator :", re.split(r"([;,])", "a,b;c"))
# Step 6: sub, with a backreference and with a function
print()
print("sub, backreference:",
re.sub(r"(\w+)@(\w+)\.(\w+)", r"\1@***.\3", "asha@example.com"))
print("sub, function :",
re.sub(r"\d+\.\d{2}", lambda m: f"{float(m.group())*1.18:.2f}",
"total=1240.50 and total=99.00"))
print("sub, count limited:", re.sub(r"a", "A", "banana", count=2))
# Step 7: Trap 1: greedy versus lazy
html = '<td>Vizag</td><td>Kochi</td>'
print()
print("greedy .+ :", re.findall(r"<td>(.+)</td>", html))
print("lazy .+?:", re.findall(r"<td>(.+?)</td>", html))
print("the greedy form runs to the LAST </td> and returns one wrong answer;")
print("it is not an error, which is what makes it dangerous.")
# Step 8: Trap 2: a plausible pattern that is wrong
print()
bad = r"\d+\.\d+"
good = r"\d+\.\d{2}\b"
text = "total=1240.50 version=3.14159 total=99.00"
print("pattern", bad, "->", re.findall(bad, text))
print("pattern", good, "->", re.findall(good, text))
print("the first matches a version number as if it were money. Anchor the")
print("pattern to what you mean -- two decimal places and a word boundary.")
# Step 9: Compile once when the pattern is reused
print()
print("re.compile returns a pattern object; compiling inside a loop over a")
print("million rows is the commonest avoidable cost in an extract script.")Saved as s4_regex.py and run with python3 s4_regex.py, it printed:
search -> ERROR at 176
findall -> ['A-1043', 'A-1044', 'B-2210', 'B-2211']
finditer with named groups:
2026-01-14 INFO A-1043 asha@example.com 1240.50
2026-01-14 WARN A-1044 biju@example.in 99.00
2026-01-15 ERROR B-2210 chitra@mail.org 15750.75
2026-01-15 INFO B-2211 devan@example.com 310.25
records parsed: 4
split on any run of whitespace: ['a', 'b', 'c', 'd']
split keeping the separator : ['a', ',', 'b', ';', 'c']
sub, backreference: asha@***.com
sub, function : total=1463.79 and total=116.82
sub, count limited: bAnAna
greedy .+ : ['Vizag</td><td>Kochi']
lazy .+?: ['Vizag', 'Kochi']
the greedy form runs to the LAST </td> and returns one wrong answer;
it is not an error, which is what makes it dangerous.
pattern \d+\.\d+ -> ['1240.50', '3.14159', '99.00']
pattern \d+\.\d{2}\b -> ['1240.50', '99.00']
the first matches a version number as if it were money. Anchor the
pattern to what you mean -- two decimal places and a word boundary.
re.compile returns a pattern object; compiling inside a loop over a
million rows is the commonest avoidable cost in an extract script.The greedy match is the classic. <td>(.+)</td>
runs to the last </td> on the line and returns one wrong string;
(.+?) stops at the first and returns two right ones. Neither raises.
And the plausible pattern that is wrong. \d+\.\d+ reads
3.14159 as money. Anchoring to two decimal places and a word boundary fixes it.
Test a pattern against what it should not match, not only against what
it should.
Regular expressions applied to whole Pandas columns — str.extract,
str.replace, str.contains — are in
Python for Data Analysis,
Unit 4.2 and are not repeated.
The first error is at character 176; the four order numbers are A-1043, A-1044, B-2210 and
B-2211, and all four lines parse into records. The greedy pattern returns one wrong cell where the lazy
one returns Vizag and Kochi, and the unanchored money pattern takes
3.14159 for an amount where the anchored one does not.
Design a normalised schema for cities, customers and their orders; create it, populate it with
four customers and six orders, and show that its constraints reject three illegal rows; then read
the orders per customer with a join, update and delete with a WHERE, and show a
transaction rolled back.
Design a small schema, create it, populate it, and run create, read, update and delete against it — with the constraints actually enforcing something.
sqlite3 database, with foreign keys switched on, which sqlite needs asked for.NOT NULL, UNIQUE and CHECK constraints, and an index on the orders' customer.executemany with ? placeholders, then commit.IntegrityError.COUNT, SUM, GROUP BY and HAVING.UPDATE and a DELETE, each with a WHERE, and the rows each touched.BEGIN, an update of every row, a failure, and a rollback.The design, in one line each. City is separated from customer because the city name depends only on the city, not on the customer — that is third normal form, and the practical consequence is that a city renamed once is renamed everywhere. Orders are a separate table because a customer has many of them, and repeating the customer's name on every order is the anomaly normalisation exists to prevent. The full treatment is in Database Management Systems, Unit 3.
The constraints are the design. A foreign key that is not declared is a comment; declared, it is the database refusing to hold a customer in a city that does not exist. The listing tries three illegal inserts on purpose and shows all three being rejected.
| Function or statement | What it does |
|---|---|
sqlite3.connect(":memory:") | a database that lives only while the program runs |
cur.executescript(...) | run several SQL statements at once |
cur.executemany(sql, rows) | one parameterised statement for many rows |
except sqlite3.IntegrityError | a constraint refused the row |
cur.rowcount | the rows the last statement touched |
con.commit(), con.rollback() | keep the transaction, or abandon it |
# Stage 4 -- LOAD into a relational database: design, create, populate, and
# all four CRUD operations, run in sqlite3. The SQL below is standard and
# runs unchanged on MySQL; only the connect call differs.
import sqlite3
# Step 1: Connect, with the foreign keys switched on
con = sqlite3.connect(":memory:")
con.execute("PRAGMA foreign_keys = ON") # sqlite needs this asked for
cur = con.cursor()
# Step 2: The schema, normalised
cur.executescript("""
CREATE TABLE city (
city_id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);
CREATE TABLE customer (
cust_id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
city_id INTEGER NOT NULL REFERENCES city(city_id),
income INTEGER NOT NULL CHECK (income >= 0)
);
CREATE TABLE ordr (
order_id INTEGER PRIMARY KEY,
cust_id INTEGER NOT NULL REFERENCES customer(cust_id),
amount REAL NOT NULL CHECK (amount > 0),
placed TEXT NOT NULL
);
CREATE INDEX ix_order_cust ON ordr(cust_id);
""")
print("tables:", [r[0] for r in cur.execute(
"SELECT name FROM sqlite_master WHERE type='table' ORDER BY name")])
# Step 3: CREATE: parameterised inserts
cities = [(1, "Vizag"), (2, "Kochi"), (3, "Kolkata"), (4, "Madurai")]
customers = [(1, "Asha Rao", 1, 40), (2, "Biju Menon", 2, 57),
(3, "Chitra Das", 3, 37), (4, "Devan Iyer", 4, 69)]
orders = [(101, 1, 1240.50, "2026-01-14"), (102, 2, 99.00, "2026-01-14"),
(103, 3, 15750.75, "2026-01-15"), (104, 4, 310.25, "2026-01-15"),
(105, 1, 480.00, "2026-01-16"), (106, 1, 75.25, "2026-01-17")]
cur.executemany("INSERT INTO city VALUES (?, ?)", cities)
cur.executemany("INSERT INTO customer VALUES (?, ?, ?, ?)", customers)
cur.executemany("INSERT INTO ordr VALUES (?, ?, ?, ?)", orders)
con.commit()
print("inserted:", cur.execute("SELECT COUNT(*) FROM customer").fetchone()[0],
"customers,", cur.execute("SELECT COUNT(*) FROM ordr").fetchone()[0], "orders")
# Step 4: The constraints do their job
for sql, params, why in (
("INSERT INTO customer VALUES (?, ?, ?, ?)", (5, "Esha Khan", 9, 36),
"city_id 9 does not exist"),
("INSERT INTO ordr VALUES (?, ?, ?, ?)", (107, 1, -5.0, "2026-01-18"),
"amount must be positive"),
("INSERT INTO city VALUES (?, ?)", (5, "Vizag"), "city name is UNIQUE")):
try:
cur.execute(sql, params)
print(" NOT REJECTED --", why)
except sqlite3.IntegrityError as e:
print(f" rejected ({why}): {type(e).__name__}")
# Step 5: READ: a three-table join with an aggregate
print()
print("orders per customer, with the city:")
q = """
SELECT c.name AS customer, ci.name AS city,
COUNT(o.order_id) AS n_orders,
ROUND(SUM(o.amount), 2) AS total
FROM customer c
JOIN city ci ON ci.city_id = c.city_id
LEFT JOIN ordr o ON o.cust_id = c.cust_id
GROUP BY c.cust_id
HAVING COUNT(o.order_id) > 0
ORDER BY total DESC
"""
for row in cur.execute(q):
print(f" {row[0]:12s} {row[1]:9s} {row[2]:2d} order(s) total {row[3]:9.2f}")
# Step 6: UPDATE and DELETE, both with a WHERE
print()
cur.execute("UPDATE customer SET income = income + 5 WHERE city_id = 1")
print("UPDATE touched", cur.rowcount, "row(s); Asha's income is now",
cur.execute("SELECT income FROM customer WHERE cust_id = 1").fetchone()[0])
cur.execute("DELETE FROM ordr WHERE amount < 100")
print("DELETE removed", cur.rowcount, "row(s);",
cur.execute("SELECT COUNT(*) FROM ordr").fetchone()[0], "orders remain")
con.commit()
# Step 7: The transaction that is rolled back
print()
try:
cur.execute("BEGIN")
cur.execute("UPDATE customer SET income = 0")
raise RuntimeError("something failed half way through")
except RuntimeError as e:
con.rollback()
print("rolled back after:", e)
print("incomes after the rollback:",
[r[0] for r in cur.execute("SELECT income FROM customer ORDER BY cust_id")])
con.close()The syllabus names PyMySQL, and a MySQL server is what a real pipeline writes to.
There is no server here, so the listing is shown rather than run — but every statement in it is
the one sqlite3 executed above.
# NOT EXECUTED -- PyMySQL needs a MySQL server, which these pages are not
# built against. Every SQL statement here is the one the sqlite3 listing
# above ran; only the connection and the placeholder style differ.
import pymysql
con = pymysql.connect(
host="localhost", user="student", password="...", database="etl",
charset="utf8mb4",
cursorclass=pymysql.cursors.DictCursor, # rows as dicts, not tuples
autocommit=False, # the default, and the right one
)
try:
with con.cursor() as cur:
cur.execute("""
CREATE TABLE IF NOT EXISTS customer (
cust_id INT PRIMARY KEY,
name VARCHAR(60) NOT NULL,
city_id INT NOT NULL,
income INT NOT NULL CHECK (income >= 0),
FOREIGN KEY (city_id) REFERENCES city(city_id)
) ENGINE=InnoDB
""")
cur.executemany(
"INSERT INTO customer VALUES (%s, %s, %s, %s)",
[(1, "Asha Rao", 1, 40), (2, "Biju Menon", 2, 57)])
cur.execute("SELECT name, income FROM customer WHERE income > %s", (40,))
for row in cur.fetchall():
print(row) # {'name': 'Biju Menon', 'income': 57}
con.commit()
except Exception:
con.rollback()
raise
finally:
con.close()
# THREE DIFFERENCES FROM THE sqlite3 LISTING, AND NOTHING ELSE:
# 1. the placeholder is %s, not ? -- and it is still a PLACEHOLDER, not
# string formatting. "... WHERE income > %s" % 40 is a SQL injection,
# cur.execute(sql, (40,)) is not.
# 2. ENGINE=InnoDB, because MyISAM ignores foreign keys silently.
# 3. autocommit=False and an explicit commit(), so a half-finished load
# leaves the table as it was.Saved as s5_sql.py and run with python3 s5_sql.py, it printed:
tables: ['city', 'customer', 'ordr']
inserted: 4 customers, 6 orders
rejected (city_id 9 does not exist): IntegrityError
rejected (amount must be positive): IntegrityError
rejected (city name is UNIQUE): IntegrityError
orders per customer, with the city:
Chitra Das Kolkata 1 order(s) total 15750.75
Asha Rao Vizag 3 order(s) total 1795.75
Devan Iyer Madurai 1 order(s) total 310.25
Biju Menon Kochi 1 order(s) total 99.00
UPDATE touched 1 row(s); Asha's income is now 45
DELETE removed 2 row(s); 4 orders remain
rolled back after: something failed half way through
incomes after the rollback: [45, 57, 37, 69]Read the join. LEFT JOIN keeps customers with no orders;
HAVING COUNT(...) > 0 then removes them again — written that way
deliberately, because HAVING filters after grouping and
WHERE before, and confusing the two is the commonest SQL error at this level.
And read the rollback. The update sets every income to zero and is then
abandoned; the incomes afterwards are [45, 57, 37, 69], unchanged. A load
that is not in a transaction is a load that can half-succeed, and a half-loaded table
is harder to recover from than an empty one.
Three tables are created and loaded with 4 customers and 6 orders, and all three illegal inserts are rejected. The join gives each customer's orders and total, largest first (Chitra Das, 15,750.75). The update touched 1 row and the delete removed 2 orders, leaving 4; the rolled-back update left every income as it was.
Hold the same customers as documents, each with its address, tags and orders inside it; query them with a filter and a projection; update, replace and delete documents; total the order value per customer with an aggregation pipeline; and count what an index saves.
Load the same data as documents, run the four kinds of operation and an aggregation on them, and see where the document model differs from the relational one.
find with a query (a dotted path, an operator, an array field) and a projection.$set one field and $inc another, in one document.replace_one, which keeps the _id and nothing else.delete_many with an operator.$unwind the orders, $group by name with $sum, and $sort.| Relational | Document | |
|---|---|---|
| Shape | rows of a fixed schema | documents that need not agree |
| A customer's orders | a second table and a join | an array inside the customer |
| Adding a field | ALTER TABLE, all rows |
$set on one document |
| Consistency across documents | the database's job | yours |
| Use when | the shape is known and relationships matter | the shape varies, and what you read together you store together |
The last row is the decision. The document model is not “SQL without the schema”; it is a bet that you will read a customer with their orders far more often than you will ask a question that crosses customers. When that bet is wrong, the joins you avoided reappear in application code, where nothing optimises them.
| Function or statement | What it does |
|---|---|
dig(doc, "address.city") | follow a dotted path into a nested document |
OPS = {"$gt": ..., ...} | the query operators, as a dictionary of functions |
copy.deepcopy(doc) | a copy that shares nothing with the original |
(op, spec), = stage.items() | unpack a one-key dictionary |
index = {d["_id"]: d for d in DOCS} | an index: a dictionary from key to document |
# Stage 5 -- the DOCUMENT model, implemented in plain Python so that the
# semantics of every PyMongo call above can be checked without a server.
# Each function here does exactly what the collection method beside it does.
import copy
# Step 1: The collection: four customer documents
DOCS = [
{"_id": 1, "name": "Asha Rao", "address": {"city": "Vizag"},
"income": 40, "tags": ["retail", "west"],
"orders": [{"amt": 1240.50}, {"amt": 480.00}, {"amt": 75.25}]},
{"_id": 2, "name": "Biju Menon", "address": {"city": "Kochi"},
"income": 57, "tags": ["wholesale"], "orders": [{"amt": 99.00}]},
{"_id": 3, "name": "Chitra Das", "address": {"city": "Kolkata"},
"income": 37, "tags": ["retail", "east"],
"orders": [{"amt": 15750.75}]},
{"_id": 4, "name": "Devan Iyer", "address": {"city": "Madurai"},
"income": 69, "tags": ["retail"], "orders": [{"amt": 310.25}]},
]
# Step 2: find, with a query and a projection
def dig(doc, path):
"""Resolve a dotted path such as address.city."""
cur = doc
for part in path.split("."):
if not isinstance(cur, dict) or part not in cur:
return None
cur = cur[part]
return cur
OPS = {"$gt": lambda a, b: a is not None and a > b,
"$gte": lambda a, b: a is not None and a >= b,
"$lt": lambda a, b: a is not None and a < b,
"$in": lambda a, b: a in b or (isinstance(a, list) and set(a) & set(b)),
"$eq": lambda a, b: a == b}
def matches(doc, query):
for field, cond in query.items():
value = dig(doc, field)
if isinstance(cond, dict):
for op, arg in cond.items():
if not OPS[op](value, arg):
return False
elif isinstance(value, list):
if cond not in value: # arrays match on any element
return False
elif value != cond:
return False
return True
def project(doc, spec):
if not spec:
return copy.deepcopy(doc)
keep = {k for k, v in spec.items() if v}
out = {}
for k in keep:
v = dig(doc, k)
if v is not None:
out[k] = v
if spec.get("_id", 1):
out["_id"] = doc["_id"]
return out
def find(docs, query=None, projection=None):
return [project(d, projection) for d in docs if matches(d, query or {})]
print("find all:", len(find(DOCS)), "document(s)")
print("find {'address.city': 'Kochi'}:",
find(DOCS, {"address.city": "Kochi"}, {"name": 1, "_id": 0}))
print("find {'income': {'$gt': 40}}:",
[d["name"] for d in find(DOCS, {"income": {"$gt": 40}})])
print("find {'tags': 'retail'} (array matches any element):",
[d["name"] for d in find(DOCS, {"tags": "retail"})])
# Step 3: update_one with $set and $inc
def update_one(docs, query, update):
for d in docs:
if matches(d, query):
for field, value in update.get("$set", {}).items():
d[field] = value
for field, value in update.get("$inc", {}).items():
d[field] = d.get(field, 0) + value
return 1
return 0
n = update_one(DOCS, {"_id": 1}, {"$set": {"status": "gold"}, "$inc": {"income": 5}})
print()
print(f"update_one matched {n}; doc 1 is now income={DOCS[0]['income']},"
f" status={DOCS[0]['status']!r}")
print(" $set adds a field that no other document has -- no schema objects,")
print(" and no other document is touched. That is the whole difference from SQL.")
# Step 4: replace_one keeps the _id and discards everything else
def replace_one(docs, query, doc):
for i, d in enumerate(docs):
if matches(d, query):
new = dict(doc)
new["_id"] = d["_id"]
docs[i] = new
return 1
return 0
replace_one(DOCS, {"_id": 4}, {"name": "Devan Iyer", "income": 70})
print()
print("after replace_one, doc 4 =", DOCS[3])
print(" address, tags and orders are GONE. replace_one is not update_one;")
print(" confusing the two is how live collections lose fields.")
# Step 5: delete_many
def delete_many(docs, query):
keep = [d for d in docs if not matches(d, query)]
removed = len(docs) - len(keep)
docs[:] = keep
return removed
print()
print("delete_many({'income': {'$lt': 40}}) removed",
delete_many(DOCS, {"income": {"$lt": 40}}), "document(s);",
len(DOCS), "remain")
# Step 6: An aggregation pipeline: unwind, group, sort
def aggregate(docs, pipeline):
stage_docs = [copy.deepcopy(d) for d in docs]
for stage in pipeline:
(op, spec), = stage.items()
if op == "$match":
stage_docs = [d for d in stage_docs if matches(d, spec)]
elif op == "$unwind":
path = spec.lstrip("$")
out = []
for d in stage_docs:
for item in dig(d, path) or []:
copyd = copy.deepcopy(d)
copyd[path] = item
out.append(copyd)
stage_docs = out
elif op == "$group":
groups = {}
for d in stage_docs:
key = dig(d, spec["_id"].lstrip("$"))
g = groups.setdefault(key, {"_id": key})
for field, acc in spec.items():
if field == "_id":
continue
(fn, arg), = acc.items()
val = 1 if fn == "$sum" and arg == 1 else dig(d, str(arg).lstrip("$"))
g[field] = g.get(field, 0) + (val or 0)
stage_docs = list(groups.values())
elif op == "$sort":
for field, direction in reversed(list(spec.items())):
stage_docs.sort(key=lambda d: d[field], reverse=direction < 0)
return stage_docs
print()
print("aggregate runs on what is LEFT after the three mutations above:",
len(DOCS), "documents")
print("aggregate: total order value per customer, largest first")
for row in aggregate(DOCS, [{"$unwind": "$orders"},
{"$group": {"_id": "$name", "n": {"$sum": 1},
"total": {"$sum": "$orders.amt"}}},
{"$sort": {"total": -1}}]):
print(f" {row['_id']:12s} {row['n']} order(s) total {row['total']:9.2f}")
print(" Devan Iyer is absent although he is still in the collection: replace_one")
print(" removed his orders array, and $unwind DROPS a document whose array is")
print(" missing or empty rather than passing it through. Use")
print(" {'$unwind': {'path': '$orders', 'preserveNullAndEmptyArrays': True}}")
print(" when that is not what you want.")
# Step 7: What an index buys, counted
def scan(docs, key):
seen = 0
for d in docs:
seen += 1
if d["_id"] == key:
return d, seen
return None, seen
index = {d["_id"]: d for d in DOCS}
doc, seen = scan(DOCS, 2)
print()
print(f"collection scan for _id=2 examined {seen} of {len(DOCS)} documents")
print(f"index lookup examined 1, and found {index[2]['name']!r}")
print("on three documents this is a curiosity; on four million it is the")
print("difference between a query and an outage.")The syllabus names PyMongo, which needs a running mongod; these pages are
not built against one, so this listing is shown, not run. The programme above does in plain Python what
each of its collection methods does, so that their meaning can be checked without a server.
# NOT EXECUTED -- PyMongo needs a running mongod, which these pages are not
# built against. Every call here is mirrored by the pure-Python listing
# below, which WAS run and produces the output shown there.
from pymongo import MongoClient, ASCENDING
client = MongoClient("mongodb://localhost:27017/")
db = client["etl"]
col = db["customers"]
col.insert_many([
{"_id": 1, "name": "Asha Rao", "address": {"city": "Vizag"},
"income": 40, "tags": ["retail", "west"],
"orders": [{"amt": 1240.50}, {"amt": 480.00}, {"amt": 75.25}]},
{"_id": 2, "name": "Biju Menon", "address": {"city": "Kochi"},
"income": 57, "tags": ["wholesale"], "orders": [{"amt": 99.00}]},
])
# find: a dotted path reaches inside a sub-document; an array field matches
# if ANY element matches; the second argument is the projection
list(col.find({"address.city": "Kochi"}, {"name": 1, "_id": 0}))
list(col.find({"income": {"$gt": 40}}))
list(col.find({"tags": "retail"}))
# update_one adds a field to ONE document and leaves every other alone
col.update_one({"_id": 1}, {"$set": {"status": "gold"}, "$inc": {"income": 5}})
# replace_one keeps the _id and DISCARDS every other field
col.replace_one({"_id": 4}, {"name": "Devan Iyer", "income": 70})
col.delete_many({"income": {"$lt": 40}})
# an aggregation pipeline: one stage at a time, in order
list(col.aggregate([
{"$unwind": "$orders"},
{"$group": {"_id": "$name", "n": {"$sum": 1},
"total": {"$sum": "$orders.amt"}}},
{"$sort": {"total": -1}},
]))
col.create_index([("address.city", ASCENDING)], name="ix_city")
col.create_index([("name", "text")], name="tx_name")
print(col.find({"_id": 2}).explain()["executionStats"]["totalDocsExamined"])
client.close()Saved as s6_documents.py and run with python3 s6_documents.py, it printed:
find all: 4 document(s)
find {'address.city': 'Kochi'}: [{'name': 'Biju Menon'}]
find {'income': {'$gt': 40}}: ['Biju Menon', 'Devan Iyer']
find {'tags': 'retail'} (array matches any element): ['Asha Rao', 'Chitra Das', 'Devan Iyer']
update_one matched 1; doc 1 is now income=45, status='gold'
$set adds a field that no other document has -- no schema objects,
and no other document is touched. That is the whole difference from SQL.
after replace_one, doc 4 = {'name': 'Devan Iyer', 'income': 70, '_id': 4}
address, tags and orders are GONE. replace_one is not update_one;
confusing the two is how live collections lose fields.
delete_many({'income': {'$lt': 40}}) removed 1 document(s); 3 remain
aggregate runs on what is LEFT after the three mutations above: 3 documents
aggregate: total order value per customer, largest first
Asha Rao 3 order(s) total 1795.75
Biju Menon 1 order(s) total 99.00
Devan Iyer is absent although he is still in the collection: replace_one
removed his orders array, and $unwind DROPS a document whose array is
missing or empty rather than passing it through. Use
{'$unwind': {'path': '$orders', 'preserveNullAndEmptyArrays': True}}
when that is not what you want.
collection scan for _id=2 examined 2 of 3 documents
index lookup examined 1, and found 'Biju Menon'
on three documents this is a curiosity; on four million it is the
difference between a query and an outage.Three results in that output are the ones to carry away.
$set touched one document and gave it a field no other
document has. No schema object changed. That is the whole difference from SQL, and the whole
cost of it: nothing now guarantees that any two documents agree.replace_one is not update_one. It kept the
_id and discarded the address, the tags and the orders. Collections lose fields
this way in production.$unwind drops a document whose array is missing, which is
why Devan Iyer vanishes from the aggregation although he is still in the collection. Pass
preserveNullAndEmptyArrays when that is not what you meant.The index demonstration counts what a scan examines against what a lookup examines. A scan examines documents until it meets the key, up to every one of them, and the lookup examines one; on three documents that is a curiosity, but the scan's count grows with the collection and the lookup's does not.
The queries return what their filters say, including a match on any element of an array. After the update, the replacement (which lost Devan Iyer's address, tags and orders) and the deletion of Chitra Das, 3 documents remain, and the aggregation totals only two customers: Asha Rao 1,795.75 and Biju Menon 99.00.
Build arrays from a list, a range and a linear spacing; show what a fixed dtype does to an assigned value; reshape, transpose and flatten an array; show the difference between writing through a slice and through a fancy index; select by condition; and aggregate a two-row array along each axis.
Build arrays from several sources, reshape and slice them, index them by position and by condition, and aggregate along an axis.
np.array, np.arange(...).reshape, np.zeros with a dtype, and np.linspace..T, .ravel(), and .reshape(2, -1).np.where, and assignment through the mask.The teaching is in
Python for Data Analysis,
Unit 1. What is shown here is the part that goes wrong inside a pipeline: views
against copies, dtype, and axis=.
| Function or statement | What it does |
|---|---|
a.shape, a.dtype | the dimensions, and the one type every element shares |
b[0, :], b[[0, 2], :] | a basic slice (a view) and a fancy index (a copy) |
a[a > 40], np.where(c, x, y) | select by condition, and choose element by element |
m.sum(axis=0), m.mean(axis=0) | collapse the rows, leaving one value per column |
m.std(), m.std(ddof=1) | the standard deviation with divisor \(n\), and with \(n-1\) |
# Stage 6a -- NumPy: build, reshape, slice, index, and aggregate.
# The teaching is in Python for Data Analysis, Unit 1; what is shown here is
# the part that bites in a pipeline -- views against copies, and axis=.
import numpy as np
np.set_printoptions(legacy="1.25") # stable printing across versions
# Step 1: Arrays from different sources
a = np.array([40, 57, 37, 69, 36, 38]) # from a list
b = np.arange(12).reshape(3, 4) # from a range
c = np.zeros((2, 3), dtype=np.int32) # filled
d = np.linspace(0, 1, 5) # evenly spaced
print("a", a.shape, a.dtype, a)
print("b", b.shape, b.dtype, "\n", b)
print("c", c.shape, c.dtype, " d", np.round(d, 2))
# Step 2: dtype is fixed, and silently truncates
print()
i = np.array([1, 2, 3], dtype=np.int32)
i[0] = 3.9
print("int32 array assigned 3.9 ->", i, " -- truncated, not rounded, no warning")
print("mixing types promotes:", (np.array([1, 2]) + np.array([0.5, 0.5])).dtype)
# Step 3: Reshape, transpose, ravel
print()
print("b.T\n", b.T)
print("b.ravel()", b.ravel())
print("b.reshape(2, -1)\n", b.reshape(2, -1), " -- -1 means 'work it out'")
# Step 4: Slicing returns a VIEW; fancy indexing returns a COPY
print()
v = b[0, :] # basic slice -> view
v[0] = 99
print("after writing through a slice, b[0,0] =", b[0, 0], " -- the original changed")
f = b[[0, 2], :] # fancy index -> copy
f[0, 0] = -1
print("after writing through a fancy index, b[0,0] =", b[0, 0],
" -- still 99, not -1: the fancy index wrote to a copy")
print("this single distinction accounts for most NumPy bugs. Use .copy() when")
print("you mean a copy and mean it on purpose.")
# Step 5: Boolean indexing and where
print()
print("a > 40 ->", a > 40)
print("a[a > 40] ->", a[a > 40])
print("np.where ->", np.where(a > 40, "high", "low"))
print("a[a > 40] = 0 assigns in place:", end=" ")
a2 = a.copy(); a2[a2 > 40] = 0; print(a2)
# Step 6: Arithmetic, broadcasting, aggregation along an axis
print()
m = np.array([[40, 57, 37], [69, 36, 38]], dtype=float)
print("m\n", m)
print("m * 2 + 1\n", m * 2 + 1)
print("m - m.mean(axis=0) (column means removed)\n",
np.round(m - m.mean(axis=0), 4))
print("m.sum()", m.sum(), " m.sum(axis=0)", m.sum(axis=0),
" m.sum(axis=1)", m.sum(axis=1))
print("axis=0 collapses the ROWS and leaves one value per column;")
print("axis=1 collapses the columns. Reading it the other way round is the")
print("commonest NumPy slip, and it usually still returns a number.")
print()
print("mean", round(float(m.mean()), 6), " std (population, ddof=0)",
round(float(m.std()), 6), " std (sample, ddof=1)",
round(float(m.std(ddof=1)), 6))
print("NumPy defaults to ddof=0 and pandas to ddof=1 -- they disagree by")
print("design, so state which you used.")Saved as s7_numpy.py and run with python3 s7_numpy.py, it printed:
a (6,) int64 [40 57 37 69 36 38]
b (3, 4) int64
[[ 0 1 2 3]
[ 4 5 6 7]
[ 8 9 10 11]]
c (2, 3) int32 d [0. 0.25 0.5 0.75 1. ]
int32 array assigned 3.9 -> [3 2 3] -- truncated, not rounded, no warning
mixing types promotes: float64
b.T
[[ 0 4 8]
[ 1 5 9]
[ 2 6 10]
[ 3 7 11]]
b.ravel() [ 0 1 2 3 4 5 6 7 8 9 10 11]
b.reshape(2, -1)
[[ 0 1 2 3 4 5]
[ 6 7 8 9 10 11]] -- -1 means 'work it out'
after writing through a slice, b[0,0] = 99 -- the original changed
after writing through a fancy index, b[0,0] = 99 -- still 99, not -1: the fancy index wrote to a copy
this single distinction accounts for most NumPy bugs. Use .copy() when
you mean a copy and mean it on purpose.
a > 40 -> [False True False True False False]
a[a > 40] -> [57 69]
np.where -> ['low' 'high' 'low' 'high' 'low' 'low']
a[a > 40] = 0 assigns in place: [40 0 37 0 36 38]
m
[[40. 57. 37.]
[69. 36. 38.]]
m * 2 + 1
[[ 81. 115. 75.]
[139. 73. 77.]]
m - m.mean(axis=0) (column means removed)
[[-14.5 10.5 -0.5]
[ 14.5 -10.5 0.5]]
m.sum() 277.0 m.sum(axis=0) [109. 93. 75.] m.sum(axis=1) [134. 143.]
axis=0 collapses the ROWS and leaves one value per column;
axis=1 collapses the columns. Reading it the other way round is the
commonest NumPy slip, and it usually still returns a number.
mean 46.166667 std (population, ddof=0) 12.455476 std (sample, ddof=1) 13.644291
NumPy defaults to ddof=0 and pandas to ddof=1 -- they disagree by
design, so state which you used.The view and the copy. A basic slice is a view — writing to
it writes to the original. A fancy index is a copy — writing to it does not. The
output shows both: after the slice write b[0,0] is 99, and after the fancy-index
write it is still 99 rather than \(-1\). Nothing warns you either way, and this one distinction
accounts for most NumPy bugs that survive a code review.
axis=0 collapses the rows and leaves one value per column;
axis=1 collapses the columns. Both return a number, so the wrong one is not an
error — it is an answer to a different question.
And the divisor. np.std defaults to ddof=0 and
Pandas' .std() to ddof=1. On the same six numbers that is
\(12.455476\) against \(13.644291\). They are both right; only an unstated convention is
wrong.
An integer array truncates 3.9 to 3 without a warning; a slice writes through to the original and a fancy index does not; the column sums of the two-row array are 109, 93 and 75 and the row sums 134 and 143; and the six incomes have mean 46.166667, with standard deviation 12.455476 or 13.644291 according to the divisor.
Given five customers (one income missing) and seven orders (one for a customer who does not exist): find and handle the missing value; join the tables three ways and count the rows; total the orders per customer; total them by month and city with a two-level index; and write the result to CSV and read it back.
Complete the pipeline: join the two tables, handle what is missing, aggregate by one key and by two, and write the result back to a file.
skipna; fill with the median.merge with how=\"inner\", \"left\" and \"outer\", counting the rows each keeps.groupby(\"name\") with count, sum and mean of the amount.groupby([\"month\", \"city\"]), then .loc on the outer level and .unstack().to_csv to an in-memory file, read_csv back, and compare.Each operation is taught in Python for Data Analysis and linked from the table at the top of this page. What follows is the pipeline's last stage, with the three places it leaks.
| Function or statement | What it does |
|---|---|
df.isna().sum() | the missing values in each column |
s.mean(skipna=False), s.fillna(s.median()) | a mean that will not skip, and a fill that is a decision |
ORDERS.merge(CUSTOMERS, on="cust_id", how=how) | a join; how decides which unmatched rows are kept |
.groupby(...).agg(["count", "sum", "mean"]) | several summaries per group |
.unstack().fillna(0) | the inner index level to columns, absent cells set to 0 |
to_csv, read_csv | write the frame out, and read it back |
# Stage 6b -- Pandas: the frame, hierarchical indexing, missing data,
# merge, group-by, and writing the result out. The teaching is in Python for
# Data Analysis, Units 2 to 5; this is the pipeline's last stage.
import io
import pandas as pd
pd.set_option("display.width", 100)
# Step 1: The two tables
CUSTOMERS = pd.DataFrame({
"cust_id": [1, 2, 3, 4, 5],
"name": ["Asha Rao", "Biju Menon", "Chitra Das", "Devan Iyer", "Esha Khan"],
"city": ["Vizag", "Kochi", "Kolkata", "Madurai", "Bhopal"],
"income": [40, 57, 37, 69, None],
})
ORDERS = pd.DataFrame({
"order_id": [101, 102, 103, 104, 105, 106, 107],
"cust_id": [1, 2, 3, 4, 1, 1, 9],
"amount": [1240.50, 99.00, 15750.75, 310.25, 480.00, 75.25, 55.00],
"month": ["Jan", "Jan", "Jan", "Feb", "Feb", "Feb", "Feb"],
})
print("customers", CUSTOMERS.shape, " orders", ORDERS.shape)
# Step 2: Missing data: find it, then decide -- do not let a default decide
print()
print("missing values per column:\n", CUSTOMERS.isna().sum().to_string())
print("mean income, skipna default True :", CUSTOMERS["income"].mean())
print("mean income, skipna=False :", CUSTOMERS["income"].mean(skipna=False))
filled = CUSTOMERS.assign(income=CUSTOMERS["income"].fillna(
CUSTOMERS["income"].median()))
print("median-filled incomes :", list(filled["income"]))
print("the fill is a DECISION and belongs in the report; pandas skipping NaN")
print("silently is what makes it easy to forget.")
# Step 3: Merge: the how= argument decides which rows survive
print()
for how in ("inner", "left", "outer"):
m = ORDERS.merge(CUSTOMERS, on="cust_id", how=how)
print(f"merge how={how:6s} -> {len(m):2d} rows, "
f"{int(m['name'].isna().sum())} with no customer, "
f"{int(m['order_id'].isna().sum())} with no order")
print("order 107 points at customer 9, who does not exist; an inner join drops")
print("it without a word. Count the rows before and after every join.")
# Step 4: Group-by with several aggregates
print()
merged = ORDERS.merge(CUSTOMERS, on="cust_id", how="inner")
g = merged.groupby("name")["amount"].agg(["count", "sum", "mean"]).round(2)
g = g.sort_values("sum", ascending=False)
print(g.to_string())
# Step 5: Hierarchical index: two keys, and the two ways to get back
print()
h = merged.groupby(["month", "city"])["amount"].sum().round(2)
print(h.to_string())
print()
print("h.loc['Feb']:\n", h.loc["Feb"].to_string())
print("h.unstack() turns the inner level into columns:")
print(h.unstack().fillna(0).round(2).to_string())
# Step 6: Write the result out, and read it back
print()
buf = io.StringIO()
g.to_csv(buf)
print("to_csv:")
print(buf.getvalue().strip())
back = pd.read_csv(io.StringIO(buf.getvalue()), index_col=0)
print("round trip identical:", back.equals(g))
print()
print("read_csv guesses dtypes from the first rows; on a column of account")
print("numbers with leading zeros it guesses int and destroys them. Pass")
print("dtype={'account': str} whenever the digits are an identifier, not a number.")Saved as s8_pandas.py and run with python3 s8_pandas.py, it printed:
customers (5, 4) orders (7, 4)
missing values per column:
cust_id 0
name 0
city 0
income 1
mean income, skipna default True : 50.75
mean income, skipna=False : nan
median-filled incomes : [40.0, 57.0, 37.0, 69.0, 48.5]
the fill is a DECISION and belongs in the report; pandas skipping NaN
silently is what makes it easy to forget.
merge how=inner -> 6 rows, 0 with no customer, 0 with no order
merge how=left -> 7 rows, 1 with no customer, 0 with no order
merge how=outer -> 8 rows, 1 with no customer, 1 with no order
order 107 points at customer 9, who does not exist; an inner join drops
it without a word. Count the rows before and after every join.
count sum mean
name
Chitra Das 1 15750.75 15750.75
Asha Rao 3 1795.75 598.58
Devan Iyer 1 310.25 310.25
Biju Menon 1 99.00 99.00
month city
Feb Madurai 310.25
Vizag 555.25
Jan Kochi 99.00
Kolkata 15750.75
Vizag 1240.50
h.loc['Feb']:
city
Madurai 310.25
Vizag 555.25
h.unstack() turns the inner level into columns:
city Kochi Kolkata Madurai Vizag
month
Feb 0.0 0.00 310.25 555.25
Jan 99.0 15750.75 0.00 1240.50
to_csv:
name,count,sum,mean
Chitra Das,1,15750.75,15750.75
Asha Rao,3,1795.75,598.58
Devan Iyer,1,310.25,310.25
Biju Menon,1,99.0,99.0
round trip identical: True
read_csv guesses dtypes from the first rows; on a column of account
numbers with leading zeros it guesses int and destroys them. Pass
dtype={'account': str} whenever the digits are an identifier, not a number.The mean that is not the mean. One income is missing.
.mean() skips it and returns \(50.75\) — the mean of four values reported
as though it were the mean of five. skipna=False returns nan, which
is the honest answer to a question that cannot be answered. Filling with the median is a
decision, and it belongs in the report beside the number.
The join that loses a row. Order 107 points at customer 9, who does not
exist. inner gives 6 rows, left 7, outer 8 — and
only the last two make the problem visible. Count the rows before and after every
join, and explain any difference; an unexplained drop is a data fault, not a join
setting.
The hierarchical index. Grouping by month and city gives a two-level index;
.loc['Feb'] selects an outer level and .unstack() turns the inner one
into columns. The unstacked table has zeros where a city had no orders in a month — those
zeros were absent before fillna(0), and deciding that absent means zero
is a decision too.
The write-out. The frame round-trips through CSV identically. It would not
if any column held an identifier made of digits: read_csv guesses dtypes from the
first rows and an account number with leading zeros comes back as an integer, silently
shortened. Pass dtype={'account': str} whenever the digits are a name rather than
a quantity.
The mean income is 50.75 over the four known values and undefined over five; the median fill gives 48.5. The joins keep 6, 7 and 8 rows, the difference being order 107 for a customer who does not exist. Chitra Das has the largest total, 15,750.75 from one order, and Asha Rao the most orders, three totalling 1,795.75. The summary round-trips through CSV unchanged.
split(","). It works until a field
contains a comma, and then it shifts every later column without raising.struct format. Fix the byte order and
the alignment in the format string, or the file is unreadable on another machine and,
worse, readable-but-wrong on some..+ where .+? belongs, and a pattern
never tested against what it should not match."... WHERE id = %s" % value
is an injection; cur.execute(sql, (value,)) is not. The placeholder is not a
style preference.update_one with replace_one, or
forgetting that $unwind drops empty arrays.axis= the wrong way round.