Choose Language Hide Translation Bar
Highlighted
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
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.

``````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
4 REPLIES 4
Highlighted
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
Level I

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

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
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.

``````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
Highlighted
Level I

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

Thank you so much!!!

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

Article Labels

There are no labels assigned to this post.