We’re asking you to select a content label when starting a new topic in the Discussions area. Read more to find out why.
Choose Language Hide Translation Bar
Highlighted
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
Highlighted
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

Highlighted
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
Highlighted
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

Highlighted
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 )] = .;

 

Highlighted
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

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

Highlighted
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