Announcement

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

  • Replacing 1s (with missing values in between) with a consecutive sequence (from 1 to the row total)

    Hi! I was wondering if you could help me with the following question:

    I have the following (abbreviated) data set, where I have two type of variables. The first one, total_mi, adds up a_m1 through a_m12, it is essentially the row total. The second type of variable is the a_m* variables, which to keep the explanation simple, is equal to 1 when something else occurs in the data set, as you can see, they do not always turn 1, which means that there may be missing values in between the different a_m* columns (see the bold row below for the example that is easy to follow).

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input float(total_mi a_m1 a_m2 a_m3 a_m4 a_m5 a_m6 a_m7 a_m8 a_m9)
    2 1 . . . . 1 . . .
    8 1 1 1 1 1 . 1 1 1
    7 1 1 1 1 1 1 . 1 .
    8 1 1 1 1 1 1 1 1 .
    6 1 1 1 1 1 1 . . .
    8 1 1 1 1 1 . 1 1 1
    7 1 1 1 1 1 1 1 . .
    8 1 1 1 1 1 1 1 1 .
    2 1 1 . . . . . . .
    5 1 1 . 1 . . 1 1 .
    4 1 . . 1 1 . 1 . .
    9 1 1 1 1 1 1 1 1 1
    7 1 1 1 1 1 . 1 1 .
    8 1 1 1 1 1 1 1 1 .
    8 1 1 1 1 1 1 . 1 1
    end
    I want to "automatically" (through some code which I can't seem to program correctly) replace the previous data set into the following:

    Code:
    clear
    input float total_mi byte(key1 key2 key3 key4 key5 key6 key7 key8 key9)
    2 1 . . . . .2 . . .
    8 1 2 3 4 5 . 6 7 .
    7 1 2 3 4 5 6 . 7 .
    8 1 2 3 4 5 6 7 . .
    6 1 2 3 4 5 6 . . .
    8 1 2 3 4 5 . 6 7 .
    7 1 2 3 4 5 6 7 . .
    8 1 2 3 4 5 6 7 8 .
    2 1 . . . . . . . .
    5 1 2 . 3 . . 4 5 .
    4 1 . . 2 3 . 4 . .
    9 1 2 3 4 5 6 7 8 9
    7 1 2 3 4 5 . 6 7 .
    8 1 2 3 4 5 6 7 8 .
    8 1 2 3 4 5 6 . 7 8
    end

    As you can see, all that I want to do is turn the 1s into a consecutive sequence from 1 to n, where n is the row total (total_mi). How can I achieve this? Any help/suggestions are welcome.

    Thanks!

  • #2
    Thanks for the data example. The easiest way would be to reshape long, sum and reshape wide again. Here is one way with the data in wide layout.

    Code:
    * Example generated by -dataex-. To install: ssc install dataex
    clear
    input float(total_mi a_m1 a_m2 a_m3 a_m4 a_m5 a_m6 a_m7 a_m8 a_m9)
    2 1 . . . . 1 . . .
    8 1 1 1 1 1 . 1 1 1
    7 1 1 1 1 1 1 . 1 .
    8 1 1 1 1 1 1 1 1 .
    6 1 1 1 1 1 1 . . .
    8 1 1 1 1 1 . 1 1 1
    7 1 1 1 1 1 1 1 . .
    8 1 1 1 1 1 1 1 1 .
    2 1 1 . . . . . . .
    5 1 1 . 1 . . 1 1 .
    4 1 . . 1 1 . 1 . .
    9 1 1 1 1 1 1 1 1 1
    7 1 1 1 1 1 . 1 1 .
    8 1 1 1 1 1 1 1 1 .
    8 1 1 1 1 1 1 . 1 1
    end
    
    gen order=_n
    expand 2, gen(x)
    forval i=1/9{
    replace a_m`i'=0 if a_m`i'==. & x==1
    }
    forval i=2/9{
    local j= `i'-1
    bys order: replace a_m`i'=a_m`j'+ a_m`i' if x==1
    }
    forval i=1/9{
    bys order (x): replace a_m`i'=. if a_m`i'[_n-1]==. & _n>1
    }
    keep if x==1
    drop x order

    Result:

    Code:
    . l, clean
    
           total_mi   a_m1   a_m2   a_m3   a_m4   a_m5   a_m6   a_m7   a_m8   a_m9  
      1.          2      1      .      .      .      .      2      .      .      .  
      2.          8      1      2      3      4      5      .      6      7      8  
      3.          7      1      2      3      4      5      6      .      7      .  
      4.          8      1      2      3      4      5      6      7      8      .  
      5.          6      1      2      3      4      5      6      .      .      .  
      6.          8      1      2      3      4      5      .      6      7      8  
      7.          7      1      2      3      4      5      6      7      .      .  
      8.          8      1      2      3      4      5      6      7      8      .  
      9.          2      1      2      .      .      .      .      .      .      .  
     10.          5      1      2      .      3      .      .      4      5      .  
     11.          4      1      .      .      2      3      .      4      .      .  
     12.          9      1      2      3      4      5      6      7      8      9  
     13.          7      1      2      3      4      5      .      6      7      .  
     14.          8      1      2      3      4      5      6      7      8      .  
     15.          8      1      2      3      4      5      6      .      7      8
    Last edited by Andrew Musau; 19 Mar 2019, 17:44.

    Comment


    • #3
      Code:
      gen long obs_no = _n
      reshape long a_m, i(obs_no)
      by obs_no (_j): gen key = sum(!missing(a_m))
      reshape wide a_m key, i(obs_no) j(_j)
      Notes: Depending on what you plan to do next, you might skip the final -reshape wide- command and leave the data in long layout. Most Stata commands work better (or only) with long data. Also, coding variables as 1/ missing value is usually not a good idea in Stata. 1/0 variables work much better with most things in Stata. Consider replacing the missing values by 0.

      Added: Crossed with #2, which alludes to my solution as an alternative to his.

      Comment


      • #4
        What you ask for can be done with just one loop and no reshape. Nevertheless I agree completely with Clyde's advice.

        Code:
        * Example generated by -dataex-. To install: ssc install dataex
        clear
        input float(total_mi a_m1 a_m2 a_m3 a_m4 a_m5 a_m6 a_m7 a_m8 a_m9)
        2 1 . . . . 1 . . .
        8 1 1 1 1 1 . 1 1 1
        7 1 1 1 1 1 1 . 1 .
        8 1 1 1 1 1 1 1 1 .
        6 1 1 1 1 1 1 . . .
        8 1 1 1 1 1 . 1 1 1
        7 1 1 1 1 1 1 1 . .
        8 1 1 1 1 1 1 1 1 .
        2 1 1 . . . . . . .
        5 1 1 . 1 . . 1 1 .
        4 1 . . 1 1 . 1 . .
        9 1 1 1 1 1 1 1 1 1
        7 1 1 1 1 1 . 1 1 .
        8 1 1 1 1 1 1 1 1 .
        8 1 1 1 1 1 1 . 1 1
        end
        
        gen sum = 0 
        quietly forval j = 1/9 { 
            replace sum = sum + (a_m`j' == 1) 
            replace a_m`j' = sum if a_m`j' == 1 
        } 
        
        list 
        
        
             +-------------------------------------------------------------------------------+
             | total_mi   a_m1   a_m2   a_m3   a_m4   a_m5   a_m6   a_m7   a_m8   a_m9   sum |
             |-------------------------------------------------------------------------------|
          1. |        2      1      .      .      .      .      2      .      .      .     2 |
          2. |        8      1      2      3      4      5      .      6      7      8     8 |
          3. |        7      1      2      3      4      5      6      .      7      .     7 |
          4. |        8      1      2      3      4      5      6      7      8      .     8 |
          5. |        6      1      2      3      4      5      6      .      .      .     6 |
             |-------------------------------------------------------------------------------|
          6. |        8      1      2      3      4      5      .      6      7      8     8 |
          7. |        7      1      2      3      4      5      6      7      .      .     7 |
          8. |        8      1      2      3      4      5      6      7      8      .     8 |
          9. |        2      1      2      .      .      .      .      .      .      .     2 |
         10. |        5      1      2      .      3      .      .      4      5      .     5 |
             |-------------------------------------------------------------------------------|
         11. |        4      1      .      .      2      3      .      4      .      .     4 |
         12. |        9      1      2      3      4      5      6      7      8      9     9 |
         13. |        7      1      2      3      4      5      .      6      7      .     7 |
         14. |        8      1      2      3      4      5      6      7      8      .     8 |
         15. |        8      1      2      3      4      5      6      .      7      8     8 |
             +-------------------------------------------------------------------------------+

        Comment


        • #5
          Thank you all, this was very useful.

          Comment

          Working...
          X