Announcement

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

  • Programming to transpose string data (row to column)

    Dear all,

    I am having trouble transposing a row of data and putting that data into one variable, something similar to xpose and sxpose, but not exactly the same.

    Here is what I need help with: I have a dataset like below:

    Code:
    input str3(var1 var2) str5 var3 str4 var4 str5 var5
    
    "one" "two" "three" "four" ""
    ""    ""    ""      ""     ""
    ""    ""    ""      ""     ""
    ""    ""    ""      ""     ""
    end
    I want to have the row data (one, two, three and four) in a variable. Something like this:
    Code:
    "one" "two" "three" "four" "one"
    ""    ""    ""      ""     "two"
    ""    ""    ""      ""     "three"
    ""    ""    ""      ""     "four"
    I cannot use sxpose and xpose, because it will mess other data.

    So, I was trying to use loop to transpose the row into column. I have tried looping various ways, for instance the following without success:
    Code:
    foreach i of varlist var1-var4 {
    forv n=1/4 {
    replace var=`i'[1] in `n' if var==""
    }
    }
    Would someone be able to help?

    Thanks.
    Masood

  • #2
    Problems needing pure transposes seem to be rare. I'd point instead to reshape

    Code:
    input str3(var1 var2) str5 var3 str4 var4 str5 var5
    
    "one" "two" "three" "four" ""
    ""    ""    ""      ""     ""
    end
    
    gen id = _n 
    
    reshape long var, i(id) 
    
    list, sepby(id) 
    
         +-----------------+
         | id   _j     var |
         |-----------------|
      1. |  1    1     one |
      2. |  1    2     two |
      3. |  1    3   three |
      4. |  1    4    four |
      5. |  1    5         |
         |-----------------|
      6. |  2    1         |
      7. |  2    2         |
      8. |  2    3         |
      9. |  2    4         |
     10. |  2    5         |
         +-----------------+


    Comment


    • #3
      Nick,

      I cannot use reshape for it will mess the dataset even more.

      Can you see the following code, and let me know why it doesn't work for the above problem? It returns "invalid syntax" error.

      Code:
      . foreach i of varlist E-DP {
        2. forv n=2 {
        3. replace name=`i'[2] in `n'
        4. local n=`n'+1
        5. }
        6. }
      Thanks,
      Masood

      Comment


      • #4
        I can't see a syntax error in your code, but it's some distance from what you want, as [2] is wired into your code and you won't loop over observations that way. The inner loop should execute just once for each time round the outer loop, regardless of your attempt to bump up the counter.

        How do the variable names in #3 relate to those in #1?

        I read your example and code as implying that you have 3 blank observations in your data under each informative observation.

        Code:
         
        clear 
        input str5(var1 var2 var3 var4 var5) 
        "one" "two" "three" "four" ""
        ""    ""    ""      ""     ""
        ""    ""    ""      ""     ""
        ""    ""    ""      ""     ""
        "yet" "more" "stuff" "here" ""
        ""    ""    ""      ""     ""
        ""    ""    ""      ""     ""
        ""    ""    ""      ""     ""
        end
        egen which = seq(), to(4) 
        forval j = 1/4 { 
           replace var5 = var`j'[_n + 1 - `j'] if which == `j' 
        } 
        
        list, sep(4)
        
             +--------------------------------------------+
             | var1   var2    var3   var4    var5   which |
             |--------------------------------------------|
          1. |  one    two   three   four     one       1 |
          2. |                                two       2 |
          3. |                              three       3 |
          4. |                               four       4 |
             |--------------------------------------------|
          5. |  yet   more   stuff   here     yet       1 |
          6. |                               more       2 |
          7. |                              stuff       3 |
          8. |                               here       4 |
             +--------------------------------------------+

        Comment


        • #5
          It seems that something like

          Code:
           
          forval n = 2 {
          ...
          }
          fails regardless as 2 is not a range. 2/2 would be legal, but already mentioned, you can't bump the counter from within the loop.

          Comment


          • #6
            Thanks Nick, it works flawlessly.

            For my purpose, just before seeing your response, I did this, which also works:

            Code:
            forv n=1/4 {
            replace var5=var`n'[1] in `n'
            }
            Thanks again,
            Masood

            Comment


            • #7
              OK, but that would only work for the first 4 observations.

              Comment


              • #8
                Originally posted by Nick Cox View Post
                OK, but that would only work for the first 4 observations.
                Dear Nick Cox, I have problem in transposing my data like this. My variable for each firm is now put in column like this.
                Code:
                * Example generated by -dataex-. To install: ssc install dataex
                clear
                input float date2 double(R1 R2 R3 R4)
                13179  .02679275019  .02903811252  .03347534996  .00060132291
                13208   .0276285495   .0176366843  .00294464075  .03712389766
                13237  .00522778192  .01299826689  .01350557839  .02902060624
                13269   .0133729569   .0119760479  .02085747392   .0816954723
                13300  .01906158357  .01521555367  .00658735778  .02620238702
                13328   .0035971223  -.0149875104 -.00686978353 -.04264752683
                13361 -.03440860215 -.05071851225 -.03929618768 -.05041910766
                13391  .02895322939   .0320569902  .02564102564  .07770263297
                13422   .0505050505  .05522001725   .0277198078  .05238589211
                13453  .02884615384   .0269828291  .01700879765  .00246426811
                13482  .06675567423  .07006369426  .06286330158   .0595556237
                13514 -.01751314142  -.0194516279  .00290866783  .00894151074
                13545  .04354032833  .05391743892  .02784222737  .05786911844
                13573  .00410396716             0  .02200902934  .02249488752
                13604 -.03269754768 -.05035971223 -.03699613473        -.0395
                13634  .03943661971  .04545454545  .02867212223   .0218636127
                13664   .0677506775  .06843800322  .06116722783  .09933774834
                13695  .03616751269  .03617181612  .01895376191  .04784598248
                13726    .104715248   .0909090909  .07202505219  .03407079646
                13755 -.00609756097         -.026 -.01411879259 -.01326486949
                13787  .04796430563  .05749486652  .05086419753  .03902862098
                13818  -.0521554018 -.04595469255 -.03665413533 -.04048414023
                13846   .0129140932  .02781546811    .023175669 -.00869943453
                13879 -.01459534368  .01306933863 -.00100755667 -.00061344297
                13909  .01528117359  .01170960187             0  .02308078273
                13937  .07164358819  .07638888888   .0640443772  .09073075036
                13969  .03707865168  .05017921146  .05260663507  .02522971013
                13999  .00758396533  .00955631399  .01404989858 -.00175438596
                14028 -.01720430107 -.02704530087 -.01159678858 -.04336361737
                14060  .01914660831   .0277970813 -.00538906828 -.01469912723
                14091  -.0397208803 -.02569303583  -.0345926263 -.09277389277
                14122 -.15092230296  -.1547536433  -.1489863272 -.25642343268
                14152  .02830809743  .05911330049  .02880886426  .02280580511
                14182   .0742637644  .07286821705  .08454496499  .12162162162
                14213  .04469606674  .06358381502  .04808676141  .01816584474
                14244  .03800285225   .0542671095   .0025406504   .0162842339
                14273  .04091174751  .02887323943  .00506842372 -.02403495994
                14301 -.05783267827 -.03696098562 -.02017145738 -.08114699016
                14334  .00595947556  .02416488983   .0185280494  .03093607937
                14364  .07168246445  .03331020124  .10343433053  .12302839116
                14392  .01768933112 -.01410342511   .0158804297  .02712876616
                14425  .02444323737   .0408719346  .02436781609  .00547195622
                14455 -.00530222693 -.02683246073 -.03923821155 -.03920318244
                14487  .02452025586 -.01277740416 -.04219409282 -.06788966553
                14517 -.01144640998 -.03201634877 -.03671071953 -.03650190114
                14546   .0347368421  .05700211118  .01168699186  -.0268350434
                14578  .08138351983  .01331557922  .02513885428  .00140636775
                14609  .16776575729  .05385728925  .05476451259  .03160453808
                14640  .01997077447 -.05779078273 -.08099688473 -.04791830322
                14669  .14565425023 -.01940993788 -.07966101694 -.01229317422
                14700   .0216756982  .09738717339   .1270718232  .08604845446
                14728 -.07466340269 -.03318903318  .01416122004 -.00769230769
                14761 -.07980599647 -.03134328358  .00859291084  .03746880124
                14791  .12985146142   .0346687211 -.03914289272 -.04630050691
                14822  .02374893977 -.02010424422    .045194508   .0250783699
                14853  .09776304888  .06838905775  .06732348111  .06346336369
                14882 -.06037735849 -.06330014224 -.02051282051  .02692784177
                14914 -.09919678714 -.02353834472   .0392670157  .02731092436
                14944 -.18546589389 -.08087091757 -.06457557212 -.03680981595
                14973  .01529282977  .00718430839   .0443076923  .08183926752
                15006  .05291871249 -.02042628774  .04065998821 -.01243455497
                15034 -.17461139896 -.08975521305 -.09173272933 -.01590457256
                15064 -.10608913998 -.06474103585 -.05610972568  -.0263080211
                15095  .04424157303   .0649627263  .08058124174  .05397923875
                15126 -.00537995965          .006  .00812314013  .01378857518
                15155 -.02096010818 -.01192842942 -.03987138263 -.01968808908
                15187 -.02969613259 -.01710261569 -.01071667782 -.01586252478
                15218 -.07971530249 -.05424769703 -.06499661475 -.01746138347
                15246 -.11600928074 -.07359307359 -.09196234612 -.08592178423
                15279  .05074365704  .02336448598  .02631578947   .0119670905
                15309   .0732722731  .06164383561  .07320913707  .07674337102
                15340   .0155159038   .0064516129  .01013758146   .0352267413
                15371 -.04507257448 -.02564102564 -.01362007168  .00464807436
                15399        -.0336 -.02850877192 -.01671511627  .01796692663
                15427  .05380794701  .04063205417   .0332594235  .05461638491
                15460 -.06520031421  -.0650759219 -.04935622317 -.00184956843
                15491 -.01680672268 -.00928074245 -.00827689992 -.00061147326
                15519 -.10256410256 -.07025761124 -.07132018209 -.04119490265
                15552 -.08761904761 -.09949622166 -.08823529411 -.09413281753
                15582  .02192066805  .01398601398  .00448028673    .011408869
                15613 -.10725229826 -.10068965517 -.10437109723 -.09078114004
                15644  .08352402745  .07515337423   .0687250996  .03250773993
                15673  .06652587117  .04136947218   .0587138863  .04270175463
                15705 -.05643564356  -.0506849315 -.06073943661 -.02684891236
                15736 -.03252885624 -.01154401154 -.02530459231  -.0192450037
                15764 -.02819956616 -.01897810218  -.0173076923 -.01360246723
                15795  .00558035714  .00892857142 -.00195694716  .00045731707
                15825  .09766925638  .06784660766  .08431372549  .06355283307
                15855  .04145601617  .05662983425  .06441076044  .07358436097
                15886  .00388349514 -.00261437908  .01019541206  .00498381145
                15917  .01934235976  .00917431192  .02943650126   .0300400534
                15946  .02561669829  .01298701298  .02696078431  .03136867904
                15978 -.02497687326 -.00641025641  -.0198886237 -.00628535512
                16009  .04743833017           .04  .05925324675  .07273877292
                16037  .02536231884  .00248138957  .01379310344  .02240566037
                16070  .03091872791  .03341584158  .05139833711  .03207493916
                16100  .01970865467  .01916167664  .01006470165   .0290665176
                16128  .02352941176  .00117508813  .01850533807  .02770233568
                16161 -.01149425287 -.01643192488 -.01677148846 -.00105708245
                16191 -.01827242524 -.01789976133 -.02558635394 -.04021164021
                end
                format %td date2

                I want to transpose in this format below. So, how should I do:


                Time Firm Return
                date. R1. .........
                date. R1. .........
                date. R2. .........
                date. R2. .........

                Comment


                • #9
                  There are no string variables in your problem. Please re-post under a title that applies to your problem.

                  Comment

                  Working...
                  X