Yearly to monthly values in panel data

[Rolf Bosch]

Hey,

I merged yearly panel data with monthly data and as a result now have a monthly panel dataset. However, now I have for GDP and FX_NOR (yearly variables) the same observations for each month during a certain year. How can I change those values to something like a weighted average for each month? I mean, if for China GDP in 2001m1 = 10 and in 2002m1 = 130, I want GDP 2001m2 = 20, 2001m3 = 30, etc..



mdate is my monthly time variable



* Example generated by -dataex-. For more info, type help dataex
clear
input int CountryID str36 Country str7 Time double Fish_EX int year double(Salmon_EX GDP FX_NOR FX) byte _merge float(mdate S_EX_growth S_EX_growth_12 GDP_growth)
1 "AD Andorra" "2000M01" 0 2000 0 1429049198.4521837 .1249738454357563 8.80184166666667 3 480 . . .
1 "AD Andorra" "2000M02" 0 2000 0 1429049198.4521837 .1249738454357563 8.80184166666667 3 481 . . 0
1 "AD Andorra" "2000M03" 0 2000 0 1429049198.4521837 .1249738454357563 8.80184166666667 3 482 . . 0
1 "AD Andorra" "2000M04" 0 2000 0 1429049198.4521837 .1249738454357563 8.80184166666667 3 483 . . 0
1 "AD Andorra" "2000M05" 0 2000 0 1429049198.4521837 .1249738454357563 8.80184166666667 3 484 . . 0
1 "AD Andorra" "2000M06" 0 2000 0 1429049198.4521837 .1249738454357563 8.80184166666667 3 485 . . 0
1 "AD Andorra" "2000M07" 0 2000 0 1429049198.4521837 .1249738454357563 8.80184166666667 3 486 . . 0
1 "AD Andorra" "2000M08" 0 2000 0 1429049198.4521837 .1249738454357563 8.80184166666667 3 487 . . 0
1 "AD Andorra" "2000M09" 0 2000 0 1429049198.4521837 .1249738454357563 8.80184166666667 3 488 . . 0
1 "AD Andorra" "2000M10" 0 2000 0 1429049198.4521837 .1249738454357563 8.80184166666667 3 489 . . 0
1 "AD Andorra" "2000M11" 0 2000 0 1429049198.4521837 .1249738454357563 8.80184166666667 3 490 . . 0
1 "AD Andorra" "2000M12" 0 2000 0 1429049198.4521837 .1249738454357563 8.80184166666667 3 491 . . 0
1 "AD Andorra" "2001M01" 0 2001 0 1546926174.4966445 .12233566589758925 8.99165416666667 3 492 . . .0824863
1 "AD Andorra" "2001M02" 0 2001 0 1546926174.4966445 .12233566589758925 8.99165416666667 3 493 . . 0
1 "AD Andorra" "2001M03" 0 2001 0 1546926174.4966445 .12233566589758925 8.99165416666667 3 494 . . 0
1 "AD Andorra" "2001M04" 0 2001 0 1546926174.4966445 .12233566589758925 8.99165416666667 3 495 . . 0
1 "AD Andorra" "2001M05" 19.959 2001 0 1546926174.4966445 .12233566589758925 8.99165416666667 3 496 . . 0
1 "AD Andorra" "2001M06" 0 2001 0 1546926174.4966445 .12233566589758925 8.99165416666667 3 497 . . 0
1 "AD Andorra" "2001M07" 0 2001 0 1546926174.4966445 .12233566589758925 8.99165416666667 3 498 . . 0
1 "AD Andorra" "2001M08" 0 2001 0 1546926174.4966445 .12233566589758925 8.99165416666667 3 499 . . 0
1 "AD Andorra" "2001M09" 0 2001 0 1546926174.4966445 .12233566589758925 8.99165416666667 3 500 . . 0
1 "AD Andorra" "2001M10" 0 2001 0 1546926174.4966445 .12233566589758925 8.99165416666667 3 501 . . 0
1 "AD Andorra" "2001M11" 0 2001 0 1546926174.4966445 .12233566589758925 8.99165416666667 3 502 . . 0
1 "AD Andorra" "2001M12" 0 2001 0 1546926174.4966445 .12233566589758925 8.99165416666667 3 503 . . 0
1 "AD Andorra" "2002M01" 0 2002 0 1755910031.9969885 .13777936776095986 7.98377883333333 3 504 . . .1350962
1 "AD Andorra" "2002M02" 0 2002 0 1755910031.9969885 .13777936776095986 7.98377883333333 3 505 . . 0
1 "AD Andorra" "2002M03" 0 2002 0 1755910031.9969885 .13777936776095986 7.98377883333333 3 506 . . 0
1 "AD Andorra" "2002M04" 0 2002 0 1755910031.9969885 .13777936776095986 7.98377883333333 3 507 . . 0
1 "AD Andorra" "2002M05" 0 2002 0 1755910031.9969885 .13777936776095986 7.98377883333333 3 508 . . 0
1 "AD Andorra" "2002M06" 0 2002 0 1755910031.9969885 .13777936776095986 7.98377883333333 3 509 . . 0
1 "AD Andorra" "2002M07" 0 2002 0 1755910031.9969885 .13777936776095986 7.98377883333333 3 510 . . 0
1 "AD Andorra" "2002M08" 0 2002 0 1755910031.9969885 .13777936776095986 7.98377883333333 3 511 . . 0
1 "AD Andorra" "2002M09" 0 2002 0 1755910031.9969885 .13777936776095986 7.98377883333333 3 512 . . 0
1 "AD Andorra" "2002M10" 0 2002 0 1755910031.9969885 .13777936776095986 7.98377883333333 3 513 . . 0
1 "AD Andorra" "2002M11" 0 2002 0 1755910031.9969885 .13777936776095986 7.98377883333333 3 514 . . 0
1 "AD Andorra" "2002M12" 0 2002 0 1755910031.9969885 .13777936776095986 7.98377883333333 3 515 . . 0
1 "AD Andorra" "2003M01" 0 2003 0 2361726862.3024826 .12711475402118103 7.08021666666667 3 516 . . .3450159
1 "AD Andorra" "2003M02" 0 2003 0 2361726862.3024826 .12711475402118103 7.08021666666667 3 517 . . 0
1 "AD Andorra" "2003M03" 0 2003 0 2361726862.3024826 .12711475402118103 7.08021666666667 3 518 . . 0
1 "AD Andorra" "2003M04" 0 2003 0 2361726862.3024826 .12711475402118103 7.08021666666667 3 519 . . 0
1 "AD Andorra" "2003M05" 0 2003 0 2361726862.3024826 .12711475402118103 7.08021666666667 3 520 . . 0
1 "AD Andorra" "2003M06" 0 2003 0 2361726862.3024826 .12711475402118103 7.08021666666667 3 521 . . 0
1 "AD Andorra" "2003M07" 0 2003 0 2361726862.3024826 .12711475402118103 7.08021666666667 3 522 . . 0
1 "AD Andorra" "2003M08" 0 2003 0 2361726862.3024826 .12711475402118103 7.08021666666667 3 523 . . 0
1 "AD Andorra" "2003M09" 0 2003 0 2361726862.3024826 .12711475402118103 7.08021666666667 3 524 . . 0
1 "AD Andorra" "2003M10" 0 2003 0 2361726862.3024826 .12711475402118103 7.08021666666667 3 525 . . 0
1 "AD Andorra" "2003M11" 0 2003 0 2361726862.3024826 .12711475402118103 7.08021666666667 3 526 . . 0
1 "AD Andorra" "2003M12" 0 2003 0 2361726862.3024826 .12711475402118103 7.08021666666667 3 527 . . 0
1 "AD Andorra" "2004M01" 0 2004 0 2894921777.99851 .11867968846581783 6.74083333333333 3 528 . . .22576486
1 "AD Andorra" "2004M02" 0 2004 0 2894921777.99851 .11867968846581783 6.74083333333333 3 529 . . 0
1 "AD Andorra" "2004M03" 0 2004 0 2894921777.99851 .11867968846581783 6.74083333333333 3 530 . . 0
1 "AD Andorra" "2004M04" 0 2004 0 2894921777.99851 .11867968846581783 6.74083333333333 3 531 . . 0
1 "AD Andorra" "2004M05" 0 2004 0 2894921777.99851 .11867968846581783 6.74083333333333 3 532 . . 0
1 "AD Andorra" "2004M06" 0 2004 0 2894921777.99851 .11867968846581783 6.74083333333333 3 533 . . 0
1 "AD Andorra" "2004M07" 0 2004 0 2894921777.99851 .11867968846581783 6.74083333333333 3 534 . . 0
1 "AD Andorra" "2004M08" 0 2004 0 2894921777.99851 .11867968846581783 6.74083333333333 3 535 . . 0
1 "AD Andorra" "2004M09" 0 2004 0 2894921777.99851 .11867968846581783 6.74083333333333 3 536 . . 0
1 "AD Andorra" "2004M10" 0 2004 0 2894921777.99851 .11867968846581783 6.74083333333333 3 537 . . 0
1 "AD Andorra" "2004M11" 0 2004 0 2894921777.99851 .11867968846581783 6.74083333333333 3 538 . . 0
1 "AD Andorra" "2004M12" 0 2004 0 2894921777.99851 .11867968846581783 6.74083333333333 3 539 . . 0
1 "AD Andorra" "2005M01" 0 2005 0 3159905484.3924885 .12417539774932093 6.4425 3 540 . . .09153398
1 "AD Andorra" "2005M02" 0 2005 0 3159905484.3924885 .12417539774932093 6.4425 3 541 . . 0
1 "AD Andorra" "2005M03" 0 2005 0 3159905484.3924885 .12417539774932093 6.4425 3 542 . . 0
1 "AD Andorra" "2005M04" 0 2005 0 3159905484.3924885 .12417539774932093 6.4425 3 543 . . 0
1 "AD Andorra" "2005M05" 0 2005 0 3159905484.3924885 .12417539774932093 6.4425 3 544 . . 0
1 "AD Andorra" "2005M06" 0 2005 0 3159905484.3924885 .12417539774932093 6.4425 3 545 . . 0
1 "AD Andorra" "2005M07" 0 2005 0 3159905484.3924885 .12417539774932093 6.4425 3 546 . . 0
1 "AD Andorra" "2005M08" 0 2005 0 3159905484.3924885 .12417539774932093 6.4425 3 547 . . 0
1 "AD Andorra" "2005M09" 0 2005 0 3159905484.3924885 .12417539774932093 6.4425 3 548 . . 0
1 "AD Andorra" "2005M10" 0 2005 0 3159905484.3924885 .12417539774932093 6.4425 3 549 . . 0
1 "AD Andorra" "2005M11" 0 2005 0 3159905484.3924885 .12417539774932093 6.4425 3 550 . . 0
1 "AD Andorra" "2005M12" 0 2005 0 3159905484.3924885 .12417539774932093 6.4425 3 551 . . 0
1 "AD Andorra" "2006M01" 0 2006 0 3456442102.6220045 .1247401247401248 6.41333333333333 3 552 . . .09384351
1 "AD Andorra" "2006M02" 0 2006 0 3456442102.6220045 .1247401247401248 6.41333333333333 3 553 . . 0
1 "AD Andorra" "2006M03" 0 2006 0 3456442102.6220045 .1247401247401248 6.41333333333333 3 554 . . 0
1 "AD Andorra" "2006M04" 0 2006 0 3456442102.6220045 .1247401247401248 6.41333333333333 3 555 . . 0
1 "AD Andorra" "2006M05" 0 2006 0 3456442102.6220045 .1247401247401248 6.41333333333333 3 556 . . 0
1 "AD Andorra" "2006M06" 0 2006 0 3456442102.6220045 .1247401247401248 6.41333333333333 3 557 . . 0
1 "AD Andorra" "2006M07" 0 2006 0 3456442102.6220045 .1247401247401248 6.41333333333333 3 558 . . 0
1 "AD Andorra" "2006M08" 0 2006 0 3456442102.6220045 .1247401247401248 6.41333333333333 3 559 . . 0
1 "AD Andorra" "2006M09" 0 2006 0 3456442102.6220045 .1247401247401248 6.41333333333333 3 560 . . 0
1 "AD Andorra" "2006M10" 0 2006 0 3456442102.6220045 .1247401247401248 6.41333333333333 3 561 . . 0
1 "AD Andorra" "2006M11" 0 2006 0 3456442102.6220045 .1247401247401248 6.41333333333333 3 562 . . 0
1 "AD Andorra" "2006M12" 0 2006 0 3456442102.6220045 .1247401247401248 6.41333333333333 3 563 . . 0
1 "AD Andorra" "2007M01" 0 2007 0 3952600602.24473 .11941996019334652 5.86166666666667 3 564 . . .14354602
1 "AD Andorra" "2007M02" 0 2007 0 3952600602.24473 .11941996019334652 5.86166666666667 3 565 . . 0
1 "AD Andorra" "2007M03" 0 2007 0 3952600602.24473 .11941996019334652 5.86166666666667 3 566 . . 0
1 "AD Andorra" "2007M04" 0 2007 0 3952600602.24473 .11941996019334652 5.86166666666667 3 567 . . 0
1 "AD Andorra" "2007M05" 0 2007 0 3952600602.24473 .11941996019334652 5.86166666666667 3 568 . . 0
1 "AD Andorra" "2007M06" 0 2007 0 3952600602.24473 .11941996019334652 5.86166666666667 3 569 . . 0
1 "AD Andorra" "2007M07" 0 2007 0 3952600602.24473 .11941996019334652 5.86166666666667 3 570 . . 0
1 "AD Andorra" "2007M08" 0 2007 0 3952600602.24473 .11941996019334652 5.86166666666667 3 571 . . 0
1 "AD Andorra" "2007M09" 0 2007 0 3952600602.24473 .11941996019334652 5.86166666666667 3 572 . . 0
1 "AD Andorra" "2007M10" 0 2007 0 3952600602.24473 .11941996019334652 5.86166666666667 3 573 . . 0
1 "AD Andorra" "2007M11" 0 2007 0 3952600602.24473 .11941996019334652 5.86166666666667 3 574 . . 0
1 "AD Andorra" "2007M12" 0 2007 0 3952600602.24473 .11941996019334652 5.86166666666667 3 575 . . 0
1 "AD Andorra" "2008M01" 0 2008 0 4085630584.444119 .12411347517730496 5.64 3 576 . . .033656318
1 "AD Andorra" "2008M02" 0 2008 0 4085630584.444119 .12411347517730496 5.64 3 577 . . 0
1 "AD Andorra" "2008M03" 0 2008 0 4085630584.444119 .12411347517730496 5.64 3 578 . . 0
1 "AD Andorra" "2008M04" 0 2008 0 4085630584.444119 .12411347517730496 5.64 3 579 . . 0
end
format %ty year
format %tm mdate
label values _merge _merge
label def _merge 3 "Matched (3)", modify
[/CODE]


