topic Re: counting columns with text in Geoprocessing Questions

counting columns with text

MiikaMäkelä1 — Mon, 04 Mar 2013 07:26:50 GMT

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

Re: counting columns with text

LucasDanzinger — Mon, 04 Mar 2013 19:09:09 GMT

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.

Re: counting columns with text

MiikaMäkelä1 — Tue, 05 Mar 2013 05:17:51 GMT

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]

Re: counting columns with text

MiikaMäkelä1 — Tue, 05 Mar 2013 05:19:21 GMT

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

Re: counting columns with text

LucasDanzinger — Fri, 10 Dec 2021 22:50:33 GMT

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!)

Re: counting columns with text

MiikaMäkelä1 — Wed, 06 Mar 2013 07:00:00 GMT

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]

Re: counting columns with text

LT — Fri, 10 Dec 2021 22:50:36 GMT

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!

Re: counting columns with text

MiikaMäkelä1 — Mon, 11 Mar 2013 11:38:24 GMT

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!

Re: counting columns with text

LT — Mon, 11 Mar 2013 13:11:07 GMT

How about using search cursors instead.

Could you post a sample dataset?

Re: counting columns with text

LT — Mon, 11 Mar 2013 13:27:28 GMT

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)

Re: counting columns with text

MiikaMäkelä1 — Tue, 12 Mar 2013 06:45:20 GMT

Thanks!

I couldn't get the first code block to work either, but it was the "less transparent" code that did the job!

Thanks so much, now I won't have to count 700k rows manually 😄

Re: counting columns with text

MiikaMäkelä1 — Tue, 12 Mar 2013 06:50:21 GMT

So, maybe the problem is something with this none constant in Python? I'm using ArcGIS 10.1 - could there be differences in Python coding? Anyway, the problem is solved with the "less transparent code".

Re: counting columns with text

LT — Fri, 15 Mar 2013 19:44:47 GMT

No, I really don't think the None constant was the problem. I bet it has to do with those special characters in your data. I don't have any data like that to test, but I do have data with <<Null>> values and it worked fine for that. My guess is that the special chars in the the strings were stymieing the len function.

Basically, you're just testing if the string is empty or null. So it could be done as:

if a! = '' and a =! None: