Restructuring a table for Multifactorial Analysis

Question

I need to transform my table to a structure like the one Jian has in his webinar on a sensory study using wine data.

Right now, I have my table structured so that a block is a product (the Sample code) with panelists and 4 features.

Analyst Code Sample CodeF1F2F3F4P1S123758.10.7P2S12343.77.61.4P3S1236.57.57.90.1P1S12486.57.51P9S1246.54.55.54P10S1245.5470.5P2S124567.81P3S1246880.2

Is there a way in jmp (or excel) to reformat using Panelist as the block mode? In this example, I will end up with 2 rows of samples, and 20 columns.  [The original data has a lot more samples, panelists, and sample features].

TCM · Answer

Order of the columns is not so much the problem (though it would be annoying since I have to do it for hundreds of columns).  The problem is that when you work with the entire table from the onset (instead of breaking into subsets of samples), the script thinks the number of panelists is the same for each product tested.  In the real world, not all panelists see all the products.  Some products may be seen by 5, some by 13, etc... From my results, it seems to impose the highest number of panelists in the study to each sample, and that is simply not the truth.

The only way I can think of to preseve the integrity of the data is to subset first then split then put them all together- which is a massive undertaking since the original data is 204 rows by 34 features.

cwillden · Answer

What do you mean the columns are not right?  Are they just in a different order than what you wanted?

TCM · Answer

Thanks for the suggestion.

In your suggestion, the rows are correct (each row is a product). But the columns are not right. The col output is: F1Analyst1, F1Analyst2, F1 Analyst3, F2Analyst 1...

The desired col structure is:Analyst1F1, Analyst1F2, Analyst1F3...

I'm trying out different roles, but I am getting a lot of missing values.  That's probably because not all the samples were seen by all panelists (e.g., sample 1 was seen by 3 people, sample 4 was seen by 6, sample 2 was seen by 5...) and the function is treating the analyst grouping as consistent across samples.

I think the solution is probably 2-step, or requires some type of dummy cells.