Solved: Re: Clearing data in selection

Neal85 · Jun 10, 2023 4:14 PM

I have a big table with several rows and columns. I want to delete some data from the table.

For e.g delete data of row 2 upto column no. 10 based on some criteria. But, keep data in the same row from column 11 intact. The code I have is as follows:

cdt = Current Data Table();
cdt << select columns( 1 :: 21 );
cdt << select where(Is Missing(:Name("Record no.")));

I want to delete the data that's been selected.

There must be a simple way of doing this, but I really don't know how to do it. I can right click and select clear.

But, I want a jsl script code so that this task can be automated.

txnelson · Jun 19, 2020 12:52 AM

Here is an alternative way to code this. It handles a numerical range of columns, and it assumes they are character columns, so it sets their values to a blank "".

names default to here(1);
cdt = open("$SAMPLE_DATA/Big Class.jmp");
rows = cdt << get rows where (:sex=="F");
cdt[rows,4::5]= "";

Jim

View solution in original post

txnelson · Jun 21, 2020 11:59 PM

There are cases, where going back to a tool that you have spent years with, learning the in's and out's, is the quick/correct thing to do. And Excel is certainly the primary tool of record when it comes to users new to JMP.

In particular, given that Excel is exCELL based and JMP is column based, JMP will not have functions that deal with things like moving some cells in a given column, up or down in the column. That is because JMP assume column values in the same row, have a relationship. Excel does not have such an assumption. On the other hand, since JMP is Column Based it takes advantage of that in many of the functions, platforms, etc. In many cases, one do some extra work, and get it to do some of the things that JMP does very easily. Excel's Vlookup is one of the items that takes much more effort to get data "Looked Up" than JMP's Update Platform. Conversely, the task you questioned the community about, moving cells up in a column requires JMP to do more work to get it accomplished. One thing that is true about JMP, over Excel, is that JMP is more extensive/complex, because of it's analytical purpose. So it does take more training/education. It is for that reason, that you will see members of the Community, urging individuals to read the JMP documents. They are really the only way to really grasp what can be done with JMP, and how to approach it.

Below is a script that moves the non missing rows within each column up. It is just one of the ways I can envision one doing this.

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/big class.jmp" );
dt1 = dt << subset(selected rows(0), selected columns(0));
dt1 << set name( "Example 1" );

// randomly clear some data from the different columns
// to have different cells blank or with missing values
For( i = 1, i <= N Cols( dt1 ), i++,
	For( k = 1, k <= 5, k++,
		If( Column( dt1, i ) << get datatype == "Character",
			Column( dt1, i )[Random Integer( 1, N Rows( dt1 ) )] = "",
			Column( dt1, i )[Random Integer( 1, N Rows( dt1 ) )] = .
		)
	)
);

// For the example, create a copy of the Example 1 data table to work on, 
// so the beginning and modified tables can be viewed
wk1 = dt1 << subset(selected rows(0), selected columns(0));
wk1 << set name("Working 1");

// Remove the missing cells for each column and move up the remaining
// rows to fill in
For(i=1,i<=n cols(wk1),i++,
	dtTemp=wk1<<subset(selected rows(0), columns(column(wk1,i)));
	If( Column( wk1, i ) << get datatype == "Character",
		dtTemp << select where( ascolumn(1) == "" ),
		dtTemp << select where( isMissing(as column(1) ) )
	);
	dtTemp << delete rows;
	thelist = column(dtTemp, 1) << get values;
	close(dtTemp, nosave);
	If( Column( wk1, i ) << get datatype == "Character",
		column(wk1,i)<< set each value(""),
		column(wk1,i)<< set each value(.)
	);
	column(wk1,i) << set values(theList);
);

Jim

View solution in original post

David_Burnham · Jun 18, 2020 06:41 AM

You can use the messages clear column selection and clear select for columns and rows respectively.

cdt = open("$SAMPLE_DATA/Big Class.jmp");
cdt << select columns(4::5);
cdt << select where(:sex=="F");
wait(2);
cdt << clear column selection; 
cdt << clear select;

-Dave

Neal85 · Jun 18, 2020 07:25 AM

Your solution is to clear the selection.

I am looking for 'clear'-- delete contents of the selection.

Sorry, I wasn't able to explain clearly.

David_Burnham · Jun 18, 2020 08:59 AM

I think what you are asking for, is that once you have selected certain rows, how do you delete them? You can use the message delete rows:

dt << delete rows;

If you check the scripting index you will see that only selected rows are deleted!

-Dave

David_Burnham · Jun 18, 2020 09:02 AM

... or maybe I am being dumb, and you mean that you want to clear the contents of the selected cells?

-Dave

Neal85 · Jun 18, 2020 09:08 AM

I want to just delete the selected contents. If I use

Delete Rows

it will delete the whole row. As I said, I don't want the whole row deleted.

David_Burnham · Jun 18, 2020 09:09 AM

OK, sorry, I read your description again:

cdt = open("$SAMPLE_DATA/Big Class.jmp");
rows = cdt << get rows where (:sex=="F");
column(cdt,4)[rows] = .;
column(cdt,5)[rows] = .;

-Dave

Neal85 · Jun 18, 2020 9:32 PM

Your solution would work for numeric columns. But, what about non-numeric ones.

Also, instead of going one column after the other. Isn't it possible to just do a range. Something like 1::4?

txnelson · Jun 19, 2020 12:52 AM

Here is an alternative way to code this. It handles a numerical range of columns, and it assumes they are character columns, so it sets their values to a blank "".

names default to here(1);
cdt = open("$SAMPLE_DATA/Big Class.jmp");
rows = cdt << get rows where (:sex=="F");
cdt[rows,4::5]= "";

Jim

Neal85 · Jun 19, 2020 01:00 AM

Thank you for the solution. It works. But, I have another problem now.
After deleting all the cells, I want to consolidate the data. ie, data will shift up to take
space left behind after deleting. Is there a jsl code to achieve that?