cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Register for our Discovery Summit 2024 conference, Oct. 21-24, where you’ll learn, connect, and be inspired.
Choose Language Hide Translation Bar
Mcc99
Level I

Subsetting Averages Based on IDs in multiple columns?

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: 

SubjectGroupData AData B
Ax52
Ay02
Ax50
Bz52
Bx52
Bx52

 

Example Output Table: 

SubjectGroupData A AverageData B Average
Ax51
Ay02
Bx52
Bz52

 

I'm using JMP Pro 18 on Windows. 

1 ACCEPTED SOLUTION

Accepted Solutions
SDF1
Super User

Re: Subsetting Averages Based on IDs in multiple columns?

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:

SDF1_0-1727723708818.png

  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!,

DS 

View solution in original post

4 REPLIES 4
SDF1
Super User

Re: Subsetting Averages Based on IDs in multiple columns?

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:

SDF1_0-1727723708818.png

  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!,

DS 

Mcc99
Level I

Re: Subsetting Averages Based on IDs in multiple columns?

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?

@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:

scott_allen_0-1727783943223.png

 

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" )
);

 

-Scott
SDF1
Super User

Re: Subsetting Averages Based on IDs in multiple columns?

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!,

DS