Summarize response data to multiple response modeling type

[#1]

I have a table of lots and wafers, which have been assigned a response code based on yield (red, green, blue; not my choice, but it's what I have to work with). There are combinations of lots and wafers that have multiple responses assigned to them. What I want to do is summarize the table by lot and wafer, and where a combination has multiple repsonses add all values to the variable.

From:

 

To:

 

[#2 · Accepted solution]

I had some old code that might help. Perhaps there is a better way now:

// ian.cox@jmp.com: 23Jun2012

NamesDefaultToHere(1);

// **********************************************************************************************
// Given a table (dt), a list of grouping columns therein (gCols), and a character response
// column (rCol), aggregates the latter into a multiple response column
// **********************************************************************************************
mrSummarize = 
Function({dt, gCols, rCol}, {Default Local},
	// Summarize using the specified grouping columns to get dt2
	dt2 = dt << Summary(Group(gCols));
	dt2 << setName("Multiple Response Summary of "||(dt << getName));
	// Loop over the rows of dt2 and build a new column with the multiple response values:
	// Exploit the linking between dt2 and dt to do this
	dt2 << newColumn(rCol, Expression, None);
	for(r2=1, r2<=NRow(dt2), r2++,
		dt2 << clearSelect;
		dt2 << selectRows(Matrix({r2}));
		r = dt << getSelectedRows;
		// Response values are initially put in a list to make getting the unique values easier
		mr2 = {};
		for(i=1, i<=NRow(r), i++,
			InsertInto(mr2, Column(dt, rCol)[r[i]]);
			);
		// Force the values in the list to be unique
		mr2 = associativeArray(mr2) << getKeys;
		// Put the list of unique values in the table
		Column(dt2, rCol)[r2] = mr2;
		);
	dt2 << clearSelect;
	// Now need to 'unpack' each list to get values suitable for the 'Multiple Response' modeling type
	allLists = Column(dt2, rCol) << getValues;
	for(r2=1, r2<=NRow(dt2), r2++,
		mr2 = allLists[r2];
		for(i=1, i<=NItems(mr2), i++,
			if(i==1,
				str2 = mr2[i],
				str2 = str2 || ", " || mr2[i];
				);
			);
		allLists[r2] = str2;
		);
	Column(dt2, rCol) << setDataType("Character");
	Column(dt2, rCol) << setModelingType("Multiple Response");
	Column(dt2, rCol) << setValues(allLists);
	dt2;
	);

// Sample table
dt = New Table( "Data",
		Add Rows( 3 ),
		New Column( "Lot",
			Numeric,
			"Nominal",
			Format( "Best", 12 ),
			Set Values( [1, 1, 2] )
		),
		New Column( "Wafer",
			Numeric,
			"Nominal",
			Format( "Best", 12 ),
			Set Values( [2, 2, 1] )
		),
		New Column( "Response",
			Character,
			"Nominal",
			Set Values( {"Green", "Red", "Blue"} )
		)
	);

// Try out the function
mrSummarize(dt, {"Lot", "Wafer"}, "Response");

[#3]

Thanks, I'll give it a shot.

[#4]

Question: @ian_jmp can you explain what's going on with: dt2 << selectRows(Matrix({r2}));? Why the matrix for a single interger?

[#5]

You are correct that:

dt2 << selectRows(r2);

also works. It's possible that in 2012 'selectRows' did need a matrix. But it's more likely that I just thought it did . . .

[#6 · Accepted solution]

Hi @MarkDayton ,

Perhaps you were looking for a scripted solution, in which case, you can stop reading... @ian_jmp 's works perfectly. However, if you want to do this interactively, the following should work for you:

1. Use Tables->Split (place Response in the Split By and Split Columns roles and Lot, Wafer in the Group By role):

2. In the new table, select the new columns and then use the Cols->Utilities->Combine Columns menu to create the new, multiple response column:

3. Press delete to do away with the select columns.

Hope this helps.

[#7]

Thanks, was hoping there was a single step process that I was missing, but this will get me there.

[#8]

That did it, though I still need to try Ian's script. I used Tabulate and Recode to accomplish the first step you did with stack, but I ended up with the same result for step two, which I hadn't used before. Again, thanks for the help.

[#9]

Did you see Help > Books > Consumer Research > Categorical Response Analysis chapter? The Categorical platform handles the multiple response type very well and provides many ways to summarize and visualize such data.

[#10]

I hadn't. Thanks for the pointer. I would never have associated Consumer Research with analysis of process control monitoring data from a semiconductor foundry :))