Create 30 minute bins by reading time stamps
Mostra commenti meno recenti
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
Risposta accettata
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.
% 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
VN = T1.Properties.VariableNames;
% timestats = [mean(diff(T1{:,1})) std(diff(T1{:,1}))]
Ts = mean(diff(T1{:,1}))
Fs = 1/Ts
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 Commenti
Alexander
il 15 Dic 2023
It's 10s in fractions of a day.
Bradley
il 15 Dic 2023
Star Strider
il 15 Dic 2023
@Bradley — So in this data set there are 3 days of data (from about ¼ to about 3¼ days) at 10s intervals? (I sorted and then resampled them, because otherwise the times are all over the place, and resampling to a uniform sampling frequency is necessary for any signal processing you may want to do. I can leave out the resampling step if necessary, that is if it corrupts the original time steps.)
I would like to be able to get the times into days-hours-minutes-seconds format. If I can create a timetable from these data, I can use retime to calculate the 30-minute averages. (The MATLAB date and time functions that were introduced a few years ago are tremendously powerful. It would be nice to be able to use them.)
Bradley
il 15 Dic 2023
files = dir('*.xlsx');
for k = 1:numel(files)
filename{k} = files(k). name;
Data{k,:} = readtable(filename{k}, 'VariableNamingRule','preserve');
end
filename{:}
Data{1}
Data{2} % Analyse 'Data{2}'
DataRevisedTT = table2timetable(Data{2}(:,[1 3 4 5 6])) % Need To Leave Out 'Device' For This to Work
% DataRevisedTT.('Calculated time')
DataRevisedTT30 = retime(DataRevisedTT, 'regular', 'mean', 'TimeStep',minutes(30)) % Use 'retime' To Calculate 30-Minute 'mean' Values
VN = DataRevisedTT30.Properties.VariableNames; % Variable Names
figure
stairs(DataRevisedTT30.('Calculated time'), DataRevisedTT30{:,1:end})
grid
xlabel('Calculated time')
ylabel('Dependent Variable Value')
legend(VN{:}, 'Location','best')
title(["Entire ‘"+string(filename{2})+" Record: 30-Minute Averages"])
[y,m,d] = ymd(DataRevisedTT30.('Calculated time'));
[G,ID] = findgroups(d);
Days = accumarray(G, (1:numel(G)).', [], @(x){DataRevisedTT30(x,:)}) % 'timetable' Arrays For Each Day
figure % Plot Daily 'timetable' Arrays
tiledlayout(4,2)
for k = 1:numel(Days)
nexttile
stairs(Days{k}.('Calculated time'), Days{k}{:,1:end})
grid
end
hl = legend(VN{:}, 'NumColumns',2);
nexttile;
Ax = gca;
Ax.Visible = 0;
pos = Ax.Position;
hl.Position = pos;
title(hl, "Common Legend")
sgtitle('Daily 30-Minute Averages')
Having accurate datetime values in the ‘DataRevised.xlsx’ file made all the difference.
Plotting the individual days makes the data a bit easier to visualise.
.
Bradley
il 18 Dic 2023
Star Strider
il 18 Dic 2023
My pleasure!
If my Answer helped you solve your problem, please Accept it!
.
Più risposte (2)
Alexander
il 13 Dic 2023
0 voti
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 Commenti
Bradley
il 14 Dic 2023
Alexander
il 14 Dic 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.
Mathieu NOE
il 13 Dic 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 Commenti
Bradley
il 14 Dic 2023
Mathieu NOE
il 19 Dic 2023
hello Bradley
I don't have this problem on my side , I supposed it's maybe due how my (french) excel reads your file
as I use xlsread, the date comes out as a fraction of 24 hours (the first date is 0.4701 => 11.2824 hours or 11:16:58 as in your excel file
what are your first column of y when your read your data ?
y = xlsread('Data.xlsx');
Bradley
il 19 Dic 2023
Mathieu NOE
il 19 Dic 2023
I know I would not win this time (again !)
Categorie
Scopri di più su Timetables in Centro assistenza e File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!
Translated by 
