transposing rows to columns

[Bela Banga]

Hi,

My data contains multiple observations for the variable Dealsubtype, whereas I want to change the layout to be dealsubtype 1, dealsubtype 2, dealsubtype 3, etc..

Is there an easy way to transpose the data in variable Dealsubtype so that instead of different rows, the different subtypes are in the columns?

Thanks in advance!

* Example generated by -dataex-. To install: ssc install dataex
clear
input str5 A str65 Acquirorname str30 Dealsubtype
"1."  "PETSMART INC."                          "Exit"                 
""    ""                                       "Buy & Build"          
""    ""                                       "Leveraged buy out"    
"2."  "ADVENT INTERNATIONAL CORPORATION"       "Buy & Build"          
""    ""                                       ""                     
"3."  "BWAY CORPORATION"                       "Leveraged buy out"    
""    ""                                       "Secondary buy-out"    
""    ""                                       "Buy & Build"          
""    ""                                       "Exit"                 
"4."  "DTZ INVESTMENT HOLDINGS LP"             "Buy & Build"          
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
"5."  "CINVEN LTD"                             "Secondary buy-out"    
""    ""                                       "Exit"                 
""    ""                                       "Buy & Build"          
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
"6."  "GFKL FINANCIAL SERVICES AG"             "Exit"                 
""    ""                                       "Buy & Build"          
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
"7."  "BRAMMER FRANCE SAS"                     "Exit"                 
""    ""                                       "Buy & Build"          
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
"8."  "AVAST SOFTWARE BV"                      "Exit"                 
""    ""                                       "Public takeover"      
""    ""                                       "Patent sale"          
""    ""                                       "Buy & Build"          
""    ""                                       "Recommended bid"      
"9."  "VEDICI GROUPE SAS"                      "Exit"                 
""    ""                                       "Buy & Build"          
""    ""                                       ""                     
""    ""                                       ""                     
"10." "PANASONIC HEALTHCARE HOLDINGS CO., LTD" "Buy & Build"          
""    ""                                       ""                     
"11." "WAYPOINT GROUP HOLDINGS LTD"            "Buy & Build"          
""    ""                                       "Exit"                 
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
"12." "MAGYAR TELECOM BV"                      "Exit"                 
""    ""                                       "Buy & Build"          
""    ""                                       ""                     
"13." "IDCSALUD SL"                            "Exit"                 
""    ""                                       "Buy & Build"          
"14." "PUBLIC SECTOR PENSION INVESTMENT BOARD" "Patent sale"          
""    ""                                       "Buy & Build"          
""    ""                                       ""                     
""    ""                                       ""                     
"15." "REICHHOLD LLC 2"                        "Exit"                 
""    ""                                       "Buy & Build"          
""    ""                                       "Patent sale"          
"16." "ALLNEX BELGIUM SA/NV"                   "Recommended bid"      
""    ""                                       "Buy & Build"          
""    ""                                       "Patent sale"          
""    ""                                       "Public takeover"      
""    ""                                       "Scheme of arrangement"
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
"17." "MANAGEMENT"                             "Buy & Build"          
""    ""                                       "Exit"                 
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
"18." "ENCAPSYS LLC"                           "Buy & Build"          
""    ""                                       "Patent sale"          
""    ""                                       "Exit"                 
""    ""                                       "Secondary buy-out"    
"19." "BRIDGEPOINT CAPITAL LTD"                "Leveraged buy out"    
""    ""                                       "Buy & Build"          
""    ""                                       "Exit"                 
""    ""                                       "Secondary buy-out"    
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
"20." "KLOCKNER PENTAPLAST GMBH"               "Patent sale"          
""    ""                                       "Exit"                 
""    ""                                       "Buy & Build"          
""    ""                                       ""                     
""    ""                                       ""                     
end

[Carlo Lazzaro]

Bela:
welcome to this forum.
See -help reshape-.

[Nick Cox]

Spreadsheet speak translated: rows in Stata are observations; columns are variables.

Is this what you want? If the order of Dealsubtype is important, it's preserved. If you want new variables to be the first subtype, second subtype, you'll need something different.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str5 A str65 Acquirorname str30 Dealsubtype
"1."  "PETSMART INC."                          "Exit"                 
""    ""                                       "Buy & Build"          
""    ""                                       "Leveraged buy out"    
"2."  "ADVENT INTERNATIONAL CORPORATION"       "Buy & Build"          
""    ""                                       ""                     
"3."  "BWAY CORPORATION"                       "Leveraged buy out"    
""    ""                                       "Secondary buy-out"    
""    ""                                       "Buy & Build"          
""    ""                                       "Exit"                 
"4."  "DTZ INVESTMENT HOLDINGS LP"             "Buy & Build"          
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
"5."  "CINVEN LTD"                             "Secondary buy-out"    
""    ""                                       "Exit"                 
""    ""                                       "Buy & Build"          
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
"6."  "GFKL FINANCIAL SERVICES AG"             "Exit"                 
""    ""                                       "Buy & Build"          
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
"7."  "BRAMMER FRANCE SAS"                     "Exit"                 
""    ""                                       "Buy & Build"          
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
"8."  "AVAST SOFTWARE BV"                      "Exit"                 
""    ""                                       "Public takeover"      
""    ""                                       "Patent sale"          
""    ""                                       "Buy & Build"          
""    ""                                       "Recommended bid"      
"9."  "VEDICI GROUPE SAS"                      "Exit"                 
""    ""                                       "Buy & Build"          
""    ""                                       ""                     
""    ""                                       ""                     
"10." "PANASONIC HEALTHCARE HOLDINGS CO., LTD" "Buy & Build"          
""    ""                                       ""                     
"11." "WAYPOINT GROUP HOLDINGS LTD"            "Buy & Build"          
""    ""                                       "Exit"                 
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
"12." "MAGYAR TELECOM BV"                      "Exit"                 
""    ""                                       "Buy & Build"          
""    ""                                       ""                     
"13." "IDCSALUD SL"                            "Exit"                 
""    ""                                       "Buy & Build"          
"14." "PUBLIC SECTOR PENSION INVESTMENT BOARD" "Patent sale"          
""    ""                                       "Buy & Build"          
""    ""                                       ""                     
""    ""                                       ""                     
"15." "REICHHOLD LLC 2"                        "Exit"                 
""    ""                                       "Buy & Build"          
""    ""                                       "Patent sale"          
"16." "ALLNEX BELGIUM SA/NV"                   "Recommended bid"      
""    ""                                       "Buy & Build"          
""    ""                                       "Patent sale"          
""    ""                                       "Public takeover"      
""    ""                                       "Scheme of arrangement"
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
"17." "MANAGEMENT"                             "Buy & Build"          
""    ""                                       "Exit"                 
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
"18." "ENCAPSYS LLC"                           "Buy & Build"          
""    ""                                       "Patent sale"          
""    ""                                       "Exit"                 
""    ""                                       "Secondary buy-out"    
"19." "BRIDGEPOINT CAPITAL LTD"                "Leveraged buy out"    
""    ""                                       "Buy & Build"          
""    ""                                       "Exit"                 
""    ""                                       "Secondary buy-out"    
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
""    ""                                       ""                     
"20." "KLOCKNER PENTAPLAST GMBH"               "Patent sale"          
""    ""                                       "Exit"                 
""    ""                                       "Buy & Build"          
""    ""                                       ""                     
""    ""                                       ""                     
end

