I have a dataset (example below) with participants (newID) with repeated assessments (assessmetnumber_mom).
At the moment I have different variables with values of air pollution (nox no2 so2 pm10 pm25) linked with each participant's assessment number. However, as you can see from the data, there are some assessments from certain participants, that have double entries (e.g. newid ==3 & assessmentnumber_mom == 4). This occurs when an assessment links with more than one station.
I was wondering if there was a way to clean this up, so that I can xtset newid assessmetnumber_mom?
In order to xtset, I need time values to not repeat within the panel.
So I was hoping someone could help me write a little code that will essentially collapse it so that assessmentnumber wouldn't repeat within newid, and the values used for nox, no2, so2, pm10, pm25 would be from stations that are closest (station_dist).
Unless the station that is closest is missing values for nox etc, then we would take the values from the next closest station with data for that particular variable. Again, this only applies to any assessmentnumber_mom that is repeated within the newid.
I hope that explanation made sense.
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input float newid byte assessmentnumber_mom float(nox no2 so2 pm10 pm25 station_dist) 1 1 . . . 18.084675 . 376.0912 1 7 . . . . . 382.2666 1 28 . . . . . 403.9746 1 37 43.73418 25.41927 . 14.848184 . 246.90134 1 40 75.427376 44.90638 . 16.270956 . 374.7404 2 11 106.80772 62.87104 . . . 762.7279 2 12 112.92995 67.08012 . 26.2029 . 802.1494 3 4 . . . 38.027218 . 344.7251 3 4 . . . 16.666666 8 475.2111 3 4 286.94693 130.54869 . . . 583.1505 3 7 . . . 22.76697 . 343.571 3 7 48.41096 37.183594 . 24.166666 19 476.3834 3 7 283.5309 103.05334 . . . 585.21094 3 10 . . . 18.018301 . 343.4341 3 10 70.1522 45.29737 . 18.333334 15 476.4892 3 10 279.87088 103.24223 . . . 584.1655 4 2 . . . . . 497.9222 4 2 . . . 13.62939 . 613.6534 4 2 . . . . . 879.3113 4 8 21.422083 17.471247 . . . 541.43115 5 1 41.56973 23.795845 . . . 726.7238 5 2 32.17558 26.352877 . . . 425.2608 5 2 51.30429 37.76747 . . . 544.9822 5 2 . . . . . 636.3493 5 2 323.7943 127.2185 . . . 723.741 5 2 28.954447 25.240583 .4618737 6.1 1.7 881.4661 5 3 28.272594 21.247633 . 8.331725 . 699.782 5 3 88.04765 39.61359 . . . 768.3689 5 3 138.61273 41.45645 . 14.360738 . 788.3668 5 4 24.470724 18.035574 . 15.925012 . 707.0011 5 4 253.4162 104.3492 . . . 774.2448 5 4 339.3024 121.02203 . 22.265076 . 793.9699 5 6 17.371136 15.184 . 14.39047 . 700.2661 5 6 154.3172 72.555595 . . . 767.7214 5 6 183.92203 74.9819 . 23.23394 . 787.509 5 8 39.79601 24.318106 . . . 112.25172 5 10 88.99467 49.65458 . . . 728.5635 5 13 130.59662 62.8964 . . . 728.5645 5 14 122.33813 67.46895 . . . 728.9645 5 15 85.43049 50.78779 . . . 720.8805 5 17 169.961 68.87522 . 19.775017 . 278.54712 5 17 128.46432 61.67989 . . . 279.74945 5 17 23.2405 17.878544 . 14.342655 . 318.5034 5 18 52.89682 30.46095 . . . 725.3632 5 20 95.29588 46.79621 . . . 729.1621 5 22 60.15305 31.37036 . . . 750.0762 5 24 25.966944 20.16592 . . . 729.7242 5 25 49.47787 29.94457 . . . 729.5167 5 27 98.57767 73.390564 . . . 730.7002 5 28 . . . 49.70063 . 645.0485 5 28 . . . . . 654.584 5 28 52.2173 33.13985 . 10.211782 . 705.0042 5 29 29.66978 23.4106 . . . 139.15396 5 29 291.86035 128.46022 . . . 279.69025 5 29 . . . . . 710.6941 5 30 45.71644 35.49867 . . . 722.7138 5 31 36.448616 21.18038 . . . 725.6688 5 34 93.48952 56.04883 . . . 728.6742 5 39 75.040955 69.82591 . . . 730.3483 5 41 32.42201 29.11511 . . . 726.4184 6 8 68.45207 46.6601 . 41.77623 . 873.7449 6 8 146.84546 62.51266 3.786868 75.36232 . 991.9138 7 5 36.81419 30.426615 . 20.8691 . 665.9031 7 7 37.309532 20.50972 . . . 333.2551 7 7 . . . 52.37954 . 793.5005 7 10 249.71664 43.28142 . 22.9726 . 667.7828 7 12 55.39602 43.89196 . 13.9478 . 666.4618 7 23 41.66542 31.7812 . 8.16926 . 664.5731 8 1 20.46082 17.299208 1.27046 12 3.3 148.19496 8 1 61.2718 37.412823 . . . 645.0388 8 1 . . . . . 679.2554 8 3 60.40278 39.93179 . 36.173912 . 666.3252 8 3 . . . . . 922.3167 9 2 267.18054 63.27314 . 21.25604 . 193.94772 9 2 45.16195 23.35582 . . . 319.4671 9 2 . . . . . 319.4671 9 2 155.59195 51.59244 . 16.425121 . 498.9487 9 2 . . . . . 758.9029 9 2 . . . . . 758.9029 9 2 . . . . . 758.9029 9 2 379.481 49.58115 . . . 773.4932 9 2 . 77.01171 . . . 773.4932 9 2 . . . 12.625 5.416038 773.4932 9 2 . . . . . 773.4932 9 2 366.7388 94.58247 10.30484 . . 776.4487 9 2 . . . 18.1454 9.1356 776.4487 9 8 28.87299 16.979662 . . . 601.14594 9 8 271.75128 114.10789 . . . 877.1158 9 26 93.01839 45.56152 . . . 334.951 9 26 68.57392 43.61274 . . . 502.7753 10 1 69.11068 41.41973 . 18.333334 4 335.0561 10 1 . . . 56.31644 . 890.0833 10 17 . . . . 12 469.4398 10 17 215.25996 88.63956 . . . 541.94775 10 17 139.48439 67.097244 . . . 666.077 10 17 92.13503 64.397194 . . . 820.5405 11 2 23.512854 18.783354 . 29.352 . 416.3487 11 42 . . . 24.2 9.8 788.7758 12 1 . . . . . 865.0882 12 1 282.57446 87.94126 . 22.500805 . 867.7262 end
I feel like this might have a simple solution, but I can't think of it at the moment.
Kind regards,
Ryan
0 Response to Trying to clean up panel data
Post a Comment