Finding Portfolio Owners in a Bulk Property File

A statewide property file lists properties, not owners. An investor with forty rentals appears forty times, once per parcel, usually with the same name and mailing address on every row, but not always spelled the same way. Finding those owners means collapsing rows into people and companies: grouping by a normalised owner name and mailing address, then dealing with the cases where one investor hides behind several LLCs or a trust.

This guide builds that grouping step by step, in SQL and in pandas, using the owner columns from a Bulk Property Records export. It assumes the file is already somewhere you can query; if not, start with loading it into PostgreSQL or querying it with DuckDB.

The columns that identify an owner

The field dictionary describes each of these in detail, including why the mailing address, not the property address, is the one that identifies an owner.

The shortcut: Owner Property Count

Every row already carries Owner Property Count, the number of properties the owner holds. For a quick first cut, filtering to rows where it is 2 or more, or sorting by it, surfaces likely portfolio owners without any grouping at all.

It is a shortcut, not a replacement. The count comes from the data provider’s own owner matching, which you cannot inspect or tune; it does not tell you which rows in your file belong to the same owner; and it may include properties outside the area you exported. The grouping below gives you the actual groups, rules you can audit, and totals for value and equity. Keeping Owner Property Count alongside your own count is a useful cross-check: large gaps between the two point to names or addresses your normalisation missed.

Why a plain GROUP BY on the owner name is not enough

Owner names come from county records, and counties do not share a style guide. The same company can appear as any of these:

Grouping on the raw name splits one owner into several groups. Grouping on name alone has the opposite problem: in a whole state, two unrelated people can share a common name. Pairing the name with the mailing address avoids most false merges, and normalising both avoids most false splits.

Step 1: Normalise names and mailing addresses

The rules are deliberately simple: upper case, punctuation to spaces, a short list of entity and street spellings collapsed to one form, whitespace squeezed. Each rule should be one you would be comfortable explaining; aggressive fuzzy matching at this stage creates merges you cannot easily audit.

In PostgreSQL

-- Owner names: upper case, punctuation to spaces, common entity
-- spellings collapsed to one form, whitespace squeezed.
CREATE OR REPLACE FUNCTION norm_owner(n text) RETURNS text
LANGUAGE sql IMMUTABLE AS $$
  SELECT nullif(btrim(regexp_replace(
    regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(
      ' ' || upper(regexp_replace(coalesce(n, ''), '[^A-Za-z0-9& ]', ' ', 'g')) || ' ',
      ' L L C | LIMITED LIABILITY (COMPANY|CO) ', ' LLC ', 'g'),
      ' INCORPORATED ', ' INC ', 'g'),
      ' CORPORATION ', ' CORP ', 'g'),
      ' LIMITED PARTNERSHIP | L P ', ' LP ', 'g'),
      ' (TRUSTEES?|TRS|TR|TRUS) ', ' TRUST ', 'g'),
    '\s+', ' ', 'g')), '')
$$;

-- Mailing street: upper case, punctuation removed, the most common
-- suffixes and unit words abbreviated.
CREATE OR REPLACE FUNCTION norm_street(s text) RETURNS text
LANGUAGE sql IMMUTABLE AS $$
  SELECT nullif(btrim(regexp_replace(
    regexp_replace(regexp_replace(regexp_replace(regexp_replace(regexp_replace(
      ' ' || upper(regexp_replace(coalesce(s, ''), '[^A-Za-z0-9 ]', ' ', 'g')) || ' ',
      ' STREET ', ' ST ', 'g'),
      ' AVENUE ', ' AVE ', 'g'),
      ' (SUITE|STE|UNIT|APT) ', ' UNIT ', 'g'),
      ' P O BOX | POST OFFICE BOX | POB ', ' PO BOX ', 'g'),
      ' ROAD ', ' RD ', 'g'),
    '\s+', ' ', 'g')), '')
$$;

Padding the value with a space on each side lets every pattern match whole words with a plain space, so TR becomes TRUST but TRAIL is left alone. Marking the functions IMMUTABLE lets you index them later.

In pandas

import re
import pandas as pd

