Mapping Property Records by County and ZIP in QGIS or kepler.gl

A property file answers “where” questions well: which counties have the most absentee ownership, which ZIP codes hold the most free-and-clear homes, where values are rising fastest. Those answers read best as a map. This guide shows how to get from a bulk export to a map in QGIS or kepler.gl: aggregate by county or ZIP, join to official boundary files, and colour the result. It also covers what to do when you want individual properties on the map.

Column names follow the Bulk Property Records export; the field dictionary describes each one.

First: what location data the export has

An export describes location through identifiers and addresses, not coordinates. There is no latitude or longitude column. That shapes everything below:

Mapping by area needs no geocoding at all, which is why it is the place to start. Points need a geocoding step, covered at the end.

Step 1: Aggregate before you map

A choropleth colours each area by one number. Compute those numbers first, in a tool that handles the full file easily, and bring only the summary into the mapping software. DuckDB does this straight from the CSV parts without a load step (see filtering with DuckDB for setup):

-- DuckDB: one row per county, ready to join to county boundaries.
-- The export names the county ("State", "County"); the five-digit code comes
-- from the Census reference file national_county2020.txt (from census.gov).
CREATE MACRO county_key(n) AS
  trim(regexp_replace(regexp_replace(regexp_replace(upper(coalesce(n, '')),
    '\b(COUNTY|PARISH|BOROUGH|CENSUS AREA|CITY AND BOROUGH|MUNICIPALITY)\b', '', 'g'),
    '[^A-Z ]', '', 'g'), '\s+', ' ', 'g'));

COPY (
  WITH ref AS (
    SELECT STATE AS st, county_key(COUNTYNAME) AS name_key, STATEFP || COUNTYFP AS geoid
    FROM read_csv('national_county2020.txt', delim = '|', header = true, all_varchar = true)
  ),
  p AS (
    SELECT *, upper(trim("State")) AS st, county_key("County") AS name_key
    FROM read_csv('exports/*.csv', header = true, all_varchar = true)
  )
  SELECT
    ref.geoid                                                        AS geoid,
    p.st                                                             AS state,
    p."County"                                                       AS county,
    count(*)                                                         AS records,
    -- No absentee column: "Owner Occupied" = false is the approximation.
    round(100.0 * avg(CASE WHEN p."Owner Occupied" = 'false'
                           THEN 1 ELSE 0 END), 1)                    AS absentee_pct,
    median(TRY_CAST(p."Estimated Value" AS DOUBLE))                  AS median_value,
    median(TRY_CAST(p."Equity Percent" AS DOUBLE))                   AS median_equity_pct
  FROM p LEFT JOIN ref USING (st, name_key)
  GROUP BY 1, 2, 3
) TO 'county_summary.csv' (HEADER);

The output has one row per county and a geoid column built from the Census state and county codes, so it is five characters and matches the boundary file exactly. Rows with an empty geoid are county names the lookup did not recognise; check them before mapping. Everything else is computed from the export itself; no figure is typed in.

Map rates, not counts

A map of record counts is mostly a map of where people live: big counties are dark, small ones pale. For most questions, a share or a median says more:

Keep the record count in the summary anyway. It is the denominator, and you will want it in tooltips to judge whether a striking colour rests on many records or few.

Step 2: Get boundary files

The U.S. Census Bureau publishes free boundary files for counties and for ZIP Code Tabulation Areas (ZCTAs). For thematic maps, use the cartographic boundary files rather than the full TIGER/Line files: they are generalised for display, much smaller, and clipped to the shoreline. The 1:500,000 versions are a good balance of detail and size.

Both keys are text with leading zeros. Every tool below needs the join column on both sides to be text, or counties in states whose codes start with 0 silently fail to join.

Step 3a: Join and colour in QGIS

QGIS is free and handles a national county layer comfortably. The join is done entirely in the interface:

