match two excel files by date and time

I have two files K_ship.xlsx (with data collected nearly every min) and K_ETR.xlsx (data collected maybe every 40 min). Both are attached.

I want to match the two files by data and time (in 2 columns, not combined) so that I can get 3 parameters from the larger file K_ship.xlsx that correspond to K_ETR.xlsx

I have written a script (also attached) used readtable and tried to subtract the two dates and the two times but I am a complete novice and I cant get the date format right.

I had posted a very messy post yesterday which I deleted and now I am only attaching my script and two data files and hopefully somone will help me!

Any other suggestions as to how to accomplish this are very welcome as i am here to learn!

Answer 1

[Tanmay Das]

Hi,

I have the understanding that you know how to import tables from excel file and you are facing problem in merging two tables based on date and time variables. The following code may be useful for your understanding:

A = outerjoin(A,B,'Type','Right','Keys',{'date','time'},'MergeKeys',true); 

where A is the table imported from K_ship.xlsx and B is the table imported from K_ETR.xlsx.

Basically, outerjoin function with 'Type' as 'Right’, right joins two tables keeping all the values in the right table and removing those values in the left table whose key values do not match with the key values of right table. You can also specify the variables that you want to keep in left table by using 'LeftVariables’ Name-Value pair argument.

You may refer to Outerjoin documentation for further information.

Hope that helps!

[Helga Gomes]

Thank you very much, Tanmay, for this extremely valuable tip! I could use 'outerjoin' in so many of my data proceesing tasks! The only question I have is can i give a window for the time stamp to match - i.e. maybe match within 10 minutes? Thanks a lot!