How to list all tasks in a table and sum up total time spend on each?

4 次查看（过去 30 天）
Harr
Harr 2023-2-28
编辑： Stephen23 2023-3-1
Hi,
I have a table (attached), first column is date (in January) and the rest of 21 columnes has different type of tasks with their starting and stopping times with format HH:MM:SS! How can i get a list showing all the tasks and total hours for each task?
I have tried the following code but i didnt manage to make it work!
% list of tasks to calculate total hours
tasks = {'Task1', 'Task2', 'Task3', 'Task4'};
% read table from file
task_table = readtable('task_table.xlsx');
% initialize array to store total hours for each task
task_times = zeros(size(tasks));
% loop through tasks and calculate total hours
for i = 1:length(tasks)
task_name = tasks{i};
for j = 1:size(task_table, 1)
for k = 1:size(task_table, 2)
if strcmp(task_table{j, k}, task_name)
% find end time of task
end_time = datetime(task_table{j, k-1}, 'Format', 'HH:mm:ss');
% find start time of task
start_time = datetime(task_table{j, k-2}, 'Format', 'HH:mm:ss');
% calculate duration in hours
task_hours = hours(end_time - start_time);
% add to total hours for this task
task_times(i) = task_times(i) + task_hours;
end
end
end
end
% display results
for i = 1:length(tasks)
fprintf('Total hours for %s: %.2f\n', tasks{i}, task_times(i));
end

请先登录，再回答此问题。

采纳的回答

Voss
Voss 2023-2-28
Ran in:
Note that the start_time* and end_time* columns in task_table are fractions of a day:
% list of tasks to calculate total hours
tasks = {'Task1', 'Task2', 'Task3', 'Task4'};
% read table from file
task_table = readtable('task_table.xlsx')
task_table = 30×19 table
Date start_time end_time Task start_time_1 end_time_1 Task_1 start_time_2 end_time_2 Task_2 start_time_3 end_time_3 Task_3 start_time_4 end_time_4 Task_4 start_time_5 end_time_5 Task_5 ___________ __________ ________ __________ ____________ __________ __________ ____________ __________ __________ ____________ __________ __________ ____________ __________ __________ ____________ __________ __________ 01-Jan-2023 0.33333 0.45833 {'Task8' } 0.5 0.54167 {'Task5' } 0.54167 0.70833 {'Task8' } NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} 02-Jan-2023 0.33333 0.45833 {'Task6' } 0.5 0.54167 {'Task5' } 0.54167 0.70833 {'Task6' } NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} 03-Jan-2023 0.33333 0.5 {'Task8' } 0.54167 0.71181 {'Task13'} NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} 04-Jan-2023 0.33333 0.67014 {'Task13'} NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} 05-Jan-2023 NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} 06-Jan-2023 NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} 07-Jan-2023 NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} 08-Jan-2023 0.33333 0.375 {'Task8' } 0.375 0.39583 {'Task5' } 0.39583 0.47917 {'Task8' } 0.52083 0.5625 {'Task4' } 0.5625 0.6875 {'Task4' } NaN NaN {0×0 char} 09-Jan-2023 0.33333 0.45833 {'Task6' } 0.5 0.72917 {'Task3' } NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} 10-Jan-2023 0.35417 0.47917 {'Task4' } 0.5 0.6875 {'Task3' } NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} 11-Jan-2023 0.35417 0.45833 {'Task4' } 0.5 0.66667 {'Task5' } 0.66667 0.72917 {'Task4' } NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} 12-Jan-2023 0.35417 0.47917 {'Task6' } 0.52083 0.625 {'Task4' } 0.625 0.77083 {'Task6' } NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} 13-Jan-2023 NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} 14-Jan-2023 NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} NaN NaN {0×0 char} 15-Jan-2023 0.375 0.39583 {'Task4' } 0.5 0.54167 {'Task5' } 0.39583 0.47917 {'Task3' } 0.54167 0.625 {0×0 char} 0.875 0.98611 {'Task6' } NaN NaN {0×0 char} 16-Jan-2023 0.35417 0.45833 {'Task3' } 0.5 0.625 {'Task4' } 0.625 0.66667 {'Task5' } 0.875 0.95833 {'Task4' } NaN NaN {0×0 char} NaN NaN {0×0 char}
Therefore, you can simply multiply them by 24 to convert them to hours.
% initialize array to store total hours for each task
task_times = zeros(size(tasks));
% loop through tasks and calculate total hours
for i = 1:length(tasks)
task_name = tasks{i};
for j = 1:size(task_table, 1)
for k = 1:size(task_table, 2)
if strcmp(task_table{j, k}, task_name)
% find end time of task
end_time = task_table{j, k-1}*24;
% find start time of task
start_time = task_table{j, k-2}*24;
% calculate duration in hours
task_hours = end_time - start_time;
% add to total hours for this task
task_times(i) = task_times(i) + task_hours;
end
end
end
end
% display results
for i = 1:length(tasks)
fprintf('Total hours for %s: %.2f\n', tasks{i}, task_times(i));
end
Total hours for Task1: 3.50 Total hours for Task2: 2.00 Total hours for Task3: 26.17 Total hours for Task4: 47.50

更多回答（1 个）

