Announcement

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

  • reshape or what?

    Dear All, I have this dataset,
    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input str3 code str4 seriesname double(yr2015 yr2016 yr2017)
    "CAN" "inf"    1.12524136094279    1.4287595470108    1.5968841285297
    "JPN" "inf"    .789517890139427  -.116666666666671   .467211747038214
    "USA" "inf"    .118627135552435   1.26158320570537   2.13011000365963
    "CAN" "prvt"                  .                  .                  .
    "JPN" "prvt" 162.30694503499905 162.35440967317416 168.19138836880097
    "USA" "prvt"  188.2037313190976  192.1654998496473                  .
    end
    and wish to have the following result
    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input str3 code double(inf2015 inf2016 inf2017) double(prvt2015 prvt2016 prvt2017)
    "CAN"   1.12524136094279    1.4287595470108    1.5968841285297                   .                  .                  .
    "JPN"   .789517890139427  -.116666666666671   .467211747038214  162.30694503499905 162.35440967317416 168.19138836880097
    "USA"   .118627135552435   1.26158320570537   2.13011000365963   188.2037313190976  192.1654998496473                  .
    end
    Any suggestions are appreciated!
    Ho-Chuan (River) Huang
    Stata 17.0, MP(4)

  • #2
    Wouldn't it be just -reshape long- and then -reshape wide-?

    Comment


    • #3
      Code:
      . reshape wide yr2015 yr2016 yr2017, i(code) j(seriesname) string
      (note: j = inf prvt)
      
      Data                               long   ->   wide
      -----------------------------------------------------------------------------
      Number of obs.                        6   ->       3
      Number of variables                   5   ->       7
      j variable (2 values)        seriesname   ->   (dropped)
      xij variables:
                                       yr2015   ->   yr2015inf yr2015prvt
                                       yr2016   ->   yr2016inf yr2016prvt
                                       yr2017   ->   yr2017inf yr2017prvt
      -----------------------------------------------------------------------------
      
      .
      . list, abbr(20)
      
           +----------------------------------------------------------------------------------+
           | code   yr2015inf    yr2016inf   yr2017inf   yr2015prvt   yr2016prvt   yr2017prvt |
           |----------------------------------------------------------------------------------|
        1. |  CAN   1.1252414    1.4287595   1.5968841            .            .            . |
        2. |  JPN   .78951789   -.11666667   .46721175    162.30695    162.35441    168.19139 |
        3. |  USA   .11862714    1.2615832     2.13011    188.20373     192.1655            . |
           +----------------------------------------------------------------------------------+
      Or am I missing something?

      Comment


      • #4
        Dear Joseph, I think the first one is
        Code:
        reshape long yr, i(code seriesname)
        but I can't figure out the second step.
        Ho-Chuan (River) Huang
        Stata 17.0, MP(4)

        Comment


        • #5
          Andrea has it, if you don't mind the variable names. Otherwise, here:

          .ÿ
          .ÿversionÿ15.1

          .ÿ
          .ÿclearÿ*

          .ÿ
          .ÿquietlyÿinputÿstr3ÿcodeÿstr4ÿseriesnameÿdouble(yr2015ÿyr2016ÿyr2017)

          .ÿ
          .ÿquietlyÿreshapeÿlongÿyr,ÿi(codeÿseriesname)ÿj(year)

          .ÿ
          .ÿquietlyÿreshapeÿwideÿyr,ÿi(codeÿyear)ÿj(seriesname)ÿstring

          .ÿquietlyÿreshapeÿwideÿyr*,ÿi(code)ÿj(year)

          .ÿ
          .ÿrenameÿyr*ÿ*

          .ÿorderÿcodeÿi*ÿp*

          .ÿ
          .ÿlist,ÿnoobsÿabbreviate(20)

          ÿÿ+-------------------------------------------------------------------------------+
          ÿÿ|ÿcodeÿÿÿÿÿinf2015ÿÿÿÿÿÿinf2016ÿÿÿÿÿinf2017ÿÿÿÿprvt2015ÿÿÿÿprvt2016ÿÿÿÿprvt2017ÿ|
          ÿÿ|-------------------------------------------------------------------------------|
          ÿÿ|ÿÿCANÿÿÿ1.1252414ÿÿÿÿ1.4287595ÿÿÿ1.5968841ÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿÿÿÿÿÿ.ÿÿÿÿÿÿÿÿÿÿÿ.ÿ|
          ÿÿ|ÿÿJPNÿÿÿ.78951789ÿÿÿ-.11666667ÿÿÿ.46721175ÿÿÿ162.30695ÿÿÿ162.35441ÿÿÿ168.19139ÿ|
          ÿÿ|ÿÿUSAÿÿÿ.11862714ÿÿÿÿ1.2615832ÿÿÿÿÿ2.13011ÿÿÿ188.20373ÿÿÿÿ192.1655ÿÿÿÿÿÿÿÿÿÿÿ.ÿ|
          ÿÿ+-------------------------------------------------------------------------------+

          .ÿ
          .ÿexit

          endÿofÿdo-file


          .
          Last edited by Joseph Coveney; 17 Mar 2019, 05:43. Reason: And, if you knew -rename group- better than I do, you could go with Andrea's results with one more line of code, if you wanted to.

          Comment


          • #6
            Originally posted by Joseph Coveney View Post
            And, if you knew . . .
            Sorry: "know". Subjunctive mood was unintended and in error.

            Comment


            • #7
              All that said, and credit to Joseph Coveney and Andrea Discacciati for their helpful answers, a long layout (structure or format) is better for most Stata purposes!

              I didn't see a way to do that directly, but this works

              Code:
              reshape long yr , i(code seriesname) j(year) 
              reshape wide yr, i(code year) j(series) string
              rename yr* *
              Although the last reshape was a wide the result is long.

              Comment


              • #8
                If we take River's requested result as a given, including the layout and the variable names, then all that Andrea's single reshape needs is a rename command (see rule 19 of help rename group for details).
                Code:
                reshape wide yr2015 yr2016 yr2017, i(code) j(seriesname) string
                rename (yr#*) (*[2]#[1])
                dataex
                Code:
                * Example generated by -dataex-. To install: ssc install dataex
                clear
                input str3 code double(inf2015 inf2016 inf2017 prvt2015 prvt2016 prvt2017)
                "CAN" 1.12524136094279   1.4287595470108  1.5968841285297                  .                  .                  .
                "JPN" .789517890139427 -.116666666666671 .467211747038214 162.30694503499905 162.35440967317416 168.19138836880097
                "USA" .118627135552435  1.26158320570537 2.13011000365963  188.2037313190976  192.1654998496473                  .
                end

                Comment


                • #9
                  Dear @Andrea Discacciati, @Joseph Coveney, @Nick Cox, and @William Lisowski, Thank you all for the helpful suggestions.
                  Ho-Chuan (River) Huang
                  Stata 17.0, MP(4)

                  Comment


                  • #10
                    None taken, Joseph. Thanks again.
                    Ho-Chuan (River) Huang
                    Stata 17.0, MP(4)

                    Comment

                    Working...
                    X