# Trying to get time between each start and stop & total time per day from a text file.

1 view (last 30 days)
Natalie Schmidt on 4 Aug 2021
Commented: Natalie Schmidt on 6 Aug 2021
This text file has the start & end times: FDATA.TXT (attached). I want to find the time between each StartF and EndF times and add it to the table. I then want to find the total time per day and add it to the table. I attached what I want it to generally look like in MATLAB (the screenshot is in Excel for an example). I've tried everything and would be so thankful for help. :) Thank you!
% convert text file to a timetable
T = table2timetable(T);
% find the time between each start and end (each EndF- the StartF above it)
% find the total time per day
TTnum = T
TTnum.Var1 = [];
TT2 = retime(TTnum,'daily','sum'); % *have to do the sum of the DELTA T columns per day??

Simon Chan on 4 Aug 2021
Noticed that row #35 and #36 are both starting time, hence I remove row #35 and try the code as follows:
clear; clc;
T(35,:)=[]; % Remove row #35
Nz = size(T.Var2,1);
T.Var4=repmat(duration.empty(1,0),Nz,1); % Prepare column 4
T.Var5 = NaT(Nz,1); % Prepare column 5
T.Var6=repmat(duration.empty(1,0),Nz,1); % Prepare column 6
%
T.Var4(1:2:Nz) = T.Var2(2:2:Nz)-T.Var2(1:2:Nz); % Calculate duration
[which_date, idx_date,~] = unique(T.Var3); % Finding unique date
idx_missing = true(Nz,1); % Index to remove the zero duration
for k = 1:size(which_date,1)
G = groupfilter(T,'Var3',@(x) x==which_date(k),'Var3');
T.Var5(idx_date(k)) = which_date(k); % Fill in column 5 for unique date
T.Var6(idx_date(k)) = sum(G.Var4); % Fill in column 6 for total duration
idx_missing(idx_date(k)) = false;
end
T.Var4(2:2:Nz)=missing; % Remove zero duration on column 4
T.Var6(idx_missing) = missing; % Remove zero duration on column 6
writetable(T,'result.xlsx')
##### 2 CommentsShowHide 1 older comment
Natalie Schmidt on 6 Aug 2021
Thank you so much! I also added in that it calculates the total time per month (attached).
But do you have any ideas on how to fix the following:
1. In the "Monthly_Time" column, there's "00:00:00" showing up in the middle of a month (ie line 36 in the Excel). How do I stop this?
2. In the 3rd Excel column, each date has a "0:00" next to it. How do I get rid of these times and just show the date?

Peter Perkins on 5 Aug 2021
I would go with unstack:
>> T1(T1.Var2=='12:14:18',:) = [];
>> T1.Properties.VariableNames = ["StartEnd" "Time" "Date"];
>> T1.Date.Format = 'default';
>> TT1 = table2timetable(T1,"RowTimes","Date");
ans =
8×2 timetable
Date StartEnd Time
___________ ___________ ________
14-Apr-2021 {'StartF:'} 12:28:50
14-Apr-2021 {'EndF:' } 12:29:26
14-Apr-2021 {'StartF:'} 12:29:28
14-Apr-2021 {'EndF:' } 12:29:29
14-Apr-2021 {'StartF:'} 12:29:30
14-Apr-2021 {'EndF:' } 12:29:56
14-Apr-2021 {'StartF:'} 12:35:40
14-Apr-2021 {'EndF:' } 12:35:41
There's nothing but the order to say which starts and ends go together. Add something explicit, and unstack the start/end times into two variables to compute the elapsed times.
>> TT1.Index = repelem((1:height(TT1)/2)',2);
>> TT2 = unstack(TT1,"Time","StartEnd","GroupingVariables","Index","NewDataVariableNames",["End" "Start"]);
ans =
8×3 timetable
Date Index End Start
___________ _____ ________ ________
14-Apr-2021 1 12:29:26 12:28:50
14-Apr-2021 2 12:29:29 12:29:28
14-Apr-2021 3 12:29:56 12:29:30
14-Apr-2021 4 12:35:41 12:35:40
14-Apr-2021 5 12:35:47 12:35:43
14-Apr-2021 6 12:36:45 12:36:44
14-Apr-2021 7 12:36:54 12:36:47
14-Apr-2021 8 12:36:56 12:36:55>> TT2.Index = []; % remove it
>> TT2.Duration = TT2.End - TT2.Start;
8×3 timetable
Date End Start Duration
___________ ________ ________ ________
14-Apr-2021 12:29:26 12:28:50 00:00:36
14-Apr-2021 12:29:29 12:29:28 00:00:01
14-Apr-2021 12:29:56 12:29:30 00:00:26
14-Apr-2021 12:35:41 12:35:40 00:00:01
14-Apr-2021 12:35:47 12:35:43 00:00:04
14-Apr-2021 12:36:45 12:36:44 00:00:01
14-Apr-2021 12:36:54 12:36:47 00:00:07
14-Apr-2021 12:36:56 12:36:55 00:00:01
It would also be possible to avoid add/removing Index by replacing unstack with explicit subscripting on the table using 1:2:end and 2:2:end.
Count up the total time in each day:
>> fun = @(s,e) e(end) - s(1)
fun =
function_handle with value:
@(s,e)e(end)-s(1)
>> TT3 = rowfun(fun,TT2,"InputVariables",["Start" "End"],"GroupingVariables","Date","OutputVariableNames","TotalTime")
>> TT3 =
4×2 timetable
Date GroupCount TotalTime
___________ __________ _________
14-Apr-2021 17 00:21:05
23-Apr-2021 92 01:38:35
28-Apr-2021 85 04:02:25
05-May-2021 18 03:17:46
Natalie Schmidt on 6 Aug 2021
Thank you so much! I also added in that it calculates the total time per month (attached).
But do you have any ideas on how to fix the following:
1. In the "Monthly_Time" column, there's "00:00:00" showing up in the middle of a month (ie line 36 in the Excel). How do I stop this?
2. In the 3rd Excel column, each date has a "0:00" next to it. How do I get rid of these times and just show the date?

R2018a

### Community Treasure Hunt

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

Start Hunting!