topic Re: Max Date - How to return unique IDs with Max Date in Python Questions

List Broken Data Source's Path

LauraMiles1 — Wed, 07 Aug 2013 16:49:25 GMT

Hi everyone, I'm a very amateur Python user. I've managed to get the following, which I pieced together from here and there, working for the most part. I need to create a csv file which has a column showing the path to the data source which is broken. Everything works fine and dandy if I take out the dataSource part; when I add it in, it will run for a while and then fail. It seems to be getting tripped up on some mxd or data source it doesn't like perhaps? I have no clue. Here's my code:

import arcpy, os
path = r"H:\Plans\GIS Plans\2003"
f = open('BrokenMXD2003.csv', 'w')
f.write("Type, File Path, Layer, Broken Path" + "\n")
for root, dirs, files in os.walk(path):
    for fileName in files:
        basename, extension = os.path.splitext(fileName)
        if extension == ".mxd":
            fullPath = os.path.join(root, fileName)
            mxd = arcpy.mapping.MapDocument(fullPath)
            brknMXD = arcpy.mapping.ListBrokenDataSources(mxd)
            for brknItem in brknMXD:
                lyrList = arcpy.mapping.ListLayers(mxd)
                f.write("MXD, " + fullPath + ", " + brknItem.name)
                if brknItem.supports("dataSource"):
                    f.write(", " + brknItem.dataSource + "\n")
                else:
                    f.write("\n")

f.close()

print "Script Completed"

And here's the error I get:

Traceback (most recent call last):
File "X:\Documents\Working Files\Broken Data Sources\ListBrokenMXD.py", line 11, in <module>
    brknMXD = arcpy.mapping.ListBrokenDataSources(mxd)
File "D:\Program Files (x86)\ArcGIS\Desktop10.2\arcpy\arcpy\utils.py", line 181, in fn_
    return fn(*args, **kw)
File "D:\Program Files (x86)\ArcGIS\Desktop10.2\arcpy\arcpy\mapping.py", line 1465, in ListBrokenDataSources
    result = mixins.MapDocumentMixin(map_document_or_layer).listBrokenDataSources()
File "D:\Program Files (x86)\ArcGIS\Desktop10.2\arcpy\arcpy\arcobjects\mixins.py", line 832, in listBrokenDataSources
    broken_sources = [l for l in self.layers if not l._arc_object.valid]
File "D:\Program Files (x86)\ArcGIS\Desktop10.2\arcpy\arcpy\arcobjects\mixins.py", line 683, in layers
    for frame in reversed(self.dataFrames):
File "D:\Program Files (x86)\ArcGIS\Desktop10.2\arcpy\arcpy\arcobjects\mixins.py", line 695, in dataFrames
    return map(convertArcObjectToPythonObject, self.pageLayout.dataFrames)
AttributeError: 'NoneType' object has no attribute 'dataFrames'

It does run and create several lines of output, but this error appears when it gets near to the end of the mxd's in the directory. I haven't a clue what the problem could be, as I said I'm quite amateur. If anyone can see what it is, I'd be greatly appreciative. Thank you!

Re: List Broken Data Source's Path

LaurenYee — Fri, 10 Dec 2021 21:13:26 GMT

Instead of populating an excel spreadsheet this will show the results in IDLE:

import arcpy, os

path = r"C:\Test"

for root, dirs, files in os.walk(path):
    for fileName in files:
        basename, extension = os.path.splitext(fileName)
        if extension == ".mxd":
            fullPath = os.path.join(root, fileName)
            mxd = arcpy.mapping.MapDocument(fullPath)
            brknMXD = arcpy.mapping.ListBrokenDataSources(mxd)
            for brknItem in brknMXD:
                print "MXD: " +fileName
                if brknItem.supports("workspacePath"):
                        source = brknItem.workspacePath
                        print  str(brknItem) + ": " +  source
                else:
                    print "Layer does not support source"
             
print "Completed"

Re: List Broken Data Source's Path

StacyRendall1 — Fri, 10 Dec 2021 21:13:28 GMT

