topic Batch processing files to extract data into a summary table in Discussions

Batch processing files to extract data into a summary table

thedellette — Fri, 09 Jun 2023 17:06:12 GMT

I have multiple files that I want to perform some analysis on. I want to create a loop to open the file, perform the analysis, calculate a value "area under a curve" in this example and then make a summary table with the file name and area. I can open the files from a folder and perform the analysis. I am not sure how to create the summary table. I have given an example with 2 files but I have many more files I would like to process in this way. Any help or guidance is greatly appreciated! Also please let me know if this does not make sense or if more information is needed.

//point to file directory

path = Pick Directory( "Select Directory Containing Data Files" );

//Gets the list of files in the selected file folder
file_list = Files In Directory( path );

//i=0;open file
nfile_list = N Items( file_list );
For( i = 1, i <= nfile_list, i += 1, 
	//Set Current Directory( dir_DirectoryPath );
	dt = Open( path || file_list[i], );
	//Calculate area
	dt = Current Data Table();
	dt << clear row states;
	obj3 = dt << Bivariate(
		Y( :Name( "Data" ) ),
		X( :Time ),
		Fit Polynomial( 6, {Line Color( {212, 73, 88} )} ),
		SendToReport( Dispatch( {}, "Bivar Plot", FrameBox, {Frame Size( 449, 411 )} ) )
	);
	//save graph to data dable
	obj3 << Save Script to Data Table( "Area" );
	//get the prediction formula string and calculate the area under the curve for 30 minute adsoription
	fm = Report( obj3 )[Outline Box( "Polynomial ?" )][Text Edit Box( 1 )] << get text;
	//get the string to the right of the equal sign and convert the string to an expression using Parse()
	fm = Parse( Trim( Word( 2, fm, "=" ) ) );
	area = Integrate( fm, :Time, 0, 3600 );
	Caption( "The area under this curve is " || Char( area ) );
	Print( area );
);

Example of Final Table I would like to have:

Any help with this would be greatly appreciated. Thanks

Re: Batch processing files to extract data into a summary table

jthi — Mon, 01 Aug 2022 19:55:18 GMT

Before you start looping, create new table with File Name and Area columns and format them as you want them to be. Easiest way is to create new table and then use Copy Table Script. Also remember to assign reference variable to that table!

Then in the loop after you have calculated the area you can get the table name by using << Get Name and then add the new rows to your collection table with << Add Rows(). Also remember to close unnecessary tables and reports after getting the relevant information from them.

Names Default To Here(1);

dt_collector = New Table("Untitled",
	Add Rows(0),
	New Column("File Name", Character, Nominal, Set Values({})),
	New Column("Area", Numeric, "Continuous",  Set Values([]))
);

// For(..
//	...
	//dt_name = dt << Get Name;
	dt_name = "table";
	area = 123;
	dt_collector << Add Row({File Name = dt_name, Area = area});
//);

Re: Batch processing files to extract data into a summary table

txnelson — Mon, 01 Aug 2022 20:04:00 GMT

I have added code that will create a new data table, and will write the file name and area to the new table

Names Default to Here( 1 );
//point to file directory

path = Pick Directory( "Select Directory Containing Data Files" );

//Gets the list of files in the selected file folder
file_list = Files In Directory( path );

// Crete beginnings of output data table
dtFinal = New Table( "Final", New Column( "File Name", Character ), New Column( "Area" ) );

//i=0;open file
nfile_list = N Items( file_list );
For( i = 1, i <= nfile_list, i += 1, 
	//Set Current Directory( dir_DirectoryPath );
	dt = Open( path || file_list[i], );
	//Calculate area
	dt = Current Data Table();
	dt << clear row states;
	obj3 = dt << Bivariate(
		invisible,
		Y( :Name( "Data" ) ),
		X( :Time ),
		Fit Polynomial( 6, {Line Color( {212, 73, 88} )} ),
		SendToReport( Dispatch( {}, "Bivar Plot", FrameBox, {Frame Size( 449, 411 )} ) )
	);
	//save graph to data dable
	obj3 << Save Script to Data Table( "Area" );
	//get the prediction formula string and calculate the area under the curve for 30 minute adsoription
	fm = Report( obj3 )[Outline Box( "Polynomial ?" )][Text Edit Box( 1 )] << get text;
	//get the string to the right of the equal sign and convert the string to an expression using Parse()
	fm = Parse( Trim( Word( 2, fm, "=" ) ) );
	area = Integrate( fm, :Time, 0, 3600 );
	Caption( "The area under this curve is " || Char( area ) );
	Print( area );
	
	// Add information to Final data table
	dtFinal << add rows( 1 );
	dtFinal:File Name[N Rows( dtFinal )] = file_list[i];
	dtFinal:area[N Rows( dtFinal )] = area;
);

Re: Batch processing files to extract data into a summary table

thedellette — Mon, 01 Aug 2022 22:36:45 GMT

Thank you! You gave a nice detailed explanation as well. Easy to follow :)

Re: Batch processing files to extract data into a summary table

thedellette — Mon, 01 Aug 2022 22:37:44 GMT

Thank you! I saw the other reply first but I like this one as well.