topic Re: summary table sorted in the wrong order if there are value labels in Discussions

summary table sorted in the wrong order if there are value labels

ron_horne — Tue, 16 Jun 2015 23:21:52 GMT

hi all,

just wondered if i came across a bug in the program.

table summary is known to produce a sorted table by the grouping variable. i just noticed it is not the case if there are value labels (and no value ordering).

for example:

Open( "$SAMPLE_DATA/Big Class.jmp" );

// this will produce a correctly sorted table

Data Table( "Big Class" ) << Summary( Group( :age ), Link to original data table( 0 ) );

// and here is where thing go a bit funny

:age << value labels( {13 = "thirteen"} );

Data Table( "Big Class" ) << Summary( Group( :age ), Link to original data table( 0 ) );

Re: summary table sorted in the wrong order if there are value labels

mikedriscoll — Wed, 17 Jun 2015 13:56:16 GMT

Your script didn't behave as you describe. When I ran your code, it applied the :age <<value labels() command to the first summary table, and it was already sorted and therefore in the correct order.

This modified code behaves as you describe. I don't have an answer for you as to why it is happening but maybe it will help someone else trouble shoot. I'm running JMP standard 11.2

Open( "$SAMPLE_DATA/Big Class.jmp" );

dt = current data table();

// this will produce a correctly sorted table

Data Table( "Big Class" ) << Summary( Group( :age ), Link to original data table( 0 ) );

// and here is where thing go a bit funny

//as column(dt, :age) << value labels( {13 = "thirteen"} ); // <-- for some reason this doesn't change the "Big Class" table for me. It still changes the above summary table even though i specify dt. Use below code to fix it.

current data table(dt);

:age << value labels( {13 = "thirteen"} );

Data Table( "Big Class" ) << Summary( Group( :age ), Link to original data table( 0 ) );

Re: summary table sorted in the wrong order if there are value labels

chungwei — Wed, 17 Jun 2015 14:38:00 GMT

In summary, the categories of the grouping column are ordered by the labels of the corresponding values.

For categories without labels, their values are formatted into text strings and ranked after the sorted labels (or before, if sorting the grouping column in descending order).

This is what you observed in your example.

Re: summary table sorted in the wrong order if there are value labels

ron_horne — Wed, 17 Jun 2015 21:23:47 GMT

Thanks Mjdeez,

i was using version 10.

Re: summary table sorted in the wrong order if there are value labels

ron_horne — Wed, 17 Jun 2015 21:26:46 GMT

thanks ChungWei,

i just find it a little surprising the sorting is done by labels and not by value. i was actually counting on the sorting and was not expecting this at all. if it is intended it is probably trying to think one step ahead of the user.

Re: summary table sorted in the wrong order if there are value labels

Jeff_Perkinson — Wed, 17 Jun 2015 21:41:21 GMT

I think the issue here is what should JMP do if there are two different values mapped to the same value label.

In your example you've only got one but consider this:


Open( "$SAMPLE_DATA/Big Class.jmp" );

dt = current data table();


:age << value labels( {13 = "thirteen", 17="thirteen"} );


Data Table("Big Class")<<Summary( Group(:age ), Link to original data table(0));

Since "thirteen" now represents two different ages it can't sort in either place.

-Jeff

Re: summary table sorted in the wrong order if there are value labels

ms — Wed, 17 Jun 2015 22:11:34 GMT

While that makes sense, Jeff, Graph Builder() seems to disagree with Summary() on how to sort categories with value labels.

dt = Open("$SAMPLE_DATA/Big Class.jmp");

dt:age << value labels({13 = "thirteen", 17 = "thirteen"});

dtsum=(dt << Summary(Group(:age)));

dtsum << Graph Builder(Variables(X(:age), Y(:N Rows)));

// And "thirteen" seems to represent 13 only

Show(dtsum:age << get values, Col Sum(dtsum:age));

Re: summary table sorted in the wrong order if there are value labels

ron_horne — Thu, 18 Jun 2015 10:25:51 GMT

Thanks Jeff and MS.

i just find it inappropriate that jmp takes value labels and all the sudden assumes the user has intended to recode the variable.

ron

Re: summary table sorted in the wrong order if there are value labels

ron_horne — Thu, 18 Jun 2015 11:16:53 GMT

thanks Jeff, this behavior of "recoding" by value labels does not happen (an shouldn't) in any of the analysis platforms.

even in other table menu options it doesn't do it such as split.

dt = Open("$SAMPLE_DATA/Big Class.jmp");

dt:age << value labels({13 = "thirteen", 17 = "thirteen"});

Data Table( "Big Class" ) << Split(

Split By( :age ),

Split( :height, :weight ),

Remaining Columns( Drop All )

);

Re: summary table sorted in the wrong order if there are value labels

chungwei — Fri, 19 Jun 2015 18:53:22 GMT

When somebody assigns the same label to 2 different values, wouldn't you think that he would like to group the 2 together ?

Re: summary table sorted in the wrong order if there are value labels

ron_horne — Sat, 20 Jun 2015 21:51:27 GMT

Hi ChungWei,

I don't think so. JMP has the perfect flexibility to offer value labels on top of the values themselves which is complementary and should not be a substitute. this allows for multidimensionality.

table summary is the only case i came across so far in the program that has this behavior of guessing that value labels should be recoded on the fly.

please let me know if you are aware of any other platforms/processes that do that.

in addition i do not find it correct to sort by the labels rather than the values. consider the following: using a variable that takes values from 1-5 where the user has labeled 1 as "Low" and 5 as "High". in this case the summary by that variable will come in an unintuitive order. (i.e. High, Low, 2, 3, 4 instead of low, 2, 3, 4, High). and that is in a case of trivial labeling. if labeling is more "sophisticated" then the difference between intuition and results can increase even further.

Thanks!

Re: summary table sorted in the wrong order if there are value labels

chungwei — Mon, 22 Jun 2015 13:02:13 GMT

The case that you mentioned, 1 labeled as low, 5 as high, comes out as you expected, low is sorted first, high is sorted last in summary.

Other platforms that do that are tabulate and chart.

Re: summary table sorted in the wrong order if there are value labels

chungwei — Mon, 22 Jun 2015 13:07:07 GMT

Grouping the data by labels gives the flexibility of aggregating different values into a group without actually changing your data.

It also removes the ambiguity, what to do when 2 values are mapped to the same label.