Quantcast

Help with Reshaping Data: Long to Wide ?

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

Help with Reshaping Data: Long to Wide ?

Chao yawo-2
Hello,  Good Morning.  I need some help in converting this data from long to wide format
so that I can have one record for each respondent.

The dataset has the following types of variables:

Resp
: is the ID for each respondent

Tie
:  is the ID for each of the respondents contacts. Note each respondent can have a different number of non-overlapping contacts. 

TieType: 
 an indicator of the type of relationship the respondent has with their contact
(1=sex; 2=social; 3=family; 4=boss). So respondent 1 for example has two types of relationship with Contact_1:  
they both a sex and a social relationship.

Gender_Resp & Gender_Tie
:  Indicates the gender of both respondent and their contact (tie) 


RespTieTieTypeGender_RespGender_Tie
1111 (Male)1 (Male)
1121 (Male)
2 (Female)
1331 (Male)
1 (Male)
1441 (Male)
2 (Female)
2611 (Male)
1 (Male)
2821 (Male)
2 (Female)
33412 (Female)1 (Male)
31512 (Female)
2 (Female)
310022 (Female)
1 (Male)
310022 (Female)
1 (Male)


Now, I want to create the following types of variable per each respondent.

--  N_Contacts: The absolute number of contacts they have:  So Respondent_1 
will have 3 contacts (#1, 3, 4); same as Respondent_3 with 3 distinct contacts

-- Mixed_Rel:  The extent to which the respondent engages in a mixed relationship
(defined as sex + social) with his/her contacts.  So, Resp_1 has a sex+social relationship
with 1 out of 3 his contacts; while Resp_2 and Resp_3 do not engage in any mixed relationship with
any contact. 

-- Prop_SameGender:  I want to find out the proportion of a respondents contacts that have same gender as he/she.
So, Resp_1 has same-gender relationship with 2 out of his 3 contacts (with contacts 1 & 3).


I would be very grateful for some pointers to create this summary file per respondent.

Thanks in advance.. Yy
===================== 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
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Help with Reshaping Data: Long to Wide ?

Rich Ulrich

What you are looking for seems to be Aggregation.  Compute the relations you want at the record level, and

count them, using Aggregate, across the IDs.  The tricky one, perhaps, is where you want to know the number of

separate values for Tie:  First, AGG on ID, TIE to make a temp file; then AGG this file to count the number of records.


You will create another AGG result, I think, to get counts of the other things in another temp file.  Then, match the two

temp files.


Your sample data confuses me -- I think that I can understand it, except that it /seems/  to define TIE=1  to be a Male

in one record, and a Female in the next.   And, for a second I imagined the ID and TIE should be the same people, and

thus should have a single ID sequence ... but this sample shows ID=1 having TIE=1, with two TieType's.


--

Rich Ulrich




From: SPSSX(r) Discussion <[hidden email]> on behalf of Chao yawo <[hidden email]>
Sent: Saturday, December 10, 2016 10:35:16 AM
To: [hidden email]
Subject: Help with Reshaping Data: Long to Wide ?
 
Hello,  Good Morning.  I need some help in converting this data from long to wide format
so that I can have one record for each respondent.

The dataset has the following types of variables:

Resp
: is the ID for each respondent

Tie
:  is the ID for each of the respondents contacts. Note each respondent can have a different number of non-overlapping contacts. 

TieType: 
 an indicator of the type of relationship the respondent has with their contact
(1=sex; 2=social; 3=family; 4=boss). So respondent 1 for example has two types of relationship with Contact_1:  
they both a sex and a social relationship.

Gender_Resp & Gender_Tie
:  Indicates the gender of both respondent and their contact (tie) 


Resp Tie TieType Gender_Resp Gender_Tie
1 1 1 1 (Male) 1 (Male)
1 1 2 1 (Male)
2 (Female)
1 3 3 1 (Male)
1 (Male)
1 4 4 1 (Male)
2 (Female)
2 6 1 1 (Male)
1 (Male)
2 8 2 1 (Male)
2 (Female)
3 34 1 2 (Female) 1 (Male)
3 15 1 2 (Female)
2 (Female)
3 100 2 2 (Female)
1 (Male)
3 100 2 2 (Female)
1 (Male)


Now, I want to create the following types of variable per each respondent.

--  N_Contacts: The absolute number of contacts they have:  So Respondent_1 
will have 3 contacts (#1, 3, 4); same as Respondent_3 with 3 distinct contacts

-- Mixed_Rel:  The extent to which the respondent engages in a mixed relationship
(defined as sex + social) with his/her contacts.  So, Resp_1 has a sex+social relationship
with 1 out of 3 his contacts; while Resp_2 and Resp_3 do not engage in any mixed relationship with
any contact. 

-- Prop_SameGender:  I want to find out the proportion of a respondents contacts that have same gender as he/she.
So, Resp_1 has same-gender relationship with 2 out of his 3 contacts (with contacts 1 & 3).


I would be very grateful for some pointers to create this summary file per respondent.

Thanks in advance.. Yy
===================== 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
===================== 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
Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Help with Reshaping Data: Long to Wide ?

Maguin, Eugene
In reply to this post by Chao yawo-2

This builds on Rich’s reply. The addition is that, unless you want to, you don’t need to save two temp files.

Gene Maguin

 

 

Easier to harder

Mixed_Rel:  
Prop_SameGender:  

Do if (gender_resp eq gender_tie).

+  compute mixed_rel=0.

+  compute prop_samegender=1.

Else.

+  compute mixed_rel=1.

+  compute prop_samegender=0.

End if.

 

Aggregate outfile=* mode=addvariables/break=resp tie/n_same=nu.

Recode n_same(2 thru hi=0).

Execute.

Aggregate outfile=* mode=addvariables/break=resp/N_Contacts=sum(n_same).

 

Casestovars id=resp.

 

 

 

 

 



 

 

 

 

 

Long to wide (have you looked at the documentation?? Just curious.

Casestovars id=resp.

 

--  N_Contacts:

 

Mixed_Rel:  

-- Prop_SameGender:  

 

 

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Chao yawo
Sent: Saturday, December 10, 2016 10:35 AM
To: [hidden email]
Subject: Help with Reshaping Data: Long to Wide ?

 

Hello,  Good Morning.  I need some help in converting this data from long to wide format
so that I can have one record for each respondent.

The dataset has the following types of variables:

Resp
: is the ID for each respondent

Tie
:  is the ID for each of the respondents contacts. Note each respondent can have a different number of non-overlapping contacts. 

TieType: 
 
an indicator of the type of relationship the respondent has with their contact
(1=sex; 2=social; 3=family; 4=boss). So respondent 1 for example has two types of relationship with Contact_1:  
they both a sex and a social relationship.

Gender_Resp & Gender_Tie
:  Indicates the gender of both respondent and their contact (tie) 

Resp

Tie

TieType

Gender_Resp

Gender_Tie

1

1

1

1 (Male)

1 (Male)

1

1

2

1 (Male)

2 (Female)

1

3

3

1 (Male)

1 (Male)

1

4

4

1 (Male)

2 (Female)

2

6

1

1 (Male)

1 (Male)

2

8

2

1 (Male)

2 (Female)

3

34

1

2 (Female)

1 (Male)

3

15

1

2 (Female)

2 (Female)

3

100

2

2 (Female)

1 (Male)

3

100

2

2 (Female)

1 (Male)



Now, I want to create the following types of variable per each respondent.

--  N_Contacts: The absolute number of contacts they have:  So Respondent_1 
will have 3 contacts (#1, 3, 4); same as Respondent_3 with 3 distinct contacts

-- Mixed_Rel:  The extent to which the respondent engages in a mixed relationship
(defined as sex + social) with his/her contacts.  So, Resp_1 has a sex+social relationship
with 1 out of 3 his contacts; while Resp_2 and Resp_3 do not engage in any mixed relationship with
any contact. 

-- Prop_SameGender:  I want to find out the proportion of a respondents contacts that have same gender as he/she.
So, Resp_1 has same-gender relationship with 2 out of his 3 contacts (with contacts 1 & 3).


I would be very grateful for some pointers to create this summary file per respondent.

Thanks in advance.. Yy

===================== 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

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

Re: Help with Reshaping Data: Long to Wide ?

David Marso
Administrator
YIKES!
COMPUTE prop_samegender=gender_resp eq gender_tie.
COMPUTE mixed_rel = NOT(prop_samegender).

Instead of ...

"Do if (gender_resp eq gender_tie).

+  compute mixed_rel=0.

+  compute prop_samegender=1.

Else.

+  compute mixed_rel=1.

+  compute prop_samegender=0.

End if."

Maguin, Eugene wrote
This builds on Rich’s reply. The addition is that, unless you want to, you don’t need to save two temp files.
Gene Maguin


Easier to harder
Mixed_Rel:
Prop_SameGender:
Do if (gender_resp eq gender_tie).
+  compute mixed_rel=0.
+  compute prop_samegender=1.
Else.
+  compute mixed_rel=1.
+  compute prop_samegender=0.
End if.

Aggregate outfile=* mode=addvariables/break=resp tie/n_same=nu.
Recode n_same(2 thru hi=0).
Execute.
Aggregate outfile=* mode=addvariables/break=resp/N_Contacts=sum(n_same).

Casestovars id=resp.












Long to wide (have you looked at the documentation?? Just curious.
Casestovars id=resp.

--  N_Contacts:

Mixed_Rel:

-- Prop_SameGender:


From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Chao yawo
Sent: Saturday, December 10, 2016 10:35 AM
To: [hidden email]
Subject: Help with Reshaping Data: Long to Wide ?

Hello,  Good Morning.  I need some help in converting this data from long to wide format
so that I can have one record for each respondent.

The dataset has the following types of variables:

Resp: is the ID for each respondent

Tie:  is the ID for each of the respondents contacts. Note each respondent can have a different number of non-overlapping contacts.

TieType:  an indicator of the type of relationship the respondent has with their contact
(1=sex; 2=social; 3=family; 4=boss). So respondent 1 for example has two types of relationship with Contact_1:
they both a sex and a social relationship.

Gender_Resp & Gender_Tie:  Indicates the gender of both respondent and their contact (tie)

Resp

Tie

TieType

Gender_Resp

Gender_Tie

1

1

1

1 (Male)

1 (Male)

1

1

2

1 (Male)

2 (Female)

1

3

3

1 (Male)

1 (Male)

1

4

4

1 (Male)

2 (Female)

2

6

1

1 (Male)

1 (Male)

2

8

2

1 (Male)

2 (Female)

3

34

1

2 (Female)

1 (Male)

3

15

1

2 (Female)

2 (Female)

3

100

2

2 (Female)

1 (Male)

3

100

2

2 (Female)

1 (Male)



Now, I want to create the following types of variable per each respondent.

--  N_Contacts: The absolute number of contacts they have:  So Respondent_1
will have 3 contacts (#1, 3, 4); same as Respondent_3 with 3 distinct contacts

-- Mixed_Rel:  The extent to which the respondent engages in a mixed relationship
(defined as sex + social) with his/her contacts.  So, Resp_1 has a sex+social relationship
with 1 out of 3 his contacts; while Resp_2 and Resp_3 do not engage in any mixed relationship with
any contact.

-- Prop_SameGender:  I want to find out the proportion of a respondents contacts that have same gender as he/she.
So, Resp_1 has same-gender relationship with 2 out of his 3 contacts (with contacts 1 & 3).


I would be very grateful for some pointers to create this summary file per respondent.

Thanks in advance.. Yy
===================== To manage your subscription to SPSSX-L, send a message to [hidden email]<mailto:[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

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

Re: Help with Reshaping Data: Long to Wide ?

Chao yawo-2
Thanks for all your responses.   I will implement them and get back to the list with responses and comments tomorrow.

cheers, Yy

On Mon, Dec 12, 2016 at 10:15 AM, David Marso <[hidden email]> wrote:
YIKES!
COMPUTE prop_samegender=gender_resp eq gender_tie.
COMPUTE mixed_rel = NOT(prop_samegender).

Instead of ...

"Do if (gender_resp eq gender_tie).

+  compute mixed_rel=0.

+  compute prop_samegender=1.

Else.

+  compute mixed_rel=1.

+  compute prop_samegender=0.

End if."


Maguin, Eugene wrote
> This builds on Rich’s reply. The addition is that, unless you want to, you
> don’t need to save two temp files.
> Gene Maguin
>
>
> Easier to harder
> Mixed_Rel:
> Prop_SameGender:
> Do if (gender_resp eq gender_tie).
> +  compute mixed_rel=0.
> +  compute prop_samegender=1.
> Else.
> +  compute mixed_rel=1.
> +  compute prop_samegender=0.
> End if.
>
> Aggregate outfile=* mode=addvariables/break=resp tie/n_same=nu.
> Recode n_same(2 thru hi=0).
> Execute.
> Aggregate outfile=* mode=addvariables/break=resp/N_Contacts=sum(n_same).
>
> Casestovars id=resp.
>
>
>
>
>
>
>
>
>
>
>
>
> Long to wide (have you looked at the documentation?? Just curious.
> Casestovars id=resp.
>
> --  N_Contacts:
>
> Mixed_Rel:
>
> -- Prop_SameGender:
>
>
> From: SPSSX(r) Discussion [mailto:

> SPSSX-L@.UGA

> ] On Behalf Of Chao yawo
> Sent: Saturday, December 10, 2016 10:35 AM
> To:

> SPSSX-L@.UGA

> Subject: Help with Reshaping Data: Long to Wide ?
>
> Hello,  Good Morning.  I need some help in converting this data from long
> to wide format
> so that I can have one record for each respondent.
>
> The dataset has the following types of variables:
>
> Resp: is the ID for each respondent
>
> Tie:  is the ID for each of the respondents contacts. Note each respondent
> can have a different number of non-overlapping contacts.
>
> TieType:  an indicator of the type of relationship the respondent has with
> their contact
> (1=sex; 2=social; 3=family; 4=boss). So respondent 1 for example has two
> types of relationship with Contact_1:
> they both a sex and a social relationship.
>
> Gender_Resp & Gender_Tie:  Indicates the gender of both respondent and
> their contact (tie)
>
> Resp
>
> Tie
>
> TieType
>
> Gender_Resp
>
> Gender_Tie
>
> 1
>
> 1
>
> 1
>
> 1 (Male)
>
> 1 (Male)
>
> 1
>
> 1
>
> 2
>
> 1 (Male)
>
> 2 (Female)
>
> 1
>
> 3
>
> 3
>
> 1 (Male)
>
> 1 (Male)
>
> 1
>
> 4
>
> 4
>
> 1 (Male)
>
> 2 (Female)
>
> 2
>
> 6
>
> 1
>
> 1 (Male)
>
> 1 (Male)
>
> 2
>
> 8
>
> 2
>
> 1 (Male)
>
> 2 (Female)
>
> 3
>
> 34
>
> 1
>
> 2 (Female)
>
> 1 (Male)
>
> 3
>
> 15
>
> 1
>
> 2 (Female)
>
> 2 (Female)
>
> 3
>
> 100
>
> 2
>
> 2 (Female)
>
> 1 (Male)
>
> 3
>
> 100
>
> 2
>
> 2 (Female)
>
> 1 (Male)
>
>
>
> Now, I want to create the following types of variable per each respondent.
>
> --  N_Contacts: The absolute number of contacts they have:  So
> Respondent_1
> will have 3 contacts (#1, 3, 4); same as Respondent_3 with 3 distinct
> contacts
>
> -- Mixed_Rel:  The extent to which the respondent engages in a mixed
> relationship
> (defined as sex + social) with his/her contacts.  So, Resp_1 has a
> sex+social relationship
> with 1 out of 3 his contacts; while Resp_2 and Resp_3 do not engage in any
> mixed relationship with
> any contact.
>
> -- Prop_SameGender:  I want to find out the proportion of a respondents
> contacts that have same gender as he/she.
> So, Resp_1 has same-gender relationship with 2 out of his 3 contacts (with
> contacts 1 & 3).
>
>
> I would be very grateful for some pointers to create this summary file per
> respondent.
>
> Thanks in advance.. Yy
> ===================== To manage your subscription to SPSSX-L, send a
> message to

> LISTSERV@.UGA

> &lt;mailto:

> LISTSERV@.UGA

> &gt; (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
>
> =====================
> To manage your subscription to SPSSX-L, send a message to

> LISTSERV@.UGA

>  (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?"
--
View this message in context: http://spssx-discussion.1045642.n5.nabble.com/Help-with-Reshaping-Data-Long-to-Wide-tp5733583p5733586.html
Sent from the SPSSX Discussion mailing list archive at Nabble.com.

=====================
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

===================== 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
Loading...