Forums » Kinsources Help & Support

Linking family id with individual id

Author Message
Robert Lynch
 Linking family id with individual id
09/01/2015 21:02  

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).

Anne Garcia-Fernandez
30/01/2015 12:19  

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 " Calc" > "Calculate dialog"
0b. Check the option " Enable regular expression in formulas" if it is not already the case

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 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 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 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
Isabelle Daillant
28/06/2015 16:50  
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.


- 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...

© Développement : DEVINSY
© Webdesign : Aurélie Martial

Collège de France
Université de Paris Sorbonne
Université Paris Ouest Nanterre La Défense
CSAC Kent University