How long does this cleanup task take you?

brady_brady

Hi SEs (and others),

 

I've attached 2 tables. I'm thinking of discussing tips for table cleanup in an upcoming JMP On Air show, but want to make sure that a) I'm covering tips that will actually save a bunch of time and b) I'm using good techniques--i.e., if my way is slower than your way (and your way is understandable/appropriate for a JMP user with relatively basic skills), I want to show your way, not mine.

 

So, how long does it take you to turn the tall table into the wide one (both attached), subject to these constraints:

 

1) Given the audience for which this is intended, you may use NO SCRIPTS beyond relatively simple formula columns and built-in column transforms, table ops, selection, copy/paste, recode, stack, split, etc... i.e., built-in functionality.

 

2) "Author" in the final table is a multiple-response field, the formatting of which is less important than the fact that all authors are included. Were it not for the fact that from 1 to several author rows per Discovery paper appear in the source table, this would be a trivial reshaping exercise.

 

Looking forward to your results--thanks for playing.

 

Cheers,

Brady

 

Discovery 2019 Abstracts.jmp

Discovery Abstracts Final.jmp

MikeD_Anderson

I got it in 11 operations - mostly in Recode.  Intermediate and final files are attached.  There are probably improvements, but I tried to avoid JSL anywhere I could.

 

Here's my procedure:

Steps:

1. Right Click on Column 1 > Recode
   1. LRT > Collapse white space
   2. LRT > Trim white space
   3. Overwrite Column 1
2. Delete stuff after row 423
3. Delete stuff before row 34
4. Delete all the remaining empty rows
5. Delete Column 2
6. Right Click on Column 1 > Recode
1. LRT > Replace String
   1. Check Use Regular Expression
   2. Search Text: (Level|Topic|Session ID)
   3. Check Replace Entire String
   4. Replacement Text: \1
   5. Click OK
2. Recode > Group Controls > Check Show Only Ungrouped
3. Select everything
   1. Right click > Group to New Value.
   2. Set the new value to "" (empty).
4. Recode to new column - I called it Easy Headings
7. Create a new column - I called it Headings
   1. Change it to character
   2. Open the formula editor
   3. Here's the formula (yes - it's a complete hack):
      1. As JSL:
         

         If( :Easy Headings != "", :Easy Headings,
         Lag( :Easy Headings ) == "Session ID", "Title ",
         Lag( :Headings ) == "Title", "Author 1",
         Lag( :Headings ) == "Author 1", "Author 2",
         Lag( :Headings ) == "Author 2", "Author 3",
         Lag( :Headings ) == "Author 3", "Author 4",
         Lag( :Headings ) == "Author 4", "Author 5",
         Lag( :Headings ) == "Author 5", "Author 6",
         "Title"
         )

      2. As a Picture:
         
8. Create a new column - I called it Content
   1. Change it to character
   2. Open the formula editor
9. Here’s the formula: 
   1. As JSL
      

      If( :Easy Headings == "",:Column 1,Right( :Column 1, Length( :Column 1 ) - Length( :Easy Headings ) - 2 ))

   2. As a Picture:
      
10. Tables > Split
    1. Split By: Headings
    2. Split Columns: Content
11. Select the Author Columns and combine into a new column called Author (multiple response). Then it’s just delete the extra columns and reorder.

 

Discovery 2019 Abstracts_MDA_Transposed.jmp

Discovery 2019 Abstracts_MDA.jmp