Survey exports give you one row per respondent and one column per question — a shape that makes analysis painful. Learn how to use pd.melt() and stack() in pandas to transform wide survey data into long format, then aggregate and visualize with ease.
You've just exported 500 responses from a Google Form or SurveyMonkey and opened the CSV. The column headers read something like Q1_Satisfaction, Q2_Ease_of_Use, Q3_Would_Recommend, Q4_Support_Rating, and so on across dozens of columns — one column per question, one row per respondent. This is called wide format: every measurement about a single subject lives on the same row, spreading outward across columns.
Wide format is a perfectly natural way to collect data. Survey tools produce it because it's easy to read row by row. But when you want to analyze patterns across questions — average scores by department, response distributions by question, or how satisfaction correlates with support ratings — that horizontal layout fights you at every turn. You can't easily groupby a column that doesn't exist yet. You can't plot all question scores in a single chart without gymnastics. What you need is long format: one row per respondent-question combination, with a column for the question name and a column for the answer. The same data, restructured so pandas can actually slice and aggregate it.
By the end of this lesson, you'll know exactly how to do that transformation. We'll work through a realistic employee engagement survey dataset and cover the full workflow from messy wide export to clean, analysis-ready long DataFrame.
What you'll learn:
pd.melt() to unpivot wide survey data into long formatstack() as an alternative approach for structured question setsYou should be comfortable loading a CSV into pandas and doing basic column operations. If you're new to pandas, start with Your First pandas DataFrame: Loading CSV and Excel Files and Exploring Data before continuing. You should also understand how groupby works conceptually — we'll use it at the end, and Grouping and Aggregating in pandas: groupby as the PivotTable Replacement covers it in depth.
Before writing a single line of code, let's make sure the mental model is solid, because this is the concept that unlocks everything else.
Imagine a simple survey with three questions asked to four employees:
Wide format (what your export looks like):
respondent_id department Q1_satisfaction Q2_ease_of_use Q3_recommend
1001 Engineering 4 3 5
1002 Marketing 2 4 3
1003 Engineering 5 5 4
1004 HR 3 2 2
Long format (what analysis needs):
respondent_id department question score
1001 Engineering Q1_satisfaction 4
1001 Engineering Q2_ease_of_use 3
1001 Engineering Q3_recommend 5
1002 Marketing Q1_satisfaction 2
1002 Marketing Q2_ease_of_use 4
1002 Marketing Q3_recommend 3
...
Notice what happened: the four respondents now occupy twelve rows (4 × 3 questions). The column names Q1_satisfaction, Q2_ease_of_use, Q3_recommend have become values in a question column. The scores that were spread across three columns are now stacked in one score column.
This is called unpivoting or melting — the wide table "melts" sideways into a tall stack. The term comes from pandas' own function name: pd.melt().
Key insight
Long format is the lingua franca of statistical analysis and visualization libraries. groupby, seaborn, plotly, and virtually every BI tool expect one observation per row. Converting to long format once at the start of your pipeline saves you from writing bespoke code for every chart and aggregation.
Let's build a dataset that mirrors what you'd actually receive from a survey export. We'll simulate an employee engagement survey with metadata columns (things about the respondent) and multiple question columns (the actual responses).
import pandas as pd
import numpy as np
# Simulate a survey export - 8 respondents, mix of departments
data = {
'respondent_id': [1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008],
'department': ['Engineering', 'Marketing', 'Engineering', 'HR',
'Marketing', 'HR', 'Engineering', 'Marketing'],
'tenure_years': [3, 1, 7, 2, 4, 6, 1, 5],
'submitted_date': ['2024-03-01', '2024-03-01', '2024-03-02', '2024-03-02',
'2024-03-03', '2024-03-03', '2024-03-04', '2024-03-04'],
'Q1_job_satisfaction': [4, 2, 5, 3, 4, 3, 5, 2],
'Q2_work_life_balance': [3, 4, 4, 2, 3, 2, 4, 5],
'Q3_manager_support': [5, 3, 5, 4, 2, 4, 5, 3],
'Q4_career_growth': [2, 3, 4, 3, 4, 2, 3, 4],
'Q5_recommend_company':[4, 2, 5, 3, 3, 3, 5, 2],
}
df = pd.DataFrame(data)
print(df.shape)
# (8, 9)
print(df.head(3))
This gives you a clean wide-format DataFrame: 8 rows, 4 metadata columns, and 5 question columns. This is exactly the shape of a real survey export — just smaller.
pd.melt() is pandas' primary function for unpivoting. It takes columns you want to keep fixed (called id_vars — the identifier variables) and columns you want to unpivot (called value_vars — the measurement columns), and stacks the value_vars into two new columns: one for the original column name, one for the value.
Here's the basic call:
# Identify which columns are metadata (keep fixed) vs. questions (unpivot)
id_cols = ['respondent_id', 'department', 'tenure_years', 'submitted_date']
question_cols = ['Q1_job_satisfaction', 'Q2_work_life_balance',
'Q3_manager_support', 'Q4_career_growth', 'Q5_recommend_company']
df_long = pd.melt(
df,
id_vars=id_cols, # columns to keep as-is on every row
value_vars=question_cols, # columns to unpivot into rows
var_name='question', # name for the new "column names" column
value_name='score' # name for the new "values" column
)
print(df_long.shape)
# (40, 6) — 8 respondents × 5 questions = 40 rows
print(df_long.head(10))
Output (first 10 rows):
respondent_id department tenure_years submitted_date question score
0 1001 Engineering 3 2024-03-01 Q1_job_satisfaction 4
1 1002 Marketing 1 2024-03-01 Q1_job_satisfaction 2
2 1003 Engineering 7 2024-03-02 Q1_job_satisfaction 5
3 1004 HR 2 2024-03-02 Q1_job_satisfaction 3
4 1005 Marketing 4 2024-03-03 Q1_job_satisfaction 4
5 1006 HR 6 2024-03-03 Q1_job_satisfaction 3
6 1007 Engineering 1 2024-03-04 Q1_job_satisfaction 5
7 1008 Marketing 5 2024-03-04 Q1_job_satisfaction 2
8 1001 Engineering 3 2024-03-01 Q2_work_life_balance 3
9 1002 Marketing 1 2024-03-01 Q2_work_life_balance 4
Notice the pattern: all 8 respondents appear for Q1 first, then all 8 for Q2, and so on. The department, tenure_years, and submitted_date columns repeat for every row belonging to that respondent — that's exactly what you want. Each row is now a single observation: one respondent, one question, one score.
Tip
If you don't specify value_vars, pandas will unpivot every column that isn't listed in id_vars. That works, but being explicit makes your code self-documenting and prevents accidents when the survey export adds a new column next month.
Right now the question column contains the raw column headers from the survey export: Q1_job_satisfaction, Q2_work_life_balance, etc. For reporting purposes, you'll often want to strip the question number prefix or convert underscores to spaces. Here's how to do that cleanly:
# Extract just the descriptive part after the underscore
# "Q1_job_satisfaction" -> "job satisfaction"
df_long['question_label'] = (
df_long['question']
.str.split('_', n=1) # split on first underscore only
.str[1] # take everything after it
.str.replace('_', ' ') # replace remaining underscores with spaces
)
# Keep the question number separately if you need sort order
df_long['question_num'] = df_long['question'].str.extract(r'(Q\d+)')
print(df_long[['question', 'question_label', 'question_num']].drop_duplicates())
Output:
question question_label question_num
0 Q1_job_satisfaction job satisfaction Q1
8 Q2_work_life_balance work life balance Q2
16 Q3_manager_support manager support Q3
24 Q4_career_growth career growth Q4
32 Q5_recommend_company recommend company Q5
You now have both a clean label for display and a question number for preserving sort order in charts.
Note
String manipulation in pandas is a whole skill on its own. If your survey column names are messier than this — mixed case, special characters, inconsistent separators — the techniques in Text Cleanup at Scale with pandas String Methods and Regular Expressions will serve you well.
Now that the data is long, the analysis almost writes itself. Want average score per question? One groupby:
avg_by_question = (
df_long
.groupby('question_label')['score']
.mean()
.round(2)
.sort_values(ascending=False)
)
print(avg_by_question)
Output:
question_label
manager support 3.88
work life balance 3.38
job satisfaction 3.38
career growth 3.12
recommend company 3.25
dtype: float64
Want to break that down by department?
dept_question = (
df_long
.groupby(['department', 'question_label'])['score']
.mean()
.round(2)
.unstack('question_label')
)
print(dept_question)
This produces a clean cross-tabulation: departments as rows, questions as columns, mean scores as values — something you simply could not produce from the wide format with a single groupby. The long format made that possible.
Key insight
Every time you think "I want to compare questions," that's a signal the question name needs to be a value in a column, not the column name itself. That's the fundamental reason why wide-to-long transformation matters.
Real surveys often mix question types: some questions get numeric Likert scores, others get free-text comments, and sometimes there are checkbox or multi-select questions. You don't want to melt all of them into the same score column — text answers mixed with numeric scores will silently convert your integer column to object dtype, breaking any aggregation you try to run.
The solution is to melt question blocks separately and then combine them, or simply exclude non-numeric columns from your melt entirely.
# More realistic export with a mix of types
data_mixed = {
'respondent_id': [1001, 1002, 1003],
'department': ['Engineering', 'Marketing', 'HR'],
'Q1_satisfaction': [4, 2, 5],
'Q2_ease_of_use': [3, 4, 5],
'Q3_open_feedback': ['Great team', 'More flexibility', 'Better tools'],
'Q4_recommend': [5, 3, 4],
}
df_mixed = pd.DataFrame(data_mixed)
# Identify numeric vs. text question columns explicitly
numeric_q_cols = ['Q1_satisfaction', 'Q2_ease_of_use', 'Q4_recommend']
text_q_cols = ['Q3_open_feedback']
# Melt numeric questions into scored responses
df_scores = pd.melt(
df_mixed,
id_vars=['respondent_id', 'department'],
value_vars=numeric_q_cols,
var_name='question',
value_name='score'
)
df_scores['score'] = df_scores['score'].astype(int)
# Melt text questions separately
df_text = pd.melt(
df_mixed,
id_vars=['respondent_id', 'department'],
value_vars=text_q_cols,
var_name='question',
value_name='response_text'
)
print(df_scores.dtypes)
# score is int64 — safe to aggregate
print(df_text.head())
You can store these in separate DataFrames or separate sheets of an output workbook, keeping numeric analysis and text analysis on clean separate tracks.
Warning
If you accidentally melt text and numeric columns into the same value column, pandas will cast the entire column to object (string) dtype. Your mean() and sum() calls will silently fail or raise errors. Always check df_long['score'].dtype after melting to confirm you got what you expected.
For surveys where your question columns follow a very consistent pattern — especially if they share a common prefix or were originally organized in a MultiIndex — pandas' stack() method offers a more structural approach.
stack() works on the columns level of a DataFrame: it "folds" column headers down into a row index level. The result is a Series with a two-level index (original row index + column name).
# Start from the original wide df, set respondent_id as index
df_stacked = (
df
.set_index(['respondent_id', 'department', 'tenure_years', 'submitted_date'])
[question_cols] # keep only the question columns
.stack() # fold column headers into the index
.reset_index() # bring index levels back as columns
.rename(columns={'level_4': 'question', 0: 'score'})
)
print(df_stacked.head(6))
The result is functionally identical to what melt() produced. The practical difference:
melt() is more explicit and easier to read. It's your go-to for survey data.stack() shines when you're working with MultiIndex column structures that arise after a pivot_table() or groupby() with multiple aggregations.For a deeper look at when to use each tool, Reshaping Wide and Long Data for Reporting: When and How to Use melt, pivot, and unstack in pandas covers the full picture.
Let's put everything together into a clean, reusable pipeline that you could drop into any survey analysis project:
import pandas as pd
def load_and_reshape_survey(filepath, id_cols, question_prefix='Q'):
"""
Load a wide-format survey CSV and reshape to long format.
Parameters
----------
filepath : str
Path to the survey CSV export.
id_cols : list
Column names to treat as respondent metadata (kept fixed).
question_prefix : str
Prefix that identifies question columns (default 'Q').
Returns
-------
pd.DataFrame
Long-format DataFrame with columns: id_cols + ['question', 'question_label',
'question_num', 'score']
"""
df = pd.read_csv(filepath)
# Automatically detect question columns by prefix
question_cols = [c for c in df.columns if c.startswith(question_prefix)]
# Melt to long format
df_long = pd.melt(
df,
id_vars=id_cols,
value_vars=question_cols,
var_name='question',
value_name='score'
)
# Coerce score to numeric, treating failures as NaN
df_long['score'] = pd.to_numeric(df_long['score'], errors='coerce')
# Parse question labels and numbers
df_long['question_label'] = (
df_long['question']
.str.split('_', n=1).str[1]
.str.replace('_', ' ')
)
df_long['question_num'] = df_long['question'].str.extract(r'(Q\d+)')
# Sort so the data reads: respondent → questions in order
df_long = df_long.sort_values(['respondent_id', 'question_num']).reset_index(drop=True)
return df_long
# Usage
id_columns = ['respondent_id', 'department', 'tenure_years', 'submitted_date']
# df_long = load_and_reshape_survey('engagement_survey_march2024.csv', id_columns)
# For now, use our in-memory df
df_long_clean = pd.melt(
df,
id_vars=id_columns,
value_vars=question_cols,
var_name='question',
value_name='score'
)
df_long_clean['score'] = pd.to_numeric(df_long_clean['score'], errors='coerce')
df_long_clean['question_label'] = (
df_long_clean['question'].str.split('_', n=1).str[1].str.replace('_', ' ')
)
# Quick sanity check: how many rows? Should be n_respondents × n_questions
expected = df.shape[0] * len(question_cols)
actual = df_long_clean.shape[0]
assert actual == expected, f"Row count mismatch: expected {expected}, got {actual}"
print(f"✓ Long format has {actual} rows ({df.shape[0]} respondents × {len(question_cols)} questions)")
The assert at the end is a habit worth keeping. After any reshape, verify that the row count is exactly what you expect. A mismatch means something went wrong — duplicate columns, an off-by-one in your id_vars, or unexpected data.
Tip
For analysis pipelines you'll run repeatedly — such as a monthly engagement survey — wrapping the reshape logic in a function like the one above pays off quickly. The article on Structuring a Reusable Data Analysis Project: Functions, Modules, Notebooks, and Scripts explains how to organize this kind of code so it's easy to maintain and share.
Work through this exercise to cement the concepts. Create the following wide-format DataFrame in a notebook:
import pandas as pd
survey_data = {
'response_id': [201, 202, 203, 204, 205],
'region': ['North', 'South', 'North', 'East', 'West'],
'customer_segment': ['Enterprise', 'SMB', 'SMB', 'Enterprise', 'SMB'],
'Q1_product_quality': [5, 3, 4, 5, 2],
'Q2_customer_service': [4, 4, 3, 5, 3],
'Q3_value_for_money': [3, 5, 4, 2, 4],
'Q4_ease_of_onboarding':[4, 3, 5, 4, 2],
'Q5_nps_score': [9, 6, 8, 10, 5],
'open_comment': ['Excellent', 'Decent', 'Happy overall', 'Best vendor', 'Needs work'],
}
df_exercise = pd.DataFrame(survey_data)
Tasks:
Melt the five numeric question columns (Q1 through Q5) into long format, keeping response_id, region, and customer_segment as id columns. Name the new columns question and rating.
Add a question_label column that strips the question number prefix (e.g., Q1_product_quality → product quality).
Calculate the average rating for each question_label, sorted highest to lowest.
Calculate the average rating per customer_segment per question_label. Which segment rates value_for_money highest?
Keep the open_comment column in a separate DataFrame (don't melt it in). Merge it back onto your long-format DataFrame so each row also has the respondent's comment.
Mistake 1: Including metadata columns in value_vars
If you accidentally list a metadata column like department in value_vars, it'll get unpivoted as if it were a question score. The department name "Engineering" will show up as a value in your score column, breaking your numeric dtype. Always double-check your id_vars and value_vars lists before melting.
Mistake 2: Forgetting that melt() doesn't sort by respondent
By default, melt() stacks all respondents for question 1, then all for question 2, and so on. If you want respondent 1001's five rows grouped together, sort after melting:
df_long = df_long.sort_values(['respondent_id', 'question_num']).reset_index(drop=True)
Mistake 3: Mixed dtypes in the score column
If even one value in a question column is a string (like "N/A" or "Prefer not to say"), pandas will infer the entire column as object, and after melting, the score column will be object too. Use pd.to_numeric(df_long['score'], errors='coerce') to convert cleanly, which turns uncastable values into NaN rather than crashing.
Mistake 4: Losing respondents in the melt
If your id_vars don't uniquely identify respondents — for example, if you forgot to include respondent_id — multiple respondents can appear indistinguishable in the long format. Always include a true unique identifier in id_vars.
Mistake 5: Not validating row counts
We showed the assert trick above. Use it every time. The expected row count is always n_respondents × n_question_columns (minus any rows dropped for missing data). If those numbers don't match, investigate before proceeding.
Warning
Survey exports from different platforms (Google Forms, Qualtrics, SurveyMonkey, Typeform) all use different conventions for column naming, skipped questions, and missing values. Before reshaping, spend time with Cleaning Messy Data with pandas: Missing Values, Duplicates, and Data Types to handle the platform-specific quirks you'll inevitably encounter.
You now have a complete, working approach to one of the most common data reshaping problems in applied analytics. Here's what you've learned:
groupby, visualization, and statistical comparison.pd.melt() is your primary tool. Specify id_vars for metadata columns that stay fixed, value_vars for question columns that become rows, and var_name/value_name for the two new columns.stack() is a structural alternative that works well when columns have MultiIndex hierarchies.pd.to_numeric(..., errors='coerce') to handle survey noise.Where to go from here:
Once your data is in long format, the full power of pandas opens up. You can visualize question score distributions with Visualizing Data with matplotlib and seaborn: Charts That Explain Your Analysis, or cross-tabulate responses by segment using Summarizing and Cross-Tabulating Categorical Data in pandas: value_counts, crosstab, and Percentage Breakdowns. If you're building a recurring survey report, the patterns in Building a Reusable ETL Pipeline in pandas: Extract, Transform, and Load Data from Multiple Sources into a Clean, Analysis-Ready Output will help you automate the whole thing end to end.