Merge Duplicate Properties Across Spreadsheets Without Losing Provenance

Two spreadsheets, one property, several spellings

Property lists rarely arrive from one place. An open-house signup sheet, a CRM export and a list from a partner can each mention the same home with different abbreviations, punctuation and unit formats. They can also mention two different condos in the same building with addresses that look almost identical. Deduplicating by text alone fails in both directions: it keeps duplicates that are spelled differently and merges distinct units that share a street line.

This guide merges rows only after a lookup has established which property each row describes, and it keeps the evidence for every decision. The result is a property-level table where each group still lists the spreadsheet rows, original spellings, warnings and collection times that produced it. Rows whose identity is not settled stay out of the merge and go to a review file instead.

It extends the address enrichment guide rather than repeating it. That guide shows how to send an address list to the API and save the responses. Here we start from those saved responses and decide which rows belong together.

• Enrich an address list and save the responses first

Decide what counts as the same property

Write the merge rule before you look at the data. A useful rule is narrow: two rows belong to one property only when each lookup succeeded, returned an identifier, carried no identity warning, and agreed with the unit in the original input. Everything else is a question for a person, not for the merge program.

The zpid is the returned property identifier and the natural merge key. Apartment-building links are the exception: a building page has no zpid of its own, so the documented identifier is its detailUrl, and the unit field says which unit the price describes. The program below keys those rows on the page and the unit together so two units in one building never collapse into a single group.

Treat a meta.warning as a stop sign. It appears when the source answered a link or zpid with a different property than the one requested. A row with a warning may still be useful, but it should not silently join a group.

Save one line per original row

The merge program reads JSON Lines: one line for each row of each source spreadsheet. Each line keeps the source file name, the row number, the exact input text, and either the full response body or the error code. Keeping the original row number is what lets you write decisions back to the spreadsheet your colleagues actually use.

Store the whole body, not a few copied columns. The meta object carries fetched_at, cached and any warning, and those values are what make the merged table auditable. If you trimmed them during enrichment, rerun the export for the rows you need rather than reconstructing them.

The example below is illustrative. The second line shows a row that failed identity resolution: it stays in the file with its error code, and the merge will report it as unresolved rather than dropping it.

{"source": "open-house-signups.csv", "row": 14, "input": "12 Oak St #3, Austin TX 78701",
 "body": {"data": {"zpid": "0000000", "address": "12 Oak St APT 3, Austin, TX 78701", "unit": "3",
                   "homeStatus": "FOR_SALE", "price": 525000, "zestimate": 512000, "...": "..."},
          "meta": {"fetched_at": "2026-10-19T14:02:11.000Z", "cached": false, "credits": 1, "...": "..."}},
 "error_code": null}
{"source": "crm-export.csv", "row": 203, "input": "12 Oak Street, Austin, TX 78701",
 "body": null, "error_code": "address_ambiguous"}

Run the merge program

The program uses only the Python standard library and makes no API requests, so you can rerun it as often as you like while you tune the review rules. Pass every snapshot file you want to combine. It writes merged.json with one entry per accepted property and unresolved.json with every row it refused to merge, along with the reason.

Each merged group has four parts. members lists the original rows. aliases lists the distinct input spellings, with whitespace collapsed but nothing else changed, so you can see how each source wrote the address. observations holds the returned values with their own fetched_at and cached flags, oldest first. key records which identifier joined the rows.

Notice what the program does not do. It never edits an input address, never chooses one spelling as correct, and never fills a missing unit from another row. Those would be judgment calls disguised as data cleaning.

"""merge_properties.py: group enriched spreadsheet rows by accepted property identity.

Input: one or more JSON Lines files written by your enrichment job, one line per
original spreadsheet row:
  {"source": "leads.csv", "row": 7, "input": "12 Oak St Apt 3, Austin, TX 78701",
   "body": {"data": {...}, "meta": {...}} or null, "error_code": "not_found" or null}

Output: merged.json (accepted property groups) and unresolved.json (every other row).
"""
import json
import re
import sys
from pathlib import Path

UNIT = re.compile(r"\s(?:APT|APARTMENT|UNIT|STE|SUITE|#)\.?\s*#?\s*([A-Z0-9-]+)\s*(?:,|$)", re.I)


def input_unit(text):
    match = UNIT.search(text)
    return match.group(1).upper() if match else None


def identity_key(data):
    """A merge key from the returned identity, or None when there is none."""
    if data.get("zpid"):
        return ["zpid", data["zpid"]]
    if data.get("detailUrl"):
        # Apartment-building pages have no zpid; keep their units apart.
        return ["page", data["detailUrl"], (data.get("unit") or "").upper()]
    return None


