topic Re: Merging time series with timestamp and duration in Discussions

Merging time series with timestamp and duration

WHG — Mon, 19 Aug 2019 07:53:42 GMT

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:

timestamp	event
20:23:56:962	A event
20:24:14:964	B event
...	...

time [s]	amplitude
0	-0.000457764
0.0000625	0.0642700195
...	...

timestamp	time [s]	event	amplitude
20:23:56:962	0	A event	-0.000457764
20:23:56:962	0.0000625		0.0642700195
20:23:56:962	0.000125		0.0927124023
...	...		...
20:24:14:964	18.002	B event	-0.013092041
...	...	...	...

I'd be very grateful for your help.

Re: Merging time series with timestamp and duration

FN — Mon, 19 Aug 2019 12:38:23 GMT

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

https://www.youtube.com/watch?v=UrW0X3XfHFA

Re: Merging time series with timestamp and duration

WHG — Tue, 20 Aug 2019 15:07:49 GMT

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.

Re: Merging time series with timestamp and duration

txnelson — Tue, 20 Aug 2019 22:19:39 GMT

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?

Re: Merging time series with timestamp and duration

WHG — Wed, 21 Aug 2019 06:23:23 GMT

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.

Re: Merging time series with timestamp and duration

txnelson — Wed, 21 Aug 2019 10:19:00 GMT

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.

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, ":" ) )
	)
);