Long vs wide data: reshaping repeated measures

Long data has one row per measurement and wide data has one row per subject. Which one you need depends on the analysis you run next, and moving between them in base R is one function call, reshape(), as long as you check the row count afterwards. This post does it on R’s ChickWeight data and runs the two analyses that disagree about layout.

The short version: a repeated-measures ANOVA works from a complete wide row for each subject, a mixed model works from the long table, and plots and most modelling formulas want long. Reshaping is part of cleaning data before a test, and the quiet danger is the empty cells it exposes.

What long and wide mean

Imagine a study where each participant is measured on four visits. In wide format the table has one row per participant and four outcome columns, one per visit: score.1, score.2, score.3, score.4. Each row is a complete record of one person, which is how spreadsheets and case report forms naturally grow.

In long format the same table has four rows per participant. One column holds the participant id, one holds the visit, and one holds the score. The visit has stopped being part of a column name and has become a value in a column. Wickham (2014) calls this arrangement tidy: each variable (id, visit, score) is a column and each observation is a row: each variable (id, visit, score) is a column and each observation is a row.

Neither layout is more correct. Wide is easier to read and to type into. Long is what most statistical modelling functions want, because a model needs the outcome in one column and the time or condition in another so it can treat time as a predictor. The R documentation for reshape() makes the same split: wide-to-long is the common direction because most univariate modelling functions expect long data, while long-to-wide is mainly for multivariate analysis that expects a matrix of measurements.

The same data in both layouts

R’s ChickWeight dataset follows 50 chicks, weighed on up to 12 days between day 0 and day 21 after hatching (Crowder and Hand, 1990). As shipped it is long: 578 rows and four columns (weight, Time, Chick, Diet), one row per weighing. Forty-five chicks have all 12 weighings. Five chicks have fewer, so 22 of the 600 chick-by-day cells have no weight.

Reshaping ChickWeight to wide takes one base R call. The id column is converted from R’s ordered factor to plain text first. ChickWeight orders that factor by diet and final weight, not by chick number, so converting it to text removes that hidden ordering before any merge.

cw <- as.data.frame(ChickWeight)
cw$Chick <- as.character(cw$Chick)

wide <- reshape(cw[, c("Chick", "Time", "weight")],
                idvar = "Chick", timevar = "Time",
                direction = "wide")
dim(wide)   # 50 rows, 13 columns: Chick plus weight.0 ... weight.21

The wide table has 50 rows, one per chick, and 13 columns: the id plus one weight column per day, named weight.0, weight.2 and so on up to weight.21. Reshape built those names from the value column and the time value joined by a dot. The wide table now shows the problem the long one hid: 22 cells are NA, with the gaps growing late in the study (five chicks lack the day-21 weight and one lacks day 4 onwards).

Converting the wide table back to long is the same call in the other direction. Give it every argument explicitly rather than relying on the settings reshape stores on the object.

long <- reshape(wide, direction = "long",
                varying = names(wide)[-1], v.names = "weight",
                timevar = "Time",
                times = c(0, 2, 4, 6, 8, 10, 12, 14, 16, 18, 20, 21),
                idvar = "Chick")
nrow(long)                  # 600: every chick-by-day cell, empty ones included
nrow(long[!is.na(long$weight), ])   # 578: the original rows

The wide-to-long round trip has one trap. The long table made from a wide one has 600 rows, not 578, because every empty cell comes back as a row with an NA weight. Drop those rows with long[!is.na(long$weight), ] and the original 578 return. Whether the NA rows should exist is not a formatting question. It decides what the analysis does with those chicks.

Reading the reshape arguments

Four arguments carry the whole operation. They are worth knowing by name, because a wrong one produces a table of the right shape with the wrong content.

• idvar names the column that identifies the subject. Long-to-wide spreads rows that share an id onto one line.
• timevar names the column whose values become the new column names (long to wide) or the column that receives them (wide to long).
• v.names names the measurement column or columns.
• varying lists the existing wide columns that hold the repeated measurements, in the order that matches times.

If a subject and occasion pair appears twice in the long data, the wide table cannot hold both values. Base R’s reshape() warns that multiple rows match and takes the first, so a duplicated visit is silently reduced to one value unless you read the warning. A check that every id-and-time pair is unique, before reshaping, costs one line and is part of the data cleaning checklist.

Packages such as tidyr offer pivot_longer() and pivot_wider() for the same job with friendlier argument names. They are a convenience and give the same rearrangement.

Which analysis expects which layout

The layout question is really a question about what each method treats as the unit of analysis.

A repeated-measures ANOVA compares the occasions using one row of measurements per subject, so a subject with an empty cell has nothing to contribute and is removed whole. A mixed model needs a long table because its formula names one outcome column, one id column for the random effect (Bates and others, 2015) and the occasion as a predictor. Each available row counts, so a subject missing one visit still contributes every visit it has (Laird and Ware, 1982). The estimate is unbiased only if the missing visits are missing at random given the observed data.

