Reshaping help with different levels for different variables

[Nabittun Nahar]

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input float uid double(typewatersources_1 typewatersources_2 typewatersources_3 typewatersources_4 distancetosource_1 Roundtriptime_1 waterusage_1_1 waterusage_2_1 waterusage_3_1)
  1 1 1 0 0    .   . 0 1 1
  2 0 0 0 0    .   . 1 1 1
  3 1 0 0 0    .   . 1 1 1
  4 0 1 1 0   20   5 0 1 1
  5 0 0 0 0    .   . 0 1 1
  6 0 0 1 0   10   1 0 1 1
  7 0 0 0 0 1500  60 1 1 1
  8 1 0 0 0    .   . 1 1 1
  9 1 1 0 0   50   3 1 1 1
 10 0 0 0 1    .   . 1 1 1
 11 0 0 0 0    5   1 1 1 0
 12 1 0 0 0    .   . 0 0 1
 13 1 1 0 0    .   . 0 0 1
 14 0 0 1 0    5   3 0 0 1
 15 0 1 0 0    2   5 1 0 1
 16 0 0 0 0    .   . 1 0 1
 17 0 1 0 0   20   1 1 1 0
 18 1 1 0 0    .   . 0 0 0
 19 1 0 0 0    .   . 0 0 1
 20 0 1 0 0    5   2 1 0 0
 21 0 0 1 0   20  10 1 1 1
 22 0 1 0 0  200   1 1 1 0
 23 1 0 0 0    .   . 1 0 0
 24 0 1 0 0  250  30 1 1 1
 25 0 1 0 0   30  10 1 1 1
 26 1 1 0 0    .   . 0 0 0
 27 0 0 1 0   20   2 1 0 0
 28 0 1 0 0   30   6 1 0 0
 29 1 0 0 0    .   . 1 0 1
 30 1 0 0 0    .   . 1 1 0
 31 0 0 0 0  100   4 1 1 1
 32 0 0 0 0    5   2 1 1 0
 33 0 0 0 0    .   . 0 1 0
 34 0 1 0 0    2   2 1 0 0
 35 1 0 0 0    .   . 1 0 1
 36 0 1 0 0  100   1 1 0 0
 37 0 1 0 0    2   3 1 1 1
 38 1 1 0 0    .   . 1 1 1
 39 1 0 0 0    .   . 0 1 1
 40 0 0 1 0  100  10 1 1 1
 41 0 1 0 1    5   2 1 1 1
 42 1 0 0 0    .   . 1 1 1
 43 1 0 0 0    .   . 1 0 1
 44 0 0 0 0    .   . 1 1 1
 45 0 0 0 0   15   5 0 1 1
 46 1 0 0 0    .   . 0 0 1
 47 0 1 0 0   50   2 0 0 0
 48 0 0 0 0    .   . 0 0 1
 49 0 0 0 0  200   3 1 1 1
 50 1 0 0 0    .   . 1 1 0
 51 0 1 0 0  300  10 1 1 0
 52 1 0 0 0    .   . 1 0 1
 53 1 0 0 0    .   . 1 0 1
 54 0 0 0 0    .   . 1 0 1
 55 1 0 0 0    .   . 1 1 1
 56 1 0 0 1    .   . 1 1 0
 57 0 0 0 1   15 200 1 0 0
 58 1 1 0 0    .   . 0 0 0
 59 1 0 0 0    .   . 1 1 0
 60 0 1 0 0    2   4 1 0 1
 61 0 0 1 0   10   2 1 0 0
 62 1 1 1 0    .   . 1 1 1
 63 1 1 0 0   10  10 0 0 0
 64 0 1 0 0  100   2 1 1 1
 65 1 1 0 0    .   . 1 1 0
 66 0 1 0 0  100   5 1 1 1
 67 1 0 0 0    .   . 1 0 0
 68 1 0 0 0    .   . 1 0 0
 69 1 0 0 1    .   . 1 1 0
 70 0 1 0 0   20   2 1 1 1
 71 1 0 0 0    .   . 1 1 0
 72 0 0 0 1    5   2 1 0 0
 73 1 0 0 0    .   . 1 0 0
 74 0 1 0 0   50  10 1 1 1
 75 0 0 0 0    1   1 0 0 1
 76 0 1 0 0  100   2 1 0 0
 77 1 1 0 0    .   . 1 1 1
 78 0 0 0 0   20   2 0 0 1
 79 1 0 0 0    .   . 1 1 1
 80 0 0 1 0   30   5 0 0 1
 81 1 0 0 0    .   . 1 0 0
 82 1 0 0 0    .   . 1 1 1
 83 0 0 0 0  300  40 1 1 1
 84 1 0 0 0    .   . 1 0 1
 85 1 0 0 0    .   . 1 1 1
 86 0 1 0 0   20  15 1 0 0
 87 1 0 0 0    .   . 0 1 0
 88 0 0 0 0  100  15 1 0 1
 89 1 1 0 0    .   . 1 1 1
 90 1 0 0 0    .   . 1 0 1
 91 1 1 0 0    .   . 0 1 1
 92 1 1 0 0    .   . 1 1 1
 93 1 1 0 0    .   . 0 0 0
 94 0 0 0 0    .   . 1 1 0
 95 1 0 0 0    .   . 1 0 1
 96 1 1 0 0    .   . 1 1 0
 97 1 1 0 0    .   . 1 1 0
 98 1 1 0 0    .   . 1 1 1
 99 1 1 0 0    .   . 1 1 1