# Real export headers -> short names. Read everything as text so APN,
# Property ID and Owner Mailing Zip keep their leading zeros.
COLS = {
    "Property ID": "property_id", "APN": "apn", "State": "state", "County": "county",
    "Address": "address",
    "Owner 1 First Name": "owner1_first", "Owner 1 Last Name": "owner1_last",
    "Owner 2 First Name": "owner2_first", "Owner 2 Last Name": "owner2_last",
    "Owner Mailing Address": "mail_street", "Owner Mailing City": "mail_city",
    "Owner Mailing State": "mail_state", "Owner Mailing Zip": "mail_zip",
    "Owner Occupied": "owner_occupied", "Corporate Owned": "corporate",
    "Free And Clear": "free_and_clear", "Estimated Value": "est_value",
    "Estimated Equity": "equity", "Owner Property Count": "owner_property_count",
}
df = pd.read_csv("state.csv", usecols=list(COLS), dtype=str).rename(columns=COLS)

# One full owner name from the two name columns.
df["owner_name"] = (df["owner1_first"].fillna("") + " " + df["owner1_last"].fillna("")).str.strip()

ENTITY = [
    (r"\bL\s*L\s*C\b|\bLIMITED LIABILITY (COMPANY|CO)\b", "LLC"),
    (r"\bINCORPORATED\b", "INC"),
    (r"\bCORPORATION\b", "CORP"),
    (r"\bLIMITED PARTNERSHIP\b|\bL\s*P\b", "LP"),
    (r"\b(TRUSTEES?|TRS|TR|TRUS)\b", "TRUST"),
]
STREET = [
    (r"\bSTREET\b", "ST"), (r"\bAVENUE\b", "AVE"), (r"\bROAD\b", "RD"),
    (r"\b(SUITE|STE|APT|UNIT)\b|#", " UNIT "),
    (r"\bP\s*O\s*BOX\b|\bPOST OFFICE BOX\b|\bPOB\b", "PO BOX"),
]

def normalise(s: pd.Series, rules) -> pd.Series:
    s = s.fillna("").str.upper().str.replace(r"[^A-Z0-9& #]", " ", regex=True)
    for pattern, repl in rules:
        s = s.str.replace(pattern, repl, regex=True)
    return s.str.replace(r"\s+", " ", regex=True).str.strip().replace("", pd.NA)

df["owner_key"] = normalise(df["owner_name"], ENTITY)
df["street_key"] = normalise(df["mail_street"], STREET)
df["zip5"] = df["mail_zip"].str[:5]

Note that every column is read as a string, including APN, Property ID and Owner Mailing Zip, and the rename to short names happens once, straight after loading. A ZIP that loses its leading zero no longer matches the same address on another row.

Step 2: Group by name and mailing address

With both keys normalised, a portfolio is a group of two or more properties that share an owner key, a street key and a five-digit ZIP. Aggregating value, equity and flags at the same time turns the result into a usable list of owners.

-- "properties" holds the export with its own headers as column names,
-- typed on load (flags boolean, values numeric). Rename to suit your table.
WITH keyed AS (
  SELECT
    norm_owner(concat_ws(' ', "Owner 1 First Name", "Owner 1 Last Name")) AS owner_key,
    norm_street("Owner Mailing Address") AS street_key,
    left("Owner Mailing Zip", 5)         AS zip5,
    "State" || '|' || "County"           AS county_key,
    "Property ID", "Estimated Value", "Estimated Equity",
    "Free And Clear", "Corporate Owned", "Owner Property Count"
  FROM properties
  -- No absentee column: "not owner occupied" is the closest approximation,
  -- and portfolio owners are almost always absentee.
  WHERE "Owner Occupied" = false
)
SELECT
  owner_key,
  street_key,
  zip5,
  count(*)                                AS properties,
  count(DISTINCT county_key)              AS counties,
  sum("Estimated Value")                  AS total_value,
  sum("Estimated Equity")                 AS total_equity,
  round(avg("Free And Clear"::int) * 100) AS pct_free_and_clear,
  bool_or("Corporate Owned")              AS any_corporate,
  max("Owner Property Count")             AS source_property_count
FROM keyed
WHERE owner_key IS NOT NULL AND street_key IS NOT NULL
GROUP BY owner_key, street_key, zip5
HAVING count(*) >= 2
ORDER BY properties DESC;

