Section 2.7

Wide vs Long Data

Q: My long file has 12 rows for one participant and 9 for another. Do I have to balance it?

Not for a mixed model, which uses whatever occasions each person actually provided. Classic repeated-measures ANOVA is the one that cannot cope, because it needs a full rectangle and will drop anyone with a gap. The subtler consequence is that once people contribute different numbers of rows, "the average" stops being one number. Three participants with 4, 2 and 2 trials, averaging 10, 20 and 30, give a grand mean of 17.5 across the eight rows and a mean of the person means of 20. Neither is wrong; they answer different questions, one about trials and one about people. Say which you computed, and prefer the person means whenever the person is the unit you sampled.

Q: My wide sheet has no ID column. Can I still pivot it to long?

Add one first. In a wide sheet each person is identified by nothing except their position in the file, and position is not data: the moment you pivot, sort, filter or merge, rows can move and nothing is left to say which measurements belonged to whom. One line that numbers the rows before any reshaping is enough (mutate(id = row_number()) in dplyr, reset_index() in pandas), though a real participant code from your codebook is better, because it still means something in the next file you open. Do it before the pivot rather than after. Once the table is long, the information you needed has already gone.

Q: Excel's pivot table turned my long file into one row per person. Is that the reshape?

It is a summary, not a reshape, and the difference matters. A PivotTable aggregates by default, summing numeric fields and counting everything else, so the numbers in the output are new numbers computed from your data rather than your data rearranged. pivot_wider() and pandas' pivot() move existing values into new columns and change nothing else. The quickest tell is to count: a genuine reshape preserves every value, so if 180 measurements went in and 60 cells came out, something was averaged or added along the way. That may be exactly what you wanted, but it is a decision about which unit you are analyzing, not a formatting step.

The same numbers can be laid out two ways. In wide format each person gets one row and their repeated measurements fan out across several columns. In long format each measurement gets its own row, tagged by who it belongs to and when it was taken. Neither is more correct, but your software has a strong opinion, and moving between the two is a single command once you can picture it.

The same data, two shapes

Picture four students measured three times. In wide form the table is short and fat, with one row per student and a column for each timepoint:

name	T1	T2	T3
Ann	10	13	15
Ben	8	11	10
…	…	…	…

The long form of the very same data is tall and thin: one row per measurement, with a time column naming the occasion and a score column holding the value:

name	time	score
Ann	T1	10
Ann	T2	13
Ann	T3	15
Ben	T1	8
…	…	…

Notice what happens to the column headers when you go long: T1, T2, T3 stop being column names and become values in the time column, and the name repeats down the rows. That's the whole trick: the reshape below lets you watch every cell make the trip.

🔄 Animated Pivot

The same four-person, three-timepoint dataset. Press the buttons to reshape it and watch each cell fly to its new home, the column headers becoming a time column, the names replicating, the scores rearranging.

name time (was a header) score

Which shape does my software want?

This is the practical reason the distinction matters, because the two ecosystems disagree:

Wide: classic point-and-click repeated-measures ANOVA in SPSS expects each timepoint in its own column (it reads "T1, T2, T3" as the levels of your within-subject factor). Correlation matrices and many spreadsheets are happiest wide too.
Long: the tidyverse (R), pandas, JASP's mixed-model tools, and every mixed / multilevel model want one row per observation, with columns for the person ID, the occasion, and the value. This is exactly tidy data: T1/T2/T3 are values of the variable time, so they belong in a column, not in the header.

So "which is right?" has no answer in the abstract. It depends on the next tool in your pipeline. What matters is that you can get from one to the other on demand.

Pivoting is one operation, not a rebuild

You never reshape a table by hand. Every data package has a matched pair of verbs:

wide → long: pivot_longer() (tidyr) or melt() (pandas), which collapse a set of columns into a key column (which header?) and a value column (what number?).
long → wide: pivot_wider() (tidyr) or pivot() (pandas), which spread a key column back out into one column per level.
in SPSS: Data → Restructure… opens a wizard offering the same pair, "Restructure selected variables into cases" for wide to long and "Restructure selected cases into variables" for long to wide.

Both are lossless and reversible: pivot longer then wider and you're back where you started, provided each row is uniquely identified by its keys (here, name + time). If a pivot silently drops or duplicates rows, that's usually a sign your keys aren't unique — the same alarm bell you'll meet again when merging datasets.

The shape decides who gets dropped

Wide format has to be a rectangle. Each person owns one row, so each occasion needs a cell, and a participant who missed T2 leaves a hole in theirs. Classic repeated-measures ANOVA then removes that person completely, T1 and T3 along with the gap, because it can only work with rows that are full. Long format has no rectangle to fill: a measurement that never happened is a row that isn't there, and a mixed model uses whichever occasions each person did provide.

