Solved

Format number with commas


Badge +1

Is there a transformer that can convert a number into a number with a thousands separator, that is, "1000" to "1,000"? I know you can do it with Python but I can't find a transformer that does this.

Thanks

icon

Best answer by david_r 9 December 2016, 12:16

View original

12 replies

Userlevel 2
Badge +17

Hi @jimo, I don't think a single transformer can do that but you can use a series of these transformers here. Assuming that an attribute called "_num" has a numeric value with integer representation:

StringPadder: Pad 0-2 spaces to the left side of the value of "_num" so that the number of characters will be a multiple of 3.

  • Attributes: _num
  • Desired String Length: @ceil(@StringLength(@Value(_num))/3.0)*3
  • Side to Pad: Left

StringReplacer: Replace every three digits with the original three digits and a comma.

  • Attributes: _num
  • Text to Match: (...)
  • Replacement Text: \\1,
  • Use Regular Expressions: yes

AttributeTrimmer: Trim excess leading spaces and trailing comma from the resulting string.

  • Attributes to Trim: _num
  • Trim Type: Both
  • Trim Characters: , <space and comma>

Userlevel 4

I see you specifically asked for a non-Python solution, but since it involves several transformers, here's one anyway ;-)

import fmeobjects
def FeatureProcessor(feature):
    value = feature.getAttribute('num') or ''
    try:
        value_str = "{:,}".format(value)
    except ValueError, TypeError:
        value_str = value
    feature.setAttribute('num', value_str)

Assumes input features with an attribute called "num" (change lines 4 and 9 as necessary)

Userlevel 2
Badge +17

Hi @jimo, I don't think a single transformer can do that but you can use a series of these transformers here. Assuming that an attribute called "_num" has a numeric value with integer representation:

StringPadder: Pad 0-2 spaces to the left side of the value of "_num" so that the number of characters will be a multiple of 3.

  • Attributes: _num
  • Desired String Length: @ceil(@StringLength(@Value(_num))/3.0)*3
  • Side to Pad: Left 

StringReplacer: Replace every three digits with the original three digits and a comma.

  • Attributes: _num
  • Text to Match: (...)
  • Replacement Text: \1,
  • Use Regular Expressions: yes 

AttributeTrimmer: Trim excess leading spaces and trailing comma from the resulting string.

  • Attributes to Trim: _num
  • Trim Type: Both
  • Trim Characters: , <space and comma>

Just an experiment. This expression consisting of some FME String/Feature/Math functions works as well as the three transformers.

 

@Trim(@ReplaceRegEx(@PadLeft(@Value(_num),@Evaluate(@ceil(@StringLength(@Value(_num))/3.0))*3),(...),"\1,")," ,") 
And I remember a similar Q&A;: Number Notation

I think the stringformatter is the way to go.

http://docs.safe.com/fme/2016.1/html/FME_Desktop_Documentation/FME_Transformers/Transformers/stringformatter.htm

.4f will be the format in your case

then a stringreplacer to replace the . to ,

Good luck

Badge +1

Those are all good answers, folks. I was really hoping that there was a single transformer that could do the job. I guess it's a good candidate for a "New Idea".

I try to limit the Python in my scripts because the next person who comes along might not know Python. However, the "{:,}".format(value) suggestion by david_r above seems to be the simplest in the Python realm.

Thanks

Badge

or ....

if you wanted a single transformer solution, you could use a bit of regex in a string replacer.

this will allow you to use one transformer against multiple attributes on the same feature, although admittedly many people may find the python script easier to maintain than the regex! neat solution though.

in 2015.0

StringReplacer settings

text to match:([0-9])(?=([0-9]{3})+$)

replacement text: \\1,

use reg expression: yes

if attr value does not match: no action

The 'positive lookahead assertion' regex is a modification of the version I found here , someone far cleverer than me worked it out. In my quick tests it worked on 1, 1234, 123456,123456789 adding the multiple commas in the right place if required.

Userlevel 2
Badge +17

or ....

if you wanted a single transformer solution, you could use a bit of regex in a string replacer.

this will allow you to use one transformer against multiple attributes on the same feature, although admittedly many people may find the python script easier to maintain than the regex! neat solution though.

in 2015.0

StringReplacer settings

text to match:([0-9])(?=([0-9]{3})+$)

replacement text: \\1,

use reg expression: yes

if attr value does not match: no action

The 'positive lookahead assertion' regex is a modification of the version I found here , someone far cleverer than me worked it out. In my quick tests it worked on 1, 1234, 123456,123456789 adding the multiple commas in the right place if required.

Good catch. Thanks for sharing the elegant regex solution.

 

Userlevel 4

I see you specifically asked for a non-Python solution, but since it involves several transformers, here's one anyway ;-)

import fmeobjects
def FeatureProcessor(feature):
    value = feature.getAttribute('num') or ''
    try:
        value_str = "{:,}".format(value)
    except ValueError, TypeError:
        value_str = value
    feature.setAttribute('num', value_str)

Assumes input features with an attribute called "num" (change lines 4 and 9 as necessary)

Alternative solution using just a single StringReplacer and some creative regex:

 

https://knowledge.safe.com/questions/40697/format-number-from-1000000-to-1-000000.html
Userlevel 4

or ....

if you wanted a single transformer solution, you could use a bit of regex in a string replacer.

this will allow you to use one transformer against multiple attributes on the same feature, although admittedly many people may find the python script easier to maintain than the regex! neat solution though.

in 2015.0

StringReplacer settings

text to match:([0-9])(?=([0-9]{3})+$)

replacement text: \1,

use reg expression: yes

if attr value does not match: no action

The 'positive lookahead assertion' regex is a modification of the version I found here , someone far cleverer than me worked it out.  In my quick tests it worked on 1, 1234, 123456,123456789 adding the multiple commas in the right place if required.

Just a heads up, this regex won't work for fractional numbers, only integers. Sample input:

 

-12345566654.33345
Returns:

 

-12345566654.33,345
Badge
Just a heads up, this regex won't work for fractional numbers, only integers. Sample input:

 

-12345566654.33345
Returns:

 

-12345566654.33,345
Thanks @david_r, yes, good spot.

 

My data happens to be integers in this instance, but I imagine there is away to incorporate(ignore) the fractions into the regex?

 

I will just stick an attributerounder in front in my case though!

 

 

Userlevel 4
Thanks @david_r, yes, good spot.

 

My data happens to be integers in this instance, but I imagine there is away to incorporate(ignore) the fractions into the regex?

 

I will just stick an attributerounder in front in my case though!

 

 

For floating point numbers you can try out the regex I posted here:

 

https://knowledge.safe.com/questions/40697/format-number-from-1000000-to-1-000000.html
Userlevel 6
Badge +31

I see you specifically asked for a non-Python solution, but since it involves several transformers, here's one anyway ;-)

import fmeobjects
def FeatureProcessor(feature):
    value = feature.getAttribute('num') or ''
    try:
        value_str = "{:,}".format(value)
    except ValueError, TypeError:
        value_str = value
    feature.setAttribute('num', value_str)

Assumes input features with an attribute called "num" (change lines 4 and 9 as necessary)

In my case, using python 3.6 it wont work due to ValueError, TypeError, If you remove these it works again.

import fmeobjects
def FeatureProcessor(feature):
    value = feature.getAttribute('originalValue') or ''
    try:
        value_str = '{:,}'.format(value)
    except:
        value_str = value
    feature.setAttribute('originalValue', value_str)

 

Reply