topic Re: Using Python to unpivot a table. in Data Management Questions

Using Python to unpivot a table.

AlfredBaldenweck — Thu, 19 Aug 2021 00:04:53 GMT

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!

Re: Using Python to unpivot a table.

forestknutsen1 — Thu, 19 Aug 2021 01:00:39 GMT

I would just make a new table. logic without the esri stuff could look like this:

rows = [['1', '3', '7/04/1996', 'T. Pratchett, N. Gaiman'],
        ['2', '4', '8/04/1996', 'person a'],
        ['3', '1', '9/04/1996', 'person a, person b, person c']]

for row in rows:
    for author in [x.strip() for x in row[-1].split(',')]:
        print row[1:-1] + [author]

['3', '7/04/1996', 'T. Pratchett']
['3', '7/04/1996', 'N. Gaiman']
['4', '8/04/1996', 'person a']
['1', '9/04/1996', 'person a']
['1', '9/04/1996', 'person b']
['1', '9/04/1996', 'person c']

Re: Using Python to unpivot a table.

JohannesLindner — Thu, 19 Aug 2021 09:09:10 GMT

# 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)

Re: Using Python to unpivot a table.

AlfredBaldenweck — Thu, 19 Aug 2021 20:36:08 GMT

This works great!

Thank you for your help again.