259925_en-US

liliconcom

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.

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 5
5. Click OK                              
6.  Now go to Tables==>Split and specify
7. Click OK and you get

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