266249_en-US

liliconcom

Table Split results do not correspond to the remaining column

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

 

 

 

Re: Table Split results do not correspond to the remaining column

Hi @WineAroma 

when i do the split by PRODUCT i get the 9 in column SCORE C in row 34. in which column do you get an 8 in row 34?

 

Re: Table Split results do not correspond to the remaining column

Hi @ron_horne,

 

Thanks for the reply!

 

I only want to split "comments" and not "score." After split, I want 4 columns: Score, A, B, C. It will still end up with 120 rows. Is it possible?

 

I just realized that when I split with just "comments,"JMP only keeps "score" for Product A.

 

How do you paste the picture inside the post? I couldn't figure out, so you need to see my screenshot on the attached power point on the original post. When I split just by "comments," your row 34 is on my row 40.

 

Thanks!

Lei

 

 

Re: Table Split results do not correspond to the remaining column

So in column A you want all of the comments for Product A.

Row number 1 of the table would be getting the first comment for each Product type, and similarly for row 2:

 

Row          A              B              C                   Score

1                   softer                    ?

2                   more astringent    ? (0, 0, or 1)

 

What would you want for the score? For row one, it could be 0 (from A), 1 (from , or 1 (from C).

 

Re: Table Split results do not correspond to the remaining column

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.

Re: Table Split results do not correspond to the remaining column

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

Re: Table Split results do not correspond to the remaining column

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.