egen rowmean only for positive values

Benthe Vrijsen

Join Date: Apr 2022
Posts: 11

egen rowmean only for positive values

10 May 2022, 11:17

Hi there,

I am trying to create a new variable which contains the row mean of diff_d* but only for the instances where the values are larger than 0.
So for example respondent 132 has a positive value for 6 out of the 7 variables. So the new variable should be the average of those 6 positive values.
Any thoughts on how to approach this?

Thanks in advance!

Code:

* Example generated by -dataex-. For more info, type help dataex
clear
input int id_3 float(diff_d4_1 diff_d5_1 diff_d6_1 diff_d7_1 diff_d8_1 diff_d9_1 diff_d11_1 TI_Count)
132   .7999878     .6000366          1          1  -.7000122   1.7999878   .1999817 12
133   .7999878     .6000366          1          1  -.7000122   1.7999878   .1999817 12
135   .7999878     .6000366          1          1  -.7000122   1.7999878   .1999817 12
134  .53497314     .3082275   .6960449   .7250671  -.9018555   1.5253906 -.07955933  7
115   .1822815   -.17214966  .25872803   .3822937 -1.1632996   1.1822815  -.4177551  5
121   .3167419 -.0040283203   .4201355  .51678467 -1.0624084    1.316742 -.28323364  5
129  -.3000183    .09997559          0  -.2999878  -1.600006          .5 -1.1999817  3
137  -.3000183    .09997559          0  -.2999878  -1.600006          .5 -1.1999817  3
141  -.3000183    .09997559          0  -.2999878  -1.600006          .5 -1.1999817  3
136  -.5588074    -.4750977  -.4025574  -.5012512  -1.772522    .2987366 -1.3724976  2
 78 -1.0753479    .22250366  -.3873596  -.7390747 -2.2457886   -.6586304 -1.2621765  1
 79  -.6914368   -.29144287  -.4473267  -.7100525  -1.954132 -.021881104 -1.5168457  1
