Extracting corresponding timestamp in two tables of different sizes.

I have two versions of one dataset. A final processed version that includes timestamps with hours and minutes (the seconds got lost early in the processing), and a raw version that retains the original datestamp with seconds. I would like to be able to extract the timestamp with seconds, and match it to the final data.

I currently have the data as two tables and have tried to extract the locations based on where variables that appear in both tables match (using ismember), but it doesn't seem to be finding the right timestamps.

x = ismember(old.Var1, new.Var1) & ismember(old.Var2, new.Var2) & ismember(old.Var3, new.Var3);

y = old.Timestamp(x);

Ay advice would be appreciated!

[Stephen23]

If you are hoping to match based on similar times then you might need RETIME or DATESHIFT or similar.

Followed by a JOIN or similar.

[DD_2023]

Thanks for the advice! Unfortuately the seconds are not consistently spaced, so I wouldn't know what to retime to.

One of my issues is, in the finalised table I have multiple data points per timestamp (on the same minute), so I need to make sure I'm matching the right row (with the correct associated data) to the timestamp with seconds in the other version of the dataset, which I'm worried may get lost in retime. This is why I was trying to match based on other columns, to ensure I wasn't mixing them up.

[Star Strider]

Another option is synchronize.

[DD_2023]

Thanks for the suggestion, I believe I still have the issue that my dataset without seconds has multiple datapoints per minute. So there's no way to ensure the correct timestamps, and corresponding data, get matched.

[Mathieu NOE]

maybe sharing some data would help

Answer 1

[Peter Perkins]

The times are changed in the new table, so all the suggestions to match times are AFAICT not gonna work. I think you're on the right track with ismember, assuming the combination of values in those vars are unique in each row. Dunno if that's true or not. But I think you need the 2nd output.

For each row in the old, you want to know where it ended up in the new. So you want the second output of

[tf,loc] = ismember(old,new)

then something like old.time will map to new.time(loc).

By the way, there is a table ismember method, something like

[tf,loc] = ismember(old(:,["Var1" "Var2"]),new(:,["Var1" "Var2"]))