Combinig parents and children infos in one row

[Giorgio Piccitto]

Dear all,
I am struggling with an operation of data managing.

I have a dataset with info at household level, and I would like to put in each row the information of one parent and all their children, such as:

family A, parent A, son 1, son 2, daughter 1
family A, parent B, son 1, son 2, daughter 1

Those are the data:

* Example generated by -dataex-. To install: ssc install dataex
clear
input long(id_famiglia id_unico apidp) byte relazione_ioaltro int isco88 float(figlio1_status figlio1_io)
68001363 68001367        .  . 111  . 0
68004083 68004087        .  . 522  . 0
68006123 68006127 68006131  2  -8  . 0
68006123 68006127 68006135  9  -8  . 0
68006123 68006127 68006139  9  -8  . 0
68006123 68006131 68006127  2   .  . 0
68006123 68006131 68006139 12   .  . 0
68006123 68006131 68006135 12   .  . 0
68006123 68006135 68006127  4  -8 -8 1
68006123 68006135 68006131  7  -8 -8 1
end
label values relazione_ioaltro a_relationship_dv
label def a_relationship_dv 2 "partner/cohabitee", modify
label def a_relationship_dv 4 "natural son/daughter", modify
label def a_relationship_dv 7 "stepson/stepdaughter", modify
label def a_relationship_dv 9 "natural parent", modify
label def a_relationship_dv 12 "step-parent", modify
label values isco88 a_jbisco88_cc
label def a_jbisco88_cc -8 "inapplicable", modify
label def a_jbisco88_cc 111 "Legislators", modify
label def a_jbisco88_cc 522 "Shop salespersons and demonstrators", modify

I tried some combination with the numerical counter, but nothing close to what I would like to obtain.

DO you have any clue?

Thanks, G.

[Liu Qiang]

you can use command: reshape and bysort familyid: egen

[Giorgio Piccitto]

Dear Liu,
what function of egen should I use?

Thanks a lot, G.

[Carole J. Wilson]

I've made some assumptions about your data and added a second family with two children to test.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long(id_famiglia id_unico apidp) byte relazione_ioaltro int isco88 float(figlio1_status figlio1_io)
68001363 68001367        .  . 111  . 0
68004083 68004087        .  . 522  . 0
68006123 68006127 68006131  2  -8  . 0
68006123 68006127 68006135  9  -8  . 0
68006123 68006127 68006139  9  -8  . 0
68006123 68006131 68006127  2   .  . 0
68006123 68006131 68006139 12   .  . 0
68006123 68006131 68006135 12   .  . 0
68006123 68006135 68006127  4  -8 -8 1
68006123 68006135 68006131  7  -8 -8 1
68006999 68006199 68006188  2  -8  . 0
68006999 68006199 68006177  9  -8  . 0
68006999 68006199 68006155  9  -8  . 0
68006999 68006188 68006199  2   .  . 0
68006999 68006188 68006155 12   .  . 0
68006999 68006188 68006188 12   .  . 0
68006999 68006177 68006199  4  -8 -8 1
68006999 68006177 68006188  7  -8 -8 1
68006999 68006166 68006199  7  -8 -8 1
68006999 68006166 68006188  4  -8 -8 1

end
label values relazione_ioaltro a_relationship_dv
label def a_relationship_dv 2 "partner/cohabitee", modify
label def a_relationship_dv 4 "natural son/daughter", modify
label def a_relationship_dv 7 "stepson/stepdaughter", modify
label def a_relationship_dv 9 "natural parent", modify
label def a_relationship_dv 12 "step-parent", modify
label values isco88 a_jbisco88_cc
label def a_jbisco88_cc -8 "inapplicable", modify
label def a_jbisco88_cc 111 "Legislators", modify
label def a_jbisco88_cc 522 "Shop salespersons and demonstrators", modify

gen long key=id_unico    //for parents, this contains the child id?
gen is_child=1 if inlist( relazione_ioaltro, 4, 7)

*make a child only dataset
preserve
keep if is_child==1
drop is_child key
sort id_famiglia id_unico apidp
bysort id_famiglia id_unico: gen child_n= _n==1
bysort id_famiglia : replace child_n=sum(child_n)
reshape wide id_unico relazione_ioaltro , i(apidp) j(child_n) //add any child specific vars you wan tto keep
gen long key=apidp     //contains the child id for children?
save child_only.dta, replace
restore

drop if is_child==1 //drop children from main dataset
drop is_child
merge m:1 key using child_only.dta, nogenerate noreport  //merge children & parents
list id_famiglia- relazione_ioaltro key- relazione_ioaltro2, sepby(id_famiglia)

[Giorgio Piccitto]

Dear Carole,
thanks for your answer.

I tried your procedure but Stata gives me an errore when doing the command -reshape-:

values of variable child_n not unique within apidp

Do you have any clue about why?

Thanks, G,

[Carole J. Wilson]

There may be duplicate entries of the same child for the same parent, but I'm not sure since I had to make some assumptions about the structure of the data. Perhaps add the following code (in blue) before the reshape command to look closely at your cases. These are the child cases sorted by parent ids. So child_n should not be repeated for the same apidp. Duplicate child_n within apida may be duplicate data, or it may indicate some structure that I did not anticipate (missing values on apidp?).

bysort id_famiglia : replace child_n=sum(child_n)
sort id_famiglia apidp child_n
list, sepby(apidp)
reshape wide id_unico relazione_ioaltro , i(apidp) j(child_n) //add any child specific vars you wan tto keep