Created:
| Last Modified:
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
1 ACCEPTED SOLUTION
Accepted Solutions
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 ) )
)
);
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 ) )
)
);
Hi, for those of you interested in using the Missing Data Pattern to improve data quality I am giving a brief update here:
I had been using the script by @txnelson to build better data sets but then I realised that there is an instance where I would like to combine columns despite their respective missing data pattern (and complement) not matching. It is when both columns have missing data in the same rows.
Here is an example, building upon the original example :
I would like to combine columns associated with Step6 as seen above.
It then occurred to me that it would help to know the percent matching between columns prior to any data handling. This would give the possibility to use such information when deciding whether to combine or not.
Having communicated with @txnelson he kindly developed his script.
Attached is the final version.
I am also including below some examples using "real" data where this method has been used to improvement data quality:
Note: Cells coloured blue denote missing data
Case 1:
From
to
Case 2:
From
to
There is a vast reduction in the columns and much fewer "empty" cells
As you can imagine, this allows for improved, faster and more efficient root cause analyses.
Attached are the scripts to make the simulated missing data example and the final script which reports "Percent Combinable" and "Columns that can be combined" based on missing data patterns.
- Philip
Went maybe a bit too heavy on scripting.. I didn't use Missing Data Patterns in this solution due to possibility of having more than 2 similar columns (not sure if it is the case here). Most likely these could also be detected with Missing Data Patterns but I made my own solution.
Result table:
New columns are created and old ones are left alone (could be easily deleted). Value in the new column name between brackets tells how many columns were combined to create the new column. Step5.v1 and Step5.v2 are not combined due to row 12 having same value twice. Should be easy to change by modifying function F_checkForInversePatterns in the script to allow columns which have matching data on same row.
This solution doesn't take into account how similar the data is inside columns (would require one additional check). Also column name similarity is simply checked if the Step name before period is same between columns.
Script attached.