The following question was asked on P&G's internal JMP User Community page. Passing along to this forum:
I have two tables that I want to join based on timestamps. However, due to the data source they do not have the same times. The only option in JMP that I can find is an exact match. Is there an option in JMP to use a nearest join similar to how pandas join or Excel Vlookup works?
The P&G JMP User Community attempted to give some answers. Is there a better solution than what we tried to recommend?
Answer #1:
Drop both tables in Excel as seperate sheets, add a vlookup column to sheet1 referencing sheet2, then port back to JMP for the join? I've not seen a nearest match feature in JMP, but would be interested if it exists...
Answer #2:
Definitions of "nearest join" are inconsistent. My memory of VLOOKUP (unless it's changed) is to take time X from data table X and match it with the nearest time Y from data table Y such that X<=Y (or is it Y<=X), which can sometimes be a very long gap, especially if the data are being collected at irregular intervals in either data table X or data table Y. The possibility of irregular intervals can really complicate joins like this.
Unfortunately there are no fast approaches in JMP, but there are a couple things you can try.
One approach I have tried before is to fit a spline to critical measurements of interest (with X variable = time), then save the prediction formula and transfer it to the other table. If done correctly, this essentially interpolates the critical measurements and matches them up.
Another option is to do a full join (time stamps won't match), then create a Formula column to check whether Time in data table X is missing; if it is, use the Time from data table Y, otherwise use the Time from data Table X. Then sort on this new Time column. Then create formulas on each critical measurement of interest. In these formulas, check whether the critical measurement is missing or not. If it is not missing, bring the critical measurement over, and otherwise, use the Lag function (Row function group in the formula editor) against whatever you're naming this new formula column to bring down the observation from one row up.
Answer #3:
In many cases you can create a nearest match when binning both time stamps the same way (e.g. bin the data by seconds, 10 seconds, 1 minute...) before joining. This works quite good as long as time stamps are not "too different" and you can accept a some data aggregation.
Binning is a column function in JMP
Follow-up from original questioner:
Thanks for the suggestions. I'll most likely do the joining outside of JMP and then do analysis after combining.
Not sure how many others come across this but this might be a suggestion to pass to JMP to have this capability built into the join dialog instead of additional steps outside of it.
@Peter_Hersh understood and I agree it is probably unwise to recommend JMP as the ideal data cleaning and tidying software at least for cases like this one. JMP's strong suit has always been in exploration and analysis vs. unusual data arrangement tasks. Data join problems based on time are currently best handled outside of JMP in the coding languages where you are making intentional decisions of what to do when you have many-to-one mappings, or time indications that are consistently off, etc. Thanks again.
@mjoner we are not planning to add this to JMP for now. The concern is that changing from a true match to a fuzzy match is not something we are comfortable with in terms of a true join. Using binning or even sorting the table by a timestamp and using compare data tables should help solve some of the challenges.
@mjoner , There is a workaround with the fast formulas inside the right-click option on the formula headers. This will allow for the date and time to be rounded to the nearest minute, second five seconds. I know this is not as convenient as a fuzzy match, but it is another option. I will make sure Development knows
