使用Pandas（Python）实现设备进出记录Excel表格格式转换的技术咨询

大家好，我目前在一家管理设备和员工记录的公司工作，日常会收到包含设备使用数据（小时计、里程、进出记录）的Excel表格，需要把这些表格处理成标准化的报告。

遇到的问题 #

现在我们收到的表格是未标准化的结构，主要存在两个问题：

• 设备的进入和退出记录是分成两行存储的
• 没有拆分出独立的「进入时间」和「退出时间」列

我们需要将原表格转换为特定格式，满足：

• 同一人员、同一设备的进出记录合并为单行
• 拆分出Date_Time_Entry（进入时间）和Date_Time_Exit（退出时间）两个独立列

预期输出 #

标准化后的表格每行对应一次完整的设备使用周期：

• 如果有完整的进出记录，合并后会包含进入时间、退出时间，以及对应的初始/最终小时计、里程数据
• 如果只有进入记录没有退出，会保留进入信息，退出相关字段留空或标记默认值，并标注状态为「Entry」

我的解决方案思路 #

我计划用Python的Pandas库自动化处理，具体步骤如下：

• 加载Excel文件时跳过前两行元数据（因为原表格前两行是备注信息）
• 校验表格是否包含所有必要的列（做了大小写模糊匹配，兼容列名的小差异）
• 按「姓名」「设备」「成本中心」对数据分组，确保同一使用周期的记录被归为一组
• 每组内筛选出进入和退出记录，合并为单行；如果只有进入记录，单独处理并标记状态
• 将处理后的结果保存为新的Excel文件，命名为原文件名加_processed后缀

另外我还做了一个简单的GUI界面，方便公司里非技术的同事使用，不用命令行操作。

我目前写的代码 #

import pandas as pd
from tkinter import Tk, Button, filedialog, messagebox, ttk
import tkinter as tk
import os

# Global variables
df = None
file_path = None

# Expected columns in the correct format
EXPECTED_COLUMNS = [
    "Name", "Equipment", "Initial Hour Meter", "Initial Km", "Cost Center",
    "Date_Time", "Final Hour Meter", "Final Km", "Entry/Exit"
]

# Function to process the table
def process_table():
    global df, file_path

    if df is None:
        messagebox.showerror("Error", "No table has been loaded!")
        return

    try:
        # Check if the expected columns exist (with handling for name differences)
        found_columns = {}
        for expected_col in EXPECTED_COLUMNS:
            for col in df.columns:
                if expected_col.lower() in col.lower():
                    found_columns[expected_col] = col
                    break

        # Check if all expected columns were found
        missing_columns = [col for col in EXPECTED_COLUMNS if col not in found_columns]
        if missing_columns:
            messagebox.showerror("Error", f"Missing columns: {', '.join(missing_columns)}")
            return

        # Rename columns to the expected names (standardization)
        df.rename(columns=found_columns, inplace=True)

        # Convert 'Date_Time' to datetime
        df['Date_Time'] = pd.to_datetime(df['Date_Time'])

        # Create final DataFrame for organized data
        df_final = pd.DataFrame(columns=[
            "Name", "Equipment", "Initial Hour Meter", "Initial Km", "Cost Center",
            "Date_Time_Entry", "Final Hour Meter", "Final Km", "Date_Time_Exit", "Entry/Exit"
        ])

        # Group by Name, Equipment, and Cost Center
        for (name, equipment, cost_center), group in df.groupby(
            ['Name', 'Equipment', 'Cost Center']
        ):
            entry = group[group['Entry/Exit'].str.strip().str.lower() == 'entry']
            exit = group[group['Entry/Exit'].str.strip().str.lower() == 'exit']

            if not entry.empty and not exit.empty:
                # Sort entries and exits to get the correct times
                entry = entry.sort_values(by='Date_Time').iloc[0]
                exit = exit.sort_values(by='Date_Time').iloc[-1]

                # Add to the final DataFrame
                df_final = df_final.append({
                    "Name": name,
                    "Equipment": equipment,
                    "Initial Hour Meter": entry['Initial Hour Meter'],
                    "Initial Km": entry['Initial Km'],
                    "Cost Center": cost_center,
                    "Date_Time_Entry": entry['Date_Time'],
                    "Final Hour Meter": exit['Final Hour Meter'],
                    "Final Km": exit['Final Km'],
                    "Date_Time_Exit": exit['Date_Time'],
                    "Entry/Exit": "Exit"
                }, ignore_index=True)
            elif not entry.empty:
                # Case where there is only an entry (no corresponding exit)
                entry = entry.sort_values(by='Date_Time').iloc[0]
                df_final = df_final.append({
                    "Name": name,
                    "Equipment": equipment,
                    "Initial Hour Meter": entry['Initial Hour Meter'],
                    "Initial Km": entry['Initial Km'],
                    "Cost Center": cost_center,
                    "Date_Time_Entry": entry['Date_Time'],
                    "Final Hour Meter": 0,
                    "Final Km": 0,
                    "Date_Time_Exit": "",
                    "Entry/Exit": "Entry"
                }, ignore_index=True)

        # Save the processed file
        output_dir = os.path.dirname(file_path)
        output_filename = os.path.basename(file_path).replace('.xlsx', '_processed.xlsx')
        output_path = os.path.join(output_dir, output_filename)
        df_final.to_excel(output_path, index=False)

        messagebox.showinfo("Success", f"File processed successfully at:\n{output_path}")
    
    except Exception as e:
        messagebox.showerror("Error", f"An error occurred while processing the file:\n{str(e)}")

