Extracting specific data from time range excel

I have exported data from a sensor reading. It doesn't allow me to extract hourly data and that's what I really need. In the excel table, I have the device name, date and time (10 mins interval), the readings. I would like to know a faster way to filter out 1 data reading per hour (data is provided every 10 mins interval).

Answer 1

[Star Strider]

It would help to have the Excel file.

The ‘1 data raeading per hour’ request is open to intpretation.

First, convert the timestamps to a datetime array.

Then, depending on what you want to do, you can either take a specific interval in every hour, or aggregate the data over every hour.

T1 = table(datetime(2023,03,01)+minutes(0:10:300).', randn(31,1), 'VariableNames',{'Timestamp','Sensor'})

T1 = 31×2 table
         Timestamp           Sensor 
    ____________________    ________

    01-Mar-2023 00:00:00    0.057252
    01-Mar-2023 00:10:00      0.4544
    01-Mar-2023 00:20:00     0.91079
    01-Mar-2023 00:30:00    -0.83407
    01-Mar-2023 00:40:00       1.745
    01-Mar-2023 00:50:00     0.13078
    01-Mar-2023 01:00:00     0.43101
    01-Mar-2023 01:10:00     -0.2888
    01-Mar-2023 01:20:00      1.5153
    01-Mar-2023 01:30:00       1.317
    01-Mar-2023 01:40:00    -0.56638
    01-Mar-2023 01:50:00    -0.84528
    01-Mar-2023 02:00:00      1.0693
    01-Mar-2023 02:10:00    -0.68066
    01-Mar-2023 02:20:00      1.6393
    01-Mar-2023 02:30:00     -2.4429

EveryHour = T1(minute(T1{:,1})==0,:)                        % First Entry Every Hour

EveryHour = 6×2 table
         Timestamp           Sensor 
    ____________________    ________

    01-Mar-2023 00:00:00    0.057252
    01-Mar-2023 01:00:00     0.43101
    01-Mar-2023 02:00:00      1.0693
    01-Mar-2023 03:00:00    -0.54088
    01-Mar-2023 04:00:00    -0.16327
    01-Mar-2023 05:00:00    -0.37763

HourlyMean = table2timetable(T1);
HourlyMean = retime(HourlyMean, 'hourly','mean')            % Mean Of Every Hour

HourlyMean = 6×1 timetable
         Timestamp           Sensor 
    ____________________    ________

    01-Mar-2023 00:00:00      0.4107
    01-Mar-2023 01:00:00     0.26048
    01-Mar-2023 02:00:00     0.29025
    01-Mar-2023 03:00:00    -0.14756
    01-Mar-2023 04:00:00     0.24564
    01-Mar-2023 05:00:00    -0.37763

A bit of clarification of what you want to do would help.

.

[Y.y]

I have attached an example of the data file.

How do I extract the 1 reading each from, say, 0:00 hr, 01:00hr etc.

Much appreciated!

[Star Strider]

T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/1314110/Data%20Sample.csv')

Warning: The DATETIME data was created using format 'MM/dd/uuuu HH:mm' but also matched 'dd/MM/uuuu HH:mm'.
To avoid ambiguity, supply a datetime format using SETVAROPTS, e.g.
opts = setvaropts(opts,varname,'InputFormat','MM/dd/uuuu HH:mm');

Warning: Column headers from the file were modified to make them valid MATLAB identifiers before creating variable names for the table. The original column headers are saved in the VariableDescriptions property.
Set 'VariableNamingRule' to 'preserve' to use the original column headers as table variable names.

T1 = 143×4 table
       Var1           Var2        Var3        RecordTime   
    __________    ____________    _____    ________________

    {'Room 1'}    {'Device 1'}    18.36    01/02/2023 00:09
    {'Room 1'}    {'Device 1'}    18.39    01/02/2023 00:19
    {'Room 1'}    {'Device 1'}    18.41    01/02/2023 00:29
    {'Room 1'}    {'Device 1'}    18.29    01/02/2023 00:39
    {'Room 1'}    {'Device 1'}    18.36    01/02/2023 00:49
    {'Room 1'}    {'Device 1'}    18.35    01/02/2023 00:59
    {'Room 1'}    {'Device 1'}    18.32    01/02/2023 01:09
    {'Room 1'}    {'Device 1'}    18.38    01/02/2023 01:20
    {'Room 1'}    {'Device 1'}    18.39    01/02/2023 01:30
    {'Room 1'}    {'Device 1'}    18.36    01/02/2023 01:40
    {'Room 1'}    {'Device 1'}    18.36    01/02/2023 01:50
    {'Room 1'}    {'Device 1'}    18.35    01/02/2023 02:00
    {'Room 1'}    {'Device 1'}    18.32    01/02/2023 02:10
    {'Room 1'}    {'Device 1'}    18.31    01/02/2023 02:20
    {'Room 1'}    {'Device 1'}    18.42    01/02/2023 02:30
    {'Room 1'}    {'Device 1'}    18.44    01/02/2023 02:40

% TT1 = table2timetable(movevars(T1,4,'Before',1))

EveryHour = T1(minute(T1{:,4})==0,:)

EveryHour = 5×4 table
       Var1           Var2        Var3        RecordTime   
    __________    ____________    _____    ________________

    {'Room 1'}    {'Device 1'}    18.35    01/02/2023 02:00
    {'Room 1'}    {'Device 1'}    18.44    01/02/2023 03:00
    {'Room 1'}    {'Device 1'}    18.36    01/02/2023 04:00
    {'Room 1'}    {'Device 1'}    18.28    01/02/2023 05:00
    {'Room 1'}    {'Device 1'}    18.41    01/02/2023 06:00

No exact data exist for some time values, such as ‘00:00’ and ‘01:00’. They could be forced to exist using the approach in Interpolate Timetable Data to Time Vector, however that can create data where none previously existed, so I do not usuallly recommend it.

.