This post presents a script that was used in order to identify families from a database comprising the name of 6116 victims of the Holocaust in italy, as well as, for most of them, the name of their mother, father and spouse.
For any two individuals, the script compares the names of their mother, father, and spouse, and assigns the same family ID to individuals that share the same two parents (siblings), are married to each other (spouses), or for which one’s parent’s spouse matches their second parent (parent and child). In all those cases, if one of the matched individual already has a family ID, the same ID is assigned to the second individual. In the case of an incomplete match between two individuals, for instance if the spouse of an individual has a different name of his or her spouse’s spouse (himself) or if a name is missing, the script returns the inconsistency in the logfile as a possible issue. The script also creates a table of social network relations using code numbers to specify the relationship between any two individuals.
The script is written in python and was originally designed to be used as a tool in ESRI ArcCatalog software. Using it outside of ArcCatalog would only require to change the first few lines of code.
This script is based on the work of Ryan Schuerman, a fellow doctoral student at Texas State University, whom I wish to thank for providing me with an early version of the 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
#