Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • reshape?

    Dear all, My data is
    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input int Code double(ITLMV ITLWC05476) long PCLMV double(PCLWC05476 ABCMV ABCWC05476)
    17145 971223.8  1.37       . 3.277 .     .
    17176  1427975 1.581       . 4.007 . -2.24
    17207  1202109 1.581       . 4.007 . -2.24
    17235  1267359 1.581       . 4.007 . -2.24
    17266  1400369 1.581       . 4.007 . -2.24
    17296  1448051 1.581       . 4.007 . -2.24
    17327  1259830 1.581       . 4.007 . -2.24
    17357  1345157 1.581       . 4.007 . -2.24
    17388  1678937 1.581       . 4.007 . -2.24
    17419  1706543 1.581       . 4.007 . -2.24
    17449  1904803 1.581       . 4.007 . -2.24
    17480  2095535 1.581 6182182 4.007 . -2.24
    17510  2088006 1.581 5087589 4.007 . -2.24
    17541  2040323  1.77 4948336 4.321 .  .894
    17572  1779322  1.77 4001092 4.321 .  .894
    17601  1548436  1.77 3562284 4.321 .  .894
    17632  1550946  1.77 2809345 4.321 .  .894
    17662  1540907  1.77 2900021 4.321 .  .894
    17693  1350176  1.77 2611800 4.321 .  .894
    17723  1277397  1.77 2483883 4.321 .  .894
    17754  1254811  1.77 2365680 4.321 .  .894
    17785  1131839  1.77 1794095 4.321 .  .894
    17815  1003849  1.77 1968971 4.321 .  .894
    17846  1001340  1.77 1816764 4.321 .  .894
    17876  1023926  1.77 1876675 4.321 .  .894
    17907 898444.8 1.979 1653223 4.629 . 1.055
    17938 988791.4 1.979 1922014 4.629 . 1.055
    17966 933579.3 1.979 1747138 4.629 . 1.055
    end
    format %tdnn/dd/CCYY Code
    where `Code' denotes times, and there are three firms (ITL, PCL, and ABC; in fact, there are hundreds of firms) and two variables (MV and WC05476; in fact, there are 9 variables) so that there are 6 (2x3) variables. How can I reshape the data into a long form.
    Ho-Chuan (River) Huang
    Stata 15.1, MP(4)

  • #2
    How do you want the new long data structure to look like? These two lines of code will reshape the data to a long form:
    Code:
    rename ( ITLMV ITLWC05476 PCLMV PCLWC05476 ABCMV ABCWC05476 ) (stub1 stub2 stub3 stub4 stub5 stub6)
    reshape long stub, i(Code) j(j)

    Comment


    • #3
      I guess this should be rewritten, but I imagine the result to be what you wish.

      Code:
      clear
      input int Code double(ITLMV ITLWC05476) long PCLMV double(PCLWC05476 ABCMV ABCWC05476)
      17145 971223.8  1.37       . 3.277 .     .
      17176  1427975 1.581       . 4.007 . -2.24
      17207  1202109 1.581       . 4.007 . -2.24
      17235  1267359 1.581       . 4.007 . -2.24
      17266  1400369 1.581       . 4.007 . -2.24
      17296  1448051 1.581       . 4.007 . -2.24
      17327  1259830 1.581       . 4.007 . -2.24
      17357  1345157 1.581       . 4.007 . -2.24
      17388  1678937 1.581       . 4.007 . -2.24
      17419  1706543 1.581       . 4.007 . -2.24
      17449  1904803 1.581       . 4.007 . -2.24
      17480  2095535 1.581 6182182 4.007 . -2.24
      17510  2088006 1.581 5087589 4.007 . -2.24
      17541  2040323  1.77 4948336 4.321 .  .894
      17572  1779322  1.77 4001092 4.321 .  .894
      17601  1548436  1.77 3562284 4.321 .  .894
      17632  1550946  1.77 2809345 4.321 .  .894
      17662  1540907  1.77 2900021 4.321 .  .894
      17693  1350176  1.77 2611800 4.321 .  .894
      17723  1277397  1.77 2483883 4.321 .  .894
      17754  1254811  1.77 2365680 4.321 .  .894
      17785  1131839  1.77 1794095 4.321 .  .894
      17815  1003849  1.77 1968971 4.321 .  .894
      17846  1001340  1.77 1816764 4.321 .  .894
      17876  1023926  1.77 1876675 4.321 .  .894
      17907 898444.8 1.979 1653223 4.629 . 1.055
      17938 988791.4 1.979 1922014 4.629 . 1.055
      17966 933579.3 1.979 1747138 4.629 . 1.055
      end
      format %tdnn/dd/CCYY Code
      
      unab stubs : *WC05476
      local stubs : subinstr local stubs "WC05476" "", all
      
      reshape long `stubs', i(Code) string 
      rename (???) (firm=) 
      rename _j which 
      reshape long firm, i(Code which) string 
      reshape wide firm, i(Code _j) j(which) string 
      rename (firm*) (*) 
      rename _j firm 
      
      list, sepby(Code) 
      
      
           +----------------------------------------+
           |       Code   firm         MV   WC05476 |
           |----------------------------------------|
        1. | 12/10/2006    ABC          .         . |
        2. | 12/10/2006    ITL   971223.8      1.37 |
        3. | 12/10/2006    PCL          .     3.277 |
           |----------------------------------------|
        4. |  1/10/2007    ABC          .     -2.24 |
        5. |  1/10/2007    ITL    1427975     1.581 |
        6. |  1/10/2007    PCL          .     4.007 |
           |----------------------------------------|
        7. |  2/10/2007    ABC          .     -2.24 |
        8. |  2/10/2007    ITL    1202109     1.581 |
        9. |  2/10/2007    PCL          .     4.007 |
           |----------------------------------------|
       10. |  3/10/2007    ABC          .     -2.24 |
       11. |  3/10/2007    ITL    1267359     1.581 |
       12. |  3/10/2007    PCL          .     4.007 |
           |----------------------------------------|
       13. |  4/10/2007    ABC          .     -2.24 |
       14. |  4/10/2007    ITL    1400369     1.581 |
       15. |  4/10/2007    PCL          .     4.007 |
           |----------------------------------------|
       16. |  5/10/2007    ABC          .     -2.24 |
       17. |  5/10/2007    ITL    1448051     1.581 |
       18. |  5/10/2007    PCL          .     4.007 |
           |----------------------------------------|
       19. |  6/10/2007    ABC          .     -2.24 |
       20. |  6/10/2007    ITL    1259830     1.581 |
       21. |  6/10/2007    PCL          .     4.007 |
           |----------------------------------------|
       22. |  7/10/2007    ABC          .     -2.24 |
       23. |  7/10/2007    ITL    1345157     1.581 |
       24. |  7/10/2007    PCL          .     4.007 |
           |----------------------------------------|
       25. |  8/10/2007    ABC          .     -2.24 |
       26. |  8/10/2007    ITL    1678937     1.581 |
       27. |  8/10/2007    PCL          .     4.007 |
           |----------------------------------------|
       28. |  9/10/2007    ABC          .     -2.24 |
       29. |  9/10/2007    ITL    1706543     1.581 |
       30. |  9/10/2007    PCL          .     4.007 |
           |----------------------------------------|
       31. | 10/10/2007    ABC          .     -2.24 |
       32. | 10/10/2007    ITL    1904803     1.581 |
       33. | 10/10/2007    PCL          .     4.007 |
           |----------------------------------------|
       34. | 11/10/2007    ABC          .     -2.24 |
       35. | 11/10/2007    ITL    2095535     1.581 |
       36. | 11/10/2007    PCL    6182182     4.007 |
           |----------------------------------------|
       37. | 12/10/2007    ABC          .     -2.24 |
       38. | 12/10/2007    ITL    2088006     1.581 |
       39. | 12/10/2007    PCL    5087589     4.007 |
           |----------------------------------------|
       40. |  1/10/2008    ABC          .      .894 |
       41. |  1/10/2008    ITL    2040323      1.77 |
       42. |  1/10/2008    PCL    4948336     4.321 |
           |----------------------------------------|
       43. |  2/10/2008    ABC          .      .894 |
       44. |  2/10/2008    ITL    1779322      1.77 |
       45. |  2/10/2008    PCL    4001092     4.321 |
           |----------------------------------------|
       46. |  3/10/2008    ABC          .      .894 |
       47. |  3/10/2008    ITL    1548436      1.77 |
       48. |  3/10/2008    PCL    3562284     4.321 |
           |----------------------------------------|
       49. |  4/10/2008    ABC          .      .894 |
       50. |  4/10/2008    ITL    1550946      1.77 |
       51. |  4/10/2008    PCL    2809345     4.321 |
           |----------------------------------------|
       52. |  5/10/2008    ABC          .      .894 |
       53. |  5/10/2008    ITL    1540907      1.77 |
       54. |  5/10/2008    PCL    2900021     4.321 |
           |----------------------------------------|
       55. |  6/10/2008    ABC          .      .894 |
       56. |  6/10/2008    ITL    1350176      1.77 |
       57. |  6/10/2008    PCL    2611800     4.321 |
           |----------------------------------------|
       58. |  7/10/2008    ABC          .      .894 |
       59. |  7/10/2008    ITL    1277397      1.77 |
       60. |  7/10/2008    PCL    2483883     4.321 |
           |----------------------------------------|
       61. |  8/10/2008    ABC          .      .894 |
       62. |  8/10/2008    ITL    1254811      1.77 |
       63. |  8/10/2008    PCL    2365680     4.321 |
           |----------------------------------------|
       64. |  9/10/2008    ABC          .      .894 |
       65. |  9/10/2008    ITL    1131839      1.77 |
       66. |  9/10/2008    PCL    1794095     4.321 |
           |----------------------------------------|
       67. | 10/10/2008    ABC          .      .894 |
       68. | 10/10/2008    ITL    1003849      1.77 |
       69. | 10/10/2008    PCL    1968971     4.321 |
           |----------------------------------------|
       70. | 11/10/2008    ABC          .      .894 |
       71. | 11/10/2008    ITL    1001340      1.77 |
       72. | 11/10/2008    PCL    1816764     4.321 |
           |----------------------------------------|
       73. | 12/10/2008    ABC          .      .894 |
       74. | 12/10/2008    ITL    1023926      1.77 |
       75. | 12/10/2008    PCL    1876675     4.321 |
           |----------------------------------------|
       76. |  1/10/2009    ABC          .     1.055 |
       77. |  1/10/2009    ITL   898444.8     1.979 |
       78. |  1/10/2009    PCL    1653223     4.629 |
           |----------------------------------------|
       79. |  2/10/2009    ABC          .     1.055 |
       80. |  2/10/2009    ITL   988791.4     1.979 |
       81. |  2/10/2009    PCL    1922014     4.629 |
           |----------------------------------------|
       82. |  3/10/2009    ABC          .     1.055 |
       83. |  3/10/2009    ITL   933579.3     1.979 |
       84. |  3/10/2009    PCL    1747138     4.629 |
           +----------------------------------------+

      Comment


      • #4
        Originally posted by Anders Alexandersson View Post
        How do you want the new long data structure to look like? These two lines of code will reshape the data to a long form:
        Code:
        rename ( ITLMV ITLWC05476 PCLMV PCLWC05476 ABCMV ABCWC05476 ) (stub1 stub2 stub3 stub4 stub5 stub6)
        reshape long stub, i(Code) j(j)
        Thanks, Anders. I know the key is to rename all the variables (hundreds of them) in a tractable form. As such, your suggestion is not that feasible.
        Ho-Chuan (River) Huang
        Stata 15.1, MP(4)

        Comment


        • #5
          Nick, many thanks. I have some difficulties in understanding
          Code:
          unab stubs : *WC05476
          local stubs : subinstr local stubs "WC05476" "", all
          but I later came out the following approach
          Code:
          foreach v in MV WC05476 {
            ren ???`v' `v'???
          } 
          reshape long MV WC05476, i(Code) j(varname) string
          In fact, for my 9 (rather than 2 here) variables, it is
          Code:
          foreach v in MV EPS UP WC05476 WC05301 WC01001 WC05508 WC02999 NOSH {
            ren ???`v' `v'???
          } 
          reshape long MV EPS UP WC05476 WC05301 WC01001 WC05508 WC02999 NOSH, i(Code) j(varname) string
          Ho-Chuan (River) Huang
          Stata 15.1, MP(4)

          Comment


          • #6
            River: The first point is covered by FAQ http://www.stata.com/support/faqs/da...-with-reshape/

            Comment


            • #7
              Hi, Nick, Thanks. I had a look, but will it be applied to more than say, three years (as the example using two year 97, 98 data)?

              Ho-Chuan (River) Huang
              Stata 15.1, MP(4)

              Comment


              • #8
                Naturally. The code ignores suffixes and selects stubs. That's the point.

                Comment


                • #9
                  Thanks again.

                  Ho-Chuan (River) Huang
                  Stata 15.1, MP(4)

                  Comment

                  Working...
                  X