topic Re: Field calculation, concatenation in Python Questions

Field calculation, concatenation

CFrate — Fri, 24 Aug 2012 15:36:22 GMT

I've hit a wall with a field calculation.
I'm writing a calculation for a full address field formed by the values of several other string fields. This is what I have:

Expression = fulladdress(!AddNum!, !Prefix! , !Street! , !Type! , !Suffix! , !Unit!)

def fulladdress(num, prefix, street, sttype, suffix, unit):

# Concatenate fields
    # for addresses without unit
    if unit:
        fulladd = num +' '+ prefix+' '+street+' '+sttype+' '+suffix+' #'+unit
    # for addresses with unit    
    else:
        fulladd = num +' '+ prefix+' '+street+' '+sttype+' '+suffix

# Remove unwanted spaces
    fulladd = fulladd.strip()
    fulladd = fulladd.replace('  ',' ')
    return fulladd

It works exactly as I expect... except that it ignores the IF statement completely. I've tried several different ways of forming the IF, but none work as expected. My calculated result is always what it should be if there's a value in the unit field.

I think I've missed something very basic...

Re: Field calculation, concatenation

ChrisSnyder — Fri, 10 Dec 2021 23:16:03 GMT

There would be a difference if the "unit" field was Null or simply a blank string of ''.

Your code should work as is when unit is a Null, however, a blank string of '' is considered a valid value.

Something like this should take care of both cases:

def fulladdress(num, prefix, street, sttype, suffix, unit):
    if unit not in [None, '']:
        fulladd = num +' '+ prefix+' '+street+' '+sttype+' '+suffix+' #'+unit
    else:
        fulladd = num +' '+ prefix+' '+street+' '+sttype+' '+suffix
    fulladd = fulladd.strip()
    fulladd = fulladd.replace('  ',' ')
    return fulladd

A tip for you: It is much easier to deal with and format complex conditional expresions in an UpdateCursor than in the "code block" of the CalculateField tool.

Re: Field calculation, concatenation

CFrate — Fri, 24 Aug 2012 17:24:55 GMT

Thanks, Chris, but it didn't yield a different result. The results still end in '#' even when there's no unit present.

My reason for going about it in the calculator is that I'm saving it as a .cal file so that my end-users can just load the expression when they need to calculate. It's the easiest option I could think of, since they're familiar enough with the field calculator.

Just so we're clear on the desired results,
Given these values:
num= 101
prefix= N
street=Main
sttype=St
suffix=''
unit=1A
The calculation should return "101 N Main St #1A"

But if the Unit value were not present, then it should just be "101 N Main St"
With my expression and the one you provided, the result is instead "101 N Main St #"

Thanks!

Re: Field calculation, concatenation

ChrisSnyder — Fri, 24 Aug 2012 17:28:41 GMT

In your database, what is the field value when there is "no unit field value"?

Is it Null, '', ' ', or ?

Re: Field calculation, concatenation

CFrate — Fri, 24 Aug 2012 17:34:19 GMT

In your database, what is the field value when there is "no unit field value"?

Is it Null, '', ' ', or ?

In such a case, it's blank. No space and no nulls (though the field is nullable).

Re: Field calculation, concatenation

BruceNielsen — Fri, 24 Aug 2012 18:06:07 GMT

what if you try

if len(unit) < 1:

Re: Field calculation, concatenation

CFrate — Fri, 24 Aug 2012 18:25:57 GMT

what if you try
if len(unit) < 1:

Well, that almost worked. Now I have the opposite problem.
Using len(), the field calculates correctly for records with no value in UNIT, but not those with a value in UNIT.

Re: Field calculation, concatenation

BruceBacia — Fri, 10 Dec 2021 23:16:06 GMT

Here's to trying a different approach:

def fulladd(num, prefix, street, sttype, suffix, unit):
    fulladd = (num + ' ' + prefix).strip()
    fulladd = (fulladd + ' ' + street).strip()
    fulladd = (fulladd + ' ' + sttype).strip()
    fulladd = (fulladd + ' ' + suffix).strip()
    if unit.strip() not in ['None','',' ']:
        fulladd = fulladd + ' #' + unit
    return fulladd

Re: Field calculation, concatenation

BruceBacia — Fri, 10 Dec 2021 23:16:09 GMT

This version should work for you. It will take care of unwanted leading or trailing spaces

def fulladd(num, prefix, street, sttype, suffix, unit):
    fulladd = ''
    for add in [num,prefix,street,sttype,suffix]:
        fulladd = ''.join([fulladd,' ',add.strip()]).strip()
    if unit.strip() not in ['None','']:
        fulladd = ''.join([fulladd,' #',unit.strip()])
    return fulladd

Re: Field calculation, concatenation

BruceBacia — Fri, 10 Dec 2021 23:16:12 GMT

Here's an even shorter, quicker version. You might want to test this one a little before implementing. I did test it, but not as extensively as the previous one.

