Creating a variable by grouping based on three variables

[Muhammad Arslan Iqbal]

Hi Everyone,

I am working with the following experiment data, where I have provided a sample below. The variables are

participantcode:
Unique for each person in a treatment,

Round:
Number of rounds for each person where max = 8 and each person has an observation for each round,

P2_group_id:
In each round, three players make a group and this is the group ID. In each round, players are randomly rematched again and new groups are formed,


T1_success:
Number of correct answers. These scores are given and does not change across rounds. The data on that is entered for all rounds for each player.

T2_success
Number of correct answers.for task2. These scores are given and does not change across rounds.The data on that is entered for all rounds for each player.

sessioncode:
Unique for each sessions. Two different sessions may have the same treatment but with different people.

Role:
Whether the person is selected as Leader or not. It is string variable with values "Leader" or "Member"

Role_V:
Whether the person was Voter initially or not. It is string variable with values "Voter", "Candidate 1", or "Candidate 2". In each group, one of these candidates is selected as the Leader.



Problem 1:
I want to create a variable that sorted by sessioncode Round P2_group_id. For each of these groups, I want to know the situation faced by voter. i.e. to generate variable x such that its value can be 1, 2, 3, 4, and 5 depending on the following:

* Situation: If the voter faced two candidates such that one candidate scored strictly better in both tasks                  ; Value = 1
*                 If the voter faced two candidates such that both scored the same in both tasks                                        ; Value = 2
*                 If the voter faced two candidates such that one candidate scored (strictly better in one task) AND (strictly worse in the other task)  ; Value = 3
*                 If the voter faced two candidates such that they scored same in Task 1 but their scores in Task 2 were not the same                       ; Value = 4
*                 If the voter faced two candidates such that they scored same in Task 2 but their scores in Task 1 were not the same                       ; Value = 5

Problem 2:
I want to create a variable that sorted by sessioncode Round P2_group_id. For each of these groups, I want to know what type of candidate is selected as the leader. i.e. to generate variable y such that its value can be 1, 2, 3, 4, 5 depending on the following:

* Situation: If the selected candidate (Leader) scored strictly better than the other Candidate in both tasks                           ; Value =1
*                 If the selected candidate (Leader) scored exactly same as the other Candidate in both tasks                              ; Value =2
*                 If the selected candidate (Leader) scored (strictly better than the other Candidate in Task 2 AND (worst or same as the Other Candidate in Task 1) ) OR (Same in Task 2 and worst in Task 1)    ; Value =3
*                 If the selected candidate (Leader) scored (strictly better than the other Candidate in Task 1 AND (worst or same as the Other Candidate in Task 2) ) OR (Same in Task 1 and worst in Task 2)    ; Value =4
*                 If the selected candidate (Leader) scored strictly worse than the other candidate in both tasks                                                                                                                                                            ; Value =5

Any hint to create such variable by using an example of conditions will be much appreciated. The data subset sample is below:

* Example generated by -dataex-. For more info, type help dataex
clear
input str8(participantcode sessioncode) byte(Round P2_group_id) str6 Role str11 Role_V byte(T1_success T2_success)
"06g44vsf" "2bihp1q9" 1 1 "Member" "Voter"       43 18
"pxq9m6zh" "2bihp1q9" 1 1 "Member" "Candidate 1" 29 13
"lb4ax3ma" "2bihp1q9" 1 1 "Leader" "Candidate 2" 27 15
"d4i5hwgv" "2bihp1q9" 1 2 "Leader" "Candidate 1" 43 15
"rnp9cb81" "2bihp1q9" 1 2 "Member" "Candidate 2" 36  9
"awwphjnn" "2bihp1q9" 1 2 "Member" "Voter"       19  6
"ojzfkhb0" "2bihp1q9" 1 3 "Member" "Voter"       41 10
"4n0o2elf" "2bihp1q9" 1 3 "Leader" "Candidate 1" 35  8
"rkay9scw" "2bihp1q9" 1 3 "Member" "Candidate 2" 22 13
"whiblk5s" "2bihp1q9" 1 4 "Leader" "Candidate 1" 45 18
"kox8u5ad" "2bihp1q9" 1 4 "Member" "Voter"       32 12
"whbzw8nj" "2bihp1q9" 1 4 "Member" "Candidate 2" 29  5
"udm3t6zf" "2bihp1q9" 1 5 "Member" "Candidate 2" 40 12
"om1a0s37" "2bihp1q9" 1 5 "Member" "Voter"       34 17
"2w2o6vk7" "2bihp1q9" 1 5 "Leader" "Candidate 1" 33 13
"lxa2cyq7" "2bihp1q9" 1 6 "Member" "Candidate 2" 35 16
"94pg2obl" "2bihp1q9" 1 6 "Member" "Voter"       27 10
"64rwlptn" "2bihp1q9" 1 6 "Leader" "Candidate 1" 23 12
"d4i5hwgv" "2bihp1q9" 2 1 "Member" "Voter"       43 15
"rkay9scw" "2bihp1q9" 2 1 "Member" "Candidate 1" 22 13
"awwphjnn" "2bihp1q9" 2 1 "Leader" "Candidate 2" 19  6
"ojzfkhb0" "2bihp1q9" 2 2 "Leader" "Candidate 1" 41 10
"om1a0s37" "2bihp1q9" 2 2 "Member" "Candidate 2" 34 17
"64rwlptn" "2bihp1q9" 2 2 "Member" "Voter"       23 12
"06g44vsf" "2bihp1q9" 2 3 "Member" "Voter"       43 18
"rnp9cb81" "2bihp1q9" 2 3 "Leader" "Candidate 1" 36  9
"94pg2obl" "2bihp1q9" 2 3 "Member" "Candidate 2" 27 10
"whiblk5s" "2bihp1q9" 2 4 "Member" "Candidate 1" 45 18
"4n0o2elf" "2bihp1q9" 2 4 "Member" "Voter"       35  8
"2w2o6vk7" "2bihp1q9" 2 4 "Leader" "Candidate 2" 33 13
"lxa2cyq7" "2bihp1q9" 2 5 "Member" "Voter"       35 16
"whbzw8nj" "2bihp1q9" 2 5 "Leader" "Candidate 1" 29  5
"pxq9m6zh" "2bihp1q9" 2 5 "Member" "Candidate 2" 29 13
"udm3t6zf" "2bihp1q9" 2 6 "Member" "Candidate 2" 40 12
"kox8u5ad" "2bihp1q9" 2 6 "Member" "Voter"       32 12
"lb4ax3ma" "2bihp1q9" 2 6 "Leader" "Candidate 1" 27 15
"rnp9cb81" "2bihp1q9" 3 1 "Member" "Candidate 1" 36  9
"pxq9m6zh" "2bihp1q9" 3 1 "Leader" "Candidate 2" 29 13
"rkay9scw" "2bihp1q9" 3 1 "Member" "Voter"       22 13
"4n0o2elf" "2bihp1q9" 3 2 "Member" "Voter"       35  8
"2w2o6vk7" "2bihp1q9" 3 2 "Leader" "Candidate 1" 33 13
"whbzw8nj" "2bihp1q9" 3 2 "Member" "Candidate 2" 29  5
"whiblk5s" "2bihp1q9" 3 3 "Member" "Voter"       45 18
"d4i5hwgv" "2bihp1q9" 3 3 "Leader" "Candidate 1" 43 15
"ojzfkhb0" "2bihp1q9" 3 3 "Member" "Candidate 2" 41 10
"06g44vsf" "2bihp1q9" 3 4 "Member" "Candidate 1" 43 18
"lb4ax3ma" "2bihp1q9" 3 4 "Member" "Voter"       27 15
"awwphjnn" "2bihp1q9" 3 4 "Leader" "Candidate 2" 19  6
"om1a0s37" "2bihp1q9" 3 5 "Member" "Candidate 1" 34 17
"kox8u5ad" "2bihp1q9" 3 5 "Leader" "Candidate 2" 32 12
"94pg2obl" "2bihp1q9" 3 5 "Member" "Voter"       27 10
"udm3t6zf" "2bihp1q9" 3 6 "Leader" "Candidate 2" 40 12
"lxa2cyq7" "2bihp1q9" 3 6 "Member" "Candidate 1" 35 16
"64rwlptn" "2bihp1q9" 3 6 "Member" "Voter"       23 12
"lxa2cyq7" "2bihp1q9" 4 1 "Leader" "Candidate 2" 35 16
"kox8u5ad" "2bihp1q9" 4 1 "Member" "Candidate 1" 32 12
"awwphjnn" "2bihp1q9" 4 1 "Member" "Voter"       19  6
"rnp9cb81" "2bihp1q9" 4 2 "Leader" "Candidate 2" 36  9
"4n0o2elf" "2bihp1q9" 4 2 "Member" "Voter"       35  8
"94pg2obl" "2bihp1q9" 4 2 "Member" "Candidate 1" 27 10
"whiblk5s" "2bihp1q9" 4 3 "Member" "Candidate 1" 45 18
"d4i5hwgv" "2bihp1q9" 4 3 "Member" "Voter"       43 15
"whbzw8nj" "2bihp1q9" 4 3 "Leader" "Candidate 2" 29  5
"udm3t6zf" "2bihp1q9" 4 4 "Member" "Candidate 2" 40 12
"om1a0s37" "2bihp1q9" 4 4 "Leader" "Candidate 1" 34 17
"lb4ax3ma" "2bihp1q9" 4 4 "Member" "Voter"       27 15
"pxq9m6zh" "2bihp1q9" 4 5 "Leader" "Candidate 2" 29 13
"64rwlptn" "2bihp1q9" 4 5 "Member" "Candidate 1" 23 12
"rkay9scw" "2bihp1q9" 4 5 "Member" "Voter"       22 13
"06g44vsf" "2bihp1q9" 4 6 "Member" "Voter"       43 18
"ojzfkhb0" "2bihp1q9" 4 6 "Member" "Candidate 1" 41 10
"2w2o6vk7" "2bihp1q9" 4 6 "Leader" "Candidate 2" 33 13
"rnp9cb81" "2bihp1q9" 5 1 "Member" "Candidate 1" 36  9
"4n0o2elf" "2bihp1q9" 5 1 "Member" "Voter"       35  8
"lb4ax3ma" "2bihp1q9" 5 1 "Leader" "Candidate 2" 27 15
"d4i5hwgv" "2bihp1q9" 5 2 "Member" "Candidate 1" 43 15
"lxa2cyq7" "2bihp1q9" 5 2 "Member" "Voter"       35 16
"rkay9scw" "2bihp1q9" 5 2 "Leader" "Candidate 2" 22 13
"ojzfkhb0" "2bihp1q9" 5 3 "Member" "Candidate 1" 41 10
"udm3t6zf" "2bihp1q9" 5 3 "Member" "Voter"       40 12
"whbzw8nj" "2bihp1q9" 5 3 "Leader" "Candidate 2" 29  5
"whiblk5s" "2bihp1q9" 5 4 "Member" "Voter"       45 18
"kox8u5ad" "2bihp1q9" 5 4 "Leader" "Candidate 2" 32 12
"64rwlptn" "2bihp1q9" 5 4 "Member" "Candidate 1" 23 12
"06g44vsf" "2bihp1q9" 5 5 "Leader" "Candidate 1" 43 18
"om1a0s37" "2bihp1q9" 5 5 "Member" "Candidate 2" 34 17
"94pg2obl" "2bihp1q9" 5 5 "Member" "Voter"       27 10
"2w2o6vk7" "2bihp1q9" 5 6 "Member" "Voter"       33 13
"pxq9m6zh" "2bihp1q9" 5 6 "Member" "Candidate 2" 29 13
"awwphjnn" "2bihp1q9" 5 6 "Leader" "Candidate 1" 19  6
"06g44vsf" "2bihp1q9" 6 1 "Leader" "Candidate 2" 43 18
"whbzw8nj" "2bihp1q9" 6 1 "Member" "Candidate 1" 29  5
"awwphjnn" "2bihp1q9" 6 1 "Member" "Voter"       19  6
"om1a0s37" "2bihp1q9" 6 2 "Member" "Voter"       34 17
"94pg2obl" "2bihp1q9" 6 2 "Member" "Candidate 1" 27 10
"64rwlptn" "2bihp1q9" 6 2 "Leader" "Candidate 2" 23 12
"udm3t6zf" "2bihp1q9" 6 3 "Member" "Candidate 1" 40 12
"4n0o2elf" "2bihp1q9" 6 3 "Leader" "Candidate 2" 35  8
"lxa2cyq7" "2bihp1q9" 6 3 "Member" "Voter"       35 16
"d4i5hwgv" "2bihp1q9" 6 4 "Member" "Candidate 1" 43 15
end

