Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 36 ยท Data Management

Reshaping Data: Wide and Long in Stata

Learn the reshape command in Stata: wide versus long layouts, the i and j variables, stub names, reshape long, reshape wide, common errors and when to use collapse and expand.

The same information can be arranged in two shapes. In wide form, each subject has one row and repeated measurements sit in separate columns (income2020, income2021, income2022). In long form, each subject has several rows, one per measurement, with a column that says which year it is. Most Stata analysis commands, especially panel-data and graphing commands, want long data. Survey exports are often wide. reshape converts between them.

Seeing the two layouts

Wide:

id income2020 income2021 income2022
1 40 43 47
2 55 52 58

Long:

id year income
1 2020 40
1 2021 43
1 2022 47
2 2020 55
2 2021 52
2 2022 58
flowchart LR
    W["Wide<br/>one row per id<br/>income2020 income2021 ..."] -->|"reshape long income, i(id) j(year)"| L["Long<br/>one row per id and year"]
    L -->|"reshape wide income, i(id) j(year)"| W

reshape long stacks the columns; reshape wide spreads them out again.

Three ideas appear in the command:

  • The stub is the shared beginning of the variable names (income).
  • i() names the variable(s) that identify one subject (id).
  • j() names the variable that holds the suffix (year): it is created by reshape long and consumed by reshape wide.

reshape long

STATA
reshape long income, i(id) j(year)

Stata finds every variable that starts with income and ends with a number, makes one row per id and suffix, and stores the suffix in year. You can reshape several stubs at once, as long as each has the same suffixes:

STATA
reshape long income hours, i(id) j(year)

If the suffix is text rather than a number (income_men, income_women), add the string option: j(group) string. Variables that do not start with a listed stub (such as birthyear) are treated as constant within id and copied to every row.

reshape wide

STATA
reshape wide income hours, i(id) j(year)

This creates income2020, income2021, ... and one row per id. It requires that the combination of i and j is unique and that any other variable is constant within id. Otherwise Stata stops with an error that points to the offending observations. List them with reshape error.

Use wide form when a command compares measurements across columns (for instance computing income2022 - income2020), or for a paired test.

Remembering the reshape

After a reshape long, Stata remembers the shape and the options. This lets you return with a short command:

STATA
reshape long income, i(id) j(year)
* ... work on long data ...
reshape wide                  // reverses it using the saved settings

Do not rely on this memory across sessions. Always write the full command in do-files.

Typical problems

  1. Names do not follow the stub+suffix pattern. For inc_2020 use the stub inc_; for 2020_income rename first (rename (*_income) (income*), or other pattern renames).
  2. Mixed stubs and suffixes. If a variable like income_total sits next to income2020, reshape may try to treat total as a suffix. Rename it first or list the exact variables with a j() pattern.
  3. Duplicate i combinations when reshaping wide: duplicates report id year finds them.
  4. A variable that changes within i blocks reshape wide. Either drop it or include it as a stub.
  5. Forgetting to check afterwards. Use isid id year (long) or isid id (wide), and compare counts: wide rows times years equals long rows (unless some are missing).
STATA
collapse (mean) income (count) n = id, by(region year)   // make a smaller dataset of statistics
expand 3                                                   // repeat each observation three times
expand n_visits                                            // repeat by a variable's value
contract region, freq(n)                                   // dataset of unique values and counts
fillin id year                                             // add all missing id-year combinations
  • collapse replaces the dataset with group statistics (mean, sum, count, median, sd, max). Save first or preserve/restore.
  • expand is the opposite of removing duplicates; it builds one row per event from a count.
  • fillin makes a balanced panel by creating the missing combinations, adding _fillin = 1 for new rows.
  • xpose, clear transposes the whole dataset (rows and columns), which is rare and not a substitute for reshape.

Stand-in: reshape long and wide

The Python code below reshapes a small wide table to long form and back, with the same i/j/stub logic. The result check at the end is the one you should write in a do-file.

Python Stand-in: reshape long, then wide

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Python Stand-in: why long data is easier to analyze

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

In wide form you would need a separate command for each year column; in long form, bysort year: summarize income or collapse (mean) income, by(year) does all years at once.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. In reshape long income, i(id) j(year), what does j(year) do?

    j() names the variable that holds the part of the name after the stub.
  2. 2. Which shape do most panel-data commands expect?

    Panel and graphing commands want one row per subject and time period.
  3. 3. reshape wide fails because i and j do not uniquely identify rows. What to do?

    Each id-year combination must be unique; locate duplicates and resolve them.
  4. 4. Which command makes a smaller dataset of group means?

    collapse (mean) var, by(group) replaces the data with group statistics.

Summary

  • Wide data has one row per subject; long data has one row per subject and period.
  • reshape long stub, i(id) j(time) stacks columns; reshape wide spreads them; i() identifies subjects, j() holds the suffix.
  • Variable names must follow the stub+suffix pattern; fix names and duplicates before reshaping.
  • collapse, expand, contract and fillin change the dataset's size and balance.
  • Check with isid and row counts after every reshape.

Next: Summary Statistics with summarize