Problem to split simple number cell

[damien henrotin]

Hello world,

I have this cell

I would like to tranform the column in another column with only 661866704.

I try to make a split but I have this result

I don't understand why? Thanks for your help

[Michael Markert]

HI, I think the problem is that you are trying to split a number, not a string. You need to use "Common transformations=>To text" first. The result of split will be an array so use "value.split(".")[0]" to get the first element.

Best
Michael

[damien henrotin]

Indeed but if I apply a text transform on the column I have this result

[Owen Stephens]

Just to check what outcome you are looking for, are you trying to just extract the information before the decimal point or do you want to round the number to the nearest whole number?

[damien henrotin]

@Owen : I need only the first part before the decimal

[Michael Markert]

if you switch from GREL to Python/Jython, you can use "return format(value,'f')"

[damien henrotin]

@michael : Nice, works perferct with your idea return format(value,'f').split(".")[0]

[Owen Stephens]

Just for completeness:

In GREL I'd use floor in this situation:

value.floor()
This gives the greatest integer less than the real number in the value - which would give you 661866704 in this case (i.e. the portion before the decimal point) - this returns it as a number, which is good if you want to use the output as a number. And you can always convert to a string with 'toString()' of course.

The alternative would be to convert to a string first as suggested above, but you need to use a format string to avoid the number being expressed as a exponential. E.g.

value.toString("%f")

and so the complete expression:

value.toString("%f").split(".")[0]

This is almost identical as the Python approach suggested.

Note that using value.toString("%f") does round  the number to 6 decimal places by default. If you only want the integer part this isn't going to matter, but if you wanted to extract the decimal part it would of course. You can specify the number of decimal places to use in the toString command with a period (decimal point) followed by a number of digits. e.g. to round to a whole number (zero decimal places):

value.toString("%.0f")

or to round to 10 decimal places:

value.toString("%.10f") 

etc.

See also the OpenRefine docs for:

toString():  https://docs.openrefine.org/manual/grelfunctions/#string-functions 

Math functions including 'floor': https://docs.openrefine.org/manual/grelfunctions/#math-functions

Best wishes

Owen

[damien henrotin]

@Owen: Thanks for your completeness, always good to know