Announcement

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

  • Data from 12 rows to 1?

    Hi,

    My data looks like the copy I made hereafter. I copied 3 months for 1 company here. Currently, my I have 12 different variables on 12 separate rows instead of 1 row per month with all 12 variables. Could you please help me to get my data to1 row per month?

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input int CompanyID byte VariableID double d float(Date MV P OI CASH CASHG TA STD LTD CSO BVS BVSF)
    1  1  298.87 516 298.87    .     .      .      .      .     .      .      .     .     .
    1  2    2.87 516      . 2.87     .      .      .      .     .      .      .     .     .
    1  3 1224990 516      .    .     .      .      .      .     .      .      .     .     .
    1  4   35480 516      .    . 35480      .      .      .     .      .      .     .     .
    1  5  120876 516      .    .     . 120876      .      .     .      .      .     .     .
    1  6  173581 516      .    .     .      . 173581      .     .      .      .     .     .
    1  7  948036 516      .    .     .      .      . 948036     .      .      .     .     .
    1  8   11588 516      .    .     .      .      .      . 11588      .      .     .     .
    1  9  105405 516      .    .     .      .      .      .     . 105405      .     .     .
    1 10  103112 516      .    .     .      .      .      .     .      . 103112     .     .
    1 11   2.255 516      .    .     .      .      .      .     .      .      . 2.255     .
    1 12   2.255 516      .    .     .      .      .      .     .      .      .     . 2.255
    1  1   284.7 517  284.7    .     .      .      .      .     .      .      .     .     .
    1  2    2.74 517      . 2.74     .      .      .      .     .      .      .     .     .
    1  3 1224990 517      .    .     .      .      .      .     .      .      .     .     .
    1  4   35480 517      .    . 35480      .      .      .     .      .      .     .     .
    1  5  120876 517      .    .     . 120876      .      .     .      .      .     .     .
    1  6  173581 517      .    .     .      . 173581      .     .      .      .     .     .
    1  7  948036 517      .    .     .      .      . 948036     .      .      .     .     .
    1  8   11588 517      .    .     .      .      .      . 11588      .      .     .     .
    1  9  105405 517      .    .     .      .      .      .     . 105405      .     .     .
    1 10  103112 517      .    .     .      .      .      .     .      . 103112     .     .
    1 11   2.255 517      .    .     .      .      .      .     .      .      . 2.255     .
    1 12   2.255 517      .    .     .      .      .      .     .      .      .     . 2.255
    1  1   292.5 518  292.5    .     .      .      .      .     .      .      .     .     .
    1  2    2.81 518      . 2.81     .      .      .      .     .      .      .     .     .
    1  3 1224990 518      .    .     .      .      .      .     .      .      .     .     .
    1  4   35480 518      .    . 35480      .      .      .     .      .      .     .     .
    1  5  120876 518      .    .     . 120876      .      .     .      .      .     .     .
    1  6  173581 518      .    .     .      . 173581      .     .      .      .     .     .
    1  7  948036 518      .    .     .      .      . 948036     .      .      .     .     .
    1  8   11588 518      .    .     .      .      .      . 11588      .      .     .     .
    1  9  105405 518      .    .     .      .      .      .     . 105405      .     .     .
    1 10  103112 518      .    .     .      .      .      .     .      . 103112     .     .
    1 11   2.255 518      .    .     .      .      .      .     .      .      . 2.255     .
    1 12   2.255 518      .    .     .      .      .      .     .      .      .     . 2.255
    end
    format %tm Date

    Kind regards,

    Bob Rotman

  • #2
    Thank you for providing example data using dataex.

    Looking at your data, I think I see what it looked like originally. So here's some code that I think will show you a useful approach using reshape wide. I made up a name for the VariableID 3 variable, but if you really don't need it, you can add
    Code:
    drop if VariableID==3
    before the reshape wide and then remove the made-up variable name from the rename command.
    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input int CompanyID byte VariableID double d float Date
    1  1  298.87 516
    1  2    2.87 516
    1  3 1224990 516
    1  4   35480 516
    1  5  120876 516
    1  6  173581 516
    1  7  948036 516
    1  8   11588 516
    1  9  105405 516
    1 10  103112 516
    1 11   2.255 516
    1 12   2.255 516
    1  1   284.7 517
    1  2    2.74 517
    1  3 1224990 517
    1  4   35480 517
    1  5  120876 517
    1  6  173581 517
    1  7  948036 517
    1  8   11588 517
    1  9  105405 517
    1 10  103112 517
    1 11   2.255 517
    1 12   2.255 517
    1  1   292.5 518
    1  2    2.81 518
    1  3 1224990 518
    1  4   35480 518
    1  5  120876 518
    1  6  173581 518
    1  7  948036 518
    1  8   11588 518
    1  9  105405 518
    1 10  103112 518
    1 11   2.255 518
    1 12   2.255 518
    end
    format %tm Date
    reshape wide d, i(CompanyID Date) j(VariableID)
    rename (d1-d12) (MV P gnxl OI CASH CASHG TA STD LTD CSO BVS BVSF)
    list, clean noobs
    Code:
    . list, clean noobs
    
        Compan~D     Date       MV      P      gnxl      OI     CASH    CASHG       TA     STD      LTD      CSO     BVS    BVSF  
               1   2003m1   298.87   2.87   1224990   35480   120876   173581   948036   11588   105405   103112   2.255   2.255  
               1   2003m2    284.7   2.74   1224990   35480   120876   173581   948036   11588   105405   103112   2.255   2.255  
               1   2003m3    292.5   2.81   1224990   35480   120876   173581   948036   11588   105405   103112   2.255   2.255
    Last edited by William Lisowski; 21 Jul 2018, 07:37.

    Comment


    • #3
      I think William has successfully constructed a -dataex- that resembles the original source for the data and shown how to reshape it wide. But in case that source is no longer available, her's a simple way to get the desired result from the data as originally shown by Bob:

      Code:
      collapse (firstnm) MV-BVSF, by(CompanyID Date)
      By the way, Stata is not a spreadsheet. And if you think of Stata as if it were a spreadsheet and work with it accordingly, you will often be led astray. I'm even guessing that that's how Bob ended up with the mangled and more or less useless (in Stata) data set he has shown. In order to keep Stata and spreadsheets separate in your mind, it is best to use different terminology when discussing them. So, Stata does not have rows and columns; it has observations and variables.

      Comment

      Working...
      X