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
Aziza
Level IV

Fill missing values using last or previous observation

Hi All, 

is there any function in JMP which fills the missing values using last or previous observation?

Thanks, 

Aziza

Greetings
1 ACCEPTED SOLUTION

Accepted Solutions
MathStatChem
Level VI

Re: Fill missing values using last or previous observation

I happen to have a utility script that does this

 

Names Default To Here( 1 );

/****************************************************************
Description: This is a data manipulation utility.  The user is presented with dialog
to choose one or more columns from the data table.  Upon selecting the "Ok" button, 
the missing values in the chosen columns will be "filled down".  Filling down is performed by
copying the last previous non missing value that is present in the previous rows.  This is performed sequentially by 
row, starting with the second row in the data table.  

This script is useful in situations where a data table has been created or imported that has
cells not completely filled as the should be (such as when importing an excel table with
merged cells in the data).

As an example a data table with this structure:

Row	Lab	Setup	Replicate 	Result
1	A	1		1			100
2		.		2			101
3		.		3			103
4		2		1			101	
5		.		2			99
6		.		3			97

If the columns "Lab" and "Setup are chosen to be filled down, then the resulting table is
Row	Lab	Setup	Replicate 	Result
1	A	1		1			100
2	A	1		2			101
3	A	1		3			103
4	A	2		1			101	
5	A	2		2			99
6	A	2		3			97

*****************************************************************/

_dt = Current Data Table();

colnames = _dt << Get Column Names( String );

fillcolumns = Expr(
	selectedcols = _lb << Get Selected;
	nc = N Items( selectedcols );

	nr = N Rows( _dt );

	For( ii = 2, ii <= nr, ii++,
		For( jj = 1, jj <= nc, jj++,
			If( is missing(Column( _dt, selectedcols[jj] )[ii]),
				Column( _dt, selectedcols[jj] )[ii] = Column( _dt, selectedcols[jj] )[ii - 1]
			);
		)
	);
	_nw << Close Window;
);

_nw = New Window( "Fill Down Blanks",
 _tb = Text Box("This script will fill missing values in the columns you select, by using the value in the last previous not empty row for that column"),
	_hb = H List Box(
		_pb = Panel Box( "Select Columns", _lb = List Box( colnames, width( 300 ) ) ),
		_bb = Button Box( "Go", fillcolumns )
	)
);


View solution in original post

4 REPLIES 4
MathStatChem
Level VI

Re: Fill missing values using last or previous observation

I happen to have a utility script that does this

 

Names Default To Here( 1 );

/****************************************************************
Description: This is a data manipulation utility.  The user is presented with dialog
to choose one or more columns from the data table.  Upon selecting the "Ok" button, 
the missing values in the chosen columns will be "filled down".  Filling down is performed by
copying the last previous non missing value that is present in the previous rows.  This is performed sequentially by 
row, starting with the second row in the data table.  

This script is useful in situations where a data table has been created or imported that has
cells not completely filled as the should be (such as when importing an excel table with
merged cells in the data).

As an example a data table with this structure:

Row	Lab	Setup	Replicate 	Result
1	A	1		1			100
2		.		2			101
3		.		3			103
4		2		1			101	
5		.		2			99
6		.		3			97

If the columns "Lab" and "Setup are chosen to be filled down, then the resulting table is
Row	Lab	Setup	Replicate 	Result
1	A	1		1			100
2	A	1		2			101
3	A	1		3			103
4	A	2		1			101	
5	A	2		2			99
6	A	2		3			97

*****************************************************************/

_dt = Current Data Table();

colnames = _dt << Get Column Names( String );

fillcolumns = Expr(
	selectedcols = _lb << Get Selected;
	nc = N Items( selectedcols );

	nr = N Rows( _dt );

	For( ii = 2, ii <= nr, ii++,
		For( jj = 1, jj <= nc, jj++,
			If( is missing(Column( _dt, selectedcols[jj] )[ii]),
				Column( _dt, selectedcols[jj] )[ii] = Column( _dt, selectedcols[jj] )[ii - 1]
			);
		)
	);
	_nw << Close Window;
);

_nw = New Window( "Fill Down Blanks",
 _tb = Text Box("This script will fill missing values in the columns you select, by using the value in the last previous not empty row for that column"),
	_hb = H List Box(
		_pb = Panel Box( "Select Columns", _lb = List Box( colnames, width( 300 ) ) ),
		_bb = Button Box( "Go", fillcolumns )
	)
);


Aziza
Level IV

Re: Fill missing values using last or previous observation

Great! It worked fine! Thank you! 

Greetings
theseventhhill
Level II

Re: Fill missing values using last or previous observation

Any suggestions on how to handle grouping variable(s) to the fill down script?

Assuming the group_by col is "grpcol"

I tried adding a line Group By(:grpcol); I keep getting "name unresolved: Group By in access or evaluation of Group By.

Can live without the friendly UI for selecting the columns but adding a grouping variable to the generic script would be great to have.

Any suggestions?

MathStatChem
Level VI

Re: Fill missing values using last or previous observation

Also check out this more comprehensive data table tools add-in  https://community.jmp.com/t5/JMP-Add-Ins/Data-Table-Tools-Add-in/ta-p/28582