Iterate through rows and write new fields based on a criteria matched with reference table
Hello,
I am trying to iterate through rows in a attribute table( named here :target_table) and look for certain search criteria based on a reference_table.csv. Search criteria is a set of numbers (see below), if criteria is met then I would like to add new fields in target_table and write with values based on the reference_table.
reference_table:
Columns: sec1,sec2,sec3 and sec4 are used as a criteria to search in target_table rows (see below). Example, in the first row: 5,6,31,32(order is not an issue), if this combination of numbers matches while scanning through first 4 rows in the target_table then "Pick" and "cornderID" column will be added into target_table with their values 31 and 200100 respectively.
target_table:
This is a shape attribute table where script will scan trough column "label" then look for the first 4 rows( moving downward) and search for criteria(as mention above) and add new fields "Pick" and "cornerID" with their values . Example, the first 4 rows combination under field "label" is 1,6,36,31, so based on the search criteria from the reference_table, it should write Pick is 36 and cornerId is 700100. Below is what would final attribute table would look like;
Final:
Any suggestion with arcpy scripting and tools will be greatly appreciated !
Thanks !
Accepted Solutions
I changed the code a bit (it was assuming the label to be as second column), but I noticed that there are a lot of combinations that do not exist in the reference table (2358 out of 3410).
Changed code (files on lines 4 and 5, indexes on line 61 and 64):
import csv
def main():
ref = r"D:\Xander\GeoNet\CSVreftarg\referenceALL_table.csv"
tar = r"D:\Xander\GeoNet\CSVreftarg\targetSubset_table.csv"
# fill dictionaries
dct_ref = createRefDict(ref)
dct_tar, dct_data = createTarDict(tar)
# read data and enrich
for fid, vals in dct_data.items():
if fid in dct_tar:
key = dct_tar[fid]
if key in dct_ref:
lst = dct_ref[key]
vals.extend(lst)
dct_data[fid] = vals
# check result:
for fid, vals in sorted(dct_data.items()):
print "{0},{1}".format(fid, ",".join(str(a) for a in vals))
def createRefDict(ref):
# create dict with key is list of value
# and value is list of return values
# sec1,sec2,sec3,sec4,Pick,cornerID
dct = {}
with open(ref, 'rb') as f:
reader = csv.reader(f)
cnt = 0
for row in reader:
cnt += 1
if cnt == 1:
pass # header
else:
key = generateKey(row[:4])
dct[key] = row[4:]
return dct
def createTarDict(tar):
# read 4 records and compose label list,
# insert this list for every FID
# FID,OBJECTID,Join_Count,TARGET_FID,lndkey,label,lndkey_1
dct = {}
lst_fid = []
lst_lbl = []
dct_data = {}
with open(tar, 'rb') as f:
reader = csv.reader(f)
cnt = 0
for row in reader:
cnt += 1
if cnt > 1:
dct_data[row[0]] = row[1:]
if (cnt-2) % 4 == 0:
key = generateKey(lst_lbl)
for fid in lst_fid:
dct[fid] = key
lst_fid = [row[0]]
lst_lbl = [row[5]] # was row[1]
else:
fid = row[0]
lbl = row[5] # was row[1]
lst_fid.append(fid)
lst_lbl.append(lbl)
# add last sequence
key = generateKey(lst_lbl)
for fid in lst_fid:
dct[fid] = key
return dct, dct_data
def generateKey(lst):
lst2 = sorted([int(a) for a in lst])
return ",".join(([str(a) for a in lst2]))
if __name__ == '__main__':
main()
In order to attach documents you need to use the advanced editor. There is a link in the upper right corner:
... to activate the advanced editor. However, this will only be available if you open the link of the discussion (the link is not available in your inbox). Once you are in the advanced editor in the lower right corner you will see the link to attach files:
I haven't tested the code to use the more fields, nor have I written the result to a csv file or other format. To do that it would be good if you could provide sample file to see what is possible.
I changed the code a bit (it was assuming the label to be as second column), but I noticed that there are a lot of combinations that do not exist in the reference table (2358 out of 3410).
Changed code (files on lines 4 and 5, indexes on line 61 and 64):
import csv
def main():
ref = r"D:\Xander\GeoNet\CSVreftarg\referenceALL_table.csv"
tar = r"D:\Xander\GeoNet\CSVreftarg\targetSubset_table.csv"
# fill dictionaries
dct_ref = createRefDict(ref)
dct_tar, dct_data = createTarDict(tar)
# read data and enrich
for fid, vals in dct_data.items():
if fid in dct_tar:
key = dct_tar[fid]
if key in dct_ref:
lst = dct_ref[key]
vals.extend(lst)
dct_data[fid] = vals
# check result:
for fid, vals in sorted(dct_data.items()):
print "{0},{1}".format(fid, ",".join(str(a) for a in vals))
def createRefDict(ref):
# create dict with key is list of value
# and value is list of return values
# sec1,sec2,sec3,sec4,Pick,cornerID
dct = {}
with open(ref, 'rb') as f:
reader = csv.reader(f)
cnt = 0
for row in reader:
cnt += 1
if cnt == 1:
pass # header
else:
key = generateKey(row[:4])
dct[key] = row[4:]
return dct
def createTarDict(tar):
# read 4 records and compose label list,
# insert this list for every FID
# FID,OBJECTID,Join_Count,TARGET_FID,lndkey,label,lndkey_1
dct = {}
lst_fid = []
lst_lbl = []
dct_data = {}
with open(tar, 'rb') as f:
reader = csv.reader(f)
cnt = 0
for row in reader:
cnt += 1
if cnt > 1:
dct_data[row[0]] = row[1:]
if (cnt-2) % 4 == 0:
key = generateKey(lst_lbl)
for fid in lst_fid:
dct[fid] = key
lst_fid = [row[0]]
lst_lbl = [row[5]] # was row[1]
else:
fid = row[0]
lbl = row[5] # was row[1]
lst_fid.append(fid)
lst_lbl.append(lbl)
# add last sequence
key = generateKey(lst_lbl)
for fid in lst_fid:
dct[fid] = key
return dct, dct_data
def generateKey(lst):
lst2 = sorted([int(a) for a in lst])
return ",".join(([str(a) for a in lst2]))
if __name__ == '__main__':
main()
