341170_en-US

liliconcom

How to improve performace when tranforming categorical column into numerical

I extract data, process data along production line, from a database that return data as couples parameter-value (value can be missing).
I use a table split to have data with parameter as column (more missing are expected in many columns).
Then I run a script to convert character to numerical when appropriate.
The problem is that the number of columns can be very high, in my example around 60000 (11 row in the example)
So, in my example, I have 8 seconds to get back data from database,
18 seconds to apply the algorithm that check all column that become 80 seconds to transform the column too.

Can anyone suggest a more efficient way to have the final result?

This is the algorithm that I have used:

names = <FONT face="Courier New" size="3">current data table()</FONT> << Get Column Names( Character, String );
for(I = 1, I <=N items(names), I++,
 values= column( names[I]) << get values;
 numerical=1;
 for(j=1, j<=N items(values),j++,
  if(!Is Missing(values[j]) & Is Missing( Num(values[j]) ),
   numerical=0; 
   break();
  );
 );
 if(numerical,
  column( names[I]) << data Type("Numeric") << set Modeling Type ("Continuous");
 );
);

ScriptingRe: How to improve performace when tranforming categorical column into numerical

Perhaps this works faster?

It avoids the loop through rows and lets JMP's table algorithms work, assuming them to be more efficient.

 

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

// set all columns to character
For( i = 1, i <= N Cols( dt ), i++,
 Column( i ) << set data type( character )
);

wait(3);

// copy data table
dt_copy = dt << Subset( All rows, Selected columns only( 0 ) );

// Convert only numerics, use copied table for testing
For( i = 1, i <= N Cols( dt ), i++,
 Column( dt_copy, i ) << set data type( numeric );
 If( Col N Missing( Column( dt_copy, i ) ) == Col N Missing( Column( dt, i ) ),
  Column( dt, i ) << set data type( numeric ) << set modeling type( continuous )
 );
);

Close(dt_copy, nosave );

Re: How to improve performace when tranforming categorical column into numerical

Very clever method, George! Unfortunately, performances are worse than the one I used. The reason is that it applies column change twice, being
column type change the most time consuming operation, as I confirmed applying the suggestion of Craige to evaluate the statement performances.
I did another analysis, by "anonymize" the table (that means change only character data, both column and row data).
The performance improved from 80 seconds to 55 seconds. This suggest that the strings have a significant impact (8315 character columns versus 55729 numerical)

I attached the comparison between the two methods and the data table I used.