Replace Cell values across columns

Hi All,

I have just started using KNIME platform and have gone through basics and nodes documentation/example.

In my data multiple columns have String data and Integer values. The string is static (“NA”) across columns. I want to convert these columns into Numeric/Integer. But due to presence of “NA” in few cells Column is not detected as Numeric.

I tried String Manipulation to replace, seem this needs to be set for each column. Is there any node which converts Cells with the word “NA” as empty/null?

Thanks in Advance.

Hi @rk44 and welcome to the community.

Using the String to Number node should help you take care of this problem. Take your columns that have been detected as strings, due to the NA cells, and feed them to this node. The NA values will be converted to KNIME missing (?) values automatically when the type conversion happens.

1 Like

Hi Scott,

Thanks for you reply.

One of the columns is detected as String but few columns are not Detected as String columns, seeing “?” in the Column header.

image

Also Conversion failed with following error, when I have applied String to Number conversion for Columns which was detected as String

“Values in 1618 cells could not be parsed, first error: ‘80,086.24’ (RowKey: Row1#0, Position: 11)”

Thanks!
RK

What node(s) are you using to bring your data into KNIME? If you can post an example workflow and some toy data, we should be able to track down the problem.

EDIT: I suspect the error you mention is due to not specifying a decimal and/or thousands separator.

The test file was originally downloaded from Google sheets, when saved using Excel columns are detected as “String”, but still fails in the conversion.

I have attached the sample file and workflow.
ExcelSheetReaderTest.knwf (3.5 MB)
test.xlsx (8.6 KB)

Is there a way to Convert a column to String, when the column header is shows “?” symbol ?
Secondly, if the Conversion error is caused by Comma separator, how to address it?

Hi Scott,

Thanks for input, after configuring “thousands separator” numbers are converted, but Fails when the cell has String data.

I have got following error message,
Values in 134 cells could not be parsed, first error: ‘NA’ (RowKey: Row151#0, Position: 10).

EDIT: Before playing more with Google sheets as I suggest below, can you instead first try the Column Auto Type Cast node to force a column type onto the [?] unknowns? The Column Rename node may be useful as well, since you can also use it assign types.


OK, it seems like the main problem here is correct detection of column type when reading in Google sheets. So far I haven’t been able to recreate the problem - any Google sheets I create are importing and converting OK - but I am just using some simple examples.

Do you have a Google sheet you can share that exhibits this issue?

With respect to the warning message you’re seeing (Values in X cells could not be parsed…) - this can be safely ignored. The String to Number node is doing the correct conversion - in this case, from “No Data” to “?”.

This is I think a separate issue from the one I discussed above, where reading data from Google sheets causes some unknown column types that need to be dealt with.

Hi Scott,

“Column Auto Type Cast” node is able to convert the ? columns to String and further I am able to convert them into Integer using “String to Number” node.

Thank you for your help!

Regards
RK

1 Like

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