Reshaping Hierarchical Data in Stata: Special Cases
The reshape command is a powerful tool that should be in every serious Stata user’s toolbox. If you’re not familiar with it, start by reading Hierarchical Data Concepts from SSCC’s Data Wrangling in Stata workshop. It will introduce you to the vocabulary we’ll use to describe the hierarchcial data to be reshaped. Then read the Reshape section from Data Wrangling in Stata.
In this article we’ll move beyond those basics to talk about using reshape in situations that are less common, but that you’re likely to see eventually.
Using Strings as Identifiers
Suppose you had data on students who have taken the SAT, including their SAT section scores. The SAT has three sections, which we’ll call verbal, math and writing (officially they’re now called Critical Reading, Mathematics, and Writing). Each student takes all three sections.
This is hierarchical data in long form. A level one unit in this data set is a test section, and a level two unit is a student. Test sections are nested under students. The level one identifier is section and the level two identifier is id.
To reshape this data set to wide form, you need to specify the level two identifier in i() and the level one identifier in j(). However, the level one identifier in this data set is a string instead of a number. That’s not actually a problem: just add the string option to the reshape command and Stata will be happy to work use a string variable as an identifier.
The only complication is the resulting variable names. Stata will append the values of the j() variable to the variable names as usual, so you’ll get scoremath, scoreverbal, and scorewriting. To make the variable names readable after reshaping, add an underscore to the score variable first:
will add an underscore to all the variable names in the varlist you specify, which could include things like x* or x-z.
rename *_ *
will remove the underscore from any variable name that ends with one.
Handling Non-standard Variable Names
By default, reshape looks for the level 1 identifiers, j(), at the end of the variable names. Bu they could be elsewhere. Consider the following variation on the previous data:
These variable names put the level 1 identifier, the name of the test section, in the middle of the variable name rather than the end. To use reshape with variable names like this, you need to tell it where to find the level 1 identifiers. You do that with @:
reshapelong SAT@Score, i(id) j(section) string
(j = Math Verbal Writing)
Data Wide -> Long
-----------------------------------------------------------------------------
Number of observations 3 -> 9
Number of variables 4 -> 3
j variable (3 values) -> section
xij variables:
SATMathScore SATVerbalScore SATWritingScore-> SATScore
-----------------------------------------------------------------------------
This tells Stata to look for variables that start with SAT and end with Score, and then the j variable, section, is everything in between.
Working With More Than Two Levels
If you have more than two levels things get more complicated at the conceptual level, but the Stata code remains the same, except that you may have to apply the reshape command multiple times. Suppose you had data on two schools, each with two classes, each of which has two students (yes, these are very small schools and classes, but it makes for manageable tables). Thus a level one unit is a student, a level two unit is a class, and a level three unit is a school. The variables are score, each student’s score on a standardized test (a level 1 variable); exp, the number of years of experience the teacher has (a level 2 variable), and lunch, the percentage of students receiving free lunches (a level 3 variable).
Now that there are three levels, a row could represent a level one unit (a student), a level two unit (a class) or a level three unit (a school). The current structure, where a row represents a level one unit, is sometimes called the long-long form.
Now consider reshaping so that a row represents a level two unit (a class). The j variable is clearly student. But the i variable is not just class, because class only uniquely identifies a class within a school. To fully identify a class you need both school and class. Thus the command is:
reshapewidescore, i(school class) j(student)
(j = 1 11)
Data Long -> Wide
-----------------------------------------------------------------------------
Number of observations 8 -> 4
Number of variables 6 -> 6
j variable (2 values) student -> (dropped)
xij variables:
score -> score1 score11
-----------------------------------------------------------------------------
Now there is one row per class, the level 2 unit. This is sometimes called long-wide form. It is long with respect to classes, but wide with respect to students. To get to one row per school, the wide-wide form, requires another reshape.
But before doing so, note that while there are only two students and two schools, the identifiers are 1 and 11 rather than 1 and 2. Those values were chosen to illustrate a problem that can easily arise when you reshape data multiple times: ambiguous variable names. In this case, school111 could mean school 1, student 11, or school 11, student 1. Adding a separator like an underscore is not just for readability here; it’s essential.
This group rename command acts on all variables that start with score and then adds an underscore to the end. If you had more level one variables you’d just add them too (for example, rename score* absences* =_)
reshapewidescore* exp, i(school) j(class)
(j = 1 11)
Data Long -> Wide
-----------------------------------------------------------------------------
Number of observations 4 -> 2
Number of variables 6 -> 8
j variable (2 values) class -> (dropped)
xij variables:
score1_ -> score1_1 score1_11
score11_ -> score11_1 score11_11
exp -> exp1 exp11
-----------------------------------------------------------------------------
This wide dataset lives up to its name, so I need to adjust the linesize for the web book. You do not.
Note that score11_1 is the score of student 11 in school 1, not the score of student 1 in school 11. You could avoid the potential for confusion by changing the form of one of the identifiers so they look different from each other. But you rarely need to use data in wide-wide form except briefly for some particular purpose, like a merge that requires it.
Now let’s work our way back to one observation per student.
The only trick is how to specify the scores in the first reshape long. In reshape wide you list actual variable names, so you could use score* to specify all of them at once. But in reshape long you’re specifying stubs of variable names, and can’t use patterns like score*. With this small example data that’s not really a problem: the stubs are just score1_ and score11_. But there’s one for each student, and in real data the list would be much longer. So we’ll do some work with macros to create the list of stubs automatically. If you’re not familiar with macros and loops, you’ll want to read Stata Macros and Loops before proceeding.
First create an empty local macro scores_stubs to contain the stubs of the score variables. Doing so ensures it is empty.
local scores_stubs
Next, loop over all the score variables (again, if there were other level 1 variables you’d just add them to the list). For each variable, remove everything in the variable name after the underscore using substr() and strpos(), and add the result to scores_stubs. If you’re not familiar with substr() and strpos(), read Working with Text Data (Strings) in Stata.
We’re back to one row per class, or long-wide form. Remove the underscores from the variable names for neatness, and so they don’t get in the way of the final reshape long:
(j = 1 11)
Data Wide -> Long
-----------------------------------------------------------------------------
Number of observations 4 -> 8
Number of variables 6 -> 6
j variable (2 values) -> id
xij variables:
score1 score11 -> score
-----------------------------------------------------------------------------
Now we’re back where we started: one row per level 1 unit (a student), or long-long form.