-1

Everyone! I have an issue with the data collected from the field, where every single row has multiple sets of data in different columns (seen Input). I tried to split each set of data into a row (seen Expected Output).

Input:

Row1: ID A1 B1 C1 D1 A2 B2 C2 D2 A3 B3 C3 D3 A4 B4 C4 D4 Name1 Name2 Name3 Name4 Date

Expected Output:

Row1: ID A1 B1 C1 D1 Name1 Date

Row2: ID A2 B2 C2 D2 Name2 Date

Row3: ID A3 B3 C3 D3 Name3 Date

Row4: ID A4 B4 C4 D4 Name4 Date

I found there is a similar post, but it is using VBA Scripting. I am a GIS guy and I have some experience in the ArcPy site package of python. I have some trouble constructing a logical workflow using ArcPy. I was wondering does anyone has experienced a similar situation and know if this could be done using Python and ArcPy or any other site-packages. If so, could you please provide me with some hints on what site package and tools that I should look into?

huj35
  • 1
  • 1

1 Answers1

0

On way of doing this with arcpy is using cursor. Create a new table, iterate throught your data, split them and insert each line in the new table.

This is using arcpy from ArcMap (not ArcGIS Pro) :

import arcpy
import os

src_data = ''  # Path to your data
dst_data = ''  # New table that will be create. 
arcpy.CreateTable_management(os.path.dirname(dst_data),
                             os.path.basename(dst_data))

# Create the correct fields in the new table
# Assuming the fields have the same name as your data in Row1
# Also assuming the A1, A2, A3 fields .... have the same type/scale/precision/length/etc
# Index split table (index from the Row1 you gave in your example)
dst_idx_split = [[0, 1, 5, 9, 13, 17, 21],  # ID A1 B1 C1 D1 Name1 Date
                 [0, 2, 6, 10, 14, 18, 21], # ID A2 B2 C2 D2 Name2 Date
                 [0, 3, 7, 11, 15, 19, 21], # etc.
                 [0, 4, 8, 12, 16, 20, 21]]

src_fields = arcpy.ListFields(src_data)
for idx in dst_idx_split[0]:
    field = src_fields[idx]
    field_name = field.baseName if field_name in ['ID', 'Data'] else field_name[:-1]  # remove the 1/2/3/4 in the field name
    arcpy.AddField_management(dst_data, field_name, field.type, field.precision, 
                              field.scale, field.length, field.alias, 
                              field.isNullable, field.required, field.domain)

# Copy the data
i_cur = arcpy.da.InsertCursor(dst_data, '*')

with arcpy.da.SearchCursor(src_data, '*') as s_cur:
    for row in s_cur:  # for each row of your source data
        for idxs in dst_idx_split: 
            dst_row = [row[idx] for idx in idxs] # extract the data for the new line 
            i_cur.insertRow(dst_row)  # insert it in new table
del i_cur
Paulloed
  • 333
  • 1
  • 9
  • Thank you very much! This is super helpful! I was wondering is there anything that wouldn't work in ArcGIS Pro? I am used to work in Pro with Python 3.0x? – huj35 Nov 18 '21 at 17:49
  • The names of some functions have changed with ArcGIS Pro, but once replaced, the code should work :) Like `arcpy.CreateTable_management` became `arcpy.management.CreateTable` but the arguments of the two functions are the same. `arcpy.AddField_management` -> `arcpy.management.AddField`. The rest (cursor and ListFields) are the same – Paulloed Nov 19 '21 at 18:39
  • Thank you very much again! Actually pro accepts both and the first one is CreateTable_management is more popular, textbook and ArcGIS help use the sample code as the first one. – huj35 Nov 19 '21 at 22:55
  • Thank you very much again! I have completed the code. It works very well. – huj35 Nov 22 '21 at 21:30
  • If your question is closed, please upvote and validate my answer :) – Paulloed Nov 23 '21 at 02:58
  • Thank you very much for the reminder. It is my first time posting the question. I have validated your answer. I don't think I am eligible yet to upvote. – huj35 Nov 24 '21 at 03:06