[Muhammad Arslan Iqbal]

Problem 1 is resolved now by using this code. I am not sure whether there is an efficient way to do this.

local grouping sessioncode Round P2_group_id
sort `grouping'
by `grouping': gen C1T1a = T1_success if Role_V=="Candidate 1"
by `grouping': gen C2T1a = T1_success if Role_V=="Candidate 2"
by `grouping': gen C1T2a = T2_success if Role_V=="Candidate 1"
by `grouping': gen C2T2a = T2_success if Role_V=="Candidate 2"
by `grouping': egen C1T1 = max(C1T1a) 
by `grouping': egen C2T1 = max(C2T1a) 
by `grouping': egen C1T2 = max(C1T2a) 
by `grouping': egen C2T2 = max(C2T2a) 
order Treatment sessioncode Round P2_group_id P2_player_id T1_success T2_success C1T1a C2T1a C1T2a C2T2a C1T1 C1T2 C2T1 C2T2 , after(participantlabel)
gen V_group = 6  //To check if some observation is missed
replace V_group = 1 if ( (C1T1>C2T1 & C1T2>C2T2) | (C1T1<C2T1 & C1T2<C2T2) )  //One of them did well in both
replace V_group = 2 if (C1T1==C2T1 & C1T2==C2T2) // Both of them did equally well in both
replace V_group = 3 if ( (C1T1>C2T1 & C1T2<C2T2) | (C1T1<C2T1 & C1T2>C2T2) )   // Strictly better in one task and worse in the other
replace V_group = 4 if (C1T1==C2T1 & C1T2!=C2T2)  // Same in Task 1 but different in Task 2
replace V_group = 5 if (C1T1!=C2T1 & C1T2==C2T2) // Same in Task 2 but different in Task 1

[Muhammad Arslan Iqbal]

Thank you everyone. The second problem is also solved. If you can suggest an efficient way to solve this then please share Or if you find any error that is much appreciated.

local grouping sessioncode Round P2_group_id
sort `grouping'
by `grouping': gen LT1a = T1_success if Role_V!="Voter" & Role == "Leader"
by `grouping': gen OT1a = T1_success if (Role_V=="Candidate 1" | Role_V =="Candidate 2") & Role != "Leader"
by `grouping': gen LT2a = T2_success if Role_V!="Voter" & Role == "Leader"
by `grouping': gen OT2a = T2_success if (Role_V=="Candidate 1" | Role_V =="Candidate 2") & Role != "Leader"
by `grouping': egen LT1 = max(LT1a) 
by `grouping': egen OT1 = max(OT1a) 
by `grouping': egen LT2 = max(LT2a) 
by `grouping': egen OT2 = max(OT2a) 
gen L_group = 6  //To check if some observation is missed
replace L_group = 1 if ( LT1>OT1 & LT2>OT2)   //Voted Leader did well in both
replace L_group = 2 if (LT1==OT1 & LT2==OT2) // Voted Leader did equally well in both
replace L_group = 3 if (LT2>OT2 & LT1 <= OT1) | (LT2==OT2 & LT1 < OT1 )   // Selected based on Task 2
replace L_group = 4 if (LT1>OT1 & LT2 <= OT2) | (LT1==OT1 & LT2 < OT2 )   // Selected based on Task 2
replace L_group = 5 if (LT1<OT1 & LT2<OT2)   // Voted leader did worse in both

