Replacing missing data by regressed values<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />Hi
I have a time series for which the variable has missing values. I would like to replace the missing values by a linear model derived from the closest available values in time.
What would be the best way?
And if the script could do that to all the columns of my table (except the time), that would be even better
Thanks
Example
What I have
What I want:
| t | Output |
|---|
| 0 | 10 |
| 1 | 15 |
| 2 | 20 |
| 4 | 30 |
| 10 | 22.5 |
| 20 | 10 |
Re: Replacing missing data by regressed valuesThe way JMP formulas work, along with JMP functions, is that the last variable created or referenced, is what is passed from the formula or function. Therefore, in your replacement formula, what is returned as the rows value, is the value from the "Values" matrix equal to the Row() number. And since the "Values" matrix is where the columns values had been gotten with the << get values message, and the interpolations were made on the "Values" matrix, by specifying
values[Row()]
the formula is moving the values one row at a time into the new column.
Another way to explain this, is to go through the process flow of the formula
All formulas loop through all rows in a data table from row 1 through the last row in the data table.
On the processing of row 1, all of the values from column "p (g/L)" are moved into a matrix for processing. Processing loops from element 1 in the matrix, through the end of the matrix, performing interpolations when necessary. By the end of the looping, all elements of the matrix are filled in. Then the value from the first element in the "Values" matrix, is returned to the formula as the value to be used for the 1st row in the column.
Then the row value is moved to run the formula for the 2nd row, and the only statements executed for the 2nd row is
values[Row()]
which returns to the formula the value of the columns 2 row, which is element 2 from the "Values" matrix.
The formula continues looping once for each subsequent row, only executing the statement
values{Row()]
I hope this makes senseRe: Replacing missing data by regressed valuesJim,
Thanks so much. this works really well now. can you explain to me what the last "values[Row()];" does
Re: Replacing missing data by regressed valuesIf you want to make the value in the Interpreted column to be equal to 0 whenever the :Time value is 0, a change to the code something like below would work
For( i = 1, i <= N Rows( values ), i++,
If( :Time[i] == 0, values[i]=0); // **************
If( Is Missing( values[i] ),
counter++,
If( counter > 0,
For( k = 1, k <= counter, k++,
base = values[i - counter - 1];
incr = Abs( (base - values[i]) / (counter + 1) );
If( base < values[i],
values[(i - counter - 1) + k] = base + k * incr,
values[(i - counter - 1) + k] = base - k * incr
);
);
counter = 0;
)
)
);Re: Replacing missing data by regressed valuesthat does it for the first value, but what i need is everything "Time" is 0, the interpolated value is zero. the time column will have multiple 0's
Re: Replacing missing data by regressed valuesFrom the code I supplied you, is this what you are asking about?
If( Is Missing( values[1] ),
values[1] = 0
);Re: Replacing missing data by regressed valuesJim,
I got the script to work. I had to add some math so as to account for the delta time for both the incr calculation for interpolation evaluation loop. final problem is that since i am planning on joining multiple run files together and running the script, I'll have multiple spots where time = 0 (time: 0,12,24,48,0,12,24,48...), thus the first interpolated value should be 0 for the given chunk of data. I'm not sure how to do this with the matrix notation.
Re: Replacing missing data by regressed valuesFrom my experiences, I have had too many issues when attempting to reference the actual formula column within the formula. Thus, the approach shown. I also was concerned with your formula from the standpoint of the number of times you are running through the data with your For() loop. My method steps through the data table only once....not counting the iteration replacement.Re: Replacing missing data by regressed valuesJim,
Thanks so much i'll give it a shot. I'm curious though; going back to the original script i thought the recursive error came from row 3, because of it would go to calculate incr and get rows-counter-1 = 3-2-1 =0. i'd ignore the error message and it successfully interpolates the rest of the column, except for the very end. adding more data to the table to be be interpolated moves the problem down. i thought they might be separate issues.
Re: Replacing missing data by regressed valuesYou are running into a recursive issue in the formula, when you try to change values within the formula column itself. I have reworked your formula to do all of the work upfront using a matrix. The resulting values are then used for each rows values
Names Default To Here( 1 );
col_name_list = Current Data Table() << get column names( string );
If( !Contains( col_name_list, "Interpolated p (g/L)" ),
Current Data Table() << New Column( "Interpolated p (g/L)",
Numeric,
Continuous,
Formula(
dt = Current Data Table();
If( Row() == 1,
counter = 0;
values = dt:Name( "p (g/L)" ) << get values;
If( Is Missing( values[1] ),
values[1] = 0
);
counter = 0;
For( i = 1, i <= N Rows( values ), i++,
If( Is Missing( values[i] ),
counter++,
If( counter > 0,
For( k = 1, k <= counter, k++,
base = values[i - counter - 1];
incr = Abs( (base - values[i]) / (counter + 1) );
If( base < values[i],
values[(i - counter - 1) + k] = base + k * incr,
values[(i - counter - 1) + k] = base - k * incr
);
);
counter = 0;
)
)
);
);
values[Row()];
)
)
);Re: Replacing missing data by regressed valuesheres the table
Re: Replacing missing data by regressed valuesJim,
Thank you for clarifying. I;m having a problem with the script. it wont execute and interpolate the last interval. i cant tell if it has something to do with the nesting of the loops or the "while" conditions. basically regardless of table length it cant fill in the last interval of missing data.
col_name_list = Current Data Table() << get column names( string );
If( !Contains( col_name_list, "Interpolated p (g/L)" ),
Current Data Table() << New Column(
"Interpolated p (g/L)",
Numeric,
Continuous,
Formula(
If(
Is Missing( :Name( "time (h)" ) ),
Empty(),
:name( "time (h)" ) == 0,
0,
!Is Missing( :Name( "p (g/L)" ) ),
:name( "p (g/L)" ),
dt = Current Data Table();
For( rows = 1, rows <= (N Rows( dt )), rows++,
If( Rows == 1, counter = 0 );
If( Is Missing( dt:name( "Interpolated p (g/L)" )[rows] ) == 1,
counter++);
If( counter > 0 & Is Missing( dt:name( "Interpolated p (g/L)" )[rows] ) == 0,
incr = (dt:name( "Interpolated p (g/L)" )[Rows] - dt:name( "Interpolated p (g/L)" )[Rows - counter - 1]) / (dt
:name( "time (h)" )[Rows] - dt:name( "time (h)" )[Rows - counter - 1]);
For( i = rows - counter, i <= rows - 1, i++,
dt:name( "Interpolated p (g/L)" )[i] = dt:name( "Interpolated p (g/L)" )[i - 1] + (incr * (dt:name( "time (h)" )[i]
-dt:name( "time (h)" )[i - 1]))
);
counter = 0;
);
);
)
)
)
);
Re: Replacing missing data by regressed valuesJMP is not case specific in functions and statements, so rows=RowsRe: Replacing missing data by regressed valuesJim,
is there a difference between "rows" and "Rows"? could you please explain it. I'm a noob.
Re: Replacing missing data by regressed valuesGood catch....it should be
If this is row 1 initialize a variable named counter to 0
Re: Replacing missing data by regressed valuesthanks it worked! you commented
If this is row 1 initialize a variable named counter to 1
, did you mean "counter to 0"?
Re: Replacing missing data by regressed valuesHere is an updated/annotated version of my original code. All you should have to do, is to change the column name from "Output" to whatever the name of the column you want to change values to.
// Set all default referenced memory variables to this unique application
Names Default To Here( 1 );
// Set the variable dt to be a pointer to the current active data table
dt = Current Data Table();
// Loop through the data table from row 1 to the last row and use a
// variable names "rows" as the index
For( rows = 1, rows <= N Rows( dt ), rows++,
// If this is row 1 initialize a variable named counter to 1
If( Rows == 1, counter = 0 );
// If the current row's value for the column named "Output" in the
// data table referenced by "dt" is missing (blank, null) increase
// the counter
If( Is Missing( dt:output[rows] ) == 1,
counter++
);
// If row is found with a non missing value for the column called Output,
// and the counter is > 0, which indicates previous rows with missing
// values, then calculate the required values and place them in their
// appropriate rows
If( counter > 0 & Is Missing( dt:output[rows] ) == 0,
// Calculate the incremental steps to use for the missing cells
incr = (dt:output[Rows] - dt:output[Rows - counter - 1]) / (counter + 1);
// Loop across the rows with missing values and fill in the data
For( i = Rows - counter, i <= Rows - 1, i++,
dt:output[i] = dt:output[i - 1] + incr
);
// reinitialize the counter
counter = 0;
);
);Re: Replacing missing data by regressed valuestxnelson,
could you update the script with comments? I am new to JSL and need help understanding how it applies to my problem. I have the same need as OP but only have one column that i need to fill in. alternatively, I have used the following VBA code in the past in to fill in missing data in excel. I was wondering if there is something similar to the End(xlDown).value function in JSL. the offset is used to specify where the time point data is. Thanks!
Sub interpolatedata()
For Each cell In Range("k4:k29")
If cell.Value = "" Then
y2 = cell.End(xlDown).Value
x2 = cell.End(xlDown).Offset(0, -8).Value
y1 = cell.End(xlUp).Value
x1 = cell.End(xlUp).Offset(0, -8).Value
x = cell.Offset(0, -8).Value
y = (y1) + (x - x1) * (y2 - y1) / (x2 - x1)
cell.Value = y
End If
Next
Re: Replacing missing data by regressed valuesSpot on MS,
Cheers!
Re: Replacing missing data by regressed valuesI had a similar problem some years ago and wrote a script to do imputation by "local regression".
Here's an updated version:
dt = Current Data Table();
n = N Rows(dt);
x = Column(1);// Column("t")
For(i = 2, i <= N Col(dt), i++,
y = Column(i);
For Each Row(
If(1 < Row() < n & Is Missing(y[]),
// find previous non-missing row
Eval(Eval Expr(r1 = Max(dt << get rows where(!Is Missing(y[]) & Row() < Expr(Row())))));
// find next non-missing row
Eval(Eval Expr(r2 = Min(dt << get rows where(!Is Missing(y[]) & Row() > Expr(Row())))));
y[] = y[r1] + (y[r2] - y[r1]) / (x[r2] - x[r1]) * (x[] - x[r1]);
)
)
);
Re: Replacing missing data by regressed valueshere is a script that will do the task
Names Default To Here( 1 );
dt = Current Data Table();
For( rows = 1, rows <= N Rows( dt ), rows++,
If( Rows == 1, counter = 0 );
If( Is Missing( dt:output[rows] ) == 1,
counter = counter + 1,
If( counter > 0,
incr = (dt:output[Rows] - dt:output[Rows - counter - 1]) / (counter + 1);
For( i = Rows - counter, i <= Rows - 1, i++,
dt:output = dt:output[i - 1] + incr
);
counter = 0;
)
);
);
// Set all default referenced memory variables to this unique application
Names Default To Here( 1 );
// Set the variable dt to be a pointer to the current active data table
dt = Current Data Table();
// Loop through the data table from row 1 to the last row and use a
// variable names "rows" as the index
For( rows = 1, rows <= N Rows( dt ), rows++,
// If this is row 1 initialize a variable named counter to 1
If( Rows == 1, counter = 0 );
// If the current row's value for the column named "Output" in the
// data table referenced by "dt" is missing (blank, null) increase
// the counter
If( Is Missing( dt:output[rows] ) == 1,
counter++
);
// If row is found with a non missing value for the column called Output,
// and the counter is > 0, which indicates previous rows with missing
// values, then calculate the required values and place them in their
// appropriate rows
If( counter > 0 & Is Missing( dt:output[rows] ) == 0,
// Calculate the incremental steps to use for the missing cells
incr = (dt:output[Rows] - dt:output[Rows - counter - 1]) / (counter + 1);
// Loop across the rows with missing values and fill in the data
For( i = Rows - counter, i <= Rows - 1, i++,
dt:output[i] = dt:output[i - 1] + incr
);
// reinitialize the counter
counter = 0;
);
);Re: Replacing missing data by regressed valuesThanks Ron but I donât see anything in this that does interpolation?
Re: Replacing missing data by regressed valuestry this for starters
Imputation Addin
