Python表格整理脚本问题：如何调整新增列的分组排序？

API 探路者

2026-7-13

表格整理Python脚本列排序问题

我写了一个用来整理工作表格的Python脚本，但新增列的排序不符合预期。我用示例数据调试脚本，目标是把每个客户的数据显示在单行，同时把唯一数据追加到新列，保证不丢数据。

当前脚本能正确识别要追加和保留的数据，但追加的列顺序不对。我需要按Address A、Address B、Address C这样的分组排序（也就是Street A、City A、ZIP A为一组，Street B、City B、ZIP B为一组，以此类推），但当前输出顺序是Street A、Street B、Street C，City A、City B、City C，ZIP A、ZIP B、ZIP C。

原脚本如下：

import pandas as pd
from tkinter import Tk
from tkinter.filedialog import askopenfilename, asksaveasfilename
 
# Create a Tkinter root window (hidden)
root = Tk()
root.withdraw()

# Ask the user to select a file
file_path = askopenfilename(filetypes=[("Excel files", "*.xlsx"), ("CSV files", "*.csv")])

# Check if a file was selected
if file_path:
    # Load the file into a DataFrame
    if file_path.endswith('.xlsx'):
        df = pd.read_excel(file_path)
    elif file_path.endswith('.csv'):
        df = pd.read_csv(file_path)
    
    # Convert all values in the DataFrame to strings
    df = df.astype(str)

    # Identify duplicates based on the "Alien Number" field
    df_unique = df.drop_duplicates(subset=["Alien Number"], keep='first')

    # Create a dictionary to hold data for each unique entity
    data_dict = {}

    # Iterate through the rows and populate the data dictionary
    for _, row in df.iterrows():
        alien_number = row["Alien Number"]
        entity_data = row.drop(labels=["Alien Number"])
        if alien_number not in data_dict:
            data_dict[alien_number] = {}  # Use a dictionary to store unique data

        # Process each column's data
        for col_name, value in entity_data.items():
            if col_name not in data_dict[alien_number]:
                data_dict[alien_number][col_name] = {value}
            else:
                data_dict[alien_number][col_name].add(value)

    # Create a new list of rows with entity and data information
    new_rows = []

    # Iterate through the unique entities
    for _, entity_row in df_unique.iterrows():
        alien_number = entity_row["Alien Number"]
        entity_data_dict = data_dict.get(alien_number, {})
       
        # Append entity data to the new row
        new_row = {**entity_row}
        for col_name, value_set in entity_data_dict.items():
            if len(value_set) == 1:
                new_row[col_name] = value_set.pop()
            else:
                for idx, value in enumerate(value_set, start=1):
                    new_col_name = f"{col_name}_{chr(ord('A') + idx)}"  # Column_B, Column_C, etc.
                    new_row[new_col_name] = value

        new_rows.append(new_row)

    # Create a new DataFrame with the combined rows
    merged_df = pd.DataFrame(new_rows)

    # Print the merged DataFrame
    print(merged_df)

    # Ask the user to provide a name for the edited file
    save_path = asksaveasfilename(defaultextension=".xlsx", filetypes=[("Excel files", "*.xlsx")])

    # Check if a save path was provided
    if save_path:

        # Export the merged DataFrame to an Excel file
        merged_df.to_excel(save_path, index=False)
        print(f"Edited file saved to: {save_path}")
    else:
        print("No save path provided. File was not saved.")
else:
    print("No file selected.")

当前输出是把同类型列（比如所有Street、所有City）排在一起，而我需要的是把同一组的列（Street A、City A、ZIP A）放在一起，再排Street B、City B、ZIP B这样的分组。

解决方案

问题出在生成新列后，DataFrame的列顺序按字典插入顺序排列，原脚本先处理完所有Street的新列，再处理City，最后处理ZIP。要实现分组排序，需要：

统计所有客户的最大地址组数（比如最多有3组地址，对应A、B、C）
重新构造列顺序：先保留原始非地址列，再按分组依次添加每组的Street、City、ZIP列

修改后的完整脚本

import pandas as pd
from tkinter import Tk
from tkinter.filedialog import askopenfilename, asksaveasfilename
 
# Create a Tkinter root window (hidden)
root = Tk()
root.withdraw()

