topic Re: Replacing missing data by regressed values in Discussions

Replacing missing data by regressed values

guilhem — Fri, 08 Jul 2016 14:39:15 GMT

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

t	Output
0	10
1	.
2	.
4	30
10	.
20	10

What I want:

t	Output
0	10
1	15
2	20
4	30
10	22.5
20	10

Re: Replacing missing data by regressed values

ron_horne — Fri, 28 Oct 2016 13:28:23 GMT

try this for starters

Imputation Addin

Re: Replacing missing data by regressed values

guilhem — Sun, 10 Jul 2016 21:31:59 GMT

Thanks Ron but I don’t see anything in this that does interpolation?

Re: Replacing missing data by regressed values

txnelson — Thu, 27 Feb 2020 17:03:16 GMT

here 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 values

ms — Thu, 27 Feb 2020 14:28:36 GMT

I 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 values

guilhem — Mon, 11 Jul 2016 08:28:11 GMT

Spot on MS,

Cheers!

Re: Replacing missing data by regressed values

tatersalad — Thu, 27 Feb 2020 14:25:49 GMT

txnelson,

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 values

txnelson — Thu, 27 Feb 2020 17:22:22 GMT

Here 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 values

tatersalad — Fri, 28 Feb 2020 09:26:39 GMT

thanks 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 values

txnelson — Fri, 28 Feb 2020 11:03:47 GMT

Good catch....it should be

If this is row 1 initialize a variable named counter to 0

Re: Replacing missing data by regressed values

tatersalad — Fri, 03 Apr 2020 00:20:16 GMT

Jim,

is there a difference between "rows" and "Rows"? could you please explain it. I'm a noob.

Re: Replacing missing data by regressed values

txnelson — Fri, 03 Apr 2020 01:54:12 GMT

JMP is not case specific in functions and statements, so rows=Rows

Re: Replacing missing data by regressed values

tatersalad — Fri, 03 Apr 2020 02:48:41 GMT

Jim,

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 values

tatersalad — Fri, 03 Apr 2020 02:50:14 GMT

heres the table

Re: Replacing missing data by regressed values

txnelson — Fri, 03 Apr 2020 11:14:45 GMT

You 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 values

tatersalad — Fri, 03 Apr 2020 16:56:25 GMT

Jim,

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 values

txnelson — Fri, 03 Apr 2020 17:07:56 GMT

From 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 values

tatersalad — Fri, 03 Apr 2020 19:50:36 GMT

Jim,

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 values

txnelson — Fri, 03 Apr 2020 19:56:07 GMT

From 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 values

tatersalad — Fri, 03 Apr 2020 20:05:52 GMT

that 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