Help doing a table transform and converting a timestamp?so I've got a data table from a sensor that I am not quite sure how to get formatted correctly. It's a light sensor that outputs voltage readings.
The first column in the table is the wavelength measured and then there's 100+ columns for each time instance it measures data.
How would I go about transforming or pivoting this correctly? Would just a Table Transpose be the correct activity?
Next, advice on how to convert raw timestamp into a usable timeseries? It comes out looking like this.
DD-MM-YYYYTHH:MM:SS.###Z
what can I do to convert this into a usable timeseries value to plot as an x-axis? It's down to the thousandth of a second, 0.1s increments.
Bonus, how to do this to find the min and max and normalize the time to be 0-->max?
...
14-07-2020T22:47:31.400Z
14-07-2020T22:47:31.500Z
14-07-2020T22:47:31.600Z
14-07-2020T22:47:31.700Z
14-07-2020T22:47:31.800Z
14-07-2020T22:47:31.900Z
...
Data TableGraphicsScriptingRe: Help doing a table transform and converting a timestamp?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:
If you learn only one Formula Editor function, Word() is the one
Using dates, times, datetimes and durations in JMP
Re: Help doing a table transform and converting a timestamp?Concerning your data, I would suggest that you look into Stacking the columns. That form will give you more JMP tools for analysis.
In regards to your Timestamp issue....here is an example script that takes an input column called "TS" and converts it into a JMP Date/Time column.
Names default to here(1);
dt = New Table( "Example",
Add Rows( 6 ),
New Column( "TS",
Character,
"Nominal",
Set Values(
{"14-07-2020T22:47:31.400Z", "14-07-2020T22:47:31.500Z",
"14-07-2020T22:47:31.600Z", "14-07-2020T22:47:31.700Z",
"14-07-2020T22:47:31.800Z", "14-07-2020T22:47:31.900Z"}
)
)
);
dt << New Column( "Column 2",
Numeric,
"Continuous",
Format( "d/m/y h:m:s", 26, 3 ),
Input Format( "d/m/y h:m:s", 0 ),
Formula( Informat( Word( 1, :TS, "Z" ), "d/m/y h:m:s" ) )
);Re: Help doing a table transform and converting a timestamp?Jeff,
I guess my age is showing.....you beat me to the answer again.
Re: Help doing a table transform and converting a timestamp?Thank you Jim and Jeff, those work great! I am reading up on word() right now.
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?
Question about scripting the stack command: looking at the script, it has a unique row for every column....how to make this dynamic for the next time I pull this data file?
Data Table( "Sheet1" ) << Stack(
columns(
:Name( "14-07-2020T22:47:31.400Z" ),
:Name( "14-07-2020T22:47:31.500Z" ),
:Name( "14-07-2020T22:47:31.600Z" ),
....for every column...
),
Source Label Column( "TimeStamp" ),
Stacked Data Column( "data" ),
Output Table( "sensor table" )
)
Re: Help doing a table transform and converting a timestamp?To convert a Time Stamp(JMP Date/Time) column to just a time value, you use the Time of Day() function. It effectively returns the number of seconds from the previous midnight to the time of the measurement.
Concerning the stacking when he column names change from day to day......it is a simple matter to get a list of the columns in the data table, and then apply that list to the Stack Platform statements. The message to pass to the data table to retrieve the column names is dt << get column names().
Would it be possible to attach a sample data table or spreadsheet of your data. It is pretty tough seeing the specifics of the input data, which may result in passing on bogus directions.Re: Help doing a table transform and converting a timestamp?See attached subset (pre stacked).
I get the time of day, but then need to guard against conditions when a run occurs over midnight and the day changes? I'm zeroing it out by doing [time of day() - col minimum(time of day())
Re: Help doing a table transform and converting a timestamp?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" );
Re: Help doing a table transform and converting a timestamp?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.
