Subscribe Bookmark RSS Feed

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

jeffub

Community Member

Joined:

Jan 26, 2012

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.

1 REPLY
ms

Super User

Joined:

Jun 23, 2011

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