1

enter image description here I am not very savvy at using Python. Actually I have around 1000 output files from using the tool zonal statistics as table in ArcGIS. The names of the files are jan_60, feb_60......dec_13. These all lie in one folder. Each of these files contain an ID and Name field which are my identifiers. And each file has got a column of interest called Mean. So what I want is that all the files be merged, using ID and name field. In the final table, I want one column added having the mean values from each of the individual tables. Note that I want the name of the new columns as the original file name. I am trying to give you a visualization below:

original files I have:

ID Name Mean

output file needed:

ID Name jan_60 feb_60 march_60

Can someone help me out, if any of you have had a similar task. It would be great if I can have a script or something. Thanks a lot.

output file needed output file needed folder containing individual files

folder containing individual files one of the individual

one of the individual

  • Do you want to merge everything in the folder, or just ones that share the same ID? – GISKid Jul 15 '14 at 17:29
  • Actually in each file, the id's are the sme, ranging from 1 to 50, say. And i want to join all the tables with respect to this id. – Meetpal Jul 15 '14 at 17:40
  • Your question isn't really clear enough, could you please post some screenshots of your folder structure or what you want done? If you want to rename your entire folder structure (not just GIS file names) than you might want to post this to overflow. – GISKid Jul 15 '14 at 18:37
  • I added the screenshots of 1. output file needed, 2. folder containing individual files, 3. one of the individual files – Meetpal Jul 15 '14 at 18:59
2

So the file names would then be fields in the output table? Seems like you would need to add a field to each table (let's call it "FILE_NAME"), merge all the tables together, then use the ArcGIS PivotTable tool (pivot on the FILE_NAME field). If you have 1000 tables, would you then have 1000+ fields? Not sure if this would be the best table structure...

Code to add file names to tables would look something like this (untested) code:

arcpy.env.workspace = r"C:\my_dir"
tableList = arcpy.ListTables()
for table in tableList:
    arcpy.AddField_managment(table, "FILE_NAME", "TEXT", "", "", "100")
    arcpy.CalculateField_managment(table, "FILE_NAME", "'" + table + "'", "PYTHON")
  • So, how do i add teh file name field in a 1000 files at once. I can't to do this manually. And once i have a field added, how can i also fill the file name into all the files? – Meetpal Jul 15 '14 at 18:34
1

Cursors can read data from the DBFs and then write that data to a "master" table that holds all the information.

Note: This is going to end up making an extremely large table or feature if you have 1000 fields. I'd also suggest testing it with a much smaller subset (e.g., two) of DBF files first, to make sure the script is working correctly for your data.

import arcpy

arcpy.env.workspace = r'C:/temp/dbf' # rename this to your dbf directory
dbfList = arcpy.ListTables()

masterTable = r'C:/temp/Backup.gdb/TmpPts' # rename this to your "master" table or feature

for dbf in dbfList:
    dbfFile = arcpy.env.workspace + '/' + dbf
    dbfName = dbf[:-4]
    # create field in master table that matches DBF file name
    arcpy.AddField_management(masterTable, dbfName, 'FLOAT')

    dbfData = {}

    # read in data from DBF and create dictionary
    with arcpy.da.SearchCursor(dbfFile, ['ZONE_CODE','MEAN']) as cursor:
        for row in cursor:
            dbfData[row[0]] = row[1]
    del cursor

    # write data from dictionary to master table
    with arcpy.da.UpdateCursor(masterTable, ['ZONE_CODE',dbfName]) as cursor:
        for row in cursor:
            row[1] = dbfData[row[0]]
            cursor.updateRow(row)
    del cursor

(Side Note: The da.SearchCursor and da.UpdateCursor are for Arc 10.1+.)

Update: This code assumes that you start with a shapefile of polygons of counties, with attributes COUNTYNAME and ZONE_CODE.

  • If you have such a thing, great, use that as the masterTable variable, and the additional dbf-derived attribute fields will be added.
  • If not, then make a table with two fields (COUNTYNAME and ZONE_CODE), and fill them in with the appropriate county names and zone codes. One simple method of doing this would be take any of the zonal stats tables (e.g. jan_60.dbf), delete the various statistics columns, and save it as a new file (e.g. alldata.dbf). This new file then is used as the masterTable variable.
  • Hey Erica, thanks a lot for the code, but its giving an error. Can you please help me debug it please. The error is: Traceback (most recent call last): File "E:\Research\Weather data\utah climate centre\iter\zonalscript.py", line 24, in <module> for row in cursor: RuntimeError: A column was specified that does not exist. Failed to execute (Script). Failed at Sat Jul 19 11:30:43 2014 (Elapsed Time: 0.41 seconds) – Meetpal Jul 19 '14 at 17:23
  • Hey Erica the code ran but it gave an output of just column names added into the table but not the contents of those columns. The mean values and the zone codes are not written into the table. I am attaching a screenshot of the table output above in the post. You can have a look on it – Meetpal Jul 19 '14 at 18:26
  • @Meetpal -- OK, I see the problem. My code assumed that you would start with a shapefile of polygons of counties, with attributes COUNTYNAME and ZONE_CODE. I will update my Answer to clarify that, and propose a couple workarounds :) – Erica Jul 19 '14 at 19:46
  • Thanks for the response, I went with the 2nd workaround and am getting the following error:Traceback (most recent call last): File "E:\Research\Weather data\utah climate centre\iter\zonalscript.py", line 25, in <module> row[1] = dbfData[row[0] - 1] KeyError: 0.0 Failed to execute (Script). Failed at Sat Jul 19 16:19:36 2014 (Elapsed Time: 0.31 seconds) – Meetpal Jul 19 '14 at 22:00
  • 1
    @Erica, del cursor is unnecessary when using with statements. with statements handle file opening and closing in the event of a premature exit. Also, os.path.splitext() is your friend. :) +1 – Paul Jul 21 '14 at 18:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.