Mean Value by Multiple Criteria

[Hans Schubert]

Good morning,

Would appreciate some help with the attached please.

In the wanted column the mean value of previous scores are required, grouped by animal.
There are some criteria for the mean calculation...
Grouped by animal
Mean of Previous ddates Only
Use score of walkmiles that are in excess of walkmiles for the current ddate only

example
10jan2024 - Walkmiles = 6 - therefore only scores with walkmiles in excess of 6 are used in the calculation. Which for the Dog are Scores of 9 + 8 + 7 = 24. The mean of which 8 is returned in the Wanted column.

Thanks

* Example generated by -dataex-. For more info, type help dataex
clear
input float ddate str3 animal byte(walkmiles score) float wanted
23376 "Dog" 10   9     .
23377 "Dog" 10   8     .
23378 "Dog"  3 100     .
23379 "Dog"  1 100     .
23380 "Dog"  2 100     .
23381 "Dog"  4 100     .
23382 "Dog" 10   7     .
23383 "Dog"  6 100     .
23384 "Dog"  5 100     .
23385 "Dog"  6 100     8
23386 "Cat" 10  12     .
23387 "Cat" 10  11     .
23388 "Cat"  3 100     .
23389 "Cat"  1 100     .
23390 "Cat"  2 100     .
23391 "Cat"  4 100     .
23392 "Cat" 10   8     .
23393 "Cat"  6 100     .
23394 "Cat"  5 100     .
23395 "Cat"  6 100     .
23396 "Cat"  5 100     .
23397 "Cat"  8 100 13.67
end
format %td ddate

[Nick Cox]

This may help. I can't see where 13.67 comes from in your example.

* Example generated by -dataex-. For more info, type help dataex
clear
input float ddate str3 animal byte(walkmiles score) float wanted
23376 "Dog" 10   9     .
23377 "Dog" 10   8     .
23378 "Dog"  3 100     .
23379 "Dog"  1 100     .
23380 "Dog"  2 100     .
23381 "Dog"  4 100     .
23382 "Dog" 10   7     .
23383 "Dog"  6 100     .
23384 "Dog"  5 100     .
23385 "Dog"  6 100     8
23386 "Cat" 10  12     .
23387 "Cat" 10  11     .
23388 "Cat"  3 100     .
23389 "Cat"  1 100     .
23390 "Cat"  2 100     .
23391 "Cat"  4 100     .
23392 "Cat" 10   8     .
23393 "Cat"  6 100     .
23394 "Cat"  5 100     .
23395 "Cat"  6 100     .
23396 "Cat"  5 100     .
23397 "Cat"  8 100 13.67
end
format %td ddate

bysort animal (ddate) : gen reference = walkmiles[_N]

by animal: egen WANTED = mean(cond(walkmiles > reference, score, .))

list, sepby(animal)

     +----------------------------------------------------------------------+
     |     ddate   animal   walkmi~s   score   wanted   refere~e     WANTED |
     |----------------------------------------------------------------------|
  1. | 11jan2024      Cat         10      12        .          8   10.33333 |
  2. | 12jan2024      Cat         10      11        .          8   10.33333 |
  3. | 13jan2024      Cat          3     100        .          8   10.33333 |
  4. | 14jan2024      Cat          1     100        .          8   10.33333 |
  5. | 15jan2024      Cat          2     100        .          8   10.33333 |
  6. | 16jan2024      Cat          4     100        .          8   10.33333 |
  7. | 17jan2024      Cat         10       8        .          8   10.33333 |
  8. | 18jan2024      Cat          6     100        .          8   10.33333 |
  9. | 19jan2024      Cat          5     100        .          8   10.33333 |
 10. | 20jan2024      Cat          6     100        .          8   10.33333 |
 11. | 21jan2024      Cat          5     100        .          8   10.33333 |
 12. | 22jan2024      Cat          8     100    13.67          8   10.33333 |
     |----------------------------------------------------------------------|
 13. | 01jan2024      Dog         10       9        .          6          8 |
 14. | 02jan2024      Dog         10       8        .          6          8 |
 15. | 03jan2024      Dog          3     100        .          6          8 |
 16. | 04jan2024      Dog          1     100        .          6          8 |
 17. | 05jan2024      Dog          2     100        .          6          8 |
 18. | 06jan2024      Dog          4     100        .          6          8 |
 19. | 07jan2024      Dog         10       7        .          6          8 |
 20. | 08jan2024      Dog          6     100        .          6          8 |
 21. | 09jan2024      Dog          5     100        .          6          8 |
 22. | 10jan2024      Dog          6     100        8          6          8 |
     +----------------------------------------------------------------------+

