Cet article présente un script qui a été utilisé pour identifier des familles à partir d’une base de donnée incluant le nom de 6116 victimes de la Shoah en Italie ainsi que, pour la plupart, le nom de leur mère, de leur père et de leur époux ou épouse.
Pour chaque combinaison possible entre deux de ces individus, le script compare les noms de leurs pères, mères, et époux ou épouses, et assigne un identifiant familial aux individus qui partagent les mêmes parents (frères et sœurs), sont mariés l’un à l’autre (mari et femme), ou pour lesquels le mari ou la femme d’un de leurs parents correspond à leur second parent (parent et enfants). dans chacun de ces cas, si l’un des deux individus à déjà un identifiant familial, ce même identifiant est attribué à l’autre individu. Dans le cas d’une correspondance partielle, par exemple si l’épouse d’un individu à un nom différent du nom de l’épouse de son époux (qui devrait être elle-même), ou si un nom est manquant, le script retourne l’incohérence dans le fichiers de log comme erreur possible. Le script créé également un tableau des relations entre tout les individus de la base de donnée.
Le script est écrit en langage python et a été conçu pour être utilisé avec le logiciel ESRI ArcCatalog. Cependant, une utilisation en dehors de ESRI ArcCatalog est possible simplement en modifiant les premières lignes de codes.
Ce script est basé sur le travail de Ryan Schuerman, actuellement doctorant à Texas State University, que je tiens à remercier pour m’avoir fourni la première version de ce code.
#**************************************************************************************************************
# python 2.6.5 program to convert csv data concerning people and their relations, build a social network table, and assign an family Id number to individuals.
# based on a script by Ryan Schuermann - rs1571[at]txstate[dot]edu - May-Dec 2012
# modified by Maël Le Noc - mael[dot]lenoc[at]txstate[dot]edu - Spring 2015
#
# !!!!!!!! WARNING !!!!!!!
# Your CSV file that you Save As from Excel or generate from some other source
# MUST BE IN THE EXACT FORMAT AS BELOW. I have provided an example of first line
# and first record (1 to 2 lines) for the file.
#
#Input file 1: personal information : Saved As CSV from Excel : (data\social_network_data.csv)
#ID,LAST NAME,FIRST NAME,BIRTHDAY,FATHER,MOTHER,SPOUSE
#I109,ALTMANN,FERDINANDO,1904.10.05,GUGLIELMO,SCHMIER GISELLA,HERSKOVITZ MARGHERITA (dep.)
#
# program details:
#
# creates 2D arrays concerning correlations between people. Array is [# people] by [# people]
# creates family ID number for each family (siblings, parents, children, parents' siblings,, parents' parents etc), and assign the appropriate family ID to each individual in a table.
#
#*************************************************************************************************************
import os, datetime, arcpy
#from operator import itemgetter
from numpy import *
infile1 = open(arcpy.GetParameterAsText(0),"r").readlines() # data input csv
outdir = arcpy.GetParameterAsText(1) # output folder/dir
outdir2 = arcpy.GetParameterAsText(3) # output folder/dir
#-------------------------------------------------------------------------------------------------------------------------------
# process output file name: ensure they will be txt files
#-------------------------------------------------------------------------------------------------------------------------------
rfile = root = ext = ""
rfile = arcpy.GetParameterAsText(2) # output filename txt
if ("." in rfile):
(root,ext) = rfile.split(".")
else:
root = rfile; ext = ""
if (ext != "txt"):
rfile = root + ".txt"
outfile1= open(os.path.join(outdir,rfile),"w")
rfile2 = root2 = ext2 = ""
rfile2 = arcpy.GetParameterAsText(4) # output filename txt
if ("." in rfile):
(root2,ext2) = rfile2.split(".")
else:
root2 = rfile2; ext2 = ""
if (ext2 != "txt"):
rfile2 = root2 + ".txt"
outfile2= open(os.path.join(outdir2,rfile2),"w")
del rfile; del root; del ext
del rfile2; del root2; del ext2
#-------------------------------------------------------------------------------------------------------------------------------
# declair variables
#-------------------------------------------------------------------------------------------------------------------------------
meValue = 122# self
siValue = 1 # sibling
faValue = 2 # father
moValue = 3 # mother
spValue = 4 # spouse
chValue = 5 # child
gpValue = 6 # grandparent
gcValue = 7 # grandchild
auValue = 8 # aunt/uncle
nnValue = 9 # neice/nephew
coValue = 10# cousin
# assign different relationship values for blood vs married aunts/uncles?
# grandparent's siblings?
# second, third cousins??
timeSuffix = datetime.datetime.now().strftime("_%Y_%m_%d__%H_%M_%S")
arcpy.env.overwriteOutput = True
idArray = []
oidArray = []
num_records = len(infile1) - 1 # -1 to account for header
relArray = zeros((num_records,num_records))
nineArray = zeros((num_records,1))
nineArray.fill(9999999999)
famArray = range(num_records)
famIDArray = range(num_records)
for y in range(num_records):
famIDArray[y] = nineArray[y][0]
famArray[y] = nineArray[y][0]
outstring = "Initialized 2D array of %d records..." % num_records
arcpy.AddMessage(outstring)
del outstring;
#-------------------------------------------------------------------------------------------------------------------------------
# build dictionary of IDs name, birthday, mother and father's name for quick data retrieval, and makes it easier to read
#-------------------------------------------------------------------------------------------------------------------------------
pInfo = {}
count = 0
for line in infile1:
w = line.split(",")
try:
w[6] = w[6].rstrip("\n")
w[6] = w[6].rstrip(" ")
except:
w[6] = w[6]
#rough check to ensure we are on a personal line and not the header
if not('ID' in w[0]):
count += 1
# build oid array forprocessing
oidArray.append(w[0])
#ensure ID is length 5, pad with zeros after the 'I' and build array, this is for output formatting
newID = w[0]
addZeros = 5 - len(w[0])
if (addZeros > 0):
newID = w[0][0]
for x in range (0,addZeros):
newID += "0"
newID += w[0][(0-(len(w[0])-1)):]
idArray.append(newID)
del addZeros;
#store: [id:name,id:bday,id:father,id:mother,id:spouse,id:spouse_dep]
pInfo[w[0]+"name"] = w[1] + " " + w[2]
if (('nessuna' in w[3].lower()) or ('sconosciuto' in w[3].lower()) or ('infante' in w[3].lower())):
pInfo[w[0]+"bday"] = "0"
else:
pInfo[w[0]+"bday"] = w[3]
if ('nessuna' in w[4].lower()):
pInfo[w[0]+"father"] = ""
else:
pInfo[w[0]+"father"] = w[1] + " " + w[4]
# mother has maiden and first name
if ('nessuna' in w[5].lower()):
pInfo[w[0]+"mother"] = ""
else:
pInfo[w[0]+"mother"] = w[5]
# spouse has last and first name, plus deport text ...or one of three messages equalling no spouse/unknown
if (('17' in w[6]) or ('nessuna' in w[6].lower()) or ('coniugato' in w[6].lower())):
pInfo[w[0]+"spouse"] = ""
pInfo[w[0]+"spouse_dep"] = ""
else:
if ('(dep.)' in w[6]):
pInfo[w[0]+"spouse_dep"] = "Yes"
try:
w[6] = w[6].replace("(dep.)","")
w[6] = w[6].rstrip(" ")
except:
w[6] = w[6]
else:
pInfo[w[0]+"spouse_dep"] = "No"
pInfo[w[0]+"spouse"] = w[6]
# DEBUG
#arcpy.AddMessage(line)
#outstring = "%d)%s name[%s] bday[%s] father[%s] mother[%s] spouse[%s] dep[%s]\n" % (count,w[0],pInfo[w[0]+"name"],pInfo[w[0]+"bday"],pInfo[w[0]+"father"],pInfo[w[0]+"mother"],pInfo[w[0]+"spouse"],pInfo[w[0]+"spouse_dep"])
#arcpy.AddMessage(outstring)
del line; del w
outstring = "Done with building personal dictionary of %d records..." % count
arcpy.AddMessage(outstring)
del outstring
#-------------------------------------------------------------------------------------------------------------------------------
# loop through records and determine matches
#-------------------------------------------------------------------------------------------------------------------------------
rowIndex = 0
for rows in infile1:
row = rows.split(",")
# ensure we are not on the header row and proceed
if not('ID' in row[0]):
# build the first row in the output file
outfile1.write(idArray[rowIndex]+",")
# loop through file again to look for matches
potFather = (-1,-1,"","","")
potMother = (-1,-1,"","","")
potSpouse = (-1,-1,"","","")
colIndex = 0
for cols in infile1:
col = cols.split(",")
if not('ID' in col[0]):
#-----------------------------------------------------------
# found self/self
#-----------------------------------------------------------
if (row[0] == col[0]):
#on same record, do nothing
relArray[rowIndex][colIndex] = meValue
# DEBUG
#outstring = "Found Self: [%d] [%d]" % (rowIndex,colIndex)
#arcpy.AddMessage(outstring)
#del outstring
#-----------------------------------------------------------
# SIBLING: same last name, father, mother
#-----------------------------------------------------------
elif (row[1] == col[1]) and (pInfo[row[0]+"father"] == pInfo[col[0]+"father"]) and (pInfo[row[0]+"mother"] == pInfo[col[0]+"mother"]):
# found sibling
relArray[rowIndex][colIndex] = siValue
relArray[colIndex][rowIndex] = siValue
if famArray[rowIndex] == 9999999999:
famArray[rowIndex] = rowIndex
else:
trans = famArray[rowIndex]
for x in range (0,num_records):
if famArray[x] == trans:
famArray[x] = rowIndex
if famArray[colIndex] == 9999999999:
famArray[colIndex] = rowIndex
else:
trans = famArray[colIndex]
for x in range (0,num_records):
if famArray[x] == trans:
famArray[x] = rowIndex
#outstring = "Found sibling at [%d][%d] !" % (rowIndex,colIndex)
#arcpy.AddMessage(outstring)
#del outstring
#-----------------------------------------------------------
# FATHER: father's spouse is same as mother and father is older than child
#-----------------------------------------------------------
elif (pInfo[row[0]+"father"] == pInfo[col[0]+"name"]) and (int(pInfo[row[0]+"bday"][:4]) > int(pInfo[col[0]+"bday"][:4])+13):
# found potential father
# verify that the father's spouse is row's mother, this eliminates many same name father occurances
# DEBUG
# arcpy.AddMessage("Father!")
if (pInfo[row[0]+"mother"] == pInfo[col[0]+"spouse"]):
if (potFather[0] == -1):
potFather = (rowIndex,colIndex,col[0],col[3],pInfo[col[0]+"name"])
# DEBUG
# arcpy.AddMessage("Verified!!!!!!!!!!!!!")
else:
# found more than one possible father
# to do: process
outstring = "Serious Error: Found Multiple Fathers for:(%s %s) Prev:%s %s, Current:%s %s" % (row[0],pInfo[row[0]+"name"],potFather[2],potFather[4],col[0],pInfo[col[0]+"name"])
arcpy.AddMessage(outstring)
del outstring
else:
outstring = "Possible Issue: Mother =/= Spouse for:(%s %s) Mother: %s, Father's Spouse:%s" % (row[0],pInfo[row[0]+"name"],pInfo[row[0]+"mother"],pInfo[col[0]+"spouse"])
arcpy.AddMessage(outstring)
del outstring
#-----------------------------------------------------------
# MOTHER: Mother's spouse is same as father, and mother is older than child
#-----------------------------------------------------------
elif (pInfo[row[0]+"mother"] == pInfo[col[0]+"name"])and (int(pInfo[row[0]+"bday"][:4]) > int(pInfo[col[0]+"bday"][:4])+13):
# found potential mother
# verify that the mother's spouse is row's father, this eliminates many same name mother occurances
# DEBUG
# arcpy.AddMessage("Mother!")
if (pInfo[row[0]+"father"] == pInfo[col[0]+"spouse"]):
if (potMother[0] == -1):
potMother = (rowIndex,colIndex,col[0],col[3],pInfo[col[0]+"name"])
# DEBUG
# arcpy.AddMessage("Verified!!!!!!!!!!!!!")
else:
# found more than one possible mother
# to do: process
outstring = "Serious Error: Found Multiple Mothers for:(%s %s) Prev:%s %s, Current:%s %s" % (row[0],pInfo[row[0]+"name"],potMother[2],potMother[4],col[0],pInfo[col[0]+"name"])
arcpy.AddMessage(outstring)
del outstring
else:
outstring = "Possible Issue: Father =/= Spouse for:(%s %s) Father: %s, Mother's Spouse:%s" % (row[0],pInfo[row[0]+"name"],pInfo[row[0]+"father"],pInfo[col[0]+"spouse"])
arcpy.AddMessage(outstring)
del outstring
#-----------------------------------------------------------
# SPOUSE: Spouse's spouse is same
#-----------------------------------------------------------
elif (pInfo[row[0]+"spouse"] == pInfo[col[0]+"name"]):
# found potential spouse
# first check to make sure jane doe is married to john smith AND john smith is married to jane doe
# backcheck spouse's spouse is the person we are on
# DEBUG
# arcpy.AddMessage("Spouse!")
if (pInfo[row[0]+"name"] == pInfo[col[0]+"spouse"]):
# we have a spouse/spouse match
# check to see if there has not been a prev match, it is possible that there are multiple couples with both same names
if (potSpouse[0] == -1):
potSpouse = (rowIndex,colIndex,col[0],col[3],pInfo[col[0]+"name"])
# DEBUG
# arcpy.AddMessage("Verified!!!!!!!!!!!!!")
else:
# found more than one possible spouse
# to do: analyze potSpouse and col's birthdays and choose closest one to row's birthday
outstring = "Serious Error: Found Multiple Spouse for:(%s %s) Prev:%s %s, Current:%s %s" % (row[0],pInfo[row[0]+"name"],potSpouse[2],potSpouse[4],col[0],pInfo[col[0]+"name"])
arcpy.AddMessage(outstring)
del outstring
else:
outstring = "Possible Issue: Not same Spouse for:(%s %s) Spouse: %s, Spouse's Spouse:%s" % (row[0],pInfo[row[0]+"name"],pInfo[row[0]+"spouse"],pInfo[col[0]+"spouse"])
arcpy.AddMessage(outstring)
del outstring
#-----------------------------------------------------------
# increment column loop counters etc
#-----------------------------------------------------------
colIndex += 1
#
# End column loop
#
# hopfully we have established correct father/mother/spouse relations (if any)
# set father/child reltionship values
if not(potFather[0] == -1):
relArray[potFather[0]][potFather[1]] = faValue
relArray[potFather[1]][potFather[0]] = chValue
if famArray[potFather[0]] == 9999999999:
famArray[potFather[0]] = rowIndex
else:
trans = famArray[potFather[0]]
for x in range (0,num_records):
if famArray[x] == trans:
famArray[x] = rowIndex
if famArray[potFather[1]] == 9999999999:
famArray[potFather[1]] = rowIndex
else:
trans = famArray[potFather[1]]
for x in range (0,num_records):
if famArray[x] == trans:
famArray[x] = rowIndex
# set mother/child reltionship values
if not(potMother[0] == -1):
relArray[potMother[0]][potMother[1]] = moValue
relArray[potMother[1]][potMother[0]] = chValue
if famArray[potMother[0]] == 9999999999:
famArray[potMother[0]] = rowIndex
else:
trans = famArray[potMother[0]]
for x in range (0,num_records):
if famArray[x] == trans:
famArray[x] = rowIndex
if famArray[potMother[1]] == 9999999999:
famArray[potMother[1]] = rowIndex
else:
trans = famArray[potMother[1]]
for x in range (0,num_records):
if famArray[x] == trans:
famArray[x] = rowIndex
# set spouse reltionship values
if not(potSpouse[0] == -1):
relArray[potSpouse[0]][potSpouse[1]] = spValue
relArray[potSpouse[1]][potSpouse[0]] = spValue
if famArray[potSpouse[0]] == 9999999999:
famArray[potSpouse[0]] = rowIndex
else:
trans = famArray[potSpouse[0]]
for x in range (0,num_records):
if famArray[x] == trans:
famArray[x] = rowIndex
if famArray[potSpouse[1]] == 9999999999:
famArray[potSpouse[1]] = rowIndex
else:
trans = famArray[potSpouse[1]]
for x in range (0,num_records):
if famArray[x] == trans:
famArray[x] = rowIndex
#-----------------------------------------------------------
# increment row loop counters etc
#-----------------------------------------------------------
rowIndex += 1
else:
# build the first row in the output file (header row), this should be the literal text "ID", or whatever the first value of the data input file's first (header) row is
outfile1.write(row[0]+",")
#
# End row loop
#
rowIndex = colIndex= 0
arcpy.AddMessage("Finished Assigning Nuclear Relationships...")
#-----------------------------------------------------------
# EXTENDED
#-----------------------------------------------------------
for y in range (0,num_records):
for x in range (0,num_records):
if ((relArray[y][x] == faValue) or (relArray[y][x] == moValue)):
# found father/mother at column [x],
# search row [x] for siblings (aunts/uncles - blood)
for z in range (0,num_records):
if (relArray[x][z] == siValue):
# found sibling at column [z], set values
relArray[y][z] = auValue
relArray[z][y] = nnValue
# DEBUG
outstring = "Found Aunt/Uncle(Blood) for (%s) Parent:%s Sibling:%s!!" % (pInfo[oidArray[y]+"name"], pInfo[oidArray[x]+"name"], pInfo[oidArray[z]+"name"])
arcpy.AddMessage(outstring)
del outstring
# found aunt/uncle(blood) at column [z]
# search row [z] for spouse (aunts/uncles - legal)
for k in range (0,num_records):
if (relArray[z][k] == spValue):
# found spouse at column [k], set values
relArray[z][k] = auValue
relArray[k][z] = nnValue
# DEBUG
outstring = "Found Aunt/Uncle(Legal) for (%s) Parent:%s Sibling:%s Spouse:%s!!" % (pInfo[oidArray[y]+"name"], pInfo[oidArray[x]+"name"], pInfo[oidArray[z]+"name"],pInfo[oidArray[k]+"name"])
arcpy.AddMessage(outstring)
del outstring
# found aunt/uncle(blood) at column [z]
# search row [z] for children (cousins)
for k in range (0,num_records):
if (relArray[z][k] == chValue):
# found child at column [k], set values
relArray[z][k] = coValue
relArray[k][z] = coValue
# DEBUG
outstring = "Found Cousin for (%s) Parent:%s Sibling:%s Child:%s!!" % (pInfo[oidArray[y]+"name"], pInfo[oidArray[x]+"name"], pInfo[oidArray[z]+"name"],pInfo[oidArray[k]+"name"])
arcpy.AddMessage(outstring)
del outstring
# found father/mother at column [x],
# search row [x] for mother/father (grandparents)
for z in range (0,num_records):
if ((relArray[x][z] == faValue) or (relArray[x][z] == moValue)):
# found grandparent at column [z], set values
relArray[y][z] = gpValue
relArray[z][y] = gcValue
# DEBUG
outstring = "Found Grandparent for (%s) Parent:%s Grandparent:%s!!" % (pInfo[oidArray[y]+"name"], pInfo[oidArray[x]+"name"], pInfo[oidArray[z]+"name"])
arcpy.AddMessage(outstring)
del outstring
arcpy.AddMessage("Finished Assigning Extended Relationships...")
#
# output 2D array, first header line has already been written during the primary loop
#
outfile1.write("\n")
for y in range (0,num_records):
outfile1.write(idArray[y]+",")
for x in range (0,num_records):
outfile1.write(str(int(relArray[y][x]))+",")
outfile1.write("\n")
outfile1.close()
arcpy.AddMessage("Done writing output table...")
#
# output Family ID table
#
IDfam = 1
famSavArray = range(num_records)
for y in range (0,num_records):
famSavArray[y]=famArray[y]
for y in range (0,num_records):
if famArray[y] != 9999999999:
trans = famArray[y]
for z in range (0,num_records):
if famArray[z] == trans:
famIDArray[z] = IDfam
famArray[z] = 9999999999
IDfam += 1
for y in range (0,num_records):
outfile2.write(idArray[y]+",")
if famIDArray[y] != 9999999999:
outfile2.write("fam"+str(int(famIDArray[y]))+",")
else:
outfile2.write(" "+",")
outfile2.write("\n")
# verification famille id #
#for y in range (0,num_records):
# outfile2.write(idArray[y]+",")
# try:
# outfile2.write(str(idArray[int(famSavArray[y])])+","+"fam"+str(int(famIDArray[y]))+",")
#
# except:
# outfile2.write(" "+","+" "+",")
# outfile2.write("\n")
outfile2.close()
arcpy.AddMessage("Done writing relation table...")
#
# go have a beer
#