topic Re: Python - write new row based on comma-separated values in table column? in Python Questions

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

AllenScully — Wed, 28 Feb 2018 21:27:59 GMT

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

We have a dataset that looks like this:

ROWID	ATTRB1	ATTRB2	ATTRB3	ATTRB4
1	AAA	BBB	CCC	ABCD, 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:

ROWID	ATTRB1	ATTRB2	ATTRB3	ATTRB4
1	AAA	BBB	CCC	ABCD
2	AAA	BBB	CCC	EFGH
3	AAA	BBB	CCC	IJKL

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.

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

JamesMacKay3 — Sat, 11 Dec 2021 10:07:41 GMT

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.

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

AllenScully — Wed, 28 Feb 2018 22:48:48 GMT

James this is perfect -

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