DEV Community: anders lee

What I learned turning government property tax spreadsheets into one county dataset

anders lee — Thu, 15 Oct 2026 05:30:00 +0000

I wanted a simple answer to a simple question: how much property tax does a typical homeowner pay in each U.S. county, and what are the actual local rates behind it?

The data exists, but it's spread across the Census Bureau and fifty state revenue departments, each with its own format. I started with the national picture plus three states — Texas, North Carolina and Florida — and ended up with five clean CSVs: 3,133 counties, 51 states, 4,849 Texas taxing units, 690 North Carolina county/municipal rate rows and 67 Florida counties.

Here are the parts that took longer than expected.

1. The national layer: Census ACS, and the values that aren't numbers

The American Community Survey (5-year estimates, 2019–2023) has everything needed for an "effective rate":

B25103 – median real estate taxes paid
B25077 – median value of owner-occupied homes
B25119 – median household income by tenure (owners only)
B25003 / B01003 – tenure and population

Effective rate = median tax ÷ median home value. Easy — until you sort by it.

Sixteen counties report a median tax of exactly 10001, and seven report exactly 199. Those aren't measurements. The ACS caps this table: 10001 means "$10,000 or more" and 199 means "less than $200". If you treat them as numbers, Marin County and most of northern New Jersey look like they pay the same bill, and their effective rates are understated.

I kept the numeric value (so the column stays numeric) and added a flag:

flag = "10000+" if med_tax >= 10001 else "<200" if med_tax <= 199 else ""

For those rows the effective rate is a bound, not an estimate. Worth checking for in any ACS median you use — home value and income tables have their own caps.

Connecticut is the other trap. Since 2022 the Census uses Connecticut's nine planning regions as county equivalents instead of the eight historical counties, so FIPS codes from older shapefiles won't join.

2. Texas: one spreadsheet layout per year

The Texas Comptroller publishes every taxing unit's rate — counties, cities, school districts, hospital districts, MUDs, emergency services districts. That's what makes Texas bills hard to estimate: a single address can sit in six or seven units.

Two surprises:

The 2024 and 2025 files don't share a layout. Columns moved between years, so where a file has a usable header row I look columns up by header name instead of position:

idx = {h: i for i, h in enumerate(header_row)}
rate = row[idx["TOTAL COUNTY TAX RATE"]]

Unit IDs changed shape too. The 2024 total-rate file identifies each taxing unit with a five-part ID; the 2025 file uses three parts. Joining on the first three parts of the old ID matched units year over year:

key = "-".join(str(unit_id).split("-")[:3])

I also derive a unit_type (county, school, city, special, emergency, hospital, college) from the unit's name and ID, because the file doesn't label it. And 347 units have a rate of exactly 0 — mostly utility districts that exist on paper but levied nothing this year. Keep them, but exclude them before computing averages.

3. North Carolina: per $100, plus an "effective" rate

North Carolina's Department of Revenue publishes county and municipal rates per $100 of value, the combined rate, and an effective combined rate adjusted by each county's sales-assessment ratio. That last column matters: counties reappraise on different cycles (some last did it in 2018), so nominal rates aren't comparable across counties — effective ones are closer.

Small thing: the source writes place names like Mcadenville and Mcdonald. One regex fixed it:

name = re.sub(r"\bMc([a-z])", lambda m: "Mc" + m.group(1).upper(), name)

4. Florida: mills, and one county with no county tax

Florida reports millage — dollars per $1,000 — split into county operating, county debt, school operating, school debt, dependent and independent special districts, plus levies that only cover part of a county (MSTUs, cities) expressed as a countywide equivalent.

Mixing units is the easiest mistake in this whole project: Texas and North Carolina are per $100, Florida per $1,000. I kept the source unit in every column name (rate_per_100_2025, total_mills) rather than converting silently.

And Duval County shows a county operating millage of zero. That's not missing data: Jacksonville and Duval are consolidated, so the levy appears as a municipal rate instead.

5. What the data says

A few results once everything lined up:

The median U.S. homeowner pays $2,969 a year, about 0.98% of home value.
State medians range from 0.27% (Hawaii) to 2.23% (New Jersey).
Log home value vs. log tax bill correlates at about 0.66 — strong, but the scatter around the line is where state policy shows up.
Northern New Jersey and the New York suburbs dominate the "share of income" ranking: around 7% of owner household income goes to property tax.

