4

I'm reading rows (~20 fields per row) from a database using a SearchCursor and placing them into an array. Then, I loop over the array and use an InsertCursor to insert them into a Feature Class in a different database.

The problem, I need to transform field values in the source data. For example, a column may contain the strings "T", "true", "Yes", and "1" and they must be converted to a string value of "TRUE" before being written to the destination column.

  • There may be multiple values in a source column that need to be mapped to a single value in the destination. This is the example above.
  • There may be multiple lookups per column. For example, one column may have as source value of "A" that gets transformed to "Z1" and in the same column, "B" gets transformed to "Z2", and still in the same column, "C" gets transformed to "Z1" (multiple source values mapped to same destination value).
  • There are many columns that will need lookups created. I'd prefer to have a different dictionary / lookup table for each column as there will likely be subtle differences between columns and trying to reuse dictionaries will get frustrating.

How should I approach this problem?


Update #1

Sample using suggestions by @mr.adam: Throws an error on the line if row[key].lower() in lookup(key[1]): with the message TypeError: int object is not subscriptable

lookup = {
    3: ("TRUE", ["t", "true", "1", "yes"]),
    4: ("FALSE", ["f", "false", "0", "no"])
     }

rows = []
rows.append(("abc", "123", "xyz", "True", "F"))
rows.append(("lmo", "456", "xyz", "1", "no"))
rows.append(("tuv", "456", "xyz", "yes", "0"))

for row in rows:
    for key, value in lookup.iteritems():
        if row[key].lower() in lookup(key[1]):
            row[key] = lookup(key[0])

for row in rows:
    print row

Update #2

after some additional digging, breaking down the above referenced line, row[key].lower() evaluates to "true" as expected for column 4 of the first row in the dataset. The error is thrown when evaluating the in clause of that line, lookup(key[1]).

  • Try: if row[key].lower() in value[1]: row[key] = value[0]. Also, note that this method will only allow one list of values and their replacement per field, so you can't replace some values of one field with "TRUE" and other values of that same field with "FALSE". – nmpeterson Feb 4 '15 at 19:48
  • @nmpeterson - when evaluated, your correction does return the expected values for value[0] and value[1]. However, the assignment on the next line fails TypeError: tuple object does not support item assignment. I was wondering how this approach would handle mapping multiple values but I was going to look at that after I had a better understanding of the code as-is. Do you have an idea? – DenaliHardtail Feb 4 '15 at 19:56
  • @nmpeterson yes, that's a good point. the first part of my answer is kind of the extreme other end of the spectrum, where all lookups are applied to all fields. A little bit of trickery could find a good middle ground where certain lookups were applied to multiple fields. – mr.adam Feb 4 '15 at 20:06
  • @DenaliHardtail You are creating your rows as a list [] of tuples (), where you should actually be making a list [] of lists []. You can't set values in tuples the same way as in lists. In other words, use this: rows.append(["abc", "123", "xyz", "True", "F"]). – mr.adam Feb 4 '15 at 20:07
  • I just looked at this again and realized I was completely wrong about the int object is not subscriptable error. I was using the wrong syntx to access the dictionary. Instead of lookup(key[1]) (which is completely wrong) we should just use need value[1]. I'll change the code in my answer as well. – mr.adam Feb 4 '15 at 23:13
0

I would make a dictionary that looks something like this:

lookups = {
    "TRUE":["t", "true", "1", "yes"],
    "FALSE":["f", "false", "0", "no"]
     }

Then, the logic could look like:

for row in rows:
    for i in range(0,len(row)):
        for key, value in lookup.iteritems():
            if str(row[i]).lower() in value:
                row[i] = key

That code will update the entire table at once, row by row. This would be a problem if you have field1 where the value "T" should be translated to "TRUE" and field2 where "T" should be translated to "Top". To if that is the case, you could modify the dictionary to:

lookups = {
    fieldindexnumber: ("TRUE",["t", "true", "1", "yes"]),
    fieldindexnumber: ("FALSE",["f", "false", "0", "no"]),
    fieldindexnumber: ("Unknown",["u"]), # make sure the second object in the tuple is a list []
     }

Then just change the looping structure to:

for row in rows:
    for key, value in lookup.iteritems():
        if row[key].lower() in value[1]:
            row[key] = value[0]

Note that I made all of the potential values lowercase and then cast the existing value to lowercase. I've found that to be very helpful a lot of times, but it may not be what you're looking for.

You may want to implement it in a different way, but I would definitely recommend going with some version of this dictionary, because you can just store it at the top of your script and it will be clearly laid out in case you want to change/add key-value pairs.

  • I tried the above suggestion. Please see the error and code pasted to the original question... – DenaliHardtail Feb 4 '15 at 19:14
  • ah, make sure that the second half of every dictionary item is a list, even if it's empty or only has one entry. I'll update my answer a bit. – mr.adam Feb 4 '15 at 19:57
0

My suggestion: first, create a dictionary of dictionaries. The parent dict's keys will be the index position of the various fields in your SearchCursor (as in @mr.adam's answer). The values will be sub-dictionaries, whose keys are the desired output values and whose values are lists of the possible inputs that will be transformed into the corresponding key.

translations = {

    # 1st field:
    0: {"TRUE": ["t", "true", "1", "yes"],
        "FALSE": ["f", "false", "0", "no"]},

    # 2nd field:
    1: {"Z1": ["A", "C"],
        "Z2": ["B"]}

    # etc.
}

Then define a generic translation function that accepts an input value and a dictionary in the same form as the sub-dictionaries above, returning the transformed value if a match is found, or else the unchanged input value:

def translate(in_value, translation_dict):
    out_value = in_value
    for k, v in translation_dict.iteritems():
        if out_value in v:
            out_value = k
            break
    return out_value

And finally, apply this function to each value in each row, using the field's index to grab the appropriate translation dictionary:

for row in rows:
    for i in xrange(len(row)):
        row[i] = translate(row[i], translations[i])

The rows will then be updated and available for use with your InsertCursor.


One further recommendation: instead of copying the rows to memory, modifying them and then using an InsertCursor, I would do it all on-the-fly, like so:

with arcpy.da.InsertCursor(out_table, [field_list]) as ic:
    with arcpy.da.SearchCursor(in_table, [field_list]) as sc:
        for row in sc:
            for i in xrange(len(row)):
                row[i] = translate(row[i], translations[i])
            ic.insertRow(row)
  • Yeah, the multi-field is good. I was also thinking that you could make the keys of each entry into a list of field index integers, instead of a single field index, and then cycle through those as well. – mr.adam Feb 5 '15 at 15:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.