topic Re: graphically compare data results in datafolders in Discussions

graphically compare data results in datafolders

PerttiPasanen — Sun, 11 Jun 2023 11:13:28 GMT

Hi,

I am fairly new to JMP and looking to do following, quite challenging operation automatically, preferably via automated JMP script or any other solution you experts may recommend :).

My data:

I have two folders of results, datafolder1 and datafolder2. They are technically the same measurements, there is a minor difference done the the setup and same measurements are repeated after and before the change.

Each folder has multiple .txt files that are the result files in .txt table format. The .txt files have same names for each folder, and the column names are the same. In practice, only the numeric data is slightly different for each file.

What i want:

I want to graphically compare difference of result datafolder1 vs result datafolder2 for each column in with same name of .txt and generate .pdf output of the results. I managed to generate the output that i wanted in .pdf format, but had to 8-12 hours of manual work to get that output with graph builder. The output file is called "wanted_output.pdf". Now i got many other similar cases to do, and doing this by manual would take many months to do.

the data files are as attachment. [data has since been removed]

Thank you in advance, been stuggling with this issue for months, and I hope JMP has simple solution for this!

Re: graphically compare data results in datafolders

jthi — Mon, 26 Apr 2021 08:37:59 GMT

File / Import Multiple Files could get you started fairly easily. You will have to do some changes to default settings when importing the files (change separator from comma to tab).

From this point you can start joining the datatables. I'm not sure if there is any easy way to do this except for JSL and looping over the list of datatables you just got from previous step.

Repeat this process for both folders and then concatenate datatables together and finally create graph with graph builder (you might need to stack the data before creating graph).

Re: graphically compare data results in datafolders

PerttiPasanen — Mon, 26 Apr 2021 09:55:18 GMT

Thank you Jarmo for quick reply!

I indeed need an JSL to do this all, its too time consuming to do this for multiple other cases.

In architectural view, following JSL could workout:

1.Import all the files from both folder.

2. Join/&concatenate results into one file, JMP seems to automatically add some kind of indexing when table name is the same (of XXXX).

3. Sort the column names in alphabetical order.

4. plot column 1&2 into one plot, plot column 3&4 for next plot, plot column 5&6 for next plot... repeat plotting following numbers until there are no columns left.

It all sounds easy, but I have only beginner understanding of JSL, any tips and examples for each steps?

Re: graphically compare data results in datafolders

jthi — Mon, 26 Apr 2021 10:03:58 GMT

Could you send me a private message in JMP Community? I can help maybe a bit easier through email and then we can possibly post the final solution here.

Re: graphically compare data results in datafolders

txnelson — Tue, 27 Apr 2021 05:04:56 GMT

I had to guess at a few things, but the script below creates a separate pdf for each of the files found in the datatable folders. I modified the graph format a bit. It shows all of the same information, as I can best determine, but in a slightly different form.

In the script, you will need to put in the paths to folder 1 and folder 2. Currently, the pdf's are being save in the $TEMP directory. You will need to change them to a real folder.

Names Default To Here( 1 );

// Set folder1 and folder2 paths

folder1 = "path to folder 1";
folder2 = "path to folder 2";

// Get files in datafolder 1 folder
fileNamesList = Files In Directory( folder1 );