[Hans Schubert]

Thanks, Nick. Need it to lookup previous dates only. Putting 10 in the final date returns "." in the wanted column.
Similarly for cat on 21jan2024 - need it to lookup all dates previous to 21jan2024 and return the mean score for all walkmiles greater than 5. All those wanted observations should be populated. Hope thats clear. Thanks again.

[Nick Cox]

Sorry, but #2 doesn't clarify anything for me. I get 12, 11 and 8 as scores to be averaged for cat, so 31/3. Where does 13,67 come from?

[Hans Schubert]

Sorry thats a mistake. Your correct, should be 10.33 as you've calculated.

[Hans Schubert]

Good morning
Just coming back to this again...

* Example generated by -dataex-. For more info, type help dataex
clear
input float ddate str3 animal byte(walkmiles score)
23386 "Cat" 10  12
23387 "Cat" 10  11
23388 "Cat"  3 100
23389 "Cat"  1 100
23390 "Cat"  2 100
23391 "Cat"  4 100
23392 "Cat" 10   8
23393 "Cat"  6 100
23394 "Cat"  5 100
23395 "Cat"  6 100
23396 "Cat"  5 100
23397 "Cat"  8 100
23376 "Dog" 10   9
23377 "Dog" 10   8
23378 "Dog"  3 100
23379 "Dog"  1 100
23380 "Dog"  2 100
23381 "Dog"  4 100
23382 "Dog" 10   7
23383 "Dog"  6 100
23384 "Dog"  5 100
23385 "Dog"  6 100
end
format %td ddate 
 bysort animal (ddate) : gen reference = walkmiles[_N]  by animal: egen WANTED = mean(cond(walkmiles > reference, score, .))

Right now it is calculating the mean score for all walkmiles in excess of the reference for the last observation in the group (animal) only Could it be coded to do the same calculation for every observation in the group (animal)? For example in ROW 11 ...the mean score for all previous walkmiles in excess of 5 would be calculated Thanks

[Nick Cox]

This may help. "Previous" meant exclusively is just a variation on this, i.e. the last expression used should be

bysort animal : gen wanted = numer[_n-1] / denom[_n-1]

* Example generated by -dataex-. For more info, type help dataex
clear
input float ddate str3 animal byte(walkmiles score)
23386 "Cat" 10  12
23387 "Cat" 10  11
23388 "Cat"  3 100
23389 "Cat"  1 100
23390 "Cat"  2 100
23391 "Cat"  4 100
23392 "Cat" 10   8
23393 "Cat"  6 100
23394 "Cat"  5 100
23395 "Cat"  6 100
23396 "Cat"  5 100
23397 "Cat"  8 100
23376 "Dog" 10   9
23377 "Dog" 10   8
23378 "Dog"  3 100
23379 "Dog"  1 100
23380 "Dog"  2 100
23381 "Dog"  4 100
23382 "Dog" 10   7
23383 "Dog"  6 100
23384 "Dog"  5 100
23385 "Dog"  6 100
end

bysort animal (ddate) : gen numer = sum(cond(walkmiles > 5 & walkmiles < ., walkmiles, 0))
by animal : gen denom = sum(walkmiles > 5 & walkmiles < .)

gen wanted = numer / denom

