Filter DataFrame by using data through Tkinter GUI and displaying results in Treeview

Search DataFrame and display result in Treeview

We will create a Pandas DataFrame using Excel page, take user input and after searching show the result in a Treeview.

Creating DataFrame

Using the sample student Excel page, we will create the Pandas DataFrame by using read_excel().

df = pd.read_excel("E:\\data\\student.xlsx") # create DataFrame

Tkinter interface to search and filter Pandas DataFrame and display rows in Treeview

Tkinter window

We will keep one entry widget e1 to collect the user input and on click of the Button b1 we will call the function my_search().

import tkinter as tk
my_w = tk.Tk()
my_w.geometry("500x350")  # width x height
my_w.title("plus2net.com")  # Adding a title

l1 = tk.Label(my_w, text="Search", width=5, font=18)  # added one Label
l1.grid(row=1, column=1, padx=3, pady=10)

e1 = tk.Entry(my_w, width=35, bg="yellow", font=18)  # added one Entry box
e1.grid(row=1, column=2, padx=1)

b1 = tk.Button(my_w, text="Search", width=7, font=18, command=lambda: my_search())
b1.grid(row=1, column=3, padx=2)
my_w.mainloop()

my_search() Function

First we will create a list of column names or headers from the main dataframe df.
We will read the user inputs in entry widget e1 and then remove the space from both sides by using strip().
If the user input is not a digit then we will use string method contains() to match against the name column of the source DataFrame df.

We used tolist() to create a list from the DataFrame

def my_search():
    l1 = list(df)  # List of column names as headers
    query = e1.get().strip() # get user entered string
    if query.isdigit():  # if query is number
        str1 = df["id"] == int(query)  #
    else:
        str1 = df.name.str.contains(query, case=False)  # name column value matching
    df2 = df[(str1)]  # combine all conditions using | operator
    r_set = df2.to_numpy().tolist()  # Create list of list using rows

Creating Treeview

Inside the function my_search() we will use Tkinter Treeview to display Tabular data. Here the final output of the query or the resulted dataframe is displayed using the Treeview.

trv = ttk.Treeview(my_w, selectmode="browse")  # selectmode="browse" or "extended"
trv.grid(row=2, column=1, columnspan=3, padx=10, pady=20)  #
trv["height"] = 10  # Number of rows to display, default is 10
trv["show"] = "headings"

Previously we have collected all column headers as a list l1 from the source dataframe df. We will first display the headers by looping through the list l1.
Dynamic Creation of Header & Columns in Treeview

trv["columns"] = l1
    for i in l1:
        trv.column(i, width=90, anchor="c")
        # Headings of respective columns
        trv.heading(i, text=i)

Inserting data rows to Treeview

From our resulted DataFrame we have collected the list of rows as r_set. Using this we will insert each row to our Treeview trv.

for dt in r_set:
        v = [r for r in dt]  # creating a list from each row
        trv.insert("", "end", iid=v[0], values=v)  # adding row

import pandas as pd
from tkinter import ttk  # for treeview

df = pd.read_excel("F:\\data\\student.xlsx") # create DataFrame

import tkinter as tk

my_w = tk.Tk()
my_w.geometry("500x350")  # width x height
my_w.title("plus2net.com")  # Adding a title

l1 = tk.Label(my_w, text="Search", width=5, font=18)  # added one Label
l1.grid(row=1, column=1, padx=3, pady=10)

e1 = tk.Entry(my_w, width=35, bg="yellow", font=18)  # added one Entry box
e1.grid(row=1, column=2, padx=1)

b1 = tk.Button(my_w, text="Search", width=7, font=18, command=lambda: my_search())
b1.grid(row=1, column=3, padx=2)

def my_search():
    l1 = list(df)  # List of column names as headers
    query = e1.get().strip() # get user entered string
    if query.isdigit():  # if query is number
        str1 = df["id"] == int(query)  #
    else:
        str1 = df.name.str.contains(query, case=False)  # name column value matching
    df2 = df[(str1)]  # combine all conditions using | operator
    r_set = df2.to_numpy().tolist()  # Create list of list using rows
    trv = ttk.Treeview(my_w, selectmode="browse")  # selectmode="browse" or "extended"
    trv.grid(row=2, column=1, columnspan=3, padx=10, pady=20)  #
    trv["height"] = 10  # Number of rows to display, default is 10
    trv["show"] = "headings"
    # column identifiers
    trv["columns"] = l1
    for i in l1:
        trv.column(i, width=90, anchor="c")
        # Headings of respective columns
        trv.heading(i, text=i)
    for dt in r_set:
        v = [r for r in dt]  # creating a list from each row
        trv.insert("", "end", iid=v[0], values=v)  # adding row