Reshaping and missing data are therefore the same topic here. The long table is not tidier about missing values, it just records them as absent rows instead of as marked cells. Going long does not impute anything.

Worked example: the two layouts, two answers

The engine ran both analyses on ChickWeight. The linear mixed model used the long table with all 578 weighings of all 50 chicks. It gave each chick its own intercept and its own growth slope, with day as a number, so the fixed effect is average weight gain per day. The average gain was 8.453 grams per day (95% confidence interval 7.366 to 9.54), with an F statistic of 244.2 on 1 and 48.74 degrees of freedom (p below 0.001).

The chicks differ. The between-chick standard deviation is 11.85 grams in the starting weight and 3.761 grams per day in the growth rate, against a residual standard deviation of 12.79 grams. The assumption check is mixed: the residuals fail a Shapiro-Wilk normality test, and the engine flags that there is no drop-in non-parametric alternative for this model. Its advice is to read the p value with some caution, a concern the engine notes that 578 observations soften. The chick-level random intercepts and slopes show no sign of non-normality.

The repeated-measures ANOVA needs the complete wide layout, so the engine kept only the chicks weighed on every day: 45 chicks, 540 weighings. The remaining five chicks, with 38 weighings between them, contribute nothing. With day as a 12-level factor and the Greenhouse-Geisser correction (degrees of freedom 1.222 and 53.75), the F statistic is 231.6 (p below 0.001) and generalized eta-squared is 0.7108 (95% CI 0.6707 to 0.7416). The correction matters because weights fan out over time; the sphericity guide explains why.

Do not compare 244.2 with 231.6. The two models ask different questions: one tests a straight-line trend in every weighing, the other tests whether the 12 day means differ among complete chicks. What the pair shows is the layout cost. Wide plus classical ANOVA lost 5 of 50 chicks (10%) and 38 of 578 weighings. Long plus mixed model lost nothing. The full comparison of the two methods is in the guide to repeated-measures ANOVA versus mixed models.

Mistakes to avoid when you reshape

• Reshaping before checking ids. A mistyped or duplicated id turns into a phantom subject in wide format, or a merged one. Count unique ids first.
• Treating wide-format NA as “not measured”. An empty cell can mean the subject died, dropped out, or that nobody entered the value. The reason matters for the analysis, so record it before it vanishes in the long table.
• Forgetting that the day lives in a column name. The day in weight.10 is part of a name. If you give reshape() v.names but no times, it numbers the occasions 1 to 12 instead of using days 0 to 21, so pass times yourself and check the values. If your tool returns the day as text, convert it, because text sorts day 10 before day 2.
• Trusting the row count. After long-to-wide, rows should equal the number of subjects. After wide-to-long, rows should equal subjects times occasions, minus any NA rows you drop. A different count means the reshape did not do what you meant.
• Ignoring independence. Rows in a long table from one subject are not independent observations. Putting them in long format does not fix that; a model with a subject-level term does. See the independence assumption.

For a general routine to follow before any test, including how to log each transformation, see the guide to preparing data for statistics. The method reference pages describe the engine’s mixed model and repeated-measures ANOVA.

The rule to keep

Store and clean in long format, convert to wide only for the analysis or table that needs it, and count rows after every conversion. The chick results here illustrate data layout in one research dataset; they are not a study of chick nutrition.

# Results

- Test: Linear mixed-effects model (random intercept and slope)
- n: 578
- statistic: 244.2
- p-value: < 0.001
- effect size: NA
- 95% CI (Time): [7.366, 9.54]
- seed: 20261110

## Assumption checks
- normality (Shapiro-Wilk): passed=FALSE (evidence of non-normality)
- random-intercept normality (Shapiro-Wilk on BLUPs): passed=TRUE (no evidence against normality)
- random-slope normality (Shapiro-Wilk on BLUPs): passed=TRUE (no evidence against normality)

## Term tests
| Term | Test | Statistic | df | Residual df | p-value |
|---|---|---|---|---|---|
| Time | F | 244.2 | 1 | 48.74 | < 0.001 |

## Random effects
- degrees of freedom: Kenward-Roger
- cluster sizes: 2 to 12 rows (median 12); 0 cluster(s) with a single row
- predictors: Time (within cluster)
| Group | Term | With | Variance | SD or correlation | 95% CI |
|---|---|---|---|---|---|
| Chick | (Intercept) | - | 140.5 | 11.85 | - |
| Chick | Time | - | 14.14 | 3.761 | - |
| Chick | (Intercept) | Time | - | -0.9508 | - |
| Residual | - | - | 163.5 | 12.79 | - |
- variance component intervals: not computed for a random-slope model (too costly to profile on every run)

## Power
- Power is not calculated for this test.

