Using Python to unpivot a table.

AlfredBaldenweck

I have a table in which one of the fields is currently filled with a comma separated list. (Made-up sample below)

OBJECTID	RELID	DATE	Authors
1	3	7/04/1996	T. Pratchett, N. Gaiman

 

I'd like to split this so that I have a record per each author, with the rest of the information maintained.

OBJECTID	RELID	DATE	Authors
1	3	7/04/1995	T. Pratchett
1	3	7/04/1995	N. Gaiman

 

The workflow, I think, would be

• Get the table
• Search the table by row
• Split the Authors column into a list
• If there is only 1 or 0 authors, ignore it and move on
• If there are two or more authors,
  • For each author, create a new row and pass the original values from the row it came from

I'm very much a beginner at this, so the following is my best guess before I ran out of ideas, specifically on how to get the original values to the new rows. 

If anyone could give me some pointers, I'd really appreciate it.

import arcpy, os
from collections import defaultdict

inFC = #######

myFeatures = dict()

with arcpy.da.SearchCursor(inFC, ['OBJECTID', 'RELID', 'DATE', 'AUTHORS']) as cursor:
    for row in cursor:
        listA= list(row[4].split(","))
        if count(listA)== 1:
            ""
        elif count(listA) == 0:
            ""
        else:
            rows = arcp.da.InsertCursor(inFC)
            for x in listA:
                row = rows.NewRow()
                row.setValue("RELID", 

 

Thank you!

 

JohannesLindner

# your input table and fields
in_fc = "..."
in_fields = ["OBJECTID", "RELID", "DATE", "AUTHORS"]

# your output table and fields
# this can be in_fc and in_fields, but for testing I suggest you create a new table...
# if you really need to copy ObjectID, you have to create a new field, FID in my example
out_fc = "..."
out_fields = ["FID", "RELID", "DATE", "AUTHORS"]

# index of the field that has to be split
# !python uses zero-based indexing!
split_field_index = 3
split_pattern = ", "


# read data
data = [list(row) for row in arcpy.da.SearchCursor(in_fc, in_fields)]

# optional: delete all rows from out_fc
# !!! if out_fc is the origin table of a relationship class, the foreign key in the related table will be set to Null !!!
arcpy.management.TruncateTable(out_fc)

with arcpy.da.InsertCursor(out_fc, out_fields) as cursor:
    # loop through original data
    for row in data:
        split_field = row[split_field_index]
        # split_field is empty? just copy the row
        if split_field is None:
            cursor.insertRow(row)
            continue
        # split split_field 
        split_values = split_field.split(split_pattern)
        # loop through split_values and insert rows
        for value in split_values:
            row[split_field_index] = value
            cursor.insertRow(row)