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

1 view (last 30 days)
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 = readtable('FDATA.TXT');
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??

Accepted Answer

Simon Chan
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 = readtable('FDATA.TXT');
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 Comments
Natalie Schmidt
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?

Sign in to comment.

More Answers (1)

Peter Perkins
Peter Perkins on 5 Aug 2021
I would go with unstack:
>> T1 = readtable('https://www.mathworks.com/matlabcentral/answers/uploaded_files/702232/FDATA.TXT');
>> T1(T1.Var2=='12:14:18',:) = [];
>> T1.Properties.VariableNames = ["StartEnd" "Time" "Date"];
>> T1.Date.Format = 'default';
>> TT1 = table2timetable(T1,"RowTimes","Date");
>> head(TT1)
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"]);
>> head(TT2)
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;
>> head(TT2)
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
  1 Comment
Natalie Schmidt
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?

Sign in to comment.

Products


Release

R2018a

Community Treasure Hunt

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

Start Hunting!

Translated by