Searching a string on a table to get time
7 Ansichten (letzte 30 Tage)
Ältere Kommentare anzeigen
Hi,
I have an excel spreadsheet (attached). The table is basically information from a ticket system. The column are as follows: ID, creation date & time, several comments (each one in a different column) and ticket closing date & time.
The first step I do is reading it: Tbl = readtable(filename, 'ReadVariableNames', false);
I want to calculate:
1) the time between when the ticket was acknowledged and the creation time
2) the time between when the ticket is asked to be closed and when it is actually closed.
A ticket is acknowledged in different ways, but it always says "your ticket".
A ticket is asked to be closed in different ways, it says: "can this be closed?", ''can we close this?", "is this still an issue?" or "are you happy to close this?"
So, what I'm thinking is: searching the table for key phrases (like "your ticket"), and then reading the time of the corresponding cell. However, how can I do this without using a for loop to go through the columns?
Thanks
0 Kommentare
Antworten (2)
Stephen23
am 12 Dez. 2023
Bearbeitet: Stephen23
am 12 Dez. 2023
"I still have the same issue about searching into all the Comment columns."
The MATLAB documentation explains that you can use PATTERN objects to specify the variables/columns:
tbl = readtable('test.xlsx');
pat = "Comment" + wildcardPattern;
tbl = convertvars(tbl,pat,'string')
idx = contains(tbl{:,pat},"your ticket")
vec = ["can this be closed?","can we close this?","is this still an issue?","are you happy to close this?"];
idy = contains(tbl{:,pat},vec)
Then you can use ANY, FIND, etc. as required to obtain the columns or rows that you require from the table. Note that not all rows have both start and end text.
3 Kommentare
Stephen23
am 13 Dez. 2023
Bearbeitet: Stephen23
am 13 Dez. 2023
"Unfortunately, it seems I can't use pattern with R2018a (it seems it started with 2020b)."
Obtain the column/variable names, use text tools to select the ones you want, then use the names you selected, e.g.:
tbl = readtable('test.xlsx');
pat = tbl.Properties.VariableNames; % changed this line
pat = pat(startsWith(pat,'Comment')); % changed this line
tbl = convertvars(tbl,pat,'string')
out = rowfun(@myfun, tbl, 'NumOutputs',2, 'OutputVariableNames',["acknowledge","askclose"])
out.acknowledge - tbl.Start
tbl.End - out.askclose
function [start,close] = myfun(varargin)
X = cellfun(@isstring,varargin);
S = [varargin{X}];
Y = find(contains(S,"your ticket",'IgnoreCase',true),1,'first');
Z = find(strlength(S)>0,1,'last');
Ty = split(S(Y),';');
Tz = split(S(Z),';');
start = datetime(Ty(1), 'Format','d/MMM/yy HH:mm:ss');
close = datetime(Tz(1), 'Format','d/MMM/yy HH:mm:ss');
end
Siehe auch
Kategorien
Mehr zu Text Files finden Sie in Help Center und File Exchange
Community Treasure Hunt
Find the treasures in MATLAB Central and discover how the community can help you!
Start Hunting!