34821_en-US

liliconcom

Merge data by time stamp

I need to merge two different data sets. The first, A, has data collected every 2 minutes. The second, B, has data collected only when a measurement crossed a threshhold. 

 

A-sample

2017-01-21:00:02:00, 1, 2, 3, 4

2017-01-21:00:04:00, 2, 3, 4, 5

2017-01-21:00:06:00, 3, 4, 5, 6

...

B-sample

2017-01-21:00:02:05, 4500

2017-01-21:00:05:14, 5700

...

It will be sufficient to put B data into the nearest A row. For example, B-data at 2017-01-21:00:05:14 could be placed into A-row=2017-01-21:00:06:00

 

I have about 170,000 rows in A; and about 4,000 rows in B.

 

Thanks

 

 

Re: Merge data by time stampThank you so much txnelson! I will test it out.

-gzhou
Re: Merge data by time stamp

Here is a modification of the script that I believe will give you what you want.  Now, it hasn't been fully tested, since you did not provide sample data tables, but I believe it should work, if the variable you are placing your experiment number into is called "Experiment".

Names Default To Here( 1 );
Clear Symbols();
list_tables = {};


For( i = 1, i <= N Table(), i++,
 Insert Into( list_tables, Data Table( i ) << get name )
);  //sets up names of data tables to pick from
dt1 = Data Table( list_tables[1] );
dt2 = Data Table( list_tables[2] ); //Reasonable Starting point
win_tableselect = New Window( "Select Tables",
 <<MODAL></MODAL>

Re: Merge data by time stamp

How could you modify this JMP script to allow it join two data tables by matching two different variable? 

 

For example: In the data set below, I would like to match by Experiment # and then match by the local min of the timestamp to join values of data table 2 onto data table 1 such that values of data table 2 is in one row of the nearest timestamp in data table 1 with the correct experiment #.

A-sample

Experiment 1, 2017-01-21:00:02:00, 1, 2, 3, 4

Experiment 1, 2017-01-21:00:04:00, 2, 3, 4, 5

Experiment 2, 2017-01-21:00:06:00, 3, 4, 5, 6

...

B-sample

Experiment 1, 2017-01-21:00:02:05, 4500

Experiment 2, 2017-01-21:00:05:14, 5700

 

Any assistance would be helpful!

 

Thanks

Re: Merge data by time stamp

Jim,

Thanks so much. I think we are all set now.

Re: Merge data by time stamp

There were a couple of items that did not work with the latest version(s) of JMP in the script that was referenced.  I reworked them, and removed the requirement to run this as a function call.  So all you have to do is to open the 2 data tables and run the script below.  I ran it against your data and it worked well.  I am not sure the results are exactly what you want, but it will give you a data table you can work from.

Names Default To Here( 1 );
Clear Symbols();
list_tables = {};


For( i = 1, i <= N Table(), i++,
 Insert Into( list_tables, Data Table( i ) << get name )
);&nbsp; //sets up names of data tables to pick from
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;dt1 = Data Table( list_tables[1] );
dt2 = Data Table( list_tables[2] ); //Reasonable Starting point
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;win_tableselect = New Window( "Select Tables",
 <<MODAL></MODAL>

Re: Merge data by time stamp

attaching subsets of the files I am using

Re: Merge data by time stamp

thanks for the quick response.

I put a "datetime-in-seconds" column in each table, so all data points have a timestamp.

I run the script, it asks me to choose a column in each table, which I do by selecting the "datetime-in-seconds" column in each table. 

The end result is just an additional column in dt2 called "Loc Min" which is simply ascending integers 1,2,3,.. 

Is the function failing, or am I missing something on how to use it?

Re: Merge data by time stamp

https://community.jmp.com/t5/Discussions/Join-Question-timestamp-range-kinda-sorta/m-p/20828

 

This will do it or at least get you on the right track.  And it's stupidly fast.