5

BACKGROUND: Long time reader, first time poster. Looking for help using QGIS to score data in a CSV table that was yielded from processing the contiguous neighbors of every polygon from a file with over 750k features.

VERSION: 2.2 or 3.0

GOAL: an equation to run in Field Calculator

DATA: Column A (small string), Column B (long string), Column C (output of function, a small integer)

NATURAL LANGUAGE ARTICULATION: If any part of B contains all of A, then count all instances of all of A in all of B, output number of instances as integer to Column C

EXAMPLE

COLUMN A     COLUMN B                                              COLUMN C
---------    --------------------------------------------------    --------
Doe, John    Doe, John; Smith, John; Johnson, Harry; Doe, John;    2
2
  • Could you add a specific example into the question to clarify? May 20 '18 at 15:15
  • Please Edit the question to add clarifications. It's not fair to those who would help you to force them to scan comments for critical information.
    – Vince
    May 21 '18 at 1:25
5

You can define a custom function.

Open 'Function Editor' tab in 'Field Calculator'. Create new file, name it. Paste this script into the editor.

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

@qgsfunction('auto', 'Custom')
def func(field_a, field_b, feature, parent):
    return field_b.count(field_a)

Click 'Load' button. Go back to 'Expression' tab and write this expression: (You should use double quotes for field, not 'A' or 'B')

func( "A" , "B" )

enter image description here

Result:

enter image description here

0
4

Checking if A is in B can be done using regular expressions:

CASE WHEN regexp_match('QGIS ROCKS','\\sROCKS') > 0 
THEN 1
ELSE 0
END

but the counting will probably require a custom function.

0
3

We found a way to do this in Excel:

https://exceljet.net/formula/count-specific-words-in-a-cell

Generic formula:

=(LEN(text)-LEN(SUBSTITUTE(text,word,"")))/LEN(word)

Specific formula for the example above:

=(LEN(B1)-LEN(SUBSTITUTE(B1,A1,"")))/LEN(A1)

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.