Rank individual with characteristic A against members with characteristic B

[Prashant Loyalka]

Hi,

I am unsure of how to write code for the following situation. I have a data set of students nested in classes. I would like to rank each male student in each class against the female students in the class (but not against all the students in the class). I would like to rank them using "math score". In other words, in Class A, I want to rank Bob's math score against Allie, Kim, and Sally but not against, say, Sam and Joe. I would like to loop through the entire dataset and do this for each male student.

I am using Stata version 15.1 on Windows 10.

Best wishes and many thanks in advance,

Prashant Loyalka
Assistant Professor
Stanford University

[Clyde Schechter]

What does it mean to "rank" Bob's score against Allie, Kim, and Sally? I suppose that if Allie's score > Kim's, and Kim's > Sally's, then Allie, Kim, and Sally themselves rank as 1, 2, and 3 respectively. So if Bob's score equals, say, Kim's, then Bob presumably Bob's rank against them would be 2. Am I right so far?

But what if Bob's score is between Allie's and Kim's? What if Bob beats all three, or falls below all three?

Added: And what happens if Allie and Kim have the same score?

[William Lisowski]

Welcome to Statalist.

Please review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post, looking especially at sections 9-12 on how to best pose your question.

I am afraid that "rank each male's math score against each of the females" doesn't really explain to me what the result will be. If Bob's math score exceeds Allie's, ties Kim's, and is less than Sally's, what would Bob's "rank" be, assuming that class A consists of the six students you named.

Even the best descriptions of data are no substitute for an actual example of the data. This problem is sufficiently complex that whoever helps you will want to be able to test their code in an example of your data.

Please be sure to use the dataex command to show your example data. If you are running version 15.1 or a fully updated version 14.2, it is already part of your official Stata installation. If not, run ssc install dataex to get it. Either way, run help dataex and read the simple instructions for using it. dataex will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use dataex.

[Joseph Coveney]

I would like to rank each male student in each class against the female students in the class . . . I would like to rank them using "math score". . . . I would like to loop through the entire dataset and do this for each male student.

Maybe something like the following?

.ÿversionÿ15.1

.ÿ
.ÿclearÿ*

.ÿsetÿseedÿ`=strreverse("1471864")'

.ÿ
.ÿinputÿstr1ÿsexÿstr10ÿgiven_name

ÿÿÿÿÿÿÿÿÿÿÿsexÿÿgiven_name
ÿÿ1.ÿFÿAllie
ÿÿ2.ÿFÿKim
ÿÿ3.ÿFÿSally
ÿÿ4.ÿMÿBob
ÿÿ5.ÿMÿSam
ÿÿ6.ÿMÿJoe
ÿÿ7.ÿend

.ÿ
.ÿgenerateÿbyteÿmath_scoreÿ=ÿruniformint(65,ÿ100)

.ÿ
.ÿ*
.ÿ*ÿBeginÿhere
.ÿ*
.ÿ
.ÿpreserve

.ÿquietlyÿkeepÿifÿsexÿ==ÿ"F"

.ÿtempfileÿgirls

.ÿquietlyÿsaveÿ`girls'

.ÿ
.ÿrestore

.ÿquietlyÿdropÿifÿsexÿ==ÿ"F"

.ÿassertÿsexÿ==ÿ"M"

.ÿtempfileÿboys

.ÿquietlyÿsaveÿ`boys'

.ÿ
.ÿquietlyÿcount

.ÿlocalÿboy_countÿ=ÿr(N)

.ÿ
.ÿtempfileÿranked

.ÿdropÿ_all

.ÿquietlyÿsaveÿ`ranked',ÿemptyok

.ÿ
.ÿforvaluesÿrowÿ=ÿ1/`boy_count'ÿ{
ÿÿ2.ÿÿÿÿÿÿÿÿÿquietlyÿuseÿ`boys'ÿinÿ`row',ÿclear
ÿÿ3.ÿÿÿÿÿÿÿÿÿquietlyÿappendÿusingÿ`girls'
ÿÿ4.ÿÿÿÿÿÿÿÿÿegenÿdoubleÿrankÿ=ÿrank(math_score)
ÿÿ5.ÿÿÿÿÿÿÿÿÿquietlyÿkeepÿifÿsexÿ==ÿ"M"
ÿÿ6.ÿÿÿÿÿÿÿÿÿquietlyÿappendÿusingÿ`ranked'
ÿÿ7.ÿÿÿÿÿÿÿÿÿquietlyÿsaveÿ`ranked',ÿreplace
ÿÿ8.ÿ}

.ÿ
.ÿappendÿusingÿ`girls'

.ÿ
.ÿlist,ÿnoobsÿsepby(sex)ÿabbreviate(20)

