Combine Tables with different record rate

Hey Everybody,

I need to join two tables by the common Parameter "Time_secs_" which represents the record date of my sensor data. Unfortunately, in one table the record is 0.5 seconds while in the second it is 0.1 seconds. Therefore the second table has 481002 rows while the first only has 80167 rows.

Now I want to join them by their record rate and simply add blank cells for every signal from table 1 to the time_secs_0 0.1, 0.2, 0.25, 0.3, 0.4, 0.6, 0.7, 0.75, 0.8, 0.9 (some signals are also taken every 0.25 seconds, thats why 0.25 and 0.75 are included).

How can I do this? Is there any specification I can make to the "join"-function or will it not work with this at all?

Thanks a lot in advance!

[dpb]

Not precisely clear to me what you really intend as the result from description but you might look at resample-and-aggregate-data-in-timetable

[Richard Vergin]

Basically I want to join two tables with different sizes. The first table contains more rows since the sensor data is recorded every 0.1 seconds while in the other it is only recorded every 0.5 seconds. So for every tenth of a second in those records which have no records (table 2, 0.2, 0.3...) i simply want to ad blank cells.

As a similar metric I would have the time available in hours, seconds and minutes but it is not recognized as a similar metric since it is not written in the same rows in the two tables due to the additional rows for table 1 with the higher record rate... I know it's a very specific problem but I was hoping that maybe Matlab can search for specific values in the time variables (hours, minutes, seconds) and then join the tables...

[Walter Roberson]

If you can use timetable objects then call synchronize()

[Richard Vergin]

Okay so I have another problem: the time is only specified in different colums. Column 1 is hours, column 2 is minutes, column 3 seconds...all in format cell.

How can I create an additional column within the table which contains all the information of those three others and makes a time out of this?

Example: - Column 1 = 1 - Column 2 = 26 - Column 3 = 37 -> Column 4 should be in time format: 01:26:37

[Richard Vergin]

For interested readers: after adding a Time-Column with similar values (thanks to Guillaume's solution) I was able to combine the tables. Outerjoin works for adapting the fine record rate, joing for adapting the small one.

[Peter Perkins]

Richard, you can do that (use tables and joins), but if you are using R2016b or later, you will be much happier using timetables and synchronize:

>> tt1 = timetable((1:10)','RowTimes',seconds(0:1:9))
tt1 =
  10×1 timetable
    Time     Var1
    _____    ____
    0 sec      1 
    1 sec      2 
    2 sec      3 
    3 sec      4 
    4 sec      5 
    5 sec      6 
    6 sec      7 
    7 sec      8 
    8 sec      9 
    9 sec     10 
>> tt2 = timetable((11:15)','RowTimes',seconds(0:2:8))
tt2 =
  5×1 timetable
    Time     Var1
    _____    ____
    0 sec     11 
    2 sec     12 
    4 sec     13 
    6 sec     14 
    8 sec     15 
>> synchronize(tt1,tt2)
ans =
  10×2 timetable
    Time     Var1_tt1    Var1_tt2
    _____    ________    ________
    0 sec        1          11   
    1 sec        2         NaN   
    2 sec        3          12   
    3 sec        4         NaN   
    4 sec        5          13   
    5 sec        6         NaN   
    6 sec        7          14   
    7 sec        8         NaN   
    8 sec        9          15   
    9 sec       10         NaN

But beware: .1 is not a nice floating point number. You may find that synchronize treats some times that look the same as different, because they are different. At that level of resolution, you are usually better off using the milliseconds function to construct time vectors than using the seconds function.

Answer 1

[Guillaume]

How can I create an additional column within the table which contains all the information of those three others and makes a time out of this?

yourtable.newcolumnname = datetime([zeros(height(yourtable), 3), yourtable.hourcolumn, yourtable.minutecolumn, yourtable.secondcolumn], 'Format', 'HH:mm:ss')

[Walter Roberson]

yourtable.newcolumnname = hours(yourtable.hourcolumn) + minutes(yourtable.minutecolumn) + seconds(yourtable.secondcolumn);

This would create a duration object with the appropriate data.

[Richard Vergin]

Thanks a lot! Both works for me :))

[Richard Vergin]

Hey, I'm on a similar problem now. I'm trying to create a datetime-column from 6 columns, including year, month and day now. I'm trying with the same logic that Guillaume proposed and wrote the following code:

Minus22_FlightParameter_Hydraulics_Uniform_Table.GMTDATETIME = datetime([zeros(height(Minus22_FlightParameter_Hydraulics_Uniform_Table),6),... Minus22_FlightParameter_Hydraulics_Uniform_Table.GMTYEAR, Minus22_FlightParameter_Hydraulics_Uniform_Table.GMTMONTH,... Minus22_FlightParameter_Hydraulics_Uniform_Table.GMTDAY, Minus22_FlightParameter_Hydraulics_Uniform_Table.GMTHOURS,... Minus22_FlightParameter_Hydraulics_Uniform_Table.GMTMINUTES, Minus22_FlightParameter_Hydraulics_Uniform_Table.GMTSECONDS], 'Format', 'yyyy:MM:dd:HH:mm:ss');

However I receive the following error message: Error using datetime (line 569) Numeric input data must be a matrix with three or six columns, or else three, six, or seven separate numeric arrays. You can also create datetimes from a single numeric array using the 'ConvertFrom' parameter.

I don't know how to fix it, since I am using 6 seperate numeric arrays. Do you know what I am doing wrong?

Best, Richard

[Walter Roberson]

What is the

zeros(height(Minus22_FlightParameter_Hydraulics_Uniform_Table),6),

part there for? That is going to generate 6 columns of zeros, and then you add on 6 more columns of numeric values, for a total of 12 columns.

[Richard Vergin]

Ah I don’t Know I just reused that code line. But without it works perfectly fine, thanks a lot! :)