Takeaways for working with government data

Look for sentinel values before sorting or averaging. Round numbers at the edges of a distribution are suspicious.
Put the unit in the column name. Per $100, per $1,000 and percent all look like small decimals.
Match on headers, not positions, when a publisher has more than one year of files.
Treat zeros and blanks differently and document which is which.
Read the footnotes. Duval, Connecticut and the ACS caps were all documented — just not in the spreadsheet itself.

The compiled CSVs are public under CC BY 4.0 as the U.S. County Property Tax Rates dataset on Kaggle and Hugging Face. If you just want to look up a county or estimate a bill, I built the calculators on top of it at countypropertytaxes.com.

Writing a PDF by hand: graph paper that prints at exactly the right size

anders lee — Mon, 12 Oct 2026 06:34:23 +0000

I needed graph paper with 1/4 inch squares. Not "about a quarter inch": a floor plan at one square
per foot is only useful if four squares measure exactly one inch. Many of the free printables I found
were PNGs or PDFs wrapping a raster image, and the grid came out at 0.24 inch once printed.

Two things cause that. The image has a resolution that doesn't map cleanly to inches, and the PDF
page is the full sheet with the grid running to the edge, so the print dialog shrinks it into the
printable area. Both go away if the grid is generated as vector lines on a page that already
leaves a margin. A PDF that contains only lines turns out to be small enough to write by hand.

Points, not pixels

PDF coordinates are in points: 1/72 inch. That makes the units trivial:

const PT_PER_IN = 72;
const PT_PER_MM = 72 / 25.4;
// US Letter is 612 × 792 pt, A4 is 595.28 × 841.89 pt

A 1/4 inch square is 18 pt, a 5 mm square is 14.17 pt. There is no DPI anywhere, so nothing gets
resampled.

Fit whole squares, then center them

The second rule: never cut a square at the margin. Take the area inside the margins, see how many
whole squares fit, and center that block:

function fitCells(area, s) {
  const nx = Math.max(1, Math.floor((area.x1 - area.x0) / s + 1e-6));
  const ny = Math.max(1, Math.floor((area.y1 - area.y0) / s + 1e-6));
  const x0 = (area.x0 + area.x1 - nx * s) / 2;
  const y0 = (area.y0 + area.y1 - ny * s) / 2;
  return { nx, ny, x0, y0 };
}

The 1e-6 matters: 8 inches of space divided by 18 pt is exactly 32 squares, and without the
epsilon floating point can give 31.999999 and lose a column. With 1/4 inch margins a Letter page
holds 32 × 42 squares; A4 holds 31 × 44.

Drawing the grid is then two loops of lines, with every Nth line heavier (a bold line per inch on
quarter-inch paper).

The content stream

A PDF page's drawing is a text stream of postfix operators. For lines you need four of them:
w (line width), RG (stroke color), m / l (move / line to) and S (stroke). One wrinkle:
PDF's y axis points up, so if you lay the page out top-down like a screen, flip it when writing:

0.35 w 0.557 0.694 0.839 RG
36 774 m 36 18 l
54 774 m 54 18 l
...
S

Grouping all lines of the same width and color under one w … RG … S keeps the stream short.
A full Letter page of 1/4 inch grid is about 76 lines.

Two tricks for the other paper types:

Dots are zero-length lines with round caps: set 1 J, then x y m x y l with the line width equal to the dot diameter. Far smaller than drawing circles.
Circles (for polar paper) are four cubic Béziers per circle, with control points at 0.5523 × r from each quadrant point—the usual approximation, invisible on paper.

The file around it

A minimal PDF is a handful of numbered objects, a cross-reference table giving the byte offset of
each object, and a trailer. This is the whole writer:

