Task Tracking Using Python and ArcGIS

Managing a team of Geospatial Analysts, and all their ongoing projects, requires a decent bit of effort for the Project Manager on one of our current contracts. At the onset, the PM would ask each GA what they were working on, obtain it verbally or via email, and manually enter the information into an excel spreadsheet. He would then discuss the spreadsheet with the client each week, noting any task updates or closures. The importance of the spreadsheet and the weekly client meetings cannot be understated; however, I believed that the amount of labor associated with tracking tasks could be greatly reduced.

I originally got the idea for semi-automating our task-tracking system during a seminar at the ESRI International User’s Conference in San Diego. A presenter in one of the technical sessions showed how he and his team built a project tracking system using python code and an open source GUI. I took some notes, and knew that any similar workflow I created would have to be built off IDLE, because downloading any new programs to my government machine often proves to be prohibitively difficult.

To test the code’s functionality, I decided to create a python script in IDLE, as it was easy and familiar to me. I eventually used an ArcGIS tool paired with the script as my GUI, which worked well to recreate what each Analyst was familiar with using.

In order to manipulate the task tracking spreadsheet, I had to download a few non-native python modules (xlrd, xlwt, and xlutils), as they were not available in the core-install on my machine. I used a combination of functions in these modules in order to open, read, write, and save information into the excel document.

Python Script

Here is most of the code associated with manipulating the excel spreadsheet and adding new information to it. As far as I know, I have to recreate the file’s formatting from scratch every time I want to edit it because every time it is opened and saved, it loses all of its previous formatting. What the script essentially does is: opens the excel document (on shared space so each GA can access it), finds which sheet to write the new information to (the first sheet), finds the next available open row, and writes the information obtained by the user in each column of that row, and resaves the spreadsheet.

from xlrd import open_workbook
import xlwt
from xlutils.copy import copy
import arcpy
import subprocess
def main():
 
    #SET STYLE FOR EACH ROW/COLUMN THAT IS ADDED TO SPREADSHEET
    style = xlwt.easyxf(
        "font: name Calibri, color-index black, bold off;";
        "borders:left thin, right thin, top thin, bottom thin;",
            num_format_str="#,##0")
 
    #OPEN WORKBOOK USING XLRD
    book = open_workbook("D:\Temp\TaskTracker.xls")
 
    #IDENTIFY WHICH SHEET TO MANIPULATE (FIRST SHEET)
    sh = book.sheet_by_index(0)
 
    #IDENTIFY THE ROW TO WRITE THE NEW RECORD (THE FIRST EMPTY ROW AT THE BOTTOM, AFTER THE LAST RECORD)
    row_to_write = sh.nrows 
 
    #REOPEN WORKBOOK USING XLRD AGAIN (NEEDED TO DO TWICE IN ORDER TO GET AN ACCURATE ROW_TO_WRITE VALUE ABOVE)
    book = open_workbook("D:\Temp\TaskTracker.xls",formatting_info = True)
 
    #USE XLUTILS LIBRARY TO COPY THE BOOK
    wb = copy(book)
    sheet = wb.get_sheet(0)
 
    #OBTAINS THE CELL VALUE IMMEDIATELY PRECEDING THE CELL TO WRITE THE CORRECT NEXT RECORD (EX IF THE LAST RECORD IS 77, THE NEXT ONE WILL BE 78)
    value = sh.cell(row_to_write - 1,0).value
 
    #GET VARIABLES FROM THE USER
    title = arcpy.GetParameterAsText(0)
    request_date = arcpy.GetParameterAsText(1)
    due = arcpy.GetParameterAsText(2)
    requester = arcpy.GetParameterAsText(3)
    region = arcpy.GetParameterAsText(4)
    ga = arcpy.GetParameterAsText(5)
    rfi = arcpy.GetParameterAsText(6)
    map_size = arcpy.GetParameterAsText(7)
    number_of_maps = arcpy.GetParameterAsText(8)
    hard_soft = arcpy.GetParameterAsText(9)
 
    #WRITE VARIABLES TO THE WORKSHEET
    sheet.write(row_to_write, 0, value+1, style)
    sheet.write(row_to_write, 1, title, style)
    sheet.write(row_to_write, 2, request_date, style)
    sheet.write(row_to_write, 3, due, style)
    sheet.write(row_to_write, 4, requester, style)
    sheet.write(row_to_write, 5, region, style)
    sheet.write(row_to_write, 6, ga, style)
    sheet.write(row_to_write, 7, rfi, style)
    sheet.write(row_to_write, 8, map_size, style)
    sheet.write(row_to_write, 9, number_of_maps, style)
    sheet.write(row_to_write, 10, hard_soft, style)
 
    #RESAVE THE WORKBOOK
    wb.save("D:\Temp\TaskTracker.xls";)

Immediately following the spreadsheet’s update, a text file is created or overwritten on disk that contains the same information, and is attached to an automatically populated email, to be sent to the PM, and Deputy PM, making them aware that a new task was added to the tracker.

The pop-up email’s code is shown here:

#EMAIL GENERATION
    outlookpath2doc = 'C:/Program Files (x86)\Microsoft Office\Office14\OUTLOOK.EXE';
    compose = '/c ipm.note';
    recipients = '/m'; + 'ProjectManager@example.com; deputyPM@example.com&subject=New project added to the tracker &body=PM/Deputy PM,\n\nAttached is the RFI/relevant information associated with my latest project.\n\nRespectfully,\n\n' + 'ga+';
    attachment = '/a' 'D:\\RFIfile.txt';
    command = ''.join([outlookpath2doc, compose, recipients, attachment])
    process = subprocess.Popen(command, shell=False, stdout=subprocess.PIPE)

ArcGIS Tool

As shown in the python script, the variables that are used in code are set by “GetParameterAsText” from the ArcGIS Tool. Essentially, anything entered in the tool will be passed to the respective variable in the script and ultimately end up in the new task tracker record. In order to minimize user-error and typos, I set the parameters to accept only specific data types (RFI = int, Number of Maps = int, Request Date = date, Due Date = date). I also utilized “value lists” for the Region, Tasked GA, Map Size, and Hard or Soft Copy parameters, so that only values from the specified list can be used (like a word bank).

Reasoning for Effort

The main driver behind this project was to alleviate some of the Project Manager/Deputy PM’s work associated with manual task-tracking. The tool has become part of the Geospatial Analyst’s workflow and increased the accuracy/timeliness of task reporting, and has led to a more homogeneous tracker spreadsheet. This in-turn has helped analyze which GAs are tasked the most, and adds fuel to the conversation of future resource and personnel allocations in support of our client.

The full source code for the script discussed in this post can be found at https://github.com/Zekiah/scripts/blob/master/rfi_tracker.py

This post was written by:
Christian
Associate