Choose Language Hide Translation Bar
Highlighted
thisismiller
Level II

Multiple columns with their own dedicated timestamp - Join/Combine/Concat timestamps

I have many files with the below format, where each measurement has its own recorded timestamp. I'd like a table that has only one time column, so that I can chart multiple variables against the same timeline. 

 

Here's an example of my data:

 

datetime_measure1value_measure1datetime_measure2value_measure2datetime_measure3value_measure3
1:39:15.000 PM1.71381:39:15.000 PM400.03451:39:15:000 PM50.9007
1:39:23.071 PM0.09761:39:25.730 PM399.87931:39:17.591 PM51.7412
1:39:26.410 PM1.71631:39:35.990 PM399.94871:39:33.590 PM51.1936
1:39:37.150 PM0.12451:39:40.029 PM400.03611:39:50.869 PM

50.9051

 

Ideally I'd like it to look something like this:

 

datetime value_measure1value_mesure2value_measure3
1:39:15.000 PM1.7138400.0345 50.9007
1:39:17.591 PM--51.7412
1:39:23.071 PM0.0976--
1:39:25.730 PM-399.8793-
1:39:26.410 PM1.7163--
1:39:33.590 PM--51.1936
1:39:35.990 PM-399.9487-
1:39:37.150 PM0.1245--
1:39:40.029 PM-400.0361-
1:39:50.869 PM--

50.9051

 

 

I have been attempting to use the stack, join, and concatenate formulas in JMP to do this. But it has produced undesirable results. Usually I end up with "value" columns that are unsorted correctly. I may need to manipulate the data first to add some kind of sorting column, but haven't been successful yet. Need help as I am not a JMP scripting expert. 

 

Thank you.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
MathStatChem
Level VI

Re: Multiple columns with their own dedicated timestamp - Join/Combine/Concat timestamps

You can do this with two table operations:Stack all the columns (Tables > Stack), and check the box for "Multiple Series Stack".  Enter the value 2 for the number or series, and make sure the "contiguous" box is not checked.  Click OK.  This will create a table that looks like this:

 

8-5-2019 7-06-49 PM.jpg

 

Then, you want to split this table (Tables > Spit).  Split by Label 2, split the Data 2 column, and group by Data.

8-5-2019 7-10-32 PM.jpg

this results in this table:

8-5-2019 7-11-23 PM.jpg

 

 Note, you will have another problem to deal with, I think.  I don't believe JMP supports the datetime format  (hh:mm:ss.ddd AM/PM) you are using, if you want to treat those values like numbers.  If you treat them like character values you will be alright, though, and the steps above will still work.  

 

View solution in original post

2 REPLIES 2
Highlighted
MathStatChem
Level VI

Re: Multiple columns with their own dedicated timestamp - Join/Combine/Concat timestamps

You can do this with two table operations:Stack all the columns (Tables > Stack), and check the box for "Multiple Series Stack".  Enter the value 2 for the number or series, and make sure the "contiguous" box is not checked.  Click OK.  This will create a table that looks like this:

 

8-5-2019 7-06-49 PM.jpg

 

Then, you want to split this table (Tables > Spit).  Split by Label 2, split the Data 2 column, and group by Data.

8-5-2019 7-10-32 PM.jpg

this results in this table:

8-5-2019 7-11-23 PM.jpg

 

 Note, you will have another problem to deal with, I think.  I don't believe JMP supports the datetime format  (hh:mm:ss.ddd AM/PM) you are using, if you want to treat those values like numbers.  If you treat them like character values you will be alright, though, and the steps above will still work.  

 

View solution in original post

Highlighted
thisismiller
Level II

Re: Multiple columns with their own dedicated timestamp - Join/Combine/Concat timestamps

This works well, thank you! Is there a way to script this solution so that all I have to do is point to the csv file? 

 

I understand how to create a JMP script which imports a csv file and goes through all of the appropriate opening options. But I have never used JMP script to use the join and split functions. 

Article Labels

    There are no labels assigned to this post.