Announcement

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

    Problem reshape command to change data from wide to long

    Hi all,

    I'm using Stata 17 on a Mac Catalina 10.15.17.
    My data set is currently in a wide format and I would like to change this into a long format.
    What I need is for every firm (variable: name), the data for the years 2016-2020 underneath each other instead of next to each other.
    Below I have a copy of my dataset with v5 = 2016, v6 = 2017, v7 = 2018, v8 =2019 and v9 = 2020.

    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input str45 name str5(v5 v6 v7 v8 v9) str11 cusip
"A-MARK PRECIOUS METALS - ESG Combined Score"   "NA"    "28,04" "29,76" "28,96" "28,5"  "00181T107"
"AAON - ESG Combined Score"                     "18,58" "18,99" "20,6"  "21,26" "NA"    "360206"   
"AAR - ESG Combined Score"                      "24,86" "24,36" "23,28" "27,93" "23,24" "361105"   
"AARON S COMPANY - ESG Combined Score"          "NA"    "NA"    "NA"    "NA"    "45,49" "00258W108"
"ABBOTT LABORATORIES - ESG Combined Score"      "63,16" "76,62" "46"    "77,21" "57,81" "2824100"  
"ABBVIE - ESG Combined Score"                   "74,88" "70,79" "50,58" "56,9"  "48,59" "00287Y109"
"ABEONA THERAPEUTICS - ESG Combined Score"      "NA"    "30,87" "37,34" "36,02" "34,8"  "00289Y107"
"ABERCROMBIE & FITCH 'A' - ESG Combined Score"  "37,56" "57,64" "57,93" "61,43" "68,47" "2896207"  
"ABIOMED - ESG Combined Score"                  "20,93" "25,49" "20,45" "21,13" "24,15" "3654100"  
"ABM INDS. - ESG Combined Score"                "52,64" "65,07" "63,37" "67,89" "NA"    "957100"   
"ABRAXAS PETROLEUM - ESG Combined Score"        "NA"    "25,84" "24,78" "21,82" "18,04" "3830304"  
"ACACIA RESH.-ACI.TECHS. - ESG Combined Score"  "NA"    "7,14"  "16,13" "12,62" "13,04" "3881307"  
"ACADIA HEALTHCARE CO. - ESG Combined Score"    "19,41" "29,55" "47"    "53,83" "52,47" "00404A109"
"ACADIA PHARMACEUTICALS - ESG Combined Score"   "18,48" "27,31" "19,54" "43,53" "32,72" "4225108"  
"ACADIA REAL.TST.SHRE. - ESG Combined Score"    "39,27" "55,62" "70,02" "77,84" "NA"    "4239109"  
"ACCEL ENTERTAINMENT A 1 - ESG Combined Score"  "NA"    "NA"    "NA"    "15,71" "NA"    "00436Q106"
"ACCELERATE DIAGNOSTICS - ESG Combined Score"   "30,41" "32,17" "37,23" "32,87" "26,84" "00430H102"
"ACCELERON PHARMA - ESG Combined Score"         "17,72" "37,51" "35,89" "44,4"  "48,74" "00434H108"
"ACCENTURE CLASS A - ESG Combined Score"        "78,16" "78,45" "73,63" "72,57" "73,81" "G1151C101"
"ACCO BRANDS - ESG Combined Score"              "57,89" "60,04" "70,07" "74,89" "74,04" "00081T108"
"ACCURAY - ESG Combined Score"                  "NA"    "36,34" "45,23" "36,94" "NA"    "4397105"  
"ACELRX PHARMACEUTICALS - ESG Combined Score"   "NA"    "NA"    "NA"    "30,26" "27,09" "00444T100"
"ACI WORLDWIDE - ESG Combined Score"            "47,83" "48,52" "49,5"  "52,13" "51,74" "4498101"  
"ACLARIS THERAPEUTICS - ESG Combined Score"     "11,09" "24,6"  "22,72" "21,42" "36,34" "00461U105"
"ACME UNITED - ESG Combined Score"              "NA"    "NA"    "NA"    "NA"    "24,58" "4816104"  
"ACNB - ESG Combined Score"                     "NA"    "23,08" "22,93" "32,77" "37,1"  "868109"   
"ACORDA THERAPEUTICS - ESG Combined Score"      "23,68" "24,94" "27,45" "37,7"  "37,3"  "00484M601"
"ACRES COMMERCIAL REALTY - ESG Combined Score"  "NA"    "NA"    "NA"    "28,07" "23,54" "00489Q102"
"ACTIVISION BLIZZARD - ESG Combined Score"      "53,16" "43,41" "51,78" "59,86" "35,62" "00507V109"
"ACUITY BRANDS - ESG Combined Score"            "30,4"  "41,05" "41,33" "62,56" "55,18" "00508Y102"
"ACUSHNET HOLDINGS - ESG Combined Score"        "28,04" "34,99" "31,43" "46,43" "NA"    "5098108"  
"ACUTUS MEDICAL - ESG Combined Score"           "NA"    "NA"    "NA"    "NA"    "17,15" "5111109"  
"ADAMAS PHARMACEUTICALS - ESG Combined Score"   "22,78" "25,82" "29,59" "19,92" "32,09" "00548A106"
"ADAMS RES.& EN. - ESG Combined Score"          "NA"    "16,11" "15,31" "16,04" "21,75" "6351308"  
"ADAPTHEALTH - ESG Combined Score"              "NA"    "NA"    "NA"    "8,44"  "9,35"  "00653Q102"
"ADAPTIVE BIOTECHNOLOGIES - ESG Combined Score" "NA"    "NA"    "16,15" "19,81" "39,48" "00650F109"
"ADDUS HOMECARE - ESG Combined Score"           "25,81" "20,71" "19,8"  "20,77" "26,75" "6739106"  
"ADIENT - ESG Combined Score"                   "36,89" "39,62" "35,15" "39,73" "34,96" "G0084W101"
"ADMA BIOLOGICS - ESG Combined Score"           "NA"    "30,22" "32,56" "27,28" "26,33" "899104"   
"ADOBE (NAS) - ESG Combined Score"              "62,58" "75,6"  "78,43" "67,3"  "77,67" "00724F101"
"ADT - ESG Combined Score"                      "NA"    "21,62" "16,34" "20,1"  "22,63" "00090Q103"
"ADTALEM GLOBAL EDUCATION - ESG Combined Score" "42,91" "23,68" "57,18" "72,07" "63,13" "00737L103"
"ADTRAN - ESG Combined Score"                   "52,79" "47,36" "53,51" "35,93" "50,83" "00738A106"
"ADV.AUTO PARTS - ESG Combined Score"           "33,27" "54,78" "62,67" "43,49" "52,08" "00751Y106"
"ADVANCED DRAINAGE SYS. - ESG Combined Score"   "23,13" "25,44" "18,28" "23,46" "36,22" "00790R104"
"ADVANCED EMISSIONS SLTN. - ESG Combined Score" "NA"    "23,31" "21,29" "32,2"  "32,38" "00770C101"
"ADVANCED ENERGY INDS. - ESG Combined Score"    "28,45" "35,6"  "27,81" "48,06" "48,38" "7973100"  
"ADVANCED MICRO DEVICES - ESG Combined Score"   "65,07" "69,89" "42,82" "65,32" "NA"    "7903107"  
"ADVANSIX - ESG Combined Score"                 "29,14" "47,45" "48,11" "51,71" "57,44" "00773T101"
"ADVANTAGE SOLUTIONS A - ESG Combined Score"    "NA"    "NA"    "NA"    "NA"    "40,49" "00791N102"
"ADVENT TECHNOLOGIES - ESG Combined Score"      "NA"    "NA"    "NA"    "NA"    "10,41" "00788A105"
"ADVERUM BIOTCHS. - ESG Combined Score"         "NA"    "NA"    "24,45" "26,54" "35,42" "00773U108"
"AECOM - ESG Combined Score"                    "44,94" "49,53" "56,1"  "57,4"  "52,08" "00766T100"
"AEGLEA BIOTHERAPEUTICS - ESG Combined Score"   "NA"    "NA"    "NA"    "28,91" "26,79" "00773J103"
"AERCAP HOLDINGS N V - ESG Combined Score"      "1,01"  "0,74"  "0,32"  "0,37"  "38,59" "N00985106"
"AERIE PHARMACEUTICALS - ESG Combined Score"    "23,28" "20,16" "28"    "47,22" "56,57" "00771V108"
"AEROJET ROCKETDYNE HDG. - ESG Combined Score"  "20,25" "24,22" "23,53" "20,05" "43,05" "7800105"  
"AEROVIRONMENT - ESG Combined Score"            "33,37" "31,54" "24,13" "37,88" "34,92" "8073108"  
"AERSALE - ESG Combined Score"                  "NA"    "NA"    "NA"    "NA"    "18,07" "NA"       
"AES - ESG Combined Score"                      "60,85" "55,98" "58,72" "68,04" "NA"    "00130H105"
"AESTHETIC MED. INTHDG. - ESG Combined Score"   "NA"    "NA"    "NA"    "NA"    "40,03" "00809M104"
"AFFILIATED MANAGERS - ESG Combined Score"      "40,91" "42,03" "53,24" "46,37" "53,62" "8252108"  
"AFFIMED - ESG Combined Score"                  "NA"    "NA"    "NA"    "32,62" "26,23" "N01045108"
"AFLAC - ESG Combined Score"                    "51,32" "60"    "29,66" "59,81" "NA"    "1055102"  
"AFYA - ESG Combined Score"                     "NA"    "NA"    "NA"    "30,82" "42,69" "G01125106"
"AG MORTGAGE INV.TRUST - ESG Combined Score"    "18,99" "18,47" "19,8"  "21,28" "14,09" "1228501"  
"AGCO - ESG Combined Score"                     "48,48" "55,11" "49,94" "56,54" "NA"    "1084102"  
"AGENUS - ESG Combined Score"                   "NA"    "27,72" "39,34" "36,24" "43,44" "00847G705"
"AGILE THERAPEUTICS - ESG Combined Score"       "NA"    "24,5"  "22,8"  "23,13" "35,71" "00847L100"
"AGILENT TECHS. - ESG Combined Score"           "88,42" "87,48" "89,44" "88,66" "88,65" "00846U101"
"AGILYSYS - ESG Combined Score"                 "NA"    "NA"    "41,42" "35,68" "29,35" "00847J105"
"AGIOS PHARMACEUTICALS - ESG Combined Score"    "20,9"  "38,55" "39,56" "51,73" "57,55" "00847X104"
"AGNC INVESTMENT REIT - ESG Combined Score"     "17,35" "25,17" "39,64" "48,94" "NA"    "00123Q104"
"AGREE REALTY - ESG Combined Score"             "28,96" "28,68" "31,34" "41,27" "31,04" "8492100"  
"AGROFRESH SOLUTIONS - ESG Combined Score"      "NA"    "14,74" "20,45" "27"    "25,84" "00856G109"
"AIR LEASE - ESG Combined Score"                "22,31" "26,38" "29,96" "NA"    "NA"    "00912X302"
"AIR PRDS.& CHEMS. - ESG Combined Score"        "73,18" "79,64" "84,83" "80,06" "80,25" "9158106"  
"AIR TRANSPORT SVS.GP. - ESG Combined Score"    "16,58" "15,54" "21,05" "23,56" "36,34" "00922R105"
"AIRBNB A - ESG Combined Score"                 "NA"    "NA"    "NA"    "NA"    "23,69" "9066101"  
"AKAMAI TECHS. - ESG Combined Score"            "44,73" "60,98" "52,16" "57,48" "58,29" "00971T101"
"AKEBIA THERAPEUTICS - ESG Combined Score"      "17,75" "20,97" "23,39" "28,32" "31,91" "00972D105"
"AKERNA - ESG Combined Score"                   "NA"    "NA"    "NA"    "29,94" "29,36" "00973W102"
"AKERO THERAPEUTICS - ESG Combined Score"       "NA"    "NA"    "NA"    "21,5"  "26,12" "00973Y108"
"AKOUOS - ESG Combined Score"                   "NA"    "NA"    "NA"    "NA"    "18,69" "00973J101"
"AKOUSTIS TECHNOLOGIES - ESG Combined Score"    "NA"    "7,04"  "9,86"  "17,52" "14,62" "00973N102"
"ALAMO GROUP - ESG Combined Score"              "19,76" "11,7"  "14,06" "41,68" "48,44" "11311107" 
"ALARMCOM HOLDINGS - ESG Combined Score"        "31,38" "39,27" "45,19" "42,2"  "45,16" "11642105" 
"ALASKA AIR GROUP - ESG Combined Score"         "64,74" "61"    "54,45" "54,29" "50,48" "11659109" 
"ALBANY INTL.'A' - ESG Combined Score"          "28,64" "28,89" "27,68" "33,8"  "40,72" "12348108" 
"ALBEMARLE - ESG Combined Score"                "57,97" "69,04" "35,65" "65,42" "43,67" "12653101" 
"ALBIREO PHARMA - ESG Combined Score"           "NA"    "21,86" "23,41" "24,49" "25,08" "01345P106"
"ALCOA - ESG Combined Score"                    "87,29" "80,75" "83,33" "80,45" "87,39" "13872106" 
"ALDEYRA THERAPEUTICS - ESG Combined Score"     "NA"    "NA"    "NA"    "29,21" "26,25" "01438T106"
"ALECTOR - ESG Combined Score"                  "NA"    "NA"    "17,4"  "16,4"  "20,85" "14442107" 
"ALERUS FINL. - ESG Combined Score"             "NA"    "NA"    "17,24" "44,97" "45,88" "01446U103"
"ALEXANDER AND BALDWIN - ESG Combined Score"    "38,84" "42,03" "36,39" "47,79" "29,63" "14491104" 
"ALEXANDER'S - ESG Combined Score"              "15,24" "15,52" "12,98" "46,27" "44,34" "14752109" 
"ALEXANDRIA RLST.EQTIES. - ESG Combined Score"  "42,1"  "68,98" "70,16" "73,97" "73,46" "15271109" 
"ALICO - ESG Combined Score"                    "NA"    "21,19" "13,28" "11,77" "28,94" "16230104" 
"ALIGN TECHNOLOGY - ESG Combined Score"         "20,35" "21,76" "30"    "36,98" "51,73" "16255101" 
end
    I used the following code:

    //reshape data from wide to long
    reshape long v, i(name) j(year)

    However I get the following error:

    (j = 5 6 7 8 9)
    variable id does not uniquely identify the observations
    Your data are currently wide. You are performing a reshape long. You specified i(name) and j(year). In the
    current wide form, variable name should uniquely identify the observations. Remember this picture:

    long wide
    +---------------+ +------------------+
    | i j a b | | i a1 a2 b1 b2 |
    |---------------| <--- reshape ---> |------------------|
    | 1 1 1 2 | | 1 1 3 2 4 |
    | 1 2 3 4 | | 2 5 7 6 8 |
    | 2 1 5 6 | +------------------+
    | 2 2 7 8 |
    +---------------+
    Type reshape error for a list of the problem observations.
    r(9);


    After typing reshape error for a list of the problem observations, I almost lose all of my observations.;
    Also, I tried to create a unique identifier variable that associates with the variable name in my dataset with the commands: gen id=_N and egen id =group (name), but both commands do not solve the problem.

    So, can someone please help me and show me which code I should use or steps I should take to change my data from wide to long?

    Thanks in advance!
    Tags: None
  • #2
    The problem is not with the -reshape- command. The problem is that your observations are not uniquely identified by the variable name. The first step is to see the offending observations:

    Code:
    duplicates tag name, gen(flag)
