How do I sort data individually in a column when the data is given as a group?

Question

The data I am looking at is movies and one of the columns is genres. However JMP has the genres that a particular movie is categorized under grouped as one data set "Fantasy|Horror|Romance|Thriller" rather than as individual movie genres "Fantasy", "Horror", "Romance", "Thriller". I want to separate the genre column so that I can analyze the genres individually. How would I clean the data so that when I go to sort the movies based on genre it gives me the individual genre rather than the grouped set?

txnelson · Accepted Answer

Click on the column header for the column in question(genres).  Then go to the column pull down menu and select

Cols=>Utilities=>Text to Columns

Fill out the simple dialog box that pops up to specify your "|"

and click OK, and the different fields from the multiple field column will be separated into new columns.

Note: This is a data table I just threw together.  The genres are just random values

Now just go to

Tables=>Stack

and stack all of the new genres columns

This will give you a table that actually will give you a nice way to select Genres.

You can also go another step, and split the data

Tables=>Split

Where you Split By the Genres column, Group by the Title column, and also specify the Genres column as the Split column

It will give you a data table like the one below

If this doesn't meet your requirements, please specify what you want the data table to look like, and I am sure the Community can come up with an answer on how to transform your data.