I hope someone can help me. Thank you in advance

[Carlo Lazzaro]

Rolf:
provided that what you're after makes sense in macroeconomic terms, you may want to try:

. bysort CountryID year: g monthly_GDP=((GDP/_N)*_n)

In all likelihood, your -GDP- variables will need support from -recast-.

[William Lisowski]

Perhaps this example using your data will start you in a useful direction.

xtset CountryID mdate
generate double yGDP = GDP if year!=L.year
ipolate yGDP mdate, generate(mGDP) epolate
format %16.0f GDP yGDP mGDP
list, noobs separator(12) abbreviate(16)

. list, noobs separator(12) abbreviate(16)

  +-------------------------------------------------------------------+
  | CountryID   year     mdate          GDP         yGDP         mGDP |
  |-------------------------------------------------------------------|
  |         1   2000    2000m1   1429049198   1429049198   1429049198 |
  |         1   2000    2000m2   1429049198            .   1438872280 |
  |         1   2000    2000m3   1429049198            .   1448695361 |
  |         1   2000    2000m4   1429049198            .   1458518442 |
  |         1   2000    2000m5   1429049198            .   1468341524 |
  |         1   2000    2000m6   1429049198            .   1478164605 |
  |         1   2000    2000m7   1429049198            .   1487987686 |
  |         1   2000    2000m8   1429049198            .   1497810768 |
  |         1   2000    2000m9   1429049198            .   1507633849 |
  |         1   2000   2000m10   1429049198            .   1517456930 |
  |         1   2000   2000m11   1429049198            .   1527280012 |
  |         1   2000   2000m12   1429049198            .   1537103093 |
  |-------------------------------------------------------------------|
  |         1   2001    2001m1   1546926174   1546926174   1546926174 |
  |         1   2001    2001m2   1546926174            .   1564341496 |
  |         1   2001    2001m3   1546926174            .   1581756817 |
  |         1   2001    2001m4   1546926174            .   1599172139 |
  |         1   2001    2001m5   1546926174            .   1616587460 |
  |         1   2001    2001m6   1546926174            .   1634002782 |
  |         1   2001    2001m7   1546926174            .   1651418103 |
  |         1   2001    2001m8   1546926174            .   1668833425 |
  |         1   2001    2001m9   1546926174            .   1686248746 |
  |         1   2001   2001m10   1546926174            .   1703664068 |
  |         1   2001   2001m11   1546926174            .   1721079389 |
  |         1   2001   2001m12   1546926174            .   1738494711 |
  |-------------------------------------------------------------------|
  |         1   2002    2002m1   1755910032   1755910032   1755910032 |
  |         1   2002    2002m2   1755910032            .   1806394768 |
  |         1   2002    2002m3   1755910032            .   1856879504 |
  |         1   2002    2002m4   1755910032            .   1907364240 |
  |         1   2002    2002m5   1755910032            .   1957848975 |
  |         1   2002    2002m6   1755910032            .   2008333711 |
  |         1   2002    2002m7   1755910032            .   2058818447 |
  |         1   2002    2002m8   1755910032            .   2109303183 |
  |         1   2002    2002m9   1755910032            .   2159787919 |
  |         1   2002   2002m10   1755910032            .   2210272655 |
  |         1   2002   2002m11   1755910032            .   2260757391 |
  |         1   2002   2002m12   1755910032            .   2311242126 |
  |-------------------------------------------------------------------|
  |         1   2003    2003m1   2361726862   2361726862   2361726862 |
  |         1   2003    2003m2   2361726862            .   2406159772 |
  |         1   2003    2003m3   2361726862            .   2450592682 |
  |         1   2003    2003m4   2361726862            .   2495025591 |
  |         1   2003    2003m5   2361726862            .   2539458501 |
  |         1   2003    2003m6   2361726862            .   2583891411 |
  |         1   2003    2003m7   2361726862            .   2628324320 |
  |         1   2003    2003m8   2361726862            .   2672757230 |
  |         1   2003    2003m9   2361726862            .   2717190139 |
  |         1   2003   2003m10   2361726862            .   2761623049 |
  |         1   2003   2003m11   2361726862            .   2806055959 |
  |         1   2003   2003m12   2361726862            .   2850488868 |
  |-------------------------------------------------------------------|
  |         1   2004    2004m1   2894921778   2894921778   2894921778 |
  |         1   2004    2004m2   2894921778            .   2917003754 |
  |         1   2004    2004m3   2894921778            .   2939085729 |
  |         1   2004    2004m4   2894921778            .   2961167705 |
  |         1   2004    2004m5   2894921778            .   2983249680 |
  |         1   2004    2004m6   2894921778            .   3005331656 |
  |         1   2004    2004m7   2894921778            .   3027413631 |
  |         1   2004    2004m8   2894921778            .   3049495607 |
  |         1   2004    2004m9   2894921778            .   3071577582 |
  |         1   2004   2004m10   2894921778            .   3093659558 |
  |         1   2004   2004m11   2894921778            .   3115741533 |
  |         1   2004   2004m12   2894921778            .   3137823509 |
  |-------------------------------------------------------------------|
  |         1   2005    2005m1   3159905484   3159905484   3159905484 |
  |         1   2005    2005m2   3159905484            .   3184616869 |
  |         1   2005    2005m3   3159905484            .   3209328254 |
  |         1   2005    2005m4   3159905484            .   3234039639 |
  |         1   2005    2005m5   3159905484            .   3258751024 |
  |         1   2005    2005m6   3159905484            .   3283462409 |
  |         1   2005    2005m7   3159905484            .   3308173794 |
  |         1   2005    2005m8   3159905484            .   3332885178 |
  |         1   2005    2005m9   3159905484            .   3357596563 |
  |         1   2005   2005m10   3159905484            .   3382307948 |
  |         1   2005   2005m11   3159905484            .   3407019333 |
  |         1   2005   2005m12   3159905484            .   3431730718 |
  |-------------------------------------------------------------------|
  |         1   2006    2006m1   3456442103   3456442103   3456442103 |
  |         1   2006    2006m2   3456442103            .   3497788644 |
  |         1   2006    2006m3   3456442103            .   3539135186 |
  |         1   2006    2006m4   3456442103            .   3580481728 |
  |         1   2006    2006m5   3456442103            .   3621828269 |
  |         1   2006    2006m6   3456442103            .   3663174811 |
  |         1   2006    2006m7   3456442103            .   3704521352 |
  |         1   2006    2006m8   3456442103            .   3745867894 |
  |         1   2006    2006m9   3456442103            .   3787214436 |
  |         1   2006   2006m10   3456442103            .   3828560977 |
  |         1   2006   2006m11   3456442103            .   3869907519 |
  |         1   2006   2006m12   3456442103            .   3911254061 |
  |-------------------------------------------------------------------|
  |         1   2007    2007m1   3952600602   3952600602   3952600602 |
  |         1   2007    2007m2   3952600602            .   3963686434 |
  |         1   2007    2007m3   3952600602            .   3974772266 |
  |         1   2007    2007m4   3952600602            .   3985858098 |
  |         1   2007    2007m5   3952600602            .   3996943930 |
  |         1   2007    2007m6   3952600602            .   4008029761 |
  |         1   2007    2007m7   3952600602            .   4019115593 |
  |         1   2007    2007m8   3952600602            .   4030201425 |
  |         1   2007    2007m9   3952600602            .   4041287257 |
  |         1   2007   2007m10   3952600602            .   4052373089 |
  |         1   2007   2007m11   3952600602            .   4063458921 |
  |         1   2007   2007m12   3952600602            .   4074544753 |
  |-------------------------------------------------------------------|
  |         1   2008    2008m1   4085630584   4085630584   4085630584 |
  |         1   2008    2008m2   4085630584            .   4096716416 |
  |         1   2008    2008m3   4085630584            .   4107802248 |
  |         1   2008    2008m4   4085630584            .   4118888080 |
  +-------------------------------------------------------------------+

[Rolf Bosch]

I solved it, thanks a lot!