With a handful of dropouts this decides, without announcing itself, how much of your data reaches the analysis at all. If your sample size shrinks between the data file and the output, the shape is the first place to look and why those values are missing is the second.

Collapsing is not pivoting

A pivot is reversible. Averaging is not, and students reach for it constantly, usually while trying to make a file "fit" a procedure. Take the site's own strength-training file: 60 athletes, half on a program and half controls, measured at weeks 0, 6 and 12. Go long and you have 180 rows. Three questions are now available, and the shape you hand the software decides which one it answers.

Feed all 180 rows to a two-sample t-test comparing the groups and it reports t(178) = 1.37, p = .172, with a 95% interval on the difference of [−1.25, 6.95]. Those degrees of freedom are the giveaway. There were never 180 independent athletes; there were 60, each measured three times, and the three measurements of one athlete agree closely (the intraclass correlation here is .91). Collapse each athlete to their own mean and the same comparison becomes t(58) = 0.81, p = .424, interval [−4.22, 9.92]. The estimate did not move at all. Only the honesty of it did: the standard error grew from 2.08 to 3.53, a factor of 1.70, almost exactly the √2.83 that the design effect predicts for three measurements agreeing that closely.

Collapsing bought independence. It also threw away the study. The athletes were not recruited to differ from each other at baseline, and they don't: the two groups start at 101.9 and 101.5. What the program did was change people over 12 weeks, by 8.7 points against the controls' 3.3, which is t(58) = 6.77 and p < .001, an effect neither of the first two tests came close to seeing. It lives in the differences between an athlete's own occasions, and averaging those occasions together is precisely the operation that deletes it.

What this asks of you is that you know your level of analysis, which is the phrase you will meet in a methods section. One row per measurement keeps every question open and asks you to model the structure. One row per person makes the rows independent and fixes the question to be about people. Reshaping moves between the two on demand; averaging picks one and discards the other, so do it deliberately and say in the write-up that you did. Software will not warn you either way. Ask pivot_wider() for one row per person when a person has several values and tidyr says the values "are not uniquely identified", handing back a column of lists; add values_fn = mean and it averages without further comment, which is the same decision made in four characters.

Wide is short and fat (one row per subject); long is tall and thin (one row per measurement). Long format is tidy and is what R, pandas, JASP, and mixed models want; wide is what SPSS repeated-measures and correlation matrices want. Learn the two reshape verbs and the shape becomes a setting you flip, not a table you rebuild.

Why it matters: half of "my analysis won't run" is really "my data is in the wrong shape for this tool." Keep your clean data in long (tidy) form as the reference copy, and pivot to wide only when a specific procedure demands it, as a step in your script, so the reshape is documented and reversible like every other cleaning step.

Common questions

My long file has 12 rows for one participant and 9 for another. Do I have to balance it?

Not for a mixed model, which uses whatever occasions each person actually provided. Classic repeated-measures ANOVA is the one that cannot cope, because it needs a full rectangle and will drop anyone with a gap. The subtler consequence is that once people contribute different numbers of rows, "the average" stops being one number. Three participants with 4, 2 and 2 trials, averaging 10, 20 and 30, give a grand mean of 17.5 across the eight rows and a mean of the person means of 20. Neither is wrong; they answer different questions, one about trials and one about people. Say which you computed, and prefer the person means whenever the person is the unit you sampled.

My wide sheet has no ID column. Can I still pivot it to long?

Add one first. In a wide sheet each person is identified by nothing except their position in the file, and position is not data: the moment you pivot, sort, filter or merge, rows can move and nothing is left to say which measurements belonged to whom. One line that numbers the rows before any reshaping is enough (mutate(id = row_number()) in dplyr, reset_index() in pandas), though a real participant code from your codebook is better, because it still means something in the next file you open. Do it before the pivot rather than after. Once the table is long, the information you needed has already gone.

Excel's pivot table turned my long file into one row per person. Is that the reshape?

It is a summary, not a reshape, and the difference matters. A PivotTable aggregates by default, summing numeric fields and counting everything else, so the numbers in the output are new numbers computed from your data rather than your data rearranged. pivot_wider() and pandas' pivot() move existing values into new columns and change nothing else. The quickest tell is to count: a genuine reshape preserves every value, so if 180 measurements went in and 60 cells came out, something was averaged or added along the way. That may be exactly what you wanted, but it is a decision about which unit you are analyzing, not a formatting step.