How to filter columns in Tk treeview based on column value?-CodePudding

just started learning python/tkinter and already ran into a problem. I'm trying to open excel file in treeview which works perfect, now I found another script online which applies a drop menu to filter based on column values. But I'm failing to incorporate into mine, can anyone help please?

Current Code:

from tkinter import *
from tkinter import ttk, filedialog
import pandas as pd
win = Tk()
win.geometry("700x350")
style = ttk.Style()
style.theme_use('clam')

frame = Frame(win)
frame.pack(pady=20)


def open_file():
    global df
    filename = filedialog.askopenfilename(title="Open a File")

if filename:
    try:
        filename = r"{}".format(filename)
        df = pd.read_excel(filename)
    except ValueError:
        label.config(text="File could not be opened")
    except FileNotFoundError:
        label.config(text="File Not Found")

clear_treeview()

tree["column"] = list(df.columns)
tree["show"] = "headings"

for col in tree["column"]:
    tree.heading(col, text=col)
    tree.column("#1", anchor="center", width=200)
    tree.column("#2", anchor="center", width=150)
    tree.column("#3", anchor="center", width=150)
    tree.column("#4", anchor="center", width=150)

df_rows = df.to_numpy().tolist()
for row in df_rows:
    tree.insert("", "end", values=row)

tree.pack()


def clear_treeview():
    tree.delete(*tree.get_children())


tree = ttk.Treeview(frame)

m = Menu(win)
win.config(menu=m)

file_menu = Menu(m, tearoff=False)
m.add_cascade(label="Menu", menu=file_menu)
file_menu.add_command(label="Open Spreadsheet", command=open_file)

label = Label(win, text='')
label.pack(pady=20)

win.mainloop()

And code found online for filter:

from tkinter import *
import pandas as pd
from tkinter import ttk

df = pd.DataFrame({"currency":["EUR","XCD","ARS","CAD"],
               "volume":[400,500,600,700]})


class app(Tk):
    def __init__(self):
        Tk.__init__(self)
        self.title("Python Guides")

        self.tree = ttk.Treeview(self)
        columns = list(df.columns)
        self.Combo = ttk.Combobox(self, values=list(df["currency"].unique()),state="readonly")
        self.Combo.pack()
        self.Combo.bind("<<ComboboxSelected>>", self.select_currency)
        self.tree["columns"] = columns
        self.tree.pack(expand=TRUE, fill=BOTH)

        for i in columns:
            self.tree.column(i, anchor="w")
            self.tree.heading(i, text=i, anchor="w")

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

    def select_currency(self,event=None):
        self.tree.delete(*self.tree.get_children())
        for index, row in df.loc[df["currency"].eq(self.Combo.get())].iterrows():
            self.tree.insert("", "end", text=index, values=list(row))

ws = app()
ws.mainloop()

Example of table to be imported, How it's imported in treeview, Example of drop menu filter with values from Column "currency"

CodePudding user response：

I didn't know which column you want to populate with values so I left it as a Combobox selector, Selecting field will populate the values Combobox.

from tkinter import *
from tkinter import ttk, filedialog
import pandas as pd

win = Tk()
win.geometry("700x350")
style = ttk.Style()
style.theme_use('clam')

frame = Frame(win)
frame.pack(pady=20)
filename = None


def open_file():
    global df
    global filename
    filename = filedialog.askopenfilename(title="Open a File")

    if filename:
        try:
            filename = r"{}".format(filename)
            df = pd.read_excel(filename)
        except ValueError:
            label.config(text="File could not be opened")
        except FileNotFoundError:
            label.config(text="File Not Found")

    def create_column_combox():  # Creating the select field and value Combobox
        def populate_values_combo(e=None):  # populate values combobox with unique column values
            state = 'normal'
            combo_values = []
            if col_combo.get() == 'All':  # if option All selected
                state = 'disabled'  # disabling the option to select values
                select_value()
            else:
                combo_values = ['All']   df[col_combo.get()].unique().tolist()  # adding option All to values

            col_values_combo.config(values=combo_values, state=state)

        def select_value(e=None):  # cleaning tree and populating with df values
            tree.delete(*tree.get_children())
            if col_combo.get() == 'All' or col_values_combo.get() == 'All':  # if All selected as column or value
                col_values_combo.current(0)  # setting values to All
                [tree.insert("", "end", text=str(index), values=list(row)) for index, row in df.iterrows()]
            else:
                for index, row in df.loc[df[col_combo.get()].eq(col_values_combo.get())].iterrows():
                    tree.insert("", "end", text=index, values=list(row))

        columns = ['All']   list(df.columns)  # Adding option All to list of columns
        column_combo_label = Label(frame, text='Select field')
        column_combo_label.pack()
        col_combo = ttk.Combobox(frame, values=columns, state='readonly')
        col_combo.pack()
        col_combo.bind("<<ComboboxSelected>>", populate_values_combo)
        volume_combo_label = Label(frame, text='Select value')
        volume_combo_label.pack()
        col_values_combo = ttk.Combobox(frame, state='disabled')
        col_values_combo.pack()
        col_values_combo.bind("<<ComboboxSelected>>", select_value)

    create_column_combox()
    clear_treeview()

    tree["column"] = list(df.columns)
    tree["show"] = "headings"

    for col in tree["column"]:
        tree.heading(col, text=col)
        tree.column("#1", anchor="center", width=200)
        tree.column("#2", anchor="center", width=150)
        tree.column("#3", anchor="center", width=150)
        tree.column("#4", anchor="center", width=150)

    df_rows = df.to_numpy().tolist()
    for row in df_rows:
        tree.insert("", "end", values=row)

    tree.pack()


def clear_treeview():
    tree.delete(*tree.get_children())


tree = ttk.Treeview(frame)

m = Menu(win)
win.config(menu=m)

file_menu = Menu(m, tearoff=False)
m.add_cascade(label="Menu", menu=file_menu)
file_menu.add_command(label="Open Spreadsheet", command=open_file)

label = Label(win, text='')
label.pack(pady=20)

win.mainloop()