Select column in timetable based on value in table

Hi everyone,

I have a timetable SL containing a distance in metres (SL(:, 1)) and data from 9 sensors.

I also have the distance where these sensors are located in a different table SensorLoc.

My goal is to select the data from one of the sensors based on the closest value in SL(:,1).

For example for the first row BL_TS = 205 so that means that 'Sensor_13' is the closest.

I'd like to create a new matrix containing the data from the sensor closest to the location for each datetime.

Does anyone has some advice on how to start with this?

Thanks a lot!

Answer 1

[Peter Perkins]

Two steps:

1) Find the nearest sensor. This easy using interpolation:

>> load("SL.mat")
>> load("SensorLoc.mat")
>> SensorLoc.Index = (1:height(SensorLoc))'
SensorLoc =
  9×3 table
       Sensor        Loc    Index
    _____________    ___    _____
    {'Sensor_02'}     10      1  
    {'Sensor_04'}     20      2  
    {'Sensor_05'}     30      3  
    {'Sensor_07'}     50      4  
    {'Sensor_08'}     60      5  
    {'Sensor_09'}     80      6  
    {'Sensor_11'}    100      7  
    {'Sensor_12'}    150      8  
    {'Sensor_13'}    200      9  
>> SL2 = SL(:,"BL_TS");
>> SL2.SensorIndex = interp1(SensorLoc.Loc,SensorLoc.Index,SL2.BL_TS,"nearest","extrap")
SL2 =
  527×2 timetable
          Times_TS          BL_TS    SensorIndex
    ____________________    _____    ___________
    08-Oct-2021 15:15:00     205          9     
    08-Oct-2021 15:30:00     229          9     
    08-Oct-2021 16:00:00     214          9     
    08-Oct-2021 16:15:00     220          9     
    08-Oct-2021 17:15:00     183          9     
    08-Oct-2021 17:30:00       0          1     
             :                :           :        
    18-Oct-2021 17:30:00     109          7     
    18-Oct-2021 17:45:00      10          1     
    18-Oct-2021 18:00:00     217          9     
    18-Oct-2021 18:15:00     175          9     
    18-Oct-2021 18:30:00     199          9     
    18-Oct-2021 18:45:00     198          9     
	Display all 527 rows.

2) For each row of SL, pick out the data from the right sensor. Several ways to do this, simplest involves turning the (SL row index,sensor index) pairs into linear indices:

>> SensorData = SL{:,2:end};
>> SL2.SensorData = SensorData(sub2ind(size(SensorData),(1:height(SensorData))',SL2.SensorIndex))
SL2 =
  527×3 timetable
          Times_TS          BL_TS    SensorIndex    SensorData
    ____________________    _____    ___________    __________
    08-Oct-2021 15:15:00     205          9            2.1119 
    08-Oct-2021 15:30:00     229          9            2.1945 
    08-Oct-2021 16:00:00     214          9            2.3331 
    08-Oct-2021 16:15:00     220          9            2.4322 
    08-Oct-2021 17:15:00     183          9            2.4121 
    08-Oct-2021 17:30:00       0          1            2.4047 
             :                :           :             :     
    18-Oct-2021 17:30:00     109          7           0.19229 
    18-Oct-2021 17:45:00      10          1           0.22684 
    18-Oct-2021 18:00:00     217          9           0.14355 
    18-Oct-2021 18:15:00     175          9           0.11803 
    18-Oct-2021 18:30:00     199          9          0.084789 
    18-Oct-2021 18:45:00     198          9           0.05123 
	Display all 527 rows.

[Siegmund Nuyts]

Thanks a lot Peter!

That worked perfectly