Content description:
In this article I'll describe using Tkinter and Pandas to display a refueling report received
from a gas station in xlsx format.
GUI class defined using tkinter in a separate file.
Excel file loaded using Pandas library - how to perform data searching and aggregation.
The project consists of two files: main.py and ui.py. In the ui.py file I define the GUI. There is a Data directory in the project directory with gas station report files in xlsx format.
.
├── Data
│ ├── WZ_2023-02-01_11_45_56.xlsx
│ └── WZ_2023-09-20_09_31_59.xlsx
├── main.py
└── ui.py
The main window consists of the following elements: a menu bar, a row containing simple navigation using breadcrumbs, a main row containing data in tabular form, and a status row.
All rows except the main row contain only enough space to hold the widgets they contain. The row containing the table data takes up all the remaining available space.
import tkinter as tk
from tkinter import ttk
from tkinter.font import nametofont
class UI:
def __init__(self, root):
root.rowconfigure(0, weight=0) # Breadcrumbs row
root.rowconfigure(1, weight=1) # Main row
root.rowconfigure(2, weight=0) # Status line row
root.columnconfigure(0, weight=1)
menu = self.create_menu(root)
root.config(menu=menu)
self.breadcrumbs_frame = tk.Frame(root)
self.breadcrumbs_frame.grid(column=0, row=0, sticky="w")
self.main_frame = tk.Frame(root)
self.main_frame.columnconfigure(0, weight=1)
self.main_frame.rowconfigure(0, weight=1)
self.main_frame.grid(column=0, row=1, sticky="nswe")
self.status_frame = tk.Frame(root)
self.status_frame.grid(column=0, row=2, sticky="w")
self.create_breadcrumbs()
self.create_main_content()
self.create_status_bar()
In the create_menu() method, I define a simple menu containing two items: File and Help, which allow you to close the program or display the About window.
def create_menu(self, root):
menu_bar = tk.Menu(root)
file_menu = tk.Menu(menu_bar, tearoff=0)
file_menu.add_command(label="Exit", command=root.quit_app)
menu_bar.add_cascade(label="File", menu=file_menu)
about_menu = tk.Menu(menu_bar, tearoff=0)
about_menu.add_command(label="About", command=self.create_about)
menu_bar.add_cascade(label="Help", menu=about_menu)
return menu_bar
Then I define a simple template navigation that will allow you to quickly return to the main view or category view.
def create_breadcrumbs(self):
self.items_button = tk.Button(master=self.breadcrumbs_frame, text="Items", bd=0)
self.items_button.grid(column=0, row=0, padx=5, pady=5)
tk.Label(master=self.breadcrumbs_frame, text=">").grid(column=1, row=0)
self.category_button = tk.Button(master=self.breadcrumbs_frame, bd=0)
self.category_button.grid(column=2, row=0)
tk.Label(master=self.breadcrumbs_frame, text=">").grid(column=3, row=0)
self.detail_button = tk.Button(master=self.breadcrumbs_frame, bd=0)
self.detail_button.grid(column=4, row=0)
The main row contains a TreeView widget that presents data in a table format. The data can be scrolled using the vertical scroll bar. Every other row in the table is in a darker color.
def create_main_content(self):
self.treeview = ttk.Treeview(master=self.main_frame, show="headings")
self.treeview.tag_configure("even", background="#D3D3D3")
self.treeview.tag_configure("font", font=tk.font.Font(size=12))
self.treeview.configure
scrollbar = ttk.Scrollbar(
self.main_frame, orient="vertical", command=self.treeview.yview
)
scrollbar.grid(column=1, row=0, sticky="ns")
self.treeview.configure(yscrollcommand=scrollbar.set)
nametofont("TkHeadingFont").configure(weight="bold", size=12)
self.treeview.grid(row=0, column=0, sticky="nswe")
There is a label in the status bar that will contain messages about the correct operation of the program and aggregated values.
def create_status_bar(self):
self.status_label = tk.Label(master=self.status_frame)
self.status_label.grid(column=0, row=0, padx=5)
I load data from the last report into a Pandas frame in the main.py file .
import os
import glob
import pandas as pd
import tkinter as tk
from ui import UI
class App(tk.Tk):
def __init__(self):
super().__init__()
self.wm_attributes("-zoomed", True)
self.ui = UI(self)
self.report = self.load_last_report()
from_date = self.report["Date"].min()
to_date = self.report["Date"].max()
self.title(
f"FUEL - from {from_date.day}/{from_date.month}/{from_date.year} \
to {to_date.day}/{to_date.month}/{to_date.year}"
)
self.load_items()
def load_last_report(self):
try:
current_dir = os.path.dirname(os.path.abspath(__file__))
data_dir = os.path.join(current_dir, "Data")
reports = glob.glob(os.path.join(data_dir, "*.xlsx"))
last_report = max(reports, key=os.path.getctime)
path_to_last_report = os.path.join(data_dir, last_report)
report = pd.read_excel(path_to_last_report)
return report
except Exception as e:
print("Exception:", e)
self.ui.status_label.config(
text="Something went wrong when loading your data"
)
return None
def quit_app(self):
self.destroy()
The load_items() method displays data about all types of fuel purchased.
def load_items(self):
self.ui.status_label.config(text="")
self.ui.treeview.delete(*self.ui.treeview.get_children())
self.ui.treeview["columns"] = ("#", "Item", "Quantity")
self.ui.treeview.column("#", anchor=tk.CENTER, stretch=0, width=50)
self.ui.treeview.column("Item", anchor=tk.CENTER)
self.ui.treeview.column("Quantity", anchor=tk.CENTER)
self.ui.treeview.heading("#", text="#", anchor=tk.CENTER)
self.ui.treeview.heading("Item", text="Item", anchor=tk.CENTER)
self.ui.treeview.heading("Quantity", text="Quantity", anchor=tk.CENTER)
fuel_group = self.report.groupby(Product name")
fuel_group_sums = fuel_group["Quantity"].sum()
for i, group in enumerate(fuel_group_sums.items(), start=1):
item, quantity = group
if i % 2:
self.ui.treeview.insert(
"", "end", values=(str(i), item, quantity), tag="font"
)
else:
self.ui.treeview.insert(
"",
"end",
values=(str(i), item, quantity),
tag=("even", "font"),
)
self.ui.treeview.bind("<<TreeviewSelect>>", self.on_item_selected)
self.ui.items_button.config(command=self.load_items)
self.ui.category_button.config(text="Category", state="disabled")
self.ui.detail_button.config(text="Detail", state="disabled")
Once the user selects fuel, detailed data about the amount of fuel purchased for each car appears. This data appears in the same widget.
def on_item_selected(self, event=None, item_values=None):
if not item_values:
selected_item = self.ui.treeview.selection()
item_values = self.ui.treeview.item(selected_item)
_, category, quantity = item_values["values"]
self.ui.status_label.config(text=f"Total quantity for {category}: {quantity}")
filt = self.report["Product name"] == category
cars_group = self.report[filt].groupby("Registration number")
cars_group_sums = cars_group["Quantity"].sum()
self.ui.treeview.unbind("<<TreeviewSelect>>")
self.ui.treeview.delete(*self.ui.treeview.get_children())
for i, group in enumerate(cars_group_sums.items(), start=1):
item, quantity = group
if i % 2:
self.ui.treeview.insert(
"", "end", values=(str(i), item, quantity), tag="font"
)
else:
self.ui.treeview.insert(
"",
"end",
values=(str(i), item, quantity),
tag=("even", "font"),
)
self.ui.treeview.bind("<<TreeviewSelect>>", self.on_car_selected)
self.ui.category_button.config(
command=lambda: self.on_item_selected(item_values=item_values)
)
self.ui.category_button.config(state="active", text=category)
self.ui.detail_button.config(text="Detail", state="disabled")
When the user selects a specific car, data on refueling dates, quantities, net and gross costs is displayed.
def on_car_selected(self, event=None, car=None):
selected_item = self.ui.treeview.selection()
if selected_item:
item_values = self.ui.treeview.item(selected_item)
_, car, quantity = item_values["values"]
filt = self.report["Registration number"] == car
group = self.report[filt][["Date", "Quantity", "Net amount", "Gross amount]]
group["Date"] = group["Date"].dt.strftime("%d/%m/%Y")
self.ui.status_label.config(
text=f"Total quantity for car {car}: {quantity}"
)
self.ui.detail_button.config(state="active", text=car)
self.ui.treeview.delete(*self.ui.treeview.get_children())
self.ui.treeview["columns"] = ("#", "Date", "Quantity", "Net", "Gross")
self.ui.treeview.column("#", anchor=tk.CENTER, stretch=0, width=50)
self.ui.treeview.column("Date", anchor=tk.CENTER)
self.ui.treeview.column("Quantity", anchor=tk.CENTER)
self.ui.treeview.column("Net", anchor=tk.CENTER)
self.ui.treeview.column("Gross", anchor=tk.CENTER)
self.ui.treeview.heading("#", text="#", anchor=tk.CENTER)
self.ui.treeview.heading("Date", text="Date", anchor=tk.CENTER)
self.ui.treeview.heading("Quantity", text="Quantity", anchor=tk.CENTER)
self.ui.treeview.heading("Net", text="Net amount", anchor=tk.CENTER)
self.ui.treeview.heading("Gross", text="Gross amount", anchor=tk.CENTER)
self.ui.treeview.unbind("<<TreeviewSelect>>")
for i, value in enumerate(group.values, start=1):
if i % 2:
self.ui.treeview.insert(
"",
"end",
values=(
str(i),
value[0],
value[1],
value[2],
value[3],
),
tag="font",
)
else:
self.ui.treeview.insert(
"",
"end",
values=(
str(i),
value[0],
value[1],
value[2],
value[3],
),
tag=("even", "font"),
)