topic Re: Concatenate selected row from multiple subsetted data tables into 1 new data table in Discussions

Concatenate selected row from multiple subsetted data tables into 1 new data table

bzanos — Fri, 09 Jun 2023 19:51:37 GMT

Hi,

I have a data set(as example, Big Class) that contains all the data. I subset the data by age and select first row of each 
subsetted data table. Then, I subset it again so that I can do concatenation of the selected row data into new table.  

I notice that there is additional data (unwanted data ) concatenated in the new table (highlighted in red rectangular). 
I am not sure what is the problem of my script. 
I attach my script in below.
I really appreciate if anyone could help me to fix this issue.

// Open Big Class data table
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

//Subset the data table by age
listDT = dt << Subset(
	By( :age ),
	All rows,
	Selected columns only( 0 ),
	columns( :name, :sex, :height, :weight )
);

//Select first row of each subset data table 
For( i = 1, i <= N Items( listDT ), i++,
      
    listDT[i] << Select Rows( {1} );
    listDT[i] << Subset("");
    
    Close(listDT[i],"No Save"); 
    Close(dt,"No Save"); 

);

// Create Tables list
Tables = {};
For( i = 1, i <= N Table(), i++,
	Insert Into( Tables, Data Table( i ) << get name );
);

//Create new table
New Table("FirstRowData");

// Run the concatenations
For( i = 1, i <= N Items( Tables ), i++,
	 Data Table( "FirstRowData" ) << Concatenate(
		Data Table( Tables[i] ),
		append to first table(1)
	);
	Close( Data Table( Tables[i] ), No Save );
);

Re: Concatenate selected row from multiple subsetted data tables into 1 new data table

HadleyMyers — Mon, 05 Jul 2021 11:03:58 GMT

Hi,

The issue here seems to be the table variables that were created for each subset. Table variables with the same name ("age") were then getting added as columns with each concatenation. See script below, and notice the For-loop added to delete the table variables. Does this resolve the issue for you?

// Open Big Class data table
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

//Subset the data table by age
listDT = dt << Subset(
	By( :age ),
	All rows,
	Selected columns only( 0 ),
	columns( :name, :sex, :height, :weight )
);

//Select first row of each subset data table 
For( i = 1, i <= N Items( listDT ), i++,
      
    listDT[i] << Select Rows( {1} );
    listDT[i] << Subset("");
    
    Close(listDT[i],"No Save"); 
    Close(dt,"No Save"); 

);

// Create Tables list
Tables = {};
For( i = 1, i <= N Table(), i++,
	Insert Into( Tables, Data Table( i ) << get name );
);

//Delete table variables
For( i = 1, i <= N Table(), i++,
	Data Table(Tables[i]) << Delete Table Variable("age");
);

//Create new table
New Table("FirstRowData");

// Run the concatenations
For( i = 1, i <= N Items( Tables ), i++,
	 Data Table( "FirstRowData" ) << Concatenate(
		Data Table( Tables[i] ),
		append to first table(1)
	);
	Close( Data Table( Tables[i] ), No Save );
);

Re: Concatenate selected row from multiple subsetted data tables into 1 new data table

bzanos — Mon, 05 Jul 2021 12:06:43 GMT

Thank you!

It works perfectly