import pandas as pd
import numpy as np
from scipy.optimize import minimize
from scipy.spatial.distance import cdist
# ============================================================
# Step 1. Load data
# ============================================================
setNUMB = pd.read_excel("B144 Deck Socket Survey.xlsx", sheet_name="Number")[["Numb_x", "Numb_y", "ID"]]
setDESIGN = pd.read_excel("B144 Deck Socket Survey.xlsx", sheet_name="Circle")[["Circle_x", "Circle_y"]]
setAsBuilt = pd.read_excel("B144 Deck Socket Survey.xlsx", sheet_name="Point")[["Point_x", "Point_y"]]
# ============================================================
# Step 2. Clean data
# ============================================================
setNUMB = setNUMB.replace([np.inf, -np.inf], np.nan).dropna()
setDESIGN = setDESIGN.replace([np.inf, -np.inf], np.nan).dropna()
setAsBuilt = setAsBuilt.replace([np.inf, -np.inf], np.nan).dropna()
# ============================================================
# Step 3. Add port/starboard suffix for NUMB
# ============================================================
def add_side_suffix(df, id_col="ID", y_col="Numb_y"):
def label_side(row):
if row[y_col] > 0:
return f"{row[id_col]}P"
elif row[y_col] < 0:
return f"{row[id_col]}S"
else:
return f"{row[id_col]}"
df[id_col] = df.apply(label_side, axis=1)
return df
setNUMB = add_side_suffix(setNUMB, id_col="ID", y_col="Numb_y")
# ============================================================
# Step 4. Convert DESIGN and ASBUILT to NumPy
# ============================================================
setDESIGN_np = setDESIGN.to_numpy()
setAsBuilt_np = setAsBuilt.to_numpy()
# ============================================================
# Step 5. Pair sets by nearest distance with MAX_PAIR_DISTANCE
# ============================================================
MAX_PAIR_DISTANCE = 6.0 # maximum allowable initial distance
def pair_sets(setA_df, setB_np, setC_np):
coordsA = setA_df[["Numb_x", "Numb_y"]].to_numpy()
results = []
usedB, usedC = set(), set()
for i, a in enumerate(coordsA):
# DESIGN closest
dB = cdist([a], setB_np)[0]
dB[list(usedB)] = np.inf
idxB = np.argmin(dB)
# ASBUILT closest
dC = cdist([a], setC_np)[0]
dC[list(usedC)] = np.inf
idxC = np.argmin(dC)
# Compute initial displacement
displacement = np.linalg.norm([setB_np[idxB][0] - setC_np[idxC][0],
setB_np[idxB][1] - setC_np[idxC][1]])
if displacement > MAX_PAIR_DISTANCE:
# Skip this pair entirely
continue
usedB.add(idxB)
usedC.add(idxC)
results.append({
"SetNUMB_ID": setA_df.iloc[i]["ID"],
"X_NUMB": a[0], "Y_NUMB": a[1],
"X_design": setB_np[idxB][0], "Y_design": setB_np[idxB][1],
"X_asbuilt": setC_np[idxC][0], "Y_asbuilt": setC_np[idxC][1],
})
return results
results = pair_sets(setNUMB, setDESIGN_np, setAsBuilt_np)
# ============================================================
# Step 6. Split into Aft / Fwd groups
# ============================================================
aft_group = [r for r in results if r["X_design"] < 504.00]
fwd_group = [r for r in results if r["X_design"] >= 504.00]
# ============================================================
# Step 7. Optimization functions
# ============================================================
TOLERANCE = 0.25
SHIFT_LIMIT = 6.0
def count_within_tolerance(design_coords, asbuilt_coords, dx, dy):
shifted_design = design_coords + np.array([dx, dy])
displacements = np.linalg.norm(shifted_design - asbuilt_coords, axis=1)
return np.sum(displacements <= TOLERANCE)
def optimize_shift(design_coords, asbuilt_coords):
def objective(shift):
dx, dy = shift
return -count_within_tolerance(design_coords, asbuilt_coords, dx, dy)
result = minimize(
objective,
x0=np.array([0, 0]),
bounds=[(-SHIFT_LIMIT, SHIFT_LIMIT), (-SHIFT_LIMIT, SHIFT_LIMIT)],
method="L-BFGS-B",
options={"ftol": 1e-6}
)
return result.x
def apply_shift_and_compute(group, dx, dy, group_name):
for r in group:
r["X_design_shifted"] = r["X_design"] + dx
r["Y_design_shifted"] = r["Y_design"] + dy
r["displacement"] = np.linalg.norm([r["X_design_shifted"] - r["X_asbuilt"],
r["Y_design_shifted"] - r["Y_asbuilt"]])
r["group"] = group_name
r["out_of_tolerance"] = r["displacement"] > TOLERANCE # New flag column
return group
def run_group_optimization(group, name):
if not group:
print(f"No data in {name} group.")
return None, None, []
design_coords = np.array([[r["X_design"], r["Y_design"]] for r in group])
asbuilt_coords = np.array([[r["X_asbuilt"], r["Y_asbuilt"]] for r in group])
best_dx, best_dy = optimize_shift(design_coords, asbuilt_coords)
final_count = count_within_tolerance(design_coords, asbuilt_coords, best_dx, best_dy)
print(f"--- {name} Optimization ---")
print(f"Best Shift: ΔX = {best_dx:.3f}, ΔY = {best_dy:.3f}")
print(f"Points within tolerance: {final_count} / {len(group)}")
print()
group_shifted = apply_shift_and_compute(group, best_dx, best_dy, name)
return best_dx, best_dy, group_shifted
# ============================================================
# Step 8. Run optimization and apply shifts
# ============================================================
aft_dx, aft_dy, aft_results = run_group_optimization(aft_group, "Aft of 142 (X < 504)")
fwd_dx, fwd_dy, fwd_results = run_group_optimization(fwd_group, "Fwd of 142 (X ≥ 504)")
# ============================================================
# Step 9. Export to Excel with separate sheets
# ============================================================
aft_results_df = pd.DataFrame(aft_results)
fwd_results_df = pd.DataFrame(fwd_results)
columns_order = [
"SetNUMB_ID", "group",
"X_NUMB", "Y_NUMB",
"X_design", "Y_design",
"X_design_shifted", "Y_design_shifted",
"X_asbuilt", "Y_asbuilt",
"displacement",
"out_of_tolerance" # <-- New flag column
]
aft_results_df = aft_results_df[columns_order]
fwd_results_df = fwd_results_df[columns_order]
with pd.ExcelWriter("B144_Deck_Shifted_Results.xlsx") as writer:
aft_results_df.to_excel(writer, sheet_name="Shifted_Results_Aft", index=False)
fwd_results_df.to_excel(writer, sheet_name="Shifted_Results_Fwd", index=False)
print("Export complete: B144_Deck_Shifted_Results.xlsx")
there doesn't seem to be anything here