Reshaping data with three dimensions

[Khati Zolfaghari]

Hello dear all,

I have panel data on the fertility rate. My data is on this shape:

iso    Area    Year    January    February    March    April    May    June    July    August September October    November    December
USA    1    2000    330108    317377    340553    317180    341207    341206    348975    360080    347609    343921    333811    336787
USA    1    2001    335198    303534    338684    323613    344017    331085    351047    361802    342564    344074    323746    326569
USA    1    2002    330674    303977    331505    324432    339007    327588    357669    359417    348814    345814    318573    334256
USA    1    2003    329803    307248    336920    330106    346754    337425    364226    360103    359644    354048    320094    343579
USA    1    2004    332907    315821    346348    333335    337651    344881    359426    355408    356033    348466    335704    346072
USA    1    2005    331478    309620    349321    332477    346276    350879    357053    369316    363369    344639    335667    348254
USA    1    2006    340297    319235    356786    329809    355437    358251    367934    387798    374711    367354    351832    356111
USA    1    2007    354943    326891    360828    338224    362319    358606    379616    390378    366904    369324    353660    354540
USA    1    2008    356457    338521    350630    346397    354886    348587    375384    373333    367965    357875    323788    353871
USA    1    2009    337980    316641    347803    337272    345257    346971    368450    359554    361922    347625    320195    340995
USA    1    2010    323249    301994    338613    325028    328273    334535    345199    349747    350745    336809    326220    338974
USA    1    2011    320477    297961    330151    313275    326647    337280    345560    359404    345548    328488    321479    327320
USA    1    2012    316315    304505    324322    306700    330146    327189    347542    361114    340113    345497    325146    324252
USA    1    2013    323691    291748    320529    311645    329520    319779    349158    353485    338008    340562    318482    335574
USA    1    2014    326366    298404    323572    318990    334613    325981    355524    354257    348424    343356    317998    340591
USA    1    2015    325955    298058    328923    320832    327917    330541    353415    351791    347516    339007    318820    335722
USA    1    2016    316707    306015    328526    313485    327631    332324    342728    357830    345552    330958    319389    324730
USA    1    2017    313786    289054    319625    300116    322456    323920    334924    352151    337175    329869    316511    315913
USA    1    2018    314808    284250    316044    298394    320622    314816    329009    344750    322772    326778    308703    310766
USA    1    2019    310872    279963    304237    298947    316386    304092    333646    341685    325781    325043    298086    308802
USA    1    2020    304722    282654    301625    290478    301481    302164    321637    319621    311705    305085    282597    289878
USA    1    2021    277000    266000    302000    293000    301000    313000    326000    330000    325000    315000    301000    311000
USA    1    2022    294000    275000    307000    288000    297000    304000                        
AUT    1    2000    6587    6384    6799    6327    6668    6419    6755    6685    6827    6433    6101    6283
AUT    1    2001    6611    5866    6376    6187    6409    6127    6625    6491    6513    6372    5801    6080
AUT    1    2002    6594    6110    6724    6313    6500    6373    6927    6573    6944    6671    6244    6426
AUT    1    2003    6570    6023    6485    6150    6426    6345    6924    6628    6661    6593    5928    6211
AUT    1    2004    6780    6149    6424    6340    6509    6550    6986    6928    6976    6521    6367    6438
AUT    1    2005    6482    5953    6554    6337    6640    6697    7065    6778    6847    6678    5973    6186
AUT    1    2006    6526    6079    6515    6126    6545    6413    6939    6941    6858    6705    6239    6028

but I want to reshape it in this way:

 
iso
Year
month
number of birth

USA
2000
January
330108

USA
2000
February
317377

USA
2000
March
340553

USA
2000
April
317180

USA
2000
May
341207

USA
2000
June
341206

USA
2000
July
348975

USA
2000
August
360080

USA
2000
September
347609

USA
2000
October
343921

USA
2000
November
333811

USA
2000
December
336787

But unfortunately, it is 3 dimensions, and I tried several ways to reshape it but I could not. I appreciate receiving any assistance you can provide.


