cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
aumair
Level III

Deleting cell values

Hello, 

 

I have a similar table as shown below:

1. Table has numeric columns x_1, x_2, x3, ...

2. I want to delete a specific value, for example 8, from all the rows of cols x_1, x_2, x_3, ... . (Required o/p table shown below as well.) 

 

Input table

A B C x_1 x_2 x_3 x_4 x_5 x_6 E
      1 8 2 7 2 2  
      2 8 8 8 8 8  
      8 8 2 5 3 8  

 

Required Output Table

 

A B C x_1 x_2 x_3 x_4 x_5 x_6 E
      1 . 2 7 2 2  
      2 . . . . .  
      . . 2 5 3 .  

 

My script is attached...


Thanks in advance for the help/feedback :)

 

 

dt = Current Data Table ();
for (i=1, i<=6, i++, //iterate through columns loop
temp = "x_" || char (i); //column name
dt << Go To (temp); //select column
dt << Select Where (temp==8);// Select rows where value is 8 ***PROBLEM*** Not selecting rows :(
//After selecting rows where value is 8 for a specific column, I want to delect the value...May be use Empty() ??
);
2 ACCEPTED SOLUTIONS

Accepted Solutions
txnelson
Super User

Re: Deleting cell values

You are approaching the problem correctly, you just have some syntax issues.  Below is a working version of your code

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

// Select the columns to be evaluated
// ColList = {"x_1","x_2","x_3","x_4","x_5","x_6"};
// or if there is a programmatic way to get the list,
// do something like below
ColList = dt << get column names( numeric, string );
// Loop backwards through the list so the "i" index
// maintains it's proper reference
For( i = N Items( ColList ), i >= 1, i--,
	If( Left( ColList[i], 2 ) != "x_",
		ColList = Remove( ColList, i, 1 )
	)
);

// Loop across ColList and set the values qualified
// values to missing
For( i = 1, i <= N Items( ColList ), i++,
	// The line below takes advantage of a JMP feature where
	// you can change specific values in a column in one statement
	// by just using a matrix of the rows you want to change
	//    Column A[5,6,9,22] = 4;
	// This would set a column named "Column A"'s rows 5,6,9 and 22
	// to the value of 4
	// In your case, you want to eliminate the cell value, so the 
	// line sets the value to a missing value, which in JMP is "."
	Column( dt, ColList[i] )[dt << get rows where( As Column( dt, ColList[i] ) == 8 )] = .;
);

 

Jim

View solution in original post

XanGregg
Staff

Re: Deleting cell values

Data table subscripting is also an option, in case it is faster for your situation.

 

dt = open("$SAMPLE_DATA/Big Class.jmp");
r = loc(dt[0, 4]==61)
dt[r, 4] = .;

View solution in original post

5 REPLIES 5
txnelson
Super User

Re: Deleting cell values

You are approaching the problem correctly, you just have some syntax issues.  Below is a working version of your code

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

// Select the columns to be evaluated
// ColList = {"x_1","x_2","x_3","x_4","x_5","x_6"};
// or if there is a programmatic way to get the list,
// do something like below
ColList = dt << get column names( numeric, string );
// Loop backwards through the list so the "i" index
// maintains it's proper reference
For( i = N Items( ColList ), i >= 1, i--,
	If( Left( ColList[i], 2 ) != "x_",
		ColList = Remove( ColList, i, 1 )
	)
);

// Loop across ColList and set the values qualified
// values to missing
For( i = 1, i <= N Items( ColList ), i++,
	// The line below takes advantage of a JMP feature where
	// you can change specific values in a column in one statement
	// by just using a matrix of the rows you want to change
	//    Column A[5,6,9,22] = 4;
	// This would set a column named "Column A"'s rows 5,6,9 and 22
	// to the value of 4
	// In your case, you want to eliminate the cell value, so the 
	// line sets the value to a missing value, which in JMP is "."
	Column( dt, ColList[i] )[dt << get rows where( As Column( dt, ColList[i] ) == 8 )] = .;
);

 

Jim
aumair
Level III

Re: Deleting cell values

Thanks a lot!

I was thinking of using a loop to access rows iterativelyand then change it, but this is very nice way to use JSL :)

Column( dt, ColList[i] )[dt << get rows where( As Column( dt, ColList[i] ) == 8 )] = .;

 

XanGregg
Staff

Re: Deleting cell values

Data table subscripting is also an option, in case it is faster for your situation.

 

dt = open("$SAMPLE_DATA/Big Class.jmp");
r = loc(dt[0, 4]==61)
dt[r, 4] = .;
UberBock
Level III

Re: Deleting cell values

Interesting.  I have a slightly different need for something like this.

 

Say I have 2 columns A and B.  A is supposed to have date/time and B has a key value.  I have lines where the Date/time is missing but column B has a key.

 

How can I delete the key value when the date/time is missing.

txnelson
Super User

Re: Deleting cell values

Here is a very small change to the previous script that will do what you want

names default to here(1);
dt = open("$SAMPLE_DATA/Big Class.jmp");
// set some height rows to missing
dt:height[6] = .;
dt:height[14] = .;


r = loc(ismissing(dt[0, 4]));
// set the values in column 5(wieght) to missing
// for the rows found(located) in column 4
dt[r, 5] = .;
Jim