Combine daily data into one variable

[Sigrid Vikjord]

Hi,

I have data from an activity measurement device. Participants have worn this for seven days (but several individuals removed it after a couple of days, so there are missing data). I have one variable per day. The activity is measured in minutes/day. I want to create a variable which shows the mean no. of minutes of the given activity (say cycling) during the wear time of the device. How could I achieve this? I tried to simply summarize the variables and divide by seven, but that resulted in a variable with only 300 observations (should be around 4500).

gen lying_time = .
replace lying_time = ((LyTmMon_NT4ActLogM + LyTmTues_NT4ActLogM + LyTmWed_NT4ActLogM + LyTmThur_NT4ActLogM + LyTmFri_NT4ActLogM + LylTmSatu_NT4ActLogM + LyTmSun_NT4ActLogM))/7

* Example generated by -dataex-. For more info, type help dataex
clear
input float id double(CyclTmMon_NT4ActLogM CyclTmTues_NT4ActLogM CyclTmWed_NT4ActLogM CyclTmThur_NT4ActLogM CyclTmSatu_NT4ActLogM CyclTmSun_NT4ActLogM)
  1      5      .   3.5   .08  8.83   4.92
  2   8.58     25     .  5.58 39.17  15.17
  3   1.42      .   5.5 11.42  1.83   7.17
  4      .  16.25    22 47.92  8.08   9.75
  5      .  25.92  4.33 25.58 43.33      .
  6   9.58   3.25  5.92     .  7.75  21.08
  7     10   18.5     .  5.42 17.17  15.08
  8      3   8.92     .  6.67  1.83      7
  9      9      .     .     .  2.58    .75
 10      .   13.5    20 17.17 30.92      .
 11      .   1.33   .75   2.5  6.08      2
 12    .08    .25     .    .5  8.17     .5
 13  48.92    .58 69.33   .67   .75    .58
 14      .   5.92   .42  3.75   .17      .
 15   4.42   3.83     .   8.5    .5    .33
 16    .25  65.42     .  7.33   .33     60
 17  14.83  32.75  2.58 10.08 26.92   3.75
 18   1.08    .42   .58     .  23.5   4.92
 19  92.17  38.75 41.83     . 52.83  42.58
 20   31.5   3.08     .  1.25   .92  26.83
 21    .08   1.33   .75     .   .42    .17
 22   4.42   9.83  2.25  5.42  5.67  25.42
 23   1.08      .  2.67  2.75  3.67  12.75
 24      . 199.42 16.75  6.58  8.33   1.83
 25  28.67      .     .  5.25  3.08   4.92
 26   6.67    .08     .   .08  2.25    .83
 27  21.58  11.83     . 33.92 18.42  16.33
 28   7.83    8.5     . 24.58 11.17  28.42
 29  11.67   7.17  19.5     .     2   5.42
 30 101.08  14.42 53.92     . 40.58  10.33
 31  10.33   6.92     .  7.83     2   2.08
 32  23.08   3.67     .   .67 21.42   8.25
 33      .   4.75    .5   .67   .67    .42
 34      .  17.92 46.67  10.5  2.33  33.92
 35      .  71.25 51.58 60.58  3.08  57.83
 36   7.08   3.75     .  2.17  1.92   1.92
 37      .   2.75  1.58  4.58 16.75  18.75
 38     20  17.75 25.67     . 18.42   5.75
 39   9.42  10.25     .  3.75 44.67  11.83
 40      .    .67 13.67 20.67  8.25      .
 41      .  40.83  5.42   .58 20.92      .
 42   9.67   8.75     .  8.25 21.75  54.42
 43   3.58      .  1.67 37.08  9.25   8.58
 44   2.42   4.33     .  1.58     9    2.5
 45      .      .     .     .   .25    .08
 46      .    3.5 10.17  12.5  15.5   7.08
 47 100.83      . 38.25 75.33  2.75  22.67
 48  21.92   3.25   .17   .58 46.08   16.5
 49   1.42   1.08     .  5.75   .67    .17
 50   15.5      .     3 15.17   4.5   8.58
 51      7   2.58  4.75     .   .58    .58
 52  15.17      .  2.17  2.58   .25  39.42
 53  22.83      9  9.92     .  1.92   3.58
 54  17.42  23.08     . 16.58     6   37.5
 55   4.83  19.17 13.42     . 11.83   4.42
 56    1.5   3.58     .  3.58   .67   8.17
 57   4.17  21.42   .75     .  9.42  33.92
 58 106.83  86.42     . 47.75  9.42  43.25
 59  20.33   5.58 10.75   .08 20.58   1.75
 60      .   1.58  13.5  5.25  1.08  16.25
 61   1.08      .   .67  2.58  3.08   2.33
 62   1.83   2.92     .   .42  1.83    2.5
 63  25.75  38.67  13.5     . 10.92  15.75
 64   5.25  16.58     . 168.5  6.75 157.83
 65      .      .  8.58 13.83   9.5  10.42
 66  41.08  22.58     .  6.42    32  11.08
 67    .33  12.92   4.5     . 16.25   3.75
 68   2.42   2.58     .  2.33     7   8.75
 69      .     41 30.08 32.75 47.58     13
 70  70.25      .  1.67  3.42  3.33   2.83
 71      .   4.75     .  2.08     1      .
 72 110.67  96.17 10.33     .  64.5   8.42
 73      .   4.08  4.42  1.42  5.33      .
 74  41.67  30.83     .  7.58    35   8.75
 75   9.42      6   .25     .  3.67    .25
 76      .   1.83     5     .     .      .
 77  15.42      .  2.33   .75   .67   1.17
 78      .     15 50.92 29.92 53.25  64.33
 79   4.42      .  1.25   .33   3.5    .58
 80      .    1.5  2.92  9.58    .5      .
 81  13.92  25.42  6.83     . 21.83   9.08
 82     17   7.92     . 39.67 52.42  38.67
 83   2.67    .08   .08     .  8.67    .08
 84      6  12.42   .08     .   4.5  20.92
 85  22.58   13.5     .     4  3.17   5.42
 86  45.08      . 12.83 19.67  3.17   1.58
 87  23.17      .     6  4.08   2.5   5.83
 88     26      3 22.92     .     .  20.08
 89    .42   5.58  5.08     5 37.08   6.75
 90   6.08   2.17     .     3  8.08   1.33
 91      .   7.67 10.83    14     6   8.83
 92      .    7.5   .08   .08  1.58  11.25
 93  16.75      .  5.25  6.58 12.08  19.83
 94   4.67    .83     .   .17   .67    .75
 95   5.83  87.75 82.83 67.33 23.92  29.83
 96   3.08  17.33     .  2.33  4.67  18.92
 97  14.08  19.33 11.67  5.42     5   5.83
 98      .      .   .42   .33    .5      2
 99      .  33.92 29.33 53.67 71.25      .
100  13.92   5.75  4.67     . 81.67   6.08
end

------------------ copy up to and including the previous line ------

Thank you in advance for any input.

[Ken Chui]

Use egen rowmean, that would adjust for the presence of missing data:

egen mean_act = rowmean(CyclTm*_NT4ActLogM)

[Sigrid Vikjord]

Thank you so much, that worked perfectly.