Problem to split simple number cell

93 views
Skip to first unread message

damien henrotin

unread,
Nov 4, 2022, 6:17:24 AM11/4/22
to OpenRefine

Hello world,

I have this cell


OR1.JPG

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

I try to make a split but I have this result

OR2.JPG

I don't understand why? Thanks for your help

Michael Markert

unread,
Nov 4, 2022, 6:27:33 AM11/4/22
to OpenRefine

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

unread,
Nov 4, 2022, 6:39:39 AM11/4/22
to OpenRefine
Indeed but if I apply a text transform on the column I have this result

OR3.JPG

Owen Stephens

unread,
Nov 4, 2022, 6:41:39 AM11/4/22
to OpenRefine
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

unread,
Nov 4, 2022, 6:48:05 AM11/4/22
to OpenRefine
@Owen : I need only the first part before the decimal

Michael Markert

unread,
Nov 4, 2022, 7:04:12 AM11/4/22
to OpenRefine
if you switch from GREL to Python/Jython, you can use "return format(value,'f')"

damien henrotin

unread,
Nov 4, 2022, 7:09:22 AM11/4/22
to OpenRefine
@michael : Nice, works perferct with your idea return format(value,'f').split(".")[0]

Owen Stephens

unread,
Nov 4, 2022, 8:02:14 AM11/4/22
to OpenRefine
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:

Best wishes

Owen

damien henrotin

unread,
Nov 4, 2022, 9:17:50 AM11/4/22
to OpenRefine
@Owen: Thanks for your completeness, always good to know
Reply all
Reply to author
Forward
0 new messages