Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Question on reshaping data

    Hello,

    I've been playing around for quite a while now and I can't seem to reshape my dataset in order for it to be compatible with a software called GeoDa. So, here's my question:

    I have the following data (see screenshot). I need to reshape it in a way that I don't loose any data while having the nuts2016 code be a unique identifier in each row, i.e. BE10 shuld appear only once in the table.

    Any thoughts?

    Thanks,
    Anton.

    Click image for larger version Name: Screenshot 2022-08-14 at 19.15.54.png Views: 1 Size: 165.7 KB ID: 1677722
    Tags: None
  • #2
    All Forum members are requested to read the Forum FAQ before posting. Please do so, with particular attention to sections 9 through 12. In particular, take note that you are specifically asked not to use screenshots to show example data because they are not useful to those who want to help you. The helpful way to show example data is with the -dataex- command. If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

    The key difficulties you face here are:
    1. Which of these variables are constant within values of nuts2016. It looks like everything between countrycode and year is that way. Is that correct in the full data ? It needs to be known in order to properly formulate the varlist and -i()- options for -reshape-.
    2. You will need something like the variable party_abbrev as the -j()- option in your -reshape wide- command. But party_abbrev cannot be used as is because it contains characters (&) that are not legal in Stata variable names. Probably the simplest solution is to use the -strtovar()- function to change its values.
    If you need more concrete guidance to resolve your problem, please post back, showing example data with the -dataex- command and responding to the question posed above.
    • 1 like

    Comment

    • #3
      Hello, Mr. Schechter.

      Thanks for your response. I'm attaching a dataex output in this reply.

      Regarding your first point, you're right except for year, which varies.

      Regarding your second point, I also concluded that j has to be the party_abbreviation which gives me a syntax error. I can't seem to make the -strtovar()- command to work on this one. Any more help will be appreciated!

      Code:
      * Example generated by -dataex-. To install: ssc install dataex
