8

I have a field which contains a series of words separated by commas.

I am wondering if it is possible to count the number of times the letter 'G' occurs. Alternatively it might be possible to split the field by commas and go down another avenue...

Here's a few example fields:

  • Urban,Green,Urban,Green,Green
  • Urban,Urban,Urban,Urban,Green,Urban
  • Urban,Green,Green

Essentially I would like to produce a new column which tells me how many times 'Green' appears in each field.

  • Both of those things are easily possible in Python. What have you attempted? – Vince Feb 28 at 14:49
  • Unfortunately I don't know any and I try and do everything via field calculator. I would do it within Excel by 'find' and cutting all non 'g' characters, counting the string and then saving as a dbf but I no longer have the version of excel that saves as dbf. I am willing to try and learn if you know of any links that might help me get going :)! – Sablepoot Feb 28 at 14:54
  • 1
    I've just answered with a method like your Excel method. It uses regexp_replace. – Spacedman Feb 28 at 15:39
13
array_length(
    array_filter(
        string_to_array("FIELD_NAME"),
        @element='Green'
    )
)

Change FIELD_NAME into your field name. If string is separated by another character, you should add that character to string_to_array function as seperator. For example:

string_to_array("FIELD_NAME", ';')

For Urban,Green,Urban,Green,Green string:

  • string_to_array("FIELD_NAME") returns ['Urban', 'Green', 'Urban', 'Green', 'Green']
  • array_filter(ARRAY, @element='Green') returns ['Green', 'Green', 'Green']
  • array_length returns 3
|improve this answer|||||
  • Thank you, this is another cracking route to the answer and I now understand what yet more functions do :)! – Sablepoot Feb 29 at 10:11
8

Here's a way to find the number of Gs. Replace everything that isn't a G with an empty string, and count the length of what is left:

length(regexp_replace('Urban,Green,Urban,Green,Green','[^G]',''))
-> 3

length(regexp_replace('Urban','[^G]',''))
-> 0

[^G] is a regexp pattern that matches any character that isn't a G.

|improve this answer|||||
  • 1
    What a nice short answer. – Kadir Şahbaz Feb 28 at 15:53
  • 1
    Thank you, super stuff. Very happy I now know this exists! – Sablepoot Feb 29 at 10:10
7

It might be simpler ways of doing it, but I would have done it by a custom function in the field calculator.

Open the attribute table for your layer. Open the field calculator (Ctrl-I or abacus icon)

Go to edit function. In the upper edit field, delete what is there and write

 from qgis.core import *
 from qgis.gui import *

 @qgsfunction(args='auto', group='Custom')
 def cnt(field, substring, feature, parent):
     return field.count(substring)

Press Save and load functions,

Go back to the expression editor and write

cnt(<fieldname>,<searchstring>)

Define a fieldname and press Done.

|improve this answer|||||
6

This expression should work:

array_length( 
    array_remove_all(
        array_foreach( 
        string_to_array( "column_name" ),

            array_find( string_to_array( @element ), 'Urban' )

        ),
        -1
    )
)

It turns turns a comma separeted sequence of strings, for example Urban,Green,Urban,Green,Green into an array.

Then it checks each element if it contains the string 'Urban', outputing an array where -1 means the element is not the string 'Urban'.

Then it creates a new array without the -1 elements, and finally counts the length of that array to get the instances of the search string.

Just replace the string 'Urban' with whatever you want to count.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.