Reshape from Long to Wide

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Reshape from Long to Wide

Chao yawo-2
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
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Reshape from Long to Wide

David Marso
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?"
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Reshape from Long to Wide

Bruce Weaver
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."

NOTE: My Hotmail account is not monitored regularly.
To send me an e-mail, please use the address shown above.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Reshape from Long to Wide

David Marso
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

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?"
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Reshape from Long to Wide

Bruce Weaver
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>
>
--
Bruce Weaver
bweaver@lakeheadu.ca
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.
Loading...