Announcement

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

  • Manipulating a dataset

    Dear all,
    I have the following dataset for about 600 household:
    Key Village Nursery hh_address parent_interviewed mobile number Parent name age_parent hh_members name_1 hh_gender_1 hh_relationship_1 age_calc_1 name_2 hh_gender_2 hh_relationship_2 age_calc_2 name_3 hh_gender_3 hh_relationship_3 age_calc_3 name_4 hh_gender_4 hh_relationship_4 age_calc_4
    1248674fh49bg845624120 Beba Ndaa 10 st. sham el zein Mother 999 Amira
    24
    4 Mohsen
    Male
    Husband/wife 30 Asmaa
    Female son/daughter
    15 Omar Male son/daughter 4 Mai Female Mother/Father 65
    3545674groxzbg84149789 Beba Shenwya the ghalfia street behind the bakery Mother 12193719371 Samia 32 2 Sayed Male Husband/wife 36
    Zaki Male son/daughter 3
    1145674bwbzng84567904 Bndr Om el Kora Al amal street Husband 18369458264 Omar 35 3 Saffa Female Husband/wife 27 Salma Male son/daughter 2.5 Amira Female son/daughter 0.5

    I would to manipulate the data to take the following shape:
    Key Village Nursery hh_address Parent_interviewed Mobile number Parent name age_parent hh_members name gender hh_relationship age_calc
    1248674fh49bg845624120 Beba Ndaa 10 st. sham el zein Mother 999 Amira 24 4 Mohsen Male Husband/wife 30
    1248674fh49bg845624120 Beba Ndaa 10 st. sham el zein Mother 999 Amira 24 4 Asmaa Female son/daughter 15
    1248674fh49bg845624120 Beba Ndaa 10 st. sham el zein Mother 999 Amira 24 4 Omar Male son/daughter 4
    1248674fh49bg845624120 Beba Ndaa 10 st. sham el zein Mother 999 Amira 24 4 Mai Female Mother/Father 65
    3545674groxzbg84149789 Beba Shenwya the ghalfia street behind the bakery Mother 12193719371 Samia 32 2 Sayed Male Husband/wife 36
    3545674groxzbg84149789 Beba Shenwya the ghalfia street behind the bakery Mother 12193719371 Samia 32 2 Zaki Male son/daughter 3
    1145674bwbzng84567904 Bndr Om el Kora Al amal street husband 1836945826 Omar 35 3 saffa Female Husband/wife 27
    1145674bwbzng84567904 Bndr Om el Kora Al amal street husband 1836945826 Omar 35 3 Salma Male Son / daughter 2.5
    1145674bwbzng84567904 Bndr Om el Kora Al amal street husband 1836945826 Omar 35 3 Amira Female son/daughter 0.5
    I am not sure how I could transform the data to take this shape and would greatly appreciate your help on this. Also please note that some households have up to 13 members within it.
    Thank you in advance.
    Last edited by Tina Samuel; 17 Jul 2023, 09:53.

  • #2
    Code:
    forv i = 1/4 {
        preserve
        keep key village nursery hh_address parent_interviewed mobilenumber parentname age_parent hh_members name_`i' hh_gender_`i' hh_relationship_`i' age_calc_`i'
        ren name_`i' name
        ren hh_gender_`i' gender
        ren hh_relationship_`i' hh_relationship
        ren age_calc_`i' age_calc
        save round`i', replace
        restore
    }
    
    use round1, clear
    forv i = 2/4 {
        append using round`i'
        drop if name==""
    }
    sort key

    Comment


    • #3
      The code in #2 will get you a separate file for all the *_1 variables, another for the *_2 variables, etc. and then puts them all together at the end. If you would like everything in a single data set, in one step, -reshape- is the way to go.
      Code:
      ds *_1
      local stubs  `r(varlist)'
      local stubs: subinstr local stubs "_1" "", all
      
      reshape long `stubs', i(key) j(member_num) string
      destring member_num, ignore("_")
      In the future, when showing data examples, please use the -dataex- command to do so. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

      Comment


      • #4
        Thank you, that worked perfectly. Also thank you for sharing the -dataex- tip with me, it would be easier to work with.

        Comment

        Working...
        X