my_w.mainloop()

Without using search button

KeyRelease event to search and filter Pandas DataFrame and display rows in Treeview

As we type the matching records will be displayed and there is no need to press Search button to get the result.
We can bind KeyRelease event of entry widget e1 to trigger the function.

e1.bind("<KeyRelease>",my_search)

We have to change the function declaration.

def my_search(*args):

def my_search(*args):
    l1=list(df) # List of column names as header 
-----
-----
e1.bind("<KeyRelease>",my_search)

Multiple word search

When we use more than one word to search then all words are to be checked for matching results. All the matching results against each word of the entered string are to be joined to return the final result.

Searching multiple words in DataFrame and display results in Treeview Part III

Step 1
Break the input string using split() and one list is created with each words. (remove the negative words from the list if required. )

words=query.split(' ') # List of words by breaking a string.

To remove some words or not to consider a list of words for searching.

words=query.split(' ') # List of words by breaking a string. 
words_stop=['to','and','or'] # List of words to be removed from searching
words=list(set(words)-set(words_stop)) # final list of words to search

Step 2
Loop through all words and append to filtered dataframe df2 by using contains() string method.

for w in words:
            df2=df2.append(df[df.name.str.contains(w,case=False)],ignore_index=True)

Step 3
From the final dataframe df2 remove the duplicate rows if any. Display the dataframe in Treeview.

df2=df2.drop_duplicates()  # remove duplicate rows

Using concat()

In place of append() we can use concat() as we may get this warning.

FutureWarning: The frame.append method is deprecated and will be removed 
from pandas in a future version. Use pandas.concat instead.

#df2=df2.append(df[df.name.str.contains(w,case=False)],ignore_index=True)
df2=pd.concat([df2,df[df.name.str.contains(w,case=False)]])

Save the output or result

We can save the filtered output by using asksaveasfile dialog. User can browse the local directory system and enter the file name to save at desired location.

Here our DataFrame df2 is the filtered output so we will use to_excel() to save the DataFrame in Excel format as per the selected path.
Note that we have used df2 as global variable as it is used inside different functions.

import pandas as pd
from tkinter import ttk
from tkinter import filedialog # to show file dialog box for saving
df=pd.read_excel("E:\\data\\student.xlsx") # create dataframe
import tkinter as tk

my_w = tk.Tk()
my_w.geometry("500x350")  # width x height
my_w.title("plus2net.com")  # Adding a title
font1=['Times',28,'normal']
l1=tk.Label(my_w,text="Search", width=5,font=18)
l1.grid(row=1,column=1,padx=3,pady=10)
e1=tk.Entry(my_w,width=15,bg='yellow',font=font1)
e1.grid(row=1,column=2,padx=1)
b1 = tk.Button(my_w, text="Save Result",bg='lightgreen',
     font=18, command=lambda: my_save())
b1.grid(row=1, column=3, padx=2)
df2 = df.iloc[:0] # create dataframe of same structure or columns 

def my_search(*args):
    global df2
    df2 = df.iloc[:0] # create dataframe of same structure or columns 
    l1=list(df) # List of column names as header 
    query=e1.get().strip() 
    if query.isdigit():
        df2=df[df['id']==int(query)]
    else:
        words=query.split(' ') # List of words by breaking a string. 
        words_stop=['to','and','or'] # List of words to be removed from searching
        words=list(set(words)-set(words_stop)) # final list of words to search
        for w in words:
            df2=df2.append(df[df.name.str.contains(w,case=False)],ignore_index=True)
    df2=df2.drop_duplicates()  # remove duplicate rows
    r_set=df2.to_numpy().tolist() # create list of list using rows
    trv=ttk.Treeview(my_w,selectmode='browse')
    trv.grid(row=2,column=1,columnspan=3,padx=10,pady=20)
    trv['height']=10
    trv['show']='headings'
    trv['columns']=l1
    for i in l1:
        trv.column(i,width=90,anchor='c')
        trv.heading(i,text=i)
    for dt in r_set:
        v=[r for r in dt]
        trv.insert("",'end',iid=v[0],values=v)
e1.bind("<KeyRelease>",my_search) 

def my_save():
    global df2
    file_path = filedialog.asksaveasfilename(
        filetypes=[("Excel file", ".xlsx")],
    defaultextension=".xlsx")
    if(file_path): # user has not Cancelled the browse window 
        df2.to_excel(file_path,index=False) # Save in Excel file
