Suppose I have this table: CategoryItemLevelA1deltaA2foxtrotA3hotelB1julietB3lima Note that Category=B, Item=2 is missing. If I try to "split" this table with "Split By" = Category and "Split Columns" = Level, and "Keep All", it returns this: ItemAB1deltajuliet2foxtrotlima3hotel Note that on the newly Split table, it shows that Item=2,Category=B is "lima", but that's incorrect -- that cell should be blank, and "lima" should be under Item=3,Category=B. Is this user error, or is this a bug in JMP?

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Bad Splitting?

BHarris

Suppose I have this table:

Category	Item	Level
A	1	delta
A	2	foxtrot
A	3	hotel
B	1	juliet
B	3	lima

Note that Category=B, Item=2 is missing.

If I try to "split" this table with "Split By" = Category and "Split Columns" = Level, and "Keep All", it returns this:

Item	A	B
1	delta	juliet
2	foxtrot	lima
3	hotel

Note that on the newly Split table, it shows that Item=2,Category=B is "lima", but that's incorrect -- that cell should be blank, and "lima" should be under Item=3,Category=B.

Is this user error, or is this a bug in JMP?

Accepted answers

Chris_Kirchberg

Hi @BHarris, try using Item in group. The result will be what you would expect.

Best,

All comments

Chris_Kirchberg

Hi @BHarris, try using Item in group. The result will be what you would expect.

Best,

BHarris

Yes, that does seem to work.

Can you explain what's going on? I'd like to understand why the Group step was necessary.

Chris_Kirchberg

Sure, Splitting without the group is doing it in a literal fashion by row and is not aware that Item has a missing component for category. Group forces level to separate out by Item for each category by taking a look at Item first then Category.

From online help (https://www.jmp.com/support/help/en/18.2/?os=mac&source=application#page/jmp/split-columns-in-data-tables.shtml

Group

Specifies a Group variable when you want your data to be split within each group of the selected variable. Each group results in a row in the output table. You must also specify the required variables, Split By, and Split Columns.

Note: If the variable that you want to group by contains unequal groups or is in a random order, specifying it as the Group variable ensures that your data is restructured properly, and any missing values are assigned in the appropriate places.

Otherwise you would have to put in an extra row and define Category and Item but leave Level blank for that row.

Hope that helps.

BHarris

The more I think about this, the less it makes sense. I don't understand why split would behave like that under any circumstance -- almost like the developers simply nested some loops and hoped that the data would fall in the right spots...

I'm left wondering if (a) I'm still too dumb to understand the underlying rationale for this behavior, (b) this was implemented this way to offer high performance (fast splits on large tables) where the inputs are full-factorial and the user is expected to know that assumption is being made, or (c) it's really just a bug that needs to be submitted/fixed.

(I won't be offended if it's (a). )

BHarris

I'd love to see an animation someday that shows what split (and stack) are doing. I imagine the split-by column values being pushed up into the column headers, and the split-columns getting pushed into the cells of those new columns. (I was surprised just now to learn that if there are multiple split-columns for a given split-by column, then new numbered columns are created.)

But the real interesting part comes when there are conflicts, e.g. multiple values trying to end up in the same cell. JMP does give a warning that this is happening (which is great!), but this whole post came about from a dataset that looked like it was being split correctly, but after I dug into it I realized it wasn't, and I had ignored that warning because I didn't understand it.

My 2 cents, the warning should be changed from: "Multiple rows, possibly with different values, of the columns to be kept are mapped to the same row in the split table. Only one value of each column is retained." ---> to: "Multiple values being mapped to the same cell; only one is kept."

hogi

@BHarris wrote:
I was surprised just now to learn that if there are multiple split-columns for a given split-by column, then new numbered columns are created.

if multiple split-columns are used, JMP creates the column names as a combination of the name of the split-column - and the entry of the split-by-Column:

I only one column is selected as split-column, the name of the split-column is not included in the column name:

My colleagues roll their eyes when I start using Big Class to explain a functionality of JMP.
But it's a wonderful trick to use an alternative data set to play with the settings:
with "M" and "F" it's obvious what JMP is doing, no need to wonder about the "numbers" that appeared with the original data set.

Big Class looks very simple, but it's complicated enough to explain many of the details.
Just collinearities are missing ...