Value selection and copy into new columns

[#1]

Hey,

I´m trying to automatize a (seemingly fairly easy) value selection, column creation and copy/paste process, however, I cannot find the right script compilation for it.

1) I need to create 3 new columns adding a different selection of values from one same source column to each of those 3 new columns and create a subset of each selection when all columns are filled in.

2) The name of the subsets should correspond to the header of the original source file plus a new appendix.

Be aware:

The location of columns and the amount of rows varies in the data files I have, however, the names are all the same.

I tried these two scripts which of course are not correct but I do not know what to change exactly to make it work. Any suggestions?

Version 1:

Names Default To Here( 1 );
dt = Current Data Table();
//Select values to copy into new column
vals 1 = Select Where (:p-value (two-sided test)2 <= 0,09); //it seems that the "2" in the column name is confusing, but unfortunately, it would take a long time to go through all tables and change the column name
 // Put these in a new column 1
dt = NewColumn("sign_0,09", Numeric, Continuous, Values(vals1));
Wait( 5 );
//Column 2
vals 2 = Select Where (:p-value (two-sided test)2 <= 0,05);
dt<<get selected rows
dt = NewColumn("sign_0,05", Numeric, Continuous, Values(vals2));
Wait( 5 );
//Column 3
vals 3 = Select Where (:p-value (two-sided test)2 <= 0,01);
dt<<get selected rows
dt = NewColumn("sign_0,01", Numeric, Continuous, Values(vals3));
//Create subsets
dt << Select where :sign_0,09 != "";
dt<< Get selected rows
//the new name should be automatically given, too, is that possible?
dt << Subset( Output Table( "name as in original file plus _0,09 at the end" ), Selected Rows( 1 ), selected columns( 0 ) );
dt << Select where :sign_0,05 != "";
dt<< Get selected rows
dt << Subset( Output Table( "name as in original file plus _0,05 at the end" ), Selected Rows( 1 ), selected columns( 0 ) );
dt << Select where :sign_0,01 != "";
dt<< Get selected rows
dt << Subset( Output Table( "name as in original file plus _0,01 at the end" ), Selected Rows( 1 ), selected columns( 0 ) );

Version 2:

Names Default To Here( 1 );
dt = Current Data Table();

dt << :p-value (two-sided test)2 = [1,?]; //the end row varies and must be found automatically
results << New Column("sign_0,09", Numerical, Continuous);
For( i = 1, i <= NItems( "p-value (two-sided test)2"), i++,
    results << Select Where (:p-value (two-sided test)2 <= 0,09) );
    selRows = results << getSelectedRows;
 AsColumn( results, "sign_0,09" )[selRows] = "p-value (two-sided test)2"[i];
//same process for 2 other new columns
dt<<Subset
dt << Select where :sign_0,09 != "";
dt<< Get selected rows
//the new name should be automatically given, too, is that possible?
dt << Subset( Output Table( "name as in original file plus _0,09 at the end" ), Selected Rows( 1 ), selected columns( 0 ) );
//same process for other 2 columns

I would be very happy to find a solution as in the threads so far there is often a copy/paste into new data tables, however, "never" into a new column, and mostly it´s related to a string selection it seems. Not sure if the selection works the same for numerical data.

Thanks for any help!

