topic Re: Converting time to time intervals and resetting based on second column values to prevent x-axis offset in Discussions

Converting time to time intervals and resetting based on second column values to prevent x-axis offset

polymerist — Fri, 13 Jun 2025 20:02:21 GMT

I have a data table with the columns "Time" (Numeric, continuous, formatted in 12-hr h:m:s) and "Name" (Character, just a list of the experiment to which each time point is associated). When I graph using other columns not mentioned here as the Y-axis, and Time as the x-axis, the issue arises where each plot between x-grouping variables is shifted since the x-axis scale remains the same between x-grouping variables (Figure 1).

Figure 1. Each x-grouping variable is a "Name" column variable with "Name also as the Overlay.

My thought is to write a script to:

1. Create a new column

2. Code new column with a formula that converts the "Time" column to time in seconds, starting at "0" and counting up with a specified interval (2 seconds in this case), resetting to 0 as soon as the "Name" column changes values.

My main questions are:

1. Is this possible?

2. Is there an easier way to do this without making a new column?

Currently on JMP 17.2.0 on Windows 10.

Thanks!

Re: Converting time to time intervals and resetting based on second column values to prevent x-axis offset

mmarchandFSLR — Fri, 13 Jun 2025 20:19:47 GMT

See if this does what you need.

dt << New Column( "ElapsedTime", numeric, continuous, Formula( :LocalTime - Col Min( :Local Time, :Name ) ) );

Re: Converting time to time intervals and resetting based on second column values to prevent x-axis offset

polymerist — Fri, 13 Jun 2025 20:37:55 GMT

Thanks for the reply!

Unfortunately, that returns the column "Elapsed Time" seen in the figure below.

I tried the following code, which yielded the column, "Time (s)" in the figure above as well. Seems like it's doing the set inititial to zero function, but not converting anything else.

Names Default To Here(1);  // Ensure scope is local for column references
dt = Current Data Table(); // Reference to the active data table

// Add a new numeric column "Time (s)" with the specified formula
dt << New Column( "Time (s)",
    Numeric, Continuous, Format( "Best", 12 ),  // numeric format (optional)
    Formula(
        If(
            Row() == 1,
            // Case 1: First row of the table -> start at 0 seconds
            0,
            :Name("Name") != Lag( :Name("Name") ),
            // Case 2: Identifier changed from the previous row -> new run, reset time to 0
            0,
            // Case 3: Otherwise (same run as previous row) -> accumulate time difference
            Lag( :Name("Time (s)") ) + ( :LocalTime - Lag( :LocalTime ) )
        )
    )
);

Re: Converting time to time intervals and resetting based on second column values to prevent x-axis offset

mmarchandFSLR — Fri, 13 Jun 2025 21:14:35 GMT

There may be something unexpected about your :LocalTime column. I tried my formula on a made-up table, and it worked as intended. How is your :LocalTime column formatted?

Re: Converting time to time intervals and resetting based on second column values to prevent x-axis offset

txnelson — Fri, 13 Jun 2025 21:15:09 GMT

I created an example data table

And then I ran the following JSL

Names Default To Here(1);  // Ensure scope is local for column references
dt = Current Data Table(); // Reference to the active data table

// Add a new numeric column "Time (s)" with the specified formula
dt << New Column( "Time (s)",
    Numeric, Continuous, Format( "Best", 12 ),  // numeric format (optional)
    Formula(
        If(
            Row() == 1,
            // Case 1: First row of the table -> start at 0 seconds
            0,
            :Name("Name") != Lag( :Name("Name") ),
            // Case 2: Identifier changed from the previous row -> new run, reset time to 0
            0,
            // Case 3: Otherwise (same run as previous row) -> accumulate time difference
            Lag( :Name("Time (s)") ) + ( :LocalTime - Lag( :LocalTime ) )
        )
    )
)
dt << New Column( "ElapsedTime", numeric, continuous, Formula( :LocalTime - Col Min( :Local Time, :Name ) ) );

An got the following output

Re: Converting time to time intervals and resetting based on second column values to prevent x-axis offset

polymerist — Fri, 13 Jun 2025 21:21:24 GMT

Okay, nevermind, the code I posted actually worked! I had my column name misspelled :confounded_face:

Only remaining question then would be, is there an easier way to do this without making a new column?

Re: Converting time to time intervals and resetting based on second column values to prevent x-axis offset

mmarchandFSLR — Fri, 13 Jun 2025 21:32:18 GMT

It requires a new column. It doesn't have to be a persistent column, meaning it could be a column that exists only while the graph is there.

Graph Builder(
	Transform Column(
		"Time (s)",
		Formula( :LocalTime - Col Min( :LocalTime, :Name ) )
	),
	Show Control Panel( 0 ),
	Variables( X( :"Time (s)"n ), Y( :Y Column ), Group X( :Name ) ),
	Elements( Smoother( X, Y, Legend( 8 ) ) )
);

Re: Converting time to time intervals and resetting based on second column values to prevent x-axis offset

txnelson — Fri, 13 Jun 2025 21:37:49 GMT

You can always use a Transform (virtual) column when you run your Graph Builder. That way you do not have to have an actual column in your data table.

Re: Converting time to time intervals and resetting based on second column values to prevent x-axis offset

polymerist — Fri, 13 Jun 2025 21:56:46 GMT

This is perfect. Thank you and @txnelson both for the great solutions!

Re: Converting time to time intervals and resetting based on second column values to prevent x-axis offset

txnelson — Sat, 14 Jun 2025 01:01:30 GMT

As an incidental side note, the standard in JMP is to place the units of measurement in to the Units Column Property. In your case the difference would be that the actual column name would be Time, however, by default, the Units value is displayed between a set of parentheses, changing the displayed value as Time (s)