list, sepby(animal)

     +--------------------------------------------------------------+
     | ddate   animal   walkmi~s   score   numer   denom     wanted |
     |--------------------------------------------------------------|
  1. | 23386      Cat         10      12      10       1         10 |
  2. | 23387      Cat         10      11      20       2         10 |
  3. | 23388      Cat          3     100      20       2         10 |
  4. | 23389      Cat          1     100      20       2         10 |
  5. | 23390      Cat          2     100      20       2         10 |
  6. | 23391      Cat          4     100      20       2         10 |
  7. | 23392      Cat         10       8      30       3         10 |
  8. | 23393      Cat          6     100      36       4          9 |
  9. | 23394      Cat          5     100      36       4          9 |
 10. | 23395      Cat          6     100      42       5        8.4 |
 11. | 23396      Cat          5     100      42       5        8.4 |
 12. | 23397      Cat          8     100      50       6   8.333333 |
     |--------------------------------------------------------------|
 13. | 23376      Dog         10       9      10       1         10 |
 14. | 23377      Dog         10       8      20       2         10 |
 15. | 23378      Dog          3     100      20       2         10 |
 16. | 23379      Dog          1     100      20       2         10 |
 17. | 23380      Dog          2     100      20       2         10 |
 18. | 23381      Dog          4     100      20       2         10 |
 19. | 23382      Dog         10       7      30       3         10 |
 20. | 23383      Dog          6     100      36       4          9 |
 21. | 23384      Dog          5     100      36       4          9 |
 22. | 23385      Dog          6     100      42       5        8.4 |
     +--------------------------------------------------------------+

I am assuming at most one observation for each (identifier, daily date) pair.

[Hans Schubert]

Thanks, Nick.

Apologies if I'm not being very clear here.

Going through it using row 11....
So the walkmiles for that row are 5
Therefore I need the mean score for all previous dates where the walkmiles are in excess of 5 walkmiles
That would be 100-100-8-11-12
Mean = 46.2 in wanted for cat

[Nick Cox]

You did say score -- sorry about that -- so all you need to do should be to fix the code accordingly.

sum(cond(walkmiles > 5 & walkmiles < ., score, 0))

[Hans Schubert]

Thanks very much, Nick. Perfect now.

[Hans Schubert]

Apologies, back again on this one.

This is fine now, except for the walkmiles being static at > 5
Which is fine for row 11, but that figure (walkmiles) is dynamic by row.

Going through it using row 10....
So the walkmiles for that row are 6
Therefore I need the mean score for all previous dates where the walkmiles are in excess of 6 walkmiles
That would be 8-11-12
Mean = 10.33 in wanted for cat

[Nick Cox]

As before I am struggling a bit with what you want. Presumably it's not just changing the code so that 6 is mentioned not 5.

This code calculates the mean score for each animal for all previous days with walkmiles above the latest walkmiles.

I doubt it's really what you want but you should be able to adapt the code. rangerun is from SSC and requires in turn rangestat from SSC.

* Example generated by -dataex-. For more info, type help dataex
clear
input float ddate str3 animal byte(walkmiles score)
23386 "Cat" 10  12
23387 "Cat" 10  11
23388 "Cat"  3 100
23389 "Cat"  1 100
23390 "Cat"  2 100
23391 "Cat"  4 100
23392 "Cat" 10   8
23393 "Cat"  6 100
23394 "Cat"  5 100
23395 "Cat"  6 100
23396 "Cat"  5 100
23397 "Cat"  8 100
23376 "Dog" 10   9
23377 "Dog" 10   8
23378 "Dog"  3 100
23379 "Dog"  1 100
23380 "Dog"  2 100
23381 "Dog"  4 100
23382 "Dog" 10   7
23383 "Dog"  6 100
23384 "Dog"  5 100
23385 "Dog"  6 100
end

program meanabovelast
    su score if walkmiles >= walkmiles[_N] & ddate < ddate[_N], meanonly 
    gen result = r(mean)
end 

rangerun meanabovelast, int(ddate . 0) by(animal) use(score walkmiles ddate)