[#2]

@Pacco ,
            Lets see how I can try and help. First things first, a request from my end is when you are pasting code, please paste it using the "JSL" code block, so it is easy for the community member s to read through your code. Now, once we have taken care of that, looking through your code: 

1. vals 1 = Select Where (:p-value (two-sided test)2 <= 0,09); //it seems that the "2" in the column name is confusing, but unfortunately, it would take a long time to go through all tables and change the column name
- When you have a complicated name like this for a column and don't want to change it, it would be advisable to use the name() . so the above line should be re-written as : 

vals1 = Select Where (Name("p-value (two-sided test)2 ")<= 0,09); // I don't know if you are using European convention for the comma instead of decimal

2. Now, a select where statement is a message that is issued to a data table, so the above line of code should further be refined as : 

vals1 = dt << Select Where (Name("p-value (two-sided test)2") <= 0,09); 

If you would be kind enough to try these suggestions and maybe provide a sample dataset, I can try to fix the other issues as well. 

[#3]

Hey,

Thank you very much for the input. This is already very helpful to know.

I´m sorry for the wrong copying of the script. I´ll keep it in mind for next time.

To make it more clear what I am trying to do I attach a stepwise overview of what the result should look like. I hope the guide gives more clarity.

I also attach the example in jmp format.

Thanks for the help!

[#4]

Thanks for posting some more details, that helps some. Instead of trying to create the new columns and filling them with values all through JSL, I would just create three new formula based columns with the formula:

If( :Name( "p-value (two-sided test) 2" ) <= 0.09,
	:Name( "p-value (two-sided test) 2" )
)

dt = Open( "seas_ratio_1_monthly.jmp" );

dt << New Column( "sign_0.09",
	Numeric,
	"Nominal",
	Format( "Best", 9 ),
	Formula(
		If( :Name( "p-value (two-sided test) 2" ) <= 0.09,
			:Name( "p-value (two-sided test) 2" )
		)
	)
);
dt << New Column( "sign_0.05",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula(
		If( :Name( "p-value (two-sided test) 2" ) <= 0.05,
			:Name( "p-value (two-sided test) 2" )
		)
	)
);
dt << New Column( "sign_0.01",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula(
		If( :Name( "p-value (two-sided test) 2" ) <= 0.01,
			:Name( "p-value (two-sided test) 2" )
		)
	)
);

But, I must admit that I'm still not sure what the value of the data tables with only the rows that are significant at a particlar level. How are you going to use those tables? Do they need all three significance indicator columns (i.e., sign_0,09; sign_0,05; and sign_0,01)? If not you can avoid creating those columns all together and just subset the rows that less than each level. Can you tell us more about your ultimate goal and why you want data tables like you describe? We may be able to provide even easier methods.

[#5 · Accepted solution]

@Jeff_Perkinson

Thank you Jeff,

I had read your post too late. I just added the create subsets part to the selection you proposed and apart from the automatized name creation it works perfectly. Thank you for the suggestion.

dt = Current Data Table();

dt << New Column( "sign_0,09",
	Numeric,
	"Nominal",
	Format( "Best", 9 ),
	Formula(
		If( :Name( "p-value (two-sided test) 2" ) <= 0.09,
			:Name( "p-value (two-sided test) 2" )
		)
	)
);
dt << New Column( "sign_0,05",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula(
		If( :Name( "p-value (two-sided test) 2" ) <= 0.05,
			:Name( "p-value (two-sided test) 2" )
		)
	)
);
dt << New Column( "sign_0,01",
	Numeric,
	"Continuous",
	Format( "Best", 12 ),
	Formula(
		If( :Name( "p-value (two-sided test) 2" ) <= 0.01,
			:Name( "p-value (two-sided test) 2" )
		)
	)
);

/* Step 3 */
// We already have the rows that meet the conditions 
dt << Select Where (Name("p-value (two-sided test)2") <= 0.09);
dt << Subset( Output Table( "new_0,09" ), Selected Rows( 1 ), selected columns( 0 ) );
dt << Select Where (Name("p-value (two-sided test)2") <= 0.05);
dt << Subset( Output Table( "new_0,05" ), Selected Rows( 1 ), selected columns( 0 ) );
dt << Select Where (Name("p-value (two-sided test)2") <= 0.01);
dt << Subset( Output Table( "new_0,01" ), Selected Rows( 1 ), selected columns( 0 ) );
/* End of Step 3 */ 

[#6]

I've read your description of what you're trying to do but I'm having difficulty understanding it without a concrete example. Additionally, since your JSL isn't doing what you'd like it to do it's difficult to use that to guess how to help you.

Could you post some more details about exactly what you're trying to do, preferably with screen shots of the data tables in question and what you'd like to end up with? I think that would help us in the Community to help you more directly.

Thanks.

[#7]

Hi Jeff,

Thanks for having a look, too.

I attached the files in my reply above! I hope they clarify my question.

[#8]

@Pacco,
     Ok, this should get you 90% of the way there. 

dt = Current Data Table(); // Assuming the data table is open 

/* Step 1 - add 3 columns  */ 
dt << New Column("sign_0,09",Numeric,Continuous)
   << New Column("sign_0,05",Numeric,Continuous)
   << New Column("sign_0,01",Numeric,Continuous); 

// Getting the values of column of interest 
ColVals = dt:Name("p-value (two-sided test) 2") << Get Values; 

// Set up a dummy matrix 
Dummy = J(NRows(dt),1,.); 

// All Rows 
AllRows = Index(1,NRows(dt),1);

/* Step 2 - Select from the column based on conditions */ 
/* This could also be replaced by loop */
SR1 = dt << Select Where(:Name("p-value (two-sided test) 2") <= 0.09) << Get Selected Rows;  // You can edit this to conform to european convention of 0,09
dt << Clear Select; 
Dummy[SR1,0] = ColVals[SR1]; 

dt:Name("sign_0,09") << Set Values(Dummy); 

// Re-initialize dummy matrix 
Dummy = J(NRows(dt),1,.); 

SR2 = dt << Select Where(:Name("p-value (two-sided test) 2") <= 0.05) << Get Selected Rows;  // You can edit this to conform to european convention of 0,05
dt << Clear Select; 
Dummy[SR2,0] = ColVals[SR2]; 

dt:Name("sign_0,05") << Set Values(Dummy); 

// Re-initialize dummy matrix 
Dummy = J(NRows(dt),1,.); 

SR3 = dt << Select Where(:Name("p-value (two-sided test) 2") <= 0.01) << Get Selected Rows;  // You can edit this to conform to european convention of 0,01
dt << Clear Select; 
Dummy[SR3,0] = ColVals[SR3]; 

dt:Name("sign_0,01") << Set Values(Dummy); 
/* End of Step 2 */ 

/* Step 3 */
// We already have the rows that meet the conditions 
Show(SR1); 
Show(SR2); 
Show(SR3); 
/* End of Step 3 */ 

 @Jeff_Perkinson, 
       Is there an easy way to find common elements between matrices ? Maybe Design() might help, but  can you provide an example 

A = [1,2,3,4,5,6]; 

B = [1,2,3]; 

C = [3,5]; 


// The common element would be 3 

    I know I could use associative arrays and the intersect functionality, but shouldn't matrices have an easy way to determine common/uncommon elements. 

[#9]

That looks great. I will test it as soon as possible.

If you refer to common elements within the 3 new columns then this is only the case regarding the selected values that will be added to each of the columns!

E.g. the selection of values <= 0,01 will also appear in columns with 0,05 and 0,09 and the ones <= 0,05 will appear also in 0,09.

Simplified:

A = [0,01,0,05,0,09]

B = [0,01,0,05]

C = [0,01]

However, there will NOT be any other pattern or kind of "every second row is equal in all three rows" structure.

Thus, yes, maybe there is a way to work with matrices and make the whole process smoother.

A = [1,2,3,4,5,6]; 

B = [1,2,3]; 

C = [3,5];