100 1 0 0 0    .   . 1 1 1
end
label values typewatersources_1 typewatersources_1
label def typewatersources_1 0 "No", modify
label def typewatersources_1 1 "Yes", modify
label values typewatersources_2 typewatersources_2
label def typewatersources_2 0 "No", modify
label def typewatersources_2 1 "Yes", modify
label values typewatersources_3 typewatersources_3
label def typewatersources_3 0 "No", modify
label def typewatersources_3 1 "Yes", modify
label values typewatersources_4 typewatersources_4
label def typewatersources_4 0 "No", modify
label def typewatersources_4 1 "Yes", modify
label values waterusage_1_1 waterusage_1_1
label def waterusage_1_1 0 "No", modify
label def waterusage_1_1 1 "Yes", modify
label values waterusage_2_1 waterusage_2_1
label def waterusage_2_1 0 "No", modify
label def waterusage_2_1 1 "Yes", modify
label values waterusage_3_1 waterusage_3_1
label def waterusage_3_1 0 "No", modify
label def waterusage_3_1 1 "Yes", modify

Hi there,
I am working on reshaping wide to long format and having different levels for different variables. The variable typewatersources has 18 levels, and waterusage_1_, waterusage_2_, and waterusage_3_ have 7 levels, while all other variables have three. I want to reshape the wide-form data like the image attached here. Can I do the reshape with all the variables, or do I need to do it separately for different levels and then merge them?

reshape long typewatersources_ distancetosource_ Roundtriptime_ litrescollected_ paymentperlitre_ queuingtime_ waterusage_1_, waterusage_2_ and waterusage_3_, i(uid) j(sources).


I am mainly having trouble understanding the water usage variables. Please note that each user chooses three water sources from the 18 types and can use water from a given source for up to seven purposes (drinking, cooking, hygiene, etc.). Please help me with the correct commands to make the reshape look like the image. Thanks! I appreciate your help!


Sincerely
Nabittu

[Andrew Musau]

It appears that you just want to reshape long "typewatersources". Then you need to rename the usage variables to reflect the different categories.

rename typewatersources_* selected*
reshape long selected, i(uid) j(source)

Res.:

. l in 1/20, sepby(uid)

     +--------------------------------------------------------------------------------+
     | uid   source   selected   distan~1   Roundt~1   wate~1_1   wate~2_1   wate~3_1 |
     |--------------------------------------------------------------------------------|
  1. |   1        1        Yes          .          .         No        Yes        Yes |
  2. |   1        2        Yes          .          .         No        Yes        Yes |
  3. |   1        3         No          .          .         No        Yes        Yes |
  4. |   1        4         No          .          .         No        Yes        Yes |
     |--------------------------------------------------------------------------------|
  5. |   2        1         No          .          .        Yes        Yes        Yes |
  6. |   2        2         No          .          .        Yes        Yes        Yes |
  7. |   2        3         No          .          .        Yes        Yes        Yes |
  8. |   2        4         No          .          .        Yes        Yes        Yes |
     |--------------------------------------------------------------------------------|
  9. |   3        1        Yes          .          .        Yes        Yes        Yes |
 10. |   3        2         No          .          .        Yes        Yes        Yes |
 11. |   3        3         No          .          .        Yes        Yes        Yes |
 12. |   3        4         No          .          .        Yes        Yes        Yes |
     |--------------------------------------------------------------------------------|
 13. |   4        1         No         20          5         No        Yes        Yes |
 14. |   4        2        Yes         20          5         No        Yes        Yes |
 15. |   4        3        Yes         20          5         No        Yes        Yes |
 16. |   4        4         No         20          5         No        Yes        Yes |
     |--------------------------------------------------------------------------------|
 17. |   5        1         No          .          .         No        Yes        Yes |
 18. |   5        2         No          .          .         No        Yes        Yes |
 19. |   5        3         No          .          .         No        Yes        Yes |
 20. |   5        4         No          .          .         No        Yes        Yes |
     +--------------------------------------------------------------------------------+

