Hi all, I have been unable to find a previously answered question on the issue I am facing. An example of my data is shown below
I have a monthly panel dataset on counties in the US. Fips identifies a county with smoke being a variable I am investigating. The variables fipsneighbor1 - fipsneighbor8 give me the fips code of neighbouring counties. What I want to do is generate a variable that calculates the average of smoke of all adjacent counties, i.e. for the county with fips = 10001 I would have a variable adjsmoke equal to the average smoke of the adjacent fips codes 10003 10005 24011 24029 24035 34009 34011 34033 on that particular date.
In terms of why my neighbouring fips code are like this for explanation purposes I had a long data set (shown below)
which I reshaped into wide format using the following code
In order to produce a wide dataset (shown below) which I merged with my main dataset.
The reason why I included this is because I feel the way I have shown the neighbouring fips code of a county is the reason I am having issues.
Thanks in advance for any guidance.
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input float date long(fips fipsneighbor1 fipsneighbor2 fipsneighbor3 fipsneighbor4 fipsneighbor5 fipsneighbor6 fipsneighbor7 fipsneighbor8) float av_per_covered_smoke_per_month 558 10001 10003 10005 24011 24029 24035 34009 34011 34033 .1961142 559 10001 10003 10005 24011 24029 24035 34009 34011 34033 .0436705 568 10001 10003 10005 24011 24029 24035 34009 34011 34033 .032979734 570 10001 10003 10005 24011 24029 24035 34009 34011 34033 .010257742 578 10001 10003 10005 24011 24029 24035 34009 34011 34033 .0006418067 581 10001 10003 10005 24011 24029 24035 34009 34011 34033 .06780533 582 10001 10003 10005 24011 24029 24035 34009 34011 34033 .1070103 583 10001 10003 10005 24011 24029 24035 34009 34011 34033 .072941884 592 10001 10003 10005 24011 24029 24035 34009 34011 34033 .03525944 603 10001 10003 10005 24011 24029 24035 34009 34011 34033 .1069096 604 10001 10003 10005 24011 24029 24035 34009 34011 34033 .13109824 605 10001 10003 10005 24011 24029 24035 34009 34011 34033 .1831884 607 10001 10003 10005 24011 24029 24035 34009 34011 34033 .15773147 609 10001 10003 10005 24011 24029 24035 34009 34011 34033 .08946574 615 10001 10003 10005 24011 24029 24035 34009 34011 34033 .03450233 616 10001 10003 10005 24011 24029 24035 34009 34011 34033 .1869274 618 10001 10003 10005 24011 24029 24035 34009 34011 34033 .11505583 619 10001 10003 10005 24011 24029 24035 34009 34011 34033 .09338056 627 10001 10003 10005 24011 24029 24035 34009 34011 34033 .008289928 630 10001 10003 10005 24011 24029 24035 34009 34011 34033 .10019654 631 10001 10003 10005 24011 24029 24035 34009 34011 34033 .13337664 632 10001 10003 10005 24011 24029 24035 34009 34011 34033 .26234472 641 10001 10003 10005 24011 24029 24035 34009 34011 34033 .1647501 643 10001 10003 10005 24011 24029 24035 34009 34011 34033 .1061244 654 10001 10003 10005 24011 24029 24035 34009 34011 34033 .22167034 655 10001 10003 10005 24011 24029 24035 34009 34011 34033 .2700496 664 10001 10003 10005 24011 24029 24035 34009 34011 34033 .06446587 666 10001 10003 10005 24011 24029 24035 34009 34011 34033 .2254688 667 10001 10003 10005 24011 24029 24035 34009 34011 34033 .05444578 676 10001 10003 10005 24011 24029 24035 34009 34011 34033 .1967047 548 10003 10001 24015 24029 34015 34033 42029 42045 . .0533122 558 10003 10001 24015 24029 34015 34033 42029 42045 . .2150744 559 10003 10001 24015 24029 34015 34033 42029 42045 . .00466294 568 10003 10001 24015 24029 34015 34033 42029 42045 . .065753154 569 10003 10001 24015 24029 34015 34033 42029 42045 . .016367888 570 10003 10001 24015 24029 34015 34033 42029 42045 . .04809622 571 10003 10001 24015 24029 34015 34033 42029 42045 . .19725946 581 10003 10001 24015 24029 34015 34033 42029 42045 . .09862588 589 10003 10001 24015 24029 34015 34033 42029 42045 . .000030619405 592 10003 10001 24015 24029 34015 34033 42029 42045 . .032881062 603 10003 10001 24015 24029 34015 34033 42029 42045 . .10993586 604 10003 10001 24015 24029 34015 34033 42029 42045 . .10217185 605 10003 10001 24015 24029 34015 34033 42029 42045 . .19657345 607 10003 10001 24015 24029 34015 34033 42029 42045 . .13150631 609 10003 10001 24015 24029 34015 34033 42029 42045 . .05785644 614 10003 10001 24015 24029 34015 34033 42029 42045 . .013283235 616 10003 10001 24015 24029 34015 34033 42029 42045 . .1545057 617 10003 10001 24015 24029 34015 34033 42029 42045 . .3344066 618 10003 10001 24015 24029 34015 34033 42029 42045 . .0448908 627 10003 10001 24015 24029 34015 34033 42029 42045 . .00052990305 628 10003 10001 24015 24029 34015 34033 42029 42045 . .14675705 629 10003 10001 24015 24029 34015 34033 42029 42045 . .2461496 632 10003 10001 24015 24029 34015 34033 42029 42045 . .26902437 641 10003 10001 24015 24029 34015 34033 42029 42045 . .15828156 642 10003 10001 24015 24029 34015 34033 42029 42045 . .03831706 643 10003 10001 24015 24029 34015 34033 42029 42045 . .12734681 652 10003 10001 24015 24029 34015 34033 42029 42045 . .020762434 654 10003 10001 24015 24029 34015 34033 42029 42045 . .23013605 655 10003 10001 24015 24029 34015 34033 42029 42045 . .2958892 664 10003 10001 24015 24029 34015 34033 42029 42045 . .04677977 666 10003 10001 24015 24029 34015 34033 42029 42045 . .2354119 676 10003 10001 24015 24029 34015 34033 42029 42045 . .19088583 677 10003 10001 24015 24029 34015 34033 42029 42045 . .03283391 679 10003 10001 24015 24029 34015 34033 42029 42045 . .02849763 548 24029 10001 10003 24003 24005 24015 24025 24035 . .0851827 558 24029 10001 10003 24003 24005 24015 24025 24035 . .21356656 559 24029 10001 10003 24003 24005 24015 24025 24035 . .0032093804 570 24029 10001 10003 24003 24005 24015 24025 24035 . .04905337 582 24029 10001 10003 24003 24005 24015 24025 24035 . .09919176 590 24029 10001 10003 24003 24005 24015 24025 24035 . .002201769 603 24029 10001 10003 24003 24005 24015 24025 24035 . .118102 604 24029 10001 10003 24003 24005 24015 24025 24035 . .08999056 605 24029 10001 10003 24003 24005 24015 24025 24035 . .1750362 615 24029 10001 10003 24003 24005 24015 24025 24035 . .033037722 617 24029 10001 10003 24003 24005 24015 24025 24035 . .3616422 618 24029 10001 10003 24003 24005 24015 24025 24035 . .0814766 627 24029 10001 10003 24003 24005 24015 24025 24035 . .0002337162 628 24029 10001 10003 24003 24005 24015 24025 24035 . .15745997 629 24029 10001 10003 24003 24005 24015 24025 24035 . .23013598 630 24029 10001 10003 24003 24005 24015 24025 24035 . .11528352 632 24029 10001 10003 24003 24005 24015 24025 24035 . .26118797 637 24029 10001 10003 24003 24005 24015 24025 24035 . .0008544384 639 24029 10001 10003 24003 24005 24015 24025 24035 . .006891858 641 24029 10001 10003 24003 24005 24015 24025 24035 . .12527087 642 24029 10001 10003 24003 24005 24015 24025 24035 . .03771503 654 24029 10001 10003 24003 24005 24015 24025 24035 . .2174835 655 24029 10001 10003 24003 24005 24015 24025 24035 . .28498343 664 24029 10001 10003 24003 24005 24015 24025 24035 . .032904003 666 24029 10001 10003 24003 24005 24015 24025 24035 . .2236287 676 24029 10001 10003 24003 24005 24015 24025 24035 . .178637 548 34033 10001 10003 34011 34015 . . . . .06330993 558 34033 10001 10003 34011 34015 . . . . .20197415 559 34033 10001 10003 34011 34015 . . . . .014636617 569 34033 10001 10003 34011 34015 . . . . .016581196 570 34033 10001 10003 34011 34015 . . . . .034968823 571 34033 10001 10003 34011 34015 . . . . .19939888 578 34033 10001 10003 34011 34015 . . . . .00024615144 581 34033 10001 10003 34011 34015 . . . . .09488098 582 34033 10001 10003 34011 34015 . . . . .09867228 592 34033 10001 10003 34011 34015 . . . . .04531752 end format %tmMonth_CCYY date
I have a monthly panel dataset on counties in the US. Fips identifies a county with smoke being a variable I am investigating. The variables fipsneighbor1 - fipsneighbor8 give me the fips code of neighbouring counties. What I want to do is generate a variable that calculates the average of smoke of all adjacent counties, i.e. for the county with fips = 10001 I would have a variable adjsmoke equal to the average smoke of the adjacent fips codes 10003 10005 24011 24029 24035 34009 34011 34033 on that particular date.
In terms of why my neighbouring fips code are like this for explanation purposes I had a long data set (shown below)
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input long(fips fipsneighbor) 1001 1021 1001 1047 1001 1051 1001 1085 1001 1101 1003 1025 1003 1053 1003 1097 1003 1099 1003 1129 1003 12033 1005 1011 1005 1045 1005 1067 1005 1109 1005 1113 1005 13061 1005 13239 1005 13259 end
Code:
bysort fips (fipsneighbor) : gen id = sum(fipsneighbor!= fipsneighbor[_n-1]) reshape wide fipsneighbor , i(fips) j(id)
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input long(fips fipsneighbor1 fipsneighbor2 fipsneighbor3 fipsneighbor4 fipsneighbor5 fipsneighbor6 fipsneighbor7 fipsneighbor8 fipsneighbor9 fipsneighbor10 fipsneighbor11 fipsneighbor12 fipsneighbor13 fipsneighbor14) 1001 1021 1047 1051 1085 1101 . . . . . . . . . 1003 1025 1053 1097 1099 1129 12033 . . . . . . . . 1005 1011 1045 1067 1109 1113 13061 13239 13259 . . . . . . 1007 1021 1065 1073 1105 1117 1125 . . . . . . . . 1009 1043 1055 1073 1095 1115 1127 . . . . . . . . 1011 1005 1087 1101 1109 1113 . . . . . . . . . 1013 1035 1039 1041 1085 1099 1131 . . . . . . . . 1015 1019 1029 1055 1115 1121 . . . . . . . . . 1017 1081 1111 1123 13145 13285 . . . . . . . . . 1019 1015 1029 1049 1055 13055 13115 13233 . . . . . . . 1021 1001 1007 1037 1047 1051 1105 1117 . . . . . . . 1023 1025 1091 1119 1129 28023 28075 28153 . . . . . . . 1025 1003 1023 1091 1099 1129 1131 . . . . . . . . 1027 1029 1037 1111 1121 1123 . . . . . . . . . 1029 1015 1019 1027 1111 1121 13045 13143 13233 . . . . . .
Thanks in advance for any guidance.
Comment