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

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??

Answer 1

[Simon Chan]

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')

[Natalie Schmidt]

Thank you! But when I open the Excel file, the dates have 0:00 after them, i.e.

"4/14/2021 0:00". How do I get rid of that?

[Natalie Schmidt]

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?

Answer 2

[Peter Perkins]

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 

[Natalie Schmidt]

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?