TRY_CAST the Junk Away — Solution & ExplanationProblems / TRY_CAST the Junk Away / EditorialApproach

Parse once: TRY_CAST(value AS DOUBLE) converts clean strings and maps every junk value ('N/A', 'error', '', 'twelve') to NULL instead of raising an error.

Exploit COUNT semantics: COUNT(*) counts rows, COUNT(expr) counts non-NULL results. So COUNT(TRY_CAST(...)) is valid_count and COUNT(*) - COUNT(TRY_CAST(...)) is junk_count.

AVG(TRY_CAST(...)) skips the NULLs, averaging only valid readings; round to 2 decimals. Sensor 3 is all junk, so its AVG is NULL and its valid_count is 0 — exactly what the report should show.

Group by sensor_id, order by sensor_id.

Key InsightThe difference between CAST and TRY_CAST is the difference between a pipeline that crashes on the first bad row and one that quantifies its dirty data. Because failed casts become NULLs, the whole valid/junk/average report falls out of standard aggregate semantics: COUNT(col) vs COUNT(*) and NULL-ignoring AVG. The classic mistake is treating junk as zero — that silently drags the average down; NULL is the honest answer.Hints

TRY_CAST(value AS DOUBLE) is the safe version of CAST: instead of aborting the query on 'N/A' or 'twelve', it produces NULL for that row.

COUNT(expr) counts only non-NULL values — so COUNT(TRY_CAST(...)) is exactly the valid rows, and COUNT(*) - COUNT(TRY_CAST(...)) is the junk rows.

AVG ignores NULLs automatically, so junk rows never pollute the average. For the all-junk sensor, AVG over zero values returns NULL — do not COALESCE it to 0.

In this PySpark environment use F.expr("TRY_CAST(value AS DOUBLE)") to build the parsed column; then F.count on it gives valid_count and F.avg the average.

Solve TRY_CAST the Junk Away yourself →