Creating label formatted like " 1,000' " from numeric field?

Question

This question builds on a previous question I came across at gis.stackexchange: How to easily edit attribute data using Regular Expressions?

Perhaps someone can point me in the right direction?

I have a vector shapefile of contour lines. In the attribute table I have already converted a field called FEET consisting of integer data containing elevation values to a string field called Label. Additionally I successfully appended a ' to the end of the string so that when the data is labeled it will be read as feet.

The next thing I am attempting to do is to insert a comma between the first two values of the string only if the string length is greater than 4, or so that a comma will only appear between the thousands and hundreds digits in the Label field. The length has to be greater than 4 because I'm also counting the '

My code so far is:

case when  length( "Label") >4  then regexp_replace( "Label", '^([0-9])+([0-9]{3}\')$','$1 , $2') else "Label" end

however the regexp_replace function doesn't seem to accept the group variables $1 $2 in the 3rd parameter of the function.

I've looked at http://docs.python.org/2/library/re.html as the field-calculator regex is from what I understand based on python's use of regex but haven't been able to figure this out. Maybe what I'm attempting to do isn't possible yet with this function in field calculator in QGIS 1.8 on mac OSX. Or more likely my syntax is wrong as I'm a novice programmer and somewhat new to regular expressions.

Answer 1

QGIS has created a function called format_number to address this. You simply do format_number(12345,0) to get 12,345.

Answer 2

Using two string functions in the QGIS calculator on version 2.6 Mac OS X I was able to accomplish this. Here are the steps:

1. Using the field calculator create a new field that is a string type with a relevant width.

2. Run the following expression to set the value of the field:
   concat( format_number( "Field_name" , 0) , '\'')

This created a column with a string formatted as I wanted so for example the number 2000 would be formatted to 2,000' with the trailing ' which stands for feet.

Answer 3

You can do digit grouping easily in Python. Here's a function that I've used, which I think I found on StackOverflow:

def digitgroup(n, sep = ','):
    if n == "": n = 0
    s = str(n)[::-1]
    groups = []
    i = 0
    while i < len(s):
        groups.append(s[i:i+3])
        i += 3
    return sep.join(groups)[::-1]

Answer 4

In QGIS, the function "format_number" under the string menu in field calculator reformats numbers to locale specific separators and decimal places.

Answer 5

For people whose locale does not fit the desired formatting display, format_number is not an adequate solution. For example, on my French system, format_number(2000) always displays 2 000 (with a space as thousands separator) independently of QGIS language settings, although I sometimes need an "English-style" display -- eg. 2,000.

In such cases, the following (and somewhat cumbersome) approach works (at least on QGIS 2.18):

if (your_field > 1000,
  concat(
    floor(your_field/1000),
    ',',
    your_field-1000*floor(your_field/1000)
  ),
  your_field
)

You can of course replace ',' with whatever thousands separator you want. Note that this expression only works with numbers lower than 1,000,000.

Answer 6

It's a bit more complicated than that in QGis. As per this article, you have to create your own function definition in Python, then import it into QGis (this can be done automatically on startup). This may seem tricky, but you only need to do it once and it will always be there!

This is the function I used, based on dmahr's above. Not sure how it deals with strings, as I modified the code a bit.

from qgis.utils import qgsfunction
from qgis.core import QGis

@qgsfunction(2, "Python") # number of arguments
def thousands_separator(values, feature, parent): # values are the arguments passed in
    # this is the documentation for your function which you will see in QGis
    """
    Adds a separator to values over 1000\n        
    Parameters: (n, sep) \n                   
    n = expression_field \n
    sep = desired separator (default is ",")
    """ 
    number = values[0]                        # the first argument (which in the
    separator = values[1]                     # labelling case is a field name
    if separator == '':
        separator = ','
    s = str(number)[::-1]
    groups = []
    i = 0
    while i < len(s):
        groups.append(s[i:i+3])
        i += 3
    return separator.join(groups)[::-1]

You then save that in .qgis/python, import the file into QGis using the console, then it will show up in "Python" functions in the label tool.

The expression I used is: thousands_comma( "[field_name]" ,',')