Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- JMP User Community
- :
- Discussions
- :
- Discussions
- :
- Deleting cell values

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

Nov 9, 2017 12:51 AM
(1769 views)

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() ??

);

1 ACCEPTED SOLUTION

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

I have attached an example data table the above code works with.

Jim

2 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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

I have attached an example data table the above code works with.

Jim

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

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