This learning module illustrates how to reshape data
files in SPSS versions 11 and up. These examples take long data files and reshape them into
wide form.
Common examples of reshaping data are shown, but they do not exhaustively demonstrate the
different kinds of data reshaping that you could encounter.
Example #1: One variable
Let’s use the https://stats.idre.ucla.edu/wp-content/uploads/2016/02/kidslw.sav
file for our first example.
get file 'c:https://stats.idre.ucla.edu/wp-content/uploads/2016/02/kidslw.sav'.
list famid birth age.
FAMID BIRTH AGE
1.00 1.00 9.00
1.00 2.00 6.00
1.00 3.00 3.00
2.00 1.00 8.00
2.00 2.00 6.00
2.00 3.00 2.00
3.00 1.00 6.00
3.00 2.00 4.00
3.00 3.00 2.00
Number of cases read: 9 Number of cases listed: 9
Let’s make the variable age in this file wide, making one record per family which would contain
age1, age2
and age3, the ages of the kids in the family
(age2 would be missing if there is only one kid, and age3 would be missing if there are only
two kids). We will use the SPSS command casestovars to reshape
the data from long to wide. On the /id subcommand, we list the
name of the variable on which we want SPSS to "break" the data. Note
that the data need to be sorted on the variable listed on the /id
subcommand. We use the variable birth as the index variable on
the /index subcommand. This is the variable that is controlling
the naming of the variables in the new data set. Because birth
has two decimal places, as seen in the output above, the new variables have
.00 at the end of the names. If you do not want that, you can change the
number of decimal places associated with the index variable. Using the
point-and-click interface, you can do this in the "Variable View" window in
the column called "Decimals", or if you would rather use syntax, you can use
the formats command. We list on the /drop subcommand all of the other
variables in the data set. Any variable that you do not list on the /drop
subcommand will be reshaped.
casestovars /id=famid /index = birth /drop id kidname wt sex. list.FAMID AGE.1.00 AGE.2.00 AGE.3.00 1.00 9.00 6.00 3.00 2.00 8.00 6.00 2.00 3.00 6.00 4.00 2.00 Number of cases read: 3 Number of cases listed: 3
Also note that in the output, SPSS tells us which variable were used to
create the new variables, how many cases were in the long data set (called
"Cases In" in the Processing Statistics table), how many cases are in the wide
data set (called "Cases Out"), and how many index values were created. You
can use this information to ensure that the reshaping went as desired.
Example #2: Two variables
In the example above, we reshaped
only one variable, age. In the example below, we reshape the variables
age
and wt. You can reshape as many
variables as needed by not including them on the /drop subcommand.
get file 'c:https://stats.idre.ucla.edu/wp-content/uploads/2016/02/kidslw.sav'. list famid birth age. ID FAMID BIRTH AGE 1.00 1.00 1.00 9.00 2.00 1.00 2.00 6.00 3.00 1.00 3.00 3.00 4.00 2.00 1.00 8.00 5.00 2.00 2.00 6.00 6.00 2.00 3.00 2.00 7.00 3.00 1.00 6.00 8.00 3.00 2.00 4.00 9.00 3.00 3.00 2.00 Number of cases read: 9 Number of cases listed: 9casestovars /id=famid /index=birth /drop id kidname sex. list famid age.1.00 age.2.00 age.3.00 wt.1.00 wt.2.00 wt.3.00.FAMID AGE.1.00 AGE.2.00 AGE.3.00 WT.1.00 WT.2.00 WT.3.00 1.00 9.00 6.00 3.00 60.00 40.00 20.00 2.00 8.00 6.00 2.00 80.00 50.00 20.00 3.00 6.00 4.00 2.00 60.00 40.00 20.00 Number of cases read: 3 Number of cases listed: 3
Example #3: Both numeric and character variables
The examples above showed how to reshape data using numeric
variables, but sometimes you will need to reshape character (i.e., string) variables as well. In
this example, we reshape two numeric variables, age and wt, and
two string variables, sex and kidname.
Consider the kidslw
data file shown below.
get file 'c:https://stats.idre.ucla.edu/wp-content/uploads/2016/02/kidslw.sav'. list. ID FAMID KIDNAME BIRTH AGE WT SEX 1.00 1.00 Beth 1.00 9.00 60.00 f 2.00 1.00 Bob 2.00 6.00 40.00 m 3.00 1.00 Barb 3.00 3.00 20.00 f 4.00 2.00 Andy 1.00 8.00 80.00 m 5.00 2.00 Al 2.00 6.00 50.00 m 6.00 2.00 Ann 3.00 2.00 20.00 f 7.00 3.00 Pete 1.00 6.00 60.00 m 8.00 3.00 Pam 2.00 4.00 40.00 f 9.00 3.00 Phil 3.00 2.00 20.00 m Number of cases read: 9 Number of cases listed: 9 casestovars /id=famid /drop id birth. list famid v1 v2 v3 age.1 age.2 age.3 wt.1 wt.2 wt.3 sex.1 sex.2 sex.3.The variables are listed in the following order: LINE 1: FAMID V1 V2 V3 AGE.1.00 AGE.2.00 AGE.3.00 WT.1.00 LINE 2: WT.2.00 WT.3.00 SEX.1.00 SEX.2.00 SEX.3.00 FAMID: 1.00 Beth Bob Barb 9.00 6.00 3.00 60.00 WT.2.00: 40.00 20.00 f m f FAMID: 2.00 Andy Al Ann 8.00 6.00 2.00 80.00 WT.2.00: 50.00 20.00 m m f FAMID: 3.00 Pete Pam Phil 6.00 4.00 2.00 60.00 WT.2.00: 40.00 20.00 m f m Number of cases read: 3 Number of cases listed: 3
The warning message below is part of the SPSS output.
As you can see, SPSS has renamed some of the variables with names that are
not very informative. We can use the /rename subcommand to rename the
variables to meaningful names.
get file "c:https://stats.idre.ucla.edu/wp-content/uploads/2016/02/kidslw.sav".casestovars /id=famid /rename kidname=n /index=birth /drop id sex. list famid n.1.00 n.2.00 n.3.00 age.1.00 age.2.00 age.3.00 wt.1.00 wt.2.00 wt.3.00.The variables are listed in the following order: LINE 1: FAMID N.1.00 N.2.00 N.3.00 AGE.1.00 AGE.2.00 AGE.3.00 WT.1.00 LINE 2: WT.2.00 WT.3.00 FAMID: 1.00 Beth Bob Barb 9.00 6.00 3.00 60.00 WT.2.00: 40.00 20.00 FAMID: 2.00 Andy Al Ann 8.00 6.00 2.00 80.00 WT.2.00: 50.00 20.00 FAMID: 3.00 Pete Pam Phil 6.00 4.00 2.00 60.00 WT.2.00: 40.00 20.00 Number of cases read: 3 Number of cases listed: 3
Another change that we can make to the naming of variables is altering the
separator that is used between the root name and the index. For example,
in the above output, the root names are name, age and wt;
the indexes are 1, 2 and 3. These values are
separated by periods (.), but we can change that using the /separator
subcommand, as shown below. Also, we will use the formats command
to change the format of birth so that it no longer has decimals.
get file "c:https://stats.idre.ucla.edu/wp-content/uploads/2016/02/kidslw.sav". formats birth (f1). casestovars /id=famid /index=birth /rename kidname=name /separator = "_" /drop id sex. list famid name_1 name_2 name_3 age_1 age_2 age_3 wt_1 wt_2 wt_3.The variables are listed in the following order: LINE 1: FAMID NAME_1 NAME_2 NAME_3 AGE_1 AGE_2 AGE_3 WT_1 LINE 2: WT_2 WT_3 FAMID: 1.00 Beth Bob Barb 9.00 6.00 3.00 60.00 WT_2: 40.00 20.00 FAMID: 2.00 Andy Al Ann 8.00 6.00 2.00 80.00 WT_2: 50.00 20.00 FAMID: 3.00 Pete Pam Phil 6.00 4.00 2.00 60.00 WT_2: 40.00 20.00 Number of cases read: 3 Number of cases listed: 3
Example #4: Character suffixes
Variables with character suffixes are not a problem with the
casestovars command. Nothing needs to be done differently when
you have character suffixes, as illustrated below. Note that in this
example, the data file is not sorted on the variable that we will use as the
id variable; therefore, we need to sort the data before reshaping it.
We will use the file https://stats.idre.ucla.edu/wp-content/uploads/2016/02/dadmoml.sav .
get file 'c:https://stats.idre.ucla.edu/wp-content/uploads/2016/02/dadmoml.sav'. list.ID FAMID NAME INC DADMOM 1.00 2.00 Art 22000.00 dad 2.00 1.00 Bill 30000.00 dad 3.00 3.00 Paul 25000.00 dad 4.00 1.00 Bess 15000.00 mom 5.00 3.00 Pat 50000.00 mom 6.00 2.00 Amy 18000.00 mom Number of cases read: 6 Number of cases listed: 6sort cases by famid. casestovars /id=famid /drop=id /index=dadmom. list.FAMID NAME.DAD NAME.MOM INC.DAD INC.MOM 1.00 Bill Bess 30000.00 15000.00 2.00 Art Amy 22000.00 18000.00 3.00 Paul Pat 25000.00 50000.00 Number of cases read: 3 Number of cases listed: 3
Example #5: No index variable
There may be times when your data are ordered but an index
variable is not present. This is not a problem, as there is no need for
an index variable, as long as the data set is properly sorted (i.e., by famid and
birth) and there are no missing values.
get file 'c:kidslw.sav'. list famid birth age.FAMID BIRTH AGE 1.00 1.00 9.00 1.00 2.00 6.00 1.00 3.00 3.00 2.00 1.00 8.00 2.00 2.00 6.00 2.00 3.00 2.00 3.00 1.00 6.00 3.00 2.00 4.00 3.00 3.00 2.00 Number of cases read: 9 Number of cases listed: 9casestovars /id=famid /drop id kidname birth wt sex. list famid age.1 age.2 age.3.FAMID AGE.1 AGE.2 AGE.3 1.00 9.00 6.00 3.00 2.00 8.00 6.00 2.00 3.00 6.00 4.00 2.00 Number of cases read: 3 Number of cases listed: 3
Example #6: A more complex example
In the example below, we have three variables, a, b and outcome. We would like to make the variable
outcome wide,
creating three variables containing the values in outcome in the long
data set. We would like all of the values associated with b = 1 to be in
the first column of outcome, all of the values associated with b = 2 in
the second column of outcome, and all of the values associated with b =
3 in the third column of outcome. However, we do not have the variable that we would like to
use on the /id subcommand. We will create this variable, which we
will call index, using the compute and if commands and
the lag function. The resulting data set is shown below.
get file "c:reshape.sav".
compute index = 1.
if b = lag(b) index = lag(index)+1.
execute.
list.
A B OUTCOME INDEX
1.00 1.00 1.00 1.00
1.00 1.00 4.00 2.00
1.00 1.00 3.00 3.00
1.00 1.00 4.00 4.00
2.00 1.00 4.00 5.00
2.00 1.00 34.00 6.00
2.00 1.00 56.00 7.00
2.00 1.00 67.00 8.00
3.00 1.00 45.00 9.00
3.00 1.00 53.00 10.00
3.00 1.00 456.00 11.00
3.00 1.00 56.00 12.00
1.00 2.00 67.00 1.00
1.00 2.00 8.00 2.00
1.00 2.00 454.00 3.00
1.00 2.00 6.00 4.00
2.00 2.00 7.00 5.00
2.00 2.00 45.00 6.00
2.00 2.00 4.00 7.00
2.00 2.00 5.00 8.00
3.00 2.00 4.00 9.00
3.00 2.00 6.00 10.00
3.00 2.00 7.00 11.00
3.00 2.00 8.00 12.00
1.00 3.00 9.00 1.00
1.00 3.00 565.00 2.00
1.00 3.00 5.00 3.00
1.00 3.00 4.00 4.00
2.00 3.00 46.00 5.00
2.00 3.00 657.00 6.00
2.00 3.00 8.00 7.00
2.00 3.00 67.00 8.00
3.00 3.00 454.00 9.00
3.00 3.00 6768.00 10.00
3.00 3.00 45.00 11.00
3.00 3.00 78.00 12.00
Number of cases read: 36 Number of cases listed: 36
Before we can reshape the data, we need to sort them by the variable that we
will use on the /id subcommand. Also, we will drop the variable
b. If we do not drop b, we will get three variables, called
b.1, b.2 and b.3, that will be constants, each with the value
of its index.
sort cases by index. casestovars /id = index /drop b. list.INDEX A V1 V2 V3 1.00 1.00 1.00 67.00 9.00 2.00 1.00 4.00 8.00 565.00 3.00 1.00 3.00 454.00 5.00 4.00 1.00 4.00 6.00 4.00 5.00 2.00 4.00 7.00 46.00 6.00 2.00 34.00 45.00 657.00 7.00 2.00 56.00 4.00 8.00 8.00 2.00 67.00 5.00 67.00 9.00 3.00 45.00 4.00 454.00 10.00 3.00 53.00 6.00 6768.00 11.00 3.00 456.00 7.00 45.00 12.00 3.00 56.00 8.00 78.00 Number of cases read: 12 Number of cases listed: 12