String Manipulation (Multi Column) with many columns

KNIME Analytics Platform
1

Hello KNIMErs,

I’m a little bit confused. Is it a bug or a feature?
I have to replace letters in a huge table wit round about 6000+ columns.
I want to use the String Manipulation (Multi Column) Node to do this job with regexReplacer-function

regexReplace($$CURRENTCOLUMN$$, “[a-zA-Z]”, “”)

But this expression throws an error
image

If I relace $$CURRENTCOLUMN$$ with only one column name it works.
Has anybody an idea what the problem is?

Regards,
Brotfahrer

2

Hi,
as a workaround you could transform this very wide table to a very long table with “unpivot”. Then you can replace all letters with the string manipulation node or string cleaner node.

4 Likes
3

Hi @ActionAndi ,

thanks for the hint!
This is now my workaround

image
image963×394 16.9 KB

Unfortunatlly the workaround takes a waste of time but it works :wink:

Regards,
Brotfahrer

2 Likes
4

or, you actually read the warning message, specifically the part

is not applicable for the arguments (java.lang.Double, …)

you are trying to use a STRING manipulation on a column that is no STRING but a DOUBLE (number)

5

Hi @fe145f9fb2a1f6b ,

that’s maybe the problem. I use the CSV Reader Node to import time series data which ar normally double values. I noticed in some channels is text like “nan” so the CSV Reader Node crashed because it requests double values and “nan” is not a double value. So I tried to read all data as string. When I import my data as string KNIME shows me in the table specs that all columns are string values…

The workaround is working, very slow, but it’s working. So my workstation has something to do over the Easter weekend :laughing:

Regards,
Brotfahrer

6

different solutions are possible which will all have a better performance compared to pivoting:

  1. use a proper file format (e.g. parquet) that carries data type information or a database, instead of text files

  2. read it in as string, run a string Replacer to get rid of “NaN” or the String cleaner and remove everything which isnt a number/dot/comma (assuming you aren’t using scientific notation like 2E10. then just String to Number convert

given 1) is usually not possible, i would recommend to use the String Cleaner Node

1 Like
7

Thanks for the String Cleaner Node. I didn’t know it. I will try it because it’s much more faster than my workaround.

To use a proper file format is very difficult. I have to convert MF4-Files. KNIME’s MF4-Reader Node doesn’t work properly so the only way I know ist to convert the MF4 files to CSV

8

Ah you should have mention that. Just use a Python node to load the mf4 files. Much quicker

9

Here’s a very simple example:

I ran these asammdf libraries with quite large and complex mdf files successfully. So give it a try :slight_smile:

First of all, you need to create a python environment and install asammdf packages:
Introduction — asammdf “7.3.14” documentation

10

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