SPSSX Discussion

Reshape from Long to Wide

Classic

List

Threaded

5 messages Options

Chao yawo-2

Reshape from Long to Wide

Hello all,

good morning. I am trying to reshape a data from wide to long to use for a GENLIN model.

Each respondent in the data was asked to name 3 associates who they ask for help (named Fr1, Fr2, Fr3) and 3 others who they seek advice from (Sis1, Sis2, Sis3).

They were also asked to provide a number of information about each associates, for example, their age (Fr1_Age....Sis3_Age).

Respondents also report their Age, Gender, etc (called R_Age, R_Gender).

I want to reshape the data from wide to long, as shown in the image posted here: https://ibb.co/bBVpoQ.

I am able to do it for one of the relations, using the following command:

VARSTOCASES

/MAKE trans1 FROM Fr_Alters_1 Fr_Alters_2 Fr_Alters_3

/MAKE trans2 FROM Age1 Age2 Age3

/INDEX=Index1(3)

/KEEP=id R_Age1 R_Age2 R_Age3 Relation1 Relation2 Relation3

/NULL=KEEP.

However, I am not been successful in getting the command to reproduce the two relations at the same time.

I will appreciate some help from you all in accomplishing this.

thanks - Cy

-----------------------------------------------------

===================== To manage your subscription to SPSSX-L, send a message to [hidden email] (not to SPSSX-L), with no body text except the command. To leave the list, send the command SIGNOFF SPSSX-L For a list of commands to manage subscriptions, send the command INFO REFCARD

David Marso

Re: Reshape from Long to Wide

Administrator

The VARSTOCASES command reshapes data from WIDE to LONG.
You are barking up the wrong tree.
CASESTOVARS is the appropriate command to go from LONG to WIDE.

AGGREGATE following VECTOR and COMPUTE will also suffice.

data:

id index x y z
1 1 a 1 4
1 2 b 3 6
1 3 c 4 2
2 1 a 2 4
2 2 b 1 2
2 3 c 4 5

VECTOR x (3,A1) /y (3,F1) /z (3,F1).
COMPUTE x(index)=x.
COMPUTE y(index)=y.
COMPUTE z(index)=z.

AGGREGATE OUTFILE *
/ BREAK id
/ x1 TO x3 y1 TO y3 z1 TO z3 =
MAX(x1 TO x3 y1 TO y3 z1 TO z3 ).

Yields:
id x1 TO x3 y1 TO y3 z1 TO z3
1 a b c 1 3 4 4 6 2
2 a b c 2 1 4 4 2 5

Chao yawo-2 wrote

Hello all,

good morning. I am trying to reshape a data from wide to long to use for a
GENLIN model.

Each respondent in the data was asked to name 3 associates who they ask for
help (named Fr1, Fr2, Fr3) and 3 others who they seek advice from (Sis1,
Sis2, Sis3).

They were also asked to provide a number of information about each
associates, for example, their age (Fr1_Age....Sis3_Age).

Respondents also report their Age, Gender, etc (called R_Age, R_Gender).

I want to reshape the data from wide to long, as shown in the image posted
here: * https://ibb.co/bBVpoQ <https://ibb.co/bBVpoQ>.*

I am able to do it for one of the relations, using the following command:

VARSTOCASES
/MAKE trans1 FROM Fr_Alters_1 Fr_Alters_2 Fr_Alters_3
/MAKE trans2 FROM Age1 Age2 Age3
/INDEX=Index1(3)
/KEEP=id R_Age1 R_Age2 R_Age3 Relation1 Relation2 Relation3
/NULL=KEEP.

However, I am not been successful in getting the command to reproduce the
two relations at the same time.

I will appreciate some help from you all in accomplishing this.

thanks - Cy

-----------------------------------------------------

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

Please reply to the list and not to my personal email.
Those desiring my consulting or training services please feel free to email me.
---
"Nolite dare sanctum canibus neque mittatis margaritas vestras ante porcos ne forte conculcent eas pedibus suis."
Cum es damnatorum possederunt porcos iens ut salire off sanguinum cliff in abyssum?"

Bruce Weaver

Re: Reshape from Long to Wide

Administrator

You lost me there, David. The image file the OP referred to *does* show the original file as WIDE and the final file as LONG.

To the OP, people may be reluctant to click on links like the one you provided. It is better to provide short text listings of the original and final data files (e.g., using DATA LIST, as David did in his reply).

I don't have SPSS on this machine, so cannot test right now. But I think you want something like the following (given the images you posted):

VARSTOCASES
/MAKE CONTACTS FROM Fr1 to Fr3 Sis1 to Sis3
/MAKE ContactAge FROM Fr1_Age to Fr3_Age Sis1_Age to Sis3_Age
/INDEX=ContactID(6)
/KEEP=id R_Age R_Gender
/NULL=KEEP.
COMPUTE Friend = ContactID LT 4.
COMPUTE Sister = ContactID GT 3.
STRING Tie_ID (A8).
COMPUTE Tie_ID = CONCAT(STRING(ID,F5.0),"_",STRING(ContactID,F1.0)).
FORMATS Friend Sister (F1).
LIST.

The ContactAge variable is not shown in your LONG final file, but you probably want to include that info.

HTH.

David Marso wrote

The VARSTOCASES command reshapes data from WIDE to LONG.
You are barking up the wrong tree.
CASESTOVARS is the appropriate command to go from LONG to WIDE.

AGGREGATE following VECTOR and COMPUTE will also suffice.

data:

id index x y z
1 1 a 1 4
1 2 b 3 6
1 3 c 4 2
2 1 a 2 4
2 2 b 1 2
2 3 c 4 5

VECTOR x (3,A1) /y (3,F1) /z (3,F1).
COMPUTE x(index)=x.
COMPUTE y(index)=y.
COMPUTE z(index)=z.

AGGREGATE OUTFILE *
/ BREAK id
/ x1 TO x3 y1 TO y3 z1 TO z3 =
MAX(x1 TO x3 y1 TO y3 z1 TO z3 ).

Yields:
id x1 TO x3 y1 TO y3 z1 TO z3
1 a b c 1 3 4 4 6 2
2 a b c 2 1 4 4 2 5

Chao yawo-2 wrote

Hello all,

good morning. I am trying to reshape a data from wide to long to use for a
GENLIN model.

Each respondent in the data was asked to name 3 associates who they ask for
help (named Fr1, Fr2, Fr3) and 3 others who they seek advice from (Sis1,
Sis2, Sis3).

They were also asked to provide a number of information about each
associates, for example, their age (Fr1_Age....Sis3_Age).

Respondents also report their Age, Gender, etc (called R_Age, R_Gender).

I want to reshape the data from wide to long, as shown in the image posted
here: * https://ibb.co/bBVpoQ <https://ibb.co/bBVpoQ>.*

I am able to do it for one of the relations, using the following command:

VARSTOCASES
/MAKE trans1 FROM Fr_Alters_1 Fr_Alters_2 Fr_Alters_3
/MAKE trans2 FROM Age1 Age2 Age3
/INDEX=Index1(3)
/KEEP=id R_Age1 R_Age2 R_Age3 Relation1 Relation2 Relation3
/NULL=KEEP.

However, I am not been successful in getting the command to reproduce the
two relations at the same time.

I will appreciate some help from you all in accomplishing this.

thanks - Cy

-----------------------------------------------------

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

--
Bruce Weaver
bweaver@lakeheadu.ca
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

