Announcement

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

  • combine columns and rows into one row

    Hello Users,
    Please help with data transformation.

    The current data format:
    sub-var1 sub-var2 sub-var3
    var1 2 5 7
    var2 9 10 6
    var3 3 8 1
    sub-var4 sub-var5 sub-var6
    var4 0 4 7

    I want to transform them into:

    var1 var2 var3 var4 var5 var6 var7 var8 var9 var10 var11 var12
    2 5 7 9 10 6 3 8 1 0 4 7

    Thank you very much.
    Lynn

  • #2
    As described in the StataList FAQ you are asked to read before first posting to the list, please post an example of your data using -dataex-. Your situation will require a special purpose bit of programming (I think), and doing that without being certain of the actual structure (data types, etc.) of your data set is not possible. How to program it would like depend on finding some regularity in the variable names, which is not possible with your example.
    Last edited by Mike Lacy; 30 Mar 2020, 08:41.

    Comment


    • #3
      For the example you supplied, and treating your first line as an observation and assuming you don't really want just one observation as your goal, here is something that works. If this doesn't work, you will need to supply a better example of your actual data and your goal.

      Code:
      * Example generated by -dataex-. To install: ssc install dataex
      clear
      input str7(s1 s2 s3) str1 s4
      "sub-var1" "sub-var2" "sub-var3" ""
      "var1"    "2"       "5"       "7"
      "var2"    "9"       "10"      "6"
      "var3"    "3"       "8"       "1"
      "sub-var4" "sub-var5" "sub-var6" ""
      "var4"    "0"       "4"       "7"
      end
      list
      describe 
      //
      // Transpose using Mata; apparently -xpose- won't handle strings.
      putmata s = (s*) 
      mata: s = s'
      clear
      getmata (s*) = s
      //
      list // for illustration
      drop s1 s5 // not needed
      foreach v of varlist * {
         local name = `v'[1]
         rename `v' `name'
      }
      drop in 1
      destring var*, replace force
      list

      Comment


      • #4
        Hello Mike,
        Thank you very much for your time and apologies for the confusion.
        I am learning about dataex and get back to you with my data.

        Comment


        • #5
          Hello again,
          The current data format:
          Code:
          clear
          input str9 deposits cheque savings one_day one_month six_month sixplus total
          "retail(r)" 0 612 6707638 55014 0 0 6763264 ""
          "bank(b)" 0 0 100373 0 0 0 100373 ""
          "other(o)" 0 612 5400560 45000 0 0 5446172 ""
          end
          list
          describe
          I want to transform them into:

          Code:
          clear
          input r_cheque r_savings r_one_day r_one_month r_six_month r_sixplus r_total b_cheque b_savings b_one_day b_one_month b_six_month b_sixplus b_total o_cheque o_savings o_one_day o_one_month o_six_month o_sixplus o_total
          0 612 6707638 55014 0 0 6763264 0 0 100373 0 0 0 100373 0 612 5400560 45000 0 0 5446172
          end
          list 
          describe

          Comment


          • #6
            I do not know how useful it is to have all your data in one observation, but here is one way.

            Code:
            * Example generated by -dataex-. To install: ssc install dataex
            clear
            input str9 deposits float(cheque savings one_day one_month six_month sixplus total)
            "retail(r)" 0 612 6707638 55014 0 0 6763264
            "bank(b)"   0   0  100373     0 0 0  100373
            "other(o)"  0 612 5400560 45000 0 0 5446172
            end
            
            ds deposits, not
            local list= r(varlist)
            local n: word count `list'
            local label ""
            local i 1
            foreach var of local list{
                local label "`label' `i' "`var'""
                local ++i
            }
            rename (`list') (var#), addnumber(1)
            reshape long var, i(deposits) j(which)
            lab def which `label'
            lab values which which
            decode which, gen(w)
            replace w= substr(deposit, 1,1)+"_"+ w
            drop deposits
            replace which=1
            reshape wide var, i(which) j(w, string)
            drop which
            rename var* *
            Res.:

            Code:
            * Example generated by -dataex-. To install: ssc install dataex
            clear
            input float(b_cheque b_one_day b_one_month b_savings b_six_month b_sixplus b_total o_cheque o_one_day o_one_month o_savings o_six_month o_sixplus o_total r_cheque r_one_day r_one_month r_savings r_six_month r_sixplus r_total)
            0 100373 0 0 0 0 100373 0 5400560 45000 612 0 0 5446172 0 6707638 55014 612 0 0 6763264
            end

            Comment


            • #7
              Hello Andrew and Mike,
              Thank you so much. It solves my problem.

              Comment

              Working...
              X