83857_en-US

liliconcom

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

 

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!

 

Re: How do can splice partial columns in multiple data files into one data table?perfect! Jim are proficient in this area.

Thank Jim!Re: How do can splice partial columns in multiple data files into one data table?

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=""></DELETE>

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

Thank Jim!

I use the JMP pro 14.
I want to do it in one script.
There are over 3,000 files, each of which has over 200,000 rows and 50 columns.

Re: How do can splice partial columns in multiple data files into one data table?1. What version of JMP are you running?
2. Do you want the solution to be in the form of a JMP Script, or to run this interactively?
3. Approximately how many rows and columns are there per input file and how many files are there?Re: How do can splice partial columns in multiple data files into one data table?As with multiple Big class.jmp, each file simply selects data from columns 3 and 5, splicing multiple files to produce new tables with many rows and only two columns.Thank you very much!Re: How do can splice partial columns in multiple data files into one data table?

The details will depend on the specifics of your use case. But I would start by reviewing what multiple file import can do (requires JMP 14).