# Celeus - reproduce.R  (run from this package folder; bundled data in data/)
# Test: Linear mixed-effects model (random intercept and slope)  |  engine 0.53.2  |  catalog c40-dc08
# Research context: not provided for this run.
# Reproduces the recorded result deterministically.

set.seed(20261110)
.data_path <- "data/long-vs-wide-data.csv"
if (!file.exists(.data_path)) stop("Run reproduce.R from the package folder (the bundled data is in data/).")
data <- readr::read_csv(.data_path, show_col_types = FALSE)  # needs the readr package
clean_cells <- function (df) 
{
    ctrl_collapse <- "[\r\n\t\v\f]+"
    zero_width_chars <- intToUtf8(c(8203L, 65279L), multiple = TRUE)
    uni_space_chars <- intToUtf8(c(160L, 8239L, 8199L), multiple = TRUE)
    clean_text <- function(x) {
        out <- x
        for (ch in zero_width_chars) out <- gsub(ch, "", out, 
            fixed = TRUE)
        out <- gsub(ctrl_collapse, " ", out)
        for (ch in uni_space_chars) out <- gsub(ch, " ", out, 
            fixed = TRUE)
        trimws(gsub(" +", " ", out))
    }
    changed_cols <- character(0)
    cells_cleaned <- 0L
    retyped <- character(0)
    for (j in seq_along(df)) {
        col <- df[[j]]
        if (!is.character(col)) 
            next
        cleaned <- clean_text(col)
        n_changed <- sum(!is.na(col) & !is.na(cleaned) & col != 
            cleaned, na.rm = TRUE)
        if (n_changed > 0L) {
            changed_cols <- c(changed_cols, names(df)[j])
            cells_cleaned <- cells_cleaned + n_changed
        }
        df[[j]] <- cleaned
        non_blank <- cleaned[!is.na(cleaned) & nzchar(cleaned)]
        if (length(non_blank) && all(grepl("^[+-]?(0|[1-9][0-9]*)([.][0-9]+)?$", 
            non_blank))) {
            parsed <- suppressWarnings(as.numeric(cleaned))
            was_blank <- is.na(cleaned) | !nzchar(cleaned)
            lost <- (is.na(parsed) | is.infinite(parsed)) & !was_blank
            if (!any(lost)) {
                finite <- parsed[!is.na(parsed)]
                all_whole <- all(finite == floor(finite))
                in_range <- all(abs(finite) <= .Machine$integer.max)
                df[[j]] <- if (all_whole && in_range) 
                  as.integer(parsed)
                else parsed
                retyped <- c(retyped, names(df)[j])
            }
        }
    }
    names(df) <- clean_text(names(df))
    if (length(changed_cols)) {
        attr(df, "ingest_clean_columns") <- changed_cols
        attr(df, "ingest_clean_cells_cleaned") <- cells_cleaned
    }
    if (length(retyped)) 
        attr(df, "ingest_clean_retyped") <- retyped
    df
}
data <- clean_cells(data)
# Complete-case analysis: drop rows missing any analysis value.
.acols <- c("weight", "Time", "Chick")
data <- data[stats::complete.cases(data[, .acols, drop = FALSE]), , drop = FALSE]
result <- { .full <- lme4::lmer(`weight` ~ `Time` + (1 + `Time` | `Chick`), data = data, REML = TRUE); .tt <- { .X <- lme4::getME(.full, "X"); .asg <- attr(.X, "assign"); .I <- diag(ncol(.X)); t(vapply(sort(unique(.asg[.asg > 0L])), function(j) unlist(pbkrtest::KRmodcomp(.full, .I[.asg == j, , drop = FALSE])$test["Ftest", c("stat", "ndf", "ddf", "p.value")]), numeric(4L))) }; .p <- .tt[1L, "p.value"]; .ct <- { .V <- pbkrtest::vcovAdj(.full); .V0 <- as.matrix(stats::vcov(.full)); .L <- diag(ncol(.V0)); .b <- lme4::fixef(.full); .se <- sqrt(diag(as.matrix(.V))); .df <- vapply(seq_len(ncol(.V0)), function(i) pbkrtest::Lb_ddf(L = .L[i, , drop = FALSE], V0 = .V0, Vadj = .V), numeric(1L)); .tv <- .b / .se; .q <- stats::qt(1 - (1 - 0.95) / 2, .df); cbind(Estimate = .b, `Std. Error` = .se, df = .df, `t value` = .tv, `Pr(>|t|)` = 2 * stats::pt(abs(.tv), .df, lower.tail = FALSE), conf.low = .b - .q * .se, conf.high = .b + .q * .se) }; list(model = summary(.full), p_value = unname(.p), estimate = unname(.ct[2L, "Estimate"]), conf_int = unname(.ct[2L, c("conf.low", "conf.high")]), coefficients = .ct, term_tests = .tt) }
print(result)