How To Iterate Split By Attributes

[LeviCecil]

I'm writing a script to split a feature class of 15000 rooms, into subgroups of rooms by floor. I'd like to loop through each group as it is split and write this table to csv, or directly to Google sheets if possible. As it stands, it looks like the split by attribute tool just runs and creates a new feature for each group. Will I need to iterate after it runs?

[DavidPike]

Can you share what the data looks like i.e. how to group by floor (spatial or attribute based selection criteria etc.)

What do you mean by 'split' i.e. what's the exact intended outcome?  

For write table to CSV - what does this look like?  A table with all the ObjectIDs and attributes for features in each floor?

[LeviCecil]

So each room has a corresponding Floor ID attribute, which is what I'm splitting by. I'm using the split by attributes tool. The desired outcome is writing each one of these resulting features (rooms on the 1st floor, 2nd floor, etc) to a Google sheet in a directory for that floor. The problem I'm seeing is that when I run the tool, it just splits everything at once. I'm wondering if there's a way to iterate through each group as it's being split, instead of after the fact? I could do that by looping through the resulting features in the temp gdb.

And yes, the end result is a sheet that contains all the attributes for each room. ObjectID doesn't need to be included. 

[TonyAlmeida]

Not sure why your tying to iterate before the split, but as DavidSolari mentioned, you should just be able to exprot the table.

Here how I would iterate and you don't need to splitbyattribute, not sure if this is what you are looking for but I use this for something similar.

import arcpy
import csv

# Path to the rooms feature class
rooms_fc = "ZipCodes"

# Field names (include all fields except ObjectID)
fields = [field.name for field in arcpy.ListFields(rooms_fc) if field.name != "ObjectID"]

# Dictionary to store rooms grouped by ZIP code
zip_groups = {}

# Iterate through each room in the feature class
with arcpy.da.SearchCursor(rooms_fc, fields) as cursor:
    for row in cursor:  # Iterating through each room
        zip_code = row[fields.index("Zip")]  # Get the ZIP code for the current room
        if zip_code not in zip_groups:
            zip_groups[zip_code] = []
        zip_groups[zip_code].append(row)  # Add the room to its ZIP group

# Iterate through each ZIP group and write to CSV
output_folder = "C:\Temp"
for zip_code, rooms in zip_groups.items():  # Iterating through each ZIP group
    csv_file = f"{output_folder}/zip_{zip_code}.csv"
    with open(csv_file, mode="w", newline="") as file:
        writer = csv.writer(file)
        writer.writerow(fields)  # Write the header (field names)
        writer.writerows(rooms)  # Write the rows (room data)
    print(f"CSV file created for ZIP {zip_code}: {csv_file}")

print("All ZIP codes processed and CSV files created.")

[DavidSolari]

If your data is one feature per room and every room has a floor number attribute, you don't need to process it any further. Use Export Table (right-click the layer, then Data → Export Table) to dump everything to a CSV, then you can open that in the spreadsheet tool of your choice and pivot the data into the proper shape. Alternatively you can use tools like Summary Statistics or Pivot Table to aggregate your data before you export it.

If this isn't suitable you'll have to post a minimal excerpt of your data so we can see what the structure is and suggest the proper tools.

[DavidPike]

yeh it seems more of a data wrangling exercise in Excel after the entire attribute table is exported.  

[TonyAlmeida]

For google sheets you will need a JSON key file associated with a Google Cloud service account.

I just went through this  see below, hopefully it helps.

import gspread
import pandas as pd
import xlrd
import arcpy
from oauth2client.service_account import ServiceAccountCredentials

# Define the scope
scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"]

# Add your credentials file
creds = ServiceAccountCredentials.from_json_keyfile_name('xyzx.json', scope) #JSON key file associated with a Google Cloud service account.

# Authorize the client
client = gspread.authorize(creds)

# Open the Google Sheets spreadsheet by name
spreadsheet = client.open("Name")

# Select the first sheet (or specify the sheet name)
sheet = spreadsheet.worksheet("Sheet1")

# Load the Excel file from your PC
excel_file_path = r"C:\temp\xyzx.xls"

# Read the Excel file
try:
    df = pd.read_excel(excel_file_path)
    arcpy.AddWarning("Excel file read successfully!")
except Exception as e:
    arcpy.AddWarning(f"An error occurred while reading the Excel file: {e}")
    exit()

# Extract only the first column
first_column = df.iloc[:, [0]]  # This will select only the first column

# Replace NaN values with None (or an empty string "")
first_column = first_column.where(pd.notnull(first_column), None)

# Convert the DataFrame to a list of lists, including the column name
column_names = [first_column.columns.tolist()]  # Get the column name
data = column_names + first_column.values.tolist()  # Combine column name and data

# Clear the existing content in the sheet (optional)
sheet.clear()

# Update the Google Sheets with the new data
try:
    sheet.update(data)
    arcpy.AddWarning("Google Name Sheets updated successfully!")
except Exception as e:
    arcpy.AddWarning(f"An error occurred while updating Google Sheets: {e}")

# Reopen the workbook after writing to Google Sheets
workbook = xlrd.open_workbook(excel_file_path)
xls_sheet = workbook.sheet_by_index(0)  # Get the first sheet again

# Count rows with at least one text value after update (excluding the first row)
text_rows_after = sum(
    1 for row_idx in range(1, xls_sheet.nrows)
    if any(isinstance(value, str) and value.strip() != "" for value in xls_sheet.row_values(row_idx))
)

# Calculate the change
text_rows_change = text_rows_after 

# print/Log the results
arcpy.AddWarning(f"Number of rows with text after update: {text_rows_after}")

[LeviCecil]

Thanks for all the replies! I'm a rudimentary Excel user, so I would rather not use it as an interim step to get to Google Sheets. I'll just use export table and do the rest in FME.