nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

How to improve performace when tranforming categorical column into numerical

antonio-domenic

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");
	);
);

Accepted answers

txnelson

Here 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.

All comments

Georg

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 );

Craige_Hales

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!

antonio-domenic

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.

comparison.pptx

Example_Anonymized.jmp

txnelson

Georg

Ok, so it was just a try, thanks for evaluating and presenting results.

If you retrieve the data from database, why not to shape the data exactly there?

With SQL this all can be done even much more efficiently, slightly depending on dialect.

This is Always my strategy, to do as much shaping on database as possible, because there are larger ressources (Memory, CPU, data structures), and unnecessary steps can be eliminated. But sometimes it is not possible ...

Craige_Hales

If you try Jim's suggestion (csv temp file) try Multiple File Import and see if it gets the answer you want. MFI makes character columns when it finds data it doesn't believe is numeric.

antonio-domenic

Thank you Jim, excellent! I tried your option and the improvement was great. 18 seconds respect the previous 80 seconds. The only difference was the handling of some dates in epoch format (from old Unix equipments). Using csv, some of these dates remain character type while the previous method convert to numeric (of course they are numbers in epoch format that need a specific function to convert as date). Anyway is not a big issue.

antonio-domenic

Yes I agree, I will check with our experts if it is possible in our case (the requested data is spread in a bunch of different selections). Thank you.