The SQL quotes the export’s headers, which contain spaces. If you renamed columns when loading the file, substitute your names.

The same grouping in pandas:

# Flags are the text "true" / "false"; blanks stay missing.
for c in ["owner_occupied", "corporate", "free_and_clear"]:
    df[c] = df[c].map({"true": True, "false": False})
for c in ["est_value", "equity", "owner_property_count"]:
    df[c] = pd.to_numeric(df[c], errors="coerce")
df["county_key"] = df["state"] + "|" + df["county"]

owners = (
    df[df["owner_occupied"] == False]          # approximates absentee
    .dropna(subset=["owner_key", "street_key"])
    .groupby(["owner_key", "street_key", "zip5"], dropna=False)
    .agg(properties=("property_id", "size"),
         counties=("county_key", "nunique"),
         total_value=("est_value", "sum"),
         total_equity=("equity", "sum"),
         any_corporate=("corporate", "max"),
         source_property_count=("owner_property_count", "max"))
    .reset_index()
)
portfolios = owners[owners["properties"] >= 2].sort_values("properties", ascending=False)
print(portfolios.head(25).to_string(index=False))

Filtering to rows where Owner Occupied is false first is a judgment call, and only an approximation of absentee ownership. It removes the owner’s own home from their portfolio count, which is usually what you want for investor research. Drop the filter if you need a complete count of everything an owner holds.

Read the top of the list first

Sort by property count and look at the first few dozen groups before doing anything else. The top of the list shows the normalisation problems fastest: a bank or government agency holding many parcels, a name that is actually a placeholder, or two companies merged because they share a suite in the same office building.

Step 3: Find mailing-address hubs

Some addresses receive tax bills for many unrelated owners: property management companies, law and accounting firms, and registered agents who serve as the address of record for thousands of LLCs. Grouping by address alone would merge all of those owners. Find these hubs explicitly so you can treat them separately:

-- Mailing addresses shared by many differently named owners.
-- These are often property managers, law firms or registered agents.
WITH k AS (
  SELECT norm_street("Owner Mailing Address") AS street_key,
         left("Owner Mailing Zip", 5)         AS zip5,
         norm_owner(concat_ws(' ', "Owner 1 First Name", "Owner 1 Last Name")) AS owner_key
  FROM properties
)
SELECT street_key,
       zip5,
       count(*)                  AS properties,
       count(DISTINCT owner_key) AS distinct_owners
FROM k
GROUP BY 1, 2
HAVING count(DISTINCT owner_key) >= 10
ORDER BY distinct_owners DESC
LIMIT 50;

The threshold of ten distinct owners is a starting point, not a rule. Look at what the query returns in your state and adjust it. Hubs are interesting in their own right, too: a property manager receiving tax bills for hundreds of parcels is a business you might want to reach directly.

Step 4: Link LLCs that belong to the same investor

Many investors hold each property, or each small group of properties, in its own LLC. The names differ (OAK RIDGE HOLDINGS LLC, OAK RIDGE HOLDINGS II LLC, 4417 ELM STREET LLC), but the tax bills often go to the same office. Name-and-address grouping keeps these apart. To bring them together, link owners transitively: two owner names belong to one group if they share a mailing address, and two addresses belong to one group if they share an owner name.

That is a connected-components problem, and a union-find structure solves it in a single pass. The important detail is to skip the hub addresses from step 3; otherwise every client of one registered agent collapses into one giant group.

# Link owners that share either a normalised name or a mailing address,
# so "OAK RIDGE HOLDINGS LLC" and "OAK RIDGE HOLDINGS II LLC" at the same
# office end up in one group, but skip addresses that look like hubs.
hub = (df.groupby(["street_key", "zip5"])["owner_key"].nunique() >= 10)
hub_addrs = set(hub[hub].index)

parent: dict[str, str] = {}

def find(x: str) -> str:
    parent.setdefault(x, x)
    while parent[x] != x:
        parent[x] = parent[parent[x]]
        x = parent[x]
    return x

def union(a: str, b: str) -> None:
    ra, rb = find(a), find(b)
    if ra != rb:
        parent[rb] = ra