list, sepby(animal)

     +----------------------------------------------+
     | ddate   animal   walkmi~s   score     result |
     |----------------------------------------------|
  1. | 23386      Cat         10      12          . |
  2. | 23387      Cat         10      11         12 |
  3. | 23388      Cat          3     100       11.5 |
  4. | 23389      Cat          1     100         41 |
  5. | 23390      Cat          2     100         41 |
  6. | 23391      Cat          4     100       11.5 |
  7. | 23392      Cat         10       8       11.5 |
  8. | 23393      Cat          6     100   10.33333 |
  9. | 23394      Cat          5     100      32.75 |
 10. | 23395      Cat          6     100      32.75 |
 11. | 23396      Cat          5     100   55.16667 |
 12. | 23397      Cat          8     100   10.33333 |
     |----------------------------------------------|
 13. | 23376      Dog         10       9          . |
 14. | 23377      Dog         10       8          9 |
 15. | 23378      Dog          3     100        8.5 |
 16. | 23379      Dog          1     100         39 |
 17. | 23380      Dog          2     100         39 |
 18. | 23381      Dog          4     100        8.5 |
 19. | 23382      Dog         10       7        8.5 |
 20. | 23383      Dog          6     100          8 |
 21. | 23384      Dog          5     100         31 |
 22. | 23385      Dog          6     100         31 |
     +----------------------------------------------+

[Hans Schubert]

Still an issue there...have attached a more real world example and my interpretation of your code

* Example generated by -dataex-. For more info, type help dataex
clear
input str10 racedate str21 horse float(distf_round nmfp)
"24/06/2015" "Zzoro"  7 -.0413
"23/07/2015" "Zzoro"  7 -.1054
"14/08/2015" "Zzoro"  8  .2901
"13/10/2015" "Zzoro"  9     .5
"12/04/2016" "Zzoro" 10  .2928
"04/05/2016" "Zzoro" 12  .1667
"21/05/2016" "Zzoro"  8 -.3873
"29/07/2016" "Zzoro" 11 -.2894
"05/10/2016" "Zzoro" 10  .4454
"21/10/2016" "Zzoro"  8  .2108
"01/05/2017" "Zzoro" 10 -.0727
"19/05/2017" "Zzoro" 10  .4714
"22/06/2017" "Zzoro" 10  .1291
"30/06/2017" "Zzoro" 10 -.2928
"15/09/2017" "Zzoro" 10   .058
"27/09/2017" "Zzoro" 10 -.1291
"18/10/2017" "Zzoro" 10 -.4548
"25/03/2018" "Zzoro" 10   .338
"21/04/2018" "Zzoro" 10 -.3254
"30/04/2018" "Zzoro" 10  .3637
"28/05/2018" "Zzoro" 10      0
"01/06/2018" "Zzoro" 10 -.1054
"11/07/2018" "Zzoro" 11  .1782
"08/08/2018" "Zzoro" 12  .2673
"18/08/2018" "Zzoro" 10  -.058
"27/08/2018" "Zzoro" 10     .5
"21/09/2018" "Zzoro" 10  .2894
"22/10/2018" "Zzoro" 10 -.1741
"06/05/2019" "Zzoro" 10      0
"12/06/2019" "Zzoro" 10      0
"10/07/2019" "Zzoro" 11  .4345
"01/08/2019" "Zzoro" 10  .4216
"17/08/2019" "Zzoro" 10  .0727
"28/08/2019" "Zzoro" 11      0
"13/09/2019" "Zzoro" 10   .488
"29/09/2019" "Zzoro" 10  .2357
"01/11/2019" "Zzoro" 10 -.3563
"19/11/2019" "Zzoro" 11 -.3721
"04/06/2020" "Zzoro" 10 -.5311
"13/07/2020" "Zzoro" 11 -.3721
"22/08/2020" "Zzoro" 10  .1448
"31/08/2020" "Zzoro" 10  -.058
"12/10/2020" "Zzoro" 10 -.1543
end

gen ddate = daily(racedate, "DMY")
format ddate %td

program meanabovelast1
su nmfp if distf_round >= distf_round[_N] & ddate < ddate[_N], meanonly
gen result = r(mean)
end

rangerun meanabovelast1, int(ddate . 0) by(horse) use(nmfp distf_round ddate)

So 'result' for row 43 should be....

0.001638

which is the average of the nmfp's for the 8 distf_rounds that are greater than 10 in the previous ddates

Thanks again

[Nick Cox]