browse if flag
    will show them to you.

    If you see no actual data in the browser after this, it means that there are a bunch of blank observations in your data. This sometimes happens with data that are imported from spreadsheets or .csv files. (And your data looks like it comes from one of those sources.) If that's what you get, you can just drop all those, with -drop if missing(name)-.

    If, on the other hand, you do see duplicate observations with the same name, then you have to figure out how to fix that problem. Perhaps one of the duplicates has the wrong name and you can replace it with the correct name. Or perhaps the duplicates are complete duplicates agreeing in all variables. In that case you can just run -duplicates drop- to get rid of them. But before doing that, I suggest that you review the data management that led up to the current data set (or confer with whoever supplied the data set) about this situation because it is usually an error to have completely duplicate observations in a data set, and where one mistake is found, others often lurk.

    There is another possibility, which is that the data are fine and you simply had a misunderstanding in thinking that name would uniquely identify observations. But if that is the case, ordinarily there should be some other variable that, perhaps combined with name, will do so. Perhaps there are two cusips having the same name, and using cusip in the -i()- option would solve your problem. Or perhaps the combination of name and cusip would do the trick. Or perhaps there is another variable you didn't show that identifies, say, subdivisions within the firms, which, combined with name (or cusip or both) would uniquely identify observations. If there is no such other variable, then you have to wonder how you can have two different sets of values for those v* variables for the same firm. How can they both be right?

    In any event, you should not seek code that will workaround this problem. The error message is telling you that your data are wrong (or at least are not what you think they should be) and you should solve that problem before proceeding. Once you get a correct data set, correctly understood, your -reshape- command will work exactly as you wrote it, or perhaps with a different specification of the -i()- option.
    • 1 like

    Comment

    • #3
      Thank you very much Clyde!
      Your solutions worked and my dataset is now successfully transformed from wide to long.

      I do have another problem and I hope you can also help to solve this.
      I would like to drop the values "NA" in my dataset, but when I try for example the following code: drop if cusip == NA, stata says "NA not found".
      Do you know which code removes all the observations with NA?

      Comment

      • #4
        Code:
        drop if cusip == "NA"
        When you write a Stata expression, Stata interprets all of the "words" as variable names unless you wrap them in quotes. When you wrap them in quotes, it then interprets them as the literal string value.

        Comment

        • #5
          Thank you Clyde!

          Comment

          Previous Next
          Working...
          X