--- title: 'Filtering and Querying' source: 'https://academia.sh/en/courses/data-analysis-with-python/filtering-and-querying' course: 'Data Analysis with Python' language: en updated: '2026-10-07T04:31:10+00:00' license: 'CC BY-SA 4.0' objectives: - 'Shows by row count that a comparison made with a missing value always returns false and that negation brings the missing rows back' - 'Compares the three spellings of the same condition: the numeric filter, the negation and the text comparison' - 'Counts that two logically equivalent expressions give different row counts in a combined condition and writes down why' - 'Measures the cost of a filter in two items: the pass count and the bytes held in the intermediate table' --- # Filtering and Querying Readings with a meter reading above 120,000 are filtered with three separate spellings and three different row counts come out: the numeric comparison gives 1,835 rows and 36,435.83 m³, the negation of the same condition gives 2,026 rows and 40,356.71 m³, and the same comparison with the column read as text gives 1,915 rows and 38,035.93 m³. The difference is in the missing values: the negation takes in all 191 missing rows, the text comparison takes in the 80 'n/a' cells because a letter counts as greater than a digit. When two conditions are combined the difference grows, and two expressions written as equivalent in logic return 446 and 502 rows. A manual filter chain builds an intermediate table of 16,184 bytes in 5,034 passes, while the mask makes 6,398 passes and holds 3,399 bytes. Slicing gives a range, but what is wanted in analysis is not a range but a condition: "the readings whose meter reading is above 120,000". A conditional selection arises from a comparison, and a comparison carries a silent assumption: the assumption that every value in the dataset is comparable. In the previous lesson the meter reading column was seen to have 111 empty cells and 80 cells with "n/a" written in. A value whose being below or above a threshold is unknown falls on which side of the filter. The answer to this lesson is not a single sentence, because it changes with the spelling. The same question can be written in three separate forms, all three look correct, none of them raises an error, and the three return different row counts. That is the measure: the same condition, three spellings, three numbers. The difference always comes from the same place, the missing values, but in each spelling it falls to a different side. - **DF14.** A comparison made with a missing value returns **false**. This does not mean that the missing value is small; because the comparison cannot produce an answer, it has chosen an answer on its own. - **DF15.** Because the comparison maps "unknown" to "false", this mapping is **not preserved under negation**: in a row where `x > e` is false, `x <= e` is also false, and therefore `not(x <= e)` is true. The same row is outside in one spelling and inside in the other. - **DF16. A boolean mask** is an array that carries one truth value per row. The mask does not copy rows; conditions are combined on the mask and the result is produced once. - **DF17. Querying** is writing the filter condition as an expression. The form of the spelling does not change the result; what changes it is what the comparisons inside the condition do on a missing value. ## A Missing Value Cannot Be Compared ```python # dataset.py — MODEL: the long-format reading table of the previous lesson is # regenerated from the same seed. The dataset is fiction; the detail of the # generator is in the first lesson of the topic. import math M32 = 0xFFFFFFFF SEED, PERIODS = 20260218, ["2026-01", "2026-02", "2026-03"] REGION = [("north", 0.28, 21), ("south", 0.22, 17), ("east", 0.18, 26), ("west", 0.14, 14), ("central", 0.18, 23)] def rng(seed: int): s = ((seed ^ (seed >> 16)) * 2246822507) & M32 s = ((s ^ (s >> 13)) * 3266489909) & M32 s ^= s >> 16 def next_value() -> float: nonlocal s s = (s * 1664525 + 1013904223) & M32 return s / 4294967296 return next_value def pick(u: float, weights: list[float]) -> int: t = 0.0 for i, p in enumerate(weights): t += p if u < t: return i return len(weights) - 1 subscribers, dropped = [], [] for i in range(1400): r = rng(SEED + i) name, _, base = REGION[pick(r(), [b[1] for b in REGION])] r(); r() # the household and satisfaction draws are not used in this lesson (dropped if r() < 0.046 else subscribers).append( {"subscriber_id": 10001 + i, "region": name, "base": base}) valid = {k["subscriber_id"] for k in subscribers} pairs: dict[tuple[int, str], float] = {} for k in subscribers + dropped: r = rng(SEED + 7000 + k["subscriber_id"]) for d in range(pick(r(), [0.05, 0.12, 0.21, 0.62])): t = 0.0 if r() < 0.038 else math.floor(k["base"] * math.exp( (r() + r() + r() - 1.5) * 0.62) * (1 - d * 0.05) * 100 + 0.5) / 100 if k["subscriber_id"] in valid: pairs.setdefault((k["subscriber_id"], PERIODS[d]), t) r() LONG = [{"subscriber_id": a, "period": d, "consumption_m3": t} for (a, d), t in pairs.items()] # the meter_reading column comes from the field form as in the previous lesson: some # of it is empty, and "n/a" has been written by hand in some of it. import sys cumulative: dict[int, float] = {} records = [] for o in LONG: no, t = o["subscriber_id"], o["consumption_m3"] cumulative[no] = cumulative.get(no, 0.0) + t u = rng(SEED + 9100 + no * 4 + PERIODS.index(o["period"]))() raw = "" if u < 0.033 else "n/a" if u < 0.055 else str( 100000 + (no - 10000) * 37 + int(cumulative[no])) records.append({"subscriber_id": no, "period": o["period"], "consumption_m3": t, "meter_reading": raw}) MISSING = None def to_number(h: str): """Converts text to a number; every cell that cannot be converted becomes MISSING.""" try: return int(h) except ValueError: return MISSING numeric = [to_number(k["meter_reading"]) for k in records] text_values = [k["meter_reading"] for k in records] print(f"{len(records)} rows: {sum(1 for v in numeric if v is not MISSING)} numeric meter readings, " f"{sum(1 for k in records if k['meter_reading'] == '')} empty, " f"{sum(1 for k in records if k['meter_reading'] == 'n/a')} 'n/a'") print(f"numeric meter reading range {min(v for v in numeric if v is not MISSING)}.." f"{max(v for v in numeric if v is not MISSING)}") ``` ``` 3199 rows: 3008 numeric meter readings, 111 empty, 80 'n/a' numeric meter reading range 100067..151812 ``` Of the three thousand one hundred ninety-nine rows, 3,008 carry a numeric meter reading and 191 do not. The loss of these 191 rows dates from the recording stage: the meter could not be read, or the form was filled in incompletely. Both come to the same result: for that row the meter reading is unknown. "Unknown" is not a value, it is an **information state**, and a comparison operator cannot express it. A comparison has to give one of two options, and what it chooses is "false". This choice is a defensible default, because it is reasonable for a filter not to select a row it knows nothing about. The problem is not in the default itself but in its being **unwritten**. The distinction has to be drawn once more here: a missing value and zero are not the same thing. There are readings with zero consumption in the dataset, and these are measured records; the meter was read, and no consumption came out. They enter the comparison, they fall to one side, and they carry no uncertainty. The 191 rows with a missing meter reading, however, were not measured. A dataset that keeps the two cases in the same column has to write down, while writing the filter, which of them is being compared. ## The Same Question, Three Spellings ```python # three_spellings.py — the same question in three forms: readings with a meter reading above 120000. THRESHOLD = 120000 def mask_numeric(v): """A missing value cannot enter the comparison, the result returns false.""" return v is not MISSING and v > THRESHOLD def mask_negation(v): """The negation of the same condition: in a missing row the inner comparison is also false.""" return not (v is not MISSING and v <= THRESHOLD) s1 = [mask_numeric(v) for v in numeric] s2 = [mask_negation(v) for v in numeric] s3 = [h > str(THRESHOLD) for h in text_values] WIDTH = [42, 8, 12] show = lambda h: "".join(str(v).rjust(WIDTH[k]) if k else str(v).ljust(WIDTH[0]) for k, v in enumerate(h)) total_of = lambda m: round(sum(k["consumption_m3"] for k, b in zip(records, m) if b), 2) print(show(["spelling", "rows", "total m3"])) print(show([f"numeric meter_reading > {THRESHOLD}", sum(s1), total_of(s1)])) print(show([f"negation not(meter_reading <= {THRESHOLD})", sum(s2), total_of(s2)])) print(show([f"text meter_reading > '{THRESHOLD}'", sum(s3), total_of(s3)])) print(f"\nthe second spelling has {sum(s2) - sum(s1)} rows more than the first: all of the missing") print(f"the third spelling has {sum(s3) - sum(s1)} rows more than the first: cells written as 'n/a', " f"because text comparison counts a letter greater than a digit") ``` ``` spelling rows total m3 numeric meter_reading > 120000 1835 36435.83 negation not(meter_reading <= 120000) 2026 40356.71 text meter_reading > '120000' 1915 38035.93 the second spelling has 191 rows more than the first: all of the missing the third spelling has 80 rows more than the first: cells written as 'n/a', because text comparison counts a letter greater than a digit ``` Three rows ask the same question and give three separate numbers: 1,835, 2,026 and 1,915 rows. The consumption total that enters the report is also three separate values, and between the lowest and the highest there are 3,920.88 m³. The second spelling is the most insidious one, because negation looks natural when read: "those not below the threshold" is taken to be the same as "those that exceed the threshold". It is not. In a row with a missing meter reading the comparison `meter_reading <= 120000` returns false, because that is the answer chosen for a value that cannot be compared; the negation of that false is true. So 191 rows, although nothing at all is known about them, enter the set "those that exceed the threshold", and 3,920.88 m³ of consumption is added to the reported total. The third spelling is a direct continuation of the previous lesson. The meter reading column had been read as text because of 80 "n/a" entries; in a column read as text the `>` operator still works, but it works in dictionary order. Because all the numeric meter readings have six digits, the text comparison gives the same result as the numeric comparison on them; the only place they part is the `'n/a'` cells, and the letter "n" is greater than the digit "1". The result is exactly 80 rows more, and none of these rows has a meter reading. ## Combined Condition and the Cost of the Filter ```python # combined.py — when two conditions are combined the effect of the missing multiplies: # two expressions written as equivalent in logic do not give the same row count. def and_both(v, t): return (v is not MISSING and v > THRESHOLD) and t > 25.0 def not_or(v, t): return not ((v is not MISSING and v <= THRESHOLD) or t <= 25.0) b1 = [and_both(v, k["consumption_m3"]) for v, k in zip(numeric, records)] b2 = [not_or(v, k["consumption_m3"]) for v, k in zip(numeric, records)] print(f"(meter_reading > {THRESHOLD}) and (consumption > 25) : {sum(b1)} rows, {total_of(b1)} m3") print(f"not((meter_reading <= {THRESHOLD}) or (consumption <= 25)) : {sum(b2)} rows, {total_of(b2)} m3") print(f"the two expressions are equivalent in logic, the result differs by {sum(b2) - sum(b1)} rows") def manual_chain(): """For every condition the dataset is scanned from the start and the intermediate table is copied.""" middle = [(k, v) for k, v in zip(records, numeric) if v is not MISSING and v > THRESHOLD] final = [(k, v) for k, v in middle if k["consumption_m3"] > 25.0] return middle, final, len(records) + len(middle) middle, final, manual_passes = manual_chain() mask = bytearray(b1) print(f"\nmanual chain: {len(final)} rows, {manual_passes} passes, intermediate table " f"{sys.getsizeof([k for k, _ in middle])} bytes") print(f"mask : {sum(mask)} rows, {2 * len(records)} passes, mask " f"{sys.getsizeof(mask)} bytes, 1 line of code") print(f"the mask does not win in pass count, it wins in the intermediate table: " f"{sys.getsizeof([k for k, _ in middle]) - sys.getsizeof(mask)} bytes") ``` ``` (meter_reading > 120000) and (consumption > 25) : 446 rows, 13906.21 m3 not((meter_reading <= 120000) or (consumption <= 25)) : 502 rows, 15675.3 m3 the two expressions are equivalent in logic, the result differs by 56 rows manual chain: 446 rows, 5034 passes, intermediate table 16184 bytes mask : 446 rows, 6398 passes, mask 3399 bytes, 1 line of code the mask does not win in pass count, it wins in the intermediate table: 12785 bytes ``` When two conditions are combined the difference does not shrink, it changes direction. `A and B` and `not(not A or not B)` are the same thing in two-valued logic and can be exchanged in a textbook. Here they cannot be exchanged, because the atomic comparison is not two-valued: it squeezes three states, that is true, false and unknown, into two values and maps "unknown" to "false". This mapping is not preserved under negation. The result is 502 rows instead of 446, and the reported total is 15,675.30 m³ instead of 13,906.21 m³. The cost of the filter is in the second output, and here the gain of the abstraction is not where it is expected. The manually written chain applies every condition in a separate pass and **copies** a table in between: 5,034 passes, an intermediate table of 16,184 bytes. The mask evaluates every condition on all rows, makes 6,398 passes, that is, it takes more passes; where it gains is memory. The mask is 3,399 bytes and no row is copied; the difference is 12,785 bytes and it is preserved proportionally as the dataset grows. That querying writes the condition as an expression does not change this table either. Whether the condition is written as a string or the masks are combined with an operator, the result comes out of the same three items: which comparisons there are, what they return on a missing value, and how they are combined. The form of the spelling is a readability decision; what determines the row count is the behavior of the comparison on a missing value. ## The Third Set of the Filter ```python # three_sets.py — the defensible spelling: the filter produces not two but three sets # and the sum of the three gives the whole dataset. above = sum(1 for v in numeric if v is not MISSING and v > THRESHOLD) below = sum(1 for v in numeric if v is not MISSING and v <= THRESHOLD) unknown = sum(1 for v in numeric if v is MISSING) print(f"above the threshold {above}, below {below}, unknown {unknown}, " f"total {above + below + unknown}") print(f"unknown share: {round(100 * unknown / len(records), 2)} percent; " f"if all of it is written onto the above group, {above} becomes {above + unknown}, " f"that is a {round(100 * unknown / above, 1)} percent swing") ``` ``` above the threshold 1835, below 1173, unknown 191, total 3199 unknown share: 5.97 percent; if all of it is written onto the above group, 1835 becomes 2026, that is a 10.4 percent swing ``` When a filter spelling reports these three numbers separately, all the uncertainty of the lesson disappears. 1,835 rows are above the threshold, 1,173 rows are below it, no decision could be made about 191 rows, and the sum of the three is the whole dataset. This sum check cannot be made in any of the spellings that report a single number: which number is right between `1,835` and `2,026` is determined not by looking at the dataset but only by deciding where the missing are to be written. The size of the uncertainty can also be read here. The unknown rows are 5.97% of the dataset, but their effect on the reported number is 10.4%, because the missing are proportioned not to the whole dataset but to the part that passes the filter. The effect on the report of a missing rate that looks small depends on where the threshold is put, and it grows as the threshold narrows. That is why the missing rate alone is not a sufficient check; what has to be measured is the ratio of the missing to the **filter result**. ## Summary - A comparison made with a missing value returns false; this does not mean that the missing value is small, it means that the comparison chose an answer where it could not produce one. - The same condition gives three row counts in three spellings: numeric 1,835, negation 2,026, text comparison 1,915; the reported total moves between 36,435.83 and 40,356.71 m³. - The negation takes in all 191 missing rows; the text comparison takes in the 80 "n/a" cells, because in dictionary order a letter is greater than a digit. - In a combined condition two expressions written as equivalent in logic return 446 and 502 rows, because the comparison maps "unknown" to "false" and this mapping is not preserved under negation. - The gain of the mask is in memory, not in passes: the manual chain builds an intermediate table of 16,184 bytes in 5,034 passes while the mask makes 6,398 passes and holds 3,399 bytes. ## Next Step A filter selects rows, but a report does not ask for individual rows; it asks for a summary by breakdown. The question "average consumption per region" first splits the dataset, applies a calculation to each part, and combines the results in a single table. This three-step job fits into a single call, and that call carries two separate decisions: which group the rows that carry a missing value in the group key enter, and whether the aggregation calculation includes the missing values in the denominator. The next lesson measures these two decisions separately and counts how many rows are lost without appearing in any group.