Smallest interval in array

Hi everyone,

I am attempting to write software that organizes data from a machine that gets triggered multiple times throughout the day. I need to find which times in the day have the highest rate of triggering. In other words, how do I find the intervals with the smallest gaps in time within the data list?

The .xlsx list is attached.

Each row on the list represents an "encounter", and the machine takes a reading of the current time, temp, weight and light level. I need to find out what time interval has the highest rate of triggers.

Any ideas where to start?

Thanks in advance!

[Jan]

Start with importing the Excel file to Matlab. Which Matlab version are you using?

Then define, what you exactly want to solve: Are you really looking for the shortest distance between the time values in the 1st column? Or does "interval with highest rate" mean e.g. a certain interval of e.g. 2 hours, which contains the highest number of events? Do the numbers in the other columns matter?

[Pesach Nestlebaum]

Thanks for answering,

I imported the data already and separated the other columns for later use. That's a great clarification, I guess I am looking for a two hour interval with the highest rate of events.

Ultimately, i'd like the software to determine which times of the day are the most active for any set of data it receives, not this specific list, and keep a log for a monthly average. But this is a good start.

Answer 1

[Star Strider]

I am not certain what the desired result is here.

Try this —

T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/980625/enc_data_samp.xlsx', 'VariableNamingRule','preserve')

T1 = 59×4 table
      Time      Light    Weight    Temp
    ________    _____    ______    ____

     0.04375      1       450       52 
    0.069444      0       988       52 
    0.091667      1       633       53 
     0.14722      1        68       54 
     0.16667      2        77       55 
     0.20833      2       540       58 
     0.22431      4       406       57 
     0.23681      4       105       58 
     0.24306      4       100       60 
        0.25      5        46       62 
     0.27014      6        79       63 
     0.29375      7       102       63 
     0.29583      7       343       63 
     0.30208      7        76       65 
     0.30417      7        89       65 
     0.30486      7        68       65 

Tt = cumsum([0; T1.Time]);                              % Create Time Vector
Td = diff([0; 0; T1.Time])+eps;                         % Differences Between Trigger Events
Fr = 1./Td;                                             % Frequency Of Trigger Events

figure
plot(Tt, Td)
grid
xlabel('Time')
ylabel('Trigger Frequency')

I am not certain what the time vector (the independent variable here) actually is (I suspect that it should be ‘time of day’ or something similar), however this is likely the easiest way to determine the frequency of the trigger events as a function of it.

.

[Pesach Nestlebaum]

the column under time is what happens when you enter hours such as 6:00 into excel. It automatically lists 6:00 as 0.2500, since 6:00 is 25% of one day. the data sheet i have is not the full 24 houors, but a sample of a day just t see if i can get a working model for this.

I'm not sure what your graph represents, can you ellaborate? let me know if I can clear anything up on my end.

[Pesach Nestlebaum]

I apologize, i see now what you did! Yes this is what i was looking for in terms of distance between triggers but how can I find which hour on this graph contains the highest rate of encounters aka the lowest overall distance when summed

[Star Strider]

This is a bit convoluted, and having the times as fractions of a day does not make for the easiest conversion code.

This creates a table, converts it to a timetable, then uses retime to aggregate the trigger events every hour, then plots the results —

T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/980625/enc_data_samp.xlsx', 'VariableNamingRule','preserve');
Tt = [repmat(fix(datenum(now)), size(T1,1), 1) + T1.Time];                  % Create 'datenum' Array
T1.Time = datetime(Tt, 'ConvertFrom','datenum');                            % Create Time Vector
T1.Trigger = ones(size(T1.Time))                                            % Create SEparate 'Triggerr' Variable

T1 = 59×5 table
            Time            Light    Weight    Temp    Trigger
    ____________________    _____    ______    ____    _______

    27-Apr-2022 01:03:00      1       450       52        1   
    27-Apr-2022 01:40:00      0       988       52        1   
    27-Apr-2022 02:12:00      1       633       53        1   
    27-Apr-2022 03:32:00      1        68       54        1   
    27-Apr-2022 04:00:00      2        77       55        1   
    27-Apr-2022 05:00:00      2       540       58        1   
    27-Apr-2022 05:23:00      4       406       57        1   
    27-Apr-2022 05:41:00      4       105       58        1   
    27-Apr-2022 05:50:00      4       100       60        1   
    27-Apr-2022 06:00:00      5        46       62        1   
    27-Apr-2022 06:29:00      6        79       63        1   
    27-Apr-2022 07:03:00      7       102       63        1   
    27-Apr-2022 07:06:00      7       343       63        1   
    27-Apr-2022 07:15:00      7        76       65        1   
    27-Apr-2022 07:18:00      7        89       65        1   
    27-Apr-2022 07:19:00      7        68       65        1   

TT1 = table2timetable(T1);                                                  % Create 'timetable'
TT1c = retime(TT1,'hourly','count')                                         % Count 'Trigger' Variable Hourly

