Analyse an Attribute Table with Pandas in PyQGIS

Many questions about spatial data are not spatial at all once the spatial work is done. How many inspections per district by year? Which material has the highest failure rate among pipes older than fifty years? What is the median floor area by building use, compared with last year's survey? QGIS's attribute table and aggregate functions can answer some of these, but pandas answers all of them in a line or two — group-bys, pivots, merges, rolling windows — and then exports the result straight to a spreadsheet.

This recipe belongs to PyQGIS and the Python Data Stack. It reads an attribute table into a DataFrame without geometry, cleans types, summarises and pivots, joins external tables, writes computed columns back to the layer, and exports results.

Attributes out, results backAttributes are read from the layer without geometry into a DataFrame indexed by feature id. Pandas does the analysis: summaries, pivots and joins with spreadsheets. Summary tables go out to Excel or CSV for reports. Computed per-feature columns go back to the layer by feature id in one provider call, where they can be styled and mapped.Read once, analyse, write back by idlayerattributes onlyNoGeometryDataFrameindex = feature idgroupby · pivotmerge · rollingsummary tablesExcel · CSVnew columnsback by feature id

Prerequisites

  • QGIS 3.34 LTR or newer, or the QGIS 4 series. Pandas ships with most QGIS installers; check with import pandas; pandas.__version__ in the Python console.
  • For Excel output, openpyxl installed into QGIS's Python.

Read attributes into a DataFrame

Geometry is often the largest part of a feature. Skipping it, and indexing rows by feature id, gives a lean frame that can be joined back to the layer later.

import pandas as pd
from qgis.core import QgsProject, QgsFeatureRequest

pipes = QgsProject.instance().mapLayersByName("water_pipes")[0]

def attributes_frame(layer, fields=None, expression=None):
    names = fields or layer.fields().names()
    req = QgsFeatureRequest().setFlags(QgsFeatureRequest.NoGeometry)
    req.setSubsetOfAttributes(names, layer.fields())
    if expression:
        req.setFilterExpression(expression)
    ids, rows = [], []
    for f in layer.getFeatures(req):
        ids.append(f.id())
        rows.append([f[n] for n in names])
    return pd.DataFrame(rows, columns=names, index=pd.Index(ids, name="fid"))

df = attributes_frame(pipes, ["asset_id", "material", "diameter_mm",
                              "install_year", "district", "bursts_5y", "length_m"])
print(df.shape)
print(df.head())

Breakdown: NoGeometry and an attribute subset push the work down to the provider, so on a database only the needed columns cross the network. Indexing by feature id rather than a 0-based range is the key design choice: it makes writing results back trivial and survives filtering and sorting. Reading values by name keeps the code independent of field order.

Clean types before analysing

QGIS hands pandas NULL QVariants and Qt date objects on QGIS 3. Cleaning them once, right after reading, avoids object-dtype columns that silently break numeric operations.

def clean(df):
    def py(v):
        if v is None or (hasattr(v, "isNull") and v.isNull()):
            return None
        if hasattr(v, "toPyDateTime"):
            return v.toPyDateTime()
        if hasattr(v, "toPyDate"):
            return v.toPyDate()
        return v
    out = df.apply(lambda col: col.map(py) if col.dtype == object else col)
    return out.convert_dtypes()

df = clean(df)
print(df.dtypes)
print(df.isna().sum())

Breakdown: Mapping only object columns leaves numeric columns fast. convert_dtypes chooses pandas' nullable types, so an integer column with missing values stays Int64 instead of turning into floats, and text becomes string. The missing-value count per column is worth printing every time: it tells you which analyses will quietly drop rows. The underlying NULL rules are in handling NULL values and QVariant.

Summarise with group-bys and pivots

Group-bys answer "per category" questions; pivot tables lay two categories against each other. Both are one line each.

