Before this: Pivot without inventing zeros
The idea
Survey and spreadsheet exports are often wide: one column per question or month. Analysis tools prefer long data, with one row per observation. Unpivoting turns q1, q2 and q3 into rows of (question, score).
An unanswered question is not an observation. Producing a row with score 0 would treat "skipped" as the lowest possible answer and pull every average down; producing a row with a missing score adds noise that every later query must filter.
Convert the values to one type before unpivoting: the new score column can hold only one type, and parsing first means bad values fail early with a clear message.
Worked example
Input: r101: q1 = 4, q2 = missing, q3 = 2
- Visual
Convert q1–q3 to int64 → Unpivot (keep respondent; columns q1, q2, q3 → question, score) → Filter rows where score is present
- SQL (DuckDB)
SELECT respondent, question, CAST(answer AS BIGINT) AS score FROM (UNPIVOT survey ON q1, q2, q3 INTO NAME question VALUE answer)
- pandas
long = survey.melt(id_vars=["respondent"], var_name="question", value_name="score") long = long[long["score"].notna()]
Result: Two rows: (r101, q1, 4) and (r101, q3, 2). No row for q2.
Common mistakes
- Scoring unanswered questions as 0.
- Keeping rows with a missing score.
- Unpivoting columns of different types.
Check yourself
Does DuckDB's UNPIVOT keep NULL values?
Not by default; it drops them unless you ask for INCLUDE NULLS.
Why not fill missing answers with 0 before reshaping?
0 would become a real score and distort averages; a skipped question has no score.
Practice it
Reshape the survey to one row per answer and leave unanswered questions out.