Reshape survey data with melt and pivot

Use melt to turn question columns into rows, and pivot to reconstruct a wide table when each respondent-question pair is unique. Preserve unanswered questions until you deliberately choose an analysis population.

Reshaping changes the representation, not the evidence. A missing response should not become a zero score, and multiple answers should not be silently averaged merely to make a pivot succeed.

Define the response grain

The pandas quality lab contains three synthetic respondents and two questions: clarity and usefulness. There are six possible respondent-question cells, four observed scores and two missing answers.

import pandas as pd
from build_and_verify import ROOT

wide = pd.read_csv(ROOT / 'survey.csv',
                   dtype={'respondent_id': 'string', 'clarity': 'Int64', 'usefulness': 'Int64'})
assert wide['respondent_id'].is_unique
long = wide.melt(id_vars='respondent_id', value_vars=['clarity', 'usefulness'],
                 var_name='question', value_name='score')
assert len(long) == 6
assert long['score'].count() == 4
assert long['score'].isna().sum() == 2
assert long['score'].sum() == 14
assert not long.duplicated(['respondent_id', 'question']).any()
print(long.to_string(index=False))

The long table has one row per possible answer in this instrument. That makes question-level coverage visible alongside the observed scores. The melt reference describes the identifier and value columns used in this transformation.

Keep response coverage beside the mean

Clarity has scores 5 and 3, with one missing answer: its observed mean is 4. Usefulness has scores 4 and 2, with one missing answer: its observed mean is 3. Both questions have two responses from three eligible respondents.

If missing answers were filled with zero, the means would become approximately 2.67 and 2. Those values would describe an invented scoring rule, not the observed responses.

For a real survey, eligibility may differ by question because of branching. In that case, the denominator is not automatically every respondent. Retain the instrument's eligibility rules before calculating response rates.

Verify the round trip

import pandas as pd
from build_and_verify import ROOT

wide = pd.read_csv(ROOT / 'survey.csv',
                   dtype={'respondent_id': 'string', 'clarity': 'Int64', 'usefulness': 'Int64'})
long = wide.melt(id_vars='respondent_id', var_name='question', value_name='score')
restored = long.pivot(index='respondent_id', columns='question', values='score')
restored = restored.reindex(columns=['clarity', 'usefulness']).rename_axis(None, axis='columns')
pd.testing.assert_frame_equal(restored.sort_index(), wide.set_index('respondent_id').sort_index())
duplicate = pd.concat([long, long.iloc[[0]]], ignore_index=True)
try:
    duplicate.pivot(index='respondent_id', columns='question', values='score')
except ValueError:
    print('Round trip preserved values; duplicate response grain rejected')
else:
    raise AssertionError('Duplicate respondent-question pair accepted')

The pivot reference explains its uniqueness requirement. A pivot_table with an aggregation function can combine duplicate pairs, but that is an additional analytical decision.

Investigate repeated answers

If respondents can submit multiple times, add a submission identifier or an approved selection rule. First submission, latest submission and average across submissions answer different questions.

A timestamp alone may not resolve ties or corrections. Keep the raw submission evidence and explain how the retained response was selected. Avoid using row order as an undocumented proxy for recency.

Preserve question meaning

Question names, scale direction and allowed values belong in a data dictionary. A score of 5 could mean very satisfied in one question and very difficult in another. Combining the numbers without harmonizing their meanings can produce a meaningless overall score.

This tiny fixture uses two five-point questions only to demonstrate shape and missingness. It does not establish the validity of a survey scale or represent real learner satisfaction.

Exercise: add a third question asked only of R1 and R2. Represent R3 as ineligible rather than merely unanswered, then calculate each question's response rate using its correct denominator.

NeuraPath's Data Analytics with Generative AI course connects pandas reshaping with defensible survey reporting. The useful result preserves both the answer and the population that could have supplied it.

Continue learning

This article is part of the Pandas wrangling and data checks sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Handle missing values without inventing customer behaviour.
• Continue with Pandas string cleaning with reversible mappings.