Iterate through rows and write new fields based on a criteria matched with reference table

YamMagar

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 !

XanderBakker

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