[Nabittun Nahar]

Here's a clearer version for seeking help:

---

Subject: Help with Reshaping Water Usage Variables

Hi Andrew Musau,

Thank you for your valuable advice on my previous post—it helped clarify many things. I’m reaching out again with a question regarding water usage variables.

I have 21 water usage variables from three selected sources, with seven usage activities recorded from each source. The variables are structured as waterusage_1_i, waterusage_2_i, and waterusage_3_i, where i ranges from 1 to 7, representing different water usage activities (like cooking, drinking, etc.).

For example, uid 1 has selected water from sources 1, 2, and 14. Source 1 is used for waterusage_1_1 = cooking and waterusage_1_3 = drinking, but all other usage variables for this source are 0, as this particular source is only used for cooking and drinking by uid 1.

I need help using the reshape long command to transform these variables into a long format with a new variable (perhaps usage) that aligns the water usage activities with each selected source.

I’ve attached some variables for your convenience.

Thank you in advance for your guidance!

* Example generated by -dataex-. For more info, type help dataex
clear
input double(waterusage_1_1 waterusage_1_2 waterusage_1_3 waterusage_1_4 waterusage_1_5 waterusage_1_6 waterusage_1_88 waterusage_2_1 waterusage_2_2 waterusage_2_3 waterusage_2_4 waterusage_2_5 waterusage_2_6 waterusage_2_88 waterusage_3_1)
0 1 1 . . . . 1 1 1 . . . . 1
1 1 1 . . . . 1 1 1 . . . . 1
1 1 1 . . . . 1 1 1 . . . . 1
0 0 1 . . . . 1 1 1 . . . . 1
0 1 1 . . . . 1 1 1 . . . . 1
0 0 1 . . . . 1 1 1 . . . . 1
1 1 1 . . . . 1 1 1 . . . . 1
1 0 1 . . . . 1 1 1 . . . . 1
1 1 1 . . . . 1 1 1 . . . . 1
1 0 1 . . . . 1 0 1 . . . . 1
1 1 1 . . . . 1 1 1 . . . . 0
0 1 1 . . . . 0 1 1 . . . . 1
0 1 1 . . . . 0 0 0 1 . . . 1
0 0 1 . . . . 0 0 1 . . . . 1
1 1 1 . . . . 0 0 1 . . . . 1
1 0 1 . . . . 0 1 1 . . . . 1
1 1 1 . . . . 1 1 1 . . . . 0
0 0 0 . . . . 0 0 0 1 . . . 0
0 1 1 . . . . 0 1 1 . . . . 1
1 1 1 . . . . 0 0 1 . . . . 0
end
label values waterusage_1_1 waterusage_1_1
label def waterusage_1_1 0 "No", modify
label def waterusage_1_1 1 "Yes", modify
label values waterusage_1_2 waterusage_1_2
label def waterusage_1_2 0 "No", modify
label def waterusage_1_2 1 "Yes", modify
label values waterusage_1_3 waterusage_1_3
label def waterusage_1_3 0 "No", modify
label def waterusage_1_3 1 "Yes", modify
label values waterusage_1_5 waterusage_1_5
label values waterusage_1_6 waterusage_1_6
label values waterusage_1_88 waterusage_1_88
label values waterusage_2_1 waterusage_2_1
label def waterusage_2_1 0 "No", modify
label def waterusage_2_1 1 "Yes", modify
label values waterusage_2_2 waterusage_2_2
label def waterusage_2_2 0 "No", modify
label def waterusage_2_2 1 "Yes", modify
label values waterusage_2_3 waterusage_2_3
label def waterusage_2_3 0 "No", modify
label def waterusage_2_3 1 "Yes", modify
label values waterusage_2_4 waterusage_2_4
label def waterusage_2_4 1 "Yes", modify
label values waterusage_2_5 waterusage_2_5
label values waterusage_2_6 waterusage_2_6
label values waterusage_2_88 waterusage_2_88
label values waterusage_3_1 waterusage_3_1
label def waterusage_3_1 0 "No", modify
label def waterusage_3_1 1 "Yes", modify

[Andrew Musau]

