Location Intelligence Retail Site Selection Automation

Exporting Scored Trade Areas to XLSX for Stakeholders

This page solves one exact task: writing a scored, ranked GeoDataFrame into a multi-sheet XLSX workbook — a ranked shortlist, a per-criterion score matrix, and a demographics sheet — that finance and asset-management stakeholders can sort, pivot, and drop into a model without ever touching a GIS tool.

Excel is where a site-selection recommendation meets the people who control the budget. The workbook is the format at the end of the automated reporting and GIS export stage that a lease committee will actually open, re-sort, and paste into a discounted-cash-flow model. The task is narrow but has real traps: geometry must be handled deliberately (usually dropped), numeric dtypes must survive the write so downstream formulas work, and conditional formatting has to make the ranking legible at a glance.

Prerequisites

Before running this task you need:

  • Python packages: geopandas and pandas for the scored frame, and openpyxl as the Excel engine that supports multiple sheets and conditional formatting. Install with pip install geopandas pandas openpyxl.
  • A scored, ranked shortlist. A GeoDataFrame with one row per candidate site carrying a suitability_score, per-criterion weighted sub-scores, demographic attributes, and a geometry with a CRS. This is the output of building weighted site suitability scores, with demographic fields sourced via weighting demographic variables for target audiences.
  • The parent context. This page implements the write_xlsx writer described in Automated Reporting & GIS Export; read that first for the assembler and the CRS policy.

Configuration and execution parameters

The writer is parameterized so the workbook is reproducible and its geometry policy explicit. Everything that affects what a stakeholder sees is pinned here.

Parameter Value for this task Notes
engine openpyxl Supports multi-sheet writes and conditional formatting rules.
geometry_policy drop Drop geometry by default; alternative is a single WKT column.
wkt_crs EPSG:4326 If WKT is kept, encode it in WGS84 for portability.
area_crs EPSG:5070 Equal-area CRS for the trade-area size column.
sheets shortlist, scores, demographics One concern per sheet; no mega-sheet.
freeze_header A2 Freeze the header row so long lists stay readable.
heat_scale 3-color Conditional format on suitability_score for at-a-glance ranking.

The one decision that matters most is the geometry policy. A GeoDataFrame cannot be written to Excel with its geometry column intact — openpyxl has no cell type for a polygon. Dropping geometry is the default because a finance sheet does not use it; when a downstream tool genuinely needs the shape, encode it as a single Well-Known Text (WKT) column in WGS84 rather than exploding coordinates across cells.

Why three sheets, not one

A single mega-sheet forces a stakeholder to scroll horizontally across forty columns to find the one number they care about, and it makes pivot tables awkward because unrelated concerns share a header row. Splitting the workbook by concern keeps each sheet legible and independently pivotable:

  • Shortlist is the executive view — rank, site, headline score, and trade-area size. This is the sheet the committee opens first and the one they re-sort.
  • Scores exposes the per-criterion weighted contributions so an analyst can see why a site ranked where it did, mirroring the transparency of building weighted site suitability scores. When a weight is questioned, this sheet is the evidence.
  • Demographics carries the population, income, age, and household attributes behind each trade area, so the demographic argument stands on its own.

The site_id column appears on every sheet as the join key, so a stakeholder who builds their own model can VLOOKUP or pivot across all three without ambiguity. The conditional-format heat scale lives only on the Shortlist’s suitability_score column: a three-color red-yellow-green scale turns the ranking into something readable at a glance, where the eye finds the strongest candidates before reading a single number. Applying it to more than the one score column would add noise, not signal.

Workbook shape: one concern per sheet, one join key across all three The shortlist sheet carries rank, site, score and trade-area size; the scores sheet carries each weighted criterion contribution; the demographics sheet carries population, income and household attributes. The site identifier is the first column of every sheet, so a stakeholder can pivot or look up across all three without ambiguity. One concern per sheet — and one key that stitches them together Shortlist what the committee opens site_id rank suitability_score trade_area_km2 market heat scale on the score only Scores why it ranked there site_id reach_pop_w demo_fit_w competition_w rent_w · cotenancy_w columns sum to the score Demographics the standalone argument site_id population median_hh_income households · median_age acs_vintage margins of error kept beside site_id — the join key first column on every sheet, never renamed A stakeholder who builds their own model pivots across all three; a mega-sheet would force them to scroll.

Annotated implementation

The writer below splits the scored frame into three sheets, converts geometry per the policy, writes numeric columns with dtypes preserved, then reopens the workbook with openpyxl to apply conditional formatting and freeze panes. It never mutates the source frame.

python
from __future__ import annotations

from pathlib import Path

import geopandas as gpd
import pandas as pd
from openpyxl import load_workbook
from openpyxl.formatting.rule import ColorScaleRule
from openpyxl.utils import get_column_letter
from pyproj import CRS