Sorry, the code in #12 has <= but the text said "above". Fixing that I agree on getting 8 rounds above but disagree getting a different mean for row 43 -- which for anyone trying to follow this is the last observation in the data example.

You should have seen enough technique by now to take this further on your own.

. * Example generated by -dataex-. For more info, type help dataex
. clear

. input str10 racedate str21 horse float(distf_round nmfp)

       racedate                  horse  distf_r~d       nmfp
  1. "24/06/2015" "Zzoro"  7 -.0413
  2. "23/07/2015" "Zzoro"  7 -.1054
  3. "14/08/2015" "Zzoro"  8  .2901
  4. "13/10/2015" "Zzoro"  9     .5
  5. "12/04/2016" "Zzoro" 10  .2928
  6. "04/05/2016" "Zzoro" 12  .1667
  7. "21/05/2016" "Zzoro"  8 -.3873
  8. "29/07/2016" "Zzoro" 11 -.2894
  9. "05/10/2016" "Zzoro" 10  .4454
 10. "21/10/2016" "Zzoro"  8  .2108
 11. "01/05/2017" "Zzoro" 10 -.0727
 12. "19/05/2017" "Zzoro" 10  .4714
 13. "22/06/2017" "Zzoro" 10  .1291
 14. "30/06/2017" "Zzoro" 10 -.2928
 15. "15/09/2017" "Zzoro" 10   .058
 16. "27/09/2017" "Zzoro" 10 -.1291
 17. "18/10/2017" "Zzoro" 10 -.4548
 18. "25/03/2018" "Zzoro" 10   .338
 19. "21/04/2018" "Zzoro" 10 -.3254
 20. "30/04/2018" "Zzoro" 10  .3637
 21. "28/05/2018" "Zzoro" 10      0
 22. "01/06/2018" "Zzoro" 10 -.1054
 23. "11/07/2018" "Zzoro" 11  .1782
 24. "08/08/2018" "Zzoro" 12  .2673
 25. "18/08/2018" "Zzoro" 10  -.058
 26. "27/08/2018" "Zzoro" 10     .5
 27. "21/09/2018" "Zzoro" 10  .2894
 28. "22/10/2018" "Zzoro" 10 -.1741
 29. "06/05/2019" "Zzoro" 10      0
 30. "12/06/2019" "Zzoro" 10      0
 31. "10/07/2019" "Zzoro" 11  .4345
 32. "01/08/2019" "Zzoro" 10  .4216
 33. "17/08/2019" "Zzoro" 10  .0727
 34. "28/08/2019" "Zzoro" 11      0
 35. "13/09/2019" "Zzoro" 10   .488
 36. "29/09/2019" "Zzoro" 10  .2357
 37. "01/11/2019" "Zzoro" 10 -.3563
 38. "19/11/2019" "Zzoro" 11 -.3721
 39. "04/06/2020" "Zzoro" 10 -.5311
 40. "13/07/2020" "Zzoro" 11 -.3721
 41. "22/08/2020" "Zzoro" 10  .1448
 42. "31/08/2020" "Zzoro" 10  -.058
 43. "12/10/2020" "Zzoro" 10 -.1543
 44. end

. gen ddate = daily(racedate, "DMY")

. format ddate %td

. 
. program meanabovelast1
  1. su nmfp if distf_round > distf_round[_N] & ddate < ddate[_N], meanonly
  2. gen result = r(mean)
  3. gen count = r(N)
  4. end

. 
. rangerun meanabovelast1, int(ddate . 0) by(horse) use(nmfp distf_round ddate)
  (using rangestat version 1.1.1)