138  -.6966858     -.591156  -.6146545   -.813446 -1.8184814   .04794312 -1.7104187  1
  1 -3.7263184   -3.8315735 -3.9263306  -3.694733 -3.2631226   -3.800018  -3.668396  0
  2         -3    -2.800018  -3.199982  -3.199982       -3.5  -4.0999756         -3  0
  3 -3.0441895   -2.7779236 -3.2220764  -3.177887  -3.522095   -4.011566         -3  0
  4  -3.132782    -2.733612  -3.266388 -3.1335754  -3.566406   -3.834381         -3  0
  5  -3.883423   -3.8708496 -4.0834045 -3.8125305  -3.341705   -3.800018   -3.62912  0
  6 -3.6180725   -3.8045044  -3.818054  -3.613556 -3.2089844   -3.800018  -3.695465  0
  7  -3.645325    -3.656372 -3.8453064  -3.643097  -3.332031   -3.854675 -3.5336914  0
  8 -3.3176575   -2.9923096  -3.467621  -3.302124  -3.526184   -3.833649  -3.133484  0
  9  -3.568939    -3.953552  -3.645935 -3.4844055  -3.015289   -2.276764 -3.8844604  0
 10  -3.652069   -3.9436035 -3.5834656  -3.344055  -3.099945  -2.0940857  -3.750824  0
 11 -1.7000122   -2.2000122 -2.0999756 -2.1000366 -2.4000244  -1.0999756         -3  0
 12 -3.0921936    -3.622833  -3.561981  -3.608551  -3.360382   -2.659332  -3.872406  0
 13  -3.570007   -4.0786133  -3.874634  -3.866699 -2.9428406   -2.877838 -4.1515503  0
 14 -1.9604187    -2.483368 -2.3546448  -2.341339  -2.482239  -1.3354797  -3.143738  0
 15 -1.6348267   -2.2381592 -1.8131104 -1.4888306 -2.3667908  -.59054565  -2.247589  0
 16       -3.5           -4         -4 -4.0999756 -3.9000244   -3.200012  -4.100006  0
 17  -4.941162   -4.7384033 -4.4055176   -4.10553  -3.401398   -2.537018   -4.23703  0
 18  -4.926117   -4.7456055  -4.427704  -4.190613  -3.338135   -2.772034  -4.287842  0
 19  -3.572388   -4.0336914  -3.804016  -3.799042  -2.859436   -2.801361  -4.119751  0
 20 -3.4020996   -3.9020996 -3.8966675  -3.991211  -3.818451   -3.085785 -4.0401917  0
 21 -1.6695862   -2.1500244 -2.0543213  -2.060913 -2.3826294  -1.0651855 -2.9608765  0
 22  -1.751831   -2.2518616 -2.1546936 -2.1576233  -2.443207   -1.160492  -3.031677  0
 23 -2.1664429    -2.666443  -2.592346 -2.6182556  -2.788727   -1.644165  -3.285065  0
 24 -4.2810974    -4.663391  -4.556427  -4.480133  -3.860657  -4.0715637 -4.2588196  0
 25  -3.500336    -4.000458  -4.000458  -4.100342  -3.900116    -3.20047 -4.1002197  0
 26  -3.531891   -4.0269775  -4.029419   -4.12204  -3.919617    -3.22699  -4.107361  0
 27  -3.500336   -4.0004272 -4.0004272 -4.1003113  -3.900116  -3.2004395  -4.100189  0
 28 -2.1604004      -1.9888 -2.2227478 -2.3556519  -3.140015  -1.8549805 -2.8062744  0
 29  -2.857086    -2.831116  -3.064911 -3.1648865  -3.666229   -2.673981 -3.3986816  0
 30       -3.5           -4         -4 -4.0999756 -3.9000244   -3.200012  -4.100006  0
 31       -3.5           -4         -4 -4.0999756 -3.9000244   -3.200012  -4.100006  0
 32  -2.399994           -2 -2.4000244       -2.5       -3.5  -2.2999878 -2.9000244  0
 33  -2.944519    -2.990051  -3.192047  -3.292023 -3.6980286   -2.745514 -3.4940186  0
 34  -2.542114   -2.1687317 -2.5598755   -2.63324  -3.562164  -2.4332275  -2.971069  0
 35  -3.291962    -3.579651 -3.6743774  -3.764374  -3.835266   -3.031006  -3.835785  0
 36 -3.8861694    -3.858978 -4.1118164  -3.984192 -4.1937256   -3.613739  -3.751343  0
 37  -4.016968    -3.919739  -4.214142 -3.9140625 -3.4141235   -3.800018  -3.611267  0
 38  -4.416687     -4.38562  -4.546814 -4.2481384 -3.7579346   -3.799866  -3.878815  0
 39 -4.2673035    -4.211853   -4.42276  -4.178192 -4.0663757   -3.800018  -3.833618  0
 40  -3.865814    -3.740692 -4.0490723 -3.8742065  -4.141327   -3.674194 -3.6328735  0
 41  -4.172699    -4.101471  -4.343933  -4.043915  -3.543915   -3.800018 -3.7151184  0
 42         -4   -3.9000244  -4.200012         -4  -4.200012   -3.799988  -3.699982  0
 43 -4.0516357    -3.934906  -4.220825  -3.995514  -4.046814   -3.790497  -3.693237  0
 44         -4   -3.9000244  -4.200012         -4  -4.200012   -3.799988  -3.699982  0
 45 -3.2794495    -2.819214  -3.389374 -3.1794434  -3.580109  -3.7099304  -3.059601  0
 46         -4   -3.9000244  -4.200012         -4  -4.200012   -3.799988  -3.699982  0
 47  -3.997711    -3.896576  -4.197418  -3.997406  -4.198303   -3.799713 -3.6979675  0
 48 -3.9819946   -3.8731384  -4.179779  -3.979828 -4.1866455   -3.797394  -3.684387  0
 49         -4   -3.9000244  -4.200012         -4  -4.200012   -3.799988  -3.699982  0
 50  -3.280426    -2.820679  -3.390503   -3.18042  -3.579895  -3.7100525  -3.060303  0
 51         -4   -3.9000244  -4.200012         -4  -4.200012   -3.799988  -3.699982  0
 52         -4   -3.8999634  -4.200012 -3.8999634  -3.399994   -3.800018 -3.5999756  0
 53         -4   -3.8999634  -4.200012 -3.8999634  -3.399994   -3.800018 -3.5999756  0
 54 -3.9972534   -3.8958435 -4.1968994  -3.897217  -3.400696  -3.7996826 -3.5979004  0
 55  -3.991791    -3.897888  -4.191803  -3.893799 -3.3959045   -3.800018   -3.60202  0
 56  -3.917267    -3.879303  -4.117279  -3.837952 -3.3586426   -3.800018 -3.6206665  0
 57         -4   -3.9000244  -4.200012         -4  -4.200012   -3.799988  -3.699982  0
 58  -3.771393   -3.5570984   -3.94281  -3.742798 -4.0285645   -3.771423  -3.499939  0
 59 -3.2862244    -2.829346  -3.397003 -3.1969604  -3.664673   -3.710785 -3.0754395  0
 60 -4.2461243   -4.1931763 -4.3962097 -4.2927246  -3.841919  -3.9460144 -4.0917053  0
 61         -4   -3.8999634  -4.200012 -3.8999634  -3.399994   -3.800018 -3.5999756  0
 62         -4   -3.8999634  -4.200012 -3.8999634  -3.399994   -3.800018 -3.5999756  0
 63         -4   -3.8999634  -4.200012 -3.8999634  -3.399994   -3.800018 -3.5999756  0
 64         -4   -3.8999634  -4.200012 -3.8999634  -3.399994   -3.800018 -3.5999756  0
 65         -4   -3.8999634  -4.200012 -3.8999634  -3.399994   -3.800018 -3.5999756  0
 66         -4   -3.8999634  -4.200012 -3.8999634  -3.399994   -3.800018 -3.5999756  0
 67         -4   -3.8999634  -4.200012 -3.8999634  -3.399994   -3.800018 -3.5999756  0
 68         -4   -3.8999634  -4.200012 -3.8999634  -3.399994   -3.800018 -3.5999756  0
 69  -3.957306    -3.835907  -4.151947  -3.857239  -3.410675   -3.794678  -3.567963  0
 70  -3.980072    -3.870056  -4.177582 -3.8800354  -3.404968   -3.797516  -3.585022  0
 71  -3.606903    -3.801727  -3.806854 -3.6051636    -3.2034   -3.800018  -3.698273  0
 72  -3.825226    -3.856262 -4.0252075 -3.7688904 -3.3125916   -3.800018  -3.643677  0
 73 -4.2319336    -4.110138  -4.318817 -4.2937927  -3.878479     -3.8909  -4.255432  0
 74  -3.605072    -3.804962  -3.802612  -3.606323  -3.206268   -3.801239  -3.705902  0
 75 -3.6000366    -3.800018  -3.800018 -3.6000366  -3.199982   -3.800018  -3.699982  0
 76 -3.7690735    -4.138031  -3.799988 -3.8535156  -3.453552  -3.8845215  -3.953522  0
 77 -1.8234253    -.5058594 -1.2175903 -1.5176086         -3  -1.9351807 -1.8999634  0
 80 -2.3179016   -1.9219055 -2.3058472  -2.411682  -3.415802   -2.182129  -2.826965  0
 81 -2.2571716    -1.460205 -1.9925232 -2.1604614  -3.325867  -2.2424927  -2.499512  0
 82  -2.291565    -1.728882  -2.183136 -2.3192444 -3.4096375  -2.2277222  -2.719269  0
 83 -2.0405273   -1.6405334 -1.9891663 -2.1233826  -3.174744  -1.8206787  -2.609009  0
 84  -2.506561   -1.6103516 -2.2598877 -2.3075256  -3.302826   -2.808502   -2.45517  0
 85  -2.621765   -1.7913513  -2.432648  -2.439148 -3.3522034  -2.9565735  -2.545654  0
 86 -1.8728027    -.6144714  -1.309265  -1.583252  -3.031189   -1.993683  -1.957184  0
 87  -3.189789    -2.705078  -3.294922  -3.105072   -3.59491  -3.7203674         -3  0
 88         -3    -2.800018  -3.199982  -3.199982       -3.5  -4.0999756         -3  0
 89  -3.012146    -2.793945  -3.206055  -3.193909  -3.506073   -4.075653         -3  0
