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

The JSL debugger also has a profiler that will show which statements are using the most time. It isn't obvious to me why 60,000 columns by 11 rows should take so long, but the profiler might suggest something. (If I was guessing, I might guess the actual change from character to numeric, not all the looping.)

Using the debugger's profiler -- pick debug script, then press the #clock icon and then press the go button.

Press the #clock icon first

the result

Heat map colors!

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.

Re: How to improve performace when tranforming categorical column into numericalHere is another option to try. After your initial read of the data from your production machine, save the jmp file to a temp location as a csv file. Then turn around and read it back in. You can set the number of rows it reads to determine if the file is numeric or character, and then it reads it in. Since this is using C++ code, there might be a significant time saving.