Choose Language Hide Translation Bar
Highlighted
Sakai
Level I

How to compare 1,000 sets of paired data at once?

When analyzing data with more than 1000 sets of paired data, it could not select more than 1000 times, such as A vs B, C vs D, etc…

How can I summarize them in an Excel table, which I can compare all paired data at once?

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: How to compare 1,000 sets of paired data at once?

Below is a simple script that will run for your data.  The script assumes that you have read in the 2 workbooks into JMP, as 2 separate tables.  It then determines the names of the columns in each data table, joins the tables together, and then runs the Matched Pairs platform.  I have attached the 2 JMP tables I used from your Excel workbook. 

Additionally, I created my own sample data table with 3000 matched columns and ran the Matched Pairs platform on all 3000 columns, and it worked without error.

wilconon.PNG

Names Default To Here( 1 );

// Create a pointer to the Pre data table and get
// a list of the columns in it
dt1 = Data Table( "sample data_Sheet1" );
colList1 = dt1 << get column names( continuous );

// Create a pointer to the Post data table and get
// a list of the columns in it
dt2 = Data Table( "sample data_Sheet2" );
colList2 = dt2 << get column names( continuous );

// Join the data tables together
dtJoined = dt1 << Join(
	With( dt2 ),
	By Matching Columns( :ID = :ID ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 0, 0 ),
	Preserve main table order( 1 )
);

// create a new list in the column order required for the Matched Pairs platform
// which is pre1, post1, pre2, post2, etc.
finalList = {};
For( i = 1, i <= N Items( colList1 ), i++,
	Insert Into( finalList, colList1[i] );
	Insert Into( finalList, colList2[i] );
);

// Run the matched pairs
dtJoined << Matched Pairs(
	Y( Eval( finalList ) ),
	Separate Pairs( 1 ),
	Reference Frame( 1 ),
	Wilcoxon Signed Rank( 1 )
);

 

Jim

View solution in original post

4 REPLIES 4
Highlighted
ron_horne
Super User

Re: How to compare 1,000 sets of paired data at once?

Hi @Sakai,

Give us a small sample and an example of the comparison you need. most likely it is not too difficult.

 

 

Highlighted
Sakai
Level I

Re: How to compare 1,000 sets of paired data at once?

Thank you for your reply.

Attached excel file including 2 sheets with the same ID in Column A, respectively.

I am going to compare each same COMPOUND in Sheet1-Pre with Sheet2-Post at once. Each compound includes 243 raw data.

For instance, by using Wilcoxon signed rank test (non-parametric equivalent of the paired t-test), I want to compare the COMPOUND1 of Sheet1-Pre with COMPOUND1 of Sheet2-Post, and COMPOUND2 of Sheet1-Pre with COMPOUND2 of Sheet2-Post, etc. at once.

Highlighted
txnelson
Super User

Re: How to compare 1,000 sets of paired data at once?

Below is a simple script that will run for your data.  The script assumes that you have read in the 2 workbooks into JMP, as 2 separate tables.  It then determines the names of the columns in each data table, joins the tables together, and then runs the Matched Pairs platform.  I have attached the 2 JMP tables I used from your Excel workbook. 

Additionally, I created my own sample data table with 3000 matched columns and ran the Matched Pairs platform on all 3000 columns, and it worked without error.

wilconon.PNG

Names Default To Here( 1 );

// Create a pointer to the Pre data table and get
// a list of the columns in it
dt1 = Data Table( "sample data_Sheet1" );
colList1 = dt1 << get column names( continuous );

// Create a pointer to the Post data table and get
// a list of the columns in it
dt2 = Data Table( "sample data_Sheet2" );
colList2 = dt2 << get column names( continuous );

// Join the data tables together
dtJoined = dt1 << Join(
	With( dt2 ),
	By Matching Columns( :ID = :ID ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 0, 0 ),
	Preserve main table order( 1 )
);

// create a new list in the column order required for the Matched Pairs platform
// which is pre1, post1, pre2, post2, etc.
finalList = {};
For( i = 1, i <= N Items( colList1 ), i++,
	Insert Into( finalList, colList1[i] );
	Insert Into( finalList, colList2[i] );
);

// Run the matched pairs
dtJoined << Matched Pairs(
	Y( Eval( finalList ) ),
	Separate Pairs( 1 ),
	Reference Frame( 1 ),
	Wilcoxon Signed Rank( 1 )
);

 

Jim

View solution in original post

Highlighted
Sakai
Level I

Re: How to compare 1,000 sets of paired data at once?

Hi, @txnelson @ron_horne 

 

Thank you so much!!!

I'll let you know if I have any questions.

Article Labels

    There are no labels assigned to this post.