1. Add the boundaries: Layer → Add Layer → Add Vector Layer, and pick the county zip file.
2. Add the summary as a table: Layer → Add Layer → Add Delimited Text Layer, choose county_summary.csv, set Geometry definition to No geometry, and under field options make sure geoid is read as text.
3. Join: open the county layer’s Properties → Joins → +. Join layer: the summary. Join field: geoid. Target field: GEOID.
4. Check the join: open the attribute table and sort by the joined records column. Empty values are counties outside your export, or a key mismatch to investigate.
5. Colour: Properties → Symbology → Graduated. Value: absentee_pct. Mode: Quantile or Natural Breaks, five classes. Apply.
6. Filter to your state: in the layer’s Source settings, a provider filter such as "STATEFP" = '48' hides everything else.

A join in QGIS is virtual: it lives in the project, not in the shapefile. To keep it, right-click the layer → Export → Save Features As, and write a GeoPackage.

Choosing classes

• Quantile puts the same number of areas in each class. Good for ranking, but it can exaggerate small differences.
• Natural Breaks (Jenks) follows gaps in the data. Good for showing real clusters.
• Fixed intervals you choose (for example 0–20%, 20–40%) are best when you will compare maps across states or months, because the colours mean the same thing every time.

Step 3b: Build a GeoJSON for kepler.gl

kepler.gl runs in the browser and takes GeoJSON or CSV by drag and drop. The easiest route is to do the join in Python with geopandas and hand kepler.gl a single file that already carries both shapes and numbers:

import geopandas as gpd
import pandas as pd

# Census cartographic boundary file for counties (download from census.gov).
# Real file names include the vintage year: cb_<year>_us_county_500k.zip
counties = gpd.read_file("cb_us_county_500k.zip")          # has GEOID, NAME, STATEFP
summary = pd.read_csv("county_summary.csv", dtype={"geoid": str})

# County names the Census lookup did not match have an empty geoid.
unnamed = summary[summary["geoid"].isna()]
print(len(unnamed), "county names without a code:", unnamed[["state", "county"]].values[:10])

gdf = counties.merge(summary, left_on="GEOID", right_on="geoid", how="inner")

# Match check: every county code in the summary should have found a shape.
missing = set(summary["geoid"].dropna()) - set(gdf["GEOID"])
print(len(gdf), "counties joined;", len(missing), "without a boundary:", sorted(missing)[:10])

# Smaller, web-friendly output for kepler.gl or a browser map.
gdf["geometry"] = gdf.geometry.simplify(0.005, preserve_topology=True)
gdf.to_crs(4326).to_file("county_summary.geojson", driver="GeoJSON")

Simplifying the geometry keeps the file small enough for a browser; raise the tolerance if it is still slow, or lower it if coastlines look rough. Then, in kepler.gl:

1. Drag county_summary.geojson onto the page. It appears as a polygon layer.
2. In the layer panel, set Fill Color to absentee_pct and choose a sequential palette and a quantize or quantile scale.
3. Under Interactions → Tooltip, add NAME, records and median_value.
4. Add a filter on the colour field to highlight only areas above a threshold.

If the data must not leave your machine, check how the kepler.gl instance you use handles uploaded files, or run it yourself: it is open source and also available as a Jupyter widget.

ZIP-level maps

ZIP maps show variation that a county map averages away. The property’s ZIP is already in its own column, Zip, so no extraction is needed. Check it first:

-- The property's ZIP has its own column, "Zip". Before aggregating, list
-- values that are not five digits (a ZIP+4 suffix, if present, is cut off).
SELECT "Zip", count(*) AS records
FROM read_csv('exports/*.csv', header = true, all_varchar = true)
WHERE NOT regexp_full_match(left(trim(coalesce("Zip", '')), 5), '\d{5}')
GROUP BY 1
ORDER BY records DESC;

Blank or malformed values there are records the ZIP map will leave out; note how many. Then aggregate the same way as for counties, using the first five characters of Zip:

COPY (
  WITH z AS (
    SELECT left(trim("Zip"), 5) AS zip5, *
    FROM read_csv('exports/*.csv', header = true, all_varchar = true)
  )
  SELECT
    zip5,
    count(*)                                         AS records,
    round(100.0 * avg(CASE WHEN "Free And Clear" = 'true'
                           THEN 1 ELSE 0 END), 1)    AS free_and_clear_pct,
    median(TRY_CAST("Estimated Value" AS DOUBLE))    AS median_value
  FROM z
  WHERE regexp_full_match(zip5, '\d{5}')
  GROUP BY 1
  HAVING count(*) >= 25               -- suppress tiny areas; see "small numbers"
) TO 'zip_summary.csv' (HEADER);

ZIP codes are not polygons

A ZIP code is a set of delivery routes, not an area. The Census Bureau approximates residential ZIPs with ZCTAs, and those are the polygons you map. Expect some differences:

• ZIPs used only for PO boxes or single large organisations have no ZCTA and drop out of the join.
• ZCTAs are drawn from census blocks, so edges do not follow ZIP routes exactly.
• ZIPs cross county and occasionally state lines, so a ZIP map and a county map do not nest.

zcta = gpd.read_file("cb_2020_us_zcta520_500k.zip")        # ZCTA5CE20 = 5-digit code
zips = pd.read_csv("zip_summary.csv", dtype={"zip5": str})

z = zcta.merge(zips, left_on="ZCTA5CE20", right_on="zip5", how="inner")
unmatched = zips[~zips["zip5"].isin(zcta["ZCTA5CE20"])]
print(f"{len(z)} ZIPs mapped, {len(unmatched)} without a ZCTA "
      f"({unmatched['records'].sum()} records)")
z.to_crs(4326).to_file("zip_summary.geojson", driver="GeoJSON")

The script prints how many records fall in ZIPs without a ZCTA. If that share is small, note it in the map caption and move on. If it is large, map by county instead.

Small numbers

Rates are unstable when the denominator is small: a ZIP with four records and three absentee owners shows as 75%. The HAVING count(*) >= 25 line above suppresses those areas. The threshold is a choice; whatever you pick, state it in the legend.

Mapping owners instead of properties

The mailing columns describe where owners receive their tax bills. Aggregating by Owner Mailing State or Owner Mailing Zip maps where the owners of a state’s properties are based, which is useful for investor research and for planning outreach to out-of-state landlords:

-- Where do absentee owners of this state's properties live?
-- Aggregated by the owner's mailing state: a table, or a map of states.
-- "Owner Occupied" = false stands in for absentee (an approximation).
SELECT upper("Owner Mailing State") AS owner_state, count(*) AS properties
FROM read_csv('exports/*.csv', header = true, all_varchar = true)
WHERE "Owner Occupied" = 'false'
GROUP BY 1
ORDER BY properties DESC;

Join the result to a state boundary file by two-letter code (or to ZCTAs for Owner Mailing Zip) and colour it like any other choropleth. Keep property maps and owner maps clearly labelled; they answer different questions and are easy to confuse.

Points: geocoding addresses

To place individual properties on a map, each address needs coordinates, and the export does not include them. Geocoding converts an address into a latitude and longitude:

• U.S. Census Geocoder: free, with a batch upload for address files. Results include a match flag and coordinates. Batches have a size limit, so split large files.
• Commercial geocoders: usually higher match rates and rooftop-level precision, priced per request. Read the terms: some restrict storing results or using them outside their own maps.

import csv

# Write the addresses to geocode in the shape most batch geocoders accept:
# a unique id, then street, city, state, ZIP. Keep the id so results join
# back to the export on Property ID.
with open("records.csv", newline="") as src, open("to_geocode.csv", "w", newline="") as out:
    w = csv.writer(out)
    for row in csv.DictReader(src):
        w.writerow([row["Property ID"], row["Address"], row["City"], row["State"], row["Zip"]])

Use Property ID as the id so results join straight back to the export. Check the match rate and the match type before mapping; an address matched only to a ZIP centroid will pile up on one point. Geocode the subset you need, such as one county or one filtered list, rather than a whole state.

With coordinates, kepler.gl’s point, hexbin and heatmap layers become available, and hexbins in particular give a clear density picture without implying false precision about any single property.

Checklist

To see how large each state’s file is before you start, the data by state page lists every state’s record total, and the whole-state export guide covers getting a full state onto your machine.