export function toPDF(content, w, h, pages = 1) {
  const objs = [];
  const add = (s) => objs.push(s);
  const pageIds = Array.from({ length: pages }, (_, i) => 6 + i);
  add(`<< /Type /Catalog /Pages 2 0 R >>`);                                                    // 1
  add(`<< /Type /Pages /Kids [${pageIds.map((i) => `${i} 0 R`).join(" ")}] /Count ${pages} >>`); // 2
  add(`<< /Type /Font /Subtype /Type1 /BaseFont /Helvetica /Encoding /WinAnsiEncoding >>`);     // 3
  add(`<< /Length ${content.length} >>\nstream\n${content}\nendstream`);                     // 4
  add(`<< /Title (Graph paper) /Producer (graphpaperpdf.net) >>`);                             // 5
  for (let i = 0; i < pages; i++)
    add(`<< /Type /Page /Parent 2 0 R /MediaBox [0 0 ${w} ${h}] /Resources << /Font << /F1 3 0 R >> >> /Contents 4 0 R >>`);

  let out = "%PDF-1.4\n%\xE2\xE3\xCF\xD3\n";
  const offsets = [];
  objs.forEach((o, i) => { offsets.push(out.length); out += `${i + 1} 0 obj\n${o}\nendobj\n`; });
  const xref = out.length;
  out += `xref\n0 ${objs.length + 1}\n0000000000 65535 f \n`;
  for (const off of offsets) out += `${String(off).padStart(10, "0")} 00000 n \n`;
  out += `trailer\n<< /Size ${objs.length + 1} /Root 1 0 R /Info 5 0 R >>\nstartxref\n${xref}\n%%EOF\n`;
  return Uint8Array.from(out, (ch) => ch.charCodeAt(0) & 255);
}

Things that bit me:

Offsets are bytes, not characters. Keep everything single-byte (the content is ASCII, the binary comment on line 2 is Latin-1) and string.length is the byte offset. One stray UTF-8 character and every offset after it is wrong.
Each xref line is exactly 20 bytes, including the space before \n. Viewers are forgiving; some printers' PDF interpreters are not.
/Length must match the stream. Same single-byte rule.

The nice part is /Contents 4 0 R on every page: 100 pages of graph paper share one content
stream. A one-page 1/4 inch grid is 2.3 KB; a hundred pages is 18.8 KB.

Text, and the π problem

Labels (axis numbers on a coordinate plane, degrees on polar paper) use the built-in Helvetica, so
no font embedding. That covers digits, minus and the degree sign (\260 in WinAnsi). Then I added
radian labels, and π isn't in that encoding.

The fix is old-school: the standard Symbol font has π at the position of the letter p. So a
label like 5π/6 becomes one text object that switches fonts mid-string:

BT 0.333 0.333 0.333 rg 300 410 Td
/F1 8 Tf (5) Tj /F2 8 Tf (p) Tj /F1 8 Tf (/6) Tj ET

Text position advances after each Tj, so the pieces line up. Centering the label needs the
advance widths, which for a handful of glyphs is a small lookup table (digits are 556/1000 em in
Helvetica, π is 549 in Symbol).

Testing a file format

The tests that caught real bugs check structure rather than pixels: for every paper type, parse
startxref, confirm it points at xref, and confirm each offset in the table lands on
N 0 obj; check that /Length bytes later the stream says endstream. Geometry tests pin the
square counts (32 × 42 on Letter at 1/4 inch), so a refactor can't silently shave off a column.

Printing is the last mile

Even a perfect PDF prints small if the dialog is set to "Fit to page". Since the page already has a
1/4 inch margin—wider than the unprintable border of most home printers—"Actual size" / 100% is
safe. Measuring ten squares (2.5
inches on quarter-inch paper) is the quickest check.

I built this into a free generator at graphpaperpdf.net: square grids
in inches or millimetres, dot grid, isometric, polar, coordinate planes, lined and hexagon paper,
on Letter or A4. The PDF is generated in the browser, so nothing is uploaded. If you have a grid
type it doesn't do yet, I'd like to hear about it.

Three equations that found misprints in a national steel standard

anders lee — Sat, 10 Oct 2026 15:15:32 +0000

I needed clean data for steel beams, channels and angles: weight per metre, dimensions and
section properties. For Indian sections the source is IS 808, the national standard, made public by
Public.Resource.Org. The 2021 edition is a born-digital PDF, the 1989 edition and the 1964 handbook
are scans.

Transcribing tables is easy. Knowing the transcription (and the printed table) is right is the
interesting part. Section tables have a nice property: their columns are not independent. A few
physical relations tie them together, so every row can check itself.

The three relations

1. Mass follows from area. Steel is 7850 kg/m³. One metre of a section with area A cm² is
A × 10⁻⁴ m³, so

