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 only by their position in the file, and position is not data. Once you pivot, sort, filter or merge, rows can move, and nothing records 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). 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. A PivotTable aggregates by default, summing numeric fields and counting everything else. The numbers in the output are new numbers computed from your data, not your data rearranged. pivot_wider() and pandas' pivot() move existing values into new columns and change nothing else. The quickest check is to count: a 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 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 most procedures accept only one of them, and converting between the two is a single command.

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
…	…	…

When you go long, the column headers change role: T1, T2, T3 stop being column names and become values in the time column, and the name repeats down the rows. The reshape below shows where every cell goes.

🔄 Animated Pivot

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

name time (was a header) score

Which shape does my software need?

The distinction matters in practice because different tools require different shapes:

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 also work best with wide data.
Long: the tidyverse (R), pandas, JASP's mixed-model tools, and every mixed / multilevel model require 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, your keys usually aren't unique. The same warning sign comes up 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 choice decides how much of your data reaches the analysis, and nothing in the output says so. 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 different analyses are now possible, and the shape of the file you analyze decides which question gets answered.

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 show the problem. 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 standard error changed, and the first one was too small: it grew from 2.08 to 3.53, a factor of 1.70. That is almost exactly the √2.83 that the design effect predicts for three measurements that agree so closely.

Collapsing made the rows independent, but it also threw away what the study was about. 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. The program changed people over 12 weeks, by 8.7 points against the controls' 3.3. That is t(58) = 6.77, p < .001, an effect neither of the first two tests came close to detecting. The effect is in the differences between an athlete's own occasions, and averaging those occasions together removes it.

So you need to know your level of analysis, a phrase you will meet in methods sections. One row per measurement keeps every question open but requires you to model the structure. One row per person makes the rows independent and limits the question to 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" and returns a column of lists. Add values_fn = mean and it averages without further comment: the same decision, made by typing one argument.

Wide is short and fat (one row per subject); long is tall and thin (one row per measurement). Long format is tidy, and R, pandas, JASP, and mixed models require it. SPSS repeated-measures and correlation matrices require wide. Once you know the two reshape verbs, you can change the shape with one command.

Why it matters: often, "my analysis won't run" means "my data is in the wrong shape for this tool." Keep your clean data in long (tidy) form as the reference copy. Pivot to wide only when a specific procedure requires 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 only by their position in the file, and position is not data. Once you pivot, sort, filter or merge, rows can move, and nothing records 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). 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. A PivotTable aggregates by default, summing numeric fields and counting everything else. The numbers in the output are new numbers computed from your data, not your data rearranged. pivot_wider() and pandas' pivot() move existing values into new columns and change nothing else. The quickest check is to count: a 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 what you wanted, but it is a decision about which unit you are analyzing, not a formatting step.