Linking family id with individual id

Hi,

I am new to kinsources but wanted to have the ego id, father id, mother id, and gender (male or female) linked. But when I open the datasets in excel the pages are separated by family id's and individual id's. Is there a way to link the two? Thanks for any help. Again, all I want is to open an excel sheet with the columns: ego id, father id, mother id and sex (male or female).

Robert

Hi,

Unfortunately, there is no automatically generated format which correspond to your need. You will need to do some manipulation of the files.

Here a solution using Libre Office Calc (but I guess, using MS Excel or Open Office you will need few adaptation of my solution):
0. Enable regular expression in Calc
0a. Go in the menu "Tools" > "Options" and then in "OpenOffice.org Calc" > "Calculate dialog"
0b. Check the option " Enable regular expression in formulas" if it is not already the case
(https://wiki.openoffice.org/w/images/1/1a/Regex_howto_2.png)

1. create a new sheet, where you will put the new formated data

2. copy and paste the column A ("Id") from the sheet "Individuals" in the column A of the new sheet. Thus the cell A1 contain "Id" and the cells A2, A3, etc. contains the Id of all the individuals

3. name your columns in the new sheet
3a. the column B will contain the Gender so write "Gender" in the cell B1
3b. the new sheet, the column C will contain the FatherId so write "FatherId" in the cell C1
3c. the new sheet, the column D will contain the MotherId so write "MotherId" in the cell D1

4. Get the Gender
4a.in the cell B2 write the following formula: =LOOKUP(A2;Individuals.A$2:A9999;Individuals.C$2:C9999) [if you are using a French version, the formula will be: =RECHERCHE(A2;Individuals.A$2:A9999;Individuals.C$2:C9999)]
4b. copy and paste it to all the cells of the column

5. Get the FatherId
5a.in the cell C2 write the following formula: =LOOKUP(CONCATENATE("^(.*;)?";A2;"(;.*)?$");Families.E$2:E$99999;Families.C$2:C$99999) [French version: =RECHERCHE(CONCATENER("^(.*;)?";A2;"(;.*)?$");Families.E$2:E$99999;Families.C$2:C$99999)]
5b. copy and paste it to all the cells of the column

6. Get the MotherId
6a.in the cell D2 write the following formla: =LOOKUP(CONCATENATE("^(.*;)?";A2;"(;.*)?$");Families.E$2:E$99999;Families.D$2:D$99999) [French version: =RECHERCHE(CONCATENER("^(.*;)?";A2;"(;.*)?$");Families.E$2:E$99999;Families.D$2:D$99999)]
6b. copy and paste it to all the cells of the column


Let me know if you reach your goal!


Anne GF

Hello,
I only see this topic now.

What Anne suggests is incredibly tedious -- especially considering that it is to produce what used to be Puck's basic format in xls or txt.

By chance, THERE IS STILL A MUCH EASIER WAY TO PRODUCE IT:

- Open your corpus in Puck
- Menu "File" > Export BAR

In the window that opens:
- Type in the name of the file you want to produce INCLUDING its wished EXTENSION (.ods or .xls or .txt)
- Make sure that the Format line below says "BAR Format Files"
(if not so: choose it in the unfolding menu ; there is no automatic adjustment to the extension contained in the file's name)

- Click "Export"

That's it!

I hope Robert Lynch hasn't fled...