def accept(record):
    """Return (key, None) for an accepted identity or (None, reason) for review."""
    body = record.get("body")
    if record.get("error_code") or not body:
        return None, "lookup_failed"
    data, meta = body["data"], body["meta"]
    if meta.get("warning"):
        return None, "identity_warning"
    returned_unit = (data.get("unit") or "").upper() or None
    if input_unit(record["input"]) != returned_unit:
        return None, "unit_differs"
    key = identity_key(data)
    return (key, None) if key else (None, "no_identifier")


def main(paths):
    groups, unresolved = {}, []
    for path in paths:
        for line in Path(path).read_text(encoding="utf-8").splitlines():
            if not line.strip():
                continue
            record = json.loads(line)
            origin = {"source": record["source"], "row": record["row"], "input": record["input"]}
            key, reason = accept(record)
            if key is None:
                unresolved.append({**origin, "reason": reason, "error_code": record.get("error_code")})
                continue
            data, meta = record["body"]["data"], record["body"]["meta"]
            group = groups.setdefault(json.dumps(key), {"key": key, "members": [], "aliases": set(), "observations": []})
            group["members"].append(origin)
            group["aliases"].add(" ".join(record["input"].split()))
            group["observations"].append({
                "fetched_at": meta["fetched_at"],
                "cached": meta["cached"],
                "address": data["address"],
                "unit": data["unit"],
                "homeStatus": data["homeStatus"],
                "price": data["price"],
                "zestimate": data["zestimate"],
                "from": origin,
            })
    merged = []
    for group in groups.values():
        group["aliases"] = sorted(group["aliases"])
        group["observations"].sort(key=lambda obs: obs["fetched_at"])
        merged.append(group)
    Path("merged.json").write_text(json.dumps(merged, indent=2), encoding="utf-8")
    Path("unresolved.json").write_text(json.dumps(unresolved, indent=2), encoding="utf-8")
    print(f"{len(merged)} property groups, {len(unresolved)} rows left unresolved")


if __name__ == "__main__":
    main(sys.argv[1:])

Keep conflicting values side by side

Two spreadsheets enriched on different days can disagree. The listing price may have changed, the status may have moved from for sale to pending, or Zillow may have stopped showing an estimate. These are not duplicates to resolve; they are observations collected at different times. The merged group keeps each one with its collection time.

When a report needs a single value per property, choose it with an explicit rule, such as the observation with the latest fetched_at, and print that rule next to the table. Keep cached in view as well: a cache hit reuses an earlier collection, so two rows enriched a few hours apart can legitimately carry the same fetched_at.

Do not average prices across observations or combine a rent with a sale price. homeStatus tells you what price means for each observation, and mixing them produces a number that describes nothing.

• Understand collection time and cache reuse

Work the unresolved file deliberately

Sort unresolved.json by reason. lookup_failed rows need a corrected input or a later retry, depending on the error code. address_ambiguous rows usually need a unit, ZIP or city added; the API error details include the closest candidate's Zillow link, which a reviewer can confirm and then look up directly by url. unit_differs and identity_warning rows need a person to compare the input with the returned address.

Record each decision in a separate review column or file with the reviewer and date. Do not overwrite the original input. When a reviewer confirms a property, add a new lookup line for that row and rerun the merge. The program will then place the row in the right group, and the audit trail still shows that a person made the call.

Some rows will never resolve: a vacant lot without a page, a typo nobody can interpret, a property outside the United States. Leave them visible. An unresolved count in the summary is more honest than a clean-looking table that quietly lost them.

• lookup_failed: correct the input or retry according to the error code.
• address_ambiguous: add the missing detail or confirm the candidate link.
• unit_differs: compare the unit in the input with the returned unit.
• identity_warning: confirm which property the response describes.
• no_identifier: inspect the returned page before using the row.

• Resolve ambiguous property addresses
• How the zpid identifies a property

Test the merge before trusting it

Build a small fixture file by hand before running real spreadsheets through the program. Include two spellings of one house, two units in one building, a row with a warning, an ambiguous address, a failed lookup and an apartment-building page. Check that each lands where the rule says it should.

Validation scope: the program above was exercised against hand-written fixtures covering those cases. Its field names come from the published response contract. It has not been run against a live sample, so it makes no claim about how often real addresses resolve. Measure that on your own data before you depend on the result.

If you send the original rows through the batch endpoint, each result carries the index of its input, which makes it straightforward to write the source and row columns into the snapshot. Check the per-item ok flag: one failed item does not fail the batch.

• Batch inputs, results and credits