topic Re: Overlap / Gap Analysis by Row in Discussions

Overlap / Gap Analysis by Row

MuMover — Fri, 09 Jun 2023 00:43:15 GMT

I'm looking for a method to run a gap and overlap analysis across a set of data 1000-ish rows. Data are teams ranking their test coverage of each feature as 1, 2 or 3, Scores. I want to look for gaps across the rows where no group has scored it a 1 and also show where multiple groups rank as 1. A snapshot of the data table is below.

Feature #	FVT	EVT	CVT	MSV	GQ	Cert	HW	PE	MFG Test	PDE	TCM / FAE?
1	1		2	3		3	1	2	2
2	1		2	3		3	1	2	2
3	3	3	3	3	3	3	3	3	3	3	3
4	3	3	3	3	3	3	2	3	3	3	3
5	1		2	2		3	3	2	2
6	1		2	2		3	3	2	2
7	1		2	2		3	3	2	2
8	1		2	2		3	3	2	2

Re: Overlap / Gap Analysis by Row

txnelson — Sat, 18 Dec 2021 03:26:05 GMT

I am not exactly sure what you are looking for, but here is a little script I wrote that produces the results I think you want

Names Default To Here( 1 );
dt = Data Table( "MuMover Example" );
colNames = dt << get column names( continuous, string );

Remove From( colNames, 1, 1 );
Remove From( colNames, -2, 2 );

columnsWithNoOnes = {};

For( i = 1, i <= N Items( colNames ), i++,
	Summarize( dt, byGroup = By( colNames[i] ) );
	If( Contains( byGroup, "1" ) == 0,
		Insert Into( columnsWithNoOnes, colNames[i] )
	);
);

featureWithMultipleOnes = {};
countOfMutipleOnes = {};

For( i = 1, i <= N Rows( dt ), i++,
	temp = Loc( dt[i, Index( 2, 10 )], 1 );
	If( N Rows( temp ) > 1,
		Insert Into( featureWithMultipleOnes, i );
		Insert Into( countOfMutipleOnes, N Rows( temp ) );
	);
);

New Window( "Results",
	H List Box(
		Outline Box( "Groups with No 1's",
			Table Box( String Col Box( "Group", colNamesWithNoOnes ) )
		),
		Spacer Box( size( 15, 0 ) ),
		Outline Box( "Features With Multiple 1's",
			Table Box(
				Number Col Box( "Feature", featureWithMultipleOnes ),
				Number Col Box( "Count", countOfMutipleOnes )
			)
		)
	)
);

Re: Overlap / Gap Analysis by Row

MuMover — Mon, 20 Dec 2021 23:30:11 GMT

Thanks for getting back to me Jim. I should have been more clear on desired outcome. My goal is to have a Feature table showing what group has entered an 1 in their cell and if no groups have entered a 1. For example: in the table above the first feature has two groups showing a 1, FVT and HW, so for that feature the outcome would be FVT HW. For feature 3, no group claims a 1, so outcome for feature 3 would be "Gap". Hope that makes sense.

Re: Overlap / Gap Analysis by Row

txnelson — Tue, 21 Dec 2021 03:04:22 GMT

Here is a formula that calculates what you want

If( Row() == 1,
	colNames = Current Data Table() << get column names( continuous, string );
	Remove From( colNames, 1, 1 );
	Remove From( colNames, -2, 2 );
);
display = "";
For( i = 1, i <= N Items( colNames ), i++,
	If( Row() == 1,
		colNames = Current Data Table() << get column names( continuous, string );
		Remove From( colNames, 1, 1 );
		Remove From( colNames, -2, 2 );
	);
	display = "";
	For( i = 1, i <= N Items( colNames ), i++,
		cname = colNames[i];
		Eval( Parse( Eval Insert( "If( ^cname^ == 1, display = display || \!" \!" || colNames[i] );" ) ) );
	);
	Trim( display );
);
If( display == "",
	display = "Gap"
);
Trim( display );

Re: Overlap / Gap Analysis by Row

jthi — Tue, 21 Dec 2021 06:19:08 GMT

Here is other solution using matrices (could be turned into formula also):

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(8),
	Compress File When Saved(1),
	New Column("Feature #", Numeric, "Continuous", Format("Best", 12), Set Values([1, 2, 3, 4, 5, 6, 7, 8])),
	New Column("FVT", Numeric, "Continuous", Format("Best", 12), Set Values([1, 1, 3, 3, 1, 1, 1, 1])),
	New Column("EVT", Numeric, "Continuous", Format("Best", 12), Set Values([., ., 3, 3, ., ., ., .])),
	New Column("CVT", Numeric, "Continuous", Format("Best", 12), Set Values([2, 2, 3, 3, 2, 2, 2, 2])),
	New Column("MSV", Numeric, "Continuous", Format("Best", 12), Set Values([3, 3, 3, 3, 2, 2, 2, 2])),
	New Column("GQ", Numeric, "Continuous", Format("Best", 12), Set Values([., ., 3, 3, ., ., ., .])),
	New Column("Cert", Numeric, "Continuous", Format("Best", 12), Set Values([3, 3, 3, 3, 3, 3, 3, 3])),
	New Column("HW", Numeric, "Continuous", Format("Best", 12), Set Values([1, 1, 3, 2, 3, 3, 3, 3])),
	New Column("PE", Numeric, "Continuous", Format("Best", 12), Set Values([2, 2, 3, 3, 2, 2, 2, 2])),
	New Column("MFG Test", Numeric, "Continuous", Format("Best", 12), Set Values([2, 2, 3, 3, 2, 2, 2, 2])),
	New Column("PDE", Numeric, "Continuous", Format("Best", 12), Set Values([., ., 3, 3, ., ., ., .])),
	New Column("TCM / FAE?", Numeric, "Continuous", Format("Best", 12), Set Values([., ., 3, 3, ., ., ., .]))
);

col_names = Remove(dt << Get Column Names("String"), 1); //drop feature
all_ones = dt[0, 2::N Cols(dt)] == 1; //print this 
dt << New Column("Summary", Character, << Set Each Value(
	teams = Loc(all_ones[Row(), 0]);
	If(N Items(teams) > 0,
		Concat Items(col_names[teams], " ")
	, "Gap"
	)
));

Some explanation:

col_names = Remove(dt << Get Column Names("String"), 1); get team names

all_ones = dt[0, 2::N Cols(dt)] == 1;Get values in datatable to matrix. If cell value is 1 set it as 1, if it is missing leave as missing and other values will become 0

teams = Loc(all_ones[Row(), 0]) For current Row() get indices for columns which have 1

If(N Items(teams) > 0, Concat Items(col_names[teams], " ") , "Gap") Use col_teams[teams] to get list of teams which have 1, if teams count is 0 set as "Gap"

Re: Overlap / Gap Analysis by Row

MuMover — Tue, 21 Dec 2021 16:19:35 GMT

Thanks Jim!