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

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

AxelFoolery — Wed, 22 Apr 2020 23:00:04 GMT

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

txnelson — Thu, 23 Apr 2020 00:40:00 GMT

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.

Create a new column.
Go to the Col Info dialog for the new column
Under the Down Arrow for Initialize Data, select Sequence Data
Since you have 5 repeating rows, specify the "To" value to 5
Click OK
Now go to Tables==>Split and specify
Click OK and you get

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

AxelFoolery — Thu, 23 Apr 2020 21:34:16 GMT

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

Best regards,

Alex