Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
AxelFoolery
Level I

Help with transposing data from 1 column across a few columns and repeat

Hello - I have been searching but haven't quite figured out how to do the following: I have a text file that contains all the data (2600+ rows worth) in one column. The data is of type character and sometimes is more than 1 word. The data is ordered in groups of 5 rows. I have imported the text file and created the 5 columns in which I would like to relocate all the data. That is, I would like to transpose the content from the first 5 rows into 5 columns (1 row per column). Then transpose the next 5 rows into those same 5 columns. Repeat until all the data is transposed across those same 5 columns.

Here's a quick example I mocked up using numbers, however my data is actually of character type (and sometimes contains more than 1 word).

I suspect the solution will require a "for" loop and JSL, however I have only dabbled a bit in JSL code and can't seem to get all my variables right as well as how to index the rows and columns.

Mock data transformation.png

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: Help with transposing data from 1 column across a few columns and repeat

This is something I do on a regular basis.  Many users insert tables(not data tables) into their discussions, which when copy and pasted into a new JMP data table, end up with each element from the table insert as a separate row for the one and only copy in the JMP data table.  So here is the technique that I use.

  1. Create a new column.
  2. Go to the Col Info dialog for the new column
  3. Under the Down Arrow for Initialize Data, select Sequence Data
  4. Since you have 5 repeating rows, specify the "To" value to 5repeat1.PNG
  5. Click OK                               repeat2.PNG
  6.  Now go to Tables==>Split and specifyrepeat3.PNG
  7. Click OK and you getrepeat4.PNG
Jim

View solution in original post

2 REPLIES 2
Highlighted
txnelson
Super User

Re: Help with transposing data from 1 column across a few columns and repeat

This is something I do on a regular basis.  Many users insert tables(not data tables) into their discussions, which when copy and pasted into a new JMP data table, end up with each element from the table insert as a separate row for the one and only copy in the JMP data table.  So here is the technique that I use.

  1. Create a new column.
  2. Go to the Col Info dialog for the new column
  3. Under the Down Arrow for Initialize Data, select Sequence Data
  4. Since you have 5 repeating rows, specify the "To" value to 5repeat1.PNG
  5. Click OK                               repeat2.PNG
  6.  Now go to Tables==>Split and specifyrepeat3.PNG
  7. Click OK and you getrepeat4.PNG
Jim

View solution in original post

Highlighted
AxelFoolery
Level I

Re: Help with transposing data from 1 column across a few columns and repeat

@txnelson - Many thanks! Very clever trick, worked beautifully. 

 

Best regards,

Alex

Article Labels

    There are no labels assigned to this post.