[Nick Cox]

If I understand correctly, this

ocal grouping sessioncode Round P2_group_id
sort `grouping'
by `grouping': gen C1T1a = T1_success if Role_V=="Candidate 1"
by `grouping': gen C2T1a = T1_success if Role_V=="Candidate 2"
by `grouping': gen C1T2a = T2_success if Role_V=="Candidate 1"
by `grouping': gen C2T2a = T2_success if Role_V=="Candidate 2"
by `grouping': egen C1T1 = max(C1T1a) 
by `grouping': egen C2T1 = max(C2T1a) 
by `grouping': egen C1T2 = max(C1T2a) 
by `grouping': egen C2T2 = max(C2T2a)

can be slimmed to this

local grouping sessioncode Round P2_group_id
bysort `grouping': egen C1T1 = max(cond(Role_V == "Candidate 1", T1_success, .))  
by `grouping': egen C2T1 = max(cond(Role_V == "Candidate 2", T1_success, .))  
by `grouping': egen C1T2 = max(cond(Role_V == "Candidate 1", T2_success, .))  
by `grouping': egen C2T2 = max(cond(Role_V == "Candidate 2", T2_success, .))

[Muhammad Arslan Iqbal]

Thank you Nick for suggesting this efficient method. That is what I was looking for. Can you please suggest a link on instructions for using this cond() inside max function for "egen" command. I have read the help manual of egen but could not find such option as an example.

[Nick Cox]

See the paper Compared with… in the Stata Journal. Section 9 applies.