Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
WHG
WHG
Level I

Merging time series with timestamp and duration

I would like to merge the following tables 1 (time series with certain events, ~2000 datapoints) and 2 (time series with duration and amplitude data, ~1,000,000 datapoints) into a table like 3:

 

1:

timestampevent
20:23:56:962A event
20:24:14:964

B event

...

...

 

2:

time [s]

amplitude
0-0.000457764
0.00006250.0642700195
......

 

3:

timestamptime [s]eventamplitude
20:23:56:9620A event-0.000457764
20:23:56:9620.0000625 0.0642700195
20:23:56:9620.000125 0.0927124023
...... ...
20:24:14:96418.002B event-0.013092041
............

 

I'd be very grateful for your help.

5 REPLIES 5
Highlighted
FN
FN
Level V

Re: Merging time series with timestamp and duration

Here you can find how to join two data tables in JMP

https://www.youtube.com/watch?v=UrW0X3XfHFA
Highlighted
WHG
WHG
Level I

Re: Merging time series with timestamp and duration

Thanks. Following this path, I would require a match of columns, but the time formats in the two tables differ: One is an unusual time stamp including milliseconds (hh:mm:ss:sss), the other a duration value in seconds (the duration starts at a certain time stamp, i.e. event).

My goal is to integrate a column with time stamps for every duration value. I would like to avoid rounding the duration values to milliseconds, since this would reduce the data integrity, but I'm not sure how to handle odd time stamp formats that include microseconds. If you have any suggestions, I would be very grateful.

Highlighted
txnelson
Super User

Re: Merging time series with timestamp and duration

Can you please explain how to determine what rows in Data Table 2 are associated with Event A vs. what rows in Data Table 2 are associated with Even2 B?
Jim
Highlighted
WHG
WHG
Level I

Re: Merging time series with timestamp and duration

My apologies, I didn't make it clear enough:  I would like to synchronize the two tables by associating event A in table 1 with the time duration 0s in table 2.

 

So, it would be required to simply add the time stamp of event A to the time duration value of each row in table 2, and then associate every following event with the respective "composited" time stamp.

Highlighted
txnelson
Super User

Re: Merging time series with timestamp and duration

I don't know if this will be helpful, but here is a script that will take your timestamp values(I am assuming the timestamp column as you have it defined is a character column), and convert them into a numeric column.  The new column can then be compared with the time(s) column for putting together the data.

timestamp.PNG

names default to here(1);

// Create the sample data table
dtEvent = New Table( "Events",
	Add Rows( 2 ),
	New Column( "timestamp",
		Character,
		"Nominal",
		Set Values( {"20:23:56:962", "20:24:14:964"} )
	),
	New Column( "event",
		Character,
		"Nominal",
		Set Values( {"A event", "B event"} )
	)
);

// put in a time delay so one can see the initial data table
wait(5);

// Create a numeric version of the timestamp data
dtEvent << New Column( "Numeric timestamp",
	Format(
		"Custom",
		Formula(
			Char( Hour( value ) ) || ":" || Char( Minute( value ) ) || ":" ||
			Char( Floor( Second( value ) ) ) || ":" || Word(
				2,
				Char( Round( Second( value ), 3 ) ),
				"."
			)
		),
		20
	),
	formula(
		Num( Word( 1, :timestamp, ":" ) ) * 3600 + Num( Word( 2, :timestamp, ":" ) ) * 60
		+Num( Word( 3, :timestamp, ":" ) ) + Num( "." || Word( 4, :timestamp, ":" ) )
	)
);
Jim
Article Labels

    There are no labels assigned to this post.