Accepted Solutions
Community Manager
@aliegner1 wrote:
Question about the timestamps: how would I convert this to a raw time duration? I'm seeing there's the "Date Difference" formula, but it looks like it needs two columns. How do I just make this a time series starting at 0.000s --> max time?
Before we go too much further I think we need to clarify this question here. @txnelson interpreted that as you wanting to get rid of the date portion of the datetime value. I don't think that's what you want.
You've now got a column of datetime values. That column can be used as a time series, it just won't start at 0.
If, instead, you'd rather a column that counted the time difference between consecutive rows of the data table that's easy to do with simple subtraction and row subscripting.
I have a couple of things to point out here:
- In the first row the value is set to 0.
- I don't need to use the Date Difference() function because I'm interested in the values in secs. So, simple subtraction works.
- Because of floating point arithmetic the difference in these values aren't precisely on the tenths of a second. I've formatted this column to display in tenths of a second, i.e. Fixed Dec with 1 decimal place.
- Finally, but perhaps more complicated is that your original question said that you had multiple wavelengths. Do you want to restart this time sequence at 0 for each wavelength? If so, that's not a problem. It will just take a couple of changes to the formula.
- Now the formula checks for row 1 or for the lamda value to be different from the previous lambda (using the Lag() function) to set the sequence to 0.
- It also uses Lag() to get the value of the sequence in the previous row and adds the difference in the time values between the current and previous rows.
I hope this helps clarify and provide a good direction.
Community Manager
I think all you need is Tables->Stack putting your time columns in the Stack Columns role.
You should end up with two columns, wavelength and time.
As for converting your timestamp into a JMP datetime column, Word() and Informat() are my go to here:
Further reading:
Here is a little script that should give you a good "leg up" in getting your data into an analytical format. It takes your sample data table and creates:
names default to here(1);
dt=current data table();
// Get the column names from the data table
colNames = dt << get column names;
// Remove Lamda from the list
remove from(colNames,1,1);
// Stack the data
dtStack = dt << Stack(
columns(
colNames
),
Source Label Column( "Label" ),
Stacked Data Column( "Data" ),
Output Table( "Stacked" )
);
// Create a new JMP DateTime column
dtStack << New Column( "Timestamp",
Numeric,
"Continuous",
Format( "d/m/y h:m:s", 26, 3 ),
Input Format( "d/m/y h:m:s", 0 ),
Formula( Informat( Word( 1, :Label, "Z" ), "d/m/y h:m:s" ) )
);
// Remove formula to convert column to static values
dtStack:Timestamp << delete formula;
// Create the Time variable
dtstack << New Column( "Time",
Numeric,
"Continuous",
Format( "hr:m:s", 17, 3 ),
Input Format( "hr:m:s", 0 ),
Formula( Time Of Day( :Timestamp ) )
);
// Get rid of no longer needed "Label" column
dtStack << delete columns( "Label" );
Awesome Jim!
a few questions to see what you think:
- how to guard against a run that goes over midnight into the next day? using Time of Day to get an absoluteTime or normalize it to start on zero would be affected by a run spanning 2 days.
- Thoughts on converting the Lambda col to a character column? It's essentially a label, so it should not be numeric, right? Its measuring at several wavelengths t then plot data v time.
- how could I embed the original file into the newly created data table? I changed the initial dt = --> to a file open dialog.
