Sparse to Dense Feature Matrix — Solution & ExplanationProblems / Sparse to Dense Feature Matrix / EditorialApproach

Build the dense skeleton. users CROSS JOIN features enumerates every (user, feature) combination — 6 x 4 = 24 rows. This is the one legitimate everyday use of a Cartesian product: constructing a complete grid from dimension tables.

Attach the sparse facts. LEFT JOIN feature_usage onto the grid on both user_id and feature. The left join preserves every grid row; combinations with no usage row surface with uses = NULL.

Zero-fill. COALESCE(fu.uses, 0) converts absence into an explicit 0.

Order by user_id, feature.

In pandas the same three steps are users.merge(features, how="cross"), then .merge(usage, on=["user_id", "feature"], how="left"), then fillna(0).Key InsightSparse storage encodes zeros as *absence*, and absence cannot be aggregated back into existence — any query that starts FROM feature_usage has already lost user 5 (who used nothing) and the sso feature (which nobody used). Densification therefore always runs in one direction: dimensions first, facts second — enumerate the universe with a CROSS JOIN, then LEFT JOIN the facts on the full composite key. Joining on only one of the two keys is the classic slip: the join fans out and quietly duplicates usage counts across the grid.Hints

You cannot densify from feature_usage alone — combinations that never happened simply are not there, and a user with zero usage rows contributes nothing to any GROUP BY. The full universe lives in the dimension tables.

CROSS JOIN users x features produces exactly one row per combination — the dense skeleton. With 6 users and 4 features that is 24 rows, before any usage data is touched.

LEFT JOIN feature_usage onto the skeleton on BOTH keys (user_id AND feature); joining on just one key duplicates rows. Unmatched combinations come back with NULL uses — COALESCE(uses, 0) finishes the densification.

In PySpark, use .crossJoin(features), rename feature_usage's key columns before the left join, then F.coalesce(F.col('uses'), F.lit(0)).

Solve Sparse to Dense Feature Matrix yourself →