end

Tags: None

Nick Cox

Join Date: Mar 2014
Posts: 36058

10 May 2022, 11:43

This task would be easier if the data were reshaped to long, which might or might not be a good idea. As it is, this is crude but it is a solution until someone thinks of a better one. (I wondered about doing something in Mata, but didn't find a simpler method.)

Code:

* Example generated by -dataex-. For more info, type help dataex
clear
input int id_3 float(diff_d4_1 diff_d5_1 diff_d6_1 diff_d7_1 diff_d8_1 diff_d9_1 diff_d11_1 TI_Count)
132   .7999878     .6000366          1          1  -.7000122   1.7999878   .1999817 12
133   .7999878     .6000366          1          1  -.7000122   1.7999878   .1999817 12
135   .7999878     .6000366          1          1  -.7000122   1.7999878   .1999817 12
134  .53497314     .3082275   .6960449   .7250671  -.9018555   1.5253906 -.07955933  7
115   .1822815   -.17214966  .25872803   .3822937 -1.1632996   1.1822815  -.4177551  5
121   .3167419 -.0040283203   .4201355  .51678467 -1.0624084    1.316742 -.28323364  5
129  -.3000183    .09997559          0  -.2999878  -1.600006          .5 -1.1999817  3
137  -.3000183    .09997559          0  -.2999878  -1.600006          .5 -1.1999817  3
141  -.3000183    .09997559          0  -.2999878  -1.600006          .5 -1.1999817  3
136  -.5588074    -.4750977  -.4025574  -.5012512  -1.772522    .2987366 -1.3724976  2
end 

gen n_positive = 0 
gen sum_positive = 0 

quietly foreach v of var diff_* { 
    replace n_positive = n_positive + (`v' > 0)
    replace sum_positive = sum_positive + `v' if `v' > 0 
}

gen mean_positive = sum_positive / n_positive 

list *_positive 

     +--------------------------------+
     | n_posi~e   sum_po~e   mean_p~e |
     |--------------------------------|
  1. |        6   5.399994    .899999 |
  2. |        6   5.399994    .899999 |
  3. |        6   5.399994    .899999 |
  4. |        5   3.789703   .7579406 |
  5. |        4   2.005585   .5013962 |
     |--------------------------------|
  6. |        4   2.570404    .642601 |
  7. |        2   .5999756   .2999878 |
  8. |        2   .5999756   .2999878 |
  9. |        2   .5999756   .2999878 |
 10. |        1   .2987366   .2987366 |
     +--------------------------------+

Comment

Ali Atia

Join Date: May 2020
Posts: 737

10 May 2022, 12:45

This is still crude, but avoids a loop:

Code:

egen all = concat(diff_d*),p(";") format(%20.0g)
gen n_pos = 7-(strlen(all)-strlen(ustrregexra(all,"(?:-)|(?:(;)0(;))|(?:^0(;))|(?:(;)0$)","$1$2$3$4",.)))
replace all = ustrregexra(all,"(-.*?(;|$))","")
split all, p(";")
destring all*,replace
egen sum_pos = rowtotal(all?*)
gen mean_pos = sum_pos/n_pos
drop all* sum_pos n_pos

Last edited by Ali Atia; 10 May 2022, 13:27.

Comment

Nick Cox

Join Date: Mar 2014
Posts: 36058

10 May 2022, 15:31

I think this is better than #2.

Code:

* Example generated by -dataex-. For more info, type help dataex
clear
input int id_3 float(diff_d4_1 diff_d5_1 diff_d6_1 diff_d7_1 diff_d8_1 diff_d9_1 diff_d11_1 TI_Count)
132   .7999878     .6000366          1          1  -.7000122   1.7999878   .1999817 12
133   .7999878     .6000366          1          1  -.7000122   1.7999878   .1999817 12
135   .7999878     .6000366          1          1  -.7000122   1.7999878   .1999817 12
134  .53497314     .3082275   .6960449   .7250671  -.9018555   1.5253906 -.07955933  7
115   .1822815   -.17214966  .25872803   .3822937 -1.1632996   1.1822815  -.4177551  5
121   .3167419 -.0040283203   .4201355  .51678467 -1.0624084    1.316742 -.28323364  5
129  -.3000183    .09997559          0  -.2999878  -1.600006          .5 -1.1999817  3
137  -.3000183    .09997559          0  -.2999878  -1.600006          .5 -1.1999817  3
141  -.3000183    .09997559          0  -.2999878  -1.600006          .5 -1.1999817  3
136  -.5588074    -.4750977  -.4025574  -.5012512  -1.772522    .2987366 -1.3724976  2
end 

gen wanted = . 

mata : 
data = st_data(., "diff*")
st_store(., "wanted", rowsum(data :* (data :> 0)) :/  rowsum(data :> 0))
end 

list wanted 

     +----------+
     |   wanted |
     |----------|
  1. |  .899999 |
  2. |  .899999 |
  3. |  .899999 |
  4. | .7579406 |
  5. | .5013962 |
     |----------|
  6. |  .642601 |
  7. | .2999878 |
  8. | .2999878 |
  9. | .2999878 |
 10. | .2987366 |
     +----------+

Comment

Nick Cox

Join Date: Mar 2014

Posts: 36058
#5

11 May 2022, 04:42

#4 is not quite right if missings are present as they count as being above zero but are ignored in the sum. So, we need an extra line before we calculate the means.

Code:

_editmissing(data, 0)

or we could ignore the missings by

Code:

rowsum((data :> 0) :& (data :< .))
Comment
Benthe Vrijsen

Join Date: Apr 2022

Posts: 11
#6

26 May 2022, 01:38

I just now realized that I never replied to this.
Thank you both very much for you input, it really helped me out!
Comment
Benthe Vrijsen

Join Date: Apr 2022

Posts: 11
#7

26 May 2022, 01:39

.

Last edited by Benthe Vrijsen; 26 May 2022, 01:40. Reason: accidentally posted the same reply 3 times
Comment
Nick Cox

Join Date: Mar 2014

Posts: 36058
#8

26 May 2022, 06:27

"What I tell you three times is true"

https://en.wikiquote.org/wiki/The_Hunting_of_the_Snark
3 likes
Comment

Announcement

egen rowmean only for positive values

Comment

Comment

Comment

Comment

Comment

Comment

Comment