Code with stage logic
import os
import re
from tkinter import Tk, filedialog
import pandas as pd
# ==========================================
# ⚙️ CONFIGURATION
# ==========================================
ROWS_TO_SKIP = 2
ROWS_TO_DROP_AFTER = []
# Suffix Handling
SPECIAL_SUFFIXES = ["_Avg", "_Min", "_Max"] # Add any specific endings here
ACTION_FOR_SUFFIXES = "keep" # Set to "keep" to rename them, or "drop" to
delete them
# ==========================================
def process_and_rename_columns(cols):
seen_bases = {}
name_counts = {}
new_cols = []
base_counter = 1
for col in cols:
col_str = str(col)
# 1. Dynamic Stage Detection (Hunt for numbers in the original name)
match = [Link](r'\d+', col_str)
if match:
stage_num = [Link]()
stage_prefix = f"S{stage_num}_"
else:
stage_prefix = ""
# 2. Check if the column has a special suffix
found_suffix = ""
base_col_name = col_str
for suffix in SPECIAL_SUFFIXES:
if col_str.endswith(suffix):
found_suffix = suffix
# Extract the base (e.g., "p2_Avg" becomes just "p2")
base_col_name = col_str.replace(suffix, "")
break
# 3. Track the base name to assign/remember its "A" number
if base_col_name not in seen_bases:
seen_bases[base_col_name] = f"A{base_counter}"
base_counter += 1
# 4. Apply the appropriate renaming rule with the Stage Prefix
if found_suffix:
# Format: Stage + Base + Suffix (e.g., S2_A3_Avg)
new_name = f"{stage_prefix}{seen_bases[base_col_name]}
{found_suffix}"
else:
# Standard columns get the .1, .2 counts
if base_col_name not in name_counts:
name_counts[base_col_name] = 1
else:
name_counts[base_col_name] += 1
# Format: Stage + Base + Count (e.g., S2_A3.1)
new_name = f"{stage_prefix}{seen_bases[base_col_name]}.
{name_counts[base_col_name]}"
new_cols.append(new_name)
return new_cols
# Setup the Tkinter window
root = Tk()
[Link]()
[Link]("-topmost", True)
print("Opening file selection dialog...")
file_paths = [Link](
title="Select Data Files",
filetypes=[("Data Files", "*.csv *.xlsx *.xls"), ("All Files", "*.*")]
)
if not file_paths:
print("No files were selected. Exiting process.")
else:
for path in file_paths:
try:
print(f"\nReading: {[Link](path)}...")
file_ext = [Link](path)[1].lower()
# Load file based on extension
if file_ext == '.csv':
df = pd.read_csv(path, low_memory=False, skiprows=ROWS_TO_SKIP)
elif file_ext in ['.xls', '.xlsx']:
df = pd.read_excel(path, skiprows=ROWS_TO_SKIP)
else:
print(f"⚠️ Skipping {[Link](path)}: Unsupported
format.")
continue
# Drop specified initial rows if configured
if ROWS_TO_DROP_AFTER:
df = [Link](index=ROWS_TO_DROP_AFTER,
errors='ignore').reset_index(drop=True)
# Drop special columns if configured to "drop"
if ACTION_FOR_SUFFIXES.lower() == "drop":
cols_to_drop = [c for c in [Link] if any(str(c).endswith(s)
for s in SPECIAL_SUFFIXES)]
if cols_to_drop:
df = [Link](columns=cols_to_drop)
print(f" -> Dropped {len(cols_to_drop)} columns ending
with special suffixes.")
# Process Headers
original_columns = [Link]()
new_columns = process_and_rename_columns(original_columns)
[Link] = new_columns
# Create Mapping File
mapping_df = [Link]({
"Original_Column_Name": original_columns,
"New_Structured_Name": new_columns
})
# Setup Save Paths
dir_name = [Link](path)
base_name = [Link](path)
file_name, _ = [Link](base_name)
# Save based on original extension
if file_ext == '.csv':
data_output_path = [Link](dir_name,
f"{file_name}_renamed.csv")
map_output_path = [Link](dir_name,
f"{file_name}_mapping.csv")
df.to_csv(data_output_path, index=False)
mapping_df.to_csv(map_output_path, index=False)
else:
data_output_path = [Link](dir_name,
f"{file_name}_renamed.xlsx")
map_output_path = [Link](dir_name,
f"{file_name}_mapping.xlsx")
df.to_excel(data_output_path, index=False)
mapping_df.to_excel(map_output_path, index=False)
print(f" Success! Data saved to:
{[Link](data_output_path)}")
print(f" Success! Mapping saved to:
{[Link](map_output_path)}")
except Exception as e:
print(f"❌ Error processing {[Link](path)}: {e}")
print("\n🎉 All tasks completed!")
Without Stage Logic
import os
from tkinter import Tk, filedialog
import pandas as pd
# ==========================================
# ⚙️ CONFIGURATION
# ==========================================
ROWS_TO_SKIP = 2
ROWS_TO_DROP_AFTER = []
# Suffix Handling
SPECIAL_SUFFIXES = ["_Avg", "_Min", "_Max"] # Add any specific endings here
ACTION_FOR_SUFFIXES = "keep" # Set to "keep" to rename them, or "drop" to
delete them
# ==========================================
def process_and_rename_columns(cols):
seen_bases = {}
name_counts = {}
new_cols = []
base_counter = 1
for col in cols:
col_str = str(col)
# 1. Check if the column has a special suffix
found_suffix = ""
base_col_name = col_str
for suffix in SPECIAL_SUFFIXES:
if col_str.endswith(suffix):
found_suffix = suffix
# Extract the base (e.g., "p2_Avg" becomes just "p2")
base_col_name = col_str.replace(suffix, "")
break
# 2. Track the base name to assign/remember its "A" number
if base_col_name not in seen_bases:
seen_bases[base_col_name] = f"A{base_counter}"
base_counter += 1
# 3. Apply the appropriate renaming rule (No Stage Logic)
if found_suffix:
# Format: Base + Suffix (e.g., A3_Avg)
new_name = f"{seen_bases[base_col_name]}{found_suffix}"
else:
# Standard columns get the .1, .2 counts
if base_col_name not in name_counts:
name_counts[base_col_name] = 1
else:
name_counts[base_col_name] += 1
# Format: Base + Count (e.g., A3.1)
new_name = f"{seen_bases[base_col_name]}.
{name_counts[base_col_name]}"
new_cols.append(new_name)
return new_cols
# Setup the Tkinter window
root = Tk()
[Link]()
[Link]("-topmost", True)
print("Opening file selection dialog...")
file_paths = [Link](
title="Select Data Files",
filetypes=[("Data Files", "*.csv *.xlsx *.xls"), ("All Files", "*.*")]
)
if not file_paths:
print("No files were selected. Exiting process.")
else:
for path in file_paths:
try:
print(f"\nReading: {[Link](path)}...")
file_ext = [Link](path)[1].lower()
# Load file based on extension
if file_ext == '.csv':
df = pd.read_csv(path, low_memory=False, skiprows=ROWS_TO_SKIP)
elif file_ext in ['.xls', '.xlsx']:
df = pd.read_excel(path, skiprows=ROWS_TO_SKIP)
else:
print(f"⚠️ Skipping {[Link](path)}: Unsupported
format.")
continue
# Drop specified initial rows if configured
if ROWS_TO_DROP_AFTER:
df = [Link](index=ROWS_TO_DROP_AFTER,
errors='ignore').reset_index(drop=True)
# Drop special columns if configured to "drop"
if ACTION_FOR_SUFFIXES.lower() == "drop":
cols_to_drop = [c for c in [Link] if any(str(c).endswith(s)
for s in SPECIAL_SUFFIXES)]
if cols_to_drop:
df = [Link](columns=cols_to_drop)
print(f" -> Dropped {len(cols_to_drop)} columns ending
with special suffixes.")
# Process Headers
original_columns = [Link]()
new_columns = process_and_rename_columns(original_columns)
[Link] = new_columns
# Create Mapping File
mapping_df = [Link]({
"Original_Column_Name": original_columns,
"New_Structured_Name": new_columns
})
# Setup Save Paths
dir_name = [Link](path)
base_name = [Link](path)
file_name, _ = [Link](base_name)
# Save based on original extension
if file_ext == '.csv':
data_output_path = [Link](dir_name,
f"{file_name}_renamed.csv")
map_output_path = [Link](dir_name,
f"{file_name}_mapping.csv")
df.to_csv(data_output_path, index=False)
mapping_df.to_csv(map_output_path, index=False)
else:
data_output_path = [Link](dir_name,
f"{file_name}_renamed.xlsx")
map_output_path = [Link](dir_name,
f"{file_name}_mapping.xlsx")
df.to_excel(data_output_path, index=False)
mapping_df.to_excel(map_output_path, index=False)
print(f" Success! Data saved to:
{[Link](data_output_path)}")
print(f" Success! Mapping saved to:
{[Link](map_output_path)}")
except Exception as e:
print(f"❌ Error processing {[Link](path)}: {e}")
print("\n🎉 All tasks completed!")