I download data daily and create a geodatabase table.

Fields: Company, Customers_Active, Customers Total, County, State

Example Data

{CompanyA,CompanyB}, {141,424}, {1000,3421}, Cook County, Illinois
{CompanyA}, {145}, {1000}, Warren County, Illinois
{CompanyA,CompanyB,CompanyC}, {11,421, 30}, {100,900,60}, DuPage County, Illinois

I want to be able to "break up" the Company data into its own record if more than one company is listed. So I want the following. I don't want the brackets, but I can figure out how to remove them later.

CompanyA 141 1000 Cook County Illinois
CompanyB 424 3421 Cook County Illinois
CompanyA 145 1000 Warren County Illinois
CompanyA 11 100 DuPage County Illinois
CompanyB 421 900 DuPage County Illinois
CompanyC 30 60 DuPage County Illinois

with arcpy.da.SearchCursor("national_outage_map_merge",
                       ["Company", "Customers_Active", "Customers_Total", "County", "State"]) as cursor:
for row in cursor:
    if any(',' in code for code in row):
        print (row)

This code prints the records that have more than one company.

Do I also need to incorporate an InsertCursor?

Would it be easier to create a new record in the same table (and delete the original combined record) or just insert all records into a new table?


It would probably be easier to insert all records into a new table -- although I mostly prefer this approach for safety (no worrying about destroying all your original data accidentally), it also gives some logical separation and you don't need to wonder whether you've already processed Table 20140910B this evening, etc.

When you encounter a row with combined data, you can break it down into a list using split, then iterate through the resulting list and insert records with an InsertCursor.

insertcursor = arcpy.da.InsertCursor("newtable", ["Company", "Customers_Active", "Customers_Total", "County", "State"])
with arcpy.da.SearchCursor("national_outage_map_merge", ["Company", "Customers_Active", "Customers_Total", "County", "State"]) as searchcursor:
for row in searchcursor:
    county = row[3]
    state = row[4]
    listCo = row[0].split(',')
    listActive = row[1].split(',')
    listTotal = row[2].split(',')
    for n,Company in enumerate(listCo):
        insertcursor.insertRow((listCo[n], listActive[n], listTotal[n], county, state))
| improve this answer | |
  • Let me know if there's any typos or adjustments that need to be made and I'll update the Answer for future reference :) – Erica Sep 11 '14 at 17:12
  • I added a date field to the table. I added the date field directly after "State" in the Insert and Search Cursors and incorporated the correct row[5] code. I am getting "TypeError: argument of type 'datetime.datetime' is not iterable." Do date fields not work with Cursors? Thank you. – Andrew Sep 12 '14 at 19:48
  • I honestly do not know the answer to that (I've never tried, and a quick Google didn't enlighten me). You might want to ask that as a brand new Question -- post your current code, explain what's going on, and see what the rest of GIS.SE can do with it. Nobody besides me is likely to notice this comment :) – Erica Sep 12 '14 at 19:53
  • 1
    I actually commented out the "if any('..." line and the else lines. It seems to work now. I checked the newtable and it looks all good. I don't think those lines are necessary but I could be wrong. Thanks. – Andrew Sep 12 '14 at 20:11
  • You're right, it's not! Fixed it. – Erica Sep 12 '14 at 21:43

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.