topic Re: Is there a quick way to transform column data type? in Discussions

Is there a quick way to transform column data type?

lehaofeng — Tue, 01 Jul 2025 19:53:39 GMT

I have a question for help, thanks.
There is a table with more than 1200 columns, since many data values starting with “i”, there are about 900 columns, so I need to convert the column format from character to numeric, but with a for loop, it's too slow, is there a fast way?

Re: Is there a quick way to transform column data type?

txnelson — Tue, 21 May 2024 11:28:20 GMT

For clarification, when you state 'many data values starting with “i”', you are saying that within a column you have data values such as

i78

If this is what you are referring to, dose the value need to be changed to

178

or just remove the "i" and leave

or just set the value to a missing value

Re: Is there a quick way to transform column data type?

shampton82 — Tue, 21 May 2024 13:36:22 GMT

You could try selecting the columns, right clicking and using

Re: Is there a quick way to transform column data type?

lehaofeng — Wed, 22 May 2024 00:23:30 GMT

There are many values starting with i, we define these datas as invalid values anyway, must be turned into missing values

Re: Is there a quick way to transform column data type?

lehaofeng — Wed, 22 May 2024 00:27:59 GMT

For Each( {col, index}, {:a, :b, :c},
		col << Data Type( numeric ) << 
		Set Modeling Type( "continuous" )
	);

Thanks! But it is still slowly when there are thousand columns with many values.

Re: Is there a quick way to transform column data type?

jthi — Wed, 22 May 2024 05:02:51 GMT

You can try to speed it up by making your data table invisible (open it as invisible/query it as invisible or use << show window(0)) and using << Begin Data Update before and << End Data Update after your loop.

Re: Is there a quick way to transform column data type?

lehaofeng — Wed, 22 May 2024 06:04:01 GMT

col_names=dt<<get column names(string);
st=hptime();
dt<<Begin Data Update;

for each({col,index},col_names,
	column(dt,col)<<data type(numeric)<<set modeling type(continuous)
);

dt << End Data Update;
en=hptime();
show(en-st)

Thanks! It's still slow, 20minutes...

Is there a way to change the format before data importing?

Re: Is there a quick way to transform column data type?

jthi — Wed, 22 May 2024 06:04:44 GMT

Maybe (I don't think you have defined where the data is coming from at all)? In best case you can fix your data at the source