PLEASE NOTE THE FOLLOWING:
1. My Hotmail account is not monitored regularly. To send me an e-mail, please use the address shown above.
2. The SPSSX Discussion forum on Nabble is no longer linked to the SPSSX-L listserv administered by UGA (https://listserv.uga.edu/).

David Marso

Re: Reshape from Long to Wide

Administrator

I am basing my comment on the Subject line and the extra records in the Long file which don't appear in the wide file.

On Sun, Jun 18, 2017 at 9:03 AM, Bruce Weaver [via SPSSX Discussion] <[hidden email]> wrote:

You lost me there, David. The image file the OP referred to *does* show the original file as WIDE and the final file as LONG.

To the OP, people may be reluctant to click on links like the one you provided. It is better to provide short text listings of the original and final data files (e.g., using DATA LIST, as David did in his reply).

I don't have SPSS on this machine, so cannot test right now. But I think you want something like the following (given the images you posted):

VARSTOCASES
/MAKE CONTACTS FROM Fr1 to Fr3 Sis1 to Sis3
/MAKE ContactAge FROM Fr1_Age to Fr3_Age Sis1_Age to Sis3_Age
/INDEX=ContactID(6)
/KEEP=id R_Age R_Gender
/NULL=KEEP.
COMPUTE Friend = ContactID LT 4.
COMPUTE Sister = ContactID GT 3.
STRING Tie_ID (A8).
COMPUTE Tie_ID = CONCAT(STRING(ID,F5.0),"_",STRING(ContactID,F1.0)).
FORMATS Friend Sister (F1).
LIST.

The ContactAge variable is not shown in your LONG final file, but you probably want to include that info.

HTH.

David Marso wrote

The VARSTOCASES command reshapes data from WIDE to LONG.
You are barking up the wrong tree.
CASESTOVARS is the appropriate command to go from LONG to WIDE.

AGGREGATE following VECTOR and COMPUTE will also suffice.

data:

id index x y z
1 1 a 1 4
1 2 b 3 6
1 3 c 4 2
2 1 a 2 4
2 2 b 1 2
2 3 c 4 5

VECTOR x (3,A1) /y (3,F1) /z (3,F1).
COMPUTE x(index)=x.
COMPUTE y(index)=y.
COMPUTE z(index)=z.

AGGREGATE OUTFILE *
/ BREAK id
/ x1 TO x3 y1 TO y3 z1 TO z3 =
MAX(x1 TO x3 y1 TO y3 z1 TO z3 ).

Yields:
id x1 TO x3 y1 TO y3 z1 TO z3
1 a b c 1 3 4 4 6 2
2 a b c 2 1 4 4 2 5

Chao yawo-2 wrote

Hello all,

good morning. I am trying to reshape a data from wide to long to use for a
GENLIN model.

Each respondent in the data was asked to name 3 associates who they ask for
help (named Fr1, Fr2, Fr3) and 3 others who they seek advice from (Sis1,
Sis2, Sis3).

They were also asked to provide a number of information about each
associates, for example, their age (Fr1_Age....Sis3_Age).

Respondents also report their Age, Gender, etc (called R_Age, R_Gender).

I want to reshape the data from wide to long, as shown in the image posted
here: * https://ibb.co/bBVpoQ <https://ibb.co/bBVpoQ>.*

I am able to do it for one of the relations, using the following command:

VARSTOCASES
/MAKE trans1 FROM Fr_Alters_1 Fr_Alters_2 Fr_Alters_3
/MAKE trans2 FROM Age1 Age2 Age3
/INDEX=Index1(3)
/KEEP=id R_Age1 R_Age2 R_Age3 Relation1 Relation2 Relation3
/NULL=KEEP.

However, I am not been successful in getting the command to reproduce the
two relations at the same time.

I will appreciate some help from you all in accomplishing this.

thanks - Cy

-----------------------------------------------------

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

--
Bruce Weaver
[hidden email]
http://sites.google.com/a/lakeheadu.ca/bweaver/

"When all else fails, RTFM."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.

If you reply to this email, your message will be added to the discussion below:
http://spssx-discussion.1045642.n5.nabble.com/Reshape-from-Long-to-Wide-tp5734431p5734437.html

To unsubscribe from Reshape from Long to Wide, click here.
NAML

Bruce Weaver

Re: Reshape from Long to Wide

Administrator

I suppose you're referring to that subject line up there, are you? The one I did not pay much attention to until now.

I think the OP needs to clarify what they are trying to do exactly, and whether anything that has been suggested so far helps.

David Marso wrote

I am basing my comment on the Subject line and the extra records in the
Long file which don't appear in the wide file.

On Sun, Jun 18, 2017 at 9:03 AM, Bruce Weaver [via SPSSX Discussion] <
[hidden email]> wrote:

> You lost me there, David. The image file the OP referred to *does* show
> the original file as WIDE and the final file as LONG.
>
> To the OP, people may be reluctant to click on links like the one you
> provided. It is better to provide short text listings of the original and
> final data files (e.g., using DATA LIST, as David did in his reply).
>
> I don't have SPSS on this machine, so cannot test right now. But I think
> you want something like the following (given the images you posted):
>
> VARSTOCASES
> /MAKE CONTACTS FROM Fr1 to Fr3 Sis1 to Sis3
> /MAKE ContactAge FROM Fr1_Age to Fr3_Age Sis1_Age to Sis3_Age
> /INDEX=ContactID(6)
> /KEEP=id R_Age R_Gender
> /NULL=KEEP.
> COMPUTE Friend = ContactID LT 4.
> COMPUTE Sister = ContactID GT 3.
> STRING Tie_ID (A8).
> COMPUTE Tie_ID = CONCAT(STRING(ID,F5.0),"_",STRING(ContactID,F1.0)).
> FORMATS Friend Sister (F1).
> LIST.
>
> The ContactAge variable is not shown in your LONG final file, but you
> probably want to include that info.
>
> HTH.
>
> David Marso wrote
> The VARSTOCASES command reshapes data from WIDE to LONG.
> You are barking up the wrong tree.
> CASESTOVARS is the appropriate command to go from LONG to WIDE.
>
> AGGREGATE following VECTOR and COMPUTE will also suffice.
>
> data:
>
> id index x y z
> 1 1 a 1 4
> 1 2 b 3 6
> 1 3 c 4 2
> 2 1 a 2 4
> 2 2 b 1 2
> 2 3 c 4 5
>
> VECTOR x (3,A1) /y (3,F1) /z (3,F1).
> COMPUTE x(index)=x.
> COMPUTE y(index)=y.
> COMPUTE z(index)=z.
>
> AGGREGATE OUTFILE *
> / BREAK id
> / x1 TO x3 y1 TO y3 z1 TO z3 =
> MAX(x1 TO x3 y1 TO y3 z1 TO z3 ).
>
> Yields:
> id x1 TO x3 y1 TO y3 z1 TO z3
> 1 a b c 1 3 4 4 6 2
> 2 a b c 2 1 4 4 2 5
>
>
> Chao yawo-2 wrote
> Hello all,
>
> good morning. I am trying to reshape a data from wide to long to use for
> a
> GENLIN model.
>
> Each respondent in the data was asked to name 3 associates who they ask
> for
> help (named Fr1, Fr2, Fr3) and 3 others who they seek advice from (Sis1,
> Sis2, Sis3).
>
> They were also asked to provide a number of information about each
> associates, for example, their age (Fr1_Age....Sis3_Age).
>
> Respondents also report their Age, Gender, etc (called R_Age, R_Gender).
>
> I want to reshape the data from wide to long, as shown in the image posted
> here: * https://ibb.co/bBVpoQ <https://ibb.co/bBVpoQ>.*
>
> I am able to do it for one of the relations, using the following command:
>
> VARSTOCASES
> /MAKE trans1 FROM Fr_Alters_1 Fr_Alters_2 Fr_Alters_3
> /MAKE trans2 FROM Age1 Age2 Age3
> /INDEX=Index1(3)
> /KEEP=id R_Age1 R_Age2 R_Age3 Relation1 Relation2 Relation3
> /NULL=KEEP.
>
> However, I am not been successful in getting the command to reproduce the
> two relations at the same time.
>
> I will appreciate some help from you all in accomplishing this.
>
> thanks - Cy
>
> -----------------------------------------------------
>
> =====================
> To manage your subscription to SPSSX-L, send a message to
> [hidden email] <http:///user/SendEmail.jtp?type=node&node=5734437&i=0> (not
> to SPSSX-L), with no body text except the
> command. To leave the list, send the command
> SIGNOFF SPSSX-L
> For a list of commands to manage subscriptions, send the command
> INFO REFCARD
>
> --
> Bruce Weaver
> [hidden email]
> http://sites.google.com/a/lakeheadu.ca/bweaver/
>
> "When all else fails, RTFM."
>
>
>
> *NOTE: My Hotmail account is not monitored regularly. To send me an
> e-mail, please use the address shown above. *
>
>
> ------------------------------
> If you reply to this email, your message will be added to the discussion
> below:
> http://spssx-discussion.1045642.n5.nabble.com/Reshape-from-Long-to-Wide-
> tp5734431p5734437.html
> To unsubscribe from Reshape from Long to Wide, click here
> <http://spssx-discussion.1045642.n5.nabble.com/template/NamlServlet.jtp?macro=unsubscribe_by_code&node=5734431&code=ZGF2aWQubWFyc29AZ21haWwuY29tfDU3MzQ0MzF8LTkzOTgzMDYxMw==>
> .
> NAML
> <http://spssx-discussion.1045642.n5.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
>