Column Expression to convert string to double if the cell is a double

Hi,

I’ve a column that I had to import as string (excel reader).
It contains mostly numbers but sometimes users put a “TBC” when they don’t have the info.

So I want to use Column Expressions Node beause I’ve mutliple columns to do (instead of having multiple rule engine), but I can’t figure out how to make it works.

I tried like this to check if the cell can be converted as double, if no put a missing value, if yes then convert it (type of column is defined as “Double” as well)

if(isMissing(toDouble(column("Count")))) {
    missingValue()
} else {
    toDouble(column("Count"))
}

but it doesn’t work and I do’t understand why

Do you have any idea ?

Thank you.

I have never approached it this way… Have you tried testing to see if the count is > than 0 rather than missing? Maybe that would work.

I would split this into a simple multiple node process. You can always make it a component if you want to reuse it.

hi @Zarkoff95 ,
here’s a Column Expressions node

try {
    toDouble(
        strip(regexReplace(column("Count"), "[^\\d\\.]",""))
    )
}
catch(error) {
    missingValue()
}

input
immagine
output
immagine
Hope ith helps

6 Likes

Wow, it works like a charm ! thank you.

I finally managed it via a string manipulation (multicolumn) but I keep your solution if I finally want to return another thing than a missing value.

I did not know that “Try” was possible in Column Expression.

2 Likes

The Multi-Column String Manipulation node is how I would go about it as well since it can dynamically adapt to different header names. It also gives you the added flexibility to designate all your included columns via Regex or by prior formula test to route for conversion.

I also didn’t know about Try / Catch in the Expressions Node. Thanks @duristef!

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.