Before this: Unpivot without inventing scores
The idea
Unpivot keeps the identifier columns and turns each question column into (question, score) rows.
Rows for unanswered questions have a missing score; drop them so they don't count as answers.
Worked example
Input: respondent R1: q1 4, q2 (blank), q3 5
- Visual
Convert q1–q3 to int → Unpivot → Filter score present
- SQL (DuckDB)
SELECT respondent, question, score FROM (UNPIVOT survey ON q1, q2, q3 INTO NAME question VALUE score)
- pandas
df.melt(id_vars="respondent", var_name="question", value_name="score").dropna(subset=["score"])
Result: 2 rows: (R1, q1, 4), (R1, q3, 5)
Common mistakes
- Scoring blanks as 0.
- Unpivoting before converting types.
Check yourself
What happens to blank answers?
They are dropped, never scored.
Practice it
Unpivot without inventing scores.