TT1c = 16×4 timetable
            Time            Light    Weight    Temp    Trigger
    ____________________    _____    ______    ____    _______

    27-Apr-2022 01:00:00      2         2        2        2   
    27-Apr-2022 02:00:00      1         1        1        1   
    27-Apr-2022 03:00:00      1         1        1        1   
    27-Apr-2022 04:00:00      1         1        1        1   
    27-Apr-2022 05:00:00      4         4        4        4   
    27-Apr-2022 06:00:00      2         2        2        2   
    27-Apr-2022 07:00:00     11        11       11       11   
    27-Apr-2022 08:00:00     13        13       13       13   
    27-Apr-2022 09:00:00      6         6        6        6   
    27-Apr-2022 10:00:00      6         6        6        6   
    27-Apr-2022 11:00:00      3         3        3        3   
    27-Apr-2022 12:00:00      3         3        3        3   
    27-Apr-2022 13:00:00      2         2        2        2   
    27-Apr-2022 14:00:00      1         1        1        1   
    27-Apr-2022 15:00:00      2         2        2        2   
    27-Apr-2022 16:00:00      1         1        1        1   

figure
stairs(hour(TT1c.Time), TT1c.Trigger)
grid
xlabel('Time')
ylabel('Trigger Frequency')

This is the most efficient way I can think of to do this.

EDIT — (27 Apr 2022 at 18:16)

Corrected typographical error.

.

[Pesach Nestlebaum]

Thank you. is there a way to output which 1 hour interval has the highest volume of activity? I see the spike in the middle is obviously where it is in this case, but in othe cases it may not be so obvious. I'd need it to say something like "The interval with the most activity is between 7:30 and 8:30"

but the code would need to calculate what that interval is.

Thanks!

[Star Strider]

The retime function deals only in whole hours.

T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/980625/enc_data_samp.xlsx', 'VariableNamingRule','preserve');
Tt = [repmat(fix(datenum(now)), size(T1,1), 1) + T1.Time];                  % Create 'datenum' Array
T1.Time = datetime(Tt, 'ConvertFrom','datenum');                            % Create Time Vector
T1.Trigger = ones(size(T1.Time));                                           % Create Separate 'Trigger' Variable

TT1 = table2timetable(T1);                                                  % Create 'timetable'
TT1c = retime(TT1,'hourly','count')                                         % Count 'Trigger' Variable Hourly

TT1c = 16×4 timetable
            Time            Light    Weight    Temp    Trigger
    ____________________    _____    ______    ____    _______

    27-Apr-2022 01:00:00      2         2        2        2   
    27-Apr-2022 02:00:00      1         1        1        1   
    27-Apr-2022 03:00:00      1         1        1        1   
    27-Apr-2022 04:00:00      1         1        1        1   
    27-Apr-2022 05:00:00      4         4        4        4   
    27-Apr-2022 06:00:00      2         2        2        2   
    27-Apr-2022 07:00:00     11        11       11       11   
    27-Apr-2022 08:00:00     13        13       13       13   
    27-Apr-2022 09:00:00      6         6        6        6   
    27-Apr-2022 10:00:00      6         6        6        6   
    27-Apr-2022 11:00:00      3         3        3        3   
    27-Apr-2022 12:00:00      3         3        3        3   
    27-Apr-2022 13:00:00      2         2        2        2   
    27-Apr-2022 14:00:00      1         1        1        1   
    27-Apr-2022 15:00:00      2         2        2        2   
    27-Apr-2022 16:00:00      1         1        1        1   

[maxEvent,idx] = max(TT1c.Trigger);
fprintf('\n\nThe interval with the most activty is hour %2d with %3d events.\n\n',hour(TT1c.Time(idx)),maxEvent)

The interval with the most activty is hour  8 with  13 events.

figure
stairs(hour(TT1c.Time), TT1c.Trigger)
grid
Ax = gca;
xt = Ax.XTick;
Ax.XTick = min(xt):max(xt);
xlabel('Time (Hour)')
ylabel('Trigger Frequency (Events/Hour)')

.

[Pesach Nestlebaum]

This is sufficient. Thank you for your hard work!

[Pesach Nestlebaum]

One last question:

This code produces a variable "idx" which is equal to a single number: 8. This seems to be because there is only one column of time data being processed.

Suppose there were 7 columns of time entries instead of just one, how would i produce an array with all 7 idx values? In other words, say there were 7 days of data, each day had different number of triggers. Can I accomplish this with a for loop? Obviously I need it to be coded, not manually updating the list every time.

Thanks!

[Star Strider]

As always, my pleasure!

The ‘idx’ variable is simply the row number of the ‘Trigger’ tally maximum. It can be used to refer to the entire row of the timetable, whatever the timetable contains, so:

maxTriggerRow = TT1c{idx,:}

to display all the variables in that row, or equivalently:

maxTriggerRow = TT1c(idx,:)

to display them with their variable names as well (note the difference between the curly braces {} and parentheses ()).

Here, it just happened to also be equivalent to the hour, and the fprintf statement referred to it as such.

Answer 2

[Thomas Pursche]

Hello Pesach,

when I understood it correctly please find in the following a first approach. Just read in the table, get the specific column and afterwards calculate the distances and minimal entry. Afterwards you can put them into an interval and count them if you want to.

% read in the table

tableData = readtable('enc_data_samp.xlsx');

% access intersting column

timeColumn = tableData.Time;

% calculate distance between each entry

for ii=1:numel(timeColumn)-1

distanceList(ii) = timeColumn(ii+1)-timeColumn(ii);

end

% get the minimal distance

minimalDistance = min(distanceList);

Best regards,

Thomas

[Pesach Nestlebaum]

Thank you! This is what I needed in terms of finding all the distances between each entry. how can I now fin which hour has the most entries aka the shortest overall distance if all the distances within that hour are summed up?