How do can splice partial columns in multiple data files into one data table?

lwx228

 

Hello, everyone!

 

There are many data files in path C:\1. They have the same column name structure with 50 columns.How to use the JSL implementation to combine the data in columns 3 and 5 of these files into a new data table.

 

Setting: the format of column 3's Data Type is Character, and the format of column 5's Data Type is numeric.

 

Like Big class.jmp, but with 50 columns.

 

Thanks!

 

txnelson

Here is a good start to do what you want.......be careful, given the parameters you gave about the tables you are going to read in, the final table will be 600,000,000 rows in length.  It will take a long time to process that amount of data.

Names Default To Here( 1 );

// Set the path to the folder to get the files from
// Make sure a "/" is the last character in the path
// Change the value below to the actual path you need to point to
dirPath = "$SAMPLE_DATA/";

// Get all of the files from the folder
allFilesList = Files In Directory( dirPath );

// set the pointer to the final data table to a null value
dt = "";

// Loop across all of the files and concatenate them together
For( i = 1, i <= N Items( allFilesList ), i++,
	// Open the file
	dtIn = Open( dirPath || allFilesList[i], invisible );
	dtIn << run formulas; // make sure all records are run before continuing the process
	
	// In my test case, I make sure there are at least the 5 columns
	If( N Cols( dtIn ) >= 5,
	
		// Make sure the 3rd and 5th columns are of the correct type, and give them a standard name
		Column( dtIn, 3 ) << data type( character ) << set name( "__3__" );
		Try( Column( dtIn, 3 ) << delete property( "formula" ) );
		Column( dtIn, 5 ) << data type( numeric ) << modeling type( continuous ) << set name( "__5__" );
		Try( Column( dtIn, 3 ) << delete property( "formula" ) );
		Wait( 0 );
		
		// Delete all columns except the 3rd and 5th columns
		dtIn << delete columns( Index( 6, N Cols( dtIn ) ) );
		Wait( 0 );
		dtIn << delete columns( 4 );
		Wait( 0 );
		dtIn << delete columns( {1, 2} );
		Wait( 0 );
		
		// If this is the first data table, set the variable dt to point to the data table
		If( dt == "",
			dt = dtIn,
			// If not the first data table, then contatenate it to the first table
			Try( dt = dt << concatenate( dtIn, Append to first table( 1 ) ) );
			// Close the table just read into JMP since we are done with it
			Close( dtIn, nosave );
			//  This is a check I found I needed to make to insure only the 3rd and 5th columns are left
			If( Ncol(dt)>2,
				dt<<delete columns(index(3,ncol(dt)));
				wait(0);
			)
		);
	,
		Close( dtIn, nosave )
	);
	
);

// The data table is read in as an invisible table through the processing, 
// and now that the processing is complete, make the table visible
dt << show window( 1 );