nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Vind meer berichten getagd met

Accepted answers

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)

All comments

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:

MiikaMäkelä1

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".

MiikaMäkelä1

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 😄

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)

How about using search cursors instead.

Could you post a sample dataset?

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!

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 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]

539_error.png

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

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

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]

field_count_example.png

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.

Doe mee aan het gesprek

Aangemelde leden kunnen berichten plaatsen, updates volgen en meer. Nieuw hier? Registreer een gratis account.

Snelle Links

Community Help
Find useful guides, FAQs, and documents to help you navigate and make the most of Esri Community.