I have a variable called "family" which includes details of family members all together.
From that variable I want to extract the name of the spouse in a separate column. The logic needed is to extract all the words after either "Wife:" or "Husband:" till before the "(".
In another column I want to extract the birth year of daughter from "family". The logic will be search for the immediate next "b." after "Daughter:" and then extract the next four characters after "b." (excluding blank spaces) into a separate column.
There could be more than one spouse and more than one daughter in some cases. In those cases I want for each spouse separate columns, i.e. Spouse 1, spouse 2. And for each daughter separate columns, i.e Daughter 1, daughter 2.
Have:
family
[ Wife: XXX XXXX (architect, m. 6-Oct-1924, d. 13-Jan-1949, 2 children) , Daughter: AAA (b. 1925) , Son: BBBB (b. 1928) , Wife: YYY YYYY (architect, m. 4-Oct-1952) ]
[ Wife: XXX1 XXXX1 (div., one son) , Son: AAA1, Wife: YYY1 YYYY1 (actress, m. 1986, sep. 2008, one daughter) , Daughter: BBBB1 ]
[ Father: XXX2 XXXX2 XXXXX2 , Wife: YYYY2 YYYY2(gymnast, one daughter) , Daughter: AAA2 ]
[ Father: XXXX3 XXXXX3 ("XX3", d. 1922) , Mother: ZZZ ZZZZZ (d. 1923) , Wife: YYYY3 YYY3 (m. 28-Jun-1926, three daughters) , Daughter: AAA , Daughter: BBB , Daughter: CCCCC]
[ Wife: YYYY4 (one daughter, one son) , Daughter: AAAA3 (ballerina, b. 1962) , Son: BBB2 (b. 1968) ]
[ Father: XXXX5 (lawyer/administrator) , Wife: (d. 1970, two daughters) ]
Want:
Family
Spousename 1
Spousename 2
Daughteryear 1
Daughteryear 2
[ Wife: XXX XXXX (architect, m. 6-Oct-1924, d. 13-Jan-1949, 2 children) , Daughter: AAA (b. 1925) , Son: BBBB (b. 1928) , Wife: YYY YYYY (architect, m. 4-Oct-1952) ]
XXX XXXX
YYY YYYY
1925
[ Wife: XXX1 XXXX1 (div., one son) , Son: AAA1, Wife: YYY1 YYYY1 (actress, m. 1986, sep. 2008, one daughter) , Daughter: BBBB1 ]
XXX1 XXXX1
YYY1 YYYY1
[ Father: XXX2 XXXX2 XXXXX2 , Wife: YYYY2 YYYY2(gymnast, one daughter) , Daughter: AAA2 ]
YYYY2 YYYY2
[ Father: XXXX3 XXXXX3 ("XX3", d. 1922) , Mother: ZZZ ZZZZZ (d. 1923) , Wife: YYYY3 YYY3 (m. 28-Jun-1926, three daughters) , Daughter: AAA , Daughter: BBB , Daughter: CCCCC]
YYYY3 YYY3
[ Wife: YYYY4 (one daughter, one son) , Daughter: AAAA3 (ballerina, b. 1962) , Son: BBB2 (b. 1968) ]
YYYY4
1962
[ Father: XXXX5 (lawyer/administrator) , Wife: (d. 1970, two daughters) ]
For privacy purpose the names have been replaced with random alphabets.
Can someone help me design the code for this? Thanks.
... View more