Long to Wide: Sensor Readings — Solution & ExplanationProblems / Long to Wide: Sensor Readings / EditorialApproachEAV (entity-attribute-value) storage keeps one row per station per metric — flexible to write, awkward to read. To widen it:

GROUP BY station_id — one row per entity.

For each metric, MAX(CASE WHEN metric = 'temp' THEN value END) AS temp. The CASE nulls out every row that is not a temp reading; since each station has at most one reading per metric, MAX collapses that single value unchanged (MIN would work equally well).

A station missing a metric aggregates over nothing but NULLs for that column — the cell stays NULL, and the station still appears because grouping only requires the station to have *some* row.

In PySpark the same shape is F.max(F.when(F.col("metric") == "temp", F.col("value"))) inside .agg().Key InsightMAX-of-one-value is the standard idiom for pivoting EAV data when each pivot cell holds a single value rather than a true aggregate. The tempting alternative — self-joining readings three times, once per metric — needs LEFT JOINs to survive the station with the offline pressure sensor; conditional aggregation gets that NULL right for free.Hints

GROUP BY station_id gives one row per station; each metric column is an aggregate that only 'sees' rows of one metric.

MAX(CASE WHEN metric = 'temp' THEN value END) works because there is at most one reading per station+metric — MAX of a single value is that value, and MAX over nothing but NULLs is NULL.

Do not filter out the station with a missing metric: it must appear with NULL in that column. Conditional aggregation handles this automatically; a triple self-join with inner joins would silently drop it.

Solve Long to Wide: Sensor Readings yourself →