Information Retrieval with Work Orders Data
Since R2023b
This example shows how to use information retrieval techniques to find solutions for new work orders based on past actions taken and descriptions from work orders. It shows how you can leverage the text descriptions of past incidents and the actions taken to suggest possible solutions for new problems.
Load Data
Download the Traffic Signal Work Orders dataset [1].
dataFolder = fullfile(tempdir,"traffic_signal_work_orders"); if datasetExists(dataFolder) zipFile = matlab.internal.examples.downloadSupportFile("textanalytics","data/Traffic_Signal_Work_Orders.zip"); unzip(zipFile,"data"); end
Load the dataset from the CSV file.
filename = fullfile(dataFolder,"Traffic_Signal_Work_Orders.csv"); data = readtable(filename, ... VariableNamingRule="preserve", ... TextType="string");
View the first few rows of the data.
head(data)
Work Order ID Status Asset Type Asset ID Location ID Created Date Modified Date Submitted Date Closed Date Fiscal Year Work Type Work Needed Work Type Other Work Requested By Job Description Problem Found Action Taken Follow-Up Needed Child Work Order Parent Work Order Is Follow-Up TMC Issue ID Service Request # Damage Report Location Name Latitude Longitude Location
______________ ________ ________________ ________ ______________ ______________________________ ______________________________ ______________________________ ______________________________ ___________ ________________ _______________________________________________________________ _____________________________________________________________________ _____________________________ _____________________________________________________________________________ ________________________________________________________________________________________________________________________________ __________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________ ________________ ________________ _________________ ____________ ______________ _________________ _____________ ______________________________________________________________ ________ _________ ______________________________
"WRK17-001685" "Closed" "School Flasher" NaN <missing> "08/19/2017 08:55:00 PM +0000" "09/14/2017 06:27:00 PM +0000" "08/19/2017 09:00:00 PM +0000" "09/14/2017 06:27:00 PM +0000" 2017 "Scheduled Work" "Call-Back (Test Monitors and Cabinets)" <missing> "Austin Transportation Staff" "HAVE AUSTIN ENERGY TIE IN NEW SOURCE DROP OVERHEAD @ CIMA SERENA WB FLASHER" "N/A." "AUSTIN ENERGY TECHNICIANS DISPATCHED TO LOCATION. AE TECHS COULD NOT DO WORK BECAUSE OF LACK OF METER ON POLE/SOURCE. AE TECHS SAID TO CONTACT "WORK MANAGMENT NORTH" 5125057179 FOR FURTHER ACTION. INFORMATION WILL BE RELAYED TO SUPERVISOR. " "False" <missing> <missing> <missing> <missing> <missing> <missing> <missing> NaN NaN <missing>
"WRK17-001865" "Closed" "Signal" 317 "LOC16-001550" "08/24/2017 03:28:00 PM +0000" "09/14/2017 06:42:00 PM +0000" "08/24/2017 03:56:00 PM +0000" "09/14/2017 06:42:00 PM +0000" 2017 "Scheduled Work" "Installation - Other" <missing> "Austin Transportation Staff" <missing> "bad cable for nb in the conduits" "pulled in 20 conductor cable for nb signals and peds . installed a new 332 cabinet , respliced all signals and peds for 2 way project ." "False" <missing> <missing> <missing> <missing> <missing> <missing> "5TH ST / TRINITY ST" NaN NaN "POINT (-97.739677 30.266132)"
"WRK17-001875" "Closed" "Signal" 319 "LOC16-001560" "08/24/2017 03:45:00 PM +0000" "09/14/2017 06:54:00 PM +0000" "08/24/2017 04:03:00 PM +0000" "09/14/2017 06:54:00 PM +0000" 2017 "Scheduled Work" "Installation - Other" <missing> "MMC" "install wb standard and splice in signals and peds" <missing> "install wb mast arm, remove street light pole, splice signal cables and peds" "False" <missing> <missing> <missing> <missing> <missing> <missing> "5TH ST / RED RIVER ST" NaN NaN "POINT (-97.737488 30.265535)"
"WRK17-001890" "Closed" "School Flasher" NaN <missing> "08/24/2017 08:23:00 PM +0000" "08/24/2017 08:31:00 PM +0000" "08/24/2017 08:31:00 PM +0000" "08/28/2017 03:08:00 PM +0000" 2017 "Trouble Call" "OtherDay-Call (Deliver Timing sheets to intersections and PM)" "SOMMERS ELEMENTARY - NOT FLASHING↵↵SR #17-00242843↵#17-00244051↵" "Austin Transportation Staff" "SOMMERS ELEMENTARY - NOT FLASHING↵SR #17-00242843, #17-00244051" "NO PROBLEMS FOUND AT SCHOOL FLASHERS. BOTH PEDESTRIAN FLASHERS NEED SCHEDULE." "BOTH SCHOOL CLOCKS CHECKED FOR TIME, DATE, SCHEDULE, FLASHERS OPERATION AND COMMUNICATION.↵BOTH PEDESTRIAN FLASHER CLOCKS CHECKED FOR TIME, DATE, SCHEDULE, OPERATION, AND COMM.↵TIME, DATE AND SCHEDULE UPDATED IN PEDESTRIAN FLASHER CLOCKS." "False" <missing> <missing> <missing> <missing> <missing> <missing> <missing> NaN NaN <missing>
"WRK17-003185" "Closed" "Signal" 25 "LOC16-000120" "10/09/2017 07:46:00 PM +0000" "01/23/2023 04:47:00 PM +0000" "10/09/2017 07:49:00 PM +0000" "10/10/2017 04:45:00 PM +0000" 2018 "Scheduled Work" "Installation - Camera" <missing> "MMC" "replace the avidia cctv with a pelco repaired unit" <missing> "replaced the avidia cctv with a repaired pelco task # 2423015000" "False" <missing> <missing> <missing> <missing> <missing> <missing> "MARTIN LUTHER KING JR BLVD / CONGRESS AVE (MLK/Capitol Mall)" NaN NaN "POINT (-97.738106 30.280687)"
"WRK17-003430" "Closed" "Signal" 185 "LOC16-000915" "10/18/2017 08:43:00 PM +0000" "10/26/2017 07:30:00 PM +0000" "10/18/2017 08:49:00 PM +0000" "10/26/2017 07:30:00 PM +0000" 2018 "Trouble Call" "Visibility Issue" <missing> "MMC" "Tree limbs blocking WB signal direction." "Tree limbs blocking WB signal direction." "Cut limbs blocking WB signal direction to make visible for ongoing traffic." "True" <missing> <missing> <missing> "TMC17-006530" "17-00311041" <missing> "LAMAR BLVD / PANTHER TRL" NaN NaN "POINT (-97.789284 30.23867)"
"WRK17-001895" "Closed" "Signal" NaN <missing> "08/24/2017 08:32:00 PM +0000" "08/24/2017 08:40:00 PM +0000" "08/24/2017 08:40:00 PM +0000" "08/28/2017 03:06:00 PM +0000" 2017 "Trouble Call" "OtherDay-Call (Deliver Timing sheets to intersections and PM)" "DOSS/MURCHISON COMBO WB NOT FLASHING" "Austin Transportation Staff" "DOSS/MURCHISON COMBO WB NOT FLASHING" "WB FLASHER ON GREYSTONE DOES NOT HAVE COMMUNICATION. CLOCK HAD NO SCHEDULE. EB FLASHER ON N HILLS DR. HAS LIMBS OBSTRUCTION." "DATE, TIME, SCHEDULE, AND FLASHER OPERATION CHECKED FOR ALL CLCOKS. WB CLOCK ON GREYSTONE PROGRAMMED WITH 2017/2018 SCHEDULE. LIMBS REMOVED FROM EB FLASHER ON N HILLS DR." "False" <missing> <missing> <missing> <missing> <missing> <missing> <missing> NaN NaN <missing>
"WRK17-002010" "Closed" "Signal" 779 "LOC16-003835" "08/29/2017 07:58:00 PM +0000" "09/14/2017 07:04:00 PM +0000" "08/30/2017 11:02:00 AM +0000" "09/14/2017 07:04:00 PM +0000" 2017 "Trouble Call" "Detection Failure" <missing> "MMC" "fisheye camera turned" "gridsmart camera out of alignment" "with assistance from the TMC - realigned camera and tightened" "False" <missing> <missing> <missing> <missing> <missing> <missing> "MC KINNEY FALLS PKWY / WILLIAM CANNON DR" NaN NaN "POINT (-97.72583 30.163218)"
Filter Data
For this example, filter the data to include only work orders associated with the "School Beacon" asset type and "Trouble Call" work type. Restrict the columns to "Job Description", "Problem Found", and "Action Taken", which are the ones used in this example.
idxSB = data.("Asset Type") == "School Beacon"; idxTC = data.("Work Type") == "Trouble Call"; data = data(idxSB & idxTC,:); data = data(:,["Job Description" "Problem Found" "Action Taken"]);
View the first few rows of the filtered data.
head(data)
Job Description Problem Found Action Taken
____________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________ ________________________________ ______________________________________________________________________________________________________________
" The director of AISD Transportation Kris received an email from the principal at Becker ES regarding there SZF. They said that Becker’s lights on S. 5th and, I think on Annie, are not flashing on Thursdays or Fridays, but are flashing on Saturday and Sunday. " "NONE." "ALL CLOCKS CHECKED. ALL CLOCKS HAVE SOLID LINK. CLOCK ON ANNIE SCHEDULE CHECKED, NO ISSUES FOUND."
"700 blk of montopolis nb needs a new flasher foundation can mount the flasher and sign to the wood pole temporally until the foundation gets built." <missing> <missing>
<missing> "No Comm" "Checked school schedule and all 3 beacons operation."
"not flashing" "none" "had station 20 turn on WBCU's, checked operation. "
<missing> "School flasher turned" "Turn school flasher straight."
"School zone light pole knocked down overnight" "School zone flasher knockdown." "Rebuilt school zone flasher. Use all original except 2 4" poles. checked operation."
"School zone light pole knocked down overnight" "School zone Flasher knockdown" "SEE OTHER CSR"
<missing> "no issue found" "checked both SZ flashers on Harris Glen, both had correct schedules and time, both LEDs operated in flashers"
Prepare Data for Analysis
Rename the columns for easier access.
data.Properties.VariableNames = ["JobDescription" "ProblemFound" "ActionTaken"];
Remove rows with missing data in any of the columns.
idxEmptyAT = ismissing(data.ActionTaken); idxEmptyJD = ismissing(data.JobDescription); idxEmptyPF = ismissing(data.ProblemFound); data(idxEmptyAT | idxEmptyJD | idxEmptyPF,:) = [];
Create a new variable "Description" combining the "JobDescription" and "ProblemFound" variables. This step is performed to include more context for the retrieval.
data.Description = data.JobDescription + ". " + data.ProblemFound;Preprocess Text Data
Preprocess the Description and ActionTaken variables to remove stop words, convert to lowercase, and erase punctuation.
descriptions = preprocessText(data.Description); actions = preprocessText(data.ActionTaken);
Visualize Data
Display word clouds for the Description and ActionTaken variables.
figure tiledlayout(2,1); nexttile wordcloud(descriptions); title("Description"); nexttile wordcloud(actions); title("Action Taken");
Evaluate Similarity Between Descriptions and Actions
Calculate the similarity between preprocessed descriptions and actions using the bm25Similarity function.
similarities = bm25Similarity(actions,descriptions); positionsFound = []; for i = 1:size(descriptions,1) [~,idxActions] = sort(similarities(:,i),"descend"); found = idxActions' == i; positionsFound = [found; positionsFound]; end
Calculate the mean reciprocal rank (MRR) of the positions where the original actions were found in the similarity ranking for each problem description. Higher values for MRR means that relevant results are being ranked higher during retrieval.
meanReciprocalRank(positionsFound)
ans = 0.3045
Retrieve Actions for New Problem
Given a new problem, suggest possible actions based on the highest similarity scores.
newProblem = "One flasher is blinking and the other side is not."; newProblem = preprocessText(newProblem); similaritiesNewProblem = bm25Similarity(actions,newProblem); [~,idxActions] = sort(similaritiesNewProblem,"descend"); data.ActionTaken(idxActions(1:5))
ans = 5×1 string
"replaced AC cube flasher, replaced amber LED, checked operation and also checked operation for Berkman side as well"
"Cut several branches to have better visual of school zone sign. Clear view of sign on top of hill coming from SB side."
"Installed RF relay at the cabinet. Delivered two RF remotes to fire station 30. Checked operation. Relocated flasher to the back side of the mast arm for verification. Relocated the optical sensor to the back of mast arm, Rewired the cabinet. RF signal is too weak. Please see follow-up. Signal is intermittence."
"Called supervisor to confirm it was ok to adjust the span. Called in OCP for traffic control and add another set of eyes for vehicle and tool security. Drilled a hole for another bolt 32 inches above the existing span (East side of street). Removed the span from the old preform. Attached the span to a new preform on the elevated bolt. Tied down cable to the span using tape. The lowest point of the lower flasher is now above 18 feet from the ground."
"Updated TOD for EB flasher and corrected scheduling issue for WB flasher, WB flasher didn't have a schedule for 6/1."
Preprocessing Function
The preprocessText function tokenizes the text, removes stop words, converts text to lowercase, and erases punctuation.
function documents = preprocessText(textData) % Tokenize the text. documents = tokenizedDocument(textData); % Remove stop words documents = removeStopWords(documents); % Lower case the text documents = lower(documents); % Erase punctuation. documents = erasePunctuation(documents); end
Reciprocal Rank Function
The reciprocalRank function calculates the reciprocal rank for a given ranked list.
function rr = reciprocalRank(rankedList) % Find the index of the first relevant item (1 in rankedList) firstRelevant = find(rankedList == 1, 1); % Check if a relevant item was found if isempty(firstRelevant) rr = 0; else rr = 1 / firstRelevant; end end
Mean Reciprocal Rank Function
The meanReciprocalRank function calculates the mean reciprocal rank for multiple ranked lists.
function mrr = meanReciprocalRank(rankedLists) sumRr = 0; numQueries = size(rankedLists, 1); for i = 1:numQueries sumRr = sumRr + reciprocalRank(rankedLists(i, :)); end % Calculate the mean reciprocal rank mrr = sumRr / numQueries; end
Bibliography
Traffic Signal Work Orders. City of Austin Open Data. Retrieved April 30, 2023, from https://data.austintexas.gov/Transportation-and-Mobility/Traffic-Signal-Work-Orders/hst3-hxcz
See Also
tokenizedDocument | bm25Similarity
Related Topics
You can also select a web site from the following list:
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)