Python - write new row based on comma-separated values in table column?

6696
2
Jump to solution
02-28-2018 01:27 PM
AllenScully
Occasional Contributor II

Hopefully a simple (and fun!) Python loop question.

We have a dataset that looks like this:

ROWIDATTRB1ATTRB2ATTRB3ATTRB4
1AAABBBCCCABCD, EFGH,IKL

Where ATTRB4 often contains a comma-separated list of values.  Unfortunately we need these values as a join field in a SQL view, so the comma-separated list format output we receive needs to be changed so that there is only 1 value in the ATTRB4 column

So, what I'm working on is creating a non-normalized version of this table (actually a feature class) that will have 3 duplicate rows (duplicate rows not a problem in this case) , with identical values for ATT1-3 but with the ATT4 value being a single one of the comma-separated list values:

ROWIDATTRB1ATTRB2ATTRB3ATTRB4
1AAABBBCCCABCD
2AAABBBCCCEFGH
3AAABBBCCCIJKL

I believe I need to get the comma-separated ATTRB4 column into a list, and loop through it, but am not quite sure how to wrap my head around the code here.  

Thanks

Allen

PS the comma-separated list values are the result of GeoEvent service (v 10.5.1) that uses a GeoFence and GeoTagger to identify points in certain areas and add the GeoFence name to the output - so when a point is within multiple areas, the multi-value list is result.  If anyone happens to know a way to force GeoEvent to output in the format shown in the 2nd table, that would work too. 

0 Kudos
1 Solution

Accepted Solutions
JamesMacKay3
Occasional Contributor

Something like this should do it:

fieldList = [ "ATTRB1", "ATTRB2", "ATTRB3", "ATTRB4" ]
with arcpy.da.InsertCursor(targetTable, fieldList) as insertCursor:
    with arcpy.da.SearchCursor(sourceTable, fieldList) as ‍‍searchCursor:
        for row in searchCursor:
            for attrbPart in row[3].split(","):
                insertCursor.insertRow(( row[0], row[1], row[2], attrbPart ))

I would throw in some null testing, empty string testing, error handling, etc.

View solution in original post

2 Replies
JamesMacKay3
Occasional Contributor

Something like this should do it:

fieldList = [ "ATTRB1", "ATTRB2", "ATTRB3", "ATTRB4" ]
with arcpy.da.InsertCursor(targetTable, fieldList) as insertCursor:
    with arcpy.da.SearchCursor(sourceTable, fieldList) as ‍‍searchCursor:
        for row in searchCursor:
            for attrbPart in row[3].split(","):
                insertCursor.insertRow(( row[0], row[1], row[2], attrbPart ))

I would throw in some null testing, empty string testing, error handling, etc.

AllenScully
Occasional Contributor II

James this is perfect - 

Works like a charm almost 'out of the box'.  Many thanks

0 Kudos