I am merging sample-level metadata with time-series raw measurements using Python’s merge_asof and then updating the time-series table in JMP using Update(). Both tables contain columns for time (EFT) and batch ID. The problem: After the Python merge, the resulting table (merged_samples) contains exact matches for some time points and “nearest” matches for others. Python merge handles “nearest” correctly: merged_batch = pd.merge_asof( metadata_batch.sort_values('EFT_val'), timeseries_batch.sort_values('EFT_val'), left_on='EFT_val', right_on='EFT_val', direction='nearest' ) Python correctly pairs 208.0955556 (raw) with 209 (metadata). The resulting merged_batch has all sample rows matched to nearest time-series points. JMP Update() fails for close values : timeSeriesTable << Update( With( merged_batch ), Match Columns( :EFT_val = :EFT_val, :Batch_ID = :Batch_ID ) ); Metadata has fewer rows than the time-series table. Even values that look identical in the UI fail fail due to floating-point precision differences. When I plot the graph in 'merged_batch' individual table it works fine but after the update in `timeSeriesTable `, the graph doesn't plot all the data point and some rows are missed too. Like this: 0 - present 87.9669444444445 - present 111.966666666667 - missing 135.966111111111 - missing 183.965555555556 - present 205.965277777778 - present

AddIn: Join Nearest Rows - Join tables by using continuous columns and nearest values / @jthi

This feature is pending for a while - please support @jthi 's wish and vote: Add fuzzy join / asof merge / join by columns which do not have exact matches

I used Join to demonstrate as using Update with the provided data doesn't do anything, but join does offer merge same name columns (and update main table with data from second table). I think rounding might be one option. Or could you use pandas to build the final table as you are already using it earlier? Also, as a heads-up you might not want to use << Set Each Value in that way, as I'm not sure if JMP has yet changed it to be the intended behaviour.

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Issues with Update() on floating-point EFT values

zetaVagabond1

I am merging sample-level metadata with time-series raw measurements using Python’s merge_asof and then updating the time-series table in JMP using Update(). Both tables contain columns for time (EFT) and batch ID.

The problem:

After the Python merge, the resulting table (merged_samples) contains exact matches for some time points and “nearest” matches for others.

Python merge handles “nearest” correctly:

<CODE class="whitespace-pre! language-python">

merged_batch = pd.merge_asof(
    metadata_batch.sort_values('EFT_val'),
    timeseries_batch.sort_values('EFT_val'),
    left_on='EFT_val',
    right_on='EFT_val',
    direction='nearest'
)

Python correctly pairs 208.0955556 (raw) with 209 (metadata).
The resulting merged_batch has all sample rows matched to nearest time-series points.

JMP Update() fails for close values :

<CODE class="whitespace-pre! language-jsl">


timeSeriesTable << Update(
    With( merged_batch ),
    Match Columns(
        :EFT_val = :EFT_val,
        :Batch_ID = :Batch_ID
    )
);

Metadata has fewer rows than the time-series table.
Even values that look identical in the UI fail fail due to floating-point precision differences.

When I plot the graph in 'merged_batch' individual table it works fine but after the update in `timeSeriesTable `, the graph doesn't plot all the data point and some rows are missed too.

Like this:

0 - present
87.9669444444445 - present
111.966666666667 - missing
135.966111111111 - missing
183.965555555556 - present
205.965277777778 - present

Accepted answers

All comments

jthi

Can you provide example tables? Are you trying to join 208.0955556 with 209 in JMP?

JMP doesn't have join nearest natively (you can script it and there are scripts in community / were add-ins for it). There is also at least one wish list item requesting it.

zetaVagabond1

Yes, since JMP doesn't have that capability I used python which works and integrated very well with the code. And I am attaching the tables for reference.

and No, I am not trying to join 208.0955556 with 209. 209 is recorded value and the float is from machine, that's why I am using the mergeasof in python for nearest values.

meta-data.jmp

timeSeriesTable.jmp

jthi

Is there some issue or this just "heads-up" that JMP doesn't have this capability?

zetaVagabond1

This - When I plot the graph in 'merged_batch' individual table it works fine but after the update in `timeSeriesTable `, the graph doesn't plot all the data point and some rows are missed too.