topic Re: Value selection and copy into new columns in Discussions

Value selection and copy into new columns

Pacco — Fri, 12 Oct 2018 14:27:03 GMT

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!

Re: Value selection and copy into new columns

uday_guntupalli — Fri, 12 Oct 2018 14:30:58 GMT

@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.

Re: Value selection and copy into new columns

Jeff_Perkinson — Fri, 12 Oct 2018 14:32:10 GMT

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.

Re: Value selection and copy into new columns

Pacco — Fri, 12 Oct 2018 15:59:44 GMT

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!

Re: Value selection and copy into new columns

Pacco — Fri, 12 Oct 2018 16:01:39 GMT

Re: Value selection and copy into new columns

Pacco — Fri, 12 Oct 2018 16:04:55 GMT

Hi Jeff,

Thanks for having a look, too.

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

Re: Value selection and copy into new columns

uday_guntupalli — Fri, 12 Oct 2018 17:45:19 GMT

@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.

Re: Value selection and copy into new columns

Pacco — Fri, 12 Oct 2018 17:57:46 GMT

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];

Re: Value selection and copy into new columns

Mark_Bailey — Fri, 12 Oct 2018 18:46:24 GMT

I see this approach but there is a simpler way that does involve changing the selected row state. Instead of this:

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;

You can just use this:

SR1 = dt << Get Rows Where(:Name("p-value (two-sided test) 2") <= 0.09) 
// You can edit this to conform to european convention of 0,09

This way you don't disturb the current row seletion.

Re: Value selection and copy into new columns

Jeff_Perkinson — Fri, 12 Oct 2018 20:30:43 GMT

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.

Re: Value selection and copy into new columns

Pacco — Sat, 13 Oct 2018 09:22:07 GMT

@uday_guntupalli

I ran the script with my data (your version and with the exchange of the suggestion of @Mark_Bailey) and it works to create the columns and to get the selected data into the new column.

However, the last part (see below) to show the selected rows does not work. I adjusted it with a Select Where and Create Subset.

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

/* Step 3 */
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 */

Re: Value selection and copy into new columns

Pacco — Sat, 13 Oct 2018 09:22:39 GMT

Re: Value selection and copy into new columns

Pacco — Sat, 13 Oct 2018 09:25:15 GMT

@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 */

Re: Value selection and copy into new columns

Pacco — Sat, 13 Oct 2018 09:33:53 GMT

Hey, here is the full version without the Show functions as those were not of any use in my case.

Thanky for your help!

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 << Get Rows Where(:Name("p-value (two-sided test) 2") <= 0.09);
//do not change "." to "," as the programme uses "." for background calculation
Dummy[SR1,0] = ColVals[SR1];

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

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

SR2 = dt << Get Rows Where(:Name("p-value (two-sided test) 2") <= 0.05);
Dummy[SR2,0] = ColVals[SR2];

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

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

SR3 = dt << Get Rows Where(:Name("p-value (two-sided test) 2") <= 0.01);
Dummy[SR3,0] = ColVals[SR3];

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

/* Step 3 */
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 */

Re: Value selection and copy into new columns

Pacco — Sat, 13 Oct 2018 09:33:57 GMT

Hey, here is the full version without the Show functions as those were not of any use in my case.

Thanks for your help!

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 << Get Rows Where(:Name("p-value (two-sided test) 2") <= 0.09);
//do not change "." to "," as the programme uses "." for background calculation
Dummy[SR1,0] = ColVals[SR1];

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

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

SR2 = dt << Get Rows Where(:Name("p-value (two-sided test) 2") <= 0.05);
Dummy[SR2,0] = ColVals[SR2];

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

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

SR3 = dt << Get Rows Where(:Name("p-value (two-sided test) 2") <= 0.01);
Dummy[SR3,0] = ColVals[SR3];

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

/* Step 3 */
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 */