How to select individual cell instead of entire rows?
In table below, I need to select the two -99 cells under Value1 and Value2 column, and delete them. Can someone help? When I used select where, the entire row is selected.
Selecting a single cell, in the context of "Select Where" isn't really available. But addressing a single cell can be done and then the cell value can be changed, or the cell color can be changed. Here is an example of how to do this:
Names Default to Here(1);
dt=current data table();
dt:Value 2 << color cells("Red", dt<<get rows where(dt:Value 2 == -99));
One approach is to simply clear the value of the cell and replace it with missing value (essentially same as delete). So..
:Value1 << Set Each Value( If( Value1[ ] == -99, ., Value1[ ] ) );
:Value2 << Set Each Value( If( Value2[ ] == -99, ., Value2[ ] ) );
I've found that Set Each Value is quite fast. I ran a test on a 5 column, 6.6M row table and each of the operations above completed in ~2s.
Just in case you were looking for a non-scripting approach, you could use search and replace (Ctrl+F), or you could use Column Properties and set -99 as a missing value code. If you use the search option, be sure to leave the Replace With box empty (i.e no space).
alternatively, you can use the recode option to change the -99 to missing values or whatever value you want. just right click the column from the column list on the left and select recode. the rest is self explanatory.