. 
. list 

     +-----------------------------------------------------------------------+
     |   racedate   horse   distf_~d     nmfp       ddate     result   count |
     |-----------------------------------------------------------------------|
  1. | 24/06/2015   Zzoro          7   -.0413   24jun2015          .       0 |
  2. | 23/07/2015   Zzoro          7   -.1054   23jul2015          .       0 |
  3. | 14/08/2015   Zzoro          8    .2901   14aug2015          .       0 |
  4. | 13/10/2015   Zzoro          9       .5   13oct2015          .       0 |
  5. | 12/04/2016   Zzoro         10    .2928   12apr2016          .       0 |
     |-----------------------------------------------------------------------|
  6. | 04/05/2016   Zzoro         12    .1667   04may2016          .       0 |
  7. | 21/05/2016   Zzoro          8   -.3873   21may2016   .3198333       3 |
  8. | 29/07/2016   Zzoro         11   -.2894   29jul2016      .1667       1 |
  9. | 05/10/2016   Zzoro         10    .4454   05oct2016    -.06135       2 |
 10. | 21/10/2016   Zzoro          8    .2108   21oct2016      .2231       5 |
     |-----------------------------------------------------------------------|
 11. | 01/05/2017   Zzoro         10   -.0727   01may2017    -.06135       2 |
 12. | 19/05/2017   Zzoro         10    .4714   19may2017    -.06135       2 |
 13. | 22/06/2017   Zzoro         10    .1291   22jun2017    -.06135       2 |
 14. | 30/06/2017   Zzoro         10   -.2928   30jun2017    -.06135       2 |
 15. | 15/09/2017   Zzoro         10     .058   15sep2017    -.06135       2 |
     |-----------------------------------------------------------------------|
 16. | 27/09/2017   Zzoro         10   -.1291   27sep2017    -.06135       2 |
 17. | 18/10/2017   Zzoro         10   -.4548   18oct2017    -.06135       2 |
 18. | 25/03/2018   Zzoro         10     .338   25mar2018    -.06135       2 |
 19. | 21/04/2018   Zzoro         10   -.3254   21apr2018    -.06135       2 |
 20. | 30/04/2018   Zzoro         10    .3637   30apr2018    -.06135       2 |
     |-----------------------------------------------------------------------|
 21. | 28/05/2018   Zzoro         10        0   28may2018    -.06135       2 |
 22. | 01/06/2018   Zzoro         10   -.1054   01jun2018    -.06135       2 |
 23. | 11/07/2018   Zzoro         11    .1782   11jul2018      .1667       1 |
 24. | 08/08/2018   Zzoro         12    .2673   08aug2018          .       0 |
 25. | 18/08/2018   Zzoro         10    -.058   18aug2018      .0807       4 |
     |-----------------------------------------------------------------------|
 26. | 27/08/2018   Zzoro         10       .5   27aug2018      .0807       4 |
 27. | 21/09/2018   Zzoro         10    .2894   21sep2018      .0807       4 |
 28. | 22/10/2018   Zzoro         10   -.1741   22oct2018      .0807       4 |
 29. | 06/05/2019   Zzoro         10        0   06may2019      .0807       4 |
 30. | 12/06/2019   Zzoro         10        0   12jun2019      .0807       4 |
     |-----------------------------------------------------------------------|
 31. | 10/07/2019   Zzoro         11    .4345   10jul2019       .217       2 |
 32. | 01/08/2019   Zzoro         10    .4216   01aug2019     .15146       5 |
 33. | 17/08/2019   Zzoro         10    .0727   17aug2019     .15146       5 |
 34. | 28/08/2019   Zzoro         11        0   28aug2019       .217       2 |
 35. | 13/09/2019   Zzoro         10     .488   13sep2019   .1262167       6 |
     |-----------------------------------------------------------------------|
 36. | 29/09/2019   Zzoro         10    .2357   29sep2019   .1262167       6 |
 37. | 01/11/2019   Zzoro         10   -.3563   01nov2019   .1262167       6 |
 38. | 19/11/2019   Zzoro         11   -.3721   19nov2019       .217       2 |
 39. | 04/06/2020   Zzoro         10   -.5311   04jun2020   .0550286       7 |
 40. | 13/07/2020   Zzoro         11   -.3721   13jul2020       .217       2 |
     |-----------------------------------------------------------------------|
 41. | 22/08/2020   Zzoro         10    .1448   22aug2020   .0016375       8 |
 42. | 31/08/2020   Zzoro         10    -.058   31aug2020   .0016375       8 |
 43. | 12/10/2020   Zzoro         10   -.1543   12oct2020   .0016375       8 |
     +-----------------------------------------------------------------------+

.