my_w.mainloop()

We can save the output in different sources and formats. Here is the list to which we can save our filtered dataframe df2
List of Outputs from DataFrame
Above function my_save() we will extended to include .csv ( Comma Separated Values ) file while saving. To identify which file type the user has selected we have used endswidth() string method.

def my_save():
    global df2
    file_path = filedialog.asksaveasfilename(
        filetypes=[("Excel file", ".xlsx"),("CSV file", ".csv")],
    defaultextension=".xlsx")
    if(file_path): # user has not Cancelled the browse window 
        if file_path.endswith('.csv'):
            df2.to_csv(file_path,index=False) # Save in csv format 
        else:    
            df2.to_excel(file_path,index=False) # Save in Excel file

Extend the above code to include file extensions storages a DataFrame can export.

Copy to Clipboard

We will use to_clipboard() to copy the filtered DataFrame df2 to clipboard. After copying we will display a message for a duration of 3000 milliseconds.

def my_copy():
    df2.to_clipboard()
    l3.config(text='copied')
    l3.after(3000,lambda:l3.config(text=''))

Searching and saving DataFrame and copy to clipboard Part IV

Number of records

By using len() we will get the number of rows in our filtered DataFrame df2. This number we will convert to string type by using str() and using configure option we will display in our Label l2.

l2.config(text='No of records: '+str(len(df2)))

import pandas as pd
from tkinter import ttk
from tkinter import filedialog
df=pd.read_excel("F:\\data\\student.xlsx") # create dataframe
import tkinter as tk

my_w = tk.Tk()
my_w.geometry("620x350")  # width x height
my_w.title("plus2net.com")  # Adding a title
font1=['Times',28,'normal']
l1=tk.Label(my_w,text="Search", width=5,font=18)
l1.grid(row=1,column=1,padx=3,pady=10)
e1=tk.Entry(my_w,width=15,bg='yellow',font=font1)
e1.grid(row=1,column=2,padx=1)
b1=tk.Button(my_w,text='Save Result',bg='lightgreen', 
                font=18, command=lambda:my_save())
b1.grid(row=1,column=3,padx=2)
l2=tk.Label(my_w,text='',bg='lightgreen',anchor='w')
l2.grid(row=2,column=1,sticky='W',padx=3,pady=3)
l3=tk.Label(my_w,text='',bg='lightyellow',anchor='w')
l3.grid(row=2,column=2,columnspan=2,sticky='W',padx=3,pady=3)
b2=tk.Button(my_w,text='Copy',bg='lightblue',
             font=18,command=lambda:my_copy())
b2.grid(row=2,column=3,padx=2)

df2 = df.iloc[:0] 
def my_search(*args):
    global df2
    df2 = df.iloc[:0]  # one blank dataframe
    l1=list(df) # List of column names as header 
    query=e1.get().strip() 
    if query.isdigit():
        str1=df['id']==int(query)
    else:
        words=query.split(' ') # list of words 
        words_stop=['to','and','or']
        words=list(set(words)-set(words_stop))
        for w in words:
              #df2=df2.append(df[df.name.str.contains(w,case=False)],ignore_index=True)
              df2=pd.concat([df2,df[df.name.str.contains(w,case=False)]])
    df2=df2.drop_duplicates()
    l2.config(text='No of records: '+str(len(df2)))
    r_set=df2.to_numpy().tolist() # create list of list using rows
    trv=ttk.Treeview(my_w,selectmode='browse')
    trv.grid(row=3,column=1,columnspan=3,padx=10,pady=20)
    trv['height']=10
    trv['show']='headings'
    trv['columns']=l1
    for i in l1:
        trv.column(i,width=90,anchor='c')
        trv.heading(i,text=i)
    for dt in r_set:
        v=[r for r in dt]
        trv.insert("",'end',iid=v[0],values=v)
e1.bind("<KeyRelease>",my_search) 
def my_save():
    global df2
    file_path=filedialog.asksaveasfilename(filetypes=[('Excel File','.xlsx'),
                            ('CSV file','.csv')],defaultextension='.xlsx')
    if file_path: # user has not cancelled the operation 
        if file_path.endswith('.csv'):
            df2.to_csv(file_path,index=False)
        else:
            df2.to_excel(file_path,index=False)
def my_copy():
    df2.to_clipboard()
    l3.config(text='Copied')
    l3.after(3000,lambda:l3.config(text=''))
my_w.mainloop()