topic Re: Transform a time column into a time interval column in Discussions

Transform a time column into a time interval column

frankderuyck — Thu, 08 Jun 2023 16:34:19 GMT

In my data table I have a categorical variable with ID settings that are time functions. I would like to use the functional data explorer to study the ID time functions; however the starting time of each ID setting is not constant. To use the FDE time intervals of each ID must be scaled starting from time = 0. How can I transform my time column into a time interval column so each ID setting starts at 0? Thanks for input.

Re: Transform a time column into a time interval column

Jed_Campbell — Wed, 22 Feb 2023 15:43:50 GMT

Without knowing how your categorical variable is formatted, it's difficult to suggest a solution. However, assuming that there is at least some numeric value in the column that can be extracted, you could use the Rank() function to scale things. I've attached a sample table to show how the formulas work.

Re: Transform a time column into a time interval column

frankderuyck — Wed, 22 Feb 2023 16:18:43 GMT

Thanks for input! Where do I find the formula Last?

Re: Transform a time column into a time interval column

Jed_Campbell — Wed, 22 Feb 2023 16:39:31 GMT

If you right click a column header then select New Formula Column, you'll see a list of the formulas that JMP will automatically make for you. For me, this is about 90% of all the formulas I build in JMP. To make the formula columns in the sample table, I used this, then modified the formulas a bit in the formula editor.

To use the formula editor, right click on the header and select Formula. There, you'll see a searchable list of all the possible formulas on the left.

Re: Transform a time column into a time interval column

Mark_Bailey — Wed, 22 Feb 2023 16:59:12 GMT

Have you considered the data processing capabilities built into FDE?

Re: Transform a time column into a time interval column

frankderuyck — Wed, 22 Feb 2023 20:35:33 GMT

Hi Mark, maybe it is better to specify with an example in attached data table what I need: in the example there are 3 ID settings ;next to there is a time column in seconds. In the time interval column, for each ID setting I need a time scale tarting from 0. How do I do this in JMP? Thanks in advance for help, I need to do this for 80 data files that will concatenated with each 3 to 10 ID settings.

Re: Transform a time column into a time interval column

frankderuyck — Wed, 22 Feb 2023 20:36:15 GMT

Hi Jed, maybe it is better to specify with an example in attached data table what I need: in the example there are 3 ID settings ;next to there is a time column in seconds. In the time interval column, for each ID setting I need a time scale tarting from 0. How do I do this in JMP? Thanks in advance for help, I need to do this for 80 data files that will concatenated with each 3 to 10 ID settings.

Re: Transform a time column into a time interval column

Jed_Campbell — Wed, 22 Feb 2023 20:57:00 GMT

Thanks for the data file. Here are steps to accomplish this:

Right click Time column, select New Formula Column...Aggregate...Minimum
Right click new column, select Formula, change formula to include ID as a "By" variable (third argument)
Rename this column to "Start Point"
Select both Time and Start Point columns, right click on column header, select New Column Formula...Combine...Difference

I added 2 files to show this: a workflow if you're on JMP 17, and the data table with script saved if you're on JMP < 17.

Re: Transform a time column into a time interval column

frankderuyck — Wed, 22 Feb 2023 21:19:50 GMT

Thanks a lot Jed, this works fine!

Re: Transform a time column into a time interval column

Mark_Bailey — Wed, 22 Feb 2023 21:24:34 GMT

Thanks for sharing the example. The data processing tools in FDE won't help this case.

Re: Transform a time column into a time interval column

frankderuyck — Thu, 23 Feb 2023 09:44:27 GMT

Thanks for info on the FDE tools