Stephen23
Stephen23 2023-3-1
编辑：Stephen23 2023-3-1
Ran in:
You can do this in just a few lines of code. Don't fight MATLAB with multiple nested loops!
The data would be so much easier to work with if every task was listed on its own line, rather than that unfortunate file format of having one line per day and extending the tasks out into more and more and more and more columns:
T = readtable('task_table.xlsx', 'TextType','string') % avoid this file format.
T = 30×19 table
Date start_time end_time Task start_time_1 end_time_1 Task_1 start_time_2 end_time_2 Task_2 start_time_3 end_time_3 Task_3 start_time_4 end_time_4 Task_4 start_time_5 end_time_5 Task_5 ___________ __________ ________ _________ ____________ __________ _________ ____________ __________ _________ ____________ __________ _________ ____________ __________ _________ ____________ __________ _________ 01-Jan-2023 0.33333 0.45833 "Task8" 0.5 0.54167 "Task5" 0.54167 0.70833 "Task8" NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> 02-Jan-2023 0.33333 0.45833 "Task6" 0.5 0.54167 "Task5" 0.54167 0.70833 "Task6" NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> 03-Jan-2023 0.33333 0.5 "Task8" 0.54167 0.71181 "Task13" NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> 04-Jan-2023 0.33333 0.67014 "Task13" NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> 05-Jan-2023 NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> 06-Jan-2023 NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> 07-Jan-2023 NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> 08-Jan-2023 0.33333 0.375 "Task8" 0.375 0.39583 "Task5" 0.39583 0.47917 "Task8" 0.52083 0.5625 "Task4" 0.5625 0.6875 "Task4" NaN NaN <missing> 09-Jan-2023 0.33333 0.45833 "Task6" 0.5 0.72917 "Task3" NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> 10-Jan-2023 0.35417 0.47917 "Task4" 0.5 0.6875 "Task3" NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> 11-Jan-2023 0.35417 0.45833 "Task4" 0.5 0.66667 "Task5" 0.66667 0.72917 "Task4" NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> 12-Jan-2023 0.35417 0.47917 "Task6" 0.52083 0.625 "Task4" 0.625 0.77083 "Task6" NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> 13-Jan-2023 NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> 14-Jan-2023 NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> NaN NaN <missing> 15-Jan-2023 0.375 0.39583 "Task4" 0.5 0.54167 "Task5" 0.39583 0.47917 "Task3" 0.54167 0.625 <missing> 0.875 0.98611 "Task6" NaN NaN <missing> 16-Jan-2023 0.35417 0.45833 "Task3" 0.5 0.625 "Task4" 0.625 0.66667 "Task5" 0.875 0.95833 "Task4" NaN NaN <missing> NaN NaN <missing>
So the first thing we will do is fix that data arrangement (this is how the data should have been saved in the first place):
U = stack(T,{regexpPattern('^start_.*'),regexpPattern('^end_.*'),regexpPattern('^Task.*')}, 'NewDataVariableName',{'StartTime','EndTime','Task'});
U = rmmissing(U) % Aaaah, that is much better!
U = 81×5 table
Date Indicator StartTime EndTime Task ___________ _________ _________ _______ ________ 01-Jan-2023 2 0.33333 0.45833 "Task8" 01-Jan-2023 5 0.5 0.54167 "Task5" 01-Jan-2023 8 0.54167 0.70833 "Task8" 02-Jan-2023 2 0.33333 0.45833 "Task6" 02-Jan-2023 5 0.5 0.54167 "Task5" 02-Jan-2023 8 0.54167 0.70833 "Task6" 03-Jan-2023 2 0.33333 0.5 "Task8" 03-Jan-2023 5 0.54167 0.71181 "Task13" 04-Jan-2023 2 0.33333 0.67014 "Task13" 08-Jan-2023 2 0.33333 0.375 "Task8" 08-Jan-2023 5 0.375 0.39583 "Task5" 08-Jan-2023 8 0.39583 0.47917 "Task8" 08-Jan-2023 11 0.52083 0.5625 "Task4" 08-Jan-2023 14 0.5625 0.6875 "Task4" 09-Jan-2023 2 0.33333 0.45833 "Task6" 09-Jan-2023 5 0.5 0.72917 "Task3"
Now that the data is arranged properly, it is easy to sum the time for each task:
U.Hours = hours(hours(days(U.EndTime-U.StartTime)));
G = groupsummary(U,'Task','sum','Hours')
G = 12×3 table
Task GroupCount sum_Hours ________ __________ _________ "Task1" 2 3.5 hr "Task10" 2 5 hr "Task11" 3 4.5 hr "Task12" 2 4 hr "Task13" 3 14.167 hr "Task2" 2 2 hr "Task3" 12 26.167 hr "Task4" 24 47.5 hr "Task5" 11 13 hr "Task6" 8 23.167 hr "Task8" 6 15.5 hr "Task9" 6 14 hr
And if you only want those four tasks, then you can simply filter them, e.g.:
tasks = {'Task1', 'Task2', 'Task3', 'Task4'};
[X,Y] = ismember(tasks,G.Task);
H = G(Y,:)
H = 4×3 table
Task GroupCount sum_Hours _______ __________ _________ "Task1" 2 3.5 hr "Task2" 2 2 hr "Task3" 12 26.167 hr "Task4" 24 47.5 hr
Good data design goes a looooong way towards better code!

类别

在 Help Center 和 File Exchange 中查找有关 Data Type Conversion 的更多信息

标签

产品


版本

R2018b

Community Treasure Hunt

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

Start Hunting!

Translated by