topic Re: Subsetting Averages Based on IDs in multiple columns? in Discussions

Subsetting Averages Based on IDs in multiple columns?

Mcc99 — Mon, 30 Sep 2024 18:24:24 GMT

I need a JSL script to take averages from multiple columns of data and subset them into a separate data table. However I need the averages to be grouped by multiple categorical identifiers. For example, for the data table below I would need the averages of SubjectA Group X, and Subject A Group y, as separate lines as shown in the second example table. Something similar to this previous question. How would I modify the script to include averages for multiple columns? If I need to make the average columns in the source data table and then subset out separately that would work as well.

Example Source Table:

Subject	Group	Data A	Data B
A	x	5	2
A	y	0	2
A	x	5	0
B	z	5	2
B	x	5	2
B	x	5	2

Example Output Table:

Subject	Group	Data A Average	Data B Average
A	x	5	1
A	y	0	2
B	x	5	2
B	z	5	2

I'm using JMP Pro 18 on Windows.

Re: Subsetting Averages Based on IDs in multiple columns?

SDF1 — Mon, 30 Sep 2024 19:20:07 GMT

Hi @Mcc99 ,

There's a couple different ways to do this, but one of the easiest, might be using the Tabulate platform.

Using the sample data table that you provided, you can do it with just a little bit of JSL:

Tabulate(
	Add Table(
		Column Table( Analysis Columns( :Data A, :Data B ), Statistics( Mean ) ),
		Row Table( Grouping Columns( :Subject, :Group ) )
	)
);

The corresponding output from the Tabulate platform looks like this:

Which I believe is exactly what you're after. You can of course add more statistics to include or perform the Tabulate in other ways, but I believe this is what you want. You can then click Done and with the red hot button drop-down menu, you can select Make Into Data Table, and then get your data table from the platform (you can also use JSL to do all this for you).

It looks like the previous post you referenced used a column formula instead, which was the other way I would have suggested, but it's already been answered there, so you can always review that post in more detail if that's the way you want to go. But, I really think the Tabulate platform is what you want -- and you can play around with the different grouping columns and so forth to see how the data changes.

Hope this helps!,

Re: Subsetting Averages Based on IDs in multiple columns?

Mcc99 — Mon, 30 Sep 2024 20:27:55 GMT

Thanks! I didn't even think to use tabulate but it makes my script much cleaner than the column formula. However, I'm having trouble saving the tabulate table as a new data table. I added the script below, after the tabulate script above. Although the tabulate window opens the new data table doesn't.

<< Make Into Data Table(Outputtable("Average data"));

Re: Subsetting Averages Based on IDs in multiple columns?

scott_allen — Tue, 01 Oct 2024 12:04:46 GMT

@Mcc99

Another option you could consider is using Table > Summary. The Preview pane helps make sure you have the correct output, especially when you are grouping by multiple columns.

Tables > Summary:

Source Script:

Data Table( "Data Table Name" ) << Summary(
	Group( :Subject, :Group ),
	Mean( :Data A ),
	Mean( :Data B ),
	Freq( "None" ),
	Weight( "None" ),
	output table name( "Summary of Data Table Name by Subject, Group" )
);

Re: Subsetting Averages Based on IDs in multiple columns?

SDF1 — Tue, 01 Oct 2024 15:19:28 GMT

Hi @Mcc99 ,

Are you sure you're referencing your objects correctly in JSL? If you use the below code (assuming you're working with the data table structure like in your example, it will generate the correct tabulated data table using Tabulate, then make it into a data table with the given name and also close the Tabulate window.

Names Default To Here( 1 );

dt = Current Data Table();

dttab = dt << Tabulate(
	Add Table(
		Column Table( Analysis Columns( :Data A, :Data B ), Statistics( Mean ) ),
		Row Table( Grouping Columns( :Subject, :Group ) )
	)
);

dttab << Make Into Data Table( OutputTable( "Average Data" ) );

dttab <<Close Window;

Hope this helps!,