topic Re: Script to create a summary table with filters in Discussions

Script to create a summary table with filters

EV1976 — Sat, 10 Jun 2023 23:56:40 GMT

Hi all

I have a data table with some numerical columns (VAL1, VAL2) and some char columns (CATEG1, CATEG2, CATEG3).

I want to write a script to crete a summary table reporting the average value of each numerical value grouped by "CATEG1,23"; this easily done:

Data Table( "MyTable" ) <<
Summary(
Group(
:Lot,
:Name( "CATEG1" ),
:Name( "CATEG2" ),
:Name( "CATEG3" ),
),
Mean( :VAL1),
Mean( :VAL2),

Freq( "None" ),
Weight( "None" ),
)

However, I'like also to filter the results according to CATEG1,2,3: e.g. to include only results with CATEG1=="Standard".

I tried brutally inserting a "where" clause, but it doesn't seem to wiork

  where(:CATEG1 == "Standard")

I know that this can be easily done interactively, but I'd like to do it via script.

Thanks

EV1976

Re: Script to create a summary table with filters

jthi — Wed, 09 Nov 2022 09:30:05 GMT

By filtering do you mean you want to delete other rows? If this is the case:

Using Big Class.jmp as example table. Leaving only rows with have :sex value of "M" to summary table

Names Default To Here(1);

dt = Open("$SAMPLE_DATA/Big Class.jmp");
wait(1);
dt_summary = dt << Summary(
	Group(:age, :sex),
	Mean(:height),
	Mean(:weight),
	Freq("None"),
	Weight("None"),
	Link to original data table(0)
);
wait(1);
dt_summary << Delete Rows(dt_summary << Get Rows Where(:sex != "M"));

Using tabulate might also be a good option. You can use local data filter or directly add Where (what I'm doing here)

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");
wait(1);

tab = dt << Tabulate(
	Show Control Panel(0),
	Add Table(
		Column Table(Analysis Columns(:height, :weight), Statistics(Mean)),
		Row Table(Grouping Columns(:sex, :age))
	),
	Where(:sex == "M")
);

Scripting index has quite good suggestions:

Delete Rows:

Get Rows Where:

Select Where:

Re: Script to create a summary table with filters

EV1976 — Wed, 09 Nov 2022 10:11:01 GMT

Both solutions work for me.
"Tabulate" option suits perfectly my needs

Thank you very much

EV1976