Merge data by time stamp

michaelclarage

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

 

 

txnelson

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,
	lup_box = Lineup Box( N Col( 2 ),
		Text Box( "Main Table (i.e. SubID List)", <<set font size( 10 ) ),
		Text Box( "Update-With Table (i.e. Tool Data)", <<set font size( 10 ) ),
		c1 = Combo Box(
			list_tables,
			s1 = c1 << getselected;
			dt1 = Data Table( s1 );
			vref1[1] << delete;
			vref1 << append( //Deletes and redraws
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;				col1 = Col List Box( Data Table( dt1 ), all, maxSelected( 1 ), rt1 = col1 << get selected )
			);
		), &nbsp; //these parts change the following collistboxes and their fantastic syntax based upon the data table combo box selection
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;		c2 = Combo Box(
			list_tables,
			<<set( 2 ),
			s2 = c2 << getselected;
			dt2 = Data Table( s2 );
			vref2[1] << delete;
			vref2 << append(
				col2 = Col List Box( Data Table( dt2 ), all, maxSelected( 1 ), rt2 = col2 << get selected )
			);
		), &nbsp; //see above
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;		vref1 = V List Box(
			col1 = Col List Box(
				Data Table( dt1 ), //still stoopdi
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;				all,
				maxSelected( 1 ),
				rt1 = col1 << get selected
			)
		),
		vref2 = V List Box(
			col2 = Col List Box( Data Table( dt2 ), all, maxSelected( 1 ), rt2 = col2 << get selected )
		), &nbsp;&nbsp;&nbsp; //the point of the vrefs is to have something to delete without messing with the lineupbox's proclivity for left-right ordering
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;	),
	Text Box( "" ),
	H List Box(
		Button Box( "Ok",
			s1 = c1 << getselected;
			s2 = c2 << getselected;
			dt1 = Data Table( s1 );
			dt2 = Data Table( s2 );
			rt1 = Char( (col1 << get selected)[1] );
			rt2 = Char( (col2 << get selected)[1] );
		), &nbsp; //not everyone has the << return result option yet if they're not in 11
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;		Button Box( "Cancel" ), 

	), 

);
If( win_tableselect["Button"] == -1,
	Throw( "Canceled by User" )
);
	
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;dt2 << New Column( "Loc Min", numeric, continuous, formula( Row() ) ); //indexing, basically, to match/update upon
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;list_big = Column( dt2, rt2 ) << get values; //creates a vector of values for the following
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;dt1 << New Column( "Loc Min",
	numeric,
	continuous,
	formula( Loc Min( Abs( Column( rt1 )[Row()] - list_big ) ) )
);&nbsp;&nbsp; //simple, but fast.&nbsp; Very fast.
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;dt_join = dt1 << Join(
	With( dt2 ),
	By Matching Columns( :Loc Min = :Loc Min ),
	Drop multiples( 0, 1 ),
	Name( "Include non-matches" )(0, 0),
	Preserve main table order( 1 )
);
&nbsp;&nbsp;&nbsp;&nbsp; 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;dt1 << delete columns( "Loc Min" );
dt2 << delete columns( "Loc Min" );&nbsp; //Removes pesky join column
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;list_columns = dt_join << get column names( string );
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; 
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;For( i = 1, i <= N Items( list_columns ), i++,
	If( Contains( list_columns, "Loc Min" ),
		Column( dt_join, list_columns ) << set selected( 1 )
	)
);&nbsp; //the joined table will having lingering Loc Min-like columns.&nbsp; This just hunts them down and removes them.
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;dt_join << delete columns;
dt_join << set name( Char( dt1 << get name ) || " & " || Char( dt2 << get name ) );&nbsp; //Good enough
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Return( dt_join );