# Ask the user to select a file
file_path = askopenfilename(filetypes=[("Excel files", "*.xlsx"), ("CSV files", "*.csv")])

# Check if a file was selected
if file_path:
    # Load the file into a DataFrame
    if file_path.endswith('.xlsx'):
        df = pd.read_excel(file_path)
    elif file_path.endswith('.csv'):
        df = pd.read_csv(file_path)
    
    # Convert all values in the DataFrame to strings
    df = df.astype(str)

    # Identify duplicates based on the "Alien Number" field
    df_unique = df.drop_duplicates(subset=["Alien Number"], keep='first')

    # Create a dictionary to hold data for each unique entity
    data_dict = {}
    # 统计所有客户的最大地址组数
    max_group_count = 0

    # Iterate through the rows and populate the data dictionary
    for _, row in df.iterrows():
        alien_number = row["Alien Number"]
        entity_data = row.drop(labels=["Alien Number"])
        if alien_number not in data_dict:
            data_dict[alien_number] = {}  # Use a dictionary to store unique data

        # Process each column's data
        for col_name, value in entity_data.items():
            if col_name not in data_dict[alien_number]:
                data_dict[alien_number][col_name] = {value}
            else:
                data_dict[alien_number][col_name].add(value)
        # 更新最大组数（假设地址相关列是Street、City、ZIP）
        current_group = len(data_dict[alien_number]["Street"])
        if current_group > max_group_count:
            max_group_count = current_group

    # Create a new list of rows with entity and data information
    new_rows = []

    # Iterate through the unique entities
    for _, entity_row in df_unique.iterrows():
        alien_number = entity_row["Alien Number"]
        entity_data_dict = data_dict.get(alien_number, {})
       
        # Append entity data to the new row
        new_row = {**entity_row}
        for col_name, value_set in entity_data_dict.items():
            if len(value_set) == 1:
                new_row[col_name] = value_set.pop()
            else:
                # 修正索引，确保idx=1对应后缀A
                for idx, value in enumerate(value_set, start=1):
                    new_col_name = f"{col_name}_{chr(ord('A') + idx - 1)}"
                    new_row[new_col_name] = value

        new_rows.append(new_row)

    # Create a new DataFrame with the combined rows
    merged_df = pd.DataFrame(new_rows)

    # 重新构造列顺序
    # 1. 保留原始非地址列（根据实际列名调整）
    original_non_address_cols = [col for col in df.columns if col not in ["Street", "City", "ZIP"]]
    # 2. 按分组构造地址列
    grouped_cols = []
    for i in range(max_group_count):
        suffix = chr(ord('A') + i)
        grouped_cols.append(f"Street_{suffix}")
        grouped_cols.append(f"City_{suffix}")
        grouped_cols.append(f"ZIP_{suffix}")
    
    # 合并最终列顺序：原始非地址列 + 分组地址列
    final_cols = original_non_address_cols.copy()
    if max_group_count == 1:
        # 只有一组地址时保留原始列名
        final_cols.extend(["Street", "City", "ZIP"])
    else:
        final_cols.extend(grouped_cols)
    
    # 过滤掉DataFrame中不存在的列，避免报错
    final_cols = [col for col in final_cols if col in merged_df.columns]
    
    # 重新排序DataFrame
    merged_df = merged_df[final_cols]

    # Print the merged DataFrame
    print(merged_df)

    # Ask the user to provide a name for the edited file
    save_path = asksaveasfilename(defaultextension=".xlsx", filetypes=[("Excel files", "*.xlsx")])

    # Check if a save path was provided
    if save_path:
        merged_df.to_excel(save_path, index=False)
        print(f"Edited file saved to: {save_path}")
    else:
        print("No save path provided. File was not saved.")
else:
    print("No file selected.")

关键修改说明

新增max_group_count变量统计所有客户的最大地址组数，确保列顺序覆盖所有可能的分组
修正新列名的索引计算，让idx=1对应后缀A（原脚本会对应B）
重新构造列顺序时，先保留原始非地址列，再按A、B、C分组依次添加Street、City、ZIP列
处理了单组地址的特殊情况，保留原始列名而非添加后缀

内容的提问来源于stack exchange，提问作者iamduvede

Python表格整理脚本问题：如何调整新增列的分组排序？

修改后的完整脚本 #

关键修改说明 #

修改后的完整脚本

关键修改说明