A pivot of burst rate by material and age bandA pivot table with materials as rows, cast iron, PVC, polyethylene and ductile iron, and installation decade bands as columns. Cells hold bursts per 100 kilometres per year. Cast iron from before 1960 has the highest rate and is highlighted; modern polyethylene has the lowest. The table directs renewal planning to the worst combination.Bursts per 100 km per yearmaterial< 19601960–891990–cast iron41.218.5—ductile iron—9.74.1PVC—12.36.0polyethylene——1.8illustrative valuesrenewfirst:old castiron

df["decade_band"] = pd.cut(df["install_year"], bins=[0, 1959, 1989, 2100],
                           labels=["< 1960", "1960–89", "1990–"])
by_material = (df.groupby("material")
                 .agg(km=("length_m", lambda s: s.sum() / 1000),
                      bursts=("bursts_5y", "sum"))
                 .assign(rate=lambda t: t.bursts / t.km / 5 * 100)
                 .sort_values("rate", ascending=False))
print(by_material.round(1))

pivot = df.pivot_table(index="material", columns="decade_band",
                       values=["bursts_5y", "length_m"], aggfunc="sum", observed=False)
rate = pivot["bursts_5y"] / (pivot["length_m"] / 1000) / 5 * 100
print(rate.round(1))

Breakdown: pd.cut turns installation years into bands, the natural unit for an age analysis. Named aggregation (km=(...)) keeps output columns readable, and assign computes a rate from the aggregated totals — the correct way, since averaging per-pipe rates would weight a 2 m pipe like a 2 km one. The pivot sums bursts and lengths separately per material and band, then divides, again giving length-weighted rates. These are the tables a renewal plan is built from.

Compare periods and spot changes

Attribute tables with dates — inspections, readings, incidents — invite questions about change: which districts got worse this year, which assets have not been inspected since the last cycle. Pandas time handling makes these comparisons short and explicit.

insp = clean(attributes_frame(
    QgsProject.instance().mapLayersByName("inspections")[0],
    ["asset_id", "district", "inspected_on", "condition_score"]))
insp["inspected_on"] = pd.to_datetime(insp["inspected_on"])
insp["year"] = insp["inspected_on"].dt.year

yearly = insp.pivot_table(index="district", columns="year",
                          values="condition_score", aggfunc="mean")
change = (yearly[2026] - yearly[2025]).sort_values()
print("largest deterioration:\n", change.head(5).round(2))

latest = insp.sort_values("inspected_on").groupby("asset_id").tail(1)
overdue = latest[latest["inspected_on"] < pd.Timestamp("2024-10-01")]
print(len(overdue), "assets not inspected in the last two years")

Breakdown: Converting the date column with pd.to_datetime unlocks the .dt accessor for years, months and weekdays. A pivot of mean scores by district and year, followed by a column difference, ranks districts by change in one expression. Sorting by date and taking the last row per asset gives each asset's most recent inspection — the basis for an overdue list, which can then be written back to the layer as a flag and mapped. Comparing means is only fair when the same kinds of assets were inspected in both years; if inspection effort shifted between districts, compare like with like first.

Join external tables

Attribute analysis often needs data that is not in the layer: a cost table per material, last year's survey, a lookup of district names. Pandas merges handle it, keeping the feature-id index intact.

costs = pd.read_excel("/data/reference/renewal_costs.xlsx")    # material, eur_per_m
merged = df.reset_index().merge(costs, on="material", how="left").set_index("fid")
missing = merged["eur_per_m"].isna().sum()
if missing:
    print(f"{missing} pipes have no cost for their material:",
          merged.loc[merged.eur_per_m.isna(), "material"].unique())
merged["renewal_eur"] = merged["length_m"] * merged["eur_per_m"]
print(merged.groupby("district")["renewal_eur"].sum().sort_values().tail())

