# ArcGIS field calculator: Calculating a new field using multiple fields with python

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.

• 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. Commented Mar 10, 2015 at 4:49
• 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. Commented Mar 10, 2015 at 5:18
• possible duplicate of Calculate Field tool to calculate on null fields Commented Mar 10, 2015 at 8:11

Perhaps something like this?

Code block:

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


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

• 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. Commented Mar 10, 2015 at 23:26
• 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) Commented Mar 11, 2015 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. Commented Mar 11, 2015 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. Commented Mar 12, 2015 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.. Commented Mar 12, 2015 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.