By Quick answer: Use melt to turn wide data into long data, and pivot to turn long data back into wide. Wide means one row per subject with a column per measurement. Long means one row per measurement. melt collapses many columns into two, a name column and a value column. pivot spreads those two columns back out again. They are opposites.
What is wide format and what is long format?
Wide format has one row per subject and one column per measurement. Long format has one row per measurement, with the measurement's name in its own column. Same information, different shape.
Nearly every charting library, grouping operation and statistical model in Python expects long data, while nearly every spreadsheet a colleague sends you is wide. Reshaping is the step between the file you were given and the analysis you were asked for, and it is where many beginners get stuck on a single error message.
Here is a batch of three students with marks in three subjects, in wide format.
import pandas as pd wide = pd.DataFrame({ "student": ["Aarav", "Diya", "Rohan"], "python": [78, 91, 64], "sql": [85, 68, 79], "statistics": [72, 88, 81], })
student python sql statistics 0 Aarav 78 85 72 1 Diya 91 68 88 2 Rohan 64 79 81
Three rows, four columns. A human reads this easily. The same marks in long format take nine rows and three columns, one row per mark.
Neither shape is correct in the abstract. Wide is for reading, long is for computing. The skill is knowing which one the next step needs, and switching cheaply.
| Wide | Long | |
|---|---|---|
| One row is | A subject | A single measurement |
| Columns grow when | You add a measurement | Never, they stay fixed |
| Good for | Reading, Excel reports, correlation matrices | Grouping, plotting, modelling, databases |
| Adding a new subject area means | Altering the table structure | Appending rows |
| In this example | 3 rows × 4 columns | 9 rows × 3 columns |
Note the last row. Add a fourth subject and the wide table needs a new column, while the long table just gets three more rows. This is why database tables and dashboard tools store data long.
How do you use melt to turn wide data into long?
Call melt, tell it which columns to keep as identifiers with id_vars, and name the two columns it creates with var_name and value_name. Everything not listed in id_vars gets collapsed.
long = wide.melt(
id_vars="student", # columns to keep as they are
var_name="subject", # name for the old column headers
value_name="score", # name for the values
)student subject score 0 Aarav python 78 1 Diya python 91 2 Rohan python 64 3 Aarav sql 85 4 Diya sql 68 5 Rohan sql 79 6 Aarav statistics 72 7 Diya statistics 88 8 Rohan statistics 81
Three points that catch people out.
The old column headers become data
python, sql and statistics were column names. They are now values sitting in a column called subject. That is the whole trick of melting.
The row order is grouped by the old column, not by student
All the python rows come first, then all the sql rows. If you expected Aarav's three subjects together, sort afterwards with long.sort_values("student").
Without var_name and value_name you get variable and value
The defaults work, but naming the columns takes two seconds and saves every future reader a guess.
To melt only some columns, add value_vars. Anything left out is dropped entirely, which is a quick way to narrow a wide table and reshape it in one step.
wide.melt(id_vars="student", value_vars=["python", "sql"], var_name="subject", value_name="score") # 6 rows, statistics is gone
You can also pass several identifier columns, the normal case in real data: a batch code, a centre and an enrolment date all stay put while the measurement columns collapse.
wide.assign(batch="B-11").melt( id_vars=["student", "batch"], var_name="subject", value_name="score")
How do you use pivot to turn long data back into wide?
Call pivot with three arguments: index for the column that becomes the rows, columns for the column whose values become the new headers, and values for the numbers to fill in.
back = long.pivot(index="student", columns="subject", values="score")
subject python sql statistics student Aarav 78 85 72 Diya 91 68 88 Rohan 64 79 81
Look at the top-left corner. There is a stray subject label above the column names and student has become the index rather than an ordinary column. This is correct pandas behaviour, not a bug, but it makes the result awkward to export or merge. Flatten it:
flat = back.reset_index().rename_axis(columns=None)student python sql statistics 0 Aarav 78 85 72 1 Diya 91 68 88 2 Rohan 64 79 81
That is the original wide table, exactly. melt and pivot are a round trip, which makes them easy to test: melt something, pivot it back, check you get what you started with.
What is the difference between pivot and pivot_table?
pivot only rearranges, and fails if two rows compete for the same cell. pivot_table aggregates, and combines competing rows using a function, by default the mean. Use pivot when you expect exactly one value per cell, and pivot_table when you expect several and want them summarised.
pivot | pivot_table | |
|---|---|---|
| What it does | Rearranges values | Aggregates, then rearranges |
| Duplicate index and column pair | Raises ValueError | Combines them with aggfunc |
| Default aggregation | None | "mean" |
| Accepts a list of functions | No | Yes, e.g. ["mean", "max", "count"] |
| Row and column totals | No | Yes, with margins=True |
| Speed | Faster, it only reshapes | Slower, it groups first |
| Use it when | One value per cell, guaranteed | Several values per cell, or you want a summary |
The error-raising behaviour of pivot is a correctness check, not a limitation. If you believed there was one mark per student per subject and pivot disagrees, your assumption was wrong and you have found out cheaply.
pivot_table also gives you totals in one argument, the fastest way to produce a summary table a manager will read.
long.pivot_table(index="student", columns="subject", values="score", aggfunc="mean", margins=True, margins_name="average").round(1)
subject python sql statistics average student Aarav 78.0 85.0 72.0 78.3 Diya 91.0 68.0 88.0 82.3 Rohan 64.0 79.0 81.0 74.7 average 77.7 77.3 80.3 78.4
Why does pivot raise "Index contains duplicate entries, cannot reshape"?
Because two or more rows in your long data have the same index and column combination, so pandas does not know which value belongs in that cell. It is the commonest error in this whole topic and it almost always means your data has a dimension you forgot about.
Suppose students can retake a test, so there are two attempts per subject.
retest = pd.DataFrame({
"student": ["Aarav", "Aarav", "Diya", "Diya"],
"subject": ["python", "python", "sql", "sql"],
"attempt": [1, 2, 1, 2],
"score": [78, 84, 68, 75],
})
retest.pivot(index="student", columns="subject", values="score")ValueError: Index contains duplicate entries, cannot reshape
Aarav and python appear twice, with 78 and 84. Pandas refuses to guess. There are three honest fixes, and which one is right depends on what you actually want.
Fix 1: add the missing column to the index
If both attempts matter, say so.
retest.pivot(index=["student", "attempt"], columns="subject", values="score")
subject python sql
student attempt
Aarav 1 78.0 NaN
2 84.0 NaN
Diya 1 NaN 68.0
2 NaN 75.0Fix 2: aggregate, using pivot_table
If you want one number per student per subject, state the rule for combining attempts.
retest.pivot_table(index="student", columns="subject", values="score", aggfunc="mean")
subject python sql student Aarav 81.0 NaN Diya NaN 71.5
Fix 3: decide the duplicates are wrong and remove them
If the second attempt is a data entry error rather than a real retake, fix it at the source rather than papering over it with aggfunc.
Run df.duplicated(subset=[index_col, column_col], keep=False).sum() before every pivot. If it returns anything above zero, you decide how to handle it, rather than pandas deciding for you at the worst moment.
Note the NaN cells above. Reshaping creates gaps wherever a combination never happened, and those gaps are information: Aarav has no sql mark here. For how to handle them, see the missing values step in our step-by-step data cleaning tutorial in pandas.
How are stack and unstack different from melt and pivot?
stack and unstack do the same reshaping, but they work on the index rather than on named columns, and they return a Series with a MultiIndex rather than a flat DataFrame. Reach for them when you are already working with a MultiIndex, and for melt and pivot otherwise.
wide.set_index("student").stack()student
Aarav python 78
sql 85
statistics 72
Diya python 91
...
dtype: int64That is the long data again, but as a Series with a two-level index. .unstack() reverses it.
stack now keeps missing values rather than dropping them, so code that relied on empty combinations disappearing returns extra NaN rows. Verify on your own data before upgrading a pipeline that depends on it.
There is also pd.wide_to_long, for the narrower case of columns named with a stub and a suffix, such as score_2025 and score_2026.
| Function | Direction | Works on | Returns |
|---|---|---|---|
melt | Wide to long | Named columns | DataFrame |
pivot | Long to wide | Named columns | DataFrame |
pivot_table | Long to wide, aggregating | Named columns | DataFrame |
stack | Wide to long | The column index | Series |
unstack | Long to wide | The row index | DataFrame |
wide_to_long | Wide to long | Columns with a stub and suffix | DataFrame |
What does this look like end to end?
The common situation: several monthly spreadsheets in wide format, needing one chart.
# 1. Combine the files, still wide # 2. Reshape to long, once long = wide.melt(id_vars=["student", "batch"], var_name="subject", value_name="score") # 3. Now every analysis is one line long.groupby("subject")["score"].mean().round(1)
subject python 77.7 sql 77.3 statistics 80.3
That groupby is impossible on the wide table without naming every subject column by hand. On the long table it does not care how many subjects exist, so it keeps working when a fourth subject is added next term. For combining the source files, see how to merge multiple CSV files into one DataFrame in Python.
When should you not reshape your data?
Often. Reshaping is a habit as much as a technique, and these five situations are where the habit costs you something.
| If your situation is… | Reshaping is… | Do this instead |
|---|---|---|
| You only want one number per category | ✗ Unnecessary | df.groupby("subject")["score"].mean() gets you there directly |
| Building a correlation matrix or heatmap | ✗ The wrong direction | df.corr() wants wide; leave it wide |
| Plotting with Seaborn or Plotly Express | ✓ Genuinely needed | Reshape to long once, at the start |
| A few million rows from a SQL source | ✗ Expensive in memory | Shape it with GROUP BY before it reaches Python |
| Making a notebook print nicely | ✗ Formatting, not analysis | Say so in a comment so nobody assumes it is load-bearing |
Why the memory point matters
Long format costs memory. In the tiny example here the wide frame holds 365 bytes and the long one 1,185 bytes, over three times as much, because every identifier repeats once per measurement. On a few million rows that stops being academic. If memory is already the bottleneck, see our guide to the Polars library in Python.
If your data comes from SQL, shape it in the query, not in pandas. GROUP BY or the database's own pivot syntax runs on a machine built for the job, and it moves less data across the wire. Reshaping a large DataFrame in memory is the habit of people who learned pandas first and SQL second.
What should you do next?
Three things, each of which takes under ten minutes.
- Melt one of your own wide spreadsheets and pivot it straight back. If you get the original table, you have understood both functions.
- Run
df.duplicated(subset=[index_col, column_col], keep=False).sum()before every pivot, and decide what to do before pandas decides for you. - Check which shape your plotting library expects, and reshape once at the start rather than repeatedly through the analysis.
Frequently asked questions
What is the difference between melt and pivot in pandas?
melt goes from wide to long by collapsing many columns into a name column and a value column. pivot goes from long back to wide by spreading those two columns out again. They are inverse operations, so melting and then pivoting returns the original table.
Why does pandas say "Index contains duplicate entries, cannot reshape"?
Two or more rows share the same index and column combination, so pandas cannot decide which value belongs in that cell. Either add the column that distinguishes them to index, or use pivot_table with an aggfunc to combine them deliberately.
When should I use pivot_table instead of pivot?
Use pivot_table whenever a cell can legitimately hold more than one value and you want them summarised, or when you want row and column totals with margins=True. Use pivot when you expect exactly one value per cell and want an error if that is not true.
What is the difference between stack and melt?
They reshape the same way, but stack works on the column index and returns a Series with a MultiIndex, while melt works on named columns and returns a flat DataFrame. Use melt for ordinary tables and stack when you are already working with a MultiIndex.
Does melt change my original DataFrame?
No. melt returns a new DataFrame and leaves the original untouched, so you must assign the result to a variable. This is true of pivot, pivot_table, stack and unstack as well.
How do I get rid of the extra column label after pivoting?
Pivoting leaves the columns axis named after the column you spread out. Remove it with .rename_axis(columns=None), and turn the index back into an ordinary column with .reset_index() if you need a flat table.
Sources
All code in this article was executed on pandas 3.0.5. Every output shown is copied from the actual run, not written from memory.
- pandas documentation. "DataFrame.melt." pandas.pydata.org
- pandas documentation. "DataFrame.pivot." pandas.pydata.org
- pandas documentation. "DataFrame.pivot_table." pandas.pydata.org
- pandas user guide. "Reshaping and pivot tables." pandas.pydata.org
- pandas release notes. "What's new in 3.1.0," 22 October 2026. pandas.pydata.org
- Wickham, H. "Tidy Data." Journal of Statistical Software, 59(10), 2014. doi.org/10.18637/jss.v059.i10