* Example generated by -dataex-. For more info, type help dataex
clear
input float uid double(typewatersources_1 typewatersources_2 typewatersources_3 typewatersources_4 distancetosource_1 Roundtriptime_1 waterusage_1_1 waterusage_2_1 waterusage_3_1)
  1 1 1 0 0    .   . 0 1 1
  2 0 0 0 0    .   . 1 1 1
  3 1 0 0 0    .   . 1 1 1
  4 0 1 1 0   20   5 0 1 1
  5 0 0 0 0    .   . 0 1 1
  6 0 0 1 0   10   1 0 1 1
  7 0 0 0 0 1500  60 1 1 1
  8 1 0 0 0    .   . 1 1 1
  9 1 1 0 0   50   3 1 1 1
 10 0 0 0 1    .   . 1 1 1
 11 0 0 0 0    5   1 1 1 0
 12 1 0 0 0    .   . 0 0 1
 13 1 1 0 0    .   . 0 0 1
 14 0 0 1 0    5   3 0 0 1
 15 0 1 0 0    2   5 1 0 1
 16 0 0 0 0    .   . 1 0 1
 17 0 1 0 0   20   1 1 1 0
 18 1 1 0 0    .   . 0 0 0
 19 1 0 0 0    .   . 0 0 1
 20 0 1 0 0    5   2 1 0 0
 21 0 0 1 0   20  10 1 1 1
 22 0 1 0 0  200   1 1 1 0
 23 1 0 0 0    .   . 1 0 0
 24 0 1 0 0  250  30 1 1 1
 25 0 1 0 0   30  10 1 1 1
 26 1 1 0 0    .   . 0 0 0
 27 0 0 1 0   20   2 1 0 0
 28 0 1 0 0   30   6 1 0 0
 29 1 0 0 0    .   . 1 0 1
 30 1 0 0 0    .   . 1 1 0
 31 0 0 0 0  100   4 1 1 1
 32 0 0 0 0    5   2 1 1 0
 33 0 0 0 0    .   . 0 1 0
 34 0 1 0 0    2   2 1 0 0
 35 1 0 0 0    .   . 1 0 1
 36 0 1 0 0  100   1 1 0 0
 37 0 1 0 0    2   3 1 1 1
 38 1 1 0 0    .   . 1 1 1
 39 1 0 0 0    .   . 0 1 1
 40 0 0 1 0  100  10 1 1 1
 41 0 1 0 1    5   2 1 1 1
 42 1 0 0 0    .   . 1 1 1
 43 1 0 0 0    .   . 1 0 1
 44 0 0 0 0    .   . 1 1 1
 45 0 0 0 0   15   5 0 1 1
 46 1 0 0 0    .   . 0 0 1
 47 0 1 0 0   50   2 0 0 0
 48 0 0 0 0    .   . 0 0 1
 49 0 0 0 0  200   3 1 1 1
 50 1 0 0 0    .   . 1 1 0
 51 0 1 0 0  300  10 1 1 0
 52 1 0 0 0    .   . 1 0 1
 53 1 0 0 0    .   . 1 0 1
 54 0 0 0 0    .   . 1 0 1
 55 1 0 0 0    .   . 1 1 1
 56 1 0 0 1    .   . 1 1 0
 57 0 0 0 1   15 200 1 0 0
 58 1 1 0 0    .   . 0 0 0
 59 1 0 0 0    .   . 1 1 0
 60 0 1 0 0    2   4 1 0 1
 61 0 0 1 0   10   2 1 0 0
 62 1 1 1 0    .   . 1 1 1
 63 1 1 0 0   10  10 0 0 0
 64 0 1 0 0  100   2 1 1 1
 65 1 1 0 0    .   . 1 1 0
 66 0 1 0 0  100   5 1 1 1
 67 1 0 0 0    .   . 1 0 0
 68 1 0 0 0    .   . 1 0 0
 69 1 0 0 1    .   . 1 1 0
 70 0 1 0 0   20   2 1 1 1
 71 1 0 0 0    .   . 1 1 0
 72 0 0 0 1    5   2 1 0 0
 73 1 0 0 0    .   . 1 0 0
 74 0 1 0 0   50  10 1 1 1
 75 0 0 0 0    1   1 0 0 1
 76 0 1 0 0  100   2 1 0 0
 77 1 1 0 0    .   . 1 1 1
 78 0 0 0 0   20   2 0 0 1
 79 1 0 0 0    .   . 1 1 1
 80 0 0 1 0   30   5 0 0 1
 81 1 0 0 0    .   . 1 0 0
 82 1 0 0 0    .   . 1 1 1
 83 0 0 0 0  300  40 1 1 1
 84 1 0 0 0    .   . 1 0 1
 85 1 0 0 0    .   . 1 1 1
 86 0 1 0 0   20  15 1 0 0
 87 1 0 0 0    .   . 0 1 0
 88 0 0 0 0  100  15 1 0 1
 89 1 1 0 0    .   . 1 1 1
 90 1 0 0 0    .   . 1 0 1
 91 1 1 0 0    .   . 0 1 1
 92 1 1 0 0    .   . 1 1 1
 93 1 1 0 0    .   . 0 0 0
 94 0 0 0 0    .   . 1 1 0
 95 1 0 0 0    .   . 1 0 1
 96 1 1 0 0    .   . 1 1 0
 97 1 1 0 0    .   . 1 1 0
 98 1 1 0 0    .   . 1 1 1
 99 1 1 0 0    .   . 1 1 1
