# Create 30 minute bins by reading time stamps

11 views (last 30 days)

Show older comments

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

##### 0 Comments

### Accepted Answer

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.

% 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 Comments

Star Strider
on 18 Dec 2023

My pleasure!

If my Answer helped you solve your problem, please Accept it!

.

### More Answers (2)

Alexander
on 13 Dec 2023

##### 2 Comments

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.

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

### See Also

### Categories

### Community Treasure Hunt

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

Start Hunting!