mass (kg/m) = 0.785 × A (cm²)

2. Radius of gyration follows from inertia and area.

r = sqrt(I / A)

3. Elastic modulus follows from inertia and depth (for doubly symmetric beams, about the major axis):

Zx = Ix / (D / 2)

That is enough to check most columns of most rows. In Python, the core of it is a dozen lines:

import math

TOL = 0.02  # tables print 3 to 4 significant figures

def close(printed, derived, tol=TOL):
    # small values are printed to 0.1, so allow an absolute slack too
    return abs(printed - derived) <= tol * abs(derived) + 0.06

def check(row):
    problems = []
    if not close(row["M"], 0.785 * row["A"]):
        problems.append(("M", row["M"], round(0.785 * row["A"], 2)))
    for r, i in (("rx", "Ix"), ("ry", "Iy")):
        derived = math.sqrt(row[i] / row["A"])
        if not close(row[r], derived):
            problems.append((r, row[r], round(derived, 2)))
    if row.get("D") and not close(row["Zx"], row["Ix"] / (row["D"] / 20)):  # D in mm, Z in cm³
        problems.append(("Zx", row["Zx"], round(row["Ix"] / (row["D"] / 20), 1)))
    return problems

Run it over every row and anything it flags is either a transcription error (mine) or a misprint
(theirs). I fixed mine; the rest went into an errata.csv next to the source data, so the source
CSVs keep the printed value and the build applies the correction.

What it found

In IS 808:1989 (including its 1992 amendment):

Section	Column	Printed	From the relations	Check
MC 100	Zx	33.5	38.4	Ix / (D/2) = 192 / 5.0
MC 175 (heavy)	mass	22.7	21.7	0.785 × 27.6
MC 250	ry	2.37	2.33	√(211 / 39.0)
ISA 35×35×6	Iu	5.5	6.4	also smaller than the 5 mm angle's 5.6
ISA 65×65×4	Ix, Iu	21.70, 34.50	20.2, 32.0	the row added by the amendment

In IS 808:2021:

Section	Column	Printed	Correct	Note
MC 75	rz	39.4 mm	29.4 mm	√(78.2×10⁴ / 908); the 1989 edition had 2.94 cm
MC 175*	mass	22.7	21.4	0.785 × 27.3 — the same 22.7 as in 1989
every ISA ≥ 100 mm	rv	×10		e.g. 200.0 printed where √(Iv / A) gives 20.0 mm

The MC 175 one is my favourite: a mass that does not match its own area, carried from the 1989
table into the 2021 one, 32 years later.

Big tables from other places are not immune either. The AISC Shapes Database (the American
reference spreadsheet) has its metric and US columns side by side; comparing them row by row found
one cell, the perimeter of S24×90, with an extra zero (18400 mm instead of 1840).

The 1964 value everybody still uses

The relations also explained a long-standing confusion. Search for the weight of ISMB 200 and you
get 25.4 kg/m from most charts and 24.2 kg/m from a few. Both are "right":

Edition	Flange thickness	Area	Mass
SP 6(1):1964 handbook	10.8 mm	32.33 cm²	25.4 kg/m
IS 808:1989	10.0 mm	30.8 cm²	24.2 kg/m
IS 808:2021	10.0 mm	30.8 cm²	24.17 kg/m

The 1989 revision thinned the flange. Charts copied from the old handbook never updated, so a
12 m beam is quoted 15 kg heavier than what is rolled today. The same happened to ISMB 100
(11.5 → 8.9 kg/m) and ISMB 300 (44.2 → 46.0 kg/m, heavier this time).

Takeaways for any engineering dataset

Look for redundancy. Engineering tables usually contain derived columns. Recompute them instead of trusting them; disagreement is signal.
Keep the printed value. Store corrections separately (errata.csv with the reason and the arithmetic) rather than editing the source, so anyone can audit the change.
Check two sources against each other where they exist: two open libraries for European sections, metric vs US columns for AISC.
Old editions matter. If users quote a number that does not match the current standard, the previous edition is often why — and showing both is more useful than silently picking one.

The cleaned tables are on GitHub as CSV/JSON:
steel-section-weights. I also built them
into a free reference site with a weight calculator and charts that show all three editions side by
side: steelweight-calculator.com.