ArcGIS Pro 3.4.2: What is the best tool to map multiple XYZD points stored in a single cell within a record?
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.
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
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
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}")
---------