I am currently working with a country year panel dataset, which has the following variables for several items (e.g., rice, potatoes, millet) : production (in tonnes), production_value(million USD), producer price (prodprice, denoted by USD/tonne). Each country belongs to a particular region (countrygroup), e.g. AGO to Middle Africa, ALB to Southern Europe. See example below:
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input str3 iso3code int(year itemcode) str44 item double production float(production_value prodprice) str39 countrygroup "AGO" 2010 27 "Rice, paddy" 17697 7.52894 425.4 "Middle Africa" "AGO" 2010 56 "Maize" 1072737 472.7219 440.7 "Middle Africa" "AGO" 2010 79 "Millet" 40723 12.805456 314.5 "Middle Africa" "AGO" 2010 83 "Sorghum" 46787 14.712298 314.5 "Middle Africa" "AGO" 2010 116 "Potatoes" 841279 408.2558 485.3 "Middle Africa" "AGO" 2010 122 "Sweet potatoes" 986563 284.46454 288.3 "Middle Africa" "AGO" 2010 125 "Cassava" 13858681 4599.167 331.9 "Middle Africa" "AGO" 2010 176 "Beans, dry" 250117 363.586 1453.7 "Middle Africa" "AGO" 2010 236 "Soybeans" 6087 6.623092 1088.1 "Middle Africa" "AGO" 2010 242 "Groundnuts, with shell" 115164 119.66787 1039.1 "Middle Africa" "AGO" 2010 463 "Vegetables, fresh nes" 336716 135.01175 410.2 "Middle Africa" "AGO" 2010 486 "Bananas" 2047955 356.5315 174.1 "Middle Africa" "AGO" 2010 574 "Pineapples" 313365 308.913 985.8 "Middle Africa" "AGO" 2010 656 "Coffee, green" 9951 5.088878 511.4 "Middle Africa" "AGO" 2011 27 "Rice, paddy" 23209 10.253644 441.8 "Middle Africa" "AGO" 2011 56 "Maize" 1262222 502.5521 398.1 "Middle Africa" "AGO" 2011 79 "Millet" 61226 17.793945 290.6 "Middle Africa" "AGO" 2011 83 "Sorghum" 62169 19.921137 320.4 "Middle Africa" "AGO" 2011 116 "Potatoes" 841252 385.0954 457.8 "Middle Africa" "AGO" 2011 122 "Sweet potatoes" 1045104 287.04694 274.7 "Middle Africa" "AGO" 2011 125 "Cassava" 14333509 4409.8525 307.7 "Middle Africa" "AGO" 2011 176 "Beans, dry" 303521 476.2774 1569.2 "Middle Africa" "AGO" 2011 236 "Soybeans" 7743 8.242956 1064.6 "Middle Africa" "AGO" 2011 242 "Groundnuts, with shell" 161116 158.65514 984.7 "Middle Africa" "AGO" 2011 463 "Vegetables, fresh nes" 343142 135.16719 401.3 "Middle Africa" "AGO" 2011 486 "Bananas" 2646073 546.48376 206.5 "Middle Africa" "AGO" 2011 574 "Pineapples" 326352 300.52188 920.9 "Middle Africa" "AGO" 2011 656 "Coffee, green" 10192 7.161056 702.6 "Middle Africa" "AGO" 2012 27 "Rice, paddy" 21492 15.218292 708.1 "Middle Africa" "AGO" 2012 56 "Maize" 454343 315.0534 693.4 "Middle Africa" "AGO" 2012 79 "Millet" 18379 6.87278 373.9 "Middle Africa" "AGO" 2012 83 "Sorghum" 11491 4.670162 406.4 "Middle Africa" "AGO" 2012 116 "Potatoes" 654160 450.1857 688.2 "Middle Africa" "AGO" 2012 122 "Sweet potatoes" 644854 303.28442 470.3 "Middle Africa" "AGO" 2012 125 "Cassava" 10636400 4445.3906 417.9 "Middle Africa" "AGO" 2012 176 "Beans, dry" 96217 220.71828 2294 "Middle Africa" "AGO" 2012 236 "Soybeans" 5898 7.351807 1246.5 "Middle Africa" "AGO" 2012 242 "Groundnuts, with shell" 66616 83.73406 1257 "Middle Africa" "AGO" 2012 463 "Vegetables, fresh nes" 360000 224.74557 624.3 "Middle Africa" "AGO" 2012 486 "Bananas" 2991454 1504.0627 502.8 "Middle Africa" "AGO" 2012 574 "Pineapples" 280906 41.19376 146.6 "Middle Africa" "AGO" 2013 27 "Rice, paddy" 37608 16.170326 430 "Middle Africa" "AGO" 2013 56 "Maize" 1548750 600.4482 387.7 "Middle Africa" "AGO" 2013 79 "Millet" 38603 10.91878 282.8 "Middle Africa" "AGO" 2013 83 "Sorghum" 46423 14.57839 314 "Middle Africa" "AGO" 2013 116 "Potatoes" 670136 298.55328 445.5 "Middle Africa" "AGO" 2013 122 "Sweet potatoes" 1199749 321.3227 267.8 "Middle Africa" "AGO" 2013 125 "Cassava" 16411674 4924.27 300 "Middle Africa" "AGO" 2013 176 "Beans, dry" 311988 476.7179 1528 "Middle Africa" "AGO" 2013 236 "Soybeans" 10326 10.698492 1036.1 "Middle Africa" "AGO" 2013 242 "Groundnuts, with shell" 192028 162.80493 847.8 "Middle Africa" "AGO" 2013 358 "Cabbages and other brassicas" 308763 138.67711 449.1 "Middle Africa" "AGO" 2013 388 "Tomatoes" 17000 4.900004 288.2 "Middle Africa" "AGO" 2013 463 "Vegetables, fresh nes" 350000 84.56429 241.6 "Middle Africa" "AGO" 2013 486 "Bananas" 3095013 919.9907 297.2 "Middle Africa" "AGO" 2013 574 "Pineapples" 479357 335.536 700 "Middle Africa" "AGO" 2014 27 "Rice, paddy" 42288 17.87684 422.2 "Middle Africa" "AGO" 2014 56 "Maize" 1686869 650.0447 384.8 "Middle Africa" "AGO" 2014 79 "Millet" 43056 10.83757 251.4 "Middle Africa" "AGO" 2014 83 "Sorghum" 48133 14.861218 308.3 "Middle Africa" "AGO" 2014 116 "Potatoes" 671468 283.85657 422.2 "Middle Africa" "AGO" 2014 122 "Sweet potatoes" 1928954 508.5243 263.3 "Middle Africa" "AGO" 2014 125 "Cassava" 7638880 1789.7117 234 "Middle Africa" "AGO" 2014 176 "Beans, dry" 401500 587.0212 1460.1 "Middle Africa" "AGO" 2014 236 "Soybeans" 13763 14.019545 1017.3 "Middle Africa" "AGO" 2014 242 "Groundnuts, with shell" 252480 211.84665 837.9 "Middle Africa" "AGO" 2014 358 "Cabbages and other brassicas" 335467 148.13731 441 "Middle Africa" "AGO" 2014 388 "Tomatoes" 16295 4.498381 274.7 "Middle Africa" "AGO" 2014 463 "Vegetables, fresh nes" 348830 81.98099 237.2 "Middle Africa" "AGO" 2014 486 "Bananas" 3483432 1077.2948 308.8 "Middle Africa" "AGO" 2014 574 "Pineapples" 599156 420.518 700.9 "Middle Africa" "ALB" 1993 27 "Rice, paddy" 585 .425154 726.7 "Southern Europe" "ALB" 1993 56 "Maize" 175751 42.7409 243.2 "Southern Europe" "ALB" 1993 116 "Potatoes" 101527 32.539894 320.5 "Southern Europe" "ALB" 1993 176 "Beans, dry" 23509 10.50899 447 "Southern Europe" "ALB" 1993 388 "Tomatoes" 106038 96.8802 913.7 "Southern Europe" "ALB" 1993 463 "Vegetables, fresh nes" 51400 22.471045 437.2 "Southern Europe" "ALB" 1993 490 "Oranges" 13300 6.767994 508.9 "Southern Europe" "ALB" 1993 515 "Apples" 10000 4.610771 461.1 "Southern Europe" "ALB" 1993 536 "Plums and sloes" 5600 1.527177 272.7 "Southern Europe" "ALB" 1994 27 "Rice, paddy" 0 0 570.7 "Southern Europe" "ALB" 1994 56 "Maize" 193261 40.44 209.3 "Southern Europe" "ALB" 1994 71 "Rye" 3765 .811703 215.6 "Southern Europe" "ALB" 1994 116 "Potatoes" 88918 30.07055 338.2 "Southern Europe" "ALB" 1994 463 "Vegetables, fresh nes" 23100 9.862684 427 "Southern Europe" "ALB" 1994 536 "Plums and sloes" 7862 2.130532 275.8 "Southern Europe" "ALB" 1995 15 "Wheat" 405342 65.59109 161.8 "Southern Europe" "ALB" 1995 27 "Rice, paddy" 0 0 616 "Southern Europe" "ALB" 1995 56 "Maize" 215566 39.53313 183.4 "Southern Europe" "ALB" 1995 75 "Oats" 12989 2.802449 215.8 "Southern Europe" "ALB" 1995 116 "Potatoes" 133910 30.336416 226.5 "Southern Europe" "ALB" 1995 176 "Beans, dry" 20274 17.715624 873.8 "Southern Europe" "ALB" 1995 260 "Olives" 38763 9.617832 248.1 "Southern Europe" "ALB" 1995 388 "Tomatoes" 150000 48.545 323.6 "Southern Europe" "ALB" 1995 490 "Oranges" 3782 1.631975 431.5 "Southern Europe" "ALB" 1995 515 "Apples" 10000 3.236333 323.6 "Southern Europe" "ALB" 1995 521 "Pears" 3000 1.294533 431.5 "Southern Europe" "ALB" 1995 531 "Cherries" 3000 1.682893 561 "Southern Europe" "ALB" 1995 536 "Plums and sloes" 10000 3.236333 323.6 "Southern Europe" "ALB" 1995 560 "Grapes" 55493 15.923987 287 "Southern Europe" end
The previous example does not illustrate that there are multiply countries per countrygroup, since my dataset is large. So in the following example, I show this (by considering only the year 2010):
Code:
* Example generated by -dataex-. To install: ssc install dataex clear input str3 iso3code int(year itemcode) str44 item double production float(production_value prodprice) str39 countrygroup "AUS" 2010 15 "Wheat" 21834010 4366.163 200 "Australia and New Zealand" "AUS" 2010 27 "Rice, paddy" 196684 82.45086 419.2 "Australia and New Zealand" "AUS" 2010 44 "Barley" 7864551 1240.8302 157.8 "Australia and New Zealand" "AUS" 2010 75 "Oats" 1161606 170.48602 146.8 "Australia and New Zealand" "AUS" 2010 83 "Sorghum" 1507635 271.058 179.8 "Australia and New Zealand" "AUS" 2010 116 "Potatoes" 1278118 562.7586 440.3 "Australia and New Zealand" "AUS" 2010 156 "Sugar cane" 31234824 1260.6708 40.4 "Australia and New Zealand" "AUS" 2010 191 "Chick peas" 487046 193.00284 396.3 "Australia and New Zealand" "AUS" 2010 210 "Lupins" 822963 187.97046 228.4 "Australia and New Zealand" "AUS" 2010 234 "Nuts, nes" 37680 72.58388 1926.3 "Australia and New Zealand" "AUS" 2010 270 "Rapeseed" 1907272 769.7953 403.6 "Australia and New Zealand" "AUS" 2010 328 "Seed cotton" 939000 1843.2716 1963 "Australia and New Zealand" "AUS" 2010 329 "Cottonseed" 550000 194.5353 353.7 "Australia and New Zealand" "AUS" 2010 388 "Tomatoes" 471883 317.7169 673.3 "Australia and New Zealand" "AUS" 2010 403 "Onions, dry" 259947 165.00642 634.8 "Australia and New Zealand" "AUS" 2010 426 "Carrots and turnips" 267442 161.1777 602.7 "Australia and New Zealand" "AUS" 2010 449 "Mushrooms and truffles" 41295 216.29353 5237.8 "Australia and New Zealand" "AUS" 2010 486 "Bananas" 302173 447.6495 1481.4 "Australia and New Zealand" "AUS" 2010 490 "Oranges" 391343 280.00266 715.5 "Australia and New Zealand" "AUS" 2010 495 "Tangerines, mandarins, clementines, satsumas" 91002 113.52717 1247.5 "Australia and New Zealand" "AUS" 2010 515 "Apples" 264401 368.652 1394.3 "Australia and New Zealand" "AUS" 2010 521 "Pears" 95111 68.051125 715.5 "Australia and New Zealand" "AUS" 2010 534 "Peaches and nectarines" 77683 103.32465 1330.1 "Australia and New Zealand" "AUS" 2010 544 "Strawberries" 29334 194.81384 6641.2 "Australia and New Zealand" "AUS" 2010 560 "Grapes" 1684345 1018.1844 604.5 "Australia and New Zealand" "AUS" 2010 571 "Mangoes, mangosteens, guavas" 44342 103.7207 2339.1 "Australia and New Zealand" "AUS" 2010 767 "Cotton lint" 352049 2530.98 6543.4 "Australia and New Zealand" "NZL" 2010 15 "Wheat" 444890 130.35107 293 "Australia and New Zealand" "NZL" 2010 44 "Barley" 308298 79.08302 256.5 "Australia and New Zealand" "NZL" 2010 56 "Maize" 188812 43.53535 230.6 "Australia and New Zealand" "NZL" 2010 75 "Oats" 47608 14.64773 307.7 "Australia and New Zealand" "NZL" 2010 187 "Peas, dry" 37094 18.896685 509.4 "Australia and New Zealand" "NZL" 2010 515 "Apples" 444154 148.87888 335.1 "Australia and New Zealand" "NZL" 2010 592 "Kiwi fruit" 434120 401.9532 925.9 "Australia and New Zealand" "BRB" 2010 122 "Sweet potatoes" 1176 2.774117 2358.9 "Caribbean" "BRB" 2010 125 "Cassava" 400 .599657 1499.1 "Caribbean" "BRB" 2010 137 "Yams" 807 1.832502 2270.8 "Caribbean" "BRB" 2010 358 "Cabbages and other brassicas" 60 .144182 2403 "Caribbean" "BRB" 2010 388 "Tomatoes" 718 2.176511 3031.4 "Caribbean" "BRB" 2010 394 "Pumpkins, squash and gourds" 180 .214289 1190.5 "Caribbean" "BRB" 2010 397 "Cucumbers and gherkins" 1148 1.784287 1554.3 "Caribbean" "BRB" 2010 401 "Chillies and peppers, green" 394 1.56786 3979.3 "Caribbean" "BRB" 2010 423 "String beans" 222 1.120785 5048.6 "Caribbean" "BRB" 2010 426 "Carrots and turnips" 264 .905041 3428.2 "Caribbean" "BRB" 2010 430 "Okra" 303 .58784 1940.1 "Caribbean" "BRB" 2010 568 "Melons, other (inc.cantaloupes)" 241 .353323 1466.1 "Caribbean" "DOM" 2010 27 "Rice, paddy" 850228 391.0197 459.9 "Caribbean" "DOM" 2010 56 "Maize" 35078 13.208626 376.5 "Caribbean" "DOM" 2010 83 "Sorghum" 1150 .30585 265.9 "Caribbean" "DOM" 2010 116 "Potatoes" 52381 24.655537 470.7 "Caribbean" "DOM" 2010 122 "Sweet potatoes" 53345 14.96302 280.5 "Caribbean" "DOM" 2010 125 "Cassava" 204920 53.27082 260 "Caribbean" "DOM" 2010 135 "Yautia (cocoyam)" 29621 24.69541 833.7 "Caribbean" "DOM" 2010 137 "Yams" 26958 20.23583 750.6 "Caribbean" "DOM" 2010 156 "Sugar cane" 4577122 110.196 24.1 "Caribbean" "DOM" 2010 176 "Beans, dry" 33021 36.168846 1095.3 "Caribbean" "DOM" 2010 197 "Pigeon peas" 23536 14.977043 636.3 "Caribbean" "DOM" 2010 242 "Groundnuts, with shell" 3841 2.678128 697.3 "Caribbean" "DOM" 2010 249 "Coconuts" 128370 33.084774 257.7 "Caribbean" "DOM" 2010 358 "Cabbages and other brassicas" 42689 18.71303 438.4 "Caribbean" "DOM" 2010 372 "Lettuce and chicory" 3327 3.129794 940.7 "Caribbean" "DOM" 2010 388 "Tomatoes" 240254 153.831 640.3 "Caribbean" "DOM" 2010 394 "Pumpkins, squash and gourds" 38246 14.035492 367 "Caribbean" "DOM" 2010 397 "Cucumbers and gherkins" 6945 1.58207 227.8 "Caribbean" "DOM" 2010 399 "Eggplants (aubergines)" 22244 8.031401 361.1 "Caribbean" "DOM" 2010 401 "Chillies and peppers, green" 37026 29.07891 785.4 "Caribbean" "DOM" 2010 403 "Onions, dry" 48534 47.18024 972.1 "Caribbean" "DOM" 2010 406 "Garlic" 2001 6.206559 3102.4 "Caribbean" "DOM" 2010 426 "Carrots and turnips" 26533 8.764905 330.3 "Caribbean" "DOM" 2010 486 "Bananas" 892753 107.41273 120.3 "Caribbean" "DOM" 2010 489 "Plantains and others" 460254 169.28397 367.8 "Caribbean" "DOM" 2010 490 "Oranges" 138034 23.035366 166.9 "Caribbean" "DOM" 2010 497 "Lemons and limes" 11909 .740328 62.2 "Caribbean" "DOM" 2010 507 "Grapefruit (inc. pomelos)" 17109 21.78953 1273.6 "Caribbean" "DOM" 2010 568 "Melons, other (inc.cantaloupes)" 20447 6.503925 318.1 "Caribbean" "DOM" 2010 571 "Mangoes, mangosteens, guavas" 7867 3.250513 413.2 "Caribbean" "DOM" 2010 572 "Avocados" 285590 110.59303 387.2 "Caribbean" "DOM" 2010 574 "Pineapples" 165724 38.901 234.7 "Caribbean" "DOM" 2010 600 "Papayas" 498071 99.0816 198.9 "Caribbean" "DOM" 2010 656 "Coffee, green" 21876 56.0862 2563.8 "Caribbean" "DOM" 2010 661 "Cocoa, beans" 58334 134.20418 2300.6 "Caribbean" "DOM" 2010 826 "Tobacco, unmanufactured" 8066 23.60285 2926.2 "Caribbean" "GRD" 2010 56 "Maize" 343 .278487 816.5 "Caribbean" "GRD" 2010 122 "Sweet potatoes" 125 .179061 1428.9 "Caribbean" "GRD" 2010 125 "Cassava" 217 .132696 612.4 "Caribbean" "GRD" 2010 137 "Yams" 529 .647979 1224.8 "Caribbean" "GRD" 2010 149 "Roots and tubers, nes" 2228 2.889316 1298.3 "Caribbean" "GRD" 2010 176 "Beans, dry" 117 .209491 1786.2 "Caribbean" "GRD" 2010 197 "Pigeon peas" 740 1.386155 1837.2 "Caribbean" "GRD" 2010 249 "Coconuts" 6474 7.929283 1224.8 "Caribbean" "GRD" 2010 358 "Cabbages and other brassicas" 189 .359749 1905.2 "Caribbean" "GRD" 2010 372 "Lettuce and chicory" 99 .106864 1088.7 "Caribbean" "GRD" 2010 388 "Tomatoes" 261 .549447 2109.4 "Caribbean" "GRD" 2010 393 "Cauliflowers and broccoli" 23 .074746 3266.1 "Caribbean" "GRD" 2010 394 "Pumpkins, squash and gourds" 326 .346289 1061.5 "Caribbean" "GRD" 2010 397 "Cucumbers and gherkins" 139 .169698 1224.8 "Caribbean" "GRD" 2010 426 "Carrots and turnips" 77 .141305 1837.2 "Caribbean" "GRD" 2010 463 "Vegetables, fresh nes" 2185 3.887665 1777.3 "Caribbean" "GRD" 2010 486 "Bananas" 2703 1.260492 466.6 "Caribbean" "GRD" 2010 489 "Plantains and others" 235 .240194 1020.7 "Caribbean" end
What I would like to do is estimate regional averages for the price of each item. I figured it would look something like -bys countrygroup year itemcode: mean(prodprice), but the problem is that I would like the regional averages to be weighted by a country's production of the item, and I'm not sure how I could add this to the code. I am aware of Stata modules that are written that could do this, but I always try to improve my understanding of Stata before I resort to Stata modules.
If someone has any suggestions how I could achieve this, it would be greatly appreciated. Ofcourse, if using a Stata module is in the end the best thing to do here, I would happily do that.
Best,
Satya
0 Response to Generating weighted averages for subgroups
Post a Comment