Merging tables of dissimilar rows in MATLAB.

My program runs in parallel using parfeval to generate two tables of data. one from a sensor and the other from a robot (UR5). I want to merge these two tables together. The common column is the Time, although there are little differences in their times as they run at different speeds and that makes the rows unequal too. But I want a table containing all the other variables from rows which times are equal or very close. So at the end, there will be only one column for Time. Find photos of sample results attached. Can I get a help?

Answer 1

[jonas]

You can do the following steps:

• Create proper timetables for all variables (use datetime format)
• resample using retime to get the same time-slots (optional)
• synchronize time-series

If I remember correctly, synchronize will pad the shorter arrays with NaN's.

Please provide actual data (not images) if you need help with the code.

[Sean de Wolski]

I think you meant synchronize the timetables. It's a different doc page than the one linked. Timeseries are the old way to do things with timetables being new and shiny with current development efforts.

[DEYIRE UMAR]

The times are from datetime but converted to milliseconds. I attached now the sample of the table of data which I output to csv

[jonas]

@Sean de Wolski: That's correct! Updated the link

@Deyire: It would be easier to start from datetime format. I don't know what the current format is, perhaps duration format? What does the first value (50033818) represent?

[DEYIRE UMAR]

it is datetime in milliseconds, from this code: timestring = datestr(timestamp,'HH:MM:SS.FFF'); [~,~,~,hours,minutes,seconds] = datevec(timestring); Time = 1000*(3600*hours + 60*minutes + seconds);

[jonas]

You are not using the correct terminology, which is confusing. datetime refers to a specific MATLAB format. What you are using is not datetime but the outdated method where dates are represented by doubles. You cannot represent a duration with datetime. For this you need to use duration, which is also a specific format in MATLAB. Try this code instead:

% Read data
sensor = readtable('sensordata.csv');
robot = readtable('robotData.csv');

% Build two timetables 
TT1 = timetable(milliseconds(sensor{:,1}),sensor{:,2:end});
TT2 = timetable(milliseconds(robot{:,1}),robot{:,2:end});
TT1 = splitvars(TT1); 
TT2 = splitvars(TT2);

% specify the time-step you want in the final table,
% it is here set to 10 miliseconds
dt = milliseconds(10);

% Compile final table
TT = synchronize(TT1,TT2,'regular','linear','TimeStep',dt)

Note the important step when the time is converted from a number (double) to a time duration. It is done using the function milliseconds(). Here's an example:

milliseconds(11)

ans =

duration

   0.011 sec

Finally, I understand you started with actual clock format HH:MM:SS.FFF. It would be much more convenient to start from this format, instead of first converting to milliseconds.

[DEYIRE UMAR]

thank you. I did and it works. Am just doing little adjustments now

[Sean de Wolski]

Great example!

The timetable is created with one variable being a matrix rather than a bunch of columns, hence the splitvars. To avoid that, you could do this:

robot.Time2 = milliseconds(robot.Time2);
TT2 = table2timetable(robot);

Which will correctly bring along the robot VariableNames, etc.

[DEYIRE UMAR]

I really need this. the variable names are needed other than ' var'

[jonas]

@Sean de Wolski: Thanks, that works!

@DEYIRE: That's easy. Just write your desired names in a cell array like this:

TT.Properties.VariableNames={'A','B','C','D','E','F','G','H','I','J'}

The cell array must have the same number of cells as the number of table columns (excluding the column with time), and you can only use names that also function as variables, i.e. A-Z and underscore _.

Please copy and paste the code from the previous comment one more time to your script. I made a small but important change.

Finally, please accept if the answer was helpful :)

Answer 2

[DEYIRE UMAR]

I got it now. I merged and removed NaN but the rows I have are too little because only rows with same Time were picked. I used this code:

Log_table = outerjoin(tcp_coordinates,forces,'MergeKeys', true); Log_table = Log_table(~any(ismissing(Log_table),2),:); writetable(Log_table,'AllData.csv','Delimiter',',')

Now am asked to make it a table with not only same row values of Time but also close values so that it becomes much. I don't know if this is possible? but it doesn't look easy.