Extracting specific data from time range excel

6 次查看（过去 30 天）
Y.y
Y.y 2023-3-4
评论： Star Strider 2023-3-4
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).

请先登录，再回答此问题。

采纳的回答

Star Strider
Star Strider 2023-3-4
Ran in:
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.
.
  2 个评论
Y.y
Y.y 2023-3-4
编辑：Y.y 2023-3-4
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
Star Strider 2023-3-4
Ran in:
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.
.

请先登录，再进行评论。

更多回答（0 个）

类别

在 Help Center 和 File Exchange 中查找有关 Spreadsheets 的更多信息

标签

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!

Translated by