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
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
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
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
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