New dataset from long to wide - joint project participation

[Oscar Ll]

Dear Stata users,

I have a dataset with Project identifiers (id_project), and Person identifiers (id_person). I also have another variable (name_surname), which is the name and surname associated to each id_person. Each observation in the dataset represents a link between a project and a person.

Departing from this dataset, what I would like is to create a new dataset, where each observation corresponds different individual (id_person). Then, I want to create new variables for each individual to reflect co-participation in projects with others. Thus, for each observation, I want to make different columns with the codes and names of all other individuals with whom the focal individual has shared participation in any project. Note that persons can be attached to more than one different project, and that the number of participants in each project can vary.

This is what my dataset looks like (I've replaced the original names with XXX):

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 id_project float(id_person id_participant) str39 name_surname
"2017_01"  77 109 "XXX"         
"2017_01" 233 383 "XXX"     
"2017_01" 419 682 "XXX"          
"2017_01" 464 770 "XXX"       
"2017_01" 494 831 "XXX"      
"2017_01" 539 915 "XXX"
"2017_02" 122 187 "XXX"     
"2017_02" 131 208 "XXX"      
"2017_02" 257 426 "XXX" 
"2017_02" 274 459 "XXX"  
"2017_02" 319 524 "XXX"      
"2017_02" 332 540 "XXX"  
"2017_03"  95 146 "XXX"
"2017_03" 195 318 "XXX"        
"2017_03" 208 339 "XXX"      
"2017_03" 246 410 "XXX"   
"2017_03" 334 546 "XXX"
"2017_03" 388 641 "XXX"        
"2017_03" 484 810 "XXX"
"2017_03" 509 864 "XXX"   
end

So, I'd like to create something like this, for each individual:

I've been trying to solve it with reshape wide, but I have not managed to solve it.
Any help would be greatly appreciated. Many thanks !!

Best,
Oscar

[Hemanshu Kumar]

Consider this:

drop id_participant

tempfile main
save `main'

rename id_person colleague_id
rename name_surname colleague_name

tempfile colleagues
save `colleagues'

use `main', clear
joinby id_project using `colleagues'

drop if id_person == colleague_id

drop id_project name_surname
duplicates drop id_person colleague_id, force

bysort id_person (colleague_id): gen colleague_num = _n

reshape wide colleague@_id colleague@_name, i(id_person) j(colleague_num)
compress

which produces:

. list, noobs abbrev(20) sep(0)

  +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | id_person   colleague1_id   colleague1_name   colleague2_id   colleague2_name   colleague3_id   colleague3_name   colleague4_id   colleague4_name   colleague5_id   colleague5_name   colleague6_id   colleague6_name   colleague7_id   colleague7_name |
  |---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
  |        77             233               XXX             419               XXX             464               XXX             494               XXX             539               XXX               .                                 .                   |
  |        95             195               XXX             208               XXX             246               XXX             334               XXX             388               XXX             484               XXX             509               XXX |
  |       122             131               XXX             257               XXX             274               XXX             319               XXX             332               XXX               .                                 .                   |
  |       131             122               XXX             257               XXX             274               XXX             319               XXX             332               XXX               .                                 .                   |
  |       195              95               XXX             208               XXX             246               XXX             334               XXX             388               XXX             484               XXX             509               XXX |
  |       208              95               XXX             195               XXX             246               XXX             334               XXX             388               XXX             484               XXX             509               XXX |
  |       233              77               XXX             419               XXX             464               XXX             494               XXX             539               XXX               .                                 .                   |
  |       246              95               XXX             195               XXX             208               XXX             334               XXX             388               XXX             484               XXX             509               XXX |
  |       257             122               XXX             131               XXX             274               XXX             319               XXX             332               XXX               .                                 .                   |
  |       274             122               XXX             131               XXX             257               XXX             319               XXX             332               XXX               .                                 .                   |
  |       319             122               XXX             131               XXX             257               XXX             274               XXX             332               XXX               .                                 .                   |
  |       332             122               XXX             131               XXX             257               XXX             274               XXX             319               XXX               .                                 .                   |
  |       334              95               XXX             195               XXX             208               XXX             246               XXX             388               XXX             484               XXX             509               XXX |
  |       388              95               XXX             195               XXX             208               XXX             246               XXX             334               XXX             484               XXX             509               XXX |
  |       419              77               XXX             233               XXX             464               XXX             494               XXX             539               XXX               .                                 .                   |
  |       464              77               XXX             233               XXX             419               XXX             494               XXX             539               XXX               .                                 .                   |
  |       484              95               XXX             195               XXX             208               XXX             246               XXX             334               XXX             388               XXX             509               XXX |
  |       494              77               XXX             233               XXX             419               XXX             464               XXX             539               XXX               .                                 .                   |
  |       509              95               XXX             195               XXX             208               XXX             246               XXX             334               XXX             388               XXX             484               XXX |
  |       539              77               XXX             233               XXX             419               XXX             464               XXX             494               XXX               .                                 .                   |
  +---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

[Oscar Ll]

Many thanks !!!! That worked perfectly.

One additional question: would it be possible to create an additional variable, -for each colleague-, indicating the number of different projects in which the focal individual has collaborated with each of her contacts?
For instance: let's say that, if id_person=77 has collaborated in two different projects with colleague_1 (233), a new variable ('colleague_1_strenght'), would equal 2, and so on. The same with all colleagues.

Thanks again,
Oscar

[Hemanshu Kumar]

Your data extract wasn't illustrative of this bit (all id_person were unique and so strength would always be 1), so I have used a modified dataset, where #77 and #233 appear in two projects together:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str10 id_project float(id_person id_participant) str39 name_surname
"2017_01"  77 109 "XXX"        
"2017_01" 233 383 "XXX"    
"2017_01" 419 682 "XXX"          
"2017_02"  77 109 "XXX"
"2017_02" 233 383 "XXX"    
"2017_02" 122 187 "XXX"    
"2017_02" 131 208 "XXX"      
"2017_02" 257 426 "XXX"
end

drop id_participant

tempfile main
save `main'

rename id_person colleague_id
rename name_surname colleague_name

tempfile colleagues
save `colleagues'

use `main', clear
joinby id_project using `colleagues'

drop if id_person == colleague_id
bysort id_person colleague_id: gen colleague_strength = _N

drop id_project name_surname
duplicates drop id_person colleague_id, force

by id_person: gen colleague_num = _n

reshape wide colleague@_id colleague@_name colleague@_strength, i(id_person) j(colleague_num)
compress

which produces

. list , noobs abbrev(10) sep(0)

  +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
  | id_person   colle~1_id   col~1_name   colleagu..   colle~2_id   col~2_name   colleagu..   colle~3_id   col~3_name   colleagu..   colle~4_id   col~4_name   colleagu..   colle~5_id   col~5_name   colleagu.. |
  |--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
  |        77          122          XXX            1          131          XXX            1          233          XXX            2          257          XXX            1          419          XXX            1 |
  |       122           77          XXX            1          131          XXX            1          233          XXX            1          257          XXX            1            .                         . |
  |       131           77          XXX            1          122          XXX            1          233          XXX            1          257          XXX            1            .                         . |
  |       233           77          XXX            2          122          XXX            1          131          XXX            1          257          XXX            1          419          XXX            1 |
  |       257           77          XXX            1          122          XXX            1          131          XXX            1          233          XXX            1            .                         . |
  |       419           77          XXX            1          233          XXX            1            .                         .            .                         .            .                         . |
  +--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

[Oscar Ll]

Thank you very much !! You made my day !
The code worked perfectly.