AREA_CRS = CRS.from_epsg(5070)        # equal-area, for the km2 column
WKT_CRS = CRS.from_epsg(4326)         # if geometry is retained, ship WGS84 WKT


def _to_dataframe(sites: gpd.GeoDataFrame, policy: str = "drop") -> pd.DataFrame:
    """Convert a GeoDataFrame to a plain DataFrame under the geometry policy."""
    assert sites.crs is not None, "scored sites have no CRS; refusing to export"
    df = pd.DataFrame(sites.drop(columns=sites.geometry.name))
    if policy == "wkt":
        # Encode geometry as WGS84 WKT text in a single column.
        wkt = sites.geometry.to_crs(WKT_CRS).to_wkt()
        df["geometry_wkt"] = wkt.values
    return df


def write_xlsx(sites: gpd.GeoDataFrame, cfg) -> Path:
    """Write a multi-sheet stakeholder workbook; return the file path."""
    assert sites.crs is not None, "scored sites have no CRS; refusing to export"
    # Trade-area size MUST be measured in an equal-area CRS, not in degrees.
    area_km2 = sites.to_crs(AREA_CRS).geometry.area / 1_000_000.0

    policy = "wkt" if getattr(cfg, "include_geometry_xlsx", False) else "drop"
    flat = _to_dataframe(sites, policy=policy)
    flat = flat.assign(trade_area_km2=area_km2.values)

    shortlist = flat[["rank", "site_id", "site_name", "suitability_score",
                      "trade_area_km2"]].copy()
    scores = flat[["site_id", "site_name", "w_access", "w_demo",
                   "w_comp", "suitability_score"]].copy()
    demographics = flat[["site_id", "site_name", "population",
                         "median_income", "median_age", "households"]].copy()

    out_path = cfg.out_dir / f"site_shortlist_{cfg.run_id}.xlsx"
    out_path.parent.mkdir(parents=True, exist_ok=True)

    with pd.ExcelWriter(out_path, engine="openpyxl") as xw:
        shortlist.to_excel(xw, sheet_name="Shortlist", index=False)
        scores.to_excel(xw, sheet_name="Scores", index=False)
        demographics.to_excel(xw, sheet_name="Demographics", index=False)

    _style_workbook(out_path, n_rows=len(flat))
    return out_path


def _style_workbook(path: Path, n_rows: int) -> None:
    """Freeze headers, size columns, and heat-scale the score column."""
    wb = load_workbook(path)
    for ws in wb.worksheets:
        ws.freeze_panes = "A2"                         # keep header visible
        for col_idx, col_cells in enumerate(ws.columns, start=1):
            width = max(len(str(c.value)) for c in col_cells if c.value is not None)
            ws.column_dimensions[get_column_letter(col_idx)].width = width + 2

    ws = wb["Shortlist"]
    header = {cell.value: cell.column_letter for cell in ws[1]}
    score_col = header["suitability_score"]        # locate column by header name
    rng = f"{score_col}2:{score_col}{n_rows + 1}"
    ws.conditional_formatting.add(rng, ColorScaleRule(
        start_type="min", start_color="F8696B",       # low score = red
        mid_type="percentile", mid_value=50, mid_color="FFEB84",
        end_type="max", end_color="63BE7B"))           # high score = green
    wb.save(path)

Two-phase writing is deliberate: pandas writes the data and sheet structure, then openpyxl reopens the file to apply presentation rules that pandas cannot express. The geometry is either dropped or collapsed into one WKT column before the frame ever reaches to_excel — there is no code path that hands a shapely object to the Excel engine. The trade-area size is computed once, in the equal-area CRS, and joined back by position.

Choosing a geometry policy for the workbook Starting from the question of whether the recipient will map the data, the flow ends in three outcomes: drop geometry entirely for a finance sheet, keep a single Well-Known Text column in WGS84 when a downstream tool re-reads shapes from the workbook, or ship a GeoPackage alongside when the recipient genuinely works in a GIS. Geometry in a spreadsheet is a decision, not a default Scored GeoDataFrame Who re-reads the geometry from this file? nobody Drop geometry the default for finance keep area_km2 as a number computed in EPSG:5070 an analyst in a GIS Ship a GeoPackage alongside the workbook joined on site_id CRS travels with the file only a script re-reads it One WKT column EPSG:4326, last column never split across cells watch the 32,767-char cap A long trade-area boundary silently exceeds Excel's cell limit — simplify before encoding, or the value truncates. Whichever branch you take, record it in the workbook's metadata sheet so the recipient never has to guess.

Failure modes and debugging

