counting columns with text

[MiikaMäkelä1]

Hi all,

I have 4 columns which can contain a string (a name) or can have a value <NULL>.

I want to create a 5th column where I count the number of strings in the 4 columns.

so for example:

Column A - Column B - Column C - Column D - Column Count

textA - textB - <NULL> - <NULL> - 2
<NULL> - textB - textC - textD - 3

So how do I populate this Column Count?

Thanks for any ideas!

Miika

[LucasDanzinger]

I would use the Calculate Field tool. Use Python as the parser, and set the pre-logic script code to something like:

[PHP]def count(a, b, c, d):
    e= str(a) + str(b) + str(c) + str(d)
    return len(e)[/PHP]

The bottom should just be:

[PHP]count(!Field1!, !Field2!, !Field3!, !Field4!)[/PHP]

This will feed in these 4 fields to the function, concatenate all of the strings together, then grab the length of the string.

[MiikaMäkelä1]

Thanks for your help!

Almost - but not quite. Or then I didn't understand it 😄

I've attached an image what the real situation looks like. There were actually 5 columns that I wanted to count, not 4 - but I don't think that makes much of a difference to the code. But I don't want to count length at any point. I only want the amount of strings (so each column basically has a value of 0 or 1. I tried changing len to count but still couldn't get it to work. Could the NULL values cause problems? There is a lot of them.


[ATTACH=CONFIG]22365[/ATTACH]

[MiikaMäkelä1]

note, the image in the previous post - the count column I populated by hand for this example. This is what it should look like.

[LucasDanzinger]

Oh, sorry I didn't understand correctly. Something like this should do the trick:

def count(a,b,c,d,e):
    x = 0
    if len(str(a)) != 0 and str(a) != "None":
        x +=1
    if len(str(b)) != 0 and str(b) != "None":
        x +=1
    if len(str(c)) != 0 and str(c) != "None":
        x += 1
    if len(str(d)) != 0 and str(d) != "None":
        x += 1
    if len(str(e)) != 0 and str(e) != "None":
        x += 1
    return str(x)

And the bottom should be:

count( !field1!, !field2!, !field3!, !field4!, !field5!)

[MiikaMäkelä1]

this is getting interesting! Thanks a lot for your help so far.

The script seems to work basically, but somewhere in the middle of execution it stops in a "failure during processing" message. When I look it up, it refers to error code 539. If I understand correctly the script can't handle the special characters within the names in my fields. The Sami languages have some very strange characters. If this is the problem and we can't figure a solution for it, then a  workaround could be to actually count the NULL values. I would arrive to the same figure with an intermediate calculation: 5 - [amount of NULL values] = [amount of names]

Could you still help me with this?

[ATTACH=CONFIG]22393[/ATTACH]

[LT]

Yes, it's the character's messing it up.

Yes, you could try just reversing ldanzinger's procedure to count null's (he's also counting empty strings--don't know if you want to do that):

def count(a,b,c,d,e):
    x = 0
    if len(str(a)) == 0 or str(a) == "None":
        x +=1
    if len(str(b)) == 0 or str(b) == "None":
        x +=1
    if len(str(c)) == 0 or str(c) == "None":
        x += 1
    if len(str(d)) == 0 or str(d) == "None":
        x += 1
    if len(str(e)) == 0 or str(e) == "None":
        x += 1
    return str(x)

Actually, on second thought this error might just be due to trying to cast a unicode to string. (That is after all the entire point of unicode, that you can encode all those extra characters which an ascii string can't)  I don't think there's any need to do that. (Though maybe Lucas knows a reason this must be done.  Does PHP need strings, not unicode?  But the PHP is only using the count output.  So maybe if you still cast that...) Just try this:

def count(a,b,c,d,e):
    x = 0
    if len(a) != 0 and a != "None":
        x +=1
    if len(b) != 0 and b != "None":
        x +=1
... and so forth
    return str(x)

By the way, Lucas, I am intrigued by your mention of PHP.  I've been wondering why the syntax for field calculator statements uses the exclamation points.  Can you point me to somewhere I can read more about this?  I haven't found any mention of PHP on the field calculator page.  --Thanks!

[MiikaMäkelä1]

This is harder than I thought!

Reversing the code to count the NULL values doesn't seem to work. Could it be, that you can't count NULL values, because there is nothing to count. I heard of difficulty with these. Maybe I should just select them all and populate 0. Was just hoping to reduce any unnecessary steps.

The second code block suggestions does however run without any errors. The result just is not correct. It populates NULL for every cell, except for the ones where it does not have to deal with any NULL values - so in this case the rows where there are 5 names in total. So the NULL values override the result. Replacing the NULL values with some other string would possibly help here also, but then the code would have to skip this string. And one more step in the process.

Thanks for all suggestions so far!

[LT]

How about using search cursors instead.  

Could you post a sample dataset?

[LT · Accepted solution]

Nevermind about the data.  I found some of my own that has Nulls...


Here's the solution:

def count(a,b,c,d,e):     x = 0     if len(a) != 0 and a != None:         x +=1     if len(b) != 0 and b != None:         x +=1 ... and so forth     return str(x)

None is a built-in Python constant.  Not a string spelled 'None'.  You have to check against the constant.  In fact, a much more sleek (though less transparent) solution would be:

def count(a,b,c,d,e):     x = 0     if a:         x +=1     if b:         x +=1 ... and so forth     return str(x)