Reshaping my OECD stata sets.

[Maximilian Kranz]

Dear stata community,

i am working on my bachelors thesis at the moment and i need to reshape my data sets before running my regression and im already having Problems .
I have Importer and Exporter of FDI in one column, but im trying to reshape it so they are separated and i have two variables one for inward and one for outward by country and year.

* Example generated by -dataex-. For more info, type help dataex
clear
input str30 measurementprinciple str3 cou int year
"Directional principle: Inward"  "AUS" 2013
"Directional principle: Inward"  "AUS" 2014
"Directional principle: Inward"  "AUS" 2015
"Directional principle: Inward"  "AUS" 2016
"Directional principle: Inward"  "AUS" 2017
"Directional principle: Inward"  "AUS" 2018
"Directional principle: Inward"  "AUS" 2019
"Directional principle: Inward"  "AUS" 2013
"Directional principle: Inward"  "AUS" 2014
"Directional principle: Inward"  "AUS" 2015
"Directional principle: Inward"  "AUS" 2016
"Directional principle: Inward"  "AUS" 2017
"Directional principle: Inward"  "AUS" 2018
"Directional principle: Inward"  "AUS" 2019
"Directional principle: Outward" "AUS" 2013
"Directional principle: Outward" "AUS" 2014
"Directional principle: Outward" "AUS" 2015
"Directional principle: Outward" "AUS" 2016
"Directional principle: Outward" "AUS" 2017
"Directional principle: Outward" "AUS" 2018
"Directional principle: Outward" "AUS" 2019
"Directional principle: Inward"  "AUS" 2013
"Directional principle: Inward"  "AUS" 2014
"Directional principle: Inward"  "AUS" 2015
"Directional principle: Inward"  "AUS" 2016
"Directional principle: Inward"  "AUS" 2017
"Directional principle: Inward"  "AUS" 2018
"Directional principle: Inward"  "AUS" 2019
"Directional principle: Outward" "AUS" 2013
"Directional principle: Outward" "AUS" 2014
"Directional principle: Outward" "AUS" 2015
"Directional principle: Outward" "AUS" 2016
"Directional principle: Outward" "AUS" 2017
"Directional principle: Outward" "AUS" 2018
"Directional principle: Outward" "AUS" 2019
"Directional principle: Inward"  "AUS" 2013
"Directional principle: Inward"  "AUS" 2014
"Directional principle: Inward"  "AUS" 2015
"Directional principle: Inward"  "AUS" 2016
"Directional principle: Inward"  "AUS" 2017
"Directional principle: Inward"  "AUS" 2018
"Directional principle: Inward"  "AUS" 2019
"Directional principle: Outward" "AUS" 2013
"Directional principle: Outward" "AUS" 2014
"Directional principle: Outward" "AUS" 2015
"Directional principle: Outward" "AUS" 2016
"Directional principle: Outward" "AUS" 2017
"Directional principle: Outward" "AUS" 2018
"Directional principle: Outward" "AUS" 2019
"Directional principle: Inward"  "AUS" 2013
"Directional principle: Inward"  "AUS" 2014
"Directional principle: Inward"  "AUS" 2015
"Directional principle: Inward"  "AUS" 2016
"Directional principle: Inward"  "AUS" 2017
"Directional principle: Inward"  "AUS" 2018
"Directional principle: Inward"  "AUS" 2019
"Directional principle: Outward" "AUS" 2013
"Directional principle: Outward" "AUS" 2014
"Directional principle: Outward" "AUS" 2015
"Directional principle: Outward" "AUS" 2016
"Directional principle: Outward" "AUS" 2017
"Directional principle: Outward" "AUS" 2018
"Directional principle: Outward" "AUS" 2019
"Directional principle: Inward"  "AUS" 2013
"Directional principle: Inward"  "AUS" 2014
"Directional principle: Inward"  "AUS" 2015
"Directional principle: Inward"  "AUS" 2016
"Directional principle: Inward"  "AUS" 2017
"Directional principle: Inward"  "AUS" 2018
"Directional principle: Inward"  "AUS" 2019
"Directional principle: Outward" "AUS" 2013
"Directional principle: Outward" "AUS" 2014
"Directional principle: Outward" "AUS" 2015
"Directional principle: Outward" "AUS" 2016
"Directional principle: Outward" "AUS" 2017
"Directional principle: Outward" "AUS" 2018
"Directional principle: Outward" "AUS" 2019
"Directional principle: Inward"  "AUS" 2013
"Directional principle: Inward"  "AUS" 2014
"Directional principle: Inward"  "AUS" 2015
"Directional principle: Inward"  "AUS" 2016
"Directional principle: Inward"  "AUS" 2017
"Directional principle: Inward"  "AUS" 2018
"Directional principle: Inward"  "AUS" 2019
"Directional principle: Outward" "AUS" 2013
"Directional principle: Outward" "AUS" 2014
"Directional principle: Outward" "AUS" 2015
"Directional principle: Outward" "AUS" 2016
"Directional principle: Outward" "AUS" 2017
"Directional principle: Outward" "AUS" 2018
"Directional principle: Outward" "AUS" 2019
"Directional principle: Outward" "AUS" 2013
"Directional principle: Outward" "AUS" 2014
"Directional principle: Outward" "AUS" 2015
"Directional principle: Outward" "AUS" 2016
"Directional principle: Outward" "AUS" 2017
"Directional principle: Outward" "AUS" 2018
"Directional principle: Outward" "AUS" 2019
"Directional principle: Inward"  "AUS" 2013
"Directional principle: Inward"  "AUS" 2014
end

