Basically, I have a large polygon data set that has soil data for an area and I want to assign numeric values to the data based on soil texture text values (e.g. SIL=4, CLY=7, ...). However, the soil texture is divided into three different columns: 1) TEXTURE1; 2) TEXTURE2; and, 3) TEXTURE3. Usually TEXTURE1 gives me the data I need to assign the numeric value in a new field, but in some cases the value for TEXTURE1 is listed as N/A, and I need to use the text from TEXTURE2 to assign the number. Is it possible to write a script where if the text from the TEXTURE1 field is NOT N/A return TEXTURE1, else return TEXTURE2? The new field would be a text field.

  • 1
    Do you want to do this inside field calculator or in a python script? This can be done with a code block but you need to code all possible values. Use summary statistics to find how many unique values are in a given field and then code for each one. It might be easier to use a lookup table situation, where a table has two fields Code and Value and you can join by attributes, then any unmatched join by texture2 and calc. – Michael Stimson Mar 10 '15 at 4:49
  • 1
    Well, you can write a relatively simple nested if statement such as if tx1 = n/a, then tx2 else tx1; if tx2 = n/a then tx3 else tx2; if tx3 = n/a then fail or n/a else tx3 - all of which leads to populating txcode. Then a second new field can handle txcode lookup and assign that via lookup or dictionary or whatever. It's not clear if you specifically want to do this in one step/script or if two is ok or what. – Chris W Mar 10 '15 at 5:18
  • possible duplicate of Calculate Field tool to calculate on null fields – radouxju Mar 10 '15 at 8:11

Perhaps something like this?

Code block:

def GetTexture(Text1,Text2):
  if Text1 is None:
    return Text2
    return Text1

With expression NEWTEXTURE = GetTexture(!TEXTURE1!, !TEXTURE2!)

  • 1
    That's the basics HarvardMoe, the point that @ChrisW and I am trying to get across is that there's possibly a lot of values to code for and the dialog for field calculator isn't the best. This method works great for up to a dozen values after that it's usually best to compile a lookup table and join by attributes. Still, it's worth +1. – Michael Stimson Mar 10 '15 at 23:26
  • 1
    Yes, I see that now on reading through the original post again. Guess it all depends on the number of values. It's probably more efficient to do this with an update cursor and a dictionary for lookups (once you have the data for that) – HavardMoe Mar 11 '15 at 16:05
  • That's an excellent Idea HavardMoe, there is a python tag so perhaps code that and extend your answer, but just for a few basic cases to show how it's done. Essentially it's the same thing as creating lookup tables and joining except that the 'lookup' is the dictionary and the 'join' is done at runtime. I've already upvoted your answer so I can't again but that provides a great methodology that would definitely solve the problem and might attract more upvotes. – Michael Stimson Mar 11 '15 at 21:36
  • That's a start, but remember there are three possible fields that could have the value, so you need to nest two more condition tests in there (what if text2 is none, get text3, what if text 3 is none, fail). This would address the basic question - get one field that has a valid text value in it. This is simple enough as a field calc. @MichaelMiles-Stimson goes one further and combines the next step into this one - get the first valid text value from three possible locations and then encode that value to the desired value. That is a bit more than you'd want to handle in a field calc. – Chris W Mar 12 '15 at 22:33
  • It just depends on how many values there's likely to be @ChrisW, I don't like hard-coding more than 6 options (because I'm lazy and you end up with so much header in the script). I have done this with Regional Ecosystems which has up to RE6 field (sub-sub-sub-sub-sub species) but at that time they were definitely standard and the lookup was a field out of an excel file (copy/paste to new file) and fill in the values.. – Michael Stimson Mar 12 '15 at 22:41

Fisrt I would compile lookup tables for TEXTURE1, TEXTURE2 and TEXTURE3 using Summary Statistics (to find all the unique values), statistics doesn't matter all you want is each TEXTURE field as a 'case' field. Add Field to add the value you want to apply to each statistics table to make it into a lookup table, delete the statistics fields if you wish with Delete Field (you can do more than one field at a time).

Now the tedious part, edit the table, delete instances of N/A and Null from the table, and put the value you want to apply next to the code in the tables (all 3 could be different).

Start by joining the Lookup table for TEXTURE1, selecting matching records only and apply the matching value using Field Calculator (joined fields should be named with the table name then field name), then remove the join and join the TEXTURE2 lookup table to the TEXTURE2 field (also matching records only). Now, select in the table the ones that haven't been calculated (TargetField is NULL) and calculate the values over the join on selected records only. Remove the join and do the same for TEXTURE3.

Now all the TargetField values are populated where there is a value in TEXTURE1, TEXTURE2 or TEXTURE3, with an order of preference.

There are more steps involved but once you've done this procedure once you'll get the idea of how it works - also keep the lookup tables in case you want to do this again.

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.