foreach v in A Acquirorname { 
    replace `v' = `v'[_n-1] if missing(`v') 
}

destring A, replace 
drop if missing(Dealsubtype) 

gen which = . 
replace which = cond(A != A[_n-1], 1, which[_n-1] + 1) 

encode Dealsubtype, gen(Deal) 
label list 
su Deal, meanonly 
local G = r(max) 
forval g = 1/`G' { 
    local label`g' : label (Deal) `g' 
} 

drop Dealsubtype 
reshape wide which , i(A Ac) j(Deal) 

forval g = 1/`G' { 
    label var which`g' "`label`g''" 
} 

sort A

list 


     +---------------------------------------------------------------------------------------------------------------------+
     |  A                             Acquirorname   which1   which2   which3   which4   which5   which6   which7   which8 |
     |---------------------------------------------------------------------------------------------------------------------|
  1. |  1                            PETSMART INC.        2        1        3        .        .        .        .        . |
  2. |  2         ADVENT INTERNATIONAL CORPORATION        1        .        .        .        .        .        .        . |
  3. |  3                         BWAY CORPORATION        3        4        1        .        .        .        .        2 |
  4. |  4               DTZ INVESTMENT HOLDINGS LP        1        .        .        .        .        .        .        . |
  5. |  5                               CINVEN LTD        3        2        .        .        .        .        .        1 |
     |---------------------------------------------------------------------------------------------------------------------|
  6. |  6               GFKL FINANCIAL SERVICES AG        2        1        .        .        .        .        .        . |
  7. |  7                       BRAMMER FRANCE SAS        2        1        .        .        .        .        .        . |
  8. |  8                        AVAST SOFTWARE BV        4        1        .        3        2        5        .        . |
  9. |  9                        VEDICI GROUPE SAS        2        1        .        .        .        .        .        . |
 10. | 10   PANASONIC HEALTHCARE HOLDINGS CO., LTD        1        .        .        .        .        .        .        . |
     |---------------------------------------------------------------------------------------------------------------------|
 11. | 11              WAYPOINT GROUP HOLDINGS LTD        1        2        .        .        .        .        .        . |
 12. | 12                        MAGYAR TELECOM BV        2        1        .        .        .        .        .        . |
 13. | 13                              IDCSALUD SL        2        1        .        .        .        .        .        . |
 14. | 14   PUBLIC SECTOR PENSION INVESTMENT BOARD        2        .        .        1        .        .        .        . |
 15. | 15                          REICHHOLD LLC 2        2        1        .        3        .        .        .        . |
     |---------------------------------------------------------------------------------------------------------------------|
 16. | 16                     ALLNEX BELGIUM SA/NV        2        .        .        3        4        1        5        . |
 17. | 17                               MANAGEMENT        1        2        .        .        .        .        .        . |
 18. | 18                             ENCAPSYS LLC        1        3        .        2        .        .        .        4 |
 19. | 19                  BRIDGEPOINT CAPITAL LTD        2        3        1        .        .        .        .        4 |
 20. | 20                 KLOCKNER PENTAPLAST GMBH        3        2        .        1        .        .        .        . |
     +---------------------------------------------------------------------------------------------------------------------+

describe

Contains data
  obs:            20                          
 vars:            10                          
 size:         1,960                          
--------------------------------------------------------------------------------------------------------------------------------------------------
              storage   display    value
variable name   type    format     label      variable label
--------------------------------------------------------------------------------------------------------------------------------------------------
A               byte    %10.0g                
Acquirorname    str65   %65s                  
which1          float   %9.0g                 Buy & Build
which2          float   %9.0g                 Exit
which3          float   %9.0g                 Leveraged buy out
which4          float   %9.0g                 Patent sale
which5          float   %9.0g                 Public takeover
which6          float   %9.0g                 Recommended bid
which7          float   %9.0g                 Scheme of arrangement
which8          float   %9.0g                 Secondary buy-out
--------------------------------------------------------------------------------------------------------------------------------------------------
Sorted by: A

[Bela Banga]

Amazing, thanks Nick!