Table Split results do not correspond to the remaining column

Question

Hello Community,

I'm trying to do a simple split, but the new table do not correspond to the original data.

I have 3 columns: Product, Score, and Comments. Row 100 has Product = C, Score = 9, Comments = "lighter more acidic."

I did Table Split on Product. Now in the new table, "lighter more acidic" is under Product C column, but has score of 8. I'm expecting this to have score of 9.

What am I doing wrong? Am I misunderstanding how "Split" works? I have my data file and screen shot of what I've done in the attachment.

I'm using JMP®  15.0.0  / Microsoft Windows 10 Enterprise 64-bit.

Thank you!

Lei

Column Split Screen Shot.pptx

PRODUCT DATA.jmp

Dan_Obermiller · Answer

Think carefully before deleting any of the rows. You have three scores per observation in your original data. Deleting any of the rows is saying that you do not need all of the scores. It can also lead to inconsistencies such as having the score for Product C on row 1, but the score for Product A on row 2.

A small JMP script could remove the extra two comments in a column, if desired.

WineAroma · Answer

Hi @Dan_Obermiller,

Yes! This is exactly what I want to see. I'm planning to sort by Score at the end. I've thought about "stack" option but 3 repeating comments are hard to follow. And sorting out which 2 of 3 rows to delete seems complicated.

Row  Score                A                                       B                        C

7          4                                                                                   finish is less.....

8          7                                                       oily texture....

9          3              reduced or ....

I usually create macro and do "cut & paste" in Excel. It seems like it is the best option for now.

Thanks Everyone!

Lei

Dan_Obermiller · Answer

I just realized another possibility. Would you want the resulting table to look like this? Row Score A B C 1 0 *score is from A, B and C are "empty" 2 1 *score is from B, A and C are "empty" 3 1 softer *score is from C, A and B are "empty" This approach you are essentially "copying" the score column, and splitting the comments into A, B, and C. In order to keep the score column "as is" you need to pad empty two empty cells in A, B, and C. Maybe easier to see on rows 7, 8, and 9: Row Score A B C 7 4 finish is less..... 8 7 oily texture.... 9 3 reduced or .... Is this what you are looking for? If so, I see this as a two-step process. Tables > Split Split Columns are Comments and Score Split By column is Product. Now on this new table you are going to Tables > Stack. Stack Columns are Score A, Score B, and Score C. Stacked Data Column can be renamed to Score This will give roughly what I have here, but it will repeat each comment three times because a row is an observation of the original table.