For( File = 1, File <= N Items( fileNamesList ), File++, 

// Open the file in datafolder1
	dt1 = Open(
		folder1 || "/" || fileNamesList[File],
		Import Settings(
			End Of Line( CRLF, CR, LF ),
			End Of Field( Tab, Comma, CSV( 1 ) ),
			Strip Quotes( 1 ),
			Use Apostrophe as Quotation Mark( 0 ),
			Use Regional Settings( 0 ),
			Scan Whole File( 1 ),
			Treat empty columns as numeric( 0 ),
			CompressNumericColumns( 0 ),
			CompressCharacterColumns( 0 ),
			CompressAllowListCheck( 0 ),
			Labels( 1 ),
			Column Names Start( 1 ),
			Data Starts( 2 ),
			Lines To Read( "All" ),
			Year Rule( "20xx" )
		)
	);

// New column: Index
	dt1 << New Column( "Index", Numeric, "ordinal", Format( "Best", 12 ), Set Each Value( Row() ) );

// New column: Index
	dt1 << New Column( "Measured", Character, Set Each Value( "Before" ) );

// Open the file in datafolder2
	dt2 = Open(
		folder1 || "/" || fileNamesList[File], 
		Import Settings(
			End Of Line( CRLF, CR, LF ),
			End Of Field( Tab, Comma, CSV( 1 ) ),
			Strip Quotes( 1 ),
			Use Apostrophe as Quotation Mark( 0 ),
			Use Regional Settings( 0 ),
			Scan Whole File( 1 ),
			Treat empty columns as numeric( 0 ),
			CompressNumericColumns( 0 ),
			CompressCharacterColumns( 0 ),
			CompressAllowListCheck( 0 ),
			Labels( 1 ),
			Column Names Start( 1 ),
			Data Starts( 2 ),
			Lines To Read( "All" ),
			Year Rule( "20xx" )
		)
	);

// New column: Index
	dt2 << New Column( "Index", Numeric, "Ordinal", Format( "Best", 12 ), Set Each Value( Row() ) );

// New column: Index
	dt2 << New Column( "Measured", Character, Set Each Value( "After" ) );

	// Concatenate tables
	// → Data Table( "HW.DiodeTest.ADP_diodes" )
	dt1 << Concatenate( dt2, Append to first table );

	// Close the After data table
	Close( dt2, nosave );

	// get all continuous columns in the data table
	colNamesList = dt1 << get column names( continuous, string );

	theJournal = New Window( fileNamesList[File], <<journal );
	theJournal << show window(0);

	// Loop through all of the columns
	For( graph = 1, graph <= N Items( colNamesList ), graph++,
		gb = Graph Builder(
			Size( 531, 308 ),
			Show Control Panel( 0 ),
			Variables( X( :Index ), Y( As Column( colNamesList[graph] ) ), Overlay( :Measured ) ),
			Elements( Line( X, Y, Legend( 14 ) ) ),
			SendToReport(
				Dispatch(
					{},
					"Index",
					ScaleBox,
					{Min( -0.5 ), Max( 47.513842007589 ), Inc( 10 ), Minor Ticks( 0 )}
				),
				Dispatch(
					{},
					"400",
					ScaleBox,
					{Legend Model(
						14,
						Properties( 0, {Line Width( 4 )}, Item ID( "After", 1 ) ),
						Properties( 1, {Line Width( 4 )}, Item ID( "Before", 1 ) )
					)}
				),
				Dispatch( {}, "graph title", TextEditBox, {Set Text( "" )} ),
				Dispatch( {}, "400", LegendBox, {Sides( "Top" )} )
			)
		);
		Report( gb )[Outline Box( 1 )] << set title( colNamesList[graph] );

		theJournal << append( Report( gb ) );
		gb << close window;	
	
	);
	
	// Save the journal
	theJournal << save pdf( "$TEMP/" || fileNamesList[File] || ".pdf", portrait( 1 ) );
	
	// Close the journal
	theJournal << close window;
	
	// clean up the workspace by closing the current data table
	Close( dt1, nosave );
);

open("$TEMP/" ||fileNamesList[2]||".pdf")

Re: graphically compare data results in datafolders

ian_jmp — Mon, 26 Apr 2021 12:43:33 GMT

And, as an alternative to @txnelson's approach, and following the original idea (and using the 'Include Subfolders' option):

NamesDefaultToHere(1);

// Unzip the example file 'net_data.zip' to your Desktop

// Generated via 'File > Open Multiple..': tabList is a list of 'paired' tables
// that are built automatically according to the commonality of column names 
tabList = 
Multiple File Import(
	<<Set Folder( "$DESKTOP/net_data/" ),
	<<Set Show Hidden( 0 ),
	<<Set Subfolders( 1 ),
	<<Set Name Filter( "*.txt;" ),
	<<Set Name Enable( 1 ),
	<<Set Add File Name Column( 1 ),
	<<Set Import Mode( "CSVData" ),
	<<Set Charset( "Best Guess" ),
	<<Set Stack Mode( "Stack Similar" ),
	<<Set CSV Has Headers( 1 ),
	<<Set CSV Allow Numeric( 1 ),
	<<Set CSV First Header Line( 1 ),
	<<Set CSV Number Of Header Lines( 1 ),
	<<Set CSV First Data Line( 2 ),
	<<Set CSV EOF Comma( 0 ),
	<<Set CSV EOF Tab( 1 ),
	<<Set CSV EOF Space( 0 ),
	<<Set CSV EOF Spaces( 0 ),
	<<Set CSV EOF Other( "" ),
	<<Set CSV EOL CRLF( 1 ),
	<<Set CSV EOL CR( 1 ),
	<<Set CSV EOL LF( 1 ),
	<<Set CSV EOL Semicolon( 0 ),
	<<Set CSV EOL Other( "" ),
	<<Set CSV Quote( "\!"" ),
	<<Set CSV Escape( "" )
) << Import Data;

// Loop over each table, and add a sequential 'ID' column, grouped by the originating file
// WARNING: You may need more error checking!
 for (t=1, t<=NItems(tabList), t++,
 	dt = tabList[t];
 	nUnits = NRow(dt)/2;
 	ids = Repeat(Transpose(1::nUnits), 2);
 	dt << NewColumn("ID", Numeric, Continuous, SetValues(ids));
	);

As usual, the integrity of the final results depends on what you can (and can't) assume about the regularity of the data.

Re: graphically compare data results in datafolders

PerttiPasanen — Mon, 26 Apr 2021 15:11:16 GMT

Hi txnelson and Ian

Works like a charm! Thank you!

I had to change datafolder2 variable from folder1 to folder2.

Also for some reason .pdf printing didnt scale well, I had to change default printing settings File=>Page setup.

Problem solved, thanks for everybody help!

PS. I had to remove data from first post, but technically any before and after type of data folders works with this script.