100 1 0 0 0    .   . 1 1 1
end
label values typewatersources_1 typewatersources_1
label def typewatersources_1 0 "No", modify
label def typewatersources_1 1 "Yes", modify
label values typewatersources_2 typewatersources_2
label def typewatersources_2 0 "No", modify
label def typewatersources_2 1 "Yes", modify
label values typewatersources_3 typewatersources_3
label def typewatersources_3 0 "No", modify
label def typewatersources_3 1 "Yes", modify
label values typewatersources_4 typewatersources_4
label def typewatersources_4 0 "No", modify
label def typewatersources_4 1 "Yes", modify
label values waterusage_1_1 waterusage_1_1
label def waterusage_1_1 0 "No", modify
label def waterusage_1_1 1 "Yes", modify
label values waterusage_2_1 waterusage_2_1
label def waterusage_2_1 0 "No", modify
label def waterusage_2_1 1 "Yes", modify
label values waterusage_3_1 waterusage_3_1
label def waterusage_3_1 0 "No", modify
label def waterusage_3_1 1 "Yes", modify

rename typewatersources_* selected*
reshape long selected, i(uid) j(source)
keep if selected
reshape long waterusage, i(uid source) j(which) string
keep if waterusage
drop waterusage selected
gen usage= "cooking" if which=="_1_1", after(which)
replace usage= "drinking" if which=="_3_1"

Res.:

. l in 1/30, sepby(uid source)

     +-------------------------------------------------------+
     | uid   source   which      usage   distan~1   Roundt~1 |
     |-------------------------------------------------------|
  1. |   1        1    _2_1                     .          . |
  2. |   1        1    _3_1   drinking          .          . |
     |-------------------------------------------------------|
  3. |   1        2    _2_1                     .          . |
  4. |   1        2    _3_1   drinking          .          . |
     |-------------------------------------------------------|
  5. |   3        1    _1_1    cooking          .          . |
  6. |   3        1    _2_1                     .          . |
  7. |   3        1    _3_1   drinking          .          . |
     |-------------------------------------------------------|
  8. |   4        2    _2_1                    20          5 |
  9. |   4        2    _3_1   drinking         20          5 |
     |-------------------------------------------------------|
 10. |   4        3    _2_1                    20          5 |
 11. |   4        3    _3_1   drinking         20          5 |
     |-------------------------------------------------------|
 12. |   6        3    _2_1                    10          1 |
 13. |   6        3    _3_1   drinking         10          1 |
     |-------------------------------------------------------|
 14. |   8        1    _1_1    cooking          .          . |
 15. |   8        1    _2_1                     .          . |
 16. |   8        1    _3_1   drinking          .          . |
     |-------------------------------------------------------|
 17. |   9        1    _1_1    cooking         50          3 |
 18. |   9        1    _2_1                    50          3 |
 19. |   9        1    _3_1   drinking         50          3 |
     |-------------------------------------------------------|
 20. |   9        2    _1_1    cooking         50          3 |
 21. |   9        2    _2_1                    50          3 |
 22. |   9        2    _3_1   drinking         50          3 |
     |-------------------------------------------------------|
 23. |  10        4    _1_1    cooking          .          . |
 24. |  10        4    _2_1                     .          . |
 25. |  10        4    _3_1   drinking          .          . |
     |-------------------------------------------------------|
 26. |  12        1    _3_1   drinking          .          . |
     |-------------------------------------------------------|
 27. |  13        1    _3_1   drinking          .          . |
     |-------------------------------------------------------|
 28. |  13        2    _3_1   drinking          .          . |
     |-------------------------------------------------------|
 29. |  14        3    _3_1   drinking          5          3 |
     |-------------------------------------------------------|
 30. |  15        2    _1_1    cooking          2          5 |
     +-------------------------------------------------------+

[Nabittun Nahar]

Thank you, Andrew, for helping me with my problem.