def fulladd(num, prefix, street, sttype, suffix, unit):
    add = [num,prefix,street,sttype,suffix,''.join(["#",unit.strip()])]
    fulladd = [''.join([a.strip()," "]) for a in add if a.strip() not in ['#None','','#']]
    fulladd = ''.join([f for f in fulladd]).strip()
    return fulladd

Re: Field calculation, concatenation

BruceBacia — Fri, 10 Dec 2021 23:16:14 GMT

Ok, I promise this will be the last revision 😄 I tend to obsess about these things sometimes. Also, you might be able to change the little exclude list from ['#None','','#'] to ['','#']...... I'm not entirely sure of what a null value will return in this context (i.e. 'None' vs. '' vs. ' '). You should mess around with it. Cheers to working with structure data!


def fulladd(num, prefix, street, sttype, suffix, unit):
    add = [num,prefix,street,sttype,suffix,''.join(["#",unit.strip()])]
    fulladd = ''.join([''.join([a.strip()," "]) for a in add if a.strip() not in ['#None','','#']]).strip()
    return fulladd

Re: Field calculation, concatenation

CFrate — Fri, 10 Dec 2021 23:16:17 GMT

Ok, I promise this will be the last revision 😄 I tend to obsess about these things sometimes. Also, you might be able to change the little exclude list from ['#None','','#'] to ['','#']...... I'm not entirely sure of what a null value will return in this context (i.e. 'None' vs. '' vs. ' '). You should mess around with it. Cheers to working with structure data!
def fulladd(num, prefix, street, sttype, suffix, unit):
    add = [num,prefix,street,sttype,suffix,''.join(["#",unit.strip()])]
    fulladd = ''.join([''.join([a.strip()," "]) for a in add if a.strip() not in ['#None','','#']]).strip()
    return fulladd

That last one works perfectly on the ones I've tested so far.
Thanks, Bruce!

**EDIT**
Spoke too soon...

Using a set of 4 records, I was testing this and it worked perfectly on records with or without Unit #s. But then I removed the values from UNIT for ones on which I'd already run the expression, and when I attempted to run the expression again, the calculation failed, with only a generic 999999 error.

Re: Field calculation, concatenation

CFrate — Mon, 27 Aug 2012 19:22:19 GMT

There appears to be some issue related to running a calculation on a field more than once.
I ran the expression once, and it worked. I ran again without changing anything, and got the 999999 error again. Subsequent tests yield the same results.

Re: Field calculation, concatenation

BruceBacia — Fri, 10 Dec 2021 23:16:19 GMT

Carlo,

The problem is probably null values. Apparently you have to pass the field as a string (str(field)) to the function for field calculator to deal with these values. Also, the field calculator window seems to always want to switch the little radio button at the top from "Python" to "VB", which will make it fail right off the bat. This happened to me several times when testing. The solution below worked well with test structure data i used.

def fulladd(num,prefix,street,sttype,suffix,unit):
    add = [num,prefix,street,sttype,suffix,''.join(['#',unit])]
    fulladd = ''.join([''.join([a.strip()," "]) for a in add if a.strip() not in ['','#']]).strip()
    return fulladd

 fulladdress(str(!AddNum!), str(!Prefix!) , str(!Street!) , str(!Type!) , str(!Suffix!) , str(!Unit!))

Re: Field calculation, concatenation

BruceBacia — Fri, 10 Dec 2021 23:16:22 GMT

Heck, why not make it a one-liner


def fulladd(num,prefix,street,sttype,suffix,unit):
    fulladd = ''.join([''.join([a.strip()," "]) for a in [num,prefix,street,sttype,suffix,''.join(['#',unit])]  if a.strip() not in ['','#']]).strip()
    return fulladd

 fulladdress(str(!AddNum!), str(!Prefix!) , str(!Street!) , str(!Type!) , str(!Suffix!) , str(!Unit!)).

Re: Field calculation, concatenation

CFrate — Wed, 29 Aug 2012 19:32:34 GMT

Now I get this error:
"A field name was not found or there were unbalanced quotation marks"

The fields are all there and correct, and the syntax is checking out in Python.
And, again, the expression works correctly at first, but after multiple tests on the same features it suddenly stops working. This latest attempt, the calculation ran correctly a few times, and then I tried to calculate another field and got the 999999 error. Then the expression would no longer function and would return 999999 and the above message.
I'm about to scrap this plan and just have this calculation run externally after scheduled post/reconcile tasks run.

Thanks for your help!

Re: Field calculation, concatenation

BruceBacia — Sun, 12 Dec 2021 16:00:07 GMT

That's strange, I haven't run into any issues like that. I ran it several times on the same dataset and it worked each time. Field calculator does seem pretty shady in 10. I added one fix below ---- will strip the unit value before joining it with "#" (will take care of any potential leading spaces on the unit value.

def fulladd(num,prefix,street,sttype,suffix,unit):
    fulladd = ''.join([''.join([a.strip()," "]) for a in [num,prefix,street,sttype,suffix,''.join(['#',unit.strip()])]  if a.strip() not in ['','#']]).strip()
    return fulladd