# Function to load and display the spreadsheet
def select_file():
    global df, file_path

    file_path = filedialog.askopenfilename(
        title="Select Excel File",
        filetypes=[("Excel Files", "*.xlsx"), ("All Files", "*.*")]
    )

    if not file_path:
        messagebox.showwarning("Warning", "No file selected!")
        return

    try:
        # Load the file (header is on the 3rd row, index starts at 0 → header=2)
        df = pd.read_excel(file_path, sheet_name='Worksheet', header=2)

        # Check format and columns
        format_ok, message = check_format(df)
        if not format_ok:
            messagebox.showerror("Error", f"The table is not in the expected format.\n{message}")
            return

        # Display the loaded table in the interface
        display_table(df)

    except Exception as e:
        messagebox.showerror("Error", f"An error occurred while loading the file:\n{str(e)}")

# Function to check if the spreadsheet has the correct format
def check_format(df):
    # Normalize column names (remove extra spaces and convert to lowercase)
    spreadsheet_columns = [col.strip().lower() for col in df.columns]
    expected_columns = [col.strip().lower() for col in EXPECTED_COLUMNS]

    missing_columns = [col for col in expected_columns if col not in spreadsheet_columns]
    if missing_columns:
        return False, f"Missing columns: {', '.join(missing_columns)}"
    return True, "Correct format."

# Function to display the table in the interface
def display_table(df):
    for widget in table_frame.winfo_children():
        widget.destroy()  # Clear previous table

    tree = ttk.Treeview(table_frame)
    tree["columns"] = list(df.columns)
    tree["show"] = "headings"

    for column in df.columns:
        tree.heading(column, text=column)
        tree.column(column, width=120)  # Adjust size

    for _, row in df.iterrows():
        tree.insert("", "end", values=list(row))

    tree.pack(expand=True, fill="both")

# GUI setup
root = tk.Tk()
root.title("Table Processor")
root.geometry("800x500")

button_frame = tk.Frame(root)
button_frame.pack(pady=10)

select_button = tk.Button(button_frame, text="Select File", command=select_file)
select_button.pack(side="left", padx=10)

process_button = tk.Button(button_frame, text="Process Table", command=process_table)
process_button.pack(side="left", padx=10)

table_frame = tk.Frame(root)
table_frame.pack(expand=True, fill="both")

root.mainloop()

如果大家有优化建议或者发现代码里的问题，欢迎帮忙指出！谢谢大家😊

备注：内容来源于stack exchange，提问作者Fabio