Subscribe Bookmark RSS Feed

Count the number of zeroes in a column (JSL)

warnost

Community Trekker

Joined:

Oct 27, 2011

Hi,

I have been using the Distribution platform to manually analyze a data set, but since I will have to repeat I am now trying to create a JSL script to extract the measures that are most important. The data looks like this:

Model          Score

MODEL1     3.54485

MODEL2     2.36485

MODEL1     5.38456

MODEL2     2.14556

MODEL1     0

MODEL2     0.04856

I would like to extract the mean, std dev, etc. but also I would like to know how often the score is zero for each model. I have tried using summary, tabulate, and a few functions in JSL. I can accomplish everything but the "count zeroes" portion. I can get this number manually by creating the distribution and then doing histogram options >> show counts. Any ideas on how to do it in JSL? Code so far:

dt = Open("C:\Desktop\Results.jmp);

dt << Summary(

  Group( :Model ),

  Median( :score ),

  Mean( :score ),

  Std Dev( :score ),

  Range( :score ),

  );

1 ACCEPTED SOLUTION

Accepted Solutions
Solution

Here is a sligtly simplified version that should give the same result.

dt = Open( "C:\Desktop\results1" || filename || ".jmp" );

dt2 = dt << Summary( Group( :Model ), Median( :score ), Mean( :score ), Range( :score ) );

dt2 << Delete Rows( 2 ) << New Column( "Num Zeroes",

  Numeric,

  Continuous,

  Format( best ),

  <<set each value( N Rows( dt << get rows where( :score == 0 & :Model == "Model1" ) ) )

);


4 REPLIES
asdfasdf

Community Trekker

Joined:

Aug 1, 2012

You can use the summary command and see the counts by grouping on score:


Data Table("Untitled") << Summary(Group(:Score))


or in jsl you could do:


zero_count = 0;



for each row(


    if(:score == 0, zero_count++)


);



show(zero_count);


warnost

Community Trekker

Joined:

Oct 27, 2011

Thank you! I am sure the code below is less than optimal, but I was able to incorporate your code to get the result I wanted.

filename = ""; /*general name so I can repeat for different files*/

dt = Open("C:\Desktop\results1" || filename || ".jmp");

zero_count = 0;

for each row(

    if(:score == 0 & :Model == "Model1", zero_count++)

);

dt << Summary(

  Group( :Model ),

  Median( :score ),

  Mean( :score ),

  Range( :score ),

  );

dt2 = Data Table( "results1" || filename || " By (Model)" );

dt2 << Delete Rows(2); /*I only want Model1 */

dt2 << New Column( "Num Zeroes",

  Numeric,

  Continuous,

  Format( best),

  <<set each value( zero_count ),

  );

Solution

Here is a sligtly simplified version that should give the same result.

dt = Open( "C:\Desktop\results1" || filename || ".jmp" );

dt2 = dt << Summary( Group( :Model ), Median( :score ), Mean( :score ), Range( :score ) );

dt2 << Delete Rows( 2 ) << New Column( "Num Zeroes",

  Numeric,

  Continuous,

  Format( best ),

  <<set each value( N Rows( dt << get rows where( :score == 0 & :Model == "Model1" ) ) )

);


warnost

Community Trekker

Joined:

Oct 27, 2011

Thank you! This is much cleaner.