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