ÿÿ+--------------------------------------+
ÿÿ|ÿsexÿÿÿgiven_nameÿÿÿmath_scoreÿÿÿrankÿ|
ÿÿ|--------------------------------------|
ÿÿ|ÿÿÿMÿÿÿÿÿÿÿÿÿÿJoeÿÿÿÿÿÿÿÿÿÿÿ79ÿÿÿÿÿÿ2ÿ|
ÿÿ|ÿÿÿMÿÿÿÿÿÿÿÿÿÿSamÿÿÿÿÿÿÿÿÿÿÿ86ÿÿÿÿÿÿ3ÿ|
ÿÿ|ÿÿÿMÿÿÿÿÿÿÿÿÿÿBobÿÿÿÿÿÿÿÿÿÿÿ84ÿÿÿÿ2.5ÿ|
ÿÿ|--------------------------------------|
ÿÿ|ÿÿÿFÿÿÿÿÿÿÿÿAllieÿÿÿÿÿÿÿÿÿÿÿ74ÿÿÿÿÿÿ.ÿ|
ÿÿ|ÿÿÿFÿÿÿÿÿÿÿÿÿÿKimÿÿÿÿÿÿÿÿÿÿÿ84ÿÿÿÿÿÿ.ÿ|
ÿÿ|ÿÿÿFÿÿÿÿÿÿÿÿSallyÿÿÿÿÿÿÿÿÿÿÿ88ÿÿÿÿÿÿ.ÿ|
ÿÿ+--------------------------------------+

.ÿ
.ÿexit

endÿofÿdo-file


.

Done for one class; you'd loop over classes in the same manner.

[David Benson]

I'm joining in late, but in case it helps, I created some data to help make things concrete. So I've created 2 classes, class 1 has 12 students (8 male, 4 female), class 2 has 18 (8 male, 10 female). The match scores are a random number between 20 and 50. The numbers are small, but I wanted to give you a variety so you could see how you wanted to score / rank them, in particular thinking about how you want to handle ties and how to handle scores above all of the female scores and scores below all of the female scores.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(student_id class_id math_score male)
 1 1 27 1
 2 1 37 1
 3 1 42 1
 4 1 45 1
 5 1 31 1
 6 1 30 1
 7 1 22 1
 8 1 26 1
 9 1 34 0
10 1 24 0
11 1 41 0
12 1 34 0
13 2 30 1
14 2 31 1
15 2 33 1
16 2 44 1
17 2 29 1
18 2 22 1
19 2 38 1
20 2 44 1
21 2 25 0
22 2 48 0
23 2 25 0
24 2 50 0
25 2 34 0
26 2 33 0
27 2 24 0
28 2 38 0
29 2 31 0
30 2 37 0
end

I've posted the data above as a screenshot as well, just splitting it out by class_id and gender.

gsort class_id male  -math_score  // sorting by class & gender, math_score from highest to lowest
by class_id male: egen math_rank = rank(math_score)  // egen rank puts rank==1 means lowest score
by class_id male: egen math_fieldrank = rank(math_score), field   // egen rank, field, means rank==1 is highest score

list student_id class_id male math_score  math_rank math_fieldrank, sepby( class_id male)


     +-------------------------------------------------------------+
     | studen~d   class_id   male   math_s~e   math_r~k   math_f~k |
     |-------------------------------------------------------------|
  1. |       11          1      0         41          4          1 |
  2. |        9          1      0         34        2.5          2 |
  3. |       12          1      0         34        2.5          2 |
  4. |       10          1      0         24          1          4 |
     |-------------------------------------------------------------|
  5. |        4          1      1         45          8          1 |
  6. |        3          1      1         42          7          2 |
  7. |        2          1      1         37          6          3 |
  8. |        5          1      1         31          5          4 |
  9. |        6          1      1         30          4          5 |
 10. |        1          1      1         27          3          6 |
 11. |        8          1      1         26          2          7 |
 12. |        7          1      1         22          1          8 |
     |-------------------------------------------------------------|
 13. |       24          2      0         50         10          1 |
 14. |       22          2      0         48          9          2 |
 15. |       28          2      0         38          8          3 |
 16. |       30          2      0         37          7          4 |
 17. |       25          2      0         34          6          5 |
 18. |       26          2      0         33          5          6 |
 19. |       29          2      0         31          4          7 |
 20. |       23          2      0         25        2.5          8 |
 21. |       21          2      0         25        2.5          8 |
 22. |       27          2      0         24          1         10 |
     |-------------------------------------------------------------|
 23. |       16          2      1         44        7.5          1 |
 24. |       20          2      1         44        7.5          1 |
 25. |       19          2      1         38          6          3 |
 26. |       15          2      1         33          5          4 |
 27. |       14          2      1         31          4          5 |
 28. |       13          2      1         30          3          6 |
 29. |       17          2      1         29          2          7 |
 30. |       18          2      1         22          1          8 |
     +-------------------------------------------------------------+

Note: Since I've ranked them above by male and female, this won't get you what you want. I just thought that having an example would make it easier to explain what you want.

[Prashant Loyalka]

Sincere thanks to all of you. The clarifying questions, advice, and example data are much appreciated. I will be sure to show example data using dataex in the future.

Joseph Coveney's example and approach is exactly what I was looking for.