4333_en-US

liliconcom

How to deal with missing data in JSL: errors when running across data with missing (empty) columns?<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

Hi, I’m hoping to get some help with JSL scripting when there is data missing in a data table.

We are using JSL to execute a query against a database that pulls data into a data table that looks like:

Plate	Compound	M1	M2	M3	M4
A121	cmp001	85.203	30.022	36.797	0.0629
A122	cmp002	84.031	34.902	31.596	0.063
A123	cmp003	89.028	30.677	34.903	0.063
A124	cmp004	91.2455	35.277	35.327	0.0631
A125	cmp005	91.257	36.927	38.836	0.063

where M’s are all different measurements. We have a JSL script that makes several plots from the data and also does a fit model of

Fit Model(

               Y( :M1, :M2, :M3, :M4 ),

                               Effects( :Plate, :Compound, :Plate * :Compound ),

                               Personality( Standard Least Squares ),

                               Emphasis( Effect Leverage ),

                               Run(

                                               :M1 << {Lack of Fit( 0 ), Plot Actual by Predicted( 1 ),

                                               Plot Regression( 0 ), Plot Residual by Predicted( 1 ),

                                               Plot Effect Leverage( 1 ), Show VIF( 1 ), {:Strain <<

                                               {LSMeans Tukey HSD( 0.05, Crosstab Report( 0 ) )}}},

                                               :M2 << {Lack of Fit( 0 ), Plot Actual by Predicted( 1 ),

                                               Plot Regression( 0 ), Plot Residual by Predicted( 1 ),

                                               Plot Effect Leverage( 1 ), Show VIF( 1 ), {:Strain <<

                                               {LSMeans Tukey HSD( 0.05, Crosstab Report( 0 ) )}}},

                                               :M3 << {Lack of Fit( 0 ), Plot Actual by Predicted( 1 ),

                                               Plot Regression( 0 ), Plot Residual by Predicted( 1 ),

                                               Plot Effect Leverage( 1 ), Show VIF( 1 ), {:Strain <<

                                               {LSMeans Tukey HSD( 0.05, Crosstab Report( 0 ) )}}},

                                               :M4 << {Lack of Fit( 0 ), Plot Actual by Predicted( 1 ),

                                               Plot Regression( 0 ), Plot Residual by Predicted( 1 ),

                                               Plot Effect Leverage( 1 ), Show VIF( 1 ), {:Strain <<

                                               {LSMeans Tukey HSD( 0.05, Crosstab Report( 0 ) )}}},

                                               )

                               )

Where we are running into trouble is that occasionally we have a failed measurement system and the entire M3 or M4 column will be full of missing values (count = 0). When this occurs, the Fit Model crashes the JSLscript.

What is the best way to script a Fit Model against a data table where occasionally a column will be have no data? Is there an easy way of first looking at which columns contain data and then passing that as a list to the Fit Model platform? I don't really feel like writing a ton of IF statements to check to see which columns are full vs empty and then running a different fit model script across it as we actually have over 10 different measurements and any one of them can be missing on any day.

Thanks for your help.

How to deal with missing data in JSL: errors when running across data with missing (empty) columns?

At least in JMP 9, Fit Model( ) accepts a list argument. Eval() must be used if the list is a variable.

Heres an example on how to build a list with non-empty columns (does not check if full but is easy to add):

Ys = {:M1, :M2, :M3, :M4};

For( i = N Items( Ys ), i > 0, i--,

          Summarize( n = Count( Ys[i] ) );

          If( n == 0,

  Remove From( Ys, i )

          );

);

Fit Model(

          Y( Eval( Ys ) ),

          Effects(...