------------------ copy up to and including the previous line ------------------

Listed 100 out of 741609 observations
Use the count() option to list more


if you wanna know how the data looks in the data editor you can see it in the attachements.




Thanks for your help stata masters kind regards

Maximilian Kranz

[Bruce Weaver]

It sounds like you want to use -reshape wide-. One step on the way to doing that, I think, is to generate a new dichotomous variable flagging the direction. See the first line in my code below, for example. But you have another problem in that the data you showed (thanks for doing that, by the way) has more than 2 records for each combination of country and year:

. generate byte inward = strpos(measurementprinciple,"Inward") > 0

. sort cou year inward  

. list if year < 2015, clean noobs

              measurementprinciple   cou   year   inward  
    Directional principle: Outward   AUS   2013        0  
    Directional principle: Outward   AUS   2013        0  
    Directional principle: Outward   AUS   2013        0  
    Directional principle: Outward   AUS   2013        0  
    Directional principle: Outward   AUS   2013        0  
    Directional principle: Outward   AUS   2013        0  
    Directional principle: Outward   AUS   2013        0  
     Directional principle: Inward   AUS   2013        1  
     Directional principle: Inward   AUS   2013        1  
     Directional principle: Inward   AUS   2013        1  
     Directional principle: Inward   AUS   2013        1  
     Directional principle: Inward   AUS   2013        1  
     Directional principle: Inward   AUS   2013        1  
     Directional principle: Inward   AUS   2013        1  
     Directional principle: Inward   AUS   2013        1  
    Directional principle: Outward   AUS   2014        0  
    Directional principle: Outward   AUS   2014        0  
    Directional principle: Outward   AUS   2014        0  
    Directional principle: Outward   AUS   2014        0  
    Directional principle: Outward   AUS   2014        0  
    Directional principle: Outward   AUS   2014        0  
    Directional principle: Outward   AUS   2014        0  
     Directional principle: Inward   AUS   2014        1  
     Directional principle: Inward   AUS   2014        1  
     Directional principle: Inward   AUS   2014        1  
     Directional principle: Inward   AUS   2014        1  
     Directional principle: Inward   AUS   2014        1  
     Directional principle: Inward   AUS   2014        1  
     Directional principle: Inward   AUS   2014        1  
     Directional principle: Inward   AUS   2014        1

I suspect there are other variables you have not told us about that determine which pairs of inward and outward values go together. HTH.

[Maximilian Kranz]

thank you for your Help!
Maybe this more extensive data is giving you a better idea of what im trying to do i guess I need the counterpart country that is the variable to know who is exporter and importer ?