File "X:\Documents\Working Files\Broken Data Sources\ListBrokenMXD.py", line 11, in <module>
brknMXD = arcpy.mapping.ListBrokenDataSources(mxd)
File "D:\Program Files (x86)\ArcGIS\Desktop10.2\arcpy\arcpy\utils.py", line 181, in fn_
return fn(*args, **kw)
File "D:\Program Files (x86)\ArcGIS\Desktop10.2\arcpy\arcpy\mapping.py", line 1465, in ListBrokenDataSources
result = mixins.MapDocumentMixin(map_document_or_layer).listBrokenDataSources()
File "D:\Program Files (x86)\ArcGIS\Desktop10.2\arcpy\arcpy\arcobjects\mixins.py", line 832, in listBrokenDataSources
broken_sources = [l for l in self.layers if not l._arc_object.valid]
File "D:\Program Files (x86)\ArcGIS\Desktop10.2\arcpy\arcpy\arcobjects\mixins.py", line 683, in layers
for frame in reversed(self.dataFrames):
File "D:\Program Files (x86)\ArcGIS\Desktop10.2\arcpy\arcpy\arcobjects\mixins.py", line 695, in dataFrames
return map(convertArcObjectToPythonObject, self.pageLayout.dataFrames)
AttributeError: 'NoneType' object has no attribute 'dataFrames'

The traceback (error report) is indicating that the problem is within the Arcpy libraries, but is caused by line 11 of your script, brknMXD = arcpy.mapping.ListBrokenDataSources(mxd). I would guess that at some point you are passing an invalid mxd path...?

Try printing the mxd path each time (change is shown in red):

import arcpy, os
path = r"H:\Plans\GIS Plans\2003"
f = open('BrokenMXD2003.csv', 'w')
f.write("Type, File Path, Layer, Broken Path" + "\n")
for root, dirs, files in os.walk(path):
    for fileName in files:
        basename, extension = os.path.splitext(fileName)
        if extension == ".mxd":
            fullPath = os.path.join(root, fileName)
            mxd = arcpy.mapping.MapDocument(fullPath)
            arcpy.AddMessage(mxd)
            brknMXD = arcpy.mapping.ListBrokenDataSources(mxd)
            for brknItem in brknMXD:
                lyrList = arcpy.mapping.ListLayers(mxd)
                f.write("MXD, " + fullPath + ", " + brknItem.name)
                if brknItem.supports("dataSource"):
                    f.write(", " + brknItem.dataSource + "\n")
                else:
                    f.write("\n")

f.close()

print "Script Completed"

Re: List Broken Data Source's Path

LauraMiles1 — Wed, 07 Aug 2013 21:02:44 GMT

Hi Lauren, thanks for your answer. I'm actually successful at creating and populating the csv file, it just hangs up near the end of cycling through all the mxd's and doesn't finish the job. It puts out a csv file just fine, with the majority of the broken links listed, it just doesn't make it through every mxd in the folder and I'm not sure what the error I'm being given means.

I notice you used "workspacePath" rather than "dataSource". I tested out your code in IDLE and it seems to be providing the same information as dataSource. I changed dataSource to workspacePath in my code and tested that out. Both using your code as a straight copy and paste into IDLE, and changing dataSource to workspacePath in my own code, I'm getting the exact same error as I have been. Oddly, it does NOT stop at the same .mxd in both scenarios.

Any clues out there???

Re: List Broken Data Source's Path

LauraMiles1 — Wed, 07 Aug 2013 21:22:30 GMT

Hi Stacy, I added this one line but it seems to have had no effect. I'm not familiar with addMessage; should there have been a popup or something? You mentioned printing the mxd path but I don't see where the results of addMessage should be added to my csv file or printed in IDLE?

I've checked the .mxd's that aren't getting looped through; they open fine and seem to be "valid" so far as I can tell.

Re: List Broken Data Source's Path

StacyRendall1 — Fri, 10 Dec 2021 21:13:31 GMT

Hi Stacy, I added this one line but it seems to have had no effect. I'm not familiar with addMessage; should there have been a popup or something? You mentioned printing the mxd path but I don't see where the results of addMessage should be added to my csv file or printed in IDLE?

I've checked the .mxd's that aren't getting looped through; they open fine and seem to be "valid" so far as I can tell.

Hey. This is kind of a long answer; first part attempts answer your questions, second part provides some general advice about what I think you could do to work out the problem and (hopefully) fix it.

