Is it possible to use the Missing Data Pattern to speed up data preparation?

jpol

Lack of consistent data quality hinders data analysis and deters many from embarking on a Machine Learning journey.

In the semiconductor industry it is expected that processes are continuously improved. Unfortunately, one of the side effects of such endeavours is a change in data structure. One reason for this could be the renaming of the process step.

 

I have simulated such a case below where 20 lots have been processed through 4 process steps having step name changes.

For root cause analysis it is necessary to compare the probe results from equipment associated with a specific step. In the case below we can see that the data have become stratified.

 

I know that I could continue to use the "Combine Columns" feature in JMP (Cols\Utilities\Combine columns...) but in reality I have several hundreds of columns which need to be checked and combined.

 

When using the Tables\Missing Data Pattern feature in JMP I can readily see the data structures and potential columns to be combined.

I was wondering if the information from the Missing Data Pattern could be used to script the combine columns requirement?

 

There would be several checks required before automating the process such as:
     Are columns representing the same Step in the process?
     Are the contents of both steps similar?
     Is the Missing Data pattern and inverse for paired columns?

 

 

Broken data looks like:

 

 

 

Missing Data Pattern:

 

 

 

Re constructed data:

 

 

- jpol

 

txnelson

I think you can use the missing data pattern to identify the columns that have data with no intersection for either the non missing patterns and the missing patterns.  This script uses the output table from the Missing Pattern platform, using a slightly modified Missing Data Pattern data table from the JMP Sample data tables.  It finds all of the inversely matched patterns and then displays them in a display window

names default to here(1);
dt=New Table( "example",
	Add Rows( 4 ),
	New Column( "Count",
		Numeric,
		"Continuous",
		Format( "Fixed Dec", 10, 0 ),
		Preselect Role( Freq ),
		Set Values( [1, 3, 1, 2] )
	),
	New Column( "Number of columns missing",
		Numeric,
		"Continuous",
		Format( "Fixed Dec", 10, 0 ),
		Set Values( [1, 2, 1, 2] )
	),
	New Column( "Patterns",
		Character,
		"Ordinal",
		List Check( {"0010", "0011", "0100", "0101"} ),
		Set Values( {"0010", "0011", "0100", "0101"} )
	),
	New Column( "Trial 1", Numeric( 1 ), "Nominal", Set Values( [0, 0, 0, 0] ) ),
	New Column( "Trial 2", Numeric( 1 ), "Nominal", Set Values( [0, 0, 1, 1] ) ),
	New Column( "Trial 3", Numeric( 1 ), "Nominal", Set Values( [1, 1, 0, 0] ) ),
	New Column( "Trial 4", Numeric( 1 ), "Nominal", Set Values( [0, 1, 0, 1] ) )
);
cols1List = {};
cols2List = {};
For( i = 4, i <= N Cols( dt ), i++,
	comp = Associative Array( dt << get rows where( As Column( dt, i ) == 1 ) );
	compMissing = Associative Array( dt << get rows where( As Column( dt, i ) == 0 ) );
	If( N Items( comp ) > 0,
		For( k = i + 1, k <= N Cols( dt ), k++,
			comp2 = Associative Array( dt << get rows where( As Column( dt, k ) == 1 ) );
			comp2Missing = Associative Array( dt << get rows where( As Column( dt, k ) == 0 ) );
			comp2 << intersect( comp );
			comp2Missing << intersect( compMissing );
			If( N Items( comp2 << get keys ) == 0 & N Items( comp2Missing << get keys ) == 0,
				Insert Into( cols1List, Column( dt, i ) << get name );
				Insert Into( cols2List, Column( dt, k ) << get name );
			);
		)
	);
);
nw = New Window( "Results",
	Outline Box( "Columns that can be combined",
		Table Box( String Col Box( "Column", cols1List ), String Col Box( "With", cols2List ) )
	)
);

 

example.jmp