topic Re: Help doing a table transform and converting a timestamp? in Discussions

Help doing a table transform and converting a timestamp?

aliegner1 — Sun, 11 Jun 2023 11:05:36 GMT

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
...

Re: Help doing a table transform and converting a timestamp?

Jeff_Perkinson — Wed, 09 Sep 2020 01:19:18 GMT

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:

Re: Help doing a table transform and converting a timestamp?

txnelson — Wed, 09 Sep 2020 01:26:25 GMT

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?

txnelson — Wed, 09 Sep 2020 01:28:18 GMT

Jeff,

I guess my age is showing.....you beat me to the answer again.

Re: Help doing a table transform and converting a timestamp?

aliegner1 — Wed, 09 Sep 2020 03:35:57 GMT

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?

txnelson — Wed, 09 Sep 2020 04:05:16 GMT

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?

aliegner1 — Wed, 09 Sep 2020 04:25:15 GMT

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?

txnelson — Wed, 09 Sep 2020 10:50:35 GMT

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?

aliegner1 — Wed, 09 Sep 2020 15:20:55 GMT

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.

Re: Help doing a table transform and converting a timestamp?

txnelson — Wed, 09 Sep 2020 15:53:27 GMT

Given the questions you are asking, my first response it that you need to do some reading. You need to go through both the Discovering JMP and Using JMP documents. Both are available in the JMP Documentation Library under the Help pull down menu.

Concerning the day overflow issue....what you will need to do is to check to see if the Date Difference() is greater than 0 days, and if not, then calculate the time as required.

Lamda does not have to be changed to a character. But it can easily be done that by changing the Data Type. Or you can simply change the Modeling Type to Ordinal or Nominal.

:Lamda << Data Type(character);
// or
:Lamda<<Modeling Type(nominal);

Re: Help doing a table transform and converting a timestamp?

Jeff_Perkinson — Wed, 09 Sep 2020 16:08:43 GMT

@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.

Re: Help doing a table transform and converting a timestamp?

aliegner1 — Wed, 09 Sep 2020 19:58:57 GMT

Thank you Jeff. Your input on that final equation is great and removes the worry about a run that crosses midnight.