clear
input str2 country_code str4 nuts2 str92 regionname int year str169 party_abbreviation float partyvote str8 electorate str7 totalvote float validvote
"AT" "AT11" "Burgenland"          1990 "FPO"     19508 "205541"  "181855" 176831
"AT" "AT11" "Burgenland"          1990 "GRUNE"    4122 "205541"  "181855" 176831
"AT" "AT11" "Burgenland"          1990 "OTHER"     465 "205541"  "181855" 176831
"AT" "AT11" "Burgenland"          1990 "OVP"     62675 "205541"  "181855" 176831
"AT" "AT11" "Burgenland"          1990 "SPO"     88547 "205541"  "181855" 176831
"AT" "AT11" "Burgenland"          1990 "VGO"      1514 "205541"  "181855" 176831
"AT" "AT11" "Burgenland"          1994 "FPO"     29935 "212341"  "182883" 178953
"AT" "AT11" "Burgenland"          1994 "GRUNE"    6413 "212341"  "182883" 178953
"AT" "AT11" "Burgenland"          1994 "LIF"      5164 "212341"  "182883" 178953
"AT" "AT11" "Burgenland"          1994 "OTHER"    1323 "212341"  "182883" 178953
"AT" "AT11" "Burgenland"          1994 "OVP"     56520 "212341"  "182883" 178953
"AT" "AT11" "Burgenland"          1994 "SPO"     79598 "212341"  "182883" 178953
"AT" "AT11" "Burgenland"          1995 "FPO"     30650 "212319"  "185081" 180705
"AT" "AT11" "Burgenland"          1995 "GRUNE"    4210 "212319"  "185081" 180705
"AT" "AT11" "Burgenland"          1995 "LIF"      5597 "212319"  "185081" 180705
"AT" "AT11" "Burgenland"          1995 "NEIN"     1150 "212319"  "185081" 180705
"AT" "AT11" "Burgenland"          1995 "OTHER"     235 "212319"  "185081" 180705
"AT" "AT11" "Burgenland"          1995 "OVP"     57858 "212319"  "185081" 180705
"AT" "AT11" "Burgenland"          1995 "SPO"     81005 "212319"  "185081" 180705
"AT" "AT11" "Burgenland"          2002 "FPO"     11839 "215784"  "188948" 185994
"AT" "AT11" "Burgenland"          2002 "GRUNE"    8233 "215784"  "188948" 185994
"AT" "AT11" "Burgenland"          2002 "OTHER"    1278 "215784"  "188948" 185994
"AT" "AT11" "Burgenland"          2002 "OVP"     78845 "215784"  "188948" 185994
"AT" "AT11" "Burgenland"          2002 "SPO"     85799 "215784"  "188948" 185994
"AT" "AT11" "Burgenland"          2006 "BZO"      3170 "222281"  "186992" 182884
"AT" "AT11" "Burgenland"          2006 "FPO"     15868 "222281"  "186992" 182884
"AT" "AT11" "Burgenland"          2006 "GRUNE"   10037 "222281"  "186992" 182884
"AT" "AT11" "Burgenland"          2006 "IVE"       326 "222281"  "186992" 182884
"AT" "AT11" "Burgenland"          2006 "KPO"       921 "222281"  "186992" 182884
"AT" "AT11" "Burgenland"          2006 "MARTIN"   3573 "222281"  "186992" 182884
"AT" "AT11" "Burgenland"          2006 "OTHER"     246 "222281"  "186992" 182884
"AT" "AT11" "Burgenland"          2006 "OVP"     65970 "222281"  "186992" 182884
"AT" "AT11" "Burgenland"          2006 "SPO"     82773 "222281"  "186992" 182884
"AT" "AT11" "Burgenland"          2008 "BZO"      9729 "230138"  "186877" 182030
"AT" "AT11" "Burgenland"          2008 "FPO"     29812 "230138"  "186877" 182030
"AT" "AT11" "Burgenland"          2008 "FRITZ"    2249 "230138"  "186877" 182030
"AT" "AT11" "Burgenland"          2008 "GRUNE"    9833 "230138"  "186877" 182030
"AT" "AT11" "Burgenland"          2008 "LIF"      1796 "230138"  "186877" 182030
"AT" "AT11" "Burgenland"          2008 "OTHER"    2515 "230138"  "186877" 182030
"AT" "AT11" "Burgenland"          2008 "OVP"     52531 "230138"  "186877" 182030
"AT" "AT11" "Burgenland"          2008 "SPO"     73565 "230138"  "186877" 182030
"AT" "AT11" "Burgenland"          2013 "BZO"      3689 "232505"  "192486" 188384
"AT" "AT11" "Burgenland"          2013 "FPO"     32705 "232505"  "192486" 188384
"AT" "AT11" "Burgenland"          2013 "FRANK"   11050 "232505"  "192486" 188384
"AT" "AT11" "Burgenland"          2013 "GRUNE"   12718 "232505"  "192486" 188384
"AT" "AT11" "Burgenland"          2013 "KPO"       930 "232505"  "192486" 188384
"AT" "AT11" "Burgenland"          2013 "NEOS"     5327 "232505"  "192486" 188384
"AT" "AT11" "Burgenland"          2013 "OTHER"    1317 "232505"  "192486" 188384
"AT" "AT11" "Burgenland"          2013 "OVP"     50426 "232505"  "192486" 188384
"AT" "AT11" "Burgenland"          2013 "SPO"     70222 "232505"  "192486" 188384
"AT" "AT11" "Burgenland"          2017 "FPO"     49127 "232740"  "196577" 194630
"AT" "AT11" "Burgenland"          2017 "GRUNE"    3932 "232740"  "196577" 194630
"AT" "AT11" "Burgenland"          2017 "NEOS"     5603 "232740"  "196577" 194630
"AT" "AT11" "Burgenland"          2017 "OTHER"    2511 "232740"  "196577" 194630
"AT" "AT11" "Burgenland"          2017 "OVP"     63858 "232740"  "196577" 194630
"AT" "AT11" "Burgenland"          2017 "PILZ"     5529 "232740"  "196577" 194630
"AT" "AT11" "Burgenland"          2017 "SPO"     64070 "232740"  "196577" 194630
"AT" "AT11" "Burgenland"          2019 "FPO"     32448 "233182"  "189911" 187061
"AT" "AT11" "Burgenland"          2019 "GRUNE"   15113 "233182"  "189911" 187061
"AT" "AT11" "Burgenland"          2019 "JETZT"    2351 "233182"  "189911" 187061
"AT" "AT11" "Burgenland"          2019 "NEOS"     9130 "233182"  "189911" 187061
"AT" "AT11" "Burgenland"          2019 "OTHER"    1488 "233182"  "189911" 187061
"AT" "AT11" "Burgenland"          2019 "OVP"     71566 "233182"  "189911" 187061
"AT" "AT11" "Burgenland"          2019 "SPO"     54965 "233182"  "189911" 187061
"AT" "AT12" "Niederösterreich" 1990 "FPO"    111462 "1078506" "938394" 913049
"AT" "AT12" "Niederösterreich" 1990 "GRUNE"   28649 "1078506" "938394" 913049
"AT" "AT12" "Niederösterreich" 1990 "OTHER"    4020 "1078506" "938394" 913049
"AT" "AT12" "Niederösterreich" 1990 "OVP"    357370 "1078506" "938394" 913049
"AT" "AT12" "Niederösterreich" 1990 "SPO"    389049 "1078506" "938394" 913049
"AT" "AT12" "Niederösterreich" 1990 "VDS"     14287 "1078506" "938394" 913049
"AT" "AT12" "Niederösterreich" 1990 "VGO"      8212 "1078506" "938394" 913049
"AT" "AT12" "Niederösterreich" 1994 "FPO"    165160 "1113723" "929594" 907552
"AT" "AT12" "Niederösterreich" 1994 "GRUNE"   50554 "1113723" "929594" 907552
"AT" "AT12" "Niederösterreich" 1994 "LIF"     51060 "1113723" "929594" 907552
"AT" "AT12" "Niederösterreich" 1994 "NEIN"     9390 "1113723" "929594" 907552
"AT" "AT12" "Niederösterreich" 1994 "OTHER"    4753 "1113723" "929594" 907552
"AT" "AT12" "Niederösterreich" 1994 "OVP"    308313 "1113723" "929594" 907552
"AT" "AT12" "Niederösterreich" 1994 "SPO"    318322 "1113723" "929594" 907552
"AT" "AT12" "Niederösterreich" 1995 "FPO"    164033 "1116215" "969589" 947121
"AT" "AT12" "Niederösterreich" 1995 "GRUNE"   33957 "1116215" "969589" 947121
"AT" "AT12" "Niederösterreich" 1995 "LIF"     46841 "1116215" "969589" 947121
"AT" "AT12" "Niederösterreich" 1995 "NEIN"    11786 "1116215" "969589" 947121
"AT" "AT12" "Niederösterreich" 1995 "OTHER"    2701 "1116215" "969589" 947121
"AT" "AT12" "Niederösterreich" 1995 "OVP"    326574 "1116215" "969589" 947121
"AT" "AT12" "Niederösterreich" 1995 "SPO"    361229 "1116215" "969589" 947121
"AT" "AT12" "Niederösterreich" 2002 "FPO"     68398 "1157547" "998558" 982243
"AT" "AT12" "Niederösterreich" 2002 "GRUNE"   68162 "1157547" "998558" 982243
"AT" "AT12" "Niederösterreich" 2002 "LIF"      1852 "1157547" "998558" 982243
"AT" "AT12" "Niederösterreich" 2002 "OTHER"   10453 "1157547" "998558" 982243
"AT" "AT12" "Niederösterreich" 2002 "OVP"    469691 "1157547" "998558" 982243
"AT" "AT12" "Niederösterreich" 2002 "SPO"    363687 "1157547" "998558" 982243
"AT" "AT12" "Niederösterreich" 2006 "BZO"     21919 "1204859" "983884" 963324
"AT" "AT12" "Niederösterreich" 2006 "FPO"     93812 "1204859" "983884" 963324
"AT" "AT12" "Niederösterreich" 2006 "GRUNE"   83277 "1204859" "983884" 963324
"AT" "AT12" "Niederösterreich" 2006 "KPO"      7233 "1204859" "983884" 963324
"AT" "AT12" "Niederösterreich" 2006 "MARTIN"  28741 "1204859" "983884" 963324
"AT" "AT12" "Niederösterreich" 2006 "OVP"    377437 "1204859" "983884" 963324
"AT" "AT12" "Niederösterreich" 2006 "SPO"    350905 "1204859" "983884" 963324
"AT" "AT12" "Niederösterreich" 2008 "BZO"     61392 "1258033" "975108" 951321
"AT" "AT12" "Niederösterreich" 2008 "FPO"    175819 "1258033" "975108" 951321
end

      Comment

      • #4
        Code:
        replace party_abbreviation = "_"+strtoname(party_abbreviation)

reshape wide partyvote-validvote, i(nuts2 year) j(party_abbreviation) string
        That said, you probably shouldn't be doing this anyway. There are only a few things that can be readily done using wide data in Stata. The long layout you are starting with is far more convenient to work with, and many important aspects of data analysis and management can only be done with long data. So unless you are sure you will be doing things that require the wide layout, you should probably leave the data the way you found them. Think about it carefully before you proceed.
        Last edited by Clyde Schechter; 14 Aug 2022, 13:34.
        • 1 like

        Comment

        Previous Next
        Working...
        X