351705_en-US

liliconcom

Col Functions and Row States

According to the release notes for version 15 of JMP:

The Col functions honor excluded rows through the Excluded(Row State()) function.
Col Maximum( :height, :sex, Excluded( Row State() ) );

However, this syntax doesn't seem to work.  Can anyone suggest the corect syntax?

ScriptingRe: Col Functions and Row States

I think that it is the same concept as the one behind binning. You are mapping one value to another for each observation, in this case, a group value. It might be a bin. It might be a measure of central tendency.

Re: Col Functions and Row States

OK thanks.  I can't really get my head around the idea of generating a column statistic for every row of the table but I guess there must be use-cases.  I'll stick with using a user-defined function to calculate column stats whilst taking into account  row exclusions.

Re: Col Functions and Row States

According to the documentation, when a By variable is specified the function should be used in a column formula or For Each Row():

(Optional) A By variable to compute statistics across groups of rows. Use the By variable in a column formula or in a For Each Row() function.

In reviewing some of the examples from earlier in this post, I would agree that it appears to be treating excluded values as their own By level.

 

Re: Col Functions and Row States

It looks like it is used with a column formula where the Row() function is defined as it is evaluated.

Re: Col Functions and Row States

On one computer it works:

 

 

And on the another it fails:

 

 

Re: Col Functions and Row States

Working with Big Class (of course):

 

colmaximum(:height,excluded(rowstate()))

This is working.  But on one computer but not another.  Both running 15.1.0. 

 

Now I am totally confused!

 

Screenshots on next post ...

Re: Col Functions and Row States

Interesting. A pre-release build of JMP 16 seems to get it right for r=1 and r=2, but not thereafter:

NamesDefaultToHere(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");

mw = J(NRow(dt), 2, .);
for(r=1, r<=NRow(dt), r++,
 Row() = r;
 RowState(Row()) = ExcludedState(1);
 mw[r,1] = ColMean(:weight, Excluded(RowState(Row())));
 mw[r,2] = ColMean(:weight, Excluded());
 RowState(Row()) = ExcludedState(0);
 );
AsTable(mw);

dt << Distribution(
   Continuous Distribution(
    Column( :weight ),
    Quantiles( 0 ),
    Histogram( 0 ),
    Outlier Box Plot( 0 )
   ),
   Local Data Filter(
    Add Filter(
     columns( Transform Column( "Row", Nominal, Formula( Row() ) ) ),
     Where( Transform Column( "Row", Nominal, Formula( Row() ) ) == 1 ),
     Display(
      Transform Column( "Row", Nominal, Formula( Row() ) ),
      N Items( 15 ),
      Find( Set Text( "" ) )
     )
    )
   )
  );

Re: Col Functions and Row StatesDave,
Very confusing. I think this is a question for our friend Wendy.Re: Col Functions and Row States

I can code a solution - my question was specifically about whether version 15 allowed it to be done solely using the column functions - that was the impression I got from the release notes.

Re: Col Functions and Row States

Maybe honoring means in this case that the excluded is just additional byVar in column statistics formulas?

 

If you want maximum value of column without excluded values, could you use Summarize instead of Col Maximum? It seems to ignore excluded rows.

 

Names Default To Here(1);
dt =New Table("ExcludeColMax",
 Add Rows(6),
 Compress File When Saved(1),
 New Column("A",
  Numeric,
  "Continuous",
  Format("Best", 12),
  Set Values([1, 10, 100, 2, 20, 200]),
  Set Display Width(46)
 ),
 New Column("B",
  Character,
  "Nominal",
  Set Values({"A", "A", "A", "B", "B", "B"})
 ),
 New Column("C",
  Numeric,
  "Continuous",
  Format("Best", 12),
  Formula(Col Maximum(:A, :B, Excluded())),
  Set Display Width(65)
 ),
 Set Row States([0, 0, 6, 6, 0, 6])
);

Write("Looping col max:");
For Each Row(
 Show(Col Maximum(:A, :B, Excluded()))
);

Write("\!N\!NOnly col max:"); 
Show(Col Maximum(:A, :B, Excluded()));

Write("\!N\!NSummarize:");
Summarize(dt, exg = By(:B), exm = Max(:A));
Show(Eval List({exg, exm}));
Show(Max(exm));

Re: Col Functions and Row States

I've tested with v15.2 and the issue is the same.  Note the use case is using the function within a script, not within a column formula (not sure why I would want a column statistic in a row).

Re: Col Functions and Row States

I'll add this very simple example here if anyone else comes around:

 

Names Default To Here(1);
New Table("ExcludeColMax",
 Add Rows(6),
 Compress File When Saved(1),
 New Column("A",
  Numeric,
  "Continuous",
  Format("Best", 12),
  Set Values([1, 10, 100, 2, 20, 200]),
  Set Display Width(46)
 ),
 New Column("B",
  Character,
  "Nominal",
  Set Values({"A", "A", "A", "B", "B", "B"})
 ),
 New Column("C",
  Numeric,
  "Continuous",
  Format("Best", 12),
  Formula(Col Maximum(:A, :B, Excluded())),
  Set Display Width(65)
 ),
 Set Row States([0, 0, 6, 6, 6, 0])
);

Re: Col Functions and Row States

Thanks for letting me know.  That doesn't work with 15.1 - I'll try and do a comparison with 15.2.

Re: Col Functions and Row States

I quickly tested and seemed that using only Excluded() seemed to work (I'm using 15.2.1).