Clear Filters
Clear Filters

Create 30 minute bins by reading time stamps

11 views (last 30 days)
I have a file with time stamps, due to error in the files (gaps in time due to faulty equipment) binning 30 minute sections using the following code creates errors in the actograms produced.
function[Average] = Av_30min(y)
Average = zeros(288,size(y,2));
k = 1;
for l =1:288
Average(l,1) = mean(y(k:k+(round(length(y)/288)-1),1));
k = k+((round(length(y)/288)-1));
end
end
I want to read from say 11:30:00 - 12:00:00 and average this bin and so on and so forth. Can anyone help?
Thanks

Accepted Answer

Star Strider
Star Strider on 14 Dec 2023
What do the ‘Calculated time’ values represent? Are they fractions of a day (from about ¼ to about 3¼ days) or something else?
Until that is resolved, converting them into something useful is not likely to be possible.
Here, I used sortrows and then fillmissing and resample to create some sort of order.
% imshow(imread('Timestamps in output.PNG'))
% function readfaultydata
% clear; close all
Data = xlsread('Data.xlsx',1,'A2:F60099');
T1 = readtable('Data.xlsx', 'VariableNamingRule','preserve');
t = Data(:,1);
dt = t(2)-t(1); % Your sample rate
t = (1:length(t))*dt; % Linear timeline
x = Data(:,3);
y = Data(:,4);
z = Data(:,5);
subplot(311);plot(t,x);grid;
subplot(312);plot(t,y);grid;
subplot(313);plot(t,z);grid;
T1 = sortrows(T1,1);
T1 = fillmissing(T1, 'nearest') % Sorted With NaN Values Interpolated
T1 = 60103×9 table
Calculated time Device X Y Z LDR Var7 Var8 Var9 _______________ __________ __ __ __ ___ ______ _______ _______ 0.26653 {'BSB_6A'} 0 70 56 0 0.2611 0.85946 0.96134 0.26664 {'BSB_6A'} 0 61 72 0 0.2611 0.85946 0.96134 0.26676 {'BSB_6A'} 5 57 67 0 0.2611 0.85946 0.96134 0.26687 {'BSB_6A'} 9 28 57 0 0.2611 0.85946 0.96134 0.26699 {'BSB_6A'} 60 0 44 0 0.2611 0.85946 0.96134 0.26711 {'BSB_6A'} 62 52 57 0 0.2611 0.85946 0.96134 0.26722 {'BSB_6A'} 48 36 70 0 0.2611 0.85946 0.96134 0.26734 {'BSB_6A'} 66 33 63 0 0.2611 0.85946 0.96134 0.26745 {'BSB_6A'} 70 50 60 0 0.2611 0.85946 0.96134 0.26757 {'BSB_6A'} 34 26 56 0 0.2611 0.85946 0.96134 0.26769 {'BSB_6A'} 51 47 75 0 0.2611 0.85946 0.96134 0.2678 {'BSB_6A'} 17 56 64 0 0.2611 0.85946 0.96134 0.26792 {'BSB_6A'} 50 40 29 0 0.2611 0.85946 0.96134 0.26803 {'BSB_6A'} 26 65 79 0 0.2611 0.85946 0.96134 0.26815 {'BSB_6A'} 22 73 85 0 0.2611 0.85946 0.96134 0.26826 {'BSB_6A'} 51 70 79 0 0.2611 0.85946 0.96134
VN = T1.Properties.VariableNames;
% timestats = [mean(diff(T1{:,1})) std(diff(T1{:,1}))]
Ts = mean(diff(T1{:,1}))
Ts = 4.9837e-05
Fs = 1/Ts
Fs = 2.0065e+04
Fn = Fs/2;
[XYZr, tr] = resample(T1{:,[3 4 5]}, T1{:,1}, Fs);
% format longg
% XYZr(end-4:end,:)
XYZr = XYZr(1:end-3,:);
tr = tr(1:end-3);
% XYZr(end-4:end,:)
x = XYZr(:,1);
y = XYZr(:,3);
z = XYZr(:,3);
figure
plot(tr, XYZr)
grid
xlabel([string(VN{1}) + " (Sorted & Resampled)"])
ylabel("Amplitude")
figure
tiledlayout(3,1)
for k = 1:size(XYZr,2)
nexttile
plot(tr, XYZr(:,k))
grid
ylabel("Amplitude")
title(["Column "+k])
end
xlabel([string(VN{1}) + " (Sorted & Resampled)"])
figure
scatter3(x,y,z, 10, z, '.')
colormap(turbo)
grid on
xlabel('X')
ylabel('Y')
zlabel('Z')
.
  7 Comments
