cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
DavidLeber
Level III

Interpolate Missing?

I deal with a lot of time-based data, where each row corresponds to an hourly average and each column corresponds to an instrument reading. Sometimes, these instruments fail for an hour or two at a time and the column has a blank value. However, these processes respond fairly slowly, and so I know that the instrument values are probably somewhere between the last good value before the outage and the first good value before the outage. Instrument failures are typically independent, and as such the other instruments are still trusted during these times.

 

I'm familiar with Explore Missing Values, but I don't think I see any "Interpolate missing". Is there any option to fill in missing values by using a straight line between the last good value and the next good value?

1 ACCEPTED SOLUTION

Accepted Solutions
ms
Super User (Alumni) ms
Super User (Alumni)

Re: Interpolate Missing?

For time dependent processes (growth, seasonality etc.) locally interpolated values can make more sense than the different imputation options in JMP 14.

 

Two JSL examples for populating missing values by interpolation using splines.  

//Example table with missing values
dt = New Table("untitled 28882",
    Add Rows(10),
    New Column("Time", Set Values([1, 2, 3, 4, 5, 6, 7, 8, 9, 10])),
    New Column("Data", Set Values([2, 4, ., 6, 7, 8, ., ., 13, 17]))
);

// 1. Create a new column with imputed missing values
dt << Bivariate(Y(:Data), X(:Time), Fit Spline(1e-15, {Save Predicteds}));

// 2. Or impute missing values directly in the original column
m = Spline Eval(
    :Time << get values,
    Spline Coef(:Time << get values, :Data << get values, 1e-15)
);
For Each Row(If(Is Missing(:Data), :Data = m[Row()], :Data << color cells(10, Row())));

 

View solution in original post

2 REPLIES 2
Kevin_Anderson
Level VI

Re: Interpolate Missing?

Hi, DavidLeber!

 

You don't mention which version of JMP you're using, but what you're talking about is called Imputation.  You should look up Impute Missing Values in the documentation.

 

Modern JMP has some great imputation capabilities, and there are even add-ins available for imputations for other older versions of JMP.

 

Good luck.

ms
Super User (Alumni) ms
Super User (Alumni)

Re: Interpolate Missing?

For time dependent processes (growth, seasonality etc.) locally interpolated values can make more sense than the different imputation options in JMP 14.

 

Two JSL examples for populating missing values by interpolation using splines.  

//Example table with missing values
dt = New Table("untitled 28882",
    Add Rows(10),
    New Column("Time", Set Values([1, 2, 3, 4, 5, 6, 7, 8, 9, 10])),
    New Column("Data", Set Values([2, 4, ., 6, 7, 8, ., ., 13, 17]))
);

// 1. Create a new column with imputed missing values
dt << Bivariate(Y(:Data), X(:Time), Fit Spline(1e-15, {Save Predicteds}));

// 2. Or impute missing values directly in the original column
m = Spline Eval(
    :Time << get values,
    Spline Coef(:Time << get values, :Data << get values, 1e-15)
);
For Each Row(If(Is Missing(:Data), :Data = m[Row()], :Data << color cells(10, Row())));