[Python] Excel Table Manipulation using Pandas (Python)

Context: I work at a company where we handle equipment and employee records. We receive Excel spreadsheets with data on equipment usage (hour meters, mileage, and entries/exits). These spreadsheets need to be processed to generate standardized reports.

Problem: The spreadsheets we receive have the following format: SheetNotFormatted

We need to transform this table into a specific format where:

Entries and exits are consolidated into a single row.

The columns Date_Time_Entry and Date_Time_Exit are separated.

Proposed Solution: I decided to use Pandas (Python) to automate the processing. The idea is:

Load the spreadsheet, skipping the first two metadata rows.

Check if the expected columns are present.

Group the data by Name, Equipment, and Cost Center.

Consolidate entries and exits into a single row.

Save the processed file. SheetNotFormatted

Expected Output: SheetFormatted

Thanks in advance for any help or suggestions! If you need more details, feel free to ask.

Here is the code I am using:

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()

Continue reading...