Symptom Cause Fix
TypeError: cannot serialize geometry on write Geometry column reached to_excel Drop geometry or convert to a WKT string column first.
Numbers stored as text; formulas return #VALUE! A column was cast to object/str upstream Preserve numeric dtypes; check df.dtypes before writing.
Wrong trade-area size Area computed in EPSG:4326 degrees Compute area in an equal-area CRS (EPSG:5070).
Conditional formatting on the wrong range Off-by-one from the header row Range starts at row 2; end at n_rows + 1.
Garbled accented store names Non-UTF-8 handling openpyxl writes UTF-8; ensure upstream reads are UTF-8 too.
Scientific notation on ZIP/FIPS codes Numeric dtype on an identifier column Keep GEOIDs and ZIPs as strings so leading zeros survive.

The dtype trap is the one that silently reaches the stakeholder. If population or median_income arrives as an object dtype — common when a column carried a sentinel string upstream — Excel stores it as text, and the committee’s =SUM() returns nothing. Assert numeric dtypes before the write.

What Excel does to each column type on the way in Float and integer columns arrive as numbers and stay usable in formulas. Nullable integers and object columns arrive as text unless cast first, which silently breaks sorting and arithmetic. Dates arrive as serial numbers needing a format, and a leading-zero identifier loses its zeros unless written as text on purpose. Dtypes decide whether the committee can sort your sheet column in the frame what lands in the cell what breaks if you ignore it float64 score number nothing — round for display only int64 rank number nothing Int64 with nulls text, or a stray NA string sorts alphabetically; SUM returns 0 object mixed types text pivot tables count instead of summing datetime64 refreshed_at serial number reads as 46,000 with no date format site_id like 00427 427, zeros gone joins to the other sheets break Cast deliberately before the write: numbers as numbers, identifiers as text, dates with an explicit number format. Every row here has been a real support ticket; none of them raises an exception at write time.

Verification

Confirm the workbook’s structure and typing before shipping it:

  1. Sheet names: the workbook contains exactly Shortlist, Scores, and Demographics.
  2. Row counts: each sheet has one data row per shortlisted site (plus the header).
  3. Dtype checks: suitability_score, population, and median_income round-trip as numeric, not text.
python
import pandas as pd
from openpyxl import load_workbook

def verify_workbook(path, expected_rows: int) -> None:
    wb = load_workbook(path, read_only=True)
    assert set(wb.sheetnames) == {"Shortlist", "Scores", "Demographics"}, \
        f"unexpected sheets: {wb.sheetnames}"

    sheets = pd.read_excel(path, sheet_name=None, engine="openpyxl")
    for name, df in sheets.items():
        assert len(df) == expected_rows, f"{name}: {len(df)} rows, expected {expected_rows}"

    numeric = sheets["Shortlist"]["suitability_score"]
    assert pd.api.types.is_numeric_dtype(numeric), "suitability_score is not numeric"
    demo = sheets["Demographics"]
    for col in ("population", "median_income"):
        assert pd.api.types.is_numeric_dtype(demo[col]), f"{col} stored as text"

Reading the file back with pd.read_excel and asserting numeric dtypes is the check that matters: it proves the committee’s spreadsheet formulas will work, which a sheet-name check alone never confirms.

Frequently Asked Questions

Should the workbook contain a map?

No — a spreadsheet is the wrong medium for a map, and an embedded image is one more thing to go stale when the ranking is refreshed. Keep the workbook numeric and let the PDF committee pack carry the cartography, since both are generated from the same scored run and therefore agree by construction. If a recipient wants to map the shortlist themselves, hand them the GeoPackage rather than pasting a screenshot into a cell.

How should the workbook handle sites with missing criteria?

Keep them out of the ranked shortlist sheet and list them on a clearly labelled exclusions block with the reason. A quarantined site that silently disappears invites the question “what happened to the one on Fourth Street?” three weeks later, and a site with a zero-filled criterion is worse: it ranks, it looks legitimate, and its score is wrong. Making the exclusion visible turns a data gap into a work item rather than a mystery.

Does the score column need rounding before it is written?

Round for display, never in the value. Write the full float and apply a number format of two or three decimals, so the cell shows a readable 0.74 while the underlying value still sorts and compares exactly. Rounding the stored number is how two candidates that differed in the fourth decimal become an unresolvable tie in the workbook while the pipeline still ranks them, and the two artifacts then disagree with nothing to explain why.

How do I keep the workbook reproducible across runs?

Treat it as a pure function of the scored table plus the template settings. Sort rows by rank and then by the site identifier so ties do not shuffle between runs, write sheets in a fixed order, pin the column order explicitly instead of inheriting frame order, and keep the run identifier and weight version on a metadata sheet rather than in a filename that gets renamed on the way to a committee. Two runs of the same data should then differ only where the data differs.

← Back to Automated Reporting & GIS Export