Working with inliers

Question

I've been interested in working with inliers as I see it more and more of a problem with a lot of the data I look at. Wondering if anyone has had customers bring this up as a point point? These inliers are not something the explore outliers tool will handle well in many cases. So I end up doing a combination of one or more of these techniques. Open to suggestions on other tactics here.

I had an example of our customer data user counts. Much of the data was realistic in terms of # users, but there were these chunks of users counts and 100, 200, 500, 2500 etc. These are just some contacturally maximum number of users, but not an accurate count of users at a site. They are inliers and should really be missing or dealt with in another way. If you know these values, which are inliers (easy to spot in Graph Builder or distirbution) you can do something like this:

For Each Row(
	:Name( "Potassium (mg)" )  = Match( :Name( "Potassium (mg)" ),
		200, .,
		:Name( "Potassium (mg)" )
	)
);

This will take those inlier values out of the row destructively (you can also change to missing, use missing value codes or something like that).

I also use the range check column property extensively to scrub out values that should be omitting from the analysis:

dt:Name( "Resting Calories (kcal)" ) << set property ("Range Check", LTLT( 1400, 2500 ) );

This one, for example, will scrub out outliers on the high side and also incomplete days on the low side.

But the automatic tagging of values that occur too frequently is what I am interested in exploring. I actually think recode is a good tool here, plus it generates the script to change these to missing etc. 
Recoding a numeric column and sorting by count gives you a snapshot of values that might occur too frequently, or a piece of equipment that is just spitting out a default value. In the above example, 1822 might be suspect and warrent further investigation.

I guess my point is consider some of the other tools as off-label outlier and inlier detectors, they can be very useful.

brady_brady · Answer

It is also worth noting that it is possible to point-and-click this by a main-menu search+replace, if you:

* Search on 0
* Replace with .
* Check the "Match Entire Cell Value" box
* Check the "Search Data" box.

This is great because it is simple, but a caveat (maybe... not sure this would happen much in "real" datasets) is that it does not check for character/numeric... so any cells in character columns containing only "0", become "."

Daniel_Valente · Answer

// get the continuous columns as a list of strings
dtcollist = dt << get column names( continuous, string );

// when doing lots of updates to the data table, use this to
// prevent the window being updated for each change
dt << Start Data Update();
For Each Row(
     // Loop over each column within each row
     // probably the same performance whichever way you do it
     For( c = 1, c <= N Items( dtcollist ), c++,
           AsColumn( dt, dtcollist[c] ) = Match( AsColumn( dt, dtcollist[c] ),
                0, .,
                AsColumn( dt, dtcollist[c] )
           )
     )
);
// Tell JMP it's okay to update the data in the window now
dt << End Data Update();

This is a really useful snippet of Code, especially for the data set that I am looking at now. Regrettably the data table has 0s, which really means missing. This script will go through all the numeric continuous columns and change all the zeros to missing. Thanks Justin for helping optimize this.

Peter_Bartell · Answer

I too use Recode in this what I'll call 'off label prescription' technique to fix inliers.