* Example generated by -dataex-. For more info, type help dataex
clear
input str30 measurementprinciple str3 cou int year str62 partnercountry float value
"Directional principle: Outward" "AUS" 2013 "Myanmar"                                                         0
"Directional principle: Outward" "AUS" 2013 "ODA recipients-Asia"                                             .
"Directional principle: Outward" "AUS" 2013 "Jordan"                                                          .
"Directional principle: Outward" "AUS" 2013 "Benin"                                                           0
"Directional principle: Outward" "AUS" 2013 "Bangladesh"                                                      0
"Directional principle: Outward" "AUS" 2013 "Montenegro"                                                      0
"Directional principle: Outward" "AUS" 2013 "Botswana"                                                        .
"Directional principle: Outward" "AUS" 2013 "Albania"                                                         0
"Directional principle: Outward" "AUS" 2013 "ODA recipients-Oceania"                                          .
"Directional principle: Outward" "AUS" 2013 "Guinea"                                                          .
"Directional principle: Outward" "AUS" 2013 "Yemen"                                                           0
"Directional principle: Outward" "AUS" 2013 "Total Official Development Assistance (ODA) recipients"          .
"Directional principle: Outward" "AUS" 2013 "Egypt"                                                           .
"Directional principle: Outward" "AUS" 2013 "Kazakhstan"                                                      .
"Directional principle: Outward" "AUS" 2013 "Norfolk Island"                                                  0
"Directional principle: Outward" "AUS" 2013 "Kuwait"                                                          .
"Directional principle: Outward" "AUS" 2013 "MENA countries"                                                  .
"Directional principle: Outward" "AUS" 2013 "Norfolk Island"                                                  0
"Directional principle: Outward" "AUS" 2013 "Christmas islands"                                               0
"Directional principle: Outward" "AUS" 2013 "Guinea-Bissau"                                                   0
"Directional principle: Outward" "AUS" 2013 "Seychelles"                                                      0
"Directional principle: Outward" "AUS" 2013 "Lesotho"                                                         0
"Directional principle: Outward" "AUS" 2013 "Namibia"                                                         .
"Directional principle: Outward" "AUS" 2013 "Burundi"                                                         0
"Directional principle: Outward" "AUS" 2013 "Thailand"                                                        5
"Directional principle: Outward" "AUS" 2013 "Honduras"                                                        0
"Directional principle: Outward" "AUS" 2013 "Papua New Guinea"                                              692
"Directional principle: Outward" "AUS" 2013 "Jamaica"                                                         .
"Directional principle: Outward" "AUS" 2013 "Panama"                                                          .
"Directional principle: Outward" "AUS" 2013 "Christmas islands"                                               0
"Directional principle: Outward" "AUS" 2013 "Bulgaria"                                                        .
"Directional principle: Outward" "AUS" 2013 "Angola"                                                          0
"Directional principle: Outward" "AUS" 2013 "Cyprus"                                                          .
"Directional principle: Outward" "AUS" 2013 "Spain"                                                           .
"Directional principle: Outward" "AUS" 2013 "Cape Verde"                                                      0
"Directional principle: Outward" "AUS" 2013 "Equatorial Guinea"                                               0
"Directional principle: Outward" "AUS" 2013 "Swaziland"                                                       .
"Directional principle: Outward" "AUS" 2013 "India"                                                    31.84099
"Directional principle: Outward" "AUS" 2013 "Wallis and Futuna"                                               0
"Directional principle: Outward" "AUS" 2013 "Oman"                                                            .
"Directional principle: Outward" "AUS" 2013 "Finland"                                                         .
"Directional principle: Outward" "AUS" 2013 "Argentina"                                                       .
"Directional principle: Outward" "AUS" 2013 "Bermuda"                                                       940
"Directional principle: Outward" "AUS" 2013 "Colombia"                                                        .
"Directional principle: Outward" "AUS" 2013 "Hong Kong, China"                                            -1876
"Directional principle: Outward" "AUS" 2013 "Chile"                                                   -2.894635
"Directional principle: Outward" "AUS" 2013 "El Salvador"                                                     .
"Directional principle: Outward" "AUS" 2013 "Nauru"                                                           0
"Directional principle: Outward" "AUS" 2013 "Israel"                                                          0
"Directional principle: Outward" "AUS" 2013 "G20 Non-OECD countries"                                          .
"Directional principle: Outward" "AUS" 2013 "Ecuador"                                                         0
"Directional principle: Outward" "AUS" 2013 "French Southern Territories"                                     0
"Directional principle: Outward" "AUS" 2013 "Kiribati"                                                        .
"Directional principle: Outward" "AUS" 2013 "Cyprus"                                                          .
"Directional principle: Outward" "AUS" 2013 "Northern Mariana Islands"                                        0
"Directional principle: Outward" "AUS" 2013 "Haiti"                                                           0
"Directional principle: Outward" "AUS" 2013 "Lebanon"                                                         .
"Directional principle: Outward" "AUS" 2013 "Liechtenstein"                                                   0
"Directional principle: Outward" "AUS" 2013 "Curaçao"                                                        0
"Directional principle: Outward" "AUS" 2013 "Central African Republic"                                        0
"Directional principle: Outward" "AUS" 2013 "Philippines"                                                     .
"Directional principle: Outward" "AUS" 2013 "Afghanistan"                                                     0
"Directional principle: Outward" "AUS" 2013 "Egypt"                                                           .
"Directional principle: Outward" "AUS" 2013 "Belgium"                                                         1
"Directional principle: Outward" "AUS" 2013 "Barbados"                                                        0
"Directional principle: Outward" "AUS" 2013 "Malawi"                                                          .
"Directional principle: Outward" "AUS" 2013 "American Samoa"                                                  3
"Directional principle: Outward" "AUS" 2013 "Montenegro"                                                      0
"Directional principle: Outward" "AUS" 2013 "United Arab Emirates"                                     178.5025
"Directional principle: Outward" "AUS" 2013 "Norway"                                                          .
"Directional principle: Outward" "AUS" 2013 "Bonaire, Saint Eustatius and Saba"                               0
"Directional principle: Outward" "AUS" 2013 "Burkina Faso"                                                    0
"Directional principle: Outward" "AUS" 2013 "Ireland"                                                -109.03127
"Directional principle: Outward" "AUS" 2013 "Japan"                                                         161
"Directional principle: Outward" "AUS" 2013 "G20 countries excl. European Union"                              .
"Directional principle: Outward" "AUS" 2013 "Bhutan"                                                          0
"Directional principle: Outward" "AUS" 2013 "ASEAN countries"                                              2593
"Directional principle: Outward" "AUS" 2013 "Heard islands and Mc Donald Islands"                             0
"Directional principle: Outward" "AUS" 2013 "Spain"                                                           .
"Directional principle: Outward" "AUS" 2013 "Gabon"                                                           .
"Directional principle: Outward" "AUS" 2013 "Andorra"                                                         0
"Directional principle: Outward" "AUS" 2013 "Nigeria"                                                         0
"Directional principle: Outward" "AUS" 2013 "Benin"                                                           0
"Directional principle: Outward" "AUS" 2013 "France"                                                  -92.62833
"Directional principle: Outward" "AUS" 2013 "Slovenia"                                                        .
"Directional principle: Outward" "AUS" 2013 "G20 countries excl. European Union"                              .
"Directional principle: Outward" "AUS" 2013 "Italy"                                                           5
"Directional principle: Outward" "AUS" 2013 "Bhutan"                                                          0
"Directional principle: Outward" "AUS" 2013 "Bouvet Island"                                                   0
"Directional principle: Outward" "AUS" 2013 "Guam"                                                            0
"Directional principle: Outward" "AUS" 2013 "ODA recipients-Asia"                                             .
"Directional principle: Outward" "AUS" 2013 "Tunisia"                                                         0
"Directional principle: Outward" "AUS" 2013 "Saint Vincent and the Grenadines"                                0
"Directional principle: Outward" "AUS" 2013 "Tajikistan"                                                      0
"Directional principle: Outward" "AUS" 2013 "Chad"                                                            0
"Directional principle: Outward" "AUS" 2013 "Sierra Leone"                                                    0
"Directional principle: Outward" "AUS" 2013 "Russian Federation"                                              .
"Directional principle: Outward" "AUS" 2013 "Cocos islands"                                                   0
"Directional principle: Outward" "AUS" 2013 "Viet Nam"                                                        .
"Directional principle: Outward" "AUS" 2013 "Vanuatu"                                                         .
end

Kind regards

Maximilian Kranz