Breakdown: Resetting the index before the merge and restoring it afterwards keeps feature ids aligned — a plain merge drops the index. A left join keeps every pipe, and checking for missing costs immediately catches materials spelled differently in the two sources. For joins that should live in the QGIS project and update automatically, use a layer join instead, as in joining attributes by field value.

Write computed columns back to the layer

Per-feature results — a renewal cost, a risk score, a priority rank — are most useful on the map. Because the frame is indexed by feature id, writing back is a single dictionary comprehension.

Writing back by feature idA computed pandas Series indexed by feature id is turned into a dictionary mapping each feature id to a dictionary of field index and value. One changeAttributeValues call writes all of them. Missing values become NULL. The new field can then be styled with a graduated renderer.Series → dict → one provider callSeriesindex = fidrenewal_eurdict{fid: {idx: value}}NaN → NonechangeAttributeValuesone call

from qgis.core import QgsField
from qgis.PyQt.QtCore import QVariant

prov = pipes.dataProvider()
if pipes.fields().indexOf("renewal_eur") < 0:
    prov.addAttributes([QgsField("renewal_eur", QVariant.Double)])
    pipes.updateFields()
idx = pipes.fields().indexOf("renewal_eur")

values = merged["renewal_eur"]
changes = {int(fid): {idx: (None if pd.isna(v) else float(v))} for fid, v in values.items()}
ok = prov.changeAttributeValues(changes)
print(ok, len(changes), "features updated")

Breakdown: Casting ids with int() and values with float() converts NumPy scalar types into plain Python types the provider accepts. Missing values become None, stored as NULL. One changeAttributeValues call writes every row, which on GeoPackage is a single transaction. Add the field only if it does not exist, so the script can run repeatedly. For an undoable change in an interactive session, write through the edit buffer instead.

Export tables for reports

The summaries are usually destined for a spreadsheet or a document. Pandas writes Excel with several sheets in one call.

with pd.ExcelWriter("/data/reports/pipe_renewal_2026.xlsx") as xl:
    by_material.round(2).to_excel(xl, sheet_name="by material")
    rate.round(2).to_excel(xl, sheet_name="rate by age")
    (merged.groupby("district")["renewal_eur"].sum().round(0)
           .to_frame().to_excel(xl, sheet_name="cost by district"))

Breakdown: Each to_excel call writes one sheet; rounding before export keeps the spreadsheet readable. For CSV, to_csv with encoding="utf-8-sig" makes Excel open accented characters correctly. If the report also needs maps, a print layout with an attribute table item can show the same numbers next to the map, as in adding an attribute table to a layout.

QGIS version compatibility

The code works on QGIS 3.34 LTR, 3.40 LTR and QGIS 4 with pandas 1.5 or newer. On QGIS 4, NULLs arrive as None and the cleaning step simply passes them through; QgsField takes QMetaType.Type.Double instead of QVariant.Double. Excel export needs openpyxl.

Troubleshooting

  • Numeric columns have object dtype. NULL QVariants were not cleaned; run the cleaning step.
  • Writing back changes the wrong features. The index was lost in a merge; reset and restore it as shown.
  • TypeError on changeAttributeValues. NumPy types were passed; cast to int and float.
  • Rates look wrong. Per-feature rates were averaged; aggregate totals first, then divide.

Conclusion

Read attributes without geometry into a DataFrame indexed by feature id, clean NULLs and Qt dates once, summarise with group-bys and pivots on aggregated totals, merge external tables without losing the index, write computed columns back in one provider call, and export summaries straight to Excel.

Frequently Asked Questions

Is this faster than QGIS aggregate expressions? For one aggregate, layer.aggregate is fine. For many group-bys, pivots and merges, pandas is far quicker to write and to run.

Can I use polars instead of pandas? Yes. Build the frame from the same rows; the write-back pattern is identical.

Should I store results in the layer or a separate table? Per-feature values that drive styling belong in the layer; summaries belong in a table or report.

What about very large tables? Read in chunks with setLimit and keyset paging, or query the database directly with SQL.