AddMessage is kind of like print, except it will also work if you are running your code as a script tool within ArcMap (it will print to the little progress window). In your case it should simply print to the console output in IDLE, just like print "Script Completed" does. If AddMessage is not working, you could try just making that line print mxd.

I didn't do a great job of describing what this will do, sorry. Having the print (or AddMessage) statement just before the crash will let you check which mxd is causing the crash. So your code will run, it will print each mxd that it analyses, then it will crash. The last mxd listed before the crash is the one potentially causing the problem. Then you can open it in ArcMap and do some other investigation on that particular file...

My guess is still that at some point this statement mxd = arcpy.mapping.MapDocument(fullPath) is returning something bad, and this is causing the next functional statement (arcpy.mapping.ListBrokenDataSources(mxd)) to fail.

Lauren's answer doesn't change anything from before the crash (apart from setting up the CSV), so it is interesting that it causes a crash on a different mxd. Does your original code always crash at the same place?

In a situation like this I would always recommend doing the simplest possible thing first, then isolate and fix any errors, then add more complexity once it works. For all we know the crash could be due to some strange conflict between Arc and the open file object (f), but to find this out we need to clearly build the code up step by step. So, perhaps just print to screen for now, get rid of everything related to the CSV and everything after your crash:

import arcpy, os
path = r"H:\Plans\GIS Plans\2003"
for root, dirs, files in os.walk(path):
    for fileName in files:
        basename, extension = os.path.splitext(fileName)
        if extension == ".mxd":
            fullPath = os.path.join(root, fileName)
            mxd = arcpy.mapping.MapDocument(fullPath)
            print mxd
            brknMXD = arcpy.mapping.ListBrokenDataSources(mxd)

print "Script Completed"

This should cause the crash, and the last printed mxd name should show the mxd causing the error. Once this is resolved, add the next layer of complexity, printing to the screen:

import arcpy, os
path = r"H:\Plans\GIS Plans\2003"
for root, dirs, files in os.walk(path):
    for fileName in files:
        basename, extension = os.path.splitext(fileName)
        if extension == ".mxd":
            fullPath = os.path.join(root, fileName)
            mxd = arcpy.mapping.MapDocument(fullPath)
            print mxd
            brknMXD = arcpy.mapping.ListBrokenDataSources(mxd)
            for brknItem in brknMXD:
                print brknItem.name

print "Script Completed"

You will want to make sure that you have some mxds in your test set that you have designed with broken data sources, to ensure your code works for those.

