BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
DavidLeber

Occasional Contributor

Joined:

Sep 17, 2018

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
Highlighted
ms

Super User

Joined:

Jun 23, 2011

Solution

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())));

 

2 REPLIES 2
Kevin_Anderson

Community Trekker

Joined:

Jun 5, 2014

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.

Highlighted
ms

Super User

Joined:

Jun 23, 2011

Solution

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())));