7-Minute Video: How to reshape data from today's SE meeting WITHOUT scripts.

Question

Hi SEs,

After today's meeting, I wanted to see if I could reshape the data by a more customer-friendly means. I was able to come up with something that really didn't involve scripting, but did involve some ideas and JMP functionality that maybe aren't top-of-mind for everyone (for example, Tables > Transpose is something I used, which obviates the need for the nastiest code snipped I used earlier this morning, and I show a way to use JMP's built-in support for indicator columns that allows the original flags to be built without using formulas.)

I made a quick video to share this, hopefully some of you will find this useful.

From start to finish, the reshaping only took about 7 minutes, while talking through it, which seems to fall in the "not too bad" category for me... certainly an easier sell for a customer than some of the scripts I was using earlier.

The link is below. In case you'd like to follow along, I've attached the starting file, as well.

https://sas.webex.com/sas/lsr.php?RCID=9dafd6121d9e4597a9623b56ac253359

Cheers,

Brady

CopiedPastedSpinResults.jmp

Byron_JMP · Answer

This might work too, of course its just another variant, no formulas, less typing, but it uses a script (but its not a novel custom one).

Clean the Data

Col>Utilities>combine columns, no delimiter, column name “combined”

Col>Utilities>Recode>group similar values>max char diff=1 , done in place

Select row with missing, select matching cells, delete cells

Make Deck Indicator Column

Select row with “situation”, select matching cells.

Row, row selection, name selection

On Label column, New Column Formula, row, cumulative sum

Name this column “deck”

Make the Type Indicator Column (very carefully in this specific sequence)

Make a new column (double click after last column)

Select one row with “situation”, “need”, “pain”, “implication”, select matching cells, click copy

Click on the new column and click paste. it is very important that the row selection doesn’t change

Name this new  column “type”

Run this handy script to fill in the missing cells on the “type” column

https://community.jmp.com/docs/DOC-6515

Format for analysis

Tables, Split, Split=combined, group=deck, split by=type

brady_brady · Answer

Jordan also makes the point that you could simply select (after recoding) the first 4 occurrences of "SITUATION", "PAIN", "IMPLICATION", "NEED", then select matching cells, and mark that selection in a new column.

This of course, is faster than pretty much any other option, and for this dataset works well. You'll likely need another approach for bigger datasets, though, or datasets where the keywords are sparsely located within the rows.

So, the other approaches will work in many of those cases... but as the problem really gets large, scripting will probably be the way to handle it.

I guess the real idea here is to try as many of these approaches as you can before launch... that way, when a data table is thrown at you, you've at least got an idea--without having to actually work through the cleanup--of how much effort is likely involved, and whether it is feasible for the cleanup to be tackled via a point-and-click method.

brady_brady · Answer

SEs,

From about 02:50 - 03:30 in the video above, I create a flag column. Another way to do this, which I like even better, actually, is to use Summary, grouping by the indicator columns Situation, Pain, Implication and Need. This obviates the desire for the recoding between names like "0 Situation", and "Situation", that is done in the video above.

The end result is that using Summary, selecting the rows with NOT all zeros in the summary table, and naming the selection back in the original table can take this particular recoding exercise from about 6 minutes to about 4 minutes... worth investigating to see how you like it.

Combining columns to make the 4-character flag is still a nice trick, but if you don't need it in the table (which we really don't, we just need to mark where there is at least one "1" in the string), then Summary will make for a faster process.

Cheers,

Brady