If this runs OK, add the dataSource or workspacePath check (I don't know what either of these does, sorry...), printing them out. If everything checks out OK printing to screen, finally add the CSV output.

I also noticed that this line in your original post doesn't do anything lyrList = arcpy.mapping.ListLayers(mxd); I would recommend removing it if unused, at best it will slow things down, but may cause other problems too.

Hope this helps!

Re: List Broken Data Source's Path

LaurenYee — Thu, 08 Aug 2013 12:21:47 GMT

Hi Lauren, thanks for your answer. I'm actually successful at creating and populating the csv file, it just hangs up near the end of cycling through all the mxd's and doesn't finish the job. It puts out a csv file just fine, with the majority of the broken links listed, it just doesn't make it through every mxd in the folder and I'm not sure what the error I'm being given means.

I notice you used "workspacePath" rather than "dataSource". I tested out your code in IDLE and it seems to be providing the same information as dataSource. I changed dataSource to workspacePath in my code and tested that out. Both using your code as a straight copy and paste into IDLE, and changing dataSource to workspacePath in my own code, I'm getting the exact same error as I have been. Oddly, it does NOT stop at the same .mxd in both scenarios.

Any clues out there???

Yes, in my code where I check datasources I've had more success using workspacePath... I wish I could tell you WHY that was, I'll try and find a resource. I think it may have been because most of the paths I needed to replace referenced SDE databases(?)

Anyway, I would follow the advice on this thread and start with a smaller script to cycle through your mxds and see which one is the culprit.

How many mxds are you cycling through? - also will you be replacing these datasources with something new?

Edit, I believe this is why I used workspacePath instead . If you were to replace one path for another, it is easier to use workspacePath!

Re: List Broken Data Source's Path

LauraMiles1 — Thu, 08 Aug 2013 13:57:03 GMT

Hi Stacy, thanks for your detailed response. Something very odd is happening indeed, because I noticed after I get the error message and the script seems to be just hanging there (tried waiting 1/2 an hour and saw no additions to my csv file), I close the script and after I've closed it there are several more .mxd's that appear in the csv file. I'm wondering if that's the reason it's not "stopping" on the same .mxd every time - it looks to me like it's crashed, but it's actually still running? I'm going to try running it overnight and see what's there in the morning.

I had the script running with no errors before adding in the dataSource or workspacePath. It's definitely something about that which is causing problems. I'll go through and simplify again, as per your suggestions, and see if I can narrow it down to any particular .mxd. The addMessage didn't print anything to the idle window. I also noticed I had that unused lyr line in there - forgot to remove that.

Some data types (ie. annotation feature classes) won't return a dataSource, and apparently an error will be thrown if you don't test them out first.

Lauren, thanks for the tip on workspacePath - I'll stick with this then. Eventually I want to repair the broken links, I'm trying to find out what/where they are first so I can figure out the paths to where the data has moved to. Our folder system was totally reorganized a few years back and I want to archive all these old .mxd's by creating map packages. Do you know if workspacePath is also better to use on layer files than dataSource? I have pretty much the same script which runs on all the layer files in a directory, not getting any errors with that one.

UPDATE: I managed to isolate an .mxd, and when I checked into it, there was no data in it. No layers, just an empty data frame. This doesn't explain why sometimes the script would go past this point, or stop several .mxd's before this point? One time it even ran through every .mxd in the directory, though it still showed the error. Anyway I've deleted the offending .mxd and will try and find a way to identify an .mxd with no layers in it. I'd like to print this to my csv file as well. Thanks all for your help!

Re: Max Date - How to return unique IDs with Max Date

RichardFairhurst — Fri, 10 Dec 2021 21:13:34 GMT

Python Beginner - Using version 10.1 and not an SDE install I have an atribute table with firehydrant inspection data. I am trying to create a layer that will only show the records grouped by fire hydrant number and then only display the most recent inspection date. When I execute the following code it runs fine and does in fact create the layer but it doesn't select the Max Date. If I have a record for Hydrant 100 with two dates I only want the most recent date to be created within the new layer.
Appreciate any assistance on this.
import arcpy
from arcpy import env
env.workspace=r"Z:\GIS_Data\gdb\GIS_Test.gdb"

fc="COA_FH_Inspections"
cursor = arcpy.UpdateCursor(fc)
idlist = []
datelist = []

for row in cursor:
    idlist.append(row.getValue('HydrID'))
    datelist.append(row.getValue('TestDate'))
    cursor.updateRow(row)

print idlist
print datelist

idunique = set(idlist)

#Make a layer from the feature class
arcpy.MakeFeatureLayer_management(fc, "lyr")

for i in idunique:
    if i == idlist:
        arcpy.SelectLayerByAttribute_management("lyr", "New_Selection", '"HydrID" = i')
        arcpy.SelectLayerByAttribute_management("lyr", "subset_Selection",'"TestDate" = (Select Max("TestDate") from "lyr"')
Again thank you - Terry

SDE and personal geodatabases supposedly support subqueries, but they are not well documented and not easy to troubleshoot. The Subquery has to return a single result and be in the format:

RelateField2 IN (SELECT RelateField1 FROM Table1 WHERE SQLWhereClause1)

I also do not think a subquery for a Max value can come from the same table that you are making a selection on (at least I know they fail for an fgdb, but those are much more limited than SDE subqueries, so that may not be a restriction for your SDE database)

To do this I use the Summary Statistics. Unfortunately dates do not allow you to perform summary functions in any ESRI tools. The work around steps I have had to use are listed here. The advantage of the Summary Statistics approach over the approach you are attempting is that it can summarize thousands of max dates for thousands of unique IDs in under 2 minutes and do the selection of every Max date for every unique ID in the entire tables in one selection operation. A Feature Class to Feature Class output of that selection can make a 1:1 joinable table for other summary or data transfer operations. You can make the summary outputs to in memory tables if you don't want them written to disk.

Processing each ID with individual queries can take up to 100 times longer to process, since that causes a new disk read from the start of the table for each new query.

Re: Max Date - How to return unique IDs with Max Date

TerryHiggins1 — Thu, 08 Aug 2013 16:04:20 GMT

Perhaps I did not express myself clearly - I do not have SDE. I am dealing with a file gdb. When I export the table to Access then I can easily extract what I want but within Arc and python I am totally lost.

Re: Max Date - How to return unique IDs with Max Date

RichardFairhurst — Thu, 08 Aug 2013 16:09:28 GMT

Perhaps I did not express myself clearly - I do not have SDE. I am dealing with a file gdb. When I export the table to Access then I can easily extract what I want but within Arc and python I am totally lost.

Your screwed. fgdb's support an extremely limited set of subqueries that are under the restrictions I mentioned and cannot do what you can do with personal geodatabases and SDE databases, which support full subquery functionality. Fgdb subqueries can only return one value from an entire table and the table providing the subquery value (MAX, SUM, MEAN, etc) must be external to the table where you are making the selection based on the subquery value. Nothing can make the approach your code is attempting work with an fgdb.

Re: Max Date - How to return unique IDs with Max Date

TerryHiggins1 — Thu, 08 Aug 2013 16:15:03 GMT

Thanks for the response - obviously that was not what I wanted to here.

Re: Max Date - How to return unique IDs with Max Date

JamesCrandall — Thu, 08 Aug 2013 16:39:15 GMT

I saw your post earlier and was attempting some possible approaches. I thought about using NumPy to arrive at the max() statistic you are interested in, and it looked promising.

arcpy.da.FeatureClassToNumPyArray

However, it doesn't allow for Date fields! arrggghhh!

Sorry.
j

Re: Max Date - How to return unique IDs with Max Date

RichardFairhurst — Thu, 08 Aug 2013 16:43:32 GMT

Thanks for the response - obviously that was not what I wanted to here.

I know. I have posted an Idea on the ideas page related to the poor handling and inconsistencies of date related summaries and operations in ArcMap here and requested that they improve the ability to get such information. Please vote for it and offer any other comments or suggestions on that idea.

Re: Max Date - How to return unique IDs with Max Date

JamesCrandall — Fri, 10 Dec 2021 21:13:36 GMT

Are you opposed or comfortable using a different library? You can EASILY acheive what you want with Pandas:


import arcpy
from pandas import *

ws = "H:\Documents\ArcGIS\Default.gdb"
arcpy.env.workspace = ws
fc = "MyFC"
fields = ["_currentvalue", "_pastvalue", "_curDate"]
array = []

for field in fields:
    with arcpy.da.SearchCursor(fc, fields) as cursor:
        for row in cursor:
            array.append(row)

df = DataFrame(array, columns = ["_currentvalue", "_pastvalue", "_curDate"])

maxDate = df._curDate.max()

print maxDate  #<--- this gives me the correct max date

I have found many other uses for the pandas library too -- very powerful and a welcomed addition to my codebase!

Good luck,
james

Re: Max Date - How to return unique IDs with Max Date

RichardFairhurst — Thu, 08 Aug 2013 17:00:03 GMT

Here is another post discussing the limitations of subqueries when using fgdb's. The Pandas approach is probably your best option within Python as a work around. I personally want better support in the basic geoprocessing tools for finding Min and Max dates.

Re: Max Date - How to return unique IDs with Max Date

MattSayler — Fri, 10 Dec 2021 21:13:39 GMT

How about using a dictionary

import arcpy
from arcpy import env
env.workspace=r"Z:\GIS_Data\gdb\GIS_Test.gdb"

fc="COA_FH_Inspections"
cursor = arcpy.SearchCursor(fc) #Update cursor is for updating the data in the fc's row, search cursor should suffice in this situation
idlist = []

for row in cursor:
    idlist.append(row.getValue('HydrID'))

print idlist

idunique = set(idlist)

###MY ADDITIONAL CODE###

#create dictionary with structure {<hydrantID>:(<objectid>,<Date>),...}
maxDates = {}
for item in idunique:
    maxDates[item] = (0,"1/1/1900")

features = arcpy.SearchCursor(fc)

for feature in features:
    hydrant = feature.getValue('HydrID')
    testDate = feature.getValue('TestDate')
    objectID = feature.getValue('objectid')
    
    if testDate > maxDates[hydrant][1]:
        maxDates[hydrant] = (objectID, testDate) #if the date is greater, replace the (<objectid>,<date>) tuple with new values

#maxDates can now be used to collect the features
#for illustration purposes:
keepers = []
for key in maxDates:
    hydrant = key
    objectID = key[0]
    print "Hydrant {0} with ObjectID {1} has the most recent inspection date".format(hydrant,objectID)
    keepers.append(objectID)

#Use keepers to identify the rows in the feature class and write them out to a new feature class

Almost certainly will need additional tweaks, but that's the concept.

Re: Max Date - How to return unique IDs with Max Date

ChrisSnyder — Fri, 10 Dec 2021 21:13:42 GMT

This ***untested*** code should give you a list of the unique Ids (I assume you want to use the OBJECTID field), that represent the most recent test date for each occurance of HydrId. You could then load this list of OIDs (latestTestOidList in the code below) into a SQL query.

hydrantDict = {}
searchRows = arcpy.da.SearchCursor(hydrantTable, ["OID@","TestDate","HydrID"])
for searchRow in searchRows:
   oidValue, testDate, hydrantId = searchRow
   if hydrantId not in hydrantDict:
      hydrantDict[hydrantId] = [(testDate, oidValue)]
   else:
      hydrantDict[hydrantId].append((testDate, oidValue))
del searchRow, searchRows
latestTestOidList = [sorted(dict[hydrantId], reverse=True)[0][1] for hydrantId in hydrantDict]

Re: Max Date - How to return unique IDs with Max Date

DavidSousa — Thu, 08 Aug 2013 18:03:27 GMT

Your screwed. fgdb's support an extremely limited set of subqueries that are under the restrictions I mentioned and cannot do what you can do with personal geodatabases and SDE databases, which support full subquery functionality. Fgdb subqueries can only return one value from an entire table and the table providing the subquery value (MAX, SUM, MEAN, etc) must be external to the table where you are making the selection based on the subquery value. Nothing can make the approach your code is attempting work with an fgdb.

This is not correct. A scalar sub-query in FileGDB can reference the same table. I do this all the time, and it was written specifically for this purpose. For example, if my table is ROADS, I can execute the following sub-query in a WHERE clause:

OID = (SELECT MAX(OID) FROM ROADS)

Could you provide some specific examples of the things you have tried that do not work as you would like?

Re: Max Date - How to return unique IDs with Max Date

RichardFairhurst — Thu, 08 Aug 2013 18:25:32 GMT

This is not correct. A scalar sub-query in FileGDB can reference the same table. I do this all the time, and it was written specifically for this purpose. For example, if my table is ROADS, I can execute the following sub-query in a WHERE clause:

OID = (SELECT MAX(OID) FROM ROADS)

Could you provide some specific examples of the things you have tried that do not work as you would like?

David:

Thanks for correcting me and weighing in on this subject. Apparently my problems in attempting subqueries are either than my information is out of date (derived from tests mostly done at 9.3) or that this is related to the poor documentation that confuses syntax from many databases in the SQL Reference, which is what I have always tried to follow. In any case, I can confirm that after following David's examples he provided me in an e-mail that an fgdb can make subquery selections as David has said. Also fgdb subqueries can return Max(Date) values.

The subquery can operate within a selection involving a single table and they can select based on Max date.

So in reality it appears that the problem is that you cannot use "lyr" as the table name in the subquery. You must use the underlying Feature Class name. So for example, if my layer is named CENTERLINE but the underlying feature class of the layer is named CENTERLINE_EXTENDED, this subquery will not work:

"MODIFIED_DATE" = (SELECT MAX("MODIFIED_DATE") FROM CENTERLINE WHERE "STNAME" = 'HIGH VISTA DR')

but it will work if I put the actual feature class name. So this does work:

"MODIFIED_DATE" = (SELECT MAX("MODIFIED_DATE") FROM CENTERLINE_EXTENDED WHERE "STNAME" = 'HIGH VISTA DR')

So you do not need two queries, just one for your problem. However, you need to use the underlying name of the fc variable. So something like this should replace the two queries in the original post (untested and not sure if I have to access a property of the fc to get its name):

arcpy.SelectLayerByAttribute_management("lyr", "subset_Selection",'"HydrID" = ' + i + ' And "TestDate" = (Select Max("TestDate") from ' + fc + ' WHERE "HydrID" = ' + i)