pairs = df.dropna(subset=["owner_key", "street_key"])[["owner_key", "street_key", "zip5"]].drop_duplicates()
for owner, street, z in pairs.itertuples(index=False):
    if (street, z) in hub_addrs:
        find("N:" + owner)          # keep the name, but don't link through the hub
        continue
    union("N:" + owner, f"A:{street}|{z}")

df["group_id"] = [find("N:" + o) if isinstance(o, str) else pd.NA for o in df["owner_key"]]
groups = df.groupby("group_id").agg(
    properties=("property_id", "size"),
    names=("owner_key", lambda s: sorted(set(s.dropna()))[:5]),
)
print(groups.sort_values("properties", ascending=False).head(20))

Review the largest resulting groups by hand. A good group has names that plainly belong together. A bad one usually traces back to a single shared address that should have been on the hub list.

Step 5: Handle trusts and estates

Trusts show up in two very different kinds of portfolio. Most are personal estate-planning trusts: a couple moves their home and perhaps a rental or two into a revocable living trust. A smaller number are investment or land trusts used to hold many properties. Treat them differently:

• Personal trusts usually carry a person’s name (SMITH JOHN A TRUST, JOHN AND MARY SMITH REVOCABLE LIVING TRUST). Grouping them with the same person’s individually held parcels at the same mailing address gives a truer picture of what that household owns.
• Investment trusts often use a neutral name or a number. Treat them like LLCs: group by name and address, and link through shared addresses.
• Estates (ESTATE OF …, … HEIRS) are usually not portfolios at all, but they matter for outreach timing and tone. Flag them rather than grouping them.

A simple classification column makes these distinctions queryable:

CASE
  WHEN owner_key ~ '\m(LLC|INC|CORP|LP|LLP|LTD|CO|COMPANY|HOLDINGS|PROPERTIES|INVESTMENTS?)\M' THEN 'company'
  WHEN owner_key ~ '\mTRUST\M'                     THEN 'trust'
  WHEN owner_key ~ '\m(ESTATE OF|EST OF|HEIRS)\M' THEN 'estate'
  WHEN owner_key ~ '\m(CITY|COUNTY|STATE|AUTHORITY|DISTRICT|USA|UNITED STATES)\M' THEN 'government'
  WHEN "Corporate Owned" THEN 'company'
  ELSE 'individual'
END AS owner_class

Order matters in the CASE: a name like SMITH FAMILY TRUST LLC is an LLC first. The keyword lists are a reasonable start; add the forms you see in your own data. The Corporate Owned flag from the export acts as a fallback for business names that contain none of the keywords.

Step 6: Decide what counts as a portfolio

There is no standard definition. Pick tiers that match the decision you are making, and keep them in one place so every report uses the same cutoffs. One illustrative set:

Value and equity sums are often more informative than counts. Twenty mobile-home lots and twenty apartment buildings are different portfolios. Add sum("Estimated Value") and the share of free-and-clear properties to the tier logic if they matter to you.

Step 7: Check the result

1. Totals reconcile. The sum of properties across all groups, plus rows you excluded (blank names, hub-only rows), equals the row count you started with.
2. No group is implausibly large. Inspect anything that looks out of line with the rest of the list.
3. Spot-check ten random mid-sized groups. Open the rows behind each one and confirm they belong together.
4. Look at near misses. Sort owner keys alphabetically and scan neighbours; this is where you find spelling variants your rules missed.

Keeping it current

Portfolios change as owners buy and sell, and the monthly refresh picks those changes up. Store a stable group identifier (for example, the earliest owner key in the group) with each month’s result. Comparing group-level counts month over month shows who is buying and who is selling, which is often the most useful output of the whole exercise. The guide on how often property records change covers matching two monthly files.

When the goal is mail rather than research, the county mailing list guide covers when to send one piece per owner and how to dedupe for a mail house.

Use and compliance

Grouping public records by owner is ordinary business research, but the output is a list of people and their holdings. Use it for permitted business purposes such as investment analysis and B2B prospecting, follow the rules for any channel you contact owners through, and do not use the data for decisions about credit, employment, insurance or housing eligibility; it is not a consumer report.