Data cleaning in Stata - Merging 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
In this practical session, you will learn about:
- Long vs wide data formats
- Reshaping data from wide to long
- Reshaping data from long to wide
- Merging mulitple datasets into one
What to do when getting stuck:
- 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.
In this session you will learn about how to identify whether your data is in the long or
wide orientation and how to change between the long and wide orientation using
the reshape command.
We will go through an example of how to create subsets of the gapminder dataset for each
subject (GDP, population and life expectancy), reshape each subset to the long format
and then merge all subsets. You will also learn how to do this more efficiently using
just the reshape command.
In the exercises you will apply the same steps independently to the weo_data dataset. You will create subsets of the weo_data dataset for three different subjects (GDP per capita, unemployment rate and the general government structural balance), reshape each subset to the long format, store it in a separate file and merge all three files into a single dataset.
Practicing Reshaping
Before we go into reshaping the gapminder and the weo_data dataset, let us start with a simple
dataset to familiarize ourselves with the reshape
command in Stata.
The reshape2 dataset can be downloaded from the Stata server using the following command.
webuse reshape2, clearLet us have a look at the data
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 |
+----------------------------------+
The different rows represent each a single participant in the survey. The variables inc80 - inc82 indicate that each participates income was measured for the years 1980 - 1982. These variables represent a repeating measurement of income. This data is in the wide orientation, since the different levels of the repeated measurement year are represented through different columns.
Let us now reshape this data from the current wide format to the long format.
Here is how we use the reshape command to reshape the income
data from wide to long.
reshape long inc, i(id sex) j(year)
Following reshape, we add long and inc.
- With
longwe specify that the data should be reshaped to the long format. - With
incwe specify the stubname that is used at the beginning of the repeating variables. These are the columns that contain the dependent variable!
The second part of the command are the options following the commma. Here we use i(id sex)
and j(year)
- With
i(id sex)we specify which variables together identify a unique observation. Since the value 2 is occuring twice in the id column, Stata needs the values from id and sex in combination to identify a unique observation. - With
j(year)we specify the name to be used for the variable that represents the levels of the repeating measurement. The levels are 80, 81, 82. By usingj(year), these will be represented in a variable named year.
This command will reshape the income data to look like this. The repeated measurements of income are now represented in different rows, where each row represents a different year, as indicated by the year column. The data is now in the long orientation. The long orientation is generally considered to be a tidier way to represent data as the repeated measurement is made explicit through a separate column. When looking at the data in the wide orientation, i.e. the way it was structured before the reshape, it is not immediately obvious that each respondent, as identified by the id and sex column, is measured in 1980, 1981, and 1982.
. 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 |
+------------------------+
Exercise 1: Reshaping Football data
- Run the following command in Stata to enter the football data.
clear input id str10 team goals1 goals2 goals3 goals4 1 "Dynamic Dragons" 2 1 0 1 2 "Dynamic Dragons" 0 1 1 2 3 "Dynamic Dragons" 1 0 2 1 4 "Dynamic Dragons" 3 2 1 0 5 "Dynamic Dragons" 0 0 1 1 6 "Dynamic Dragons" 1 1 2 2 7 "Dynamic Dragons" 0 1 0 1 8 "Dynamic Dragons" 2 2 1 1 9 "Dynamic Dragons" 1 0 0 1 10 "Dynamic Dragons" 0 1 1 0 11 "Dynamic Dragons" 2 1 2 1 12 "Dynamic Dragons" 2 1 2 1 13 "Dynamic Dragons" 2 2 1 1 1 "Legendary Leprechauns" 0 0 0 0 2 "Legendary Leprechauns" 1 1 1 1 3 "Legendary Leprechauns" 2 2 2 2 4 "Legendary Leprechauns" 1 0 1 0 5 "Legendary Leprechauns" 0 1 0 1 6 "Legendary Leprechauns" 1 2 1 2 7 "Legendary Leprechauns" 2 1 2 1 8 "Legendary Leprechauns" 0 0 0 0 9 "Legendary Leprechauns" 1 1 1 1 10 "Legendary Leprechauns" 2 2 2 2 11 "Legendary Leprechauns" 1 0 1 0 12 "Legendary Leprechauns" 1 0 1 0 13 "Legendary Leprechauns" 0 0 0 0 14 "Legendary Leprechauns" 0 0 0 1 end - Reshape the data from wide to long so that it looks like below.
+--------------------------------+ | id team game goals | |--------------------------------| 1. | 1 Dynamic Dr 1 2 | 2. | 1 Dynamic Dr 2 1 | 3. | 1 Dynamic Dr 3 0 | 4. | 1 Dynamic Dr 4 1 | 5. | 1 Legendary 1 0 | |--------------------------------| 6. | 1 Legendary 2 0 | 7. | 1 Legendary 3 0 | 8. | 1 Legendary 4 0 | 9. | 2 Dynamic Dr 1 0 | 10. | 2 Dynamic Dr 2 1 | |--------------------------------| 11. | 2 Dynamic Dr 3 1 | 12. | 2 Dynamic Dr 4 2 | 13. | 2 Legendary 1 1 | 14. | 2 Legendary 2 1 | 15. | 2 Legendary 3 1 | |--------------------------------| 16. | 2 Legendary 4 1 | 17. | 3 Dynamic Dr 1 1 | 18. | 3 Dynamic Dr 2 0 | 19. | 3 Dynamic Dr 3 2 | 20. | 3 Dynamic Dr 4 1 | +--------------------------------+
Practicing Reshaping: From long back to wide
Sometimes we actually want to reshape from long to wide. We will see an example of this at the end
of this workshop. For now, let us just practice one more time with the
reshape command, by reshaping the football data back to
its
original wide format.
After having reshaped the football data the first time to the long format, it is in the below orientation.
+--------------------------------+
| id team game goals |
|--------------------------------|
1. | 1 Dynamic Dr 1 2 |
2. | 1 Dynamic Dr 2 1 |
3. | 1 Dynamic Dr 3 0 |
4. | 1 Dynamic Dr 4 1 |
5. | 1 Legendary 1 0 |
|--------------------------------|
6. | 1 Legendary 2 0 |
7. | 1 Legendary 3 0 |
8. | 1 Legendary 4 0 |
9. | 2 Dynamic Dr 1 0 |
10. | 2 Dynamic Dr 2 1 |
|--------------------------------|
11. | 2 Dynamic Dr 3 1 |
12. | 2 Dynamic Dr 4 2 |
13. | 2 Legendary 1 1 |
14. | 2 Legendary 2 1 |
15. | 2 Legendary 3 1 |
|--------------------------------|
16. | 2 Legendary 4 1 |
17. | 3 Dynamic Dr 1 1 |
18. | 3 Dynamic Dr 2 0 |
19. | 3 Dynamic Dr 3 2 |
20. | 3 Dynamic Dr 4 1 |
+--------------------------------+
To reshape the football data back to the wide orientation, all we need to do is replace long with
wide!
reshape wide goals, i(id team) j(game)
- With
widewe specify that we want to reshape the data to the wide orientation. - Following
wide, we specify the stubnamegoals. We have to use goals, since this column contains the dependent variable that has been measured repeatedly. These values will be recoded to wide. - We still use both the id and team column to identify a unique observation, which is why we
use the option
i(id team) - The j option specifies the name of the variable that contains the levels of the repeated measurement.
Exercise 2: Reshaping back to wide
- Load the reshape2 dataset.
- Reshape the data to the long format. Use the name year for the levels of the repeated measurement.
- Reshape the data back to the wide format.
Reshaping the Gapminder dataset
The gapminder dataset in its original format contains a variable that codes for the subject, that is, GDP per capita, life expectancy and population, and the actual values for each year in separate variables.
The data is in the wide format. Although year is not represented as a single column, it is a variable in the study design. In particular, it is a repeated measurement or a within-subject variable. The dependent variables GDP, life expectancy and population are measured repeatedly, within each subject (country), across a series of years.
. import excel using "https://github.com/mwiemers/datasets/raw/main/gapminder_dirty_reshape.xlsx", firstrow clear . list in 1/10+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | country contin~t subject y1952 y1957 y1962 y1967 y1972 y1977 y1982 y1987 y1992 y1997 y2002 y2007 | |------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| 1. | Afghanistan Asia gdpPercap 779.44531 820.85303 853.10071 836.19714 739.98111 786.11336 978.01144 852.39594 649.3414 635.34135 726.73405 974.58034 | 2. | Afghanistan Asia lifeExp 28.801 30.332 31.997 34.02 36.088 38.438 39.854 40.822 41.674 41.763 42.129 43.828 | 3. | Afghanistan Asia pop 8425333 9240934 10267083 11537966 13079460 14880372 12881816 13867957 16317921 22227415 25268405 31889923 | 4. | Albania Europe gdpPercap 1601.0561 1942.2842 2312.889 2760.1969 3313.4222 3533.0039 3630.8807 3738.9327 2497.4379 3193.0546 4604.2117 5937.0295 | 5. | Albania Europe lifeExp 55.23 59.28 64.82 66.22 67.69 68.93 70.42 72 71.581 72.95 75.651 76.423 | |------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| 6. | Albania Europe pop 1282697 1476505 1728137 1984060 2263554 2509048 2780097 3075321 3326498 3428038 3508512 3600523 | 7. | Algeria Africa gdpPercap 2449.0082 3013.976 2550.8169 3246.9918 4182.6638 4910.4168 5745.1602 5681.3585 5023.2166 4797.2951 5288.0404 6223.3675 | 8. | Algeria Africa lifeExp 43.077 45.685 48.303 51.407 54.518 58.014 61.368 65.799 67.744 69.152 70.994 72.301 | 9. | Algeria Africa pop 9279525 10270856 11000948 12760499 14760787 17152804 20033753 23254956 26298373 29072015 31287142 33333216 | 10. | Angola Africa gdpPercap 3520.6103 3827.9405 4269.2767 5522.7764 5473.288 3008.6474 2756.9537 2430.2083 2627.8457 2277.1409 2773.2873 4797.2313 | +------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
We want to transform the orientation of the dataset so that there is a variable year and the subjects are each presented in three separate variables as you can see below.
The reshape is supposed to put the data in the long format, when there is a single column for each variable. Repeated measurements are no longer spread out across multiple columns, where each column represents a specific level (year), but instead each row represents a different level (year) for each country.
+-----------------------------------------------------------------+
| country continent year gdpPercap lifeExp pop |
|-----------------------------------------------------------------|
1. | Afghanistan Asia 1952 779.44531 28.801 8425333 |
2. | Afghanistan Asia 1957 820.85303 30.332 9240934 |
3. | Afghanistan Asia 1962 853.10071 31.997 10267083 |
4. | Afghanistan Asia 1967 836.19714 34.02 11537966 |
5. | Afghanistan Asia 1972 739.98111 36.088 13079460 |
|-----------------------------------------------------------------|
6. | Afghanistan Asia 1977 786.11336 38.438 14880372 |
7. | Afghanistan Asia 1982 978.01144 39.854 12881816 |
8. | Afghanistan Asia 1987 852.39594 40.822 13867957 |
9. | Afghanistan Asia 1992 649.3414 41.674 16317921 |
10. | Afghanistan Asia 1997 635.34135 41.763 22227415 |
|-----------------------------------------------------------------|
11. | Afghanistan Asia 2002 726.73405 42.129 25268405 |
12. | Afghanistan Asia 2007 974.58034 43.828 31889923 |
13. | Albania Europe 1952 1601.0561 55.23 1282697 |
14. | Albania Europe 1957 1942.2842 59.28 1476505 |
15. | Albania Europe 1962 2312.889 64.82 1728137 |
|-----------------------------------------------------------------|
16. | Albania Europe 1967 2760.1969 66.22 1984060 |
17. | Albania Europe 1972 3313.4222 67.69 2263554 |
18. | Albania Europe 1977 3533.0039 68.93 2509048 |
19. | Albania Europe 1982 3630.8807 70.42 2780097 |
20. | Albania Europe 1987 3738.9327 72 3075321 |
+-----------------------------------------------------------------+
Creating a subset
In order to restructure the whole dataset from the wide to the long format, we have to create subsets of the data for each subject, reshape that subset from wide to long and in a final step merge all three subsets back together into a single dataset.
We will first create a subset for the life expectancy subject with the keep command. With the
keep command we can select those observations and
variables that we want to keep. Every other
observation and/or variable will be removed from the data.
In order to only select those observations in our dataset where the subject variable is equal to life_exp, we add an if qualifier with the expression subject==“life_exp”.
. keep if subject=="lifeExp"
(284 observations deleted)
Exercise 3: Creating a subset
We will start by practicing with the keep command.
- Load the weo_data dataset using the url “https://github.com/mwiemers/datasets/blob/main/weo_data_dirty_reshape.xlsx?raw=true”
- Use the
keepcommand to only keep the GDP per capita data. Which command can you use to quickly verify that you only have data for life expectancy without looking at each row in the data editor? - Reload the data, create a subset for the unemployment values and verify that the subset only consists of population values.
Reshaping and storing the subset in a new file
In the next step, we are going to reshape and store the subset in a new file.
In order to reshape the data to the long format with the reshape command, we need to provide following
information:
- The orientation of the new dataset, which should be long.
- The stub: The repeated measurements for the years 1952 - 2007 use the stub y
- The variables that identify a single case in the data: The countries are uniquely identified by the variable country, since there is only one row per country.
- The name for the new variable that holds the different levels of the repeated measurement, ie. the year..
The reshape command generates a long version of the data.
. reshape long y, i(country) j(year)
(note: j = 1952 1957 1962 1967 1972 1977 1982 1987 1992 1997 2002 2007)
Data wide -> long
-----------------------------------------------------------------------------
Number of obs. 142 -> 1704
Number of variables 15 -> 5
j variable (12 values) -> year
xij variables:
y1952 y1957 ... y2007 -> y
-----------------------------------------------------------------------------
. list in 1/10
+--------------------------------------------------+
| country year contin~t subject y |
|--------------------------------------------------|
1. | Afghanistan 1952 Asia lifeExp 28.801 |
2. | Afghanistan 1957 Asia lifeExp 30.332 |
3. | Afghanistan 1962 Asia lifeExp 31.997 |
4. | Afghanistan 1967 Asia lifeExp 34.02 |
5. | Afghanistan 1972 Asia lifeExp 36.088 |
|--------------------------------------------------|
6. | Afghanistan 1977 Asia lifeExp 38.438 |
7. | Afghanistan 1982 Asia lifeExp 39.854 |
8. | Afghanistan 1987 Asia lifeExp 40.822 |
9. | Afghanistan 1992 Asia lifeExp 41.674 |
10. | Afghanistan 1997 Asia lifeExp 41.763 |
+--------------------------------------------------+
We can drop the subject variable and rename the y variable to lifeExp.
. drop subject
. rename y lifeExp
. list in 1/10
+-----------------------------------------+
| country year contin~t lifeExp |
|-----------------------------------------|
1. | Afghanistan 1952 Asia 28.801 |
2. | Afghanistan 1957 Asia 30.332 |
3. | Afghanistan 1962 Asia 31.997 |
4. | Afghanistan 1967 Asia 34.02 |
5. | Afghanistan 1972 Asia 36.088 |
|-----------------------------------------|
6. | Afghanistan 1977 Asia 38.438 |
7. | Afghanistan 1982 Asia 39.854 |
8. | Afghanistan 1987 Asia 40.822 |
9. | Afghanistan 1992 Asia 41.674 |
10. | Afghanistan 1997 Asia 41.763 |
+-----------------------------------------+
Next, we are going to save the new subset under the file name gapminder_lifeExp.
. save gapminder_lifeExp, replace
(note: file gapminder_lifeExp.dta not found)
file gapminder_lifeExp.dta saved
Let us look at all commands together.
keep if subject=="lifeExp"
reshape long y, i(country) j(year)
drop subject
rename y lifeExp
save gapminder_lifeExp, replace
Exercise 4: Reshaping and storing the subset
- Load the weo_data dataset using the url “https://github.com/mwiemers/datasets/blob/main/weo_data_dirty_reshape.xlsx?raw=true”
- Create a subset for the GDP per capita data.
- Reshape the subset to the long format.
- Rename the variable holding the gdp per capita values to gdpPercap and delete the SubjectDescriptor variable.
- Store the subset under the name weo_data_gdpPercap.
Repeating the process for the remaining variables
Using preserve and
restore
In order to continue with the next subset of data, the population data, we would have to reload the original
gapminder dataset first. Instead of reloading the original gapminder dataset, we can use the preserve restore combination.
The preserve command stores a copy of the open dataset
in its current state. With the restore
command we can reload the dataset in the state it was stored in with preserve.
Therefore, we add a preserve command before we create a
subset of the data for the population. After
having saved the reshaped subset in a new file, we use restore to reload the original dataset, so
that we can proceed with creating the next subset.
We start by loading the original dataset.
. import excel using "https://github.com/mwiemers/datasets/blob/main/gapminder_dirty_reshape.xlsx?raw=true", firstrow clear
. * create subset for life_exp, reshape to long and save as new file
. preserve
. keep if subject=="lifeExp"
(284 observations deleted)
. reshape long y, i(country) j(year)
(note: j = 1952 1957 1962 1967 1972 1977 1982 1987 1992 1997 2002 2007)
Data wide -> long
-----------------------------------------------------------------------------
Number of obs. 142 -> 1704
Number of variables 15 -> 5
j variable (12 values) -> year
xij variables:
y1952 y1957 ... y2007 -> y
-----------------------------------------------------------------------------
. rename y lifeExp
. drop subject
. save gapminder_lifeExp, replace
file gapminder_lifeExp.dta saved
. restore
Next, we are going to carry out the same steps for the next subject. We simply have to replace life_exp with
pop for the keep, rename,
reshape and
save commands.
. * create subset for pop, reshape to long and save as new file
. preserve
. keep if subject=="pop"
(284 observations deleted)
. reshape long y, i(country) j(year)
(note: j = 1952 1957 1962 1967 1972 1977 1982 1987 1992 1997 2002 2007)
Data wide -> long
-----------------------------------------------------------------------------
Number of obs. 142 -> 1704
Number of variables 15 -> 5
j variable (12 values) -> year
xij variables:
y1952 y1957 ... y2007 -> y
-----------------------------------------------------------------------------
. rename y pop
. drop subject
. save gapminder_pop, replace
(note: file gapminder_pop.dta not found)
file gapminder_pop.dta saved
. restore
Finally, we run the same set of commands again for the subset of the data where the subject is gdpPercap.
. * create subset for gdpPercap and reshape to long
. preserve
. keep if subject=="gdpPercap"
(284 observations deleted)
. reshape long y, i(country) j(year)
(note: j = 1952 1957 1962 1967 1972 1977 1982 1987 1992 1997 2002 2007)
Data wide -> long
-----------------------------------------------------------------------------
Number of obs. 142 -> 1704
Number of variables 15 -> 5
j variable (12 values) -> year
xij variables:
y1952 y1957 ... y2007 -> y
-----------------------------------------------------------------------------
. rename y gdpPercap
. drop subject
. save gapminder_gdpPercap, replace
(note: file gapminder_gdpPercap.dta not found)
file gapminder_gdpPercap.dta saved
. restore
Exercise 5: Using preserve and restore with the weo_data dataset
- Update the first code chunk that you wrote to create the subset for the gdpPercap data and add the
preserveandrestorecommands at the beginning and end. - Continue with the second code chunk using
preserveandrestoreto create the subset for the population data. - Write the last code chunk to create the subset for the uenmployment rate.
Merging the separate subsets with merge
In the final step of restructuring the dataset, we are going to combine the three previously created subsets
using the merge command.
We will combine the three different datasets in such a way that the final dataset will have separate
variables for the GDP per capita, population and life expectancy data (see below). We do this by opening the
gapminder_gdpPercap.dta dataset first and then adding the pop and life_exp variables from the
gapminder_pop.dta and gapminder_lifeExp.dta datasets using the
merge command. In the last step we
save the new dataset under a new filename.
+----------------------------------------------------------------+
| country contin~t year gdpPercap lifeExp pop |
|----------------------------------------------------------------|
1. | Afghanistan Asia 1952 779.44531 28.801 8425333 |
2. | Afghanistan Asia 1957 820.85303 30.332 9240934 |
3. | Afghanistan Asia 1962 853.10071 31.997 10267083 |
4. | Afghanistan Asia 1967 836.19714 34.02 11537966 |
5. | Afghanistan Asia 1972 739.98111 36.088 13079460 |
|----------------------------------------------------------------|
6. | Afghanistan Asia 1977 786.11336 38.438 14880372 |
7. | Afghanistan Asia 1982 978.01144 39.854 12881816 |
8. | Afghanistan Asia 1987 852.39594 40.822 13867957 |
9. | Afghanistan Asia 1992 649.3414 41.674 16317921 |
10. | Afghanistan Asia 1997 635.34135 41.763 22227415 |
|----------------------------------------------------------------|
11. | Afghanistan Asia 2002 726.73405 42.129 25268405 |
12. | Afghanistan Asia 2007 974.58034 43.828 31889923 |
13. | Albania Europe 1952 1601.0561 55.23 1282697 |
14. | Albania Europe 1957 1942.2842 59.28 1476505 |
15. | Albania Europe 1962 2312.889 64.82 1728137 |
|----------------------------------------------------------------|
16. | Albania Europe 1967 2760.1969 66.22 1984060 |
17. | Albania Europe 1972 3313.4222 67.69 2263554 |
18. | Albania Europe 1977 3533.0039 68.93 2509048 |
19. | Albania Europe 1982 3630.8807 70.42 2780097 |
20. | Albania Europe 1987 3738.9327 72 3075321 |
+----------------------------------------------------------------+
Merging and appending data
There are fundamentally two ways you can combine datasets by either appending them using the
append
command or merging them using merge You use
append to add observations from another dataset
with the same variables. Appending your dataset will therefore make your dataset longer.
In the illustration below GPD per capita data from the continents Africa, Asia and Europe are being combined
using append. Another example for when to use
append would be if you collected new data for
subsequent years and would like to combine new and old data into a single dataset. In order to append two or
more datasets, they must have the same variables.
You would use merge, in contrast, in order to add
variables from another dataset containing the same
observations, that is, data from the same countries or individuals. Merging two or more datasets will make
the combined dataset wider but not longer than the original datasets. In the example we create a new dataset
gapminder_2.dta by adding the pop and life_exp variables from from the pop.dta and
life_exp.dta datasets to the gdp_pc.dta dataset.
Combining the gapminder_gdpPercap.dta, gapminder_lifeExp.dta and gapminder_pop.dta datasets
In order to combine the three subsets for the GDP per capita, life expectancy and population data, we will start by opening the gapminder_gdpPercap.dta dataset and merge it with the gapminder_lifeExp.dta dataset.
Following merge we add 1:1 as the first argument, since the data from each row in the gdp_pc.dta dataset will be merged with a single row from the gapminder_lifeExp.dta dataset.
As the next argument the merge command expects the variables that identify an observation in the datasets, which is the combination of the country and year variables.
Finally, following using we must specify the other dataset with which the gdp_pc.dta dataset should be merged, which is the life_exp.dta dataset.
. use gapminder_gdpPercap.dta, clear
. merge 1:1 country year using gapminder_lifeExp.dta
Result # of obs.
-----------------------------------------
not matched 0
matched 1,704 (_merge==3)
-----------------------------------------
If we now take a look at the data, we notice that the lifeExp and _merge variables have been
added to the dataset. Befoe we will address the newly created _merge variable, let us first recap how
the above
merge 1:1 command has combined both datasets.
The data from every country for each year in the gapminder_gdpPercap.dta dataset has been merged with the
data from every country for each year in the gapminder_lifeExp.dta dataset. The
merge command first
identifies the variables that exist in both datasets, which are the country, continent and
year variables and then adds all other variables that are unique to the gapminder_lifeExp.dta
dataset, which is the lifeExp variables.
. list in 1/10
+-------------------------------------------------------------------+
| country year contin~t gdpPercap lifeExp _merge |
|-------------------------------------------------------------------|
1. | Afghanistan 1952 Asia 779.44531 28.801 matched (3) |
2. | Afghanistan 1957 Asia 820.85303 30.332 matched (3) |
3. | Afghanistan 1962 Asia 853.10071 31.997 matched (3) |
4. | Afghanistan 1967 Asia 836.19714 34.02 matched (3) |
5. | Afghanistan 1972 Asia 739.98111 36.088 matched (3) |
|-------------------------------------------------------------------|
6. | Afghanistan 1977 Asia 786.11336 38.438 matched (3) |
7. | Afghanistan 1982 Asia 978.01144 39.854 matched (3) |
8. | Afghanistan 1987 Asia 852.39594 40.822 matched (3) |
9. | Afghanistan 1992 Asia 649.3414 41.674 matched (3) |
10. | Afghanistan 1997 Asia 635.34135 41.763 matched (3) |
+-------------------------------------------------------------------+
The _merge variable indicates whether a merge has been successful, that is, whether an observation from gdp_pc.dta could be matched with an observation in the life_exp.dta dataset based on the country and year variables.
We can use the tab command to create a table of the
_merge variable. All observations could be
matched.
. tab _merge
_merge | Freq. Percent Cum.
------------------------+-----------------------------------
matched (3) | 1,704 100.00 100.00
------------------------+-----------------------------------
Total | 1,704 100.00
Before we continue with merging the current dataset with the pop.dta dataset in order to add the pop variable, we will have to delete the _merge variable so that a new _merge variable can be created for the next merge.
. drop _merge
Now we are going to merge the existing dataset with the pop.dta dataset.
. merge 1:1 country year using gapminder_pop.dta
Result # of obs.
-----------------------------------------
not matched 0
matched 1,704 (_merge==3)
-----------------------------------------
The pop variable and another _merge have been added to the dataset.
. list in 1/10
+------------------------------------------------------------------------------+
| country year contin~t gdpPercap lifeExp pop _merge |
|------------------------------------------------------------------------------|
1. | Afghanistan 1952 Asia 779.44531 28.801 8425333 matched (3) |
2. | Afghanistan 1957 Asia 820.85303 30.332 9240934 matched (3) |
3. | Afghanistan 1962 Asia 853.10071 31.997 10267083 matched (3) |
4. | Afghanistan 1967 Asia 836.19714 34.02 11537966 matched (3) |
5. | Afghanistan 1972 Asia 739.98111 36.088 13079460 matched (3) |
|------------------------------------------------------------------------------|
6. | Afghanistan 1977 Asia 786.11336 38.438 14880372 matched (3) |
7. | Afghanistan 1982 Asia 978.01144 39.854 12881816 matched (3) |
8. | Afghanistan 1987 Asia 852.39594 40.822 13867957 matched (3) |
9. | Afghanistan 1992 Asia 649.3414 41.674 16317921 matched (3) |
10. | Afghanistan 1997 Asia 635.34135 41.763 22227415 matched (3) |
+------------------------------------------------------------------------------+
Let us verify that all observations have been matched.
. tab _merge
_merge | Freq. Percent Cum.
------------------------+-----------------------------------
matched (3) | 1,704 100.00 100.00
------------------------+-----------------------------------
Total | 1,704 100.00
To clean up the dataset, we are going to remove the _merge variable since it is no longer needed.
We also move the year variable after the continent variable, because it makes our dataset look more organized.
. drop _merge
. order year, after(continent)
. list in 1/10
+----------------------------------------------------------------+
| country contin~t year gdpPercap lifeExp pop |
|----------------------------------------------------------------|
1. | Afghanistan Asia 1952 779.44531 28.801 8425333 |
2. | Afghanistan Asia 1957 820.85303 30.332 9240934 |
3. | Afghanistan Asia 1962 853.10071 31.997 10267083 |
4. | Afghanistan Asia 1967 836.19714 34.02 11537966 |
5. | Afghanistan Asia 1972 739.98111 36.088 13079460 |
|----------------------------------------------------------------|
6. | Afghanistan Asia 1977 786.11336 38.438 14880372 |
7. | Afghanistan Asia 1982 978.01144 39.854 12881816 |
8. | Afghanistan Asia 1987 852.39594 40.822 13867957 |
9. | Afghanistan Asia 1992 649.3414 41.674 16317921 |
10. | Afghanistan Asia 1997 635.34135 41.763 22227415 |
+----------------------------------------------------------------+
Recap of merging the gapminder_gdpPercap.dta, gapminder_lifeExp.dta and gapminder_pop.dta datasets
Below are all commands we were carrying out in order to merge the three subsets. In the last step we save the new dataset under the file name gapminder_merged.dta.
* Merging the subsets
use gapminder_gdpPercap.dta, clear
merge 1:1 country year using gapminder_lifeExp.dta
drop _merge
merge 1:1 country year using gapminder_pop.dta
drop _merge
order year, after(continent)
save gapminder_merged, replace
Exercise 6: Merging the different subsets
- Open the weo_data_gdpPercap.dta dataset and merge it with the weo_data_pop.dta dataset.
- Merge the data again with the weo_data_ue_rate.dta dataset.
Bonus: Using a for loop to iterate through the three subjects
If we look back at our code to create the three subsets, we realize that the three code chunks only differ
where we pass the name of the subject to the
keep,
rename, and
save command.
We can simplify our code and reduce the number of lines, by applying a foreach loop that loops over the three subject names using a local macro.
* create subset for gdpPercap and reshape to long
preserve
keep if subject=="gdpPercap"
reshape long y, i(country) j(year)
rename y gdpPercap
drop subject
save gapminder_gdpPercap, replace
restore
* create subset for lifeExp and reshape to long
preserve
keep if subject=="lifeExp"
reshape long y, i(country) j(year)
rename y lifeExp
drop subject
save gapminder_lifeExp, replace
restore
* create subset for pop and reshape to long
preserve
keep if subject=="pop"
reshape long y, i(country) j(year)
rename y pop
drop subject
save gapminder_pop, replace
restore
Below you can see how this can be done.
We first create the string macro subjects for the three subjects GPD, life expectancy and population.
We can iterate through each of the values of the subjects macro with the
foreach commmand.
Since we want to iterate through a local macro, we have to use
of localin the
foreach
command.
Inside the main for loop we have to add double quotations around the slanted quotes for the sub macro,
when creating the subset with
keep, because it here should represent a string value
as opposed to
when renaming the y variable or saving the dataset.
There are comments added inside the for loop that explain each step
* creating subsets for lifeExp pop and gdpPercap and reshaping to long
local subjects "lifeExp pop gdpPercap"
foreach sub of local subjects{
preserve /*Creating a retrievable copy of the original dataset*/
keep if subject=="`sub'" /*Creating subset for the selected subject*/
reshape long y, i(country) j(year) /*Reshaping the subset to long*/
rename y `sub'
drop y
save gapminder`sub', replace /*Storing subset in new file*/
restore /*Restoring back to original dataset*/
}
Exercise 7: Adding a for loop
Apply a for loop to create the three subsets for the GDP per capita, unemployment and the general government structural balance.
Reshaping the gapminder dataset using only the reshape command
There is a more elegant way to reshape the gapminder dataset into the long orientation using only a combination of reshape commands. For this, we need to perform a reshape to long followed by a reshape to wide orientation in sequence. Below is the gapminder data in its original shape.
. list in 1/10
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| country contin~t subject y1952 y1957 y1962 y1967 y1972 y1977 y1982 y1987 y1992 y1997 y2002 y2007 |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
1. | Afghanistan Asia gdpPercap 779.44531 820.85303 853.10071 836.19714 739.98111 786.11336 978.01144 852.39594 649.3414 635.34135 726.73405 974.58034 |
2. | Afghanistan Asia lifeExp 28.801 30.332 31.997 34.02 36.088 38.438 39.854 40.822 41.674 41.763 42.129 43.828 |
3. | Afghanistan Asia pop 8425333 9240934 10267083 11537966 13079460 14880372 12881816 13867957 16317921 22227415 25268405 31889923 |
4. | Albania Europe gdpPercap 1601.0561 1942.2842 2312.889 2760.1969 3313.4222 3533.0039 3630.8807 3738.9327 2497.4379 3193.0546 4604.2117 5937.0295 |
5. | Albania Europe lifeExp 55.23 59.28 64.82 66.22 67.69 68.93 70.42 72 71.581 72.95 75.651 76.423 |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
6. | Albania Europe pop 1282697 1476505 1728137 1984060 2263554 2509048 2780097 3075321 3326498 3428038 3508512 3600523 |
7. | Algeria Africa gdpPercap 2449.0082 3013.976 2550.8169 3246.9918 4182.6638 4910.4168 5745.1602 5681.3585 5023.2166 4797.2951 5288.0404 6223.3675 |
8. | Algeria Africa lifeExp 43.077 45.685 48.303 51.407 54.518 58.014 61.368 65.799 67.744 69.152 70.994 72.301 |
9. | Algeria Africa pop 9279525 10270856 11000948 12760499 14760787 17152804 20033753 23254956 26298373 29072015 31287142 33333216 |
10. | Angola Africa gdpPercap 3520.6103 3827.9405 4269.2767 5522.7764 5473.288 3008.6474 2756.9537 2430.2083 2627.8457 2277.1409 2773.2873 4797.2313 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
We perform the first reshape to the long orientation, just like we did previously, but without first creating a subset for one of the three subjects (gdp, life expectancy, and population).
. reshape long y, i(country subject) j(year)
(j = 1952 1957 1962 1967 1972 1977 1982 1987 1992 1997 2002 2007)
Data Wide -> Long
-----------------------------------------------------------------------------
Number of observations 426 -> 5,112
Number of variables 15 -> 5
j variable (12 values) -> year
xij variables:
y1952 y1957 ... y2007 -> y
-----------------------------------------------------------------------------
. list in 1/20
+-------------------------------------------------------+
| country subject year contin~t y |
|-------------------------------------------------------|
1. | Afghanistan gdpPercap 1952 Asia 779.44531 |
2. | Afghanistan gdpPercap 1957 Asia 820.85303 |
3. | Afghanistan gdpPercap 1962 Asia 853.10071 |
4. | Afghanistan gdpPercap 1967 Asia 836.19714 |
5. | Afghanistan gdpPercap 1972 Asia 739.98111 |
|-------------------------------------------------------|
6. | Afghanistan gdpPercap 1977 Asia 786.11336 |
7. | Afghanistan gdpPercap 1982 Asia 978.01144 |
8. | Afghanistan gdpPercap 1987 Asia 852.39594 |
9. | Afghanistan gdpPercap 1992 Asia 649.3414 |
10. | Afghanistan gdpPercap 1997 Asia 635.34135 |
|-------------------------------------------------------|
11. | Afghanistan gdpPercap 2002 Asia 726.73405 |
12. | Afghanistan gdpPercap 2007 Asia 974.58034 |
13. | Afghanistan lifeExp 1952 Asia 28.801 |
14. | Afghanistan lifeExp 1957 Asia 30.332 |
15. | Afghanistan lifeExp 1962 Asia 31.997 |
|-------------------------------------------------------|
16. | Afghanistan lifeExp 1967 Asia 34.02 |
17. | Afghanistan lifeExp 1972 Asia 36.088 |
18. | Afghanistan lifeExp 1977 Asia 38.438 |
19. | Afghanistan lifeExp 1982 Asia 39.854 |
20. | Afghanistan lifeExp 1987 Asia 40.822 |
+-------------------------------------------------------+
This returns the data in the long orientation, where we get a single row for a specific year, for a specific subject for a specific country.
We can now reshape the data to the wide orientation so that each subject is represented in a separate column.
As before, we use the wide option to reshape from long to wide orientation. The stubname for the column holding the dependent variable is still y. The data in its final shape should have a row for each country and year, but the subjects should be spread across different columns, i.e. a separate column for each subject. Therefore, we need to include country and year with the i option and use subhect for the j option. Finally, we also need set the string option, since the subject column consists of string values.
. reshape wide y, i(country year) j(subject) string
(j = gdpPercap lifeExp pop)
Data Long -> Wide
-----------------------------------------------------------------------------
Number of observations 5,112 -> 1,704
Number of variables 5 -> 6
j variable (3 values) subject -> (dropped)
xij variables:
y -> ygdpPercap ylifeExp ypop
-----------------------------------------------------------------------------
. list in 1/20
+-----------------------------------------------------------------+
| country year ygdpPer~p ylifeExp ypop contin~t |
|-----------------------------------------------------------------|
1. | Afghanistan 1952 779.44531 28.801 8425333 Asia |
2. | Afghanistan 1957 820.85303 30.332 9240934 Asia |
3. | Afghanistan 1962 853.10071 31.997 10267083 Asia |
4. | Afghanistan 1967 836.19714 34.02 11537966 Asia |
5. | Afghanistan 1972 739.98111 36.088 13079460 Asia |
|-----------------------------------------------------------------|
6. | Afghanistan 1977 786.11336 38.438 14880372 Asia |
7. | Afghanistan 1982 978.01144 39.854 12881816 Asia |
8. | Afghanistan 1987 852.39594 40.822 13867957 Asia |
9. | Afghanistan 1992 649.3414 41.674 16317921 Asia |
10. | Afghanistan 1997 635.34135 41.763 22227415 Asia |
|-----------------------------------------------------------------|
11. | Afghanistan 2002 726.73405 42.129 25268405 Asia |
12. | Afghanistan 2007 974.58034 43.828 31889923 Asia |
13. | Albania 1952 1601.0561 55.23 1282697 Europe |
14. | Albania 1957 1942.2842 59.28 1476505 Europe |
15. | Albania 1962 2312.889 64.82 1728137 Europe |
|-----------------------------------------------------------------|
16. | Albania 1967 2760.1969 66.22 1984060 Europe |
17. | Albania 1972 3313.4222 67.69 2263554 Europe |
18. | Albania 1977 3533.0039 68.93 2509048 Europe |
19. | Albania 1982 3630.8807 70.42 2780097 Europe |
20. | Albania 1987 3738.9327 72 3075321 Europe |
+-----------------------------------------------------------------+
Exercise 8: Using reshape only
- Load the weo_data dataset using the url “https://github.com/mwiemers/datasets/blob/main/weo_data_dirty_reshape.xlsx?raw=true”
- Use a combination of reshape to the long and reshape to the wide orienation as in the example to put the weo dataset in the right shape.
- Rename the subject columns to remove the y at the start of the name.
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!