Bradley
Bradley on 18 Dec 2023
Thank you very much! This works perfectly!
Star Strider
Star Strider on 18 Dec 2023
My pleasure!
If my Answer helped you solve your problem, please Accept it!
.

Sign in to comment.

More Answers (2)

Alexander
Alexander on 13 Dec 2023
Whers is this file Data.xlsx from? I think someone has corrupted the "Calculated time" column. In this column is a mixture of formulars and manual input (see cell A1793:A1795, e.g.). If you copy A3 and paste it to A60099 your timeline is correct your program might run (I'll attach a modified xlsx). Hopefully this was your intension.
  2 Comments
Bradley
Bradley on 14 Dec 2023
Hi Alexander,
Thanks for the suggestion but that is exactly the issue, if the data ran sequentially then my analytical code would have worked fine, but alas here we are trying to find another solution to atleast make some use of this data.
Alexander
Alexander on 14 Dec 2023
Here some code, w/o editing the xlsx:
function readfaultydata
clear; close all
Data = xlsread('Data.xlsx',1,'A2:F60099');
t = Data(:,1);
dt = t(2)-t(1); % Your sample rate
t = (1:length(t))*dt; % Linear timeline
x = Data(:,3);
y = Data(:,4);
z = Data(:,5);
subplot(311);plot(t,x);grid;
subplot(312);plot(t,y);grid;
subplot(313);plot(t,z);grid;
end
Hope it helps.

Sign in to comment.


Mathieu NOE
Mathieu NOE on 13 Dec 2023
hello
this is certainly not the best and most modern way to solve your problem , but as I have not really started digging with timetables and alike , so here's a (very) low level approach
someone younger / smarter may come up with a 2 lines solutions, but for the time being this is what I can offer
in very short , I am simply looking where 30 or 00 min appears in your data and the duration is exactly 180 samples (= 30 mins) then this data is averaged and saved
the new time data correspond to the beginning of the 30 min buffer
also I didn't copy paste the header line, I think you could manage that by yourself...
result should look like :
code
y = xlsread('Data.xlsx');
%% convert y firt column (time HH:MM:SS) to hours (h), minutes (m) and seconds (s)
h = y(:,1)*24;
m = 60*(h - floor(h));
s = 60*(m - floor(m));
h = floor(h);
m = floor(m);
s = round(s);
id1 = (m ==30); % find time index matching the 30 min timestamp
[begin1,ends1] = find_start_end_group(id1);
id2 = (m ==00); % find time index matching the 00 min timestamp
[begin2,ends2] = find_start_end_group(id2);
begin = sort([begin1;begin2]); % concat all index corresponding to 00 or 30 min time stamps
%% main loop
k = 0;
for ci = 1:numel(begin)-1
ind_start = begin(ci);
ind_stop = begin(ci+1);
samples = ind_stop - ind_start;
if samples == 180 % only valid segments are processed
k = k+1;
data = y(ind_start:ind_stop,2:end);
mean_data{k,1} = mean(data,'omitnan');
date{k,1} = [sprintf('%02d', h(ind_start)) ':' sprintf('%02d', m(ind_start)) ':' sprintf('%02d', s(ind_start))]; % a very crude method to create the time string
end
end
% export the data
out = [date mean_data];
writecell(out,'toto.xlsx');
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
function [begin,ends] = find_start_end_group(ind)
% This locates the beginning /ending points of data groups
% Important : ind must be a LOGICAL array
D = diff([0;ind(:);0]);
begin = find(D == 1);
ends = find(D == -1) - 1;
end
  4 Comments
Bradley
Bradley on 19 Dec 2023
Thanks Mathieu. If you see above Star Strider has answered this in a more succint way.
Mathieu NOE
Mathieu NOE on 19 Dec 2023
I know I would not win this time (again !)

Sign in to comment.

Tags

Products


Release

R2023b

Community Treasure Hunt

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

Start Hunting!