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
Sandeep123
Level III

merging columns

I have a table with different viruses in different columns ( with rows description of detected/non detected)

 

I need them to be in one column with virus names in rows.

 

I tried to stack them. However, some patients have more than one virus. I would ideally want them to be in a columns like Virus 1 ( in majority of patients there would be only one virus), Virus 2 ( if there are two virus) , Virus 3 ( if there are three virus)

 

Thank you

Capture-5.JPG

8 REPLIES 8
Highlighted
txnelson
Super User

Re: merging columns

  1. Stack all of the virus columns.  Make sure in the new table, the MR No remains on every row
  2. I assume you then delete all rows that are marked "NON DETECTED"
  3. Sort the data by Subject(MR No), so all of a subjects rows with Viruses detected are together
  4. Create a new column which is a sequence number for each subject using this formula.  Call the new column "Virus#"
    If( Row() == 1 | :Subject != Lag( :Subject ),
    	:Virus# = 1,
    	:Virus# = Lag( :Virus# + 1 )
    )
  5. Use Tables==>Split
    1. Split By Virus#
    2. Group by   MR No
    3.  Split Column  Label
Jim
Highlighted
Sandeep123
Level III

Re: merging columns

I was able top get to step4 pretty easily but am stuck at the formula.  I am assuming simply copying and pasting formula would not work!

 

Capture-6.JPG

 

Highlighted
txnelson
Super User

Re: merging columns

The new column you create needs to be called "Virus#", then just paste the formula into the formula Editor.

Jim
Highlighted
Sandeep123
Level III

Re: merging columns

  

Hmm..did not work

  

image.png

 

Capture3.JPG

 

Highlighted
Sandeep123
Level III

Re: merging columns

another screen shot of the formula editor

Highlighted
txnelson
Super User

Re: merging columns

You referred to Subjects, so I assumed the name of the column that has the subjects name of ID was called "Subject".  Apparently that is not the case.  It appears that the column MRN is the column, but you refer to it as Mr No in the jpg you posted

 

I have modified my initial response to give more specifics that should provide you with your answers.

Jim
Highlighted
Sandeep123
Level III

Re: merging columns

awesome. Do not totally understand but it worked !

 

Now I have two worksheets which just needs to be combined

Highlighted
txnelson
Super User

Re: merging columns

  1. I am glad it worked
  2. You need to study the steps that were used to get your task done, so the next time you will be able to independently complete the task....or at least get further along.....the Discussion Community is here to help you learn about JMP, not here to write code
  3. You do not have 2 worksheets.  Excel has worksheets, JMP has data tables.  And they are not two different names for the same thing.  Because Excel is "Cell" based, and the worksheet is not just the data storage device, but also the only display vehicle, one stuffs a variety of different kinds of data and information into a worksheet.  On the other hand, JMP's data table is a column based analytical data structure, and uses independent display objects for presenting the information.  The bottom line is, be careful of combining different data structures into one JMP data table.
  4. That aside, You can use    Tables==>Join       or Tables==>Update      to put your data together
Jim
Article Labels