Many thanks in advance for your valuable time.

best regards,

[Nick Cox]

An essentially identical problem is discussed in https://www.stata.com/support/faqs/d...-with-reshape/

* Example generated by -dataex-. For more info, type help dataex
clear
input str3 iso byte Area int Year long(January February March April May June July August September October November December)
"USA" 1 2000 330108 317377 340553 317180 341207 341206 348975 360080 347609 343921 333811 336787
"USA" 1 2001 335198 303534 338684 323613 344017 331085 351047 361802 342564 344074 323746 326569
"USA" 1 2002 330674 303977 331505 324432 339007 327588 357669 359417 348814 345814 318573 334256
"USA" 1 2003 329803 307248 336920 330106 346754 337425 364226 360103 359644 354048 320094 343579
"USA" 1 2004 332907 315821 346348 333335 337651 344881 359426 355408 356033 348466 335704 346072
"USA" 1 2005 331478 309620 349321 332477 346276 350879 357053 369316 363369 344639 335667 348254
"USA" 1 2006 340297 319235 356786 329809 355437 358251 367934 387798 374711 367354 351832 356111
"USA" 1 2007 354943 326891 360828 338224 362319 358606 379616 390378 366904 369324 353660 354540
"USA" 1 2008 356457 338521 350630 346397 354886 348587 375384 373333 367965 357875 323788 353871
"USA" 1 2009 337980 316641 347803 337272 345257 346971 368450 359554 361922 347625 320195 340995
"USA" 1 2010 323249 301994 338613 325028 328273 334535 345199 349747 350745 336809 326220 338974
"USA" 1 2011 320477 297961 330151 313275 326647 337280 345560 359404 345548 328488 321479 327320
"USA" 1 2012 316315 304505 324322 306700 330146 327189 347542 361114 340113 345497 325146 324252
"USA" 1 2013 323691 291748 320529 311645 329520 319779 349158 353485 338008 340562 318482 335574
"USA" 1 2014 326366 298404 323572 318990 334613 325981 355524 354257 348424 343356 317998 340591
"USA" 1 2015 325955 298058 328923 320832 327917 330541 353415 351791 347516 339007 318820 335722
"USA" 1 2016 316707 306015 328526 313485 327631 332324 342728 357830 345552 330958 319389 324730
"USA" 1 2017 313786 289054 319625 300116 322456 323920 334924 352151 337175 329869 316511 315913
"USA" 1 2018 314808 284250 316044 298394 320622 314816 329009 344750 322772 326778 308703 310766
"USA" 1 2019 310872 279963 304237 298947 316386 304092 333646 341685 325781 325043 298086 308802
"USA" 1 2020 304722 282654 301625 290478 301481 302164 321637 319621 311705 305085 282597 289878
"USA" 1 2021 277000 266000 302000 293000 301000 313000 326000 330000 325000 315000 301000 311000
"USA" 1 2022 294000 275000 307000 288000 297000 304000      .      .      .      .      .      .
"AUT" 1 2000   6587   6384   6799   6327   6668   6419   6755   6685   6827   6433   6101   6283
"AUT" 1 2001   6611   5866   6376   6187   6409   6127   6625   6491   6513   6372   5801   6080
"AUT" 1 2002   6594   6110   6724   6313   6500   6373   6927   6573   6944   6671   6244   6426
"AUT" 1 2003   6570   6023   6485   6150   6426   6345   6924   6628   6661   6593   5928   6211
"AUT" 1 2004   6780   6149   6424   6340   6509   6550   6986   6928   6976   6521   6367   6438
"AUT" 1 2005   6482   5953   6554   6337   6640   6697   7065   6778   6847   6678   5973   6186
"AUT" 1 2006   6526   6079   6515   6126   6545   6413   6939   6941   6858   6705   6239   6028
end

rename (Jan-Dec) (wanted#), addnumber 
reshape long wanted, i(iso Area Year) j(Month)
gen mdate = ym(Year, Month)
format mdate %tm 

list

[Khati Zolfaghari]

@Nick Cox many thanks for your reply. and also sharing the link. it works.


Best regards,