ArcGIS Pro 3.4.2: What is the best tool to map multiple XYZD points stored in a single cell within a record?

Question

For example, in the screenshot below, the geotrace field contains the XYZD values (latitude, longitude, altitude, and precision) of multiple points in one cell. How can these points be mapped?

A sample dataset is attached in an Excel file.

GeoTrace.xlsx

Report_Window_DB.png

JamalNUMAN · Answer

Thank you Tom for the help. The hint you provided is very useful.

I could resolve it with the help of ChatGPT, requesting the following:

Write a Python code to read the data from the table below

"C:\D\T1.xlsx"

Then do the following

* Go to the second column with the name F2, for each record, divides its values (separated by space, semicolons, or whatever) into separate columns in separate columns, then, save the result in a new table in C:\D (Name it T2)
* For the T2, skip the first column, then keep the first two columns, delete the next two, keep the next two, then delete the next two… repeat this pattern for all columns in T2. Save the result in T3
* Now, for each row in T3, stick the values of the first column and the second column in a new record, then again stick the values of the third and the fourth column in a new record, then again stick the values of the fifth and sixth column in a new record and so forth. Save it in T4

after that, I could plot T4 in ArcGIS Pro

----------

import pandas as pd

import os

# Define file paths

input_file = r"C:\D\T1.xlsx"

output_file_t2 = r"C:\D\T2.xlsx"

output_file_t3 = r"C:\D\T3.xlsx"

output_file_t4 = r"C:\D\T4.xlsx"

# Read the Excel file

df = pd.read_excel(input_file)

# Check if 'F2' exists in the DataFrame

if 'F2' not in df.columns:

raise ValueError("Column 'F2' not found in the input file")

# Split the values in 'F2' by common delimiters

split_columns = df['F2'].astype(str).str.split(r'[; ,]+', expand=True)

# Rename columns dynamically

split_columns.columns = [f'F2_part{i+1}' for i in range(split_columns.shape[1])]

# Combine with the original DataFrame (excluding the original 'F2' column)

df_t2 = df.drop(columns=['F2']).join(split_columns)

# Save the new table to an Excel file

df_t2.to_excel(output_file_t2, index=False)

print(f"File saved successfully at: {output_file_t2}")

# Process T2 to create T3

# Skip the first column, then apply the keep-delete pattern

columns_to_keep = [df_t2.columns[0]]  # Keep the first column

cols = df_t2.columns[1:]  # Skip the first column

for i in range(0, len(cols), 4):

columns_to_keep.extend(cols[i:i+2])  # Keep two columns, skip two

df_t3 = df_t2[columns_to_keep]

# Save the result as T3.xlsx

df_t3.to_excel(output_file_t3, index=False)

print(f"File saved successfully at: {output_file_t3}")

# Process T3 to create T4 (Expanding rows based on column groups)

records = []

for _, row in df_t3.iterrows():

first_col_value = row.iloc[0]  # First column value

other_values = row.iloc[1:].values  # Other column values

for i in range(0, len(other_values) - 1, 2):

if i + 1 < len(other_values):

records.append([first_col_value, other_values[i], other_values[i + 1]])

# Convert to DataFrame

df_t4 = pd.DataFrame(records, columns=[df_t3.columns[0], 'Value1', 'Value2'])

# Save the result as T4.xlsx

df_t4.to_excel(output_file_t4, index=False)

print(f"File saved successfully at: {output_file_t4}")

---------

ThomasHoman · Answer

Based on the excel file name I would suspect that you wish to end up with a line attributed T1, T2, ... and this task might occur more than one time.

You will need to do some preprocessing either in the original excel or develop some python code. My Python is not that good so I will do things in excel.

Looking at the table, each coordinate pair is separated by a semicolon so that is the first split. Take each of those and split again on the space delimiter to end up with coordinate pairs. From there you can build your points or lines. You can see the results of this in the attached edited Excel file.

From there it is a matter of XY Table to Point then Points to Line tools to create your traces.

Hope this helps.

Tom

GeoTrace Edited.xlsx