topic Re: JSL: How to loop through columns, generate a Graph Builder report, and save each as a JPG file in Discussions

JSL: How to loop through columns, generate a Graph Builder report, and save each as a JPG file

abdulj — Fri, 22 Mar 2019 13:01:52 GMT

I'm trying to create a script that can loop through each cell of each column to count the number of cells that are less or greater than a specified value.

If i have a table of columns A ~ D and Rows 1 ~ 10, I'd like to count the values of the cells row-wise and column-wise. In other word, for all rows of column A, how many meet my criteria. And similarly for all columns of Row 1, how many meet my criteria. I would then do this for all rows and columns.

Here's what ive tried to start with, but it returns nothing. Any ideas on this or a more effcient way to do it?

Names Default To Here( 1 );

dt = Current Data Table();
Clear Log();


// Get all of the numeric columns in the data table
Numeric Columns = dt << get column names( numeric, string );
NRows = N Rows( dt );

Count = {};

//Column loop
For( i = 1, N Items( Numeric Columns ) <= 1, i++, 
	
	For Each Row(
		dt, 

		If( Column(dt, Numeric Columns[i]) < 101, 
			
			Count = Count++;
			Show( Count );
		) 
		 	
	)
		
	
);

Re: JSL: How to loop through columns, generate a Graph Builder report, and save each as a JPG file

uday_guntupalli — Fri, 22 Mar 2019 14:46:31 GMT

@abdulj ,
You could use matrices to identify conditions that you want to test against. Here is an example,

Clear Log(); 

// Open a sample data set 
dt = Open( "$SAMPLE_DATA/Cars.jmp" );

// Extracting numeric column names 
ColNames = dt << Get Column Names("String");
NumericCols = []; 
for(nCol = 1, nCol <= N Items(ColNames), nCol++,
	DType = Column(dt,nCol) << Get Data Type; 
	If(DType == "Numeric",
		NumericCols = NumericCols || nCol; 
	  );
   );   
NumericColNames = ColNames[NumericCols];

// Extract numeric data only
Mat = dt << Get As Matrix; 

// Row Wise Test 
// For e.g. Find all rows where year < 90 
YearCondition = Loc(Mat[0,1] < 90); 

Show(Mat[YearCondition,0]);

Re: JSL: How to loop through columns, generate a Graph Builder report, and save each as a JPG file

Melanie_J_Drake — Fri, 22 Mar 2019 15:14:45 GMT

There are a few problems with the script that are easily fixed.

1. The test portion of the for loop is wrong:

N Items( Numeric Columns ) <= 1

The number of Numeric columns doesn't change. If there are 0 or 1 numeric columns, the loop will run forever because 0<=1 and 1<=1. If there are at least 2 numeric columns, then the loop doesn't run at all because 2(or more)<=1 is always false. The test should be this:

i <= N Items( Numeric Columns )

That tests whether your iterator (i) is greater than the number of columns and stops when it is.

2. In a For Each Row loop, you still need to subscript into the row, you just don't have to manage the iteration yourself. So this piece:

Column(dt, Numeric Columns[i]) < 101

Should look like this:

Column(dt, Numeric Columns[i])[Row()] < 101

3. Your Count variable is a list, but you're treating it like a number. Count++ doesn't do anything to a list that isn't populated with numbers. Set Count = 0 instead of Count = {}. Additionally, Count++ returns the current value, and then sets Count to Count +1. So Count = Count++ will never change the value of Count. (Example: Count = 0. Count = Count++. Count is still equal to zero.)

Here is the full script with the changes above. The end Count is the number of cells in all numeric columns that are less than 101.

Names Default To Here( 1 );

dt = Current Data Table();
Clear Log();


// Get all of the numeric columns in the data table
Numeric Columns = dt << get column names( numeric, string );
NRows = N Rows( dt );

Count = 0;

//Column loop
For( i = 1, i <= N Items( Numeric Columns ), i++, 
	
	For Each Row(
		dt, 

		If( Column(dt, Numeric Columns[i])[Row()] < 101, 
			
			Count++;
			Show( Count );
		) 
		 	
	)
		
	
);

HTH,

Melanie

Re: JSL: How to loop through columns, generate a Graph Builder report, and save each as a JPG file

bfoulkes — Tue, 26 Mar 2019 12:55:13 GMT

You could also replace the "For each row" section with a line to count the number of rows for each column. This would utilize the datatable functions instead of needing to look through each row of each column.

COUNT_VAL=NROWS((DT<<SELECT WHERE(column(dt, Numeric Columns[i])[]<101))<<GET SELECTED ROWS);