Data cleaning in Stata - Reshaping data
The data cleaning series is going to teach you the most fundamental commands and techniques to prepare data in Stata for statistical analysis.
The series is going to cover the following topics:
- Converting string to numerical
- Dealing with missings and duplicates
- Applying variable and value labels
- Reshaping between long and wide format
- Merging multiple datasets
Data from online sources often comes in the wide format. The long format, however, is generally considered a cleaner way to represent repeated measurements and is also required for certain analysis in Stata. This session teaches you how to reshape data between the wide and long format.
By the end of this session, you will know how to:
- differentiate between long vs wide data formats
- reshape data from wide to long
- reshape data with one and multiple identifiers
- reshape data from long to wide
- reshape data with string suffixes
More information on how the session is run
How to work together in the Zoom sessions:- Please turn on your microphone and webcam.
- One shares the screen and the other requests remote control.
- Take turns on who types for each exercise.
- Ask the trainer if you struggle to find a solution.
- Use the help command. To get help with a specific command type help "command name"
- Search online. The statalist.org forum is usually the most useful resource.
Reshaping from wide to long format
Wide and long data formats
Before we explore the commands to reshape a dataset from one orientation to the other, let us first look at what the same data looks like in the wide and long orientation.
Below is a slightly modified version of the reshape2 dataset from the Stata server in wide format. The dataset has two variables, an id variable and three income variables for the years 1980 - 1982. This orientation is called wide, because the repeated measurement of the income variable over time, is spread across the columns from left to right, that is, there is a separate variable for each year. In this format each row represents a single observation.
+----------------------------+
| id inc80 inc81 inc82 |
|----------------------------|
1. | 1 5000 5500 6000 |
2. | 2 2000 2200 3300 |
3. | 3 3000 2000 1000 |
4. | 4 2400 2500 2400 |
+----------------------------+
Below you can see what the same data would look like in long format. The different times of measurement of the income variable are no longer spread out across separate variables. There is a new variable year, that indicates the year of the measurement. Consequently, the dataset appears longer in shape. The key to understanding data in the long format is that repeated measurements of the same individual, or country in this case, are spread out across rows. As the id variable indicates there are now three rows per country - one row for each year.
+------------------+
| id year inc |
|------------------|
1. | 1 80 5000 |
2. | 1 81 5500 |
3. | 1 82 6000 |
4. | 2 80 2000 |
5. | 2 81 2200 |
|------------------|
6. | 2 82 3300 |
7. | 3 80 3000 |
8. | 3 81 2000 |
9. | 3 82 1000 |
10. | 4 80 2400 |
|------------------|
11. | 4 81 2500 |
12. | 4 82 2400 |
+------------------+
Setting up our practice dataset
We are now going to set up a practice dataset based on the reshape2 dataset from the Stata server.
As you can see the dataset comes with an id column that does not provide a unique value for each observation. In order to identify each observation, you have to take into account the values from the id and sex variables.
. webuse reshape2, clear
list
+----------------------------------+
| id sex inc80 inc81 inc82 |
|----------------------------------|
1. | 1 0 5000 5500 6000 |
2. | 2 1 2000 2200 3300 |
3. | 3 0 3000 2000 1000 |
4. | 2 0 2400 2500 2400 |
+----------------------------------+
We will cover reshaping a dataset where cases are defined by values from more than one variable later. To start with a simpler example, where a single variable defines each observation, we will first replace the last value in the id variable to 4. Now we only need the id variable in order to uniquely identify each observation, since each case/row has a unique value for the id variable.
. replace id=4 in 4
(1 real change made)
. list
+----------------------------------+
| id sex inc80 inc81 inc82 |
|----------------------------------|
1. | 1 0 5000 5500 6000 |
2. | 2 1 2000 2200 3300 |
3. | 3 0 3000 2000 1000 |
4. | 4 0 2400 2500 2400 |
+----------------------------------+
Reshaping from wide to long
The reshape command takes two arguments and has two options that have to be specified. Below is an example of how to reshape the income data to the long format.
reshape long inc, i(id) j(year)
1st argument: long/wide
The first argument specifies whether the data should be reshaped to long or wide format. In this case, we want to reshape to long.
2nd argument: stub
The second argument specifies the stub for the measurement that is repeatedly measured over time. The income variables use the inc stub for each of the three variables that measure the income in 1980, 1981 and 1982.
1st option: i()
With the first option i(), you have to pass the variable that identifies the different observations in the dataset, which is the id variable.
2nd option: j()
The second option j() specifies the name for the new categorical variable that we want to create. Since the income variable is measured annually from 1980 - 1982, we call this variable year.
Let us run the reshape command and see the output it produces.
The reshape command generates a table that summarizes how the dataset has been restructured. The first row shows that the data have been reshaped from wide to long format. The table also lists how the reshape has increased the number of observations from 4 to 12 and has reduced the number of variables from 5 to 4. The new j variable years has been created with 3 values (80, 81, 82) and the values from the three income variables inc80, inc81 and inc82 have been combined in a single variable inc.
. reshape long inc, i(id) j(year)
(note: j = 80 81 82)
Data wide -> long
-----------------------------------------------------------------------------
Number of obs. 4 -> 12
Number of variables 5 -> 4
j variable (3 values) -> year
xij variables:
inc80 inc81 inc82 -> inc
-----------------------------------------------------------------------------
If we now list the dataset, we see the changes in the actual dataset. A new categorical variable year has been created. The income from each year is now represented across three rows per observation. The values in the sex column are being repeated for the three rows belonging to each respondent.
. list
+------------------------+
| id year sex inc |
|------------------------|
1. | 1 80 0 5000 |
2. | 1 81 0 5500 |
3. | 1 82 0 6000 |
4. | 2 80 1 2000 |
5. | 2 81 1 2200 |
|------------------------|
6. | 2 82 1 3300 |
7. | 3 80 0 3000 |
8. | 3 81 0 2000 |
9. | 3 82 0 1000 |
10. | 4 80 0 2400 |
|------------------------|
11. | 4 81 0 2500 |
12. | 4 82 0 2400 |
+------------------------+
Exercise 1: Reshaping from wide to long
- Import the bmi_reshape dataset with the below command
import excel using "https://github.com/mwiemers/datasets/raw/main/reshape_bmi.xlsx", firstrow clear
- List the data. The data measures the body mass index (BMI) for 5 individuals for the year 2000, 2010 and 2015 along with a few other sociological variables.
- Reshape the dataset to long format. Answer the following questions in order to select the correct values for the arguments and options of the reshape command. a. Which columns represent a repeated measurement? b. What is the stub these columns use? c. Which column uniquely identifies the observations?
- List the data after having reshaped them.
Reshaping with more than one identifier variable
The original reshape2 dataset requires values from the id and sex variable in order to identify the observations in the dataset. The value 2 repeats in the 4th row of id variable. However, since the sex variable has different values for the second and fourth row, we can uniquely identify each observation by looking at both the values from the id and sex variables.
. webuse reshape2, clear
. list
+----------------------------------+
| id sex inc80 inc81 inc82 |
|----------------------------------|
1. | 1 0 5000 5500 6000 |
2. | 2 1 2000 2200 3300 |
3. | 3 0 3000 2000 1000 |
4. | 2 0 2400 2500 2400 |
+----------------------------------+
If we were to reshape the dataset only providing the id variable to identify observations with the i() option, the reshape command would return an error notifying us, that the id variable does not uniquely identify the observations in the dataset.
. reshape long inc, i(id) j(year)
(note: j = 80 81 82)
variable id does not uniquely identify the observations
Your data are currently wide. You are performing a reshape long. You specified i(id) and j(year). In the current wide form, variable id
should uniquely identify the observations. Remember this picture:
long wide
+---------------+ +------------------+
| i j a b | | i a1 a2 b1 b2 |
|---------------| <--- reshape ---> |------------------|
| 1 1 1 2 | | 1 1 3 2 4 |
| 1 2 3 4 | | 2 5 7 6 8 |
| 2 1 5 6 | +------------------+
| 2 2 7 8 |
+---------------+
Type reshape error for a list of the problem observations.
r(9);
We have to provide both variables, the id and sex variable to the i() option in order for the reshape command being able to identify the observations in the dataset.
The summary table again gives us an overview of how the reshape has change the shape of the dataset.
. reshape long inc, i(id sex) j(year)
(note: j = 80 81 82)
Data wide -> long
-----------------------------------------------------------------------------
Number of obs. 4 -> 12
Number of variables 5 -> 4
j variable (3 values) -> year
xij variables:
inc80 inc81 inc82 -> inc
-----------------------------------------------------------------------------
The data has been correctly reshaped to long.
. list
+------------------------+
| id sex year inc |
|------------------------|
1. | 1 0 80 5000 |
2. | 1 0 81 5500 |
3. | 1 0 82 6000 |
4. | 2 0 80 2400 |
5. | 2 0 81 2500 |
|------------------------|
6. | 2 0 82 2400 |
7. | 2 1 80 2000 |
8. | 2 1 81 2200 |
9. | 2 1 82 3300 |
10. | 3 0 80 3000 |
|------------------------|
11. | 3 0 81 2000 |
12. | 3 0 82 1000 |
+------------------------+
Reshaping from long to wide
If we want to reshape the same dataset from long to wide, we only have to change the first argument of the reshape command from long to wide.
The second argument provides the name of the variable that is to be repeated for each year, which is the inc variable. The first option i() specifies variables to identify each observation - id and sex. With the second option j() we provide the categorical variable year which codes for the repeated measures of the inc variable.
. reshape wide inc, i(id sex) j(year)
(note: j = 80 81 82)
Data long -> wide
-----------------------------------------------------------------------------
Number of obs. 12 -> 4
Number of variables 4 -> 5
j variable (3 values) year -> (dropped)
xij variables:
inc -> inc80 inc81 inc82
-----------------------------------------------------------------------------
. list
+----------------------------------+
| id sex inc80 inc81 inc82 |
|----------------------------------|
1. | 1 0 5000 5500 6000 |
2. | 2 0 2400 2500 2400 |
3. | 2 1 2000 2200 3300 |
4. | 3 0 3000 2000 1000 |
+----------------------------------+
Exercise 2: Multiple identifiers and reshaping from long to wide
- Import the bmi_reshape2 dataset with the below command
import excel using "https://github.com/mwiemers/datasets/raw/main/reshape_bmi2.xlsx", firstrow clear
- List the data.
- Reshape the dataset to long format. Answer the following questions in order to select the correct values for the arguments and options of the reshape command. a. Which columns represent a repeated measurement? b. What is the common stub these columns use? c. Which columns uniquely identify the observations?
- List the data after having reshaped them.
- Reshape them back to the original wide format and list the data.
Chosing between wide or long format?
The long format is generally considered the cleaner way to represent data for data analysis purposes and many statistical software packages and also Stata require data to be in long format to analyze repeated measures data. One of the few statistcal softwares that actually requires repeated measures data to be in the wide format is SPSS.
Exercise 3: Reshaping with string suffixes
Sometimes data uses string values as the suffix. Have a look at the example below.
In order to reshape data that uses string values as a suffix (mother, father, son1, son2, daughter1, daughter2), you will have to add the string option.
. import delim using "https://raw.githubusercontent.com/mwiemers/datasets/main/reshape_str.csv", clear
(7 vars, 3 obs)
. list, ab(12)
+---------------------------------------------------------------------------------+
| famid incmother incfather incson1 incson2 incdaughter1 incdaughter2 |
|---------------------------------------------------------------------------------|
1. | 1 20000 25000 24000 . 23000 . |
2. | 2 18000 28000 38000 42000 . . |
3. | 3 45000 38000 28000 . 55000 . |
+---------------------------------------------------------------------------------+
- Import the reshape_str.csv file using the url "https://raw.githubusercontent.com/mwiemers/datasets/main/reshape_str.csv".
- Reshape the data from wide to long format.
- List the data to verify that the reshape worked as intended.
Final task: Please give us your feedback!
Upon completing the survey, you will receive the link to the solution file, to check how your commands compares to the sample solution.
In order to adapt our training to your needs and provide the most valuable learning experience for you, we depend on your feedack.
We would be grateful if you could take 1 min before the end of the workshop to get your feedback!