cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
matt7109
Level III

Sort a column by variable

Hi,

How would I go about creating a new column that contains the sorted values of one column by another variabe?

An example to hopefully clarify: if I had a table with two rows: Name and age, how would I create a third row that displays the names sorted from oldest to youngest?

 

Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Jeff_Perkinson
Community Manager Community Manager

Re: Sort a column by variable

What you've asked for is not easily done in a formula (that I can come up with anyway). It's relatively easy to do this interactively by using Tables->Sort to sort the table into a new table and then copy and paste the column of interest back into the original table.

 

However the question raises some interest about what you're really trying to do.

 

Generally speaking, a row of a data table represents a collection of values for a single entity – an individual student in your simple example – and each row is unrelated to other rows. I'm not sure how this new column – of Names in your example – would be useful in an analysis since the new column is only meaningful in the context of it's order.

 

Can you explain a little bit more of how you'd use this new column?

 

An example to hopefully clarify: if I had a table with two rows: Name and age, how would I create a third row that displays the names sorted from oldest to youngest?

 


PS: you seem to have confused rows and columns in your example. I'm assuming that you really meant a table with two columns and that you wanted to create a third column as your original question states. If I'm mistaken and you really meant rows, please let us know.

-Jeff

View solution in original post

3 REPLIES 3
Jeff_Perkinson
Community Manager Community Manager

Re: Sort a column by variable

What you've asked for is not easily done in a formula (that I can come up with anyway). It's relatively easy to do this interactively by using Tables->Sort to sort the table into a new table and then copy and paste the column of interest back into the original table.

 

However the question raises some interest about what you're really trying to do.

 

Generally speaking, a row of a data table represents a collection of values for a single entity – an individual student in your simple example – and each row is unrelated to other rows. I'm not sure how this new column – of Names in your example – would be useful in an analysis since the new column is only meaningful in the context of it's order.

 

Can you explain a little bit more of how you'd use this new column?

 

An example to hopefully clarify: if I had a table with two rows: Name and age, how would I create a third row that displays the names sorted from oldest to youngest?

 


PS: you seem to have confused rows and columns in your example. I'm assuming that you really meant a table with two columns and that you wanted to create a third column as your original question states. If I'm mistaken and you really meant rows, please let us know.

-Jeff
IcarusAgenda
Level I

Re: Sort a column by variable

If you have your data in rows instead of columns, like in Excel, you'd need to transpose the table before analyzing it. In your example, after transpose, you will have two columns: Name and age. At this point, it simply becomes a matter of sorting the table by the age column, as suggested by other users.

ih
Super User (Alumni) ih
Super User (Alumni)

Re: Sort a column by variable

Give this a try:

 

names default to here(1);

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

dt << New Column(
	"Sorted names by age",
	Expression,
	"None",
	Formula( :Name[ Rank( :age << Get Values )][ Row() ] )
);

dt << New Column(
	"Sorted names by height",
	Expression,
	"None",
	Formula( :Name[ Rank( :height << Get Values )][ Row() ] )
);

 

Note that it might be slow for large tables.

 

Edited to show a more relevent example.