Reshaping data

[Kathy Thomas]

Hello,

I am trying to reshape data from wide to long format.

We have 60 schools, each with up to 7 toilet blocks, each with up to 20 cubicles. I currently have a dataset with each observation being one of the 60 schools, and I want to get it to each row being a different cubicle.

When I reshape to get it to block level using

'reshape long blockuser blockdivided blockbin blockwater blockhandwash blocksoap blocktp numtoiletpaper numgirlscubicle numboyscubicle numsharedcubicle numcubicle cubicles_count, i(ID) j(block)'

it works, however for each block it gives me all the data on every block, as opposed to just the data for that block.

I want it so for block two the data for variables related to block 1 are missing.

I hope this makes sense

Thanks
Kathy

[Andrew Musau]

To increase your chances of receiving helpful replies, please provide a data example using the dataex command (see FAQ Advice #12 for details). You don't have to include all the variables or observations you have; just select a few that are relevant to the task. Your command should look something like this:

sort ID block
dataex blockuser blockdivided numsharedcubicle numcubicle cubicles_count ID block

After running the command, please copy and paste the output here.

[Kathy Thomas]

Thanks Andrew - here is the output below:

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(blockuser blockdivided numsharedcubicle numcubicle cubicles_count) str4 ID byte block
2 . .  8  8 "K-1"  1
1 . .  5  5 "K-1"  2
. . .  .  . "K-1"  3
. . .  .  . "K-1"  4
. . .  .  . "K-1"  5
. . .  .  . "K-1"  6
. . .  .  . "K-1"  7
1 . .  6  6 "K-10" 1
2 . .  2  2 "K-10" 2
2 . . 10 10 "K-10" 3
. . .  .  . "K-10" 4
. . .  .  . "K-10" 5
. . .  .  . "K-10" 6
. . .  .  . "K-10" 7
1 . .  5  5 "K-11" 1
1 . .  5  5 "K-11" 2
1 . .  5  5 "K-11" 3
2 . .  5  5 "K-11" 4
. . .  .  . "K-11" 5
. . .  .  . "K-11" 6
. . .  .  . "K-11" 7
1 . .  6  6 "K-12" 1
2 . .  4  4 "K-12" 2
. . .  .  . "K-12" 3
. . .  .  . "K-12" 4
. . .  .  . "K-12" 5
. . .  .  . "K-12" 6
. . .  .  . "K-12" 7
1 . .  5  5 "K-13" 1
2 . .  5  5 "K-13" 2
. . .  .  . "K-13" 3
. . .  .  . "K-13" 4
. . .  .  . "K-13" 5
. . .  .  . "K-13" 6
. . .  .  . "K-13" 7
3 1 0  6  6 "K-14" 1
. . .  .  . "K-14" 2
. . .  .  . "K-14" 3
. . .  .  . "K-14" 4
. . .  .  . "K-14" 5
. . .  .  . "K-14" 6
. . .  .  . "K-14" 7
3 1 0 12 12 "K-15" 1
. . .  .  . "K-15" 2
. . .  .  . "K-15" 3
. . .  .  . "K-15" 4
. . .  .  . "K-15" 5
. . .  .  . "K-15" 6
. . .  .  . "K-15" 7
1 . .  5  5 "K-17" 1
2 . .  6  6 "K-17" 2
2 . .  5  5 "K-17" 3
. . .  .  . "K-17" 4
. . .  .  . "K-17" 5
. . .  .  . "K-17" 6
. . .  .  . "K-17" 7
1 . .  6  6 "K-2"  1
2 . .  5  5 "K-2"  2
. . .  .  . "K-2"  3
. . .  .  . "K-2"  4
. . .  .  . "K-2"  5
. . .  .  . "K-2"  6
. . .  .  . "K-2"  7
1 . .  5  5 "K-3"  1
2 . .  5  5 "K-3"  2
. . .  .  . "K-3"  3
. . .  .  . "K-3"  4
. . .  .  . "K-3"  5
. . .  .  . "K-3"  6
. . .  .  . "K-3"  7
1 . .  3  3 "K-4"  1
2 . .  3  3 "K-4"  2
. . .  .  . "K-4"  3
. . .  .  . "K-4"  4
. . .  .  . "K-4"  5
. . .  .  . "K-4"  6
. . .  .  . "K-4"  7
3 1 0  6  6 "K-5"  1
. . .  .  . "K-5"  2
. . .  .  . "K-5"  3
. . .  .  . "K-5"  4
. . .  .  . "K-5"  5
. . .  .  . "K-5"  6
. . .  .  . "K-5"  7
1 . .  4  4 "K-6"  1
1 . .  5  5 "K-6"  2
1 . .  2  2 "K-6"  3
1 . .  6  6 "K-6"  4
1 . .  4  4 "K-6"  5
2 . .  5  5 "K-6"  6
2 . .  9  9 "K-6"  7
3 1 0 15 15 "K-7"  1
. . .  .  . "K-7"  2
. . .  .  . "K-7"  3
. . .  .  . "K-7"  4
. . .  .  . "K-7"  5
. . .  .  . "K-7"  6
. . .  .  . "K-7"  7
1 . .  3  3 "K-8"  1
2 . .  3  3 "K-8"  2
end
label values blockuser user
label def user 1 "Girls only", modify
label def user 2 "Boys only", modify
label def user 3 "Shared", modify
label values blockdivided yesno
label def yesno 1 "Yes", modify

[Andrew Musau]

Thanks. This data is already in long format. How did you want it to be? In case the issue is just missing values:

egen tag= rownonmiss(blockuser -cubicles_count)
keep if tag

Res.:

. l, sep(0)

     +-----------------------------------------------------------------------------+
     |  blockuser   blockd~d   numsha~e   numcub~e   cubicl~t     ID   block   tag |
     |-----------------------------------------------------------------------------|
  1. |  Boys only          .          .          8          8    K-1       1     3 |
  2. | Girls only          .          .          5          5    K-1       2     3 |
  3. | Girls only          .          .          6          6   K-10       1     3 |
  4. |  Boys only          .          .          2          2   K-10       2     3 |
  5. |  Boys only          .          .         10         10   K-10       3     3 |
  6. | Girls only          .          .          5          5   K-11       1     3 |
  7. | Girls only          .          .          5          5   K-11       2     3 |
  8. | Girls only          .          .          5          5   K-11       3     3 |
  9. |  Boys only          .          .          5          5   K-11       4     3 |
 10. | Girls only          .          .          6          6   K-12       1     3 |
 11. |  Boys only          .          .          4          4   K-12       2     3 |
 12. | Girls only          .          .          5          5   K-13       1     3 |
 13. |  Boys only          .          .          5          5   K-13       2     3 |
 14. |     Shared        Yes          0          6          6   K-14       1     5 |
 15. |     Shared        Yes          0         12         12   K-15       1     5 |
 16. | Girls only          .          .          5          5   K-17       1     3 |
 17. |  Boys only          .          .          6          6   K-17       2     3 |
 18. |  Boys only          .          .          5          5   K-17       3     3 |
 19. | Girls only          .          .          6          6    K-2       1     3 |
 20. |  Boys only          .          .          5          5    K-2       2     3 |
 21. | Girls only          .          .          5          5    K-3       1     3 |
 22. |  Boys only          .          .          5          5    K-3       2     3 |
 23. | Girls only          .          .          3          3    K-4       1     3 |
 24. |  Boys only          .          .          3          3    K-4       2     3 |
 25. |     Shared        Yes          0          6          6    K-5       1     5 |
 26. | Girls only          .          .          4          4    K-6       1     3 |
 27. | Girls only          .          .          5          5    K-6       2     3 |
 28. | Girls only          .          .          2          2    K-6       3     3 |
 29. | Girls only          .          .          6          6    K-6       4     3 |
 30. | Girls only          .          .          4          4    K-6       5     3 |
 31. |  Boys only          .          .          5          5    K-6       6     3 |
 32. |  Boys only          .          .          9          9    K-6       7     3 |
 33. |     Shared        Yes          0         15         15    K-7       1     5 |
 34. | Girls only          .          .          3          3    K-8       1     3 |
 35. |  Boys only          .          .          3          3    K-8       2     3 |
     +-----------------------------------------------------------------------------+

.