How to list all tasks in a table and sum up total time spend on each?
4 次查看(过去 30 天)
显示 更早的评论
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
0 个评论
采纳的回答
Voss
2023-2-28
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')
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
更多回答(1 个)
Stephen23
2023-3-1
编辑:Stephen23
2023-3-1
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.
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!
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')
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,:)
Good data design goes a looooong way towards better code!
0 个评论
另